## Spreadsheet Questions and Answers

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

#### What is a spreadsheet?

### What is a spreadsheet?

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

### Why are spreadsheets used?

- ♦ 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** - ♦ Advanced spreadsheet skills are highly valued in business.
- ♦ 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**‘.

#### What is a cell?

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

### Select the corner icon to highlight the whole sheet.

#### How do you construct formula?

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

- + Addition
- – 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**

- Addition =A1+A2
- 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.

#### How do you Cut, Copy and Paste?

### 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 to format your spreadsheet

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

### Microsoft Excel formatting

### Apple Numbers formatting

#### How do you calculate a sum?

### 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 prepare a chart (or graph)?

### Preparing Charts from the Data in your Spreadsheets

Charts (or graphs) can be easily prepared from your spreadsheet data.

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?

### 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%

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.

#### How do you link cells from other sheets in a formula?

### 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**

### Example of a Spreadsheet

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.

**free Spreadsheet Quiz**to test your basic knowledge.