Whether you are involved in budgeting, accounting or financial reporting, it is very likely that you will end up using Excel spreadsheets at some point or another. Here are some tips to make it easier. They get more advanced as you go down the page.
Copying and pasting text
This is a basic and powerful Excel tool. You can copy the contents (text or formulas) of a cell to another cell by selecting with the cursor the cell containing the contents that you want to copy. You then choose ‘Edit’ and then ‘Copy’ from the main window menus. (A short cut is to right click the mouse and select ‘Copy’). The next step is to move the cursor to the cell to which you are copying the information. Choose ‘Edit’ again and then ‘Paste’ from the main window menus. (A short cut is to right click the mouse and select ‘Copy’ or ‘Paste’, or click on the short cut icons). You can copy a block of cells in several columns or rows – but do make sure that there are sufficient empty cells where you paste this information.
Copying rows of formulas
If you copy a formula from one cell to another the formula does not change but the cell references do, so the copied formula will apply to different cells. This can be useful - it means you can copy the formulas in the rows in the various worksheets down to the new rows that you have inserted. The formulas are automatically changed so that they apply to the newly inserted row. Note that this is ‘copying’ a cell. If you ‘Move’ a cell the formula references do not change!
An alternative method to copy down cells is to highlight the cells to be copied, and then move the cursor to the bottom right corner of the cell on the right hand side. You will see that the cursor changes shape – into a thin or a thick cross. When it is in the shape of a thin, pencil line cross hold down the left click of the mouse and ‘drag’ the cells down to the next row. Check the formulas are correct before moving on to another task!
Worksheets are copied by moving the cursor onto the worksheet tab; right click, then select ‘Move or Copy Sheet’.
Excel allows you to tell one cell to look at another, and to use the contents of the other cell in a calculation. For instance, the formula ‘=A1+A2’ can be entered into any other cell. The value shown in the other cell will be the sum of the value in cell A1 and the value in cell A2. ‘A1’ and ‘A2’ are called ‘cell references’.
Cell references can always be written in one of two ways. They can be absolute, or relative. The big difference between them is that a relative cell reference will change when you copy it from one cell to another. But an absolute cell reference will not.
This can be very important when you are copying formulas around in accounts spreadsheets. Watch out for it with the SUMIF formula on the analysis sheet.
Normally, all cell references are relative. But, either the column letter or the row number part of the cell reference can be made absolute by putting a ‘$’ sign in front of it. For instance, the reference ‘$A$4’ will not change no matter where it is copied to. The reference ‘A$4’ will allow the column to change as the formula is copied across the page. But it will not let the row change if the formula is copied down the change. The reverse happens with the reference ‘$A4’.
Freeze Panes lets you keep a part of the spreadsheet on the screen at all times, even if you scroll a long way down it. This is useful for making sure that the titles of tables are always visible.
You can freeze the part of the screen ABOVE and to the LEFT of any selected cell by choosing ‘Freeze Panes’ from the ‘Window’ menu. So, you have to select a cell in the right place before ‘freezing the panes’. You can turn freeze panes off by choosing the ‘Freeze Panes’ options a second time from the ‘Window’ menu.
A cell containing the Excel ‘IF’ formula examines the contents of another cell. It compares it with the value that you have indicated in the formula. If it is the same, the cell will display one value. If it is different, the cell will display another value. For example, the ‘warning’ cell that checks if analysis codes have been entered reads as follows: =IF(F8=G8,"","CHECK CODE"). (In other words: If cell F8 is equal to cell G8 then return the text “ ”, otherwise, return the text “CHECK CODE”). The cell checks if the balance on the Analysis Sheet agrees with the balance on the Cash Book. If it does not then an analysis code has either not been entered, or has been entered incorrectly.
Insert Rows (and columns)
You can insert a new row by moving the cursor onto the left hand margin on the rows above which you wish to insert the new row. You then choose ‘Insert’ and then ‘Row’ from the main window menus. (A short cut is to right click the mouse and select ‘Insert’).The new row will be formatted in the same way as the previous row.
Page Setup lets you do the same thing when you are printing an Excel document as it does in Word. You can set a whole range of features that define how the page will be printed. One of them lets you repeat the titles of a table at the top of every page.
Choose the ‘Page Setup’ option from the ‘File’ menu. This gives you a window that has four tabs on it: Page, Margins, Header/Footer, and Sheet.
- The Page tab lets you set the paper size, and whether your spreadsheet will be printed as a landscape document (sideways) or as a portrait document (upright).
- The Margins tab lets you set the margins between your spreadsheet and the edge of the paper. It also lets you centre the spreadsheet on the page, which often looks good.
- The Header/Footer tab enables you to put headers (at the top) and footers (at the bottom) of your spreadsheet.
- The Sheet tab lets you set a number of other options, including Print Titles. Enter the rows that you want to be repeated at the top of each page (e.g. writing 1:6 for rows 1 – 6) in this box.
Paste Special is very useful for moving large amounts of data around. It lets you copy and paste data in a special way. Choose ‘Paste Special’ from the ‘Edit’ menu, and you will get a range of options. (Note this only works when you have already ‘copied’ something – otherwise, the computer has nothing to paste.)
‘Values’ is a useful option: you can select a cell that has a formula in it, which returns (for instance) the value of 4. If you use paste to copy the cell, then you will get a copy of the formula. But if you use ‘Values’ from the ‘Paste Special’ feature, then you will get the number ‘4’.
This can save a lot of time when copying data from the analysis sheet across to the consolidation form, for instance. It is also useful for moving around columns of monthly data inside the consolidation form.
‘Formats’ is another handy ‘Paste Special’ option. You can copy across just the formats of a cell, without any of its contents. This can help when putting borders around tables, or when changing the fonts used in a spreadsheet.
The SUMIF statement transforms spreadsheet-based accounts. It does all of the hard work for you, by summarising large volumes of data, by code. It uses three variables A, B & C:
A Range 1 (where it will search for the criteria)
C Range 2 (where it will find the data to sum)
On these accounts spreadsheets, the criteria (B) that it will search for is the accounts code. Range 1 (A) is the column containing the accounts codes on the cash book. Range 2 (C) is the column containing the amounts spent or received on the cash book.
The formula automatically picks out all transactions that have the same code, and adds together the total amounts spent/received for each one. That means that it does all of the analysis for your accounts.
The second tool which makes Excel an accountant’s dream is the Autofilter. This can transform any list in a spreadsheet into a database, and lets you pick out specific items that share certain characteristics. So, for instance, you can immediately pick out all transactions from the cash book that have the same code.
To use the Autofilter tool, simply select a cell that is in the list, and choose ‘Autofilter’ from the ‘Filter’ option on the ‘Data’ menu. This will place drop down menus at the top of each column of your list. You can then use these drop down menus to select the items on the list that you want to be displayed. To turn the Autofilter off, choose ‘Autofilter’ a second time from the ‘Filter’ option.
Autofilters are extremely easy to use and can save many hours of laborious work. Try using them with the Autosum button on the tool bar. Excel will automatically give you a sum function that only adds up the totals of what you can see on the screen (i.e. what you have selected using the filter) rather than everything on the list.
But, you have to be careful that you do not leave a blank line between the titles at the top of your lists of data and the data itself. Autofilter only goes up to the top of an unbroken list, and automatically assumes that the top cell contains a title. This means that the column on the left would work properly, but the column on the right would not: