Using Multiple Worksheets
The default number of worksheets in a work book is three; the maximum number of worksheets is limited only by the memory capacity of your PC. This topic is covered in details in our Excel Intermediate course ( Excel Intermediate Toronto– Excel Intermediate Online Training)
The Quick Analysis Button
To create a new worksheet, click the Insert Worksheet tab to the right of the existing sheet tabs in your Excel workbook (or use the shortcut keys Shift + F11). Right-click on a sheet tab to get to the menu below
Excel Quick Analysis Button
Navigating between sheets
The navigation buttons to the left of the sheet tabs are used to assist with viewing sheet tabs.
- Insert: Inserts a sheet tab to the left of the present sheet
- Delete: Permanently deletes the sheet, cannot delete if there is only one sheet
- Rename: To display a different name on the sheet tab
- Move or Copy: Move or copy the sheet within the Excel workbook or to a different workbook
- View Code: View the VBA code in any module in the workbook
The following shortcut keys are also useful:
Navigating between Excel sheets
- Ctrl +Page Down Make the next tab to the right active
- Ctrl + Page Up Make the next tab to the left active
Creating 3-D formulas
Instead of calculating with only rows and columns on a single sheet, we can create formulas that reference cells on multiple sheets.
A reference that refers to the same cell or cell range on multiple sheets is called a 3-D reference. All 3D formulas are based on the syntax: Sheet1:Sheet4!A2:B5.
To create a 3-D formula using a function:
- Select the cell where the formula is to be entered
- Type = (equal sign)
- Type in the function name (e.g. SUM) followed by an open bracket Click on the sheet tab of the first worksheet to be entered into the formula
- Hold down the Shift key and click on the sheet tab for the last worksheet to be entered into the formula
- On the sheet you have just selected, highlight the cell or range of cells to be included in the formula
- To complete the formula, and press the Enter key
- Example: The 3D formula “=SUM(Sheet1:Sheet4!A2)” can be used to add up the numbers in cell “A2” on 4 different worksheets. If you copy or insert a new worksheet after Sheet1 the reference will automatically include it. Similarly if you delete a worksheet it will be excluded.
It is important to realize that not all the functions will accept a 3D formula.
The following functions can all be used in 3D formulas.
- AND The logical AND for any number of arguments.
- AVEDEV The average deviation for a list of numbers.
- AVERAGE The arithmetic mean of a list or array of numbers.
- AVERAGEA The arithmetic mean of a list or array of numbers, including text and logical values.
- COUNT The number of cells with a numeric value in a list or cell range.
- COUNTA The number of non blank cells in a list or cell range.
- DEVSQ The sum of squares of deviations of data points from their sample mean.
- LARGE The Kth largest value in an array of numbers.
- MAX The largest value in a list or array of numbers.
- MAXA The largest value in a list or array of numbers, including text and logical values.
- MEDIAN The median of the numbers in a list or cell range.
- MIN The smallest number in a list or range.
- MINA The smallest number in a list or range, including text and logical values
- OR The logical OR for any number of arguments.
- PERCENTILE The Kth percentile of values in a range.
- PRODUCT The product of all the numbers in a list or cell range.
- QUARTILE The quartile of a data set.
- RANK The rank of a value in a range (in descending order).
- SKEW The number representing the skewness of a distribution.
- SMALL The Kth smallest value in an array of numbers.
- STDEV The standard deviation based on a sample.
- STDEVA The standard deviation based on a sample, including text and logical values.
- STDEVP The standard deviation based on an entire population.
- STDEVPA The standard deviation based on an entire population, including text and logical values.
- SUM The total value of the numbers in a list or cell range.
- SUMSQ The sum of the squares of all the values in a list or cell range.
- TRIM The text string with all spaces removed from the beginning and end.
- VAR The compound variance based upon the numerical values in the range.
- VARA The compound variance based upon the numerical values in the range.
- VARP The variance based on an entire population.
- VARPA The variance based on an entire population, including text and logical values
Consolidating data
When you consolidate worksheets, you are combining, condensing, and summarising data from multiple sheets into one sheet. For example, a large company may have sales or profit data broken down into several different geographical locations. Each worksheet would have the same layout and structure (template) but contain different data.
Consolidating data
To consolidate worksheets:
- Open the relevant workbook
- Select a destination sheet where the data will be consolidated. It is a good idea to set up the destination worksheet to be just like the worksheets that will supply the data. This means having the same labels and using the same layout and cell locations for the same types of data.
- Select the cell at the upper left of the range that will contain the consolidated data.
- Select the Data Ribbon, and click the Consolidate button in the Data Tool
To consolidate worksheets..
- Under the word Function, select the function you wish to use to combine the data.
consolidate Excel worksheets
- The text field under the word Reference is where you enter the ranges of data to be consolidated. Select the ranges from the different worksheets with your mouse or keyboard.
- When a range is entered, click the Add button to add it to the All References field.
- Check the “Use labels in” boxes to consolidate sheets based on row labels or column labels
- Check the “Create links to source data” if you wish to have the consolidated data updated.
- Once all options are set, make sure the worksheets and cell ranges to be consolidated are correct, and click OK.
- The worksheets will be consolidated and combined into the destination worksheet
Linking your Excel workbooks
To properly manage your workbooks, you should be able to combine or consolidate data from more than one workbook or worksheet. By doing this, you can provide a clear and unified picture of data that may be spread over multiple source.
Linking Workbooks
A cell reference like K7 simply refers to the cell at the intersection of column K and row 7. A cell reference like Store!K7 refers to the cell at column K and row 7 in the worksheet named Store. Taking this one step further, the reference [Sales.xlsx]Store!K7 refers to cell K7 in worksheet Store in the workbook Sales.
In Excel, a cell in one worksheet can contain a reference to a cell in another worksheet or in another workbook. This kind of linking can be useful if you have several sheets that have to reference the same pool of data. If you create links, the linked cells in the other worksheets or workbooks will be updated automatically when you update the source data
Using the Paste Link option
To link two Excel workbooks:
- Open both workbooks and select the cell or range of source data that you want to use from one workbook.
- Right click on the cell or range and select Copy from the menu.
- Right click on the cell in the destination workbook that will contain the linked data. (If you are linking a range of data, right click on the top left cell of the destination range.)
- Select Paste Special from the pop up menu
- Click on the Paste Link button in the lower left corner of the Paste Special dialogue box.
- The linked data will now appear in the destination workbook. If you update the source data, the linked data will be updated as well.
Creating a formula to link Excel workbooks
There is also another way to create links between workbooks – by creating a formula.
- Click on the destination cell and press the = key
- Click the source data cell in another workbook
- Press the Enter key.
- The two workbooks should now be linked, and any data or changes that appear in the source cell will be seen in the linked cell as well
Controlling link updates
When the source workbook and the destination workbook are open on the same computer, links are updated automatically. When you open a destination workbook, and the source workbook is not open, you may be alerted by the Trust Bar (see below) whether to update the links.
You can control whether the Trust Bar alerts you, and whether to update all links when the alert does not appear. You can also update only some of the links, if the workbook contains more than one link
Controlling link updates in Excel
Manually update all of the links or none of the links in a workbook
Close all Excel workbooks. If one source workbook is open, and others are closed, the updates will not be uniform. Open the workbook that contains the links. To update the links, on the Trust Bar, click Enable this Content.
Manually update all of the links in a Excel workbook
Manually update only some of the links to other workbooks
Close all workbooks. Open the workbook that contains the links. On the Data tab, in the Connections group, click Edit Links. In the Source list, click the linked object that you want to update.
• To select multiple links, hold down CTRL and click each linked object.
• To select all links, press CTRL + A.
• Click Update Values
Dealing with Broken Links
If the following message appears on your screen when you are attempting to update a linked file, this means that Excel cannot find the source file. This may be because the source file has been renamed or moved to a different location.
To re-establish the link between the two files:
- Click the Edit Links option to open the Edit Links dialogue box.
- Select the Change Source button from the Edit Links dialogue box.
- This will allow you to navigate to the source file. Once you have located the source file, select it and click OK.
- You will then see that the link has been re-established (note OK showing under Status). Any updates of data should have been completed.
- Click the Close button.
This topic is covered in details in our Excel Intermediate course ( Excel Intermediate Toronto– Excel Intermediate Online Training)
