Eric Baker

Hints & tips
Spreadsheets

v7.2  06 December 2017  © 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 Excel or Libre Office spreadsheets instead.

The hints below are Excel (2003) based but Libre Office Calc is similar (and free). Excel 2007 onwards produce prettier charts but the only feature they have that sounds really useful is multiple conditional formatting options (eg "red if 100+, yellow if 60-99, green if below 40"). That would be really good but I'm not paying extra just for that.

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 find that Libre Office is not as good as Excel 2003 at adjusting formuli as I add rows or columns.

How to stop headings disappearing?

Just go to the first scrolling row in column A, then choose Window, Freeze Panes. Go to column B and you'll freeze/lock column A too. In Libre Office it's View, Freeze cells, Freeze Rows and 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.

File, Page Setup (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 and Fit to settings until it looks good. In Libre Office it's 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. Excel 2007 and 2010 allow multiple conditional formatting - very useful.

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