How to Create an Expense Tracker in Excel? – GeeksforGeeks

Finance management plays an crucial function in an organization or in the personal finance of people ’ south life. In order to manage finances organizations have a finance director, in the lapp way individual person can manage their own expenses using an expense coach. Expense Manager, besides known as Expense Tracker is an application or software which is used to keep the records of the inflow and outflow of money. It is used to manage your daily expenses and keep the track of “ How much you spend, Over what you have spent”. For example , In this case we will make an expense tracker using Microsoft Excel, which will automate on the basis of what is entered in the tracker, it will besides show how much money is left in each category and will change on the basis of the month selected in the drop-down menu.

Implementation Steps

Step 1: First, we will open the Microsoft Excel application, and we will define the be column Month, Date, Description, Category, Income, Debits, Balance. You can define your own column as per your requirements .Defining-columns now, we will turn these columns into tables with all alternating rows. For this, we will select all the column and go the Format as Table dash inside the Home tab of excel and choose any one of the table formatting views you like. Below is the screenshot attached for the same .

Home > Format as table

Format-as-table once you have selected your table view design a immediate will come there you have to check the checkbox “ My table has headers ”, which will create the column of the table as header the header of the table. Click on the OK button .Defining-data-for-table After, you click the OK clitoris tables with headers will be created. And, you will get the table with your columns header .Tables-with-headers-created Step 2: In this footprint, we will add the necessity formula and the number format to the column of the table to automate most of the work for us. first, we will add starting date, and using that we will add up the rule to our postpone. For this first, we will add a random date in the go steady column and hit the figure clitoris .Date-column After this, we will change the date format. You can use any format you like, in orderliness to do then, we will select the date column ( here, it is column B ) and right-click on it and open format cells, there we will go to the date category and choose a date format .

column B > Right-click > Format Cells > Date > Type

Formatting-data-options now, in the Format Cells check, we will select the Category as Date and choose a Type of date .Data-format-cells next, we will set up the formula in the Month column to pull only the calendar month number from the Date column. In order to do this we will write = MONTH([@Date]) inside the month column. The formula will auto-fill the rest of the calendar month rows with the calendar month act from the date column. Whenever we will enter a new date in the date column, it will auto-update the calendar month number according to the date .

=MONTH ( [ @ Date ] )

Auto-month-filled Step 3: Next, we will add a drop-down menu over the category column therefore, we can choose a class of expense. For this, we will select the whole category column and then hold the ctrl. key and unselect the column header then from the Data Tab we will select Data validation which is present inside the Data Tools .Category-column once you click over the Data Validation option it will open a Data Validation Tab, there inside Validation criteria, in Allow segment you have to select List, which is used to create a drop-down list and the list menu should be entered inside the Source section with comma separated the excel will create the number for us .Data-validation-prompt You can add the menu tilt according to your own expenses. here, we will add Income, Mortgage, Utilities, Groceries, Fuel Chills & hangouts. After adding snap on the OK button, Excel will create the drop-down number for all the selected cells and the drop-down menu will be created .Menu-categories After this, we will format the Income, Debits, and Balance columns as currentness column. In orderliness to do so, first, we will select these columns then inside the Home tab key we will go to the number section and select the currentness, whatever currency you want. In this character, we will choose indian Rupees currency .Income-debit-balance-columns Step 4: In this measure, we will add up the convention to get the track balance. For this, we need to add a start balance to our expense tracker application. We will add 10000 rupees as Balance and define the Description for this as our starting balance and then we will define the formula to get the run balance .Starting-balance now, we will define the convention in the future cell below symmetry ( cell G3 ) to automate the hunt poise. The recipe that will be used is =SUM(G2+[@Income]-[@Debits]) .

=SUM ( G2+ [ @ Income ] – [ @ Debits ] )

Formulae-for-running-balance We have to add the formula in the hale column, for this, we will first select the column G3 and press the Ctrl+C key to copy the formula then we will select whole column G and unselect the header and the starting balance cells from column G ( here, it is cellular telephone G1 & G2 ) while holding the Ctrl key and then paste the formula using Ctrl+V key, excel will paste it in the whole column. Once we will paste it, the excel will auto-populate the starting poise of 10000 rupees in the unharmed of column G like as the month column, But as you update your expenses and income it will calculate the operate remainder .Auto-updating-balance Step 5: In this footfall, we will create the progress measure which will show how much we have spent in each class and how much income we need in each category. In decree to do thus, we need to add all the categories in a distinguish column that we want to track .Tracking-columns Moving next, we need to add a Total column in cell J1 and add the calendar month number we want to focus on in column I1 ( hera, it ’ second January so we will add calendar month number a1 ) .Total-column-and-month-number After this, we need to add the SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) formula for all the column which we want to keep the track of. We need to write SUMIFS ( ) individually for the Income and Expenses column.

beginning, we write SUMIFS ( ) for the Income column to keep its track individually with expenses. We will be using the come formula. (Note: Please keep in mind that you will be using the formula according to the columns you have to define)

=SUMIFS ( Table1 [ [ # All ], [ Income ] ], Table1 [ [ # All ], [ Month ] ], I1, Table1 [ [ # All ], [ Category ] ], I2 )

SUMIFS()-for-balance immediately, we will add SUMIFS ( ) formula for our expenses categories. For expenses tracking we will be using the adopt formula .

=SUMIFS ( Table1 [ [ # All ], [ Debits ] ], Table1 [ [ # All ], [ Month ] ], $ I $ 1, Table1 [ [ # All ], [ Category ] ], I3 )

SUMIFS()-for-expenses-categories After adding this formula we will just drag and drop the formula to all our categories columns we want to track. i, Mortgage, Utilities, Groceries, Fuel, and Chills & hangouts. This will update all the expenses according to the months .Tracking-expenses We can see mortgages, Fuel, and Groceries are coming as 0 since we have not added any expenses in these categories. nowadays, to enhance it we will change the type of these tracing column to currencies ( here, In indian Rupees ). In order to do thus, we will select all these columns, and then in the Home tab, we will go to the Number section and change the type to currencies .Change-type-of-tracked-columns Step 6: In this step, we will design our advancement bar which will show us how much income we will have and how much we got left in each class to spend. Again we will design income individually and the expense categories individually. Progress bar for Income We will beginning select the column having income value. From the Home check, we will choose Conditional Formatting in the Style department. And, choice Data Bars, we will select green bars for the income .Progress-bars We will stretch it a little longer for good visuals. now, we need to define the rules for it. For this, we will again go to Conditional Formatting and then choose Manage Rules .Manager-rule-tab We need to update our existing predominate which is by default created by excel. We just double-click to open it and update the minimum and maximum value and set its type and number. This will make our progress bar traceable .Data-bar We will set the minimal value is 0 and the maximal value as 45000, after we update these values we will press OK and apply our rules .Managing-rules After this, we will be done with income and it will look something like this .Tracking-income The same this we have to do for all of our expenses individually. You can set up the minimum and maximal values according to your necessity. hera, we will be doing expenses tracking in loss semblance with minimal and maximum value as adopt mortgage ( 0, 8000 ), Utilities ( 0, 5000 ), Groceries ( 0, 5000 ), Fuel ( 0, 2000 ), Chills & Hangout ( 0, 3000 ). Once we repeat the lapp steps for our all-expense we will get something like this .Tracking-expenses Step 7: In this step, we will try to enhance our purpose. We add borders across our tracking table. For this, we will select the whole expense table and drag it down and make a column ( here, it is column H and column K ) to equivalent length. You can find it below in the picture .Tracking-table-border Moving ahead, we will select the solid table and makes All Borders. For this, we go to the Home check, and then in the Font section, we will choose the borders option and blue-ribbon All borders it will make borders all around our table .Tracking-table-all-borders once we do this we will select the cells around our surround which we left in the above gradation, for that we need to select those cells manually by pressing and holding the ctrl key and selecting the cells .Cells-selection once we select all the cells around our table we fill them with some solid tinge. For that, we go to the Home tab, and then in the Font section, we will fill the color .Filling-cells Output hera, we will test our Expense Manager Application. We fill some of the expenses in the month of January and then we will change the calendar month number in Progress Tracking Table and fill for the month of February .

My Personal Notes


beginning :
Category : Finance

Related Posts