With the data ready and prepared, we are now ready to do the actual analysis. And if the previous steps have been conducted well, this is actually one of the quicker and easier steps. Since we already have a table in Excel, we use the built-in analysis tool called pivot table to get a quick analysis of what we want. (This is found under “Insert” – “Pivot Table”)
Having properly prepared the data and knowing what it is we want to discover, we can simply drag “Date” to the row field, “Type” to the column field, and “Transaction” to the values field, as shown to the right.
This quickly puts all the dates with transactions in the first column, makes a separate column for each type of transaction, and puts the sum of each type of transaction in the appropriate cell. However, since our goal is to do this on a monthly basis, we do not need each individual date. So what we should do is mark a cell in the first row that contains a date, and click the button that says “group field”. This will immediately recognize that we are talking about dates, and suggest that we group it by months. Simply click OK, and we will get months instead of individual dates in the first column. Voilà, you now have a basic analysis of all your income and various spending for each month through the year, as well as a general overview. Yes, it still looks messy, but this is essentially the analysis that we set out to do. You can read how much income you had in the various months, how much your fixed spending was, how much you saved and what your variable spending for each month was. Yet this still feels incomplete, and there are a few quick cleaning tips we can do to make it look better.
First, let us change those individual letters back into their full names. Simply highlight the column header that is now called “F” and type in “Fixed” instead, and continue correspondingly with the remaining headers. (This is one reason for using single letters while preparing the data, as it is much quicker to just type it out once here). Additionally, we can format the numbers of the transaction to either currency or accounting by clicking on the “Sum of Transactions” and going in to the properties. Go to number format and choose the one you want. Now our table looks much neater and complete. (I’ve blocked out most transactions from the image, but you get the idea.) And this concludes the analysis step, and all that remains is to present the data.