Develop a linked spreadsheet solution

When developing spreadsheets, you will often find yourself using the same data across multiple tasks and even across different projects. In these situations, you could duplicate the required data into each spreadsheet. However, copying data can be very time-consuming and increases the chances of errors occurring. Instead, it can be valuable to link the spreadsheets together using the tools provided in your spreadsheet software.

To link spreadsheets, you need to follow the linking procedures specific to the spreadsheet software you are using. This subtopic will discuss how worksheets and workbooks are linked using the software procedures of Excel. Note that other spreadsheet software may follow different procedures for linking. However, all spreadsheet software will have features that allow users to create a linked spreadsheet solution.

The following 4-minute video tutorial explains working with multiple worksheets.

Worksheets and workbooks

Excel allows you to connect your data by linking worksheets and workbooks.

A worksheet is the workspace where you enter data composed of columns and rows of cells. The default worksheet in Excel is labelled ‘Sheet1’ on a tab at the bottom of the window.

You can add a new sheet by clicking the ‘+’ button. Each new worksheet you add will be automatically labelled as ‘Sheet2’, ‘Sheet3’, and so on.

Where you have multiple worksheets in the same spreadsheet, it is best practice to rename the worksheets to make it easier to find the data you want. To rename a sheet, ‘right-click’ on the sheet name and select ‘Rename’.

A workbook is an Excel file containing one or more worksheets. The filename of a default workbook is ‘Book1’. New workbooks that you will open following the first workbook would be labelled as ‘Book2’, ‘Book3’, and so on. You can rename your workbook to reflect the project or task it services by clicking ‘Save As’ in the ‘File’ menu.

Linking worksheets

In Excel, it is easy to create, format and edit multiple worksheets at the same time. First, you need to select the worksheets you wish to work with. There are a number of options for selecting multiple worksheets.

1. To select a continuous range of worksheets, ‘left-click’ on the first worksheet tab, then hold down the [Shift] key and ‘left-click’ on the last worksheet tab in the selection. This will highlight both tabs selected, as well as all the tabs in between.
   
2. To select specific worksheets that are not in sequence, hold down the [Ctrl] key and ‘left-click’ each worksheet tab you wish to include in your selection.
   
3. To deselect specific worksheets that are currently selected, hold down the [Ctrl] key and ‘left-click’ the worksheet tabs you wish to deselect.

Once you have selected multiple worksheets, any formatting changes, data or formulae you enter into one of the selected worksheets will be entered into all the other selected worksheets at the same time.

For example, if you want ‘Aussie Tool Shed’ to be in italics for all worksheets, select all worksheets first before clicking the ‘Italics’ button from the ‘Font’ category on the ‘Home’ ribbon. Once you do this, you will discover that ‘Aussie Tool Shed’ will be in italics in all sheets.

Linking cells

Excel allows you to easily link cells so that any changes to the source cell (the original cell) will automatically be applied to all linked cells. The simplest way to link a cell so that it will always refer to a particular source cell is to reference the source cell in a formula.

The following example shows a workbook containing some quarterly sales data for Aussie Tool Shed’s three main business customers.

The total monthly sales are entered into quarterly sales reports. Suppose you were tasked to calculate the total annual sales for these business customers. To do this, you will first need to link cells within the same worksheet and then link cells between different worksheets.

Linking cells in the same worksheet

Your first step would be to compute the total quarterly sales for each of the three customers. You can do this by entering a SUM function in cells C8, C12 and C16 to total the monthly sales for each business customer. For example, you could enter the following function into C8:
=SUM(B6:B8)

Next, entering the following function into C17 would calculate the grand total for the quarter:
=SUM(C8,C12,C16)

Using cell references instead of values in the function links the cells. If there are any changes to a referenced cell, the destination cell (the cell containing the cell reference) will automatically update accordingly.

For example, if a September sales total was added to the spreadsheet for ‘NTD Maintenance’, the ‘NTD Maintenance total’ (C16) would update, which would cause the ‘Grand Total’ (C17) to update as well. You wouldn’t need to recalculate anything!

Next, you need to calculate the annual total for each business customer. To do this, you will need to link different worksheets.

Linking cells from different worksheets

You would follow the same process to create a quarterly sales report for December, March and June. However, to create the annual sales report, you will need to reference the totals from each of the quarterly reports, which are on different worksheets.

To link cells on different worksheets:

1. Start the function in the destination cell. In this instance, simply type the equals sign [=].
2. Then click the appropriate tab to switch to the required worksheet.
3. Next, click the cell you wish to reference.
4. Press [Enter]. Excel will add the cell reference to the function.

In this example, the formula in cell B6 is referencing the September sales total for ‘Cabinets R Us’. This information is located in cell C8 on the worksheet titled ‘Quarterly 1’. So, the formula that links the two worksheets is:
=’ Quarterly 1’!C8.’

If the referenced cell is edited, the destination cell on the linked worksheet will be automatically updated.

The same process applies to all the quarterly sales totals for each of the business customers. To see all the formulae in the sheet, press [Ctrl + `]. (The ` symbol (grave accent) is the key to the left of the number 1 in the numbers row on your keyboard.)

A formula that links worksheets within the same workbook has the following general syntax:
=‘WS’!X

Where:

• ‘…’! Indicates the cell reference is not on the same worksheet.
• WS is the name of the worksheet that contains the cell that you will reference.
• X is the cell reference in the reference workbook.

Linking cells from different workbooks

Suppose that when you were asked to create the annual sales report, the quarterly data was in separate workbooks rather than in separate worksheets within the same workbook. This time, to develop the annual report, you need to link cells from different workbooks.

To link separate workbooks, follow these steps:

1. Click on the cell where you want the value from the other workbook to appear. In this case, B6.
2. Type the equals sign [=].
3. Go to the workbook with the worksheet containing the information you require. For this example, the ‘September Quarterly Sales Report’.
4. Select the cell containing the data you want to show in the workbook where you are working. C8 shows the quarterly sales total for ‘Cabinets R Us’. Notice that your formula in the original worksheet has been updated.
5. Press the [Enter] key to finalise your formula.

A formula that links workbooks has the following general syntax:
=‘[FN]WS’!X

Where:

• ‘…’! Indicates the cell reference is not on the same worksheet.
• [FN] is the file name of the reference workbook, including the file extension.
• WS is the name of the worksheet that contains the cell that you will reference.
• X is the cell reference in the reference workbook.

In this example, as shown in the previous images, the correct formula as shown is:
=‘[AussieToolShed_Sept.xlsx]Quarterly 1’!$C$8

Where:

• AussieToolShed_Sept.xlsx is the file name of the reference workbook.
• Quarterly 1 is the name of the referenced worksheet.
• $C$8 is the absolute cell reference of the required value.

Practice Exercise

Download the following Excel files and access the Cakes by Cloe practice exercise instructions to complete this exercise.

• Cakes by Cloe practice exercise instructions
• Cakes by Cleo North Excel File
• Cakes by Cleo South Excel File
• Cakes by Cleo West Excel File
• Cakes by Cleo East Excel File

After you have completed the practice exercise, you can check the sample answer here.

The following 4-minute video will help you better understand cells and the different operations regarding cells:

The following 4-minute video tutorial includes information on formatting cells, such as changing font size and colour, using bold, italic and underline commands, applying cell styles and changing text alignment:

Formatting Cells

Data attributes describe the properties that are unique to each cell in a spreadsheet. These attributes include:

• Formula – the formula or function contained within the cell.
• Value – the final, displayed value of the cell, which is computed after applying any formulae and functions within the cell.
• Format – the appearance settings applied to the value of the cell.
• Comments – any comments associated with the cell.
• Validation – any restrictions on the data or values that can be added to the cell.

When you develop your spreadsheet, consider how data attributes can be changed to meet the task requirements. We have already discussed formulae, functions and values. Let us now look at formatting in more detail.

The importance of formatting

When developing spreadsheets, it is often necessary to use multiple formats and formatting techniques across different cells. When planning your spreadsheet, consider the formatting task requirements. ‘What should your spreadsheet look like?

Different spreadsheets will have different formatting requirements depending on the purpose. Take a look at the following two examples:

Example 1:

Example 2:

These two spreadsheets serve different functions, which means that they will have different sets of formatting requirements. For example, the spreadsheet in Example 1 uses different cell fill colours to group audit items together into categories to make them easier to locate. Meanwhile, the spreadsheet in Example 2 is used to report monetary values. It was formatted so that the decimal points line up, and the dollar symbols are displayed to clearly show that the data presented are monetary values. The total amounts were also emphasised by using boldface.

When formatting cells according to task requirements, always remember what data you are trying to report and figure out the best way to clearly report this data.

Key aspects of formatting

Spreadsheet formatting is important because it dictates how you will present your data. When you format a document, your goal is to ensure that the data you present is readable and understandable. This is why you input data into a spreadsheet in the first place. It helps you and other readers make sense of the raw data.

To ensure that the data on your spreadsheet is presentable and readable, there are three key formatting aspects to keep in mind:

• Accessibility
• Consistency
• Style.

Let us examine each of these aspects more closely.

Accessibility

Your spreadsheet must be formatted to be easily accessible and understandable by anyone who needs to read it. Think about your possible readers and ask, ‘What information will they look for?’. For example, if you develop a sales report that your supervisor will use to present the progress of the company to stakeholders, what information should you highlight? In this case, your supervisor would probably be most concerned with total figures. You could highlight the total figures in your spreadsheet by using boldface, a larger font or a different cell colour.

A well-formatted spreadsheet is easy to read and draws attention to the most appropriate information. On the other hand, a poorly formatted and inaccessible document would show little to no distinction among cells.

The data presented in the following two spreadsheets is the same. However, consider how the formatting of the second example's spreadsheet improves the accessibility of the information:

Example 1:

Example 2:

Consistency

Consistency of formatting applies when you create multiple similar workbooks. The best practice is that similar spreadsheets should follow a template that provides and highlights the same data each time. For example, the formatting of your monthly sales report for June must be consistent with that of May and previous months.

Regularly produced and referenced spreadsheets should be familiar. Changing the format every so often may confuse people who need to use your document. Automating formatting using macros or templates is the best way to ensure consistency. This will be discussed in the next topic.

Style

Your spreadsheets should also have some aesthetic similarities with other documents designed and produced by your organisation. When somebody outside your organisation reads your documents, they must be able to associate them with your organisation. This is why organisations have style manuals that detail the specific formatting and design guidelines to follow when producing documents. At the very least, most organisations will have a logo or a banner that should appear on every document or spreadsheet produced.

Here are some key aspects that you could implement during spreadsheet formatting and design to enhance presentation and readability:

1. It is important to choose the font carefully, according to the organisation's style guide. When selecting fonts, keep in mind the following:

• Select a clear font such as Calibri or Helvetica to present data.
• Limit the number of fonts used in a spreadsheet. Stick to one or two at most.
• Use a clear hierarchy of font sizes. Important texts, such as headers, should be larger than sub-headers. Subheaders should be larger than the data text.

2. When setting alignment, you control how data is displayed within a cell:

• Create a strong edge to spreadsheets by left-aligning all text data and right-aligning all numerical data. This improves readability and graphics consistency.
• Don’t use centre align as this causes the text to graphically “float” in the cell.

3. Spacing, shading, and gridlines all play a crucial role in enhancing the readability and visual appeal of a document

• Use white space to improve readability. Add white space to spreadsheets giving data additional room by adjusting column width and height. The vertical height of rows can also be increased to provide data with additional white space.
• Shade alternate rows to improve readability, for example, using a light grey.
• Use gridlines sparingly, and don’t place too much emphasis on the individual cell. For example, use a solid line for row borders and a dotted line for the column borders. Think of the gridlines as a guide for the reader’s eye.

4. Headers identify columns and rows of data within the spreadsheet:

• Define headers to make them clear and legible. Header text can be capitalised and bolded.
• 'Wrap Text' as needed to wrap data or headers around the cell.

5. Carefully consider a colour palette, for example, one or two colours that work well together.

6. Various appearances that can be applied to a cell or the text contained within it:

• Create cell styles for consistency; for example, in MS Excel, use the “New Cell Style” button to customise and name a new style.
• Use 'Conditional Cell Formatting'. This is useful if your spreadsheet contains a lot of data, as you can use conditional formatting to highlight important information in a worksheet and enhance the presentation.

This article explains 9 tips on how to improve your spreadsheet.

How to format a cell

When you format a cell, you are changing the appearance of the value contained in that cell.

You can format a cell or a range of cells. First, click a single cell or drag-select a range of cells. Next, right-click on your selection. Finally, choose the ‘Format Cells…’ option from the drop-down menu. Doing this will open the Format Cells dialogue box. You can also open this dialogue box by selecting the cell you want to format and pressing [Ctrl + Shift + F].

The Format Cells dialogue box has six tabs containing different formatting options. The six tabs are:

Let us discuss each of these formatting options further.

Number

The Number tab contains options relating to the way you can present numerical values. You can set the number format of a cell or group of cells based on the type of data you need to reflect.

The following 6-minute video explains number formatting.

Excel Numbers

For example, if you are working with sales figures, you should use the ‘Accounting’ format. Excel provides a brief description and a sample of the final formatted appearance for each of the options.

The following table outlines how the different number categories will format your data. In general, numerical values will be right-aligned in the cell. This means that the value will be positioned on the right side of the cell. Where an option asks you to specify the number of decimal places, Excel will automatically round to the number of decimal places specified or add zeros to the right of the number if required.

Custom number formats

You can also use the Number tab to design custom formats. This allows you to use formatting codes to express your data in formats not specifically built into Excel. You can also edit existing number format pre-sets to better suit the data you want to present. This is a very powerful tool and gives you a great deal of control over the way Excel displays your numerical data.

For example, imagine you are developing a spreadsheet for Aussie Tool Shed that records the weight of large tools for shipping and storage purposes. You want your data to display numbers up to two decimal places, followed by ‘kg’ as the unit of measurement. To achieve this:

Using this custom formatting, a data entry, ‘3.124’, will now be displayed as ‘3.12 kg’.

Alignment

The settings found here allow you to modify the positioning of text found inside the cell. You can set the following:

Font

This tab allows you to control the font settings for the cell. You have the following options:

Border

This tab gives you complete control over the borders around and between cells in your selection. You have the following options:

When selecting line positions, they will relate to the cell or range of cells you have selected. When you select more than one cell, you can choose to place borders around just the outside of your selection or between the cells within the selection.

Fill

This tab allows you to set the background properties of the cells selected. You have the following options:

Protection

The protection tab gives you options to lock or hide cells in conjunction with the worksheet protection options available in Excel.

Locked cells appear and function as normal, but you require a password to edit them.

Hidden cells appear to be empty. However, if you have the correct access requirements, you can still see and edit the hidden cell in the formula bar. Hidden cells are a great way to hide confidential information, such as personal information.

To use locked or hidden cells, you must protect the worksheet first. Protecting a worksheet prevents editing. Using the locked cell formatting, you can control which parts of the sheet can and can’t be edited by other people.

The following 4-minute video explains how to protect a worksheet.

Protecting a sheet in Excel

Copying and pasting data attributes

Remember that the data attributes of a cell in a spreadsheet include:

When you format cells, you can use attributes from one cell and apply them to other cells instead of manually setting the formatting for each cell. To do this, follow these steps:

You can also double-click the ‘Format Painter’ button to toggle the function on. Now it will paste the attributes into any cells you select until you either click the ‘Format Painter’ button again or press the [Esc] key.

While the ‘Format Painter’ is useful, it always copies all the attributes to the destination cells. The ‘Paste Special’ tool allows you to select which attributes to copy and which to ignore.

Anytime you have the contents of a cell (or selection of cells) copied to the clipboard, you can right-click on a selection of other cells and select ‘Paste Special’ from the drop-down menu. This opens the ‘Paste Special’ dialogue box, which gives you the option to copy some or all the copied cells’ attributes to the selected cells.

For example, by using ‘Paste Special’, ‘Values’, you can copy the calculated value from a cell containing a formula without copying the formula itself.

Cell referencing

When developing spreadsheets, it is often useful to reference the data in one cell of the spreadsheet to use it as part of a formula or function in another cell. This can be done in two ways, relative cell referencing or absolute cell referencing.

Relative cell referencing

Relative cell referencing is the default form of cell referencing in Excel. When you copy a relative cell reference and paste it into a different cell, the referenced cell changes relative to the location of the new destination cell. In other words, the direction and number of cells between the reference cell and destination cell remain the same, but the actual cell locations shift.

For example, if you reference cells A1 and B1 in cell C1 using the formula, =A1+B1. The referenced cells and destination cells are in the same row, one column apart. If you copy this (relatively referenced) formula from C1 down to C3, you will get the formula =A2+B2 in C2 and =A3+B3 in C3.

This relative referencing has shifted relative to where it was copied from and where it was copied to. The referenced cells and destination cells are in the same row, one column apart. As you shifted one row down when copying it, the referenced cells A1 and B1 were shifted down one row as well so that the new formula in C2 referenced cells A2 and B2. This then continued as you shifted down another row to C3.

The same thing happens if you copy across a row. The relative referencing tracks the relative position of the cells.

Absolute Cell Referencing

Absolute cell referencing is different. It is used when you want the cell reference to stay fixed on a specific cell, row or column, regardless of where it is copied.

Absolute referencing is specified by using $ symbols before the column letter and/or row number. If you were to use absolute referencing in the previous example (by changing the formula to =$A$1+$B$1), you would get a different result, as shown in the following image.

This time, the cell references do not shift when you copy the formula to different cells; they are fixed on cells A1 and B1 specifically due to the use of the $ signs in the formula. So, no matter where you copy it, the formula will always be =$A$1+$B$1.

You have three options when using absolute cell referencing: you can use absolute row referencing, absolute column referencing, or absolute row and column referencing.

When using a partial absolute reference, the absolutely referenced row or column stays fixed, but the relatively referenced row or column shifts relative to where it is copied from and to.

You can turn a relative cell reference into an absolute cell reference by typing $ symbols into the formula or function. You can also click on the relative cell reference in the function bar and press [fn + F4]. This automatically adds the $ symbol to both the column and row references.

When using cell references in formulae and functions, it is important to test them to make sure they actually compute the data required by the spreadsheet task. This is particularly important when you are developing linked spreadsheets.

The following 9-minute video tutorial demonstrates cell referencing.

Practice Exercise

Download the following Excel file: ABC Pty Ltd – Practical Exercise

Using Absolute references, calculate:

After you have completed the practice exercise, you can check the answer here.

1. Various appearances can be applied to a cell or the text contained within it:
   • Create cell styles for consistency; for example, in MS Excel, use the “New Cell Style” button to customise and name a new style.
   • Use “Conditional Cell Formatting”. This is useful if your spreadsheet contains a lot of data, as you can use conditional formatting to highlight important information in a worksheet and enhance presentation.

• Number
• Alignment
• Font
• Border
• Fill
• Protection.

1. Select the cells you wish to format.
2. Open the ‘Format Cells…’ dialogue box.
3. Click the ‘Custom’ category.
4. Enter the following in the ‘Type’ text field:
   1. The number format with the desired number of decimal places (for this example, 0.00).
   2. The unit of measurement is displayed as text, enclosed in double quotation marks (For this example, “kg”)

• Horizontal alignment – adjusts the position of the value from left to right.
• Vertical alignment – adjusts the position of the value up and down.
• Indentation – adjusts the space between the value and the edge of the cell.
• Orientation – adjust the angle of the text.
• Text control – changes the way the cell responds to the value within it
  • Wrap text – automatically breaks long text into multiple lines to fit inside the width of the cell. The row height automatically adjusts.
  • Shrink to fit – automatically reduces the size of the font so that the value fits inside the width of the cell.
  • Merge cells – joins two or more consecutive cells together into a single large cell.
• Text direction – adjusts text to be read from lift to right or right to left.
• Font - change the style of writing, add italics, bold or both.
• Underline – add different types of underlining.
• Colour – change the colour of your text.
• Font effects – strikethrough, superscript or subscript.
• Normal font – selecting the normal font check box resets the font, font style, size, and effects to the normal (default) style.
• Line style
• Line colour
• Line positions – top, bottom, left, right, diagonal, horizontal and vertical borders.
• Fill colours
• Fill effects
• Fill pattern colours
• Fill pattern styles.
• Formula
• Value
• Format
• Comments
• Validation.

1. Select the source cell – the cell that contains the attributes that you want to copy. For example, 10C.
2. Click the ‘Format Painter’ button, which can be found on the ‘Clipboard’ category of the ‘Home’ tab of the ribbon.
   
3. Select the destination cell or cells – the cell or cells you want to apply the attributes to. In this example, the cell fill colour, bolding and financial formatting are applied to C12.
   

• $A1 is absolute referencing column A but relative referencing row 1.
• A$1 is relative referencing column A but absolute referencing row 1
• $A$1 is absolute, referencing both column A and row 1.
• The % of total sales for each salesperson
• The commission earned by each salesperson for Qtr. 4 2023

1. Spacing, shading, and gridlines all play a crucial role in enhancing the readability and visual appeal of a document:

   • Create a strong edge to spreadsheets by left-aligning all text data and right-aligning all numerical data. This improves readability and graphics consistency.
   • Don’t use centre align as this causes the text to graphically “float” in the cell.

As with any document, errors are possible when using formulae and functions – either through ‘typos’ or through incorrect applications of syntax. This follows the ‘GIGO’ principle (Garbage In, Garbage Out), which states that your output data quality is only as good as the data that is entered into the formula.

It is important to test each formula you incorporate into a spreadsheet to confirm that the data output from the formula meets the expected output and the task requirements.

Excel offers multiple ways to test that your formula is meeting task requirements. These can be found on the ‘Formula Auditing’ group of the ‘Formulas’ tab on the ribbon.

The following options can be found in the ‘Formula Auditing’ group and will be discussed in more detail:

• Trace Precedents
• Trace Dependents
• Remove Arrows
• Show Formulas
• Error Checking
• Evaluate Formula
• Watch Window

Trace precedents and dependents

The ‘Trace Precedents’ and ‘Trace Dependents’ buttons are ways for you to check how the formulae in your worksheet are connected to each other.

A precedent cell is a cell that is referenced in a function or formula in another cell. The cell precedes or is populated before the dependent cell.

A dependent cell is a cell that contains a function or formula that references other cells. The value in this cell depends on the value in the precedent cell.

The precedents and dependents are shown through arrows connecting different cells.

How to trace precedents

To trace the precedents of a cell, follow these steps:

1. Select the cell containing the formula that you want to trace. For example, if you want to trace the precedents of C20, select C20.
   
2. Click the ‘Trace Precedents’ button. Arrows will appear, pointing from the cells needed to compute the value in your selected cell.
   
   In this example, you can see that C9, C14 and C19 are precedents for C20, as indicated by the blue dots and arrows.
3. Click the ‘Trace Precedents’ button again. This will show the next level of cells that are connected to your selected cell. You can click the ‘Trace Precedents’ button repeatedly until all levels of precedents related to your selected cell are shown.
   
   In this example, Excel shows that the cells in the range B6:B9 are precedents for C9 by placing the range in a blue box. In the same way, B11:B14 are precedents for C14, and B16:19 are precedents for C19.
   

A third click of ‘Trace Precedents’ reveals that all of the values in column B also have precedents. However, these precedent cells are located on a different worksheet. This is indicated by dashed, black arrows and the spreadsheet icons.

How to trace dependents

A similar process can be followed when tracing dependents:

1. Select the cell with dependents that you want to trace. For example, if you want to trace the dependents of cell B6, select B6.
2. Click the ‘Trace Dependents’ button. You will see blue lines coming from your selected cell pointing toward all the dependents of that cell.
   
   In this example, the blue arrow indicates that C9 is a dependent of B6.
3. Click the ‘Trace Dependents’ button again to show the next level of dependents. You can click this button repeatedly until all levels of dependents related to your selected cell are shown.
   

If any dependents are located on a different worksheet, they will be indicated by a dashed black arrow and the spreadsheet icon.

Tip: If precedent or dependent cells are in another workbook, that workbook must be open for Excel to include in tracing.

The following 3-minute video explains formula auditing in Excel.

Trace all cell relationships

Excel also allows you to trace all relationships in your active sheet at once. To do this:

1. Type the equals sign [=] into an empty cell, near but not a part of the table that contains your data.
2. Click the ‘Select All’ button – the diagonally shaded square found in the top left corner between the column and row labels. Excel will highlight all the cells in your current sheet and enter ‘=1:1048576’ into your selected cell.
   
3. Click the ‘Tick icon’ in the ‘Formula Bar’. You may see a popup warning you that Excel cannot evaluate the formulas. Click ‘OK’. The cell value will now show as a ‘0’.
   
4. Make sure the ‘0’ cell is selected. Click the ‘Trace Precedents’ button twice. This will show all relationships in your active sheet. Delete the contents of the ‘0’ cell.

Once you are finished tracing the precedents and dependents of your worksheet, you can clear the arrows from the screen by clicking the ‘Remove Arrows’ button from the ‘Formula Auditing’ category of the ‘Formulas’ tab on the ribbon. You can also opt to remove only the precedent arrows or only the dependent arrows by clicking on the drop-down menu.

By making it a habit to trace precedents and dependents, you can ensure that the functions and formulae in your spreadsheet reference the correct cells. As a result, you are also assured that your spreadsheet output meets the task requirements.

In the example used here, you know that your quarterly and annual sales figures for Aussie Tool Shed are accurate because you were able to trace the cell references that were used to calculate the total values.

Show formulas

Another feature found in the ‘Formula Auditing’ category is ‘Show Formulas’, which was discussed briefly earlier in this topic. Clicking this button will show you all the functions and formulae used in the current worksheet. These functions and formulae will be displayed in the respective cells that contain them.

As with tracing precedents and dependents, you can use the ‘Show Formulas’ button to view all of the functions and formulae and confirm they are correct. Aspects of functions and formulae to check include whether:

• the syntax is correct for each function and formula
• the correct cells or cell ranges are used in each function and formula.

By doing so, you can ensure that the values in your spreadsheet will be computed and displayed correctly according to task requirements.

Error checking

Excel cannot compute functions and formulae if they contain errors in the following:

• syntax
• arguments
• cell references.

When Excel cannot compute a value, it will display an error code instead to let you know there is a problem. Error codes include:

• #DIV/0!
• #N/A!
• #NAME?
• #NULL!
• #NUM!
• #REF!
• #VALUE!

You do not need to learn what the different error codes mean. However, you should be able to recognise an error code in order to locate a function or formula that needs to be fixed.

The ‘Error Checking’ tool uses rules that help check formulae in your spreadsheets for errors. The ‘Error Checking’ button is located in the ‘Formula Auditing’ category of the ‘Formulas’ tab.

There are three different ways in which you can check errors in your spreadsheet. Excel provides you with the following options under the ‘Error Checking’ drop-down menu:

• Error Checking
• Trace Error
• Circular References

Error checking

If your worksheet contains any errors, clicking the ‘Error Checking’ button will display the ‘Error Checking’ dialogue box, as shown in the following example.

This dialogue box will show you which cells contain errors, which formulae are involved and a brief description of the error. From here, you can select one of the following buttons to resolve the error:

• ‘Help on this Error’ – This will launch the Microsoft Support website, which will give you information and examples based on the error code.
• ‘Show Calculation Steps’ – This will open the ‘Evaluate Formula’ dialogue box. Here you can see, step-by-step, how Excel calculated the formula you entered and identify the step(s) where the error occurred.
• ‘Ignore Error’ – This removes the error tag on the cell so that it does not appear in future error checks.
• ‘Edit in Formula Bar’ – This redirects your cursor to the formula bar so that you can manually edit your formula.

If there are multiple errors on one spreadsheet, you can inspect and fix each of them by clicking ‘Next’ and ‘Previous’ in the ‘Error Checking’ dialogue box.

Trace error

To use the ‘Trace Error’ function, select a cell that contains an error, then click the ‘Trace Error’ button. Excel then displays arrows that show you which cells caused the error value on your selected cell. From the arrows in the following example, you know that B16 caused the error on C16, which, in turn, caused the error on C17.

You can use the ‘Remove Arrow’ to remove the error traces when you are finished.

Circular references

A circular reference happens when a formula references the cell it is entered into.

The following image shows an example of a circular reference. The formula is entered on C17. Notice, however, that C17 is also part of the formula. In this case, Excel will not calculate a final value. Instead, it will tag this as a circular reference, and you may see an error warning popup.

You can check your spreadsheet for circular references by going to the drop-down menu of the ‘Error Checking’ button and clicking on ‘Circular References’. A list of cells containing circular references will be displayed, as shown in the following image.

Error checking to confirm output meets task requirements

There are two (2) main types of errors in spreadsheets:

• Errors that prevent the spreadsheet software from performing the computation will display an error code instead of a value.
• Errors that can be computed but do not produce the correct value because they contain a typing mistake or reference the wrong cell can be trickier to locate.

Tracing precedents and dependents will help you identify these types of errors.

Performing regular error checks will help you ensure that all values contained in cells are correct. Errors can affect multiple cells or entire worksheets and workbooks if not identified and corrected. When you encounter errors in your document, use the error-checking tools available in the spreadsheet software to help you fix them. By doing so, you can ensure that the functions and formulae in your spreadsheet result in accurate values. This means that your spreadsheet output, as a whole, is also an accurate document that addresses the task requirements.

Evaluate formula

The ‘Evaluate Formula’ tool in the ‘Formula Auditing’ category on the ‘Formulas’ tab of the ribbon of Excel walks you through the steps used in testing a result from a formula. This helps locate errors in long formulae or those that have multiple levels of precedents.

As an example, say you wanted to evaluate the formula used in C20 to calculate the annual sales total for Aussie Tool Shed’s business customers. When you select C20 and click ‘Evaluate Formula’, the ‘Evaluate Formula’ dialogue box will appear.

In the dialogue box, you will see the absolute cell reference on the left side and the evaluation box on the right. The evaluation box will first show you the initial formula that you entered in that cell. In the previous example, you can see the initial formula, =SUM(C9, C14, C19), appears inside the evaluation box.

Clicking the ‘Evaluate’ button repeatedly will walk you through the steps that Excel used to arrive at the final value using the formula that you entered. When the evaluation is finished, you will see that the ‘Evaluate’ button will be replaced with the ‘Restart’ button, which you can click to evaluate the formula from the beginning.

You will notice that certain parts of the formula are underlined for each step in the evaluation. This means that there are other functions or formulae nested in that part of the formula. In the following example, B6 is underlined. This means that B6 contains another formula. You can check and evaluate this formula separately by clicking on the ‘Step In’ button.

In this example, the image on the right shows that B6 contains the function =SUM(B7:B9).

Clicking the ‘Step Out’ button will return you to the original formula, now with the nested formula evaluated, as shown in the following image.

As you use more complex functions and formulae in your spreadsheets, you will realise the importance of evaluating formulae. There may be times when you need to reference an existing function or formula in a new function or formula. This is called ‘nesting’. The ‘Evaluate Formula’ button will help you spot any errors or inconsistencies that come from nested functions or formulae. By evaluating formulae, you can ensure that the functions and formulae you have entered are calculating the correct values, even as you add more data to your spreadsheet. This means that the computations in your spreadsheet will always produce output according to task requirements.

The following 21-minute video explains 5 tips to evaluate advanced formulas in Excel:

Evaluating formula with F9

Additionally, the F9 key serves as a powerful tool for debugging formulas and conducting interactive scenario analysis.

To evaluate a formula in Excel using the F9 key, follow these steps:

1. Select the cell: Click on the cell containing the formula you want to evaluate.
2. Enter edit mode: Press F2 to enter the cell's edit mode. This allows you to see and edit the formula directly within the cell.
3. Highlight the part of the formula: Using your mouse or keyboard, highlight the specific part of the formula you want to evaluate. If you want to evaluate the entire formula, skip this step.
4. Press the F9 key: With the part of the formula highlighted, press F9. Excel will replace the selected portion with its calculated result.
5. Review the result: Observe the result displayed in the formula. This helps in understanding how Excel processes that part of the formula.
6. Undo the evaluation (Optional): If you want to revert to the original formula, press Ctrl + Z immediately to undo the change and restore the formula.
7. Exit edit mode: Press Enter to exit edit mode and retain any changes, or press Esc to exit without saving changes.

The following 3-minute video demonstrates how to use F9 for evaluating formulas:

Watch window

The ‘Watch Window’ tracks the changes in the value of a particular cell. This is a convenient tool to use when you are working with multiple spreadsheets at the same time or spreadsheets with many rows and columns that do not fit into the current view on your screen.

The following image shows an example of a ‘Watch Window’. From this window, you know that you are tracking cell C20. The ‘Watch Window’ gives you information on the workbook title, worksheet title, cell name, cell reference, value and formula. Any changes you apply to the cell will be updated on the watch window.

You may track multiple cells in the same window by clicking on the ‘Add Watch’ button. You may also delete a watch if you no longer need it.

The ‘Watch Window’ will be open and visible even if you open new worksheets or workbooks. You can use this to keep track of cells that hold critical data and formulae and make sure that they do not result in errors as you edit your spreadsheet. The ‘Watch Window’ is a useful tool for tracking cells that contain values that change regularly as you enter data.

For example, if your spreadsheet output will display multiple total values, it would be helpful to track these totals in the watch window. That way, you can easily check if the totals are correctly updated as you add more data. By doing this, you can ensure that the spreadsheet output contains accurate data and meets task requirements.

Practice Exercise

Download the following Excel file and the Sportique Pty Ltd practice exercise instructions to complete this exercise.

• Excel File - Sportique Pty Ltd
• Sportique Pty Ltd practice exercise instructions

After you have completed the practice exercise, check the answer here.

For this activity, you will create your first spreadsheet, an Aged Debtor Report. Through this activity, you will demonstrate your ability to use basic design features to enhance the readability and appearance of the spreadsheet

To begin with this task, carefully follow the steps below:

• Open Microsoft Excel
• Open a new workbook
• Save the workbook as ‘BSBTEC402 –Design and Produce Complex Spreadsheets –Spreadsheet 1 Aged Debtor Report’
• In cell A1, write the title of the workbook as ‘Aged Debtor Report A-5’

Copy and paste the contents of the report (Aged Debtor Report - CBSA) into the spreadsheet from cell A2 onwards
Format the text so that it contains:

• The same colour scheme (look at the extract below)
• Arial font–Size 10

• Format the ‘Aged Debtor Report A-5’ heading so that it is bold
• Adjust the rows to size 25▪Adjust the columns to fit the text
• Right-click on the worksheet tab at the bottom of the document, currently titled ‘Sheet 1’ and rename the tab ‘Aged Debtor Report A-5’
• Save your work (it is good practice to do this as you go)

Learning Checkpoint Answer

This is an example of how your spreadsheet should look: