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.
How do I sub-total a column?
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.
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 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
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?
(COUNT skips empty cells).
Can I use if in a formula?
says: if B47 is bigger than A47 then display the percentage increase,
else display Smaller! Ie IF(condition, true, false)
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.