It is easy to use Microsoft Excel to track expenses and revenue for a small business, and there is a powerful feature of Microsoft Excel that can make this task much easier. The powerful and sometimes intimidating Pivot Table. For all of my examples I am using Microsoft Excel 2010, but there are few (if any differences) between 2010 and 2007, and I switch between both versions on a regular basis with little few problems.
The first step is to enter all of your line items. In Column A enter the Amount, and in Column B enter the Type. Make sure to enter labels for your columns in A1 and B1 respectively. In this case the labels are “Amount” and “Type”. Items are shown below:
Select the data (in this case A1 through B8). Click on the tab “Insert”. On the far left hand corner is an icon labeled “PivotTable”. Left click on it. The Table/Range should already be filled in from your selection a second ago. In my case, it is “Sheet1!$A$1:$B$8”. This will change if your worksheet has a different name. Click “OK”.
It appears we are given a worksheet with no information, but we haven’t told the pivot table what we want displayed yet. Under the heading “Choose fields to add to report” check both “Item” and “Type”. This has summed up all of the items. In this case $180.00 was spent on gas, revenue is $900, and $320 is spent on supplies.
It is easy to change the display and to show different items.
If I do not want to show revenue anymore, I can do so easily. There is a triangle in “Row Labels” on the table. If you click on that, it shows a drop down menu. Uncheck “Revenue” to no longer show that item in the report.
Let us say that I want to know how many times each transaction occurred. Under “Values”, click on “Sum of Item”, and then click on “Value Field Settings”. Choose “Count”, and then “OK”. I can tell that gas was purchased twice, as were supplies.
If I want to know the average of each transaction amount the process as above, except that I would choose “Average”.
I can even calculate “% of Grand Total” if I choose (under Value Field Settings, Show Values As). In this case gas is 72% and supplies are 128%.
If I want it to read horizontally instead of top down, I just move “Type” to the section called “Column Labels”. To move it you just left click and hold, and drag the item (in this case “Type”).