Practical 17 – Mastering Workbook References

1. Objective

The primary objective of this practical is to equip students with the ability to create and manage references between different parts of an Excel workbook and between entirely separate Excel workbooks. Students will learn to:

·         Reference cells and ranges on different worksheets within the same workbook.

·         Reference cells and ranges in external Excel workbooks.

·         Understand the syntax for internal and external references.

·         Utilize 3-D references to aggregate data from multiple worksheets.

·         Manage, update, and break external links.

·         Appreciate the importance of linked data for consolidation and reporting.

2. Introduction

In real-world data management, information is often spread across multiple sheets within a single workbook or even across several different workbooks. Instead of manually copying and pasting data (which can lead to errors and makes updates difficult), Excel allows you to create dynamic links, or "references," between these locations. This means that a change in the source data will automatically update any linked cells, ensuring consistency and efficiency. Mastering workbook references is essential for building complex, interconnected models, consolidating reports, and maintaining data integrity across large projects.

3. Pre-requisites

·         Basic understanding of Microsoft Excel interface.

·         Familiarity with basic formula entry and cell references.

·         Ability to open, save, and navigate Excel workbooks and worksheets.

4. Software Required

·         Microsoft Excel

5. Procedure: Practical Steps with Examples

We will start with a new workbook and then create another workbook to demonstrate references.

Step 5.1: Setting Up the First Workbook

1.     Open Excel: Launch Microsoft Excel and select Blank workbook.

2.     Save Workbook: Save this workbook as Main_Report.xlsx to your desktop or a designated folder.

3.     Rename Sheets:

o    Rename Sheet1 to Q1_Sales.

o    Rename Sheet2 to Q2_Sales.

o    Rename Sheet3 to Annual_Summary.

4.     Enter Data on Q1_Sales:

o    On the Q1_Sales sheet, enter the following data:

Product	Jan	Feb	Mar	Total Q1
A	100	120	110
B	50	60	55
C	75	80	70
Total Sales

5.     Enter Data on Q2_Sales:

o    On the Q2_Sales sheet, enter the following data:

Product	Apr	May	Jun	Total Q2
A	130	140	125
B	65	70	60
C	85	90	78
Total Sales

Step 5.2: Referencing Cells on Another Worksheet

We will calculate the Total Q1 and Total Sales for Q1 on the Q1_Sales sheet, and then summarize Q1 totals on the Annual_Summary sheet.

1.     Calculate Total Q1 on Q1_Sales:

o    In cell E2 on Q1_Sales, type =SUM(B2:D2) and press Enter.

o    Drag the fill handle down to E4.

2.     Calculate Total Sales for Q1:

o    In cell E5 on Q1_Sales, type =SUM(E2:E4) and press Enter.

3.     Reference Q1 Totals on Annual_Summary:

o    Go to the Annual_Summary sheet.

o    In cell A1, type Annual Sales Report.

o    In cell A3, type Quarter 1 Total:.

o    In cell B3, type =Q1_Sales!E5 and press Enter.

§  Explanation: Q1_Sales! indicates the sheet name, and E5 is the cell on that sheet. The exclamation mark separates the sheet name from the cell reference.

4.     Reference Total Q2 on Annual_Summary:

o    First, calculate Total Q2 on the Q2_Sales sheet in the same way you did for Q1_Sales (i.e., E2 =SUM(B2:D2) and E5 =SUM(E2:E4)).

o    Go back to Annual_Summary.

o    In cell A4, type Quarter 2 Total:.

o    In cell B4, type =Q2_Sales!E5 and press Enter.

o    Observation: Changes made to the sales data on Q1_Sales or Q2_Sales will automatically update the Annual_Summary.

Step 5.3: Referencing Cells in Another Workbook

Now, let’s create a second workbook and link data from it to our Main_Report.xlsx.

1.     Create Second Workbook:

o    Open a new blank workbook (Ctrl+N).

o    Save this new workbook as Budget_Data.xlsx in the same folder as Main_Report.xlsx.

2.     Enter Data on Sheet1 of Budget_Data.xlsx:

o    Rename Sheet1 to Budget.

o    In cell A1, type Product A Budget.

o    In cell B1, type 500.

o    In cell A2, type Total Annual Budget.

o    In cell B2, type 1500.

3.     Reference Budget_Data.xlsx from Main_Report.xlsx:

o    Go back to Main_Report.xlsx and select the Annual_Summary sheet.

o    In cell A6, type Product A Budget:.

o    In cell B6, type =.

o    Now, without closing Main_Report.xlsx, switch to Budget_Data.xlsx.

o    Click on cell B1 on the Budget sheet.

o    Press Enter.

§  Observation: Excel will automatically insert the external reference formula into B6 of Annual_Summary: ='[Budget_Data.xlsx]Budget'!$B$1.

§  Explanation:

§  [Budget_Data.xlsx] is the name of the source workbook.

§  Budget is the name of the source worksheet within that workbook.

§  $B$1 is the absolute cell reference. Excel uses absolute references by default for external links.

4.     Close Budget_Data.xlsx: When prompted, save changes.

o    Observation: The link in Main_Report.xlsx will still work. If you open Main_Report.xlsx again without Budget_Data.xlsx being open, you might get a security warning about "links to other workbooks". Click Enable Content or Update to refresh the links.

Step 5.4: Managing External Links

Excel provides a tool to manage all external links in a workbook.

1.     Access Edit Links:

o    In Main_Report.xlsx, go to the Data tab.

o    In the Queries & Connections group, click Edit Links..

2.     Edit Links Dialog Box:

o    You will see Budget_Data.xlsx listed as a source.

o    Update Values: Click Update Values to refresh the data from the source workbook.

o    Change Source: If Budget_Data.xlsx was moved or renamed, you could use Change Source... to point to the new location or name.

o    Open Source: Opens Budget_Data.xlsx.

o    Break Link: This converts the formula into its current value, effectively removing the dynamic link. The cell B6 would then just contain 500, not the formula.

§  Caution: Breaking links is irreversible (unless you use Undo immediately).

o    Click Close.

Step 5.5: Using 3-D References

3-D references allow you to reference the same cell or range across multiple contiguous worksheets. This is ideal for summing up data across identical report structures.

1.     Create New Sheets in Main_Report.xlsx:

o    Insert a new sheet and rename it Q3_Sales.

o    Insert another new sheet and rename it Q4_Sales.

o    Make sure Q1_Sales, Q2_Sales, Q3_Sales, Q4_Sales are contiguous in your sheet tabs (e.g., Q1_Sales, Q2_Sales, Q3_Sales, Q4_Sales, Annual_Summary). You can drag sheet tabs to reorder them.

2.     Populate Q3_Sales and Q4_Sales:

o    Copy the structure and data from Q1_Sales to Q3_Sales and Q4_Sales, changing the month headers and values appropriately (e.g., Jul, Aug, Sep for Q3; Oct, Nov, Dec for Q4). Ensure the Total Q column is calculated using SUM for each quarter. Also, calculate the Total Sales (E5) for each new quarter.

3.     Calculate Annual Sales using 3-D Reference:

o    Go to the Annual_Summary sheet.

o    In cell A8, type Annual Total Sales (3-D Ref):.

o    In cell B8, type =SUM('Q1_Sales:Q4_Sales'!E5) and press Enter.

§  Explanation: Q1_Sales:Q4_Sales specifies a range of sheets. !E5 indicates that for each sheet in that range, we want to sum the value in cell E5.

o    Observation: This single formula sums the Total Sales from E5 of all four quarterly sales sheets.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks:

1.     Departmental Sales Consolidation:

o    In Main_Report.xlsx, create two new sheets: DeptA_Sales and DeptB_Sales.

o    Populate each with similar data to the Q1_Sales sheet, but for different products and monthly sales figures for each department. Include a Total column for each row and a Grand Total for each department.

o    Create a new sheet called Dept_Summary. On this sheet, link the Grand Total of DeptA_Sales and DeptB_Sales to show the overall departmental performance.

2.     Product Cost Lookup:

o    Create a new Excel workbook named Product_Costs.xlsx.

o    On Sheet1 (rename to Costs) of this workbook, list Product ID (e.g., P001, P002, P003) and their Unit Cost.

o    In your Main_Report.xlsx, on a new sheet called Cost_Analysis, list the same Product IDs. Use an external reference (and perhaps VLOOKUP if you want to be advanced) to pull the Unit Cost for each product from Product_Costs.xlsx.

3.     Quarterly Product Aggregation (3-D Reference):

o    On the Annual_Summary sheet in Main_Report.xlsx, create a new section that shows Annual Sales for Product A, Annual Sales for Product B, and Annual Sales for Product C.

o    Use 3-D reference formulas (e.g., =SUM('Q1_Sales:Q4_Sales'!B2) for Product A Jan-Mar sales, then drag right and down for other products/quarters) to sum the individual product sales across all four quarters (Q1, Q2, Q3, Q4).

4.     Update and Break Link Practice:

o    Open Budget_Data.xlsx. Change the Product A Budget to 550. Save and close Budget_Data.xlsx.

o    Open Main_Report.xlsx. Observe the prompt to Update links. Update them. Verify the change.

o    Go to Data > Edit Links and Break Link for Budget_Data.xlsx. Observe what happens to the cell containing the Product A Budget.

7. Assessment / Deliverables

·         Submit the completed Excel workbooks (Main_Report.xlsx and Budget_Data.xlsx, Product_Costs.xlsx) with all internal and external references correctly implemented.

·         Ensure all summary sheets reflect accurate data linked from their respective sources.

·         Be prepared to demonstrate how to create, manage, update, and break both internal and external links.

·         Be able to explain the syntax of different reference types and when to use them.