Eric Baker

Hints & tips
Spreadsheets

v7.3  29 November 2019  © Eric Baker
www.chericbaker.co.uk




Spreadsheets are the obvious answer for anything that needs calculations which automatically update as you update figures.

They can also be used quite effectively to hold structured data lists where few or no calculations are needed. I used to use a database for this but have never found one that's easy to use, cheap or free and universally available. So now I use Libre Office spreadsheets instead.

The hints below are based on Libre Office Calc which is similar to older Excel versions (and free). Libre even now has conditional formatting, but it's not easy to use.

How do I use a spreadsheet to control my finances?

First think carefully what you want to record and calculate. Then plan how it's going to look and print - vertical formats often work better because you can use the scroll wheel on your mouse. Then put the 1st spreadsheet together, checking the Help and also web resources as you go.

Beware - many spreadsheets have hidden errors such as a formula not copied to a new row and spreadsheets are notoriously difficult to check. So do check the basics on a calculator before deciding you can retire early or go to the Maldives after all. I found a while back that Libre Office was not as good as Excel 2003 at adjusting formuli as I added rows or columns but it's definitely got better over time.

How to stop headings disappearing?

Just go to the first scrolling row in column A, and select View, Freeze cells, Freeze Rows and (or) columns.

Why doesn't it print everything?

Setting the area to print is done by highlighting the cells to print then File, Print Area. Format, Print ranges in Libre Office. Be careful that you do not add data to the bottom of a spreadsheet and forget to alter the print area - always check via Print Preview.

Format, Print ranges in Libre Office allows you to alter margins and there is a useful "Fit to" option which will shrink prints onto a specified number of pages. Do a Print Preview and adjust your margins via Format, Page, Sheet, Scale.

Page fit

How do I sub-total a column?

=SUM(B13:B19)

How do I calculate a percentage?

To the value of H13 as a percentage difference from E13,
=((H13 - E13) / (E13 / 100))
So if E13 is 100 and H13 110 the answer is 10. If E13 is 110 and H13 100 the answer is -9. Right click the % results cell, choose Format cell and set decimals to something sensible (often 0 is best) then copy the formula as required.


Sheet columns

How to apply a format to multiple cells?

Double click the format painter to apply a colour etc to multiple other cells. Click it once when you've finished.

How to refer to a constant in a formula?

The $ sign locks a cell reference, eg $G$5 will not be altered as you copy a formula to another cell. Plain G5 will. $G5 just locks the column, not the row.

Format painter

How do I add a live pie or bar chart?

Pie chart Live charts always make a spreadsheet look special. The best way to do them is to gather a limited selection of source data in one block, perhaps on a separate page/tab, eg the totals for different types of expenditure. Then you wipe (select) that area (descriptions and figures) with the mouse and use the chart wizard icon to set up the chart. As the data alters so does the chart. Magic!

If the data you want to chart is scattered around the spreadsheet that doesn't matter - just copy the key values to one block. This may well involve aggregating several figures into one summary one, eg monthly expenditure on car(s).

How do I avoid hand filling Feb, Mar etc?

Autofill is quite powerful for data, dates and formuli. For example if you put "Jan" in a cell then drag the bottom right corner of it you'll automatically get the series Feb, Mar, Apr etc filled in. Try it out.

How do I make a cell go red if its content goes negative?

Conditional formatting (off the format menu) can alter cell properties depending on a condition, eg go red if over or under a certain value. It's not easy to use but do persist!

Can you reference a cell on another tab/page?

To reference cells on another tab (named Constants): =Constants!$C$5*A7 Instead of typing it you can get Constants!C5 by going to that sheet and clicking on the cell. This also works fine if you want a chart on one sheet with its data hidden on another.

How to calculate an average?

=SUM(A20:E20)/COUNT(A20:E20) (COUNT skips empty cells).

Can I use if in a formula?

=IF(B47>A47,((B47-A47)/(A47/100)),"Smaller!") says: if B47 is bigger than A47 then display the percentage increase, else display Smaller! Ie IF(condition, true, false)

=IF(B4=B3,"***","") will highlight duplicates in column B - sort it first.


Summary data

Chart wizard









Series fill

How do I store data lists and sort them in a spreadsheet?

You can sort data by just selecting a rectangle of cells (preferably with headings) then Data, Sort.

It's better to make the area into a sort of database if you need to sort repeatedly. Select the block of cells you're interested in then choose Data, List, Create (or equivalent): Excel database

That gives you a simple database inside a spreadsheet. You can sort the rows any way you like and also select particular rows. Prints from data lists are a bit messy to set up but this is quite a good quick and dirty way of bringing order to lists of various kinds. It expands nicely as you add new rows of data.






Sorting data