➥ Go to the start of the free online Excel Course
It is true that, at present, many banks and financial applications allow us to control our money expenditures, in order to know all the details of the personal or household economy. However, there are those who prefer more traditional methods: the spreadsheet on the computer. If you are one of those people, you may have ever wondered how to track expenses in Excel ..
But as it is probably the best way to save in the following lines we will teach you all the details that will make you control expenses in Excel thanks to its dynamic tables, categorizing expenses to keep anything of view, not you miss payments and save a little each month .
The first thing to consider in these cases, however, is that you are going to need a little help from your trusted bank so as not to take too long. You see, many of them, and also credit or debit card companies, offer expense reports, which users can download whenever we want ..
You can always fill in expenses by hand, but with a full report, everything will go more smoothly.
Make an expense control in Excel step by step
The first thing you have to do, as we mentioned before, is to enter your bank or credit card account, find your expense summary, and export it. The easiest thing is to do it with a monthly cut, although you can probably select the date range as you consider most appropriate for you..
When storing that file on your computer, you must choose an Excel format: XLS or XLSX .
Already downloaded and stored, you have to open it, and you are going to create two columns, one related to the expense categories, and others for the month, or the interval that you have previously decided, and applied to the bank report.
Next, you must enter the formula = month () in the Month column, with its corresponding cell or cells. Later you must paste the formula in all the rows of data, according to the description of each expense.
Moving to a pivot table
Now it's time to create a pivot table from Insert, PivotTable, PivotTable, creating a New Worksheet. This step is essential for your Excel expense control to work.
Once this is done, you have to drag the Type field from the Field List of the pivot table to the Row Labels section. Dragging Month to the Columns section, and Debit, Credit, etc. to the Values section.
Unless your default settings are configured differently, you will notice that the table is counting the items instead of showing the dollar amount, but you can change that from Debit Count, and Value Field Settings, setting Sum as choice in Securities section house .
With that you will get the dollar amount to be displayed, and as you add other expenses, the same will happen with those that are added. This will allow you to detect strange charges or loss of money without justification.
Article Summary
In summary, although it is likely that the report exports different financial institutions are different from each other, basically the steps you have to follow are the same, and we will summarize them for you below:
- Export the financial report
- Save them in Excel, XLS or XLSX format
- Add a column for the type of purchase and the month
- Create a month formula, and copy it to all the rows of the sheet
- Assign each purchase a "type" or "category"
- Align the categories to your budget
- Create a pivot table on the sheet
- Investigate or cancel all purchases you don't recognize
- Develop an expense forecast for more control
That is all. Under normal conditions, it should be enough so that you don't miss out on expenses or lose money .
Another idea, as a complement, is to create graphs in Excel to illustrate your expenses and see them more quickly.
