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.

new_calc

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.).

table_headers

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:

populate_months

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

write_expenses

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.

select_total

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

sum_row

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.

table_selected

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

center_text

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.

draw_borders

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

It should now look like this:

table_formated

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.

btn_pivot_table

A dialog window will open. Just click OK.

pivot_table_source

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

configure_pivot_table

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.

pivot_table_worksheet

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”.

sort_months

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.

btn_insert_chart

chart_dialog

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

pie_chart

Click “Next”.

chart_data_range

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”.

chart_data_series

 

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”.

chart_titles

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