Creating a simple expense spreadsheet
Now many a time it becomes a necessity to track daily expenditure (or weekly expenditure) and try and figure out your spending trend over the year. I created a quick solution for anyone out there who requires something like this. It should not take you more than 3-5 minutes unless you are blogging about it. (Thought of implementing it in SSRS earlier but an excel sheet is good enough to do the trick)
1. Open Excel and type in the Location, Date and Expense made... something similar to the following image:-
2. Now in case you want to just check the trends of your expenditure using databars, create another column or insert a column called trend and then click on conditional formatting on the Home Ribbon (Keeping the Trend Column Selected) and choose the Databar along with the required color formatting --> the image is as shown below:
3. Next Step Ensure that for every cell in the Trend column you point it to the respective cost which in my case are the values present in the B column so just click on the first cell in the trend column and use the following formula =B1 and then you should get a value with a Data Bar, copy and paste the same formula for the remaining cells as shown in the following pic:-
4. Create two more columns that will contain the Date Range and the cumulative total spent using the SUMIF function (An example is let's say that F & G are the new columns then F contains the distinct Dates and G will contain the total, the formula for G column cell will be as =SUMIF(D2:D9,F2:F3,B2:B9) and the result is as shown):
5. Now let us create a chart based on our expenditure with the X axis having the dates and the Y axis containing the costs (you can interchange if you like....). Click on the insert Ribbon > Line (I am creating a Line chart) > Select the 2-D line chart (No need to complicate the chart with fancy features... if that is the case choose the chart you feel is most appropriate for your purposes).
5. Now right click the chart image that was created and choose Select Data from the context menu.
6. The formula for the Horizontal axis labels will be =Sheet1!$F$2:$F$3 (or the required limit) and the formula for the X-axis series will be =Sheet1!$G$2:$G$3 and the result will be the as follows:-
We are done with our expense sheet.
1. Open Excel and type in the Location, Date and Expense made... something similar to the following image:-
2. Now in case you want to just check the trends of your expenditure using databars, create another column or insert a column called trend and then click on conditional formatting on the Home Ribbon (Keeping the Trend Column Selected) and choose the Databar along with the required color formatting --> the image is as shown below:
3. Next Step Ensure that for every cell in the Trend column you point it to the respective cost which in my case are the values present in the B column so just click on the first cell in the trend column and use the following formula =B1 and then you should get a value with a Data Bar, copy and paste the same formula for the remaining cells as shown in the following pic:-
4. Create two more columns that will contain the Date Range and the cumulative total spent using the SUMIF function (An example is let's say that F & G are the new columns then F contains the distinct Dates and G will contain the total, the formula for G column cell will be as =SUMIF(D2:D9,F2:F3,B2:B9) and the result is as shown):
5. Now let us create a chart based on our expenditure with the X axis having the dates and the Y axis containing the costs (you can interchange if you like....). Click on the insert Ribbon > Line (I am creating a Line chart) > Select the 2-D line chart (No need to complicate the chart with fancy features... if that is the case choose the chart you feel is most appropriate for your purposes).
5. Now right click the chart image that was created and choose Select Data from the context menu.
6. The formula for the Horizontal axis labels will be =Sheet1!$F$2:$F$3 (or the required limit) and the formula for the X-axis series will be =Sheet1!$G$2:$G$3 and the result will be the as follows:-
We are done with our expense sheet.
Comments