LibreOffice Calc – Learn How To Track Your Expenses

In a previous article I told you about  LibreOffice Calc’s basic functions. Now I’m going to teach you how to create a worksheet meant to keep the track of your monthly expenses.

At the end, you will know how to format cells, insert data and simple formulas, and manage them by creating simple pivot tables and graphic charts.

First, let’s open a new Calc document. Start LibreOffice. From the main window click Calc Spreadsheet.

Now look at the picture and input the table headers as I did (you may replace “Expense 1, 2 ….” with anything else (e.g. rent, energy, water, gas etc.).

Under “Month” header, type “January” and hit Enter. Then select the cell by clicking on it, then click the right bottom corner and drag down. Calc will populate next cells with the next months. Stop when you see “December” and release the mouse button. You should see this:

Now start enter the figures in every cell of your worksheet that corresponds to a month or expense.

I wrote some random numbers. You will fill the cells with your necessary data.

Let’s find out the total amount of our expenses over the month. For this, click the cell where  the “TOTAL” header and “January” rows intersect.

Click the Σ symbol on the formula toolbar. By default, Calc wants to sum the adjacent range of cells. It should look like this:

Hit enter. The sum of selected cells will be calculated.

Remember how you clicked and dragged to enter the months? Do the same with this total. Select the cell, click its right bottom corner and drag down until you meet the row labeled “December.”

Release the mouse. The “Sum” formula was copied and automatically applied.

Now let’s make our worksheet look pretty. Left-click, hold and drag the mouse starting from “Month” cell to the last cell in the bottom right of the table, which is the total sum for “December.” You’ve just selected the entire table.

I want the text to be centered in every cell, so I will click the appropriate icon from the formatting toolbar.

Now select only the headers (the column labels). Pres Ctrl+B to format the text as bold. Do the same for row labels and monthly totals. Then select the entire table again. Click the “Borders and lines” button and pick the bottom right icon.

This action will draw borders for all cells within your table.

It should now look like this:

Let’s play around and create a graphical representation of our data. We all know that a picture worth a thousand words.

Left-click, hold and drag to select the entire table. Then left-click the “Pivot table” button.

A dialog window will open. Just click OK.

The next window will ask you to set the way that data is displayed.

Drag “Month” field inside the “Row Fields” section and “Expense 1, 2, …, 8” inside “Data Fields” section. Click OK.

Calc will create a new worksheet containing the pivot table.

Have you noticed that the months are not in the correct order anymore? Calc automatically sorted them in alphabetical order. We want them displayed properly, so click the black arrow next to “Month”.

From the drop-down menu pick “Custom sort” then click the “January, February…” option. All set now.

A chart would be the perfect choice for a graphical presentation. Click any cell inside the pivot table, then click the “Chart” button.

By default, a “Bar” type of chart is selected, but I find the “Pie” more interesting. Let’s pick it.

Click “Next”.

Here you may specify the data range to be displayed, if you haven’t done so already. For now, leave it as it is and click “Next”.

 

Here you may customize data series by changing parameters like border color, fill color, name and Y axis values. Or you can choose to leave it as it is. Click “Next”.

Insert a title and a subtitle for your chart. Notice that the chart is automatically updated with the information that you provide. Finally, click “Finish”. You now have a chart that will dynamically display the entered data. If you change the figures, the chart will update.

~ Adrian