• No products in the cart.

Use these questions and answers to learn the basics of spreadsheets to present your financial data professionally in Microsoft Excel or Apple Numbers.

• ♦ A spreadsheet is mainly used to display numbers and calculations.
• ♦ A spreadsheet prepared electronically is just a version of a paper based table of figures and calculations.
• ♦ In a spreadsheet data is entered into cells of a table so calculations can be performed.
• ♦ Spreadsheet computer software such as Microsoft Excel and Apple Numbers have evolved into powerful tools to store, sort, calculate and professionally present information.

• ♦ Accountants and people in business use spreadsheets as their main office tool to organise, store, manipulate, present and forecast numerical information.
• ♦ Spreadsheets can include graphs (charts) to highlight key numerical information
• ♦ If a spreadsheet is professionally prepared with clever and correct formula,  data can be entered and the worksheet will automatically present the calculated financial information.

### What are workbooks and  sheets? The whole spreadsheet computer file is called a ‘workbook‘.

There are separate pages within the one workbook.

These separate pages are called ‘sheets‘.

### Cell References Pointing the mouse to one cell. Choose A1 ### Pointing to one cell and dragging along over the cells required  (cells A1:F1) ### Choose the whole column (Cells A1:A6) ### Highlighting from one cell (start at A1) and selecting a range of rows and columns A1:F6 ### Highlight the whole sheet ### Rules for Formula and Functions

1.   = typed in the formula bar indicates a formula

2.  The answer to the formula will appear in the cell highlighted

3.  The column/row identifier (A1) is used as required in the formula

4.  The main symbols used in formulae are

•       – Subtraction
•        * Multiplication
•       / Division
•        ^ Power of (or exponent) for example 2² = 4

5. Brackets (parentheses) are important for constructing formula using the ‘Order of Operations’

### The Order of Operations in a Formula

Formula give a different result depending on the ‘order of operations’.

The contents inside brackets are to be calculated first.

#### BODMAS (Brackets Operations Division Multiplication Addition Subtraction)

Examples • Subtraction =A5-A1
• Division =A5/2
• Cell reference squared (A1²) =A1^2
• Using brackets to set the ‘order of operations’ to ensure we correctly calculate the formula

=(B1+B2)/2

This adds B1+B2 first, then divides 2 into the total

=2*(A1+A2)+5

This first adds the items in the brackets, then multiplies the total by 2, then adds 5.

### Cut or Copy and Paste

There are various ways to copy and paste in Microsoft Excel and  Apple Numbers.

Microsoft Excel

1. Click Cut or Copy and Paste from the toolbar 2. Select your cell and right click your mouse to get formatting and copy and paste option 3. Copy down a column or across a row by highlighting and dragging to the area you want the copied  cell to go. Open the bottom right of the sighted area to copy the cell contents. In  this spreadsheet a total formula is being copied. Apple Numbers

1. Select the Edit drop down menu and then Cut or Copy and Paste 2. Select your cell and right click your mouse to get formatting and copy and paste option ### Using the Toolbar

Select the cells, columns, rows by highlighting, then format using the Font options.

Border options are available for selected cells.

Alignment allows you to orientate your text horizontally or vertically within the cell.

Merge cells together and centre text within it and also wrap text to fit within an existing column width.

The drop-down arrows in the bottom right corner of these option boxes give you more possibilities modify the cells.

### Different ways to calculate a Sum

There are several ways to calculate a sum.

1.  In the cell where the total is to be calculated press =sum and highlight the range of cells to add

2.  In the cell where the total is to be calculated press =sum and press control and separately click on each cell you want to add.

3.  In the cell where the total is to be calculated press and then type the cells you want to add. For example type =A1+A2+A3 4.  Press the autosum on the toolbar. This will highlight a suggested range and you can refine this if required. If the sighted figures are correct, click return or enter.

5.  Click on the cell with the formula you want to copy and drag to the next cell (good for column totals)

6.  Click on the cell with the sum formula you want to copy and and copy and paste. Either use the right click or use the copy and paste on the toolbar.

#### How do you filter data?

Data can be organised through the Filtering options.

Select all the data and click on the Filter button. Now click on the drop down buttons next to each heading to sort the data alphabetically and select or deselect data.

#### How do you prepare a chart (or graph)?

Highlight the data on your spreadsheet you wish to graph. Go to the insert menu  and select the type of chart for your presentation. Common types are bar, line or pie charts.

### Apple Mac Numbers

Highlight your data, select the type of chart from the Insert menu of the Chart icon. In this example the first column of data is the points on the horizontal axis 1, 2, 3, 4, 5, 6 Selecting the line graph gives you the following graph. The right hand side chart options allow for formatting the title, heading and other styling. ### Microsoft Excel Highlight the data and click on the line graph. Once you have a chart (graph) you can format it labelling the X and Y axis, adding a heading, labelling the series and making sure it is a correct representation of the data.  On the chart, right click and Choose Select Data by right clicking on the graph or by choosing it from the Chart Tools menu, gives you the following options. Editing the legend series allows you to label the series or select some cells on the spreadsheet where you have data headings to use as names.

Here we are renaming Series 1 to 2016.

In the Select Data options, a series of data can be added (click Add and then selecting the cells from the spreadsheet) or deleted. Rather than 1, 2, 3, 4, 5, 6, 7 you may want months, years or something else as the Horizontal (X) axis label. ### A more detailed example If we choose to graph our sales across the four quarters, highlight the sales cells in row 9 60,000, 76,000, 74,000, 80,000. From the  Insert menu choose the line graph At this stage the series is unnamed and the horizontal axis is 1, 2, 3, 4 (we need to label this axis as Quarters later). If your version of spreadsheet software allows, you may be able to add a chart element from the toolbar, or click on the horizontal axis data labels and right click. Here you can highlight the quarter data headings Q1, Q2, Q3, Q4 as the screen capture below. Right click on the graph to the the Select Data option. On the left hand side Legend Entries (Series) choose Edit. Then click on the row 9 cell ‘Net Sales’. This will now rename the series of data as ‘Net Sales’ instead of ‘Series 1’.

In the Chart tools you will also have the opportunity to format your horizontal axis. Here you can see we have tidied up the points where the line crosses the vertical (Y) axis and the ‘tick marks’ where the data point passes through the horizontal data point. This can be in the middle or on the tick mark We could add another data series by right clicking on the graph to get our Select Data screen and Add a second series on the Left hand side options. Click on Add and we could highlight another range of data, the Operating Expenses data. We now have a nicely presented graph we can copy and paste into a word document report.

### What is an Absolute Reference?

When cells with a formula are copied, they move to the new cell with a ‘relative reference‘ – as the cells are copied, the cell references change to match the copied location.

Note: This sort of copying will not work when we need to hold a certain cell in the formula.

To lock a cell in a formula copied into another location, an ‘absolute reference‘ can be used to lock a cell into the formula.

If C12 is in a formula and needs to be locked in wherever the formula is pasted, it should be referenced as \$C\$12.

This is an ‘absolute reference’.

The cell reference changes to 10%

Shortcuts are

PC Microsoft Excel – Highlight C12 and press Function 4

Mac Excel – Highlight C12 and press Command T

The cell reference in the formula changes to \$C\$12 Copy the sum formula from E31 to E32 by selecting E31 and dragging the bottom right corner as shown We can now calculate the new discounted price with a formula to;

Discounted Price = Total Cost – Discounted Amount

In cell G16, type =E16-F17

This formula is copied all the way down column G from G16 to G29.

The total Discounted Price in cell G31 is the sum of cells G16.G29 Now with an absolute reference, we can change the discount rate and out spreadsheet will adjust itself.

Let’s up the discount from 10% to 12.5%. To allow for an extra decimal place adjust the number format as shown. Note in this example the column of the discounted price figures has no decimal places- figures are rounded to the nearest dollar. On some versions of Excel you have an option on the toolbar to convert the cell reference in a formula to an absolute reference. ### Linking formula between sheets in the same workbook

1. Click in the cell to start entering the formula with an =

2. Go to the sheet and cell with the required data

3. Finish the formula with an enter or return key.

4. If you select a cell you don’t want by accident, press the ‘escape key’ esc

In this example, on the second sheet the data for the Office Equipment  is required from the first sheet (called ‘office furniture expenses’) of the workbook.

In the required cell (B4) on the sheet (called ‘Total business costs’),

1. enter =

2. then click on the sheet where information is located (sheet named ‘Office furniture expenses’)

3. select the required cell (E29)

4. then click enter or return

This is the formula you will have in cell B4

=’Office furniture expenses’!E29 Then we can sum the total expenses by entering in cell B10 =sum and highlighting the cells B4.B8

and refine the suggested highlighted cells by dragging the highlighted items, then press enter or return.

#### Show me how to construct a spreadsheet with a free tutorial video

Watch this video to see how a spreadsheet is formatted and built step by step.