Manage your finances with LibreOffice Calc

Do you wonder where all your money goes? This well-designed spreadsheet can answer that question at a glance.
669 readers like this.
A desk illustration in grass

Opensource.com

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 many 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 consists of two long columns with a total at the bottom. That works, but it's kind of blah.

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 tools like Gnumeric, Calligra Sheets, or EtherCalc.

Start by making 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, 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.

Create the spreadsheet

Start LibreOffice Calc and create an empty spreadsheet. Leave three blank rows at the top of the spreadsheet. We'll come back to them.

There's a reason you grouped your expenses: Those groups will become blocks on the spreadsheet. Let's start by putting your most important expense group (e.g., Home) at the top of the spreadsheet.

Type that expense group's name in the first cell of the fourth row from the top of sheet. Make it stand out by putting it in a larger (12 points is good), bold font.

In the row below that heading, add the following three columns:

  • Expense
  • Date
  • Amount

Type the names of the expenses within that 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 Number Format > Currency.

You'll have something that looks like this:

A group of expenses

opensource.com

That's one group of expenses out of the way. Instead of creating a new block for each expense group, copy what you created and paste it beside the first block. I recommend having rows of three blocks, with an empty column between them.

You'll have something like this:

A row of expenses

opensource.com

Repeat that for all your expense groups.

Total it all up

It's one thing to see all 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 group. 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.

The Sum button

opensource.com

Click the first cell in the Amount column and drag the cursor to the last cell in the column. Then, press Enter.

An expense block with a total

opensource.com

Now let's do something with the two or three blank rows you left at the top of the spreadsheet. That's where you'll put the grand total of all your expenses. I advise putting it up there so it's visible whenever you open the file.

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 that adds the values of specific cells on a spreadsheet.

Instead of manually entering the names of the cells to add, press and hold Ctrl on your keyboard. Then click the cells where you totaled each group of expenses on your spreadsheet.

Finishing up

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 monthly expenses for the full year instead?

Right-click on the tab at the bottom of the spreadsheet and select Move or Copy Sheet. In the window that pops up, 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 2017.ods.

Now that your setup is 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.

That idiot Scott Nesbitt ...
I'm a long-time user of free/open source software, and write various things for both fun and profit. I don't take myself all that seriously and I do all of my own stunts.

3 Comments

I'm glad you started out with a sheet of paper and lists. Persuading my students that turning on the computer and starting the spreadsheet was not an effective first step was one of my less successful efforts over the years. Knowing exactly what I want to know at the outset saves me a tremendous amount of time and effort when I have a task needing a spreadsheet.

I've found that planning with pen and paper lets you not only get ideas and structures out of your head, but also lets you shape and refine them before jumping into a digital tool. It's a lot easier to twiddle and twern ideas and concepts on paper than it is to in software.

In reply to by Uncle Ed McNerd (not verified)

Very useful information! I've been trying for a while now to think of a way to catalog and track my finances, and to create a budget that will automatically do the calculations for me. This looks like it will work perfectly for me. Thanks for the information!! Awesome article!

Creative Commons LicenseThis work is licensed under a Creative Commons Attribution-Share Alike 4.0 International License.