If you haven’t ever used a spreadsheet package before, imagine a large pad of graph paper. Each sheet is like a huge page of paper covered in thousands of squares. The squares – known as cells – are arranged in columns labelled A, B, C, etc. and rows numbered 1, 2, 3, etc. Any cell you click becomes the ‘active’ cell – it will display a black border and anything you type appears inside and can be formatted in the normal way. The cell’s contents are also displayed, and can be edited, in the Formula bar.

Cells are referred to by their column letter and row number e.g. D7 below.

It is quite likely that you will be asked to work on excessively large spreadsheets, covering thousands of columns and rows and which may extend across a number of sheets or even separate files (Workbooks). Although they may appear daunting, follow the guidance in this chapter to master all the ‘tricky’ tasks you may need to carry out.

In particular, you may need to:

• Understand underlying mathematical principles
• Protect cells
• Use a variety of functions
• Name cells or use an absolute cell reference
• Apply different formats
• Work across multiple worksheets or files
• Find information
• Analyse data using a pivot table

Depending on your role, you may also need to produce charts and graphs as an alternative method for presenting numerical information, either to print out or to copy into a word processed document or include in a PowerPoint presentation. To help you, this chapter ends with advice on producing and customising charts.

A FEW WORDS ABOUT USING NUMBERS

Although Excel takes the pain out of calculations, it is important to understand some of the mathematical principles underlying the program, as you must always type your instructions – the formulae – accurately. You should also be able to estimate the likely results of any calculations so that you can pick up mistakes quickly.

BODMAS

At some time you may need to create quite complex formulae and write them in a way that ensures the calculations are performed accurately. You therefore need to know that Excel carries out calculations in a particular order, following the BODMAS (or BIDMAS) rule:

Remember that you should always start a formula by typing = and try to use the address of a cell e.g. B12 or C4 in your formulae, rather than the raw figures so that you are instructing Excel to base calculations on the contents of a cell at any time. This will mean that changing any figures on a spreadsheet will update the calculations automatically.

Excel uses these operators:

+ add
- subtract
* multiply
/ divide

Percentage

Percentage means parts of a hundred.

100% is 100/100 which cancels down to 1 – the whole
50% is 50/100 which cancels down to ½
25% is 25/100 which cancels down to ¼
75% is 75/100 which cancels down to ¼
20% is 20/100 which cancels down to ⅕

• 1.To work out the percentage (e.g. 2% or 30%) of a number (e.g. 65), multiply them together:

2% of 65 = 2% x 65 = 2/100 x 65 = 130/100 = 13/10 = 1.3

30% of 65 = 30% x 65 = 30/100 x 65 = 3/10 x 65 = 195/10 = 19.5

You should now be able to carry out a common task which is to work out the final cost of an item that is being discounted. To do this, you need to use a formula that will subtract the discount e.g. 8% of the original price (£237.50 in the following example) from this figure.

Remembering BODMAS, you can use brackets so that the formula will look like this:

Final price = original price – (discount x original price)

= £237.50–(8% x £237.50)

= B2-(C2*B2)

• 2.To convert a fraction such as 3/5 into a percentage, remember that a fraction is part of a whole and 100/100 (100%) is also a whole.

So the true problem is: 3 out of 5 is the same as what out of 100?

So the answer is 60%

Note: In Excel, be careful when formatting cells. Applying a percentage format multiplies a fraction or decimal by 100 e.g. if you type 0.25 in a cell and format to percentage the cell will display 25%. To type 25% in a cell, don’t format the cell to percentages and then enter 25 or you will end up with 2500%. Instead, type the % symbol manually. The figure will still be recognised by Excel as 25%.

PROTECTING CELLS

You may not be aware of this when working with spreadsheets in the normal way, but cells in a spreadsheet are automatically locked. If you protect the worksheet, no-one will be able to amend any entries. You can use this facility if you create sensitive spreadsheets where another member of staff needs to be able to make some changes. To allow specific cells only to be edited, unlock them first before protecting the rest of the sheet.

Carry this out by following these steps:

• 1.Select the cells that you want open to editing and go to Format – Cells – Protection. Make sure you take off the tick in the Locked checkbox before clicking OK.

• 2.Open the Tools menu and select Protection – Protect Sheet. In the dialog box that opens, select the option to allow changes to unlocked cells and, if required, add a password to restrict access.

• 3.When you return to the sheet, you will find that you can now only click in the unlocked cells.

FUNCTIONS

You may already have met some basic functions that speed up totalling and averaging columns of numbers, but there are many others that can be useful.

To enter a function, you need to use the actual term recognised by Excel e.g. SUM, AVERAGE etc. and include the range of cells to which the function will apply. To add cell addresses to a formula quickly, click and drag across the range and they will be entered as the address of the first, a colon and then the address of the last. You will, however, still need to type the brackets e.g. (C2:C8).

In the following examples, you would click in B16 and type the appropriate function:

For machines with Office XP installed, you will also be offered these functions if you click the arrow next to the AutoSum button. However, in earlier versions of Excel the button only provides the SUM totalling function.

• 1.To total the cost of all seed packs, use the SUM function: =SUM (B2:B15). Alternatively, select the range of cells and click the AutoSum button to add a total to the next empty cell.
• 2.To find the average cost of seed packs, use the AVERAGE function: =AVERAGE (B2:B15). This will total the prices and then divide by the number of packs. (As an alternative, you could find the total in B16 and then work out the average on the next row by dividing the total by the number of packs using the formula =B16/14.)
• 3.To count up the number of packs of seeds, use the COUNT function: =COUNT (B2:B15). To count cells that contain a text entry, you must use the function COUNTA.
• 4.To find the most expensive pack, use the MAXIMUM function: = MAX (B2:B15)
• 5.To find the cheapest pack, use the MINIMUM function: =MIN (B2:B15)

If you look at the bottom of your screen, you will see the total of any selected cells showing on the status bar. A quick way to find the average, maximum etc. for your cells if you just want to know the answer rather than enter it on the spreadsheet is to right-click the figure and select an alternative function from the list.

IF function

This function is different as it compares each entry row by row. It is based on the following logic:

If statement A is true, display X, if not display Y

In Excel this is written as follows:
            =IF(statement,“text if true”,“text if false”)

For the seed packs, you might want to know which seeds cost more than £2. If they cost more, display the word yes, but if they cost less display the word no.

The statement is: the cost of a pack is more than £2.

• 1.Click in C2 and enter =IF(
• 2.You want to know if Carrot seeds cost more than £2, so this is written B2>2
• 3.Add a comma and then the text you want to see if it is true i.e. yes. Text should be in quotation marks so this is written “yes” (although you do not include quotes if you wanted to display a number instead of text entry).
• 4.Finally, add a comma and type the text you will see if the answer is not true i.e. “no”.

The full function is written =IF(B2>2,“yes”,“no”)

This function can be replicated down the column, as the next cell will contain a reference to Cauliflower seeds and the formula will be written =IF(B3>2,“yes”,“no”). The rest of the seed packs will be compared in the same way.

To help with any function, you can always click the insert Function button marked fx on the Formula Bar, select the relevant function and complete the boxes that are offered.

IMPORTING DATA

In many organisations, data will have been prepared in a grid or table using a different application e.g. Works or even Word, and you may want to add it to a new or existing spreadsheet. Fortunately, Excel is very accommodating. All you need to do is select all the data in the original file and click the Copy option. With your spreadsheet open, click one cell e.g. Al or further down an existing sheet, and then right-click and select Paste. The data should appear and you can continue working as normal.

To copy across a database from Access, you may prefer to use the Office Links facility described in the chapter on databases. However, as the data in a database table is set out in a similar way to a spreadsheet, you can also simply copy and paste it across.

Copying within Excel

If you are copying from another worksheet or even on the same sheet and want to copy cells whose contents were created using formulae, there will be a problem with straightforward pasting as the cell addresses will no longer apply. Instead, use the Edit menu to select Paste Special. You can now choose whether to paste only the values or to include the formulae.

INSERTING NEW COLUMNS OR ROWS

At any time, you may want to add extra rows or columns of data into a spreadsheet or delete redundant information without leaving blank cells.

• Click the column letter header to the right of a new column, or row header box below the position for a new row. With the row or column selected, go to Insert – Columns (or Rows). The new column or row will slide into place and header letters or numbers will be adjusted automatically.

• To add more than one extra column or row, select that number of header cells first.
• To delete one or more columns or rows, click the header letters or numbers to select the entire column or row and then go to Edit – Delete. Using the Delete key on your keyboard will only delete cell entries and leave empty cells in place.

ABSOLUTE CELLS

Copying formulae across rows or down columns is very simple: drag the ‘fill handle’ in the bottom, right-hand corner of the cell when the pointer shows a small black cross. As formulae normally contain ‘relative’ cell addresses, these will adjust e.g. reference to B2 will become B3, B4, B5 etc. as you move down the column.

Very often, you will want to refer to the contents of one particular cell when carrying out a calculation that will be copied/replicated across a spreadsheet e.g. if applying a discount or percentage to a range of entries, or converting units of measure. If you try to copy the formula down a column in the normal way, the contents of this particular cell will also be copied down which will result in errors.

To fix the cell address in a formula that is going to be copied, you have two options: naming the cell or using the absolute cell reference.

Naming cells

Any cell, or range of cells, can be given a name. You can then refer to them by that name and only the named cell will be included in your calculations. To name a cell or range, select it and then go to Insert – Name – Define. Type the name for the cell in the box or accept the name that appears and click OK.

Using the name in your formula rather than cell address means you can copy it down the column accurately.

Absolute cell reference

If you prefix a cell address with dollar symbols $, Excel recognises this as the absolute address and it will not change as you copy a formula down the column.

• 1.Enter the first formula as normal and then add dollar signs to the cell address you wish to fix. You can type the $$ manually or use the following shortcut.
• 2.In the Formula Bar, click between column letter (e.g. A) and row number (e.g. 10) for the cell and press the function key F4. Dollar symbols will appear. If you keep clicking F4, you can partially fix the address (e.g. A$10, so that when copied it will become B$10, C$10 etc.) or take them off completely.

• 3.Click the tick or press Enter and then copy this formula down the column. All cells will now contain a reference to A10.

FORMATS

When it comes to formatting, spreadsheets are very logical. Click any cell or select a range of cells and then use normal word processing buttons on the toolbar to change entries to bold, italic, centre alignment etc. or open the Format – Cells menu and select options from the Font, Number or Alignment tabs.

Error messages

Whenever Excel carries out a calculation, it puts the answer in the cell using a general format. As this may have a number of decimal places, the column may not be wide enough for the full display. You will see #### appearing instead of the figure. Simply widen the column to view the figures.

Customised dates

To make your dates or numbers look right, you need to format them via the Format – Cells – Number menu. However, you may not see exactly the style you want even after scrolling down through the list of styles on offer. Fortunately, you can create your very own style and impose it on the machine.

If we take dates as an example, perhaps you would like to see the format 22 Jan 04 but all you can find is 22 January 2004.

If you click the word Custom in the Category list, you will be offered a further range of dates. These are expressed in terms of d (for day), m (for month) and y (for year). The number of letters is translated into the characters in the display, with mmmm displaying the full name of the month.

dd/mm/yy will display 22/01/04
dd/mm/yyyy will display 22/01/2004
dd/mmm/yy will display 22-Jan-04
dd/mmmm/yy will display 22 January 04

If you don’t see the exact style you want, type into the box until the Sample displays your choice and then click OK.

Column headings

You may want to retain narrow widths for your columns but still display a long entry. Do this by selecting the cell, opening the Format – Cells – Alignment menu and clicking on Wrap text.

You may need to increase the row height or drag the border to prevent words being split but you will now be able to see the entire entry and still keep the column narrow. Once you have one deeper cell, you can set entries in neighbouring cells centrally or at the top or bottom of the cell using the vertical alignment options.

You can also display a heading vertically if you select the range of cells and select the vertical Text orientation box, drag the red pointer or set the measure to + or – 90 Degrees. Click the Merge cells button and then OK.

PRINTING

Depending on the default settings, your spreadsheet may or may not print out showing gridlines. To select your preferred display, open the File – Page Setup menu and click the Gridline checkbox on the Sheet tab. On the Margin tab, you can also choose to set the spreadsheet data in the middle of a page by clicking the Centring options.

Normally, only that portion of the spreadsheet with entries will print, but if you want to print selected columns only, select them with the mouse and then click Selection in the Print what box.

GIVE IT A TICK

It may seem a silly little thing, but people often use spreadsheets for questionnaires and surveys and want to be able to add a tick ✓ to a cell. Unfortunately, the option that has always been available in Word (Insert – Symbol) is not available in Excel 2000, although it has now been added to Excel XP (2002).

If you have the older version of the software, here are a couple of ways to add this symbol:

• Open a Word document, find the tick from the Insert – Symbol menu e.g. under Wingdings fonts, click to select it and then press the Insert button and close the symbol box. Now copy and paste the tick that will have been added to the page into an Excel cell.
  
• You can now use copy and paste within Excel to add the symbol to further cells in your spreadsheet. (The cell being copied will show flashing dots – a marquee – round it to show which cell is being copied, so remove these by pressing the Escape key when you have finished copying.)
• You may have the component Character Map installed on your machine (usually under Programs – Accessories – System Tools) which works in a similar way. Open the window and find the symbol, click it and then click Copy. Now paste it into a cell but make sure you format the cell to the same font e.g. Wingdings or you will display a different symbol or character (e.g. the letter P or a square).

CREATE A SERIES

One shortcut worth using is when you want row or column labels to display dates, months or days of the week. If you type one entry e.g. Monday and then copy this down using the fill handle in the bottom, right-hand corner of the cell, the next cells will display Tuesday, Wednesday, Thursday etc. as Excel recognises the date series.

To cancel this effect, type the same date twice, select both cells and copy down from the second. However, if you type two numbers into adjacent cells and copy down from the second you can create an incremental series.