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.

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.

Cut or Copy and Paste

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

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.

Microsoft Excel formatting 

Apple Numbers formatting 

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.

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?

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%

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

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

Or you can select autosum

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