Using Microsoft Excel can be tricky; however, when used properly it can also be of great use. Microsoft Excel has a functionality called Pivot Tables, which are particularly useful. Pivot tables can sort data in easy to view formats and make reports from data with ease. With the use of Pivot Tables, you can summarize Excel data and/or make charts painlessly. This can be useful for individuals keeping track of their finances and for entrepreneurs’ analytical needs.
If you want to create a Pivot Table, you must first have data that you need to sort. Pivot tables work best when each column has a discrete informational item and a title (i.e. First Name, Last Name, Address, State, etc…) across the first row of the workbook. Then you can type out or download your data from your source. A .csv file (also known as a comma-delimited file) is a typical way that your bank will let you download your banking history, so let’s use that as our example. Let’s say the data set in Microsoft Excel looked something like picture 1.
Highlight the information with your computer mouse and then (in Microsoft Excel 2007) go to the Insert menu and click PivotTable (the first option on the left – see picture 1). If you cannot find the pivot table in the drop down menu, you can alternatively hold down the “Alt” button and then type the letters “N”, “V”, and “T.” Either way, Microsoft Excel will open the Pivot Table wizard. Review the information and assuming everything looks good, click the okay button.
On the right, Microsoft Excel will open a Pivot Table Field list (see Picture 3) which you can use to sort out the type of information you would like to include on your table. Microsoft Excel will include each column header as a sortable value. So, we will see Trans #, Date, Type, Amount, Description and Balance as the possible ways to sort our table. If we are interested in seeing how much we spent in each place and the average transaction amount for the month, put the “Description” tag as the row label and then in values, put the “Amount” tag. If you want different information (ie an average instead of a sum) click the arrow next to the tag in Values, click Value Field Settings and Microsoft Excel will offer you other choices for your Pivot Table.
Our Pivot Table will end up looking like picture 2.
Pivot Tables are a “trick of the trade” and are very helpful and useful when trying to analyze large sets of data. This functionality helps to make Microsoft Excel a much more powerful piece of software than it might otherwise be.
If you are interested for more tricks and tools with Microsoft Excel, click back to my profile and look at some of my other technology articles and videos.