Hints & tips

Spreadsheets & data lists

Spreadsheets are the obvious answer for anything that needs calculations which automatically update as you update some of the 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 Open Office spreadsheets instead.

The hints below are Excel (2003) based but Open Office Calc is similar (and free).

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 go to the Maldives after all.

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.

Why doesn't it print everything?

Setting the area to print is done by highlighting the cells to print then File, Print Area. Be careful that you do not add data to the bottom of a spreadsheet and forget to alter the print area – always check via File, Print Preview (also available via an icon).

File, Page Setup 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.

How do I sub-total a column?

=SUM(B13:B19)

How do I calculate a percentage?

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.

How do I add a live pie or bar 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 copies of 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.

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

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

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.