This post was first published at Opensource.com and appears here via a CC-BY-SA 4.0 license.
If you’re like most people, you don’t have a bottomless bank account. You probably need to watch your monthly spending carefully.
There are a number of ways to do that, but that quickest and easiest way is to use a spreadsheet. Many folks create a very basic spreadsheet to do the job, one that’s consists of two long columns with a total at the bottom. That works, but it’s kind of blah.
In this article, I’m going to walk you through creating a more scannable and, I think, more visually-appealing personal expense spreadsheet using LibreOffice Calc.
Say you don’t use LibreOffice? That’s OK. You can use the information in this article with spreadsheet editors like Gnumeric, Calligra Sheets, or EtherCalc.
Start with a List of Your Expenses
Don’t bother firing up LibreOffice Calc just yet. Sit down with pen and paper and list your regular monthly expenses. Take your time, go through your records, and note everything down no matter how small. Don’t worry about how much you’re spending. Focus on where you’re putting your money.
Once you’ve done that, group your expenses under headings that make the most sense to you. For example, group your gas, electric, and water bills under the heading Utilities. You might also want to have a group of expenses with a name like Various for those unexpected expenses we all run into each month.
Creating the Spreadsheet
Start LibreOffice Calc and create an empty spreadsheet. Leave three rows at the top of the spreadsheet blank. We’ll come back to those rows later.
There’s a reason I had you group your expenses: those groups will become blocks on the spreadsheet. Let’s start by putting the group of your most important expenses at the top of the spreadsheet.
Type the name of the expense group in the first cell of the fourth row from the top of sheet. So it stands out, make it bold and in a larger font (12 points is good).
In the row below the heading you just typed, add the following three columns:
Type the the names of the expenses in the group into the cells under the Expense column.
Next, select the cells under the Date heading. Click the Format menu and select Number Format > Date. Repeat that for the cells under the Amount heading, and choose Currency instead of Date.
You’ll have something that looks like this:
That’s one group of expenses out of the way. Instead of creating a new block for each group of your expenses, copy what you created and paste it beside the first block. I recommend having rows of three blocks, with a column in between them.
You’ll have something like this:
Repeat that for all of your groups of expenses.
Totaling It All Up
It’s one thing to see all of your individual expenses, but you’ll also want to view totals for each group of expenses and for all of your expenses together.
Let’s start by totaling the amounts for each expense. You can get LibreOffice Calc to do that automatically. Highlight a cell at the bottom of the Amount column and then click the Sum button on the Formula toolbar.
Click the first cell in the Amount column and drag the cursor to the last cell in the column. Then, press Enter.
Remember when I mentioned leaving two or three rows at the top of the sheet blank? That’s where you’ll put the grand total of all your expenses. I advise putting it up there so it’s visible when you first open the spreadsheet.
In one of the cells at the top left of the sheet, type something like Grand Total or Total for the Month. Then, in the cell beside it, type =SUM(). That’s the LibreOffice Calc function which adds up the values of specific cells on a spreadsheet.
Instead of manually entering the names of the cells to add up, press and hold CTRL on your keyboard. Then click the totaled cells for each group of expenses on your spreadsheet.
You have a sheet for a tracking a month’s expenses. Having a spreadsheet for a single month’s expenses is a bit of a waste. Why not use it to track your expenses for each month of the year instead?
Right click on the tab at the bottom of the spreadsheet and select Move or Copy Sheet. In the window that displays, click -move to end position- and press Enter. Repeat that until you have 12 sheets — one for each month. Rename each sheet for each month of the year, then save the spreadsheet with a descriptive name like Monthly Expenses 2021.ods.
Now that’s out of the way, you’re ready to use the spreadsheet. While using a spreadsheet to track your expenses won’t, by itself, put you on firmer financial footing, it can help you keep on top of and control what you’re spending each month.