Expense Tracking helps you to write down what you spend and later analyse your spending habits to make decisions to spend your hard earned money more wisely. There are various spreadsheets, apps or software programs for tracking your expenses. But using your own created spreadsheet enhances your enthusiasm to enter the expenses regularly. Besides creating an expense tracker, you can learn few basic concepts of a powerful tool - Excel, which can help to make your life better in performing calculation, analysis and visualisation of data and information.
Tips:
1. Shortcuts:
Save = Ctrl+S
Copy = Ctrl+C
Paste = Ctrl+V
Bold = Ctrl+B
Go to End of Data = Ctrl+End
Select till End of Data = Ctrl+Shift+End
2. Hit Enter or Tab Key after using references to other cells(in same sheet or different sheet) or formula
3. Functions used in this Template:
SUM
IF
ISBLANK
SUMIF
SUBTOTAL
If you are unable to follow the steps, please have a look at the Video which is a screen recording of the steps.
Please note that the Videos are just Screen Recording and doesn't include any audio but includes text for guidance wherever required.
Part-1
Part-2
Part-3
Part-4
Preparing Excel Workbook:
1. Open a New Microsoft Excel Workbook. I am using Office 2007
2. Rename “Sheet1” as “Jan” and “Sheet2” as “Instructions”. This can be done by Right clicking on the Sheet tab and selecting Rename. The Sheet name becomes editable. After renaming, click any cell on the sheet which saves the new name.
Save the file as Name=Yearly Expenses_2017_Template and Type=.xlsx
OR
Name = Personal Expense Tracker_YYYY_Template
If the file is shown in Compatibility mode, close the file and open it again. Read here.
Defining Categories:
3. On the Instructions sheet, mention the Expenses Type you would like to have. I have considered the following Types:
Housing
Utilities
Groceries
Living
Transportation
Health Care
Personal
Eating Out
Entertainment
Debt Payments
Travel
Children
Savings
Type14
Type15
Type14,15 are for future purposes.
You can define what comes under each of the Expense Type. My classification is as below which can be kept in the Instructions sheet for quick reference.
Housing - Rent, Loan, Property Tax, House Insurances
Utilities - Phone/Cell, Cable/Internet, Gas, Electricity
Groceries - Food (Vegetables/Fruits/Biscuits/Chocolates), Household Supplies, Toileteries
Living - Personal Care (Salon/Cosmetics/Beauty), Maid, Bank Fees, Dry Cleaning, Pet Costs, Clothing, Daily wear ornaments, Luggage Bags, Footwear, Kitchenware
Transportation - Fuel, Auto Insurance, Parking, Taxi, Services, Pollution Certificate, Tolls (within Residence)
Health Care - Medical Premiums, Life Insurance (without returns), Medication, Eye Care, Dental, Wellness Costs
Personal - Books/CDs, Donations, Gifts, Subscriptions, Electronics, Parties, Jewellery
Eating Out - Meals, Snacks, Tea/Coffee, Juices
Entertainment - Recreation, Sports Equipment/Fees, Movies/Tickets, Hobbies, Memberships (Fitness, etc.), Holidays
Debt Payments - Loans, Credit Cards, Leases, Government Debts
Travel - Car/Bus/Train/Flight to places outside of Residence, Tolls (Travelling outside of Residence)
Children - Day Care, Lessons/Activities, Allowance, School Supplies/Fees, Babysitting
Savings - Deposits, PF, Chit, Mutual Funds, Shares, Insurance (with returns), Emergency Fund
4. You can Autofit the column by double clicking on column header border.
Bold the “Expense Type” and fill it with Light Blue color. This is the Formatting Step.
5. Leave a column and enter the date ranges for each month. Below are the Column Headers and Sample format for January:
Month Start Date End Date
January 1-Jan-17 31-Jan-17
6. Repeat the Formatting step for all the 3 columns.
7. Leave a column and enter the Transaction Type:
Cash
IC Debit
HD Debit
Credit Card
Bank1
Bank2
8. Repeat the Formatting step for the column.
Adding Columns to Monthly sheet:
9. Jump to the "Jan" sheet.
10. From Row2, enter the following Column Headers:
Date
Description
Expense Type
Amount
Comments
Transaction Type
Tip: You can use "Tab" Key to go to the next column.
10. Mouse over the column header border where you can see an icon for adjusting the width of the column on the left. On the column header after seeing the icon, left click the Mouse and drag the column to the required width.
Following are the width set and you can customize as per your preference:
Date = 10.00
Description = 30.00
Expense Type = 15.00
Amount = 10.00
Comments = 30.00
Transaction Type = 15.00
11. Format the column headers.
Select the Column Headers. This can be done in the following way:
a. Click on the first column Header (cell A2 = Date).
b. Hold the Shift Key and Press the Right Arrow till the last column Header(cell F2 = Transaction Type).
c. As can be seen all column headers are selected.
12. Click on the Center Text icon. Fill it with Light Green color
13. Go to the Column Y. Enter Column Headers for Start End Dates
Y1 = Start Date
Z1 = End Date
Enter the following so that we can get the values of January range:
In cell Y2, enter as "=Instructions!D2" and TAB OR
Put an "=" (equals symbol) in cell Y2, click on the Instructions sheet, click on the Start Date for January month. Hit Enter Key.
Similary enter the End Date in cell Z2 as "=Instructions!E2"
Keeping Validations for Column values:
14. Click on the cell A3 (below the Date header).
a. Go to Data tab, in the Data Tools group, click Data Validation.
b. On the Settings tab, in the Allow box, select Date.
c. Let the Ignore blank be default (checked)
d. Let the Data value be default = between
e. To the right of the Start date box, click on the button (red-arrowed) to collapse the dialog box, and then select the Start Date value (cell Y2).
Make the cell values as Absolute references. Put a dollar symbol ($) before the column and the row like $Y$2. Refer this page for more information.
f. Click the button (red arrowed) to expand the dialog box.
g. Repeat the step for entering the End Date value (cell Z2)
h. Click on the Error Alert tab, let the checkbox be checked, Style = Stop
Enter the following:
Title = Error
Error message: Enter dates for the month (sheet selected)
i. Click on OK button.
15. In cell H10, enter the following to COPY the Expense Type Categories:
"=Instructions!A2"
Click on Enter Key or TAB to go to another cell.
Click again on cell H10. COPY the cell via Ctrl+C keys
Select the cells from H11 to H24 by clicking on the cell H11 and pressing Shift+Down Arrow.
Use Ctrl+V to PASTE the values.
16. In cell H31, enter the following to COPY the Expense Type Categories:
"=Instructions!G2"
Click on Enter Key or TAB to go to another cell.
Click again on cell H31. COPY the cell via Ctrl+C keys
Select the cells from H31 to H36 by clicking on the cell H31 and pressing Shift+Down Arrow.
Use Ctrl+V to PASTE the values.
Tip: Remember to save your work often using Ctrl+S keys.
17. Click on the cell C3 (below the Expense Type header).
a. Go to Data tab, in the Data Tools group, click Data Validation.
b. On the Settings tab, in the Allow box, select List.
c. Let the Ignore blank, In-cell dropdown be default (checked) ,
d. To the right of the Source box, click on the button (red-arrowed) to collapse the dialog box, and then select the first Expense Type value (cell H10).
Make the cell values as Absolute references like $H$10. Hold the Shift Key and Press the Down arrow key till the last Expense type. Now the range of cells is selected which should be as "=$H$10:$H$24"
Ensure that Absolute references are used.
e. Click the button (red arrowed) to expand the dialog box.
f. Click on OK button.
18. Click on the cell D3 (below the Amount header).
a. Go to Data tab, in the Data Tools group, click Data Validation.
b. On the Settings tab, in the Allow box, select Whole number.
c. Let the Ignore blank be default (checked)
d. Enter the following:
Data = greater than
Minimum = 0
e. Click on OK button.
19. Click on the cell F3 (below the Transaction Type header).
a. Go to Data tab, in the Data Tools group, click Data Validation.
b. On the Settings tab, in the Allow box, select List.
c. Let the Ignore blank, In-cell dropdown be default (checked) ,
d. To the right of the Source box, click on the button (red-arrowed) to collapse the dialog box, and then select the first Transactio Type value (cell H31).
Make the cell values as Absolute references like $H$31. Hold the Shift Key and Press the Down arrow key till the last Transaction type. Now the range of cells is selected which should be as "=$H$31:$H$36"
Ensure that Absolute references are used.
e. Click the button (red arrowed) to expand the dialog box.
f. Click on OK button.
--------------Video Part-1 Ends--------------
20. COPY the validations of 3rd row to other rows.
a. Click on the first cell in Row3 (A3) where the validation was applied.
b. Hold the Shift Key and Press the Right Arrow till the last column of Row3(F3).
c. As can be seen all cells are selected.
d. COPY the selection using Ctrl+C keys.
e. Apply this to the remaining rows. Click on the first column of 4th row (A4) and press Shift+Down arrow key till the last row required. (I am taking 200 rows)
f. PASTE the values using Ctrl+V keys.
g. Observe that all the validations are copied till 200 rows for the 6 columns.
Tip: Remember to save your work often using Ctrl+S keys.
Entering other fields for Monthly sheet:
21. In cell H2, enter the "Total Income". Bold the text using Ctrl+B or using the Font group > B.
Cell I2 (beside Total Income) serves as input for all the monthly income like Salary, per diems, shift allowances, etc. You can keep a "=" and then using "+" sign to add additional incomes.
Ex: =50000+4000+250
Click on Enter or Tab key after entering the values.
22. In cell H3, enter the "Expenses". Bold the text using Ctrl+B or using the Font group > B.
Cell I3 (beside Expenses), write the following formula to calculate the sum of all amounts in column D. Note that we have considered only till 200 rows.
=SUM($D$3:$D$200)
Hit Enter or Tab key after entering the formula.
Refer this page for more information on SUM formula.
23. In cell H4, enter the "Surplus". Bold the text using Ctrl+B or using the Font group > B.
In cell I4 (beside Surplus), write the following formula to get the difference of Income and Expenses.
You can keep an "=" in the cell I4, select cell I2, put a "-", select cell I3 and press Enter Key.
Ensure to convert the cell references into Absolute Reference.
OR you can directly write the formula in cell I4:
=$I$2-$I$3
But there is a catch here. Sometimes the Expenses might exceed the Total Income, in which case we might get a negative Surplus amount. To handle such cases, we stop the Surplus at 0, if it goes to negative values.
We use the following IF function:
=IF(($I$2-$I$3)<0,0,($I$2-$I$3))
Hit Enter or Tab key after entering the formula.
If the difference is less than zero, we enter 0, otherwise we enter the difference.
Refer this page for more information n IF statement.
24. In cell H5, enter the "% Surplus". Bold the text using Ctrl+B or using the Font group > B.
In cell I5, write the following formula to get the percentage surplus of Total income
You can keep an "=" in the cell I5, select cell I4, put a "/", select cell I2 and press Enter Key.
Ensure to convert the cell references into Absolute Reference.
OR you can directly write the formula in cell I5:
==$I$4/$I$2
But, we do not have Total Income for all months initially. So the division may give Division by zero error. To eliminate this issue, we use the following IF function:
=IF(ISBLANK($I$2),0,$I$4/$I$2)
ISBLANK checks if a cell is empty or not.
If the Total Income is BLANK, we enter 0, otherwise do the division.
Refer this page for more information on ISBLANK function.
This value needs to be converted to a %. So, we select the cell I5, go to Home tab > Number group, click on the % icon.
You can check by entering any amount in the Amount column (say, 56) and Total Income (Say, 123). The %Surplus is shown as 54%
Calculating Category Wise Expenses:
25. We compare the Expense Type in cell H10 against the Expense Type column (Column C) and then we sum the Amount column (Column D)for those rows where the Expense Type is matching with cell H10.
Write the below formula in cell I10:
=SUMIF($C$3:$C$200,H10,$D$3:$D$200)
Notice that the above formula has H10 as Relative reference and not as Absolute reference as we are going to COPY this to the remaining Expense Types.
Refer this page for more information on SUMIF function
Select cell I10 and COPY using Ctrl+C. Click on cell I11 and press Shift+Down arrow keys till the last Expense Type. Now PASTE the formula using Ctrl+V.
Tip: Remember to save your work often using Ctrl+S keys.
Calculating Transaction Wise Expenses:
26. We compare the Transaction Type in cell H31 against the Transaction Type column (Column F) and then we sum the Amount column (Column D)for those rows where the Transaction Type is matching with cell H31.
=SUMIF($F$3:$F$200,H31,$D$3:$D$200)
Notice that the above formula has H31 as Relative reference and not as Absolute reference as we are going to COPY this to the remaining Transaction Types.
Select cell I31 and COPY using Ctrl+C. Click on cell I32 and press Shift+Down arrow keys till the last Expense Type. Now PASTE the formula using Ctrl+V.
Formatting the Columns H and I :
27. Autofit Column H by clicking on the Column H header border.
28. Cell H2 filled with Light Green color as we are expecting an Input in cell I2.
29. For all the remaining cells of H and I, fill the field names and values with Light Blue.
You can select multiple cells by clicking on the first cell and then dragging your mouse till the last cell.
Note: Fill the color only for rows which have text and are expecting values.
30. Ensure that all fields and values in Columns H and I have borders.
For Ex: Select cells H2 to cell I4. Go to Home tab > Font group > Click on Borders dropdown and select "All Borders"
Formatting the Data Columns A to G:
31. Select the cells A2 to F200. Apply All Borders to the selection.
32. Select Column B (by clicking on the Column Header). The whole Column gets selected. Go to Home tab > Alignment group > click Wrap Text. This ensures that any lengthy text is displayed completely.
Repeat Wrap Text for Column E.
33. Select Column D and go to Alignment group > click on Center Text.
34. Apply Filters for Column Headers in Row2, A to F
Select the Column Headers A2 to F2. In the Editing group > click on Sort & Filter > click on Filter.
Bold the Column Headers.
35. We may sometimes want to see the Amount of only the Filtered transactions. For this purpose, we use Subtotal function. Write the below function above the Amount column header i.e. cell D1
=SUBTOTAL(9,$D$3:$D$200)
9 is the function number that we are doing SUM of the Filtered data from D3 to D200.
Refer this page for more details on SUBTOTAL function.
36. Label this value as Filtered Amount in cell E1.
--------------Video Part-2 Ends--------------
37. Fill the cell D1 (value) with Light Blue color.
Pie Chart - Representing the Expenses in terms of percentage of each Expense Type:
38. We want to draw Pie Chart for the Expense Type. So we select cell H10 to cell I24.
39. Go to Insert tab > Charts group > click Pie > select the simple 2-D Pie.
40. Drag the Chart to an empty area ( say to the Right of Column K). You can see a + icon with arrows on all 4 sides indicating the movement of the chart.
Ensure that you are clicking on the empty space and NOT the Chart or the Legend.
You can also Expand the Chart by enlarging the Corners.
41. We can select the Chart Layout. Go to Chart Tools (this is activated when a chart is selected) > Chart Layouts group > click Layout1 (which has Chart heading and % within the chart and No Legend)
42. Click on the "Chart Title" which makes it Editable. Write it as Expenses.
43. You can change the color of the Chart in the Chart Styles.
Note: Suppose if you do not want the Expense Type 14,15 not to show in the Pie Chart, you can remove them from selection.
Select the Plot Area (where the pie is) > Right Click > Select Data. In that change the Chart data range to the required row. Click on OK button to save the Changes.
In future, if you had any new category defined for Type14 or 15 and want to include them in the Chart, you need to change manually for each month sheet.
Protecting the Excel Formulas (Optional):
44. If you feel that there are chances of editing the formulas unintentionally and feel a need to secure them, we can protect those cells.
a. Select the cells which are required for Data Entry - A3 to F200.
b. Right click the selection and select Format Cells.
c. Go to Protection tab.
d. Uncheck the Locked option.
e. Click OK button.
Similarly do for the Income value - I2.
Now go to Review tab > Changes group > Click on Protect Sheet. Click OK button.
As you can see, the blue colored cells cannot be changed or deleted.
COPYING the Jan month to Other months:
45. Right click on Jan sheet name and select Move or Copy option.
46. On the Move or Copy dialog, select the following:
To book: (leave the default)
Before sheet: Instructions
Create a copy: check it
Click on OK button
47. Rename the "Jan (2)" to Feb.
48. Change the dates in cell Y2 and Z2 to reflect for February month.
cell Y2 = =Instructions!D3
cell Z2 = =Instructions!E3
Note: Unprotect the Sheet to enable editing. Protect Sheet after editing is completed.
Delete the sample Data created in Feb sheet.
Tip: Remember to save your work often using Ctrl+S keys.
49. Repeat the above 4 steps for the remaining 10 months.
(You can COPY the Feb sheet instead of Jan sheet, as the Data is removed in Feb sheet)
Creating Yearly Summary sheet:
50. Rename Sheet3 to "Yearly Summary"
51. Move the sheet before the Instructions sheet by simply holding the sheet1 with mouse (left click) and dragging it before the Instructions sheet.
52. Type the following in the respective cells:
A2 = Income
A3 = Expenses
A4 = Surplus
A5 = % Surplus
A6 = Expenses Categorization
A7 = Instructions!A2
Select A7 and COPY the value using Ctrl+C. Click cell A8 and select till A21 using Shift+Down arrow key. PASTE using Ctrl+V.
Note: You can hide the rows for Type14 and Type15 for now and unhide them when required.
For hiding, select the Rows by Right clicking on the Row Number which gives a number of options. Select the Hide option.
Multiple adjacent rows can be selected by left clicking the row number and dragging the mouse to the adjacent row numbers.
For unhiding, Select the 2 Rows where the the hidden row is present (check for the missing row numbers). Right click on the Row Number which gives a number of options. Select the Unhide option.
53. On the Top Row, type the Month names from cell B1. Ex:
B1 = January
M1 = December
54. Type the following formula to get details from the Monthly sheets:
cell B2 = Jan!I2
Similarly, get the income values from all monthly sheets:
cell C2 = Feb!I2
OR
put an "=" in the cell, go to the respective month sheet and select the Total Income value and click on Enter Key.
55. Select the values from cell B2 to cell M2 and COPY. Select the cells B3 to B5 and PASTE the values.
Select cells B5 to M5 and make them as Percentage (Number group > %)
56. In cell B7, get the value of Housing total from Jan month:
=Jan!I10
Similarly, get the Housing values from all monthly sheets:
cell C7 = Feb!I10
57. Select the values from cell B7 to cell M7 and COPY. Select the cells from B8 to B21 and PASTE the values.
Include Bank Account Month End Balances (Optional):
58. Type in cell A22, "Bank Month End Balances"
59. From A23, type in the name of Bank Accounts
A23 = IC Bank
Till number of accounts.
--------------Video Part-3 Ends--------------
Insert a Column Chart for % Surplus
60. Go to Insert tab > Charts group > 2-D Column Clustered Column.
61. Right click the Chart Area and select "Select Data"
62. In the Chart Data Range, click on the red icon. Select cell B1 to M1. Place a comma (,) and select cell B5 to M5. Click again on the red icon to close the dialog.
Click OK button.
The first is the X Axis and the second one after comma is the Y Axis.
63. Change the Chart Layout to Layout2 (which doesn't have the between lines and has percentage on top of each column and a Chart Title)
64. Click on the Series1 legend, click Delete Key
65. Click on the Chart Title and rename to "% Surplus"
66. Move the Chart to below the Bank Account Balances. Maximize the Chart by expanding the corners.
67. You can create similar charts for other important fields like Savings, etc.
Tip: Remember to save your work often using Ctrl+S keys.
Formatting the Yearly Summary sheet:
68. Select B1 to M1 and fill with Light Blue color. Bold the selection. Select A2 to A21 and fill with Light Blue color. Bold the selection.
Fill cells A6 to M6 and A22 to M22 with Yellow color as they are Headings.
If there are Bank Accounts, fill the A23 to A26 with Light Green color. Bold the selection.
69. Autofit Column A. Adjust the Column width of B to M to 10.00. You can also select all the columns by left clicking on column B header and holding the mouse till Column M. Adjust the width of any one column to 10.00 and it applies to all the selected columns.
70. Select B1 to M26 and apply Center Text.
71.. Select A1 to M26. Apply All Borders.
--------------Video Part-4 Ends--------------
There are few Instructions to maintain the template. These can be written in the Instructions Sheet for Quick Reference.
At the start of Year, Unprotect Sheet (Review->Click Unprotect Sheet) select the Start Date and End Date columns and Replace 2013 with the current year (Ex: 2014) till Row 13
Click on Protect Sheet again.
While Creating for a new year, clear off values (including total income in all Month Sheets)
Date 1-Jan <d>-<Mon>
Amount Round off value to integers. If <0.5, enter the number else if, > or =0.5, enter the next integer
Entries are only calculated till row 200 for each sheet
Enter Total Income. If multiple incomes, keep a "=" and then enter the amounts separated by "+" and click on Enter button
Savings are shown only if Expenses is less than Total Income
If additional Type is added, make the changes in the following areas:
Expense Type name on this sheet
Pie Chart Data on Every Month Sheet
Unhide the Expense Type rows in Yearly Summary sheet
For Credit Card Enter the amount normally. But when making payment enter amount in comments section and category as Debt Payments
You incur an expense when you buy it, not when you pay credit card bill.
Here is the Template for your quick reference.
Additional Resources for learning Excel:
chandoo.org - Beginner, By Topic
Tech On The Net
ExcelIsFun - YouTube channel - Excel Basic Series
Hope you enjoyed making your own Expense Tracker and are ready for doing wonders with Excel !!!
Tips:
1. Shortcuts:
Save = Ctrl+S
Copy = Ctrl+C
Paste = Ctrl+V
Bold = Ctrl+B
Go to End of Data = Ctrl+End
Select till End of Data = Ctrl+Shift+End
2. Hit Enter or Tab Key after using references to other cells(in same sheet or different sheet) or formula
3. Functions used in this Template:
SUM
IF
ISBLANK
SUMIF
SUBTOTAL
If you are unable to follow the steps, please have a look at the Video which is a screen recording of the steps.
Please note that the Videos are just Screen Recording and doesn't include any audio but includes text for guidance wherever required.
Part-1
Part-2
Part-3
Part-4
Preparing Excel Workbook:
1. Open a New Microsoft Excel Workbook. I am using Office 2007
2. Rename “Sheet1” as “Jan” and “Sheet2” as “Instructions”. This can be done by Right clicking on the Sheet tab and selecting Rename. The Sheet name becomes editable. After renaming, click any cell on the sheet which saves the new name.
Save the file as Name=Yearly Expenses_2017_Template and Type=.xlsx
OR
Name = Personal Expense Tracker_YYYY_Template
If the file is shown in Compatibility mode, close the file and open it again. Read here.
Defining Categories:
3. On the Instructions sheet, mention the Expenses Type you would like to have. I have considered the following Types:
Housing
Utilities
Groceries
Living
Transportation
Health Care
Personal
Eating Out
Entertainment
Debt Payments
Travel
Children
Savings
Type14
Type15
Type14,15 are for future purposes.
You can define what comes under each of the Expense Type. My classification is as below which can be kept in the Instructions sheet for quick reference.
Housing - Rent, Loan, Property Tax, House Insurances
Utilities - Phone/Cell, Cable/Internet, Gas, Electricity
Groceries - Food (Vegetables/Fruits/Biscuits/Chocolates), Household Supplies, Toileteries
Living - Personal Care (Salon/Cosmetics/Beauty), Maid, Bank Fees, Dry Cleaning, Pet Costs, Clothing, Daily wear ornaments, Luggage Bags, Footwear, Kitchenware
Transportation - Fuel, Auto Insurance, Parking, Taxi, Services, Pollution Certificate, Tolls (within Residence)
Health Care - Medical Premiums, Life Insurance (without returns), Medication, Eye Care, Dental, Wellness Costs
Personal - Books/CDs, Donations, Gifts, Subscriptions, Electronics, Parties, Jewellery
Eating Out - Meals, Snacks, Tea/Coffee, Juices
Entertainment - Recreation, Sports Equipment/Fees, Movies/Tickets, Hobbies, Memberships (Fitness, etc.), Holidays
Debt Payments - Loans, Credit Cards, Leases, Government Debts
Travel - Car/Bus/Train/Flight to places outside of Residence, Tolls (Travelling outside of Residence)
Children - Day Care, Lessons/Activities, Allowance, School Supplies/Fees, Babysitting
Savings - Deposits, PF, Chit, Mutual Funds, Shares, Insurance (with returns), Emergency Fund
4. You can Autofit the column by double clicking on column header border.
Bold the “Expense Type” and fill it with Light Blue color. This is the Formatting Step.
5. Leave a column and enter the date ranges for each month. Below are the Column Headers and Sample format for January:
Month Start Date End Date
January 1-Jan-17 31-Jan-17
6. Repeat the Formatting step for all the 3 columns.
7. Leave a column and enter the Transaction Type:
Cash
IC Debit
HD Debit
Credit Card
Bank1
Bank2
8. Repeat the Formatting step for the column.
Adding Columns to Monthly sheet:
9. Jump to the "Jan" sheet.
10. From Row2, enter the following Column Headers:
Date
Description
Expense Type
Amount
Comments
Transaction Type
Tip: You can use "Tab" Key to go to the next column.
10. Mouse over the column header border where you can see an icon for adjusting the width of the column on the left. On the column header after seeing the icon, left click the Mouse and drag the column to the required width.
Following are the width set and you can customize as per your preference:
Date = 10.00
Description = 30.00
Expense Type = 15.00
Amount = 10.00
Comments = 30.00
Transaction Type = 15.00
11. Format the column headers.
Select the Column Headers. This can be done in the following way:
a. Click on the first column Header (cell A2 = Date).
b. Hold the Shift Key and Press the Right Arrow till the last column Header(cell F2 = Transaction Type).
c. As can be seen all column headers are selected.
12. Click on the Center Text icon. Fill it with Light Green color
13. Go to the Column Y. Enter Column Headers for Start End Dates
Y1 = Start Date
Z1 = End Date
Enter the following so that we can get the values of January range:
In cell Y2, enter as "=Instructions!D2" and TAB OR
Put an "=" (equals symbol) in cell Y2, click on the Instructions sheet, click on the Start Date for January month. Hit Enter Key.
Similary enter the End Date in cell Z2 as "=Instructions!E2"
Keeping Validations for Column values:
14. Click on the cell A3 (below the Date header).
a. Go to Data tab, in the Data Tools group, click Data Validation.
b. On the Settings tab, in the Allow box, select Date.
c. Let the Ignore blank be default (checked)
d. Let the Data value be default = between
e. To the right of the Start date box, click on the button (red-arrowed) to collapse the dialog box, and then select the Start Date value (cell Y2).
Make the cell values as Absolute references. Put a dollar symbol ($) before the column and the row like $Y$2. Refer this page for more information.
f. Click the button (red arrowed) to expand the dialog box.
g. Repeat the step for entering the End Date value (cell Z2)
h. Click on the Error Alert tab, let the checkbox be checked, Style = Stop
Enter the following:
Title = Error
Error message: Enter dates for the month (sheet selected)
i. Click on OK button.
15. In cell H10, enter the following to COPY the Expense Type Categories:
"=Instructions!A2"
Click on Enter Key or TAB to go to another cell.
Click again on cell H10. COPY the cell via Ctrl+C keys
Select the cells from H11 to H24 by clicking on the cell H11 and pressing Shift+Down Arrow.
Use Ctrl+V to PASTE the values.
16. In cell H31, enter the following to COPY the Expense Type Categories:
"=Instructions!G2"
Click on Enter Key or TAB to go to another cell.
Click again on cell H31. COPY the cell via Ctrl+C keys
Select the cells from H31 to H36 by clicking on the cell H31 and pressing Shift+Down Arrow.
Use Ctrl+V to PASTE the values.
Tip: Remember to save your work often using Ctrl+S keys.
17. Click on the cell C3 (below the Expense Type header).
a. Go to Data tab, in the Data Tools group, click Data Validation.
b. On the Settings tab, in the Allow box, select List.
c. Let the Ignore blank, In-cell dropdown be default (checked) ,
d. To the right of the Source box, click on the button (red-arrowed) to collapse the dialog box, and then select the first Expense Type value (cell H10).
Make the cell values as Absolute references like $H$10. Hold the Shift Key and Press the Down arrow key till the last Expense type. Now the range of cells is selected which should be as "=$H$10:$H$24"
Ensure that Absolute references are used.
e. Click the button (red arrowed) to expand the dialog box.
f. Click on OK button.
18. Click on the cell D3 (below the Amount header).
a. Go to Data tab, in the Data Tools group, click Data Validation.
b. On the Settings tab, in the Allow box, select Whole number.
c. Let the Ignore blank be default (checked)
d. Enter the following:
Data = greater than
Minimum = 0
e. Click on OK button.
19. Click on the cell F3 (below the Transaction Type header).
a. Go to Data tab, in the Data Tools group, click Data Validation.
b. On the Settings tab, in the Allow box, select List.
c. Let the Ignore blank, In-cell dropdown be default (checked) ,
d. To the right of the Source box, click on the button (red-arrowed) to collapse the dialog box, and then select the first Transactio Type value (cell H31).
Make the cell values as Absolute references like $H$31. Hold the Shift Key and Press the Down arrow key till the last Transaction type. Now the range of cells is selected which should be as "=$H$31:$H$36"
Ensure that Absolute references are used.
e. Click the button (red arrowed) to expand the dialog box.
f. Click on OK button.
--------------Video Part-1 Ends--------------
20. COPY the validations of 3rd row to other rows.
a. Click on the first cell in Row3 (A3) where the validation was applied.
b. Hold the Shift Key and Press the Right Arrow till the last column of Row3(F3).
c. As can be seen all cells are selected.
d. COPY the selection using Ctrl+C keys.
e. Apply this to the remaining rows. Click on the first column of 4th row (A4) and press Shift+Down arrow key till the last row required. (I am taking 200 rows)
f. PASTE the values using Ctrl+V keys.
g. Observe that all the validations are copied till 200 rows for the 6 columns.
Tip: Remember to save your work often using Ctrl+S keys.
Entering other fields for Monthly sheet:
21. In cell H2, enter the "Total Income". Bold the text using Ctrl+B or using the Font group > B.
Cell I2 (beside Total Income) serves as input for all the monthly income like Salary, per diems, shift allowances, etc. You can keep a "=" and then using "+" sign to add additional incomes.
Ex: =50000+4000+250
Click on Enter or Tab key after entering the values.
22. In cell H3, enter the "Expenses". Bold the text using Ctrl+B or using the Font group > B.
Cell I3 (beside Expenses), write the following formula to calculate the sum of all amounts in column D. Note that we have considered only till 200 rows.
=SUM($D$3:$D$200)
Hit Enter or Tab key after entering the formula.
Refer this page for more information on SUM formula.
23. In cell H4, enter the "Surplus". Bold the text using Ctrl+B or using the Font group > B.
In cell I4 (beside Surplus), write the following formula to get the difference of Income and Expenses.
You can keep an "=" in the cell I4, select cell I2, put a "-", select cell I3 and press Enter Key.
Ensure to convert the cell references into Absolute Reference.
OR you can directly write the formula in cell I4:
=$I$2-$I$3
But there is a catch here. Sometimes the Expenses might exceed the Total Income, in which case we might get a negative Surplus amount. To handle such cases, we stop the Surplus at 0, if it goes to negative values.
We use the following IF function:
=IF(($I$2-$I$3)<0,0,($I$2-$I$3))
Hit Enter or Tab key after entering the formula.
If the difference is less than zero, we enter 0, otherwise we enter the difference.
Refer this page for more information n IF statement.
24. In cell H5, enter the "% Surplus". Bold the text using Ctrl+B or using the Font group > B.
In cell I5, write the following formula to get the percentage surplus of Total income
You can keep an "=" in the cell I5, select cell I4, put a "/", select cell I2 and press Enter Key.
Ensure to convert the cell references into Absolute Reference.
OR you can directly write the formula in cell I5:
==$I$4/$I$2
But, we do not have Total Income for all months initially. So the division may give Division by zero error. To eliminate this issue, we use the following IF function:
=IF(ISBLANK($I$2),0,$I$4/$I$2)
ISBLANK checks if a cell is empty or not.
If the Total Income is BLANK, we enter 0, otherwise do the division.
Refer this page for more information on ISBLANK function.
This value needs to be converted to a %. So, we select the cell I5, go to Home tab > Number group, click on the % icon.
You can check by entering any amount in the Amount column (say, 56) and Total Income (Say, 123). The %Surplus is shown as 54%
Calculating Category Wise Expenses:
25. We compare the Expense Type in cell H10 against the Expense Type column (Column C) and then we sum the Amount column (Column D)for those rows where the Expense Type is matching with cell H10.
Write the below formula in cell I10:
=SUMIF($C$3:$C$200,H10,$D$3:$D$200)
Notice that the above formula has H10 as Relative reference and not as Absolute reference as we are going to COPY this to the remaining Expense Types.
Refer this page for more information on SUMIF function
Select cell I10 and COPY using Ctrl+C. Click on cell I11 and press Shift+Down arrow keys till the last Expense Type. Now PASTE the formula using Ctrl+V.
Tip: Remember to save your work often using Ctrl+S keys.
Calculating Transaction Wise Expenses:
26. We compare the Transaction Type in cell H31 against the Transaction Type column (Column F) and then we sum the Amount column (Column D)for those rows where the Transaction Type is matching with cell H31.
=SUMIF($F$3:$F$200,H31,$D$3:$D$200)
Notice that the above formula has H31 as Relative reference and not as Absolute reference as we are going to COPY this to the remaining Transaction Types.
Select cell I31 and COPY using Ctrl+C. Click on cell I32 and press Shift+Down arrow keys till the last Expense Type. Now PASTE the formula using Ctrl+V.
Formatting the Columns H and I :
27. Autofit Column H by clicking on the Column H header border.
28. Cell H2 filled with Light Green color as we are expecting an Input in cell I2.
29. For all the remaining cells of H and I, fill the field names and values with Light Blue.
You can select multiple cells by clicking on the first cell and then dragging your mouse till the last cell.
Note: Fill the color only for rows which have text and are expecting values.
30. Ensure that all fields and values in Columns H and I have borders.
For Ex: Select cells H2 to cell I4. Go to Home tab > Font group > Click on Borders dropdown and select "All Borders"
Formatting the Data Columns A to G:
31. Select the cells A2 to F200. Apply All Borders to the selection.
32. Select Column B (by clicking on the Column Header). The whole Column gets selected. Go to Home tab > Alignment group > click Wrap Text. This ensures that any lengthy text is displayed completely.
Repeat Wrap Text for Column E.
33. Select Column D and go to Alignment group > click on Center Text.
34. Apply Filters for Column Headers in Row2, A to F
Select the Column Headers A2 to F2. In the Editing group > click on Sort & Filter > click on Filter.
Bold the Column Headers.
35. We may sometimes want to see the Amount of only the Filtered transactions. For this purpose, we use Subtotal function. Write the below function above the Amount column header i.e. cell D1
=SUBTOTAL(9,$D$3:$D$200)
9 is the function number that we are doing SUM of the Filtered data from D3 to D200.
Refer this page for more details on SUBTOTAL function.
36. Label this value as Filtered Amount in cell E1.
--------------Video Part-2 Ends--------------
37. Fill the cell D1 (value) with Light Blue color.
Pie Chart - Representing the Expenses in terms of percentage of each Expense Type:
38. We want to draw Pie Chart for the Expense Type. So we select cell H10 to cell I24.
39. Go to Insert tab > Charts group > click Pie > select the simple 2-D Pie.
40. Drag the Chart to an empty area ( say to the Right of Column K). You can see a + icon with arrows on all 4 sides indicating the movement of the chart.
Ensure that you are clicking on the empty space and NOT the Chart or the Legend.
You can also Expand the Chart by enlarging the Corners.
41. We can select the Chart Layout. Go to Chart Tools (this is activated when a chart is selected) > Chart Layouts group > click Layout1 (which has Chart heading and % within the chart and No Legend)
42. Click on the "Chart Title" which makes it Editable. Write it as Expenses.
43. You can change the color of the Chart in the Chart Styles.
Note: Suppose if you do not want the Expense Type 14,15 not to show in the Pie Chart, you can remove them from selection.
Select the Plot Area (where the pie is) > Right Click > Select Data. In that change the Chart data range to the required row. Click on OK button to save the Changes.
In future, if you had any new category defined for Type14 or 15 and want to include them in the Chart, you need to change manually for each month sheet.
Protecting the Excel Formulas (Optional):
44. If you feel that there are chances of editing the formulas unintentionally and feel a need to secure them, we can protect those cells.
a. Select the cells which are required for Data Entry - A3 to F200.
b. Right click the selection and select Format Cells.
c. Go to Protection tab.
d. Uncheck the Locked option.
e. Click OK button.
Similarly do for the Income value - I2.
Now go to Review tab > Changes group > Click on Protect Sheet. Click OK button.
As you can see, the blue colored cells cannot be changed or deleted.
COPYING the Jan month to Other months:
45. Right click on Jan sheet name and select Move or Copy option.
46. On the Move or Copy dialog, select the following:
To book: (leave the default)
Before sheet: Instructions
Create a copy: check it
Click on OK button
47. Rename the "Jan (2)" to Feb.
48. Change the dates in cell Y2 and Z2 to reflect for February month.
cell Y2 = =Instructions!D3
cell Z2 = =Instructions!E3
Note: Unprotect the Sheet to enable editing. Protect Sheet after editing is completed.
Delete the sample Data created in Feb sheet.
Tip: Remember to save your work often using Ctrl+S keys.
49. Repeat the above 4 steps for the remaining 10 months.
(You can COPY the Feb sheet instead of Jan sheet, as the Data is removed in Feb sheet)
Creating Yearly Summary sheet:
50. Rename Sheet3 to "Yearly Summary"
51. Move the sheet before the Instructions sheet by simply holding the sheet1 with mouse (left click) and dragging it before the Instructions sheet.
52. Type the following in the respective cells:
A2 = Income
A3 = Expenses
A4 = Surplus
A5 = % Surplus
A6 = Expenses Categorization
A7 = Instructions!A2
Select A7 and COPY the value using Ctrl+C. Click cell A8 and select till A21 using Shift+Down arrow key. PASTE using Ctrl+V.
Note: You can hide the rows for Type14 and Type15 for now and unhide them when required.
For hiding, select the Rows by Right clicking on the Row Number which gives a number of options. Select the Hide option.
Multiple adjacent rows can be selected by left clicking the row number and dragging the mouse to the adjacent row numbers.
For unhiding, Select the 2 Rows where the the hidden row is present (check for the missing row numbers). Right click on the Row Number which gives a number of options. Select the Unhide option.
53. On the Top Row, type the Month names from cell B1. Ex:
B1 = January
M1 = December
54. Type the following formula to get details from the Monthly sheets:
cell B2 = Jan!I2
Similarly, get the income values from all monthly sheets:
cell C2 = Feb!I2
OR
put an "=" in the cell, go to the respective month sheet and select the Total Income value and click on Enter Key.
55. Select the values from cell B2 to cell M2 and COPY. Select the cells B3 to B5 and PASTE the values.
Select cells B5 to M5 and make them as Percentage (Number group > %)
56. In cell B7, get the value of Housing total from Jan month:
=Jan!I10
Similarly, get the Housing values from all monthly sheets:
cell C7 = Feb!I10
57. Select the values from cell B7 to cell M7 and COPY. Select the cells from B8 to B21 and PASTE the values.
Include Bank Account Month End Balances (Optional):
58. Type in cell A22, "Bank Month End Balances"
59. From A23, type in the name of Bank Accounts
A23 = IC Bank
Till number of accounts.
--------------Video Part-3 Ends--------------
Insert a Column Chart for % Surplus
60. Go to Insert tab > Charts group > 2-D Column Clustered Column.
61. Right click the Chart Area and select "Select Data"
62. In the Chart Data Range, click on the red icon. Select cell B1 to M1. Place a comma (,) and select cell B5 to M5. Click again on the red icon to close the dialog.
Click OK button.
The first is the X Axis and the second one after comma is the Y Axis.
63. Change the Chart Layout to Layout2 (which doesn't have the between lines and has percentage on top of each column and a Chart Title)
64. Click on the Series1 legend, click Delete Key
65. Click on the Chart Title and rename to "% Surplus"
66. Move the Chart to below the Bank Account Balances. Maximize the Chart by expanding the corners.
67. You can create similar charts for other important fields like Savings, etc.
Tip: Remember to save your work often using Ctrl+S keys.
Formatting the Yearly Summary sheet:
68. Select B1 to M1 and fill with Light Blue color. Bold the selection. Select A2 to A21 and fill with Light Blue color. Bold the selection.
Fill cells A6 to M6 and A22 to M22 with Yellow color as they are Headings.
If there are Bank Accounts, fill the A23 to A26 with Light Green color. Bold the selection.
69. Autofit Column A. Adjust the Column width of B to M to 10.00. You can also select all the columns by left clicking on column B header and holding the mouse till Column M. Adjust the width of any one column to 10.00 and it applies to all the selected columns.
70. Select B1 to M26 and apply Center Text.
71.. Select A1 to M26. Apply All Borders.
Your Expense Tracker Template is ready!!! Fill it by giving values in Monthly sheets and checking it.
--------------Video Part-4 Ends--------------
There are few Instructions to maintain the template. These can be written in the Instructions Sheet for Quick Reference.
At the start of Year, Unprotect Sheet (Review->Click Unprotect Sheet) select the Start Date and End Date columns and Replace 2013 with the current year (Ex: 2014) till Row 13
Click on Protect Sheet again.
While Creating for a new year, clear off values (including total income in all Month Sheets)
Date 1-Jan <d>-<Mon>
Amount Round off value to integers. If <0.5, enter the number else if, > or =0.5, enter the next integer
Entries are only calculated till row 200 for each sheet
Enter Total Income. If multiple incomes, keep a "=" and then enter the amounts separated by "+" and click on Enter button
Savings are shown only if Expenses is less than Total Income
If additional Type is added, make the changes in the following areas:
Expense Type name on this sheet
Pie Chart Data on Every Month Sheet
Unhide the Expense Type rows in Yearly Summary sheet
For Credit Card Enter the amount normally. But when making payment enter amount in comments section and category as Debt Payments
You incur an expense when you buy it, not when you pay credit card bill.
Here is the Template for your quick reference.
Additional Resources for learning Excel:
chandoo.org - Beginner, By Topic
Tech On The Net
ExcelIsFun - YouTube channel - Excel Basic Series
Hope you enjoyed making your own Expense Tracker and are ready for doing wonders with Excel !!!
No comments:
Post a Comment