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 do an expense control in Excel.
Well, as this is probably the best way to save, in the following lines we are going to show you all the details that will allow you to control expenses in Excel thanks to its dynamic tables, categorizing expenses so as not to lose sight of anything, that you do not miss payments and, that you 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 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 is the 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, establishing Suma as a choice in each Securities section.
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 lost money without justification.
Article Summary
In short, although the exports of different financial institutions are likely to be different from each other, basically the steps you have to follow are the same, and then we summarize them for you:
- 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 do not miss expenses or lose money.
Another idea, as a complement, is to create graphs in Excel to illustrate your expenses and see them more quickly.
