Excel Intermediate Training Course London

Managing Multiple Worksheets 

Using Multiple Worksheets 

The default number of worksheets in a workbook is one; the maximum number of worksheets is limited only by the memory capacity of your PC. 

To create a new worksheet, click the Insert Worksheet tab to the right of the existing sheet tabs in your workbook (or use the shortcut keys Shift + F11). 

Navigating between sheets 

The navigation buttons to the left of the sheet tabs are used to assist with viewing sheet tabs. 

The following shortcut keys are also useful: 

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 3D reference. 

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 

• Highlight the cell or range of cells 

• Press Enter 

Working with Dates & Formats 

Inserting and formatting dates 

Once a date and/or time has been entered into a cell, various format styles can be applied to the data. 

To apply a format: 

• Enter a date into an empty cell and press <Enter> 

• Widen the column, if necessary 

• Press <up arrow> to move back to the cell with the date 

• Go to the Home tab, Number group 

• Launch the Format Cells dialogue box 

• On the Number tab select the Date Category 

• Select the desired date format from the Type section 

• Press <Enter> or click on [OK] 

Entering date functions 

There are numerous date and time functions in Excel. These can be viewed by selecting the Formulas ribbon and clicking the Date & Time button. 

Two common functions are: 

- =NOW() – Gives today's date and time 

- =TODAY() – Gives today's date 

Using dates in formulas 

Because dates (and times) are stored as numbers, they can be used in calculations – for example, it's easy to work out the number of days between two dates or calculate an employee's wages given an hourly wage rate. 

Conditional Formatting Essentials 

Applying Conditional Formatting 

To use conditional formatting: 

• Select a range of data that you want to apply the formatting to 

• On the Home ribbon, Styles group, click the Conditional Formatting button 

• Choose from the menu of conditional formatting options 

Conditional Formatting Options 

Highlight Cells Rules – highlight cells greater than, less than, between, or equal to specified values 

Top/Bottom Rules – highlight the top or bottom numbers or percentages 

Data Bars – display coloured bars that indicate the value in the cell 

Colour Scales – use colour shading to represent values from low to high 

Icon Sets – use icons that visually indicate value categories 

Creating a Conditional Formatting Rule 

Clicking the New Rule option opens the New Formatting Rule dialogue, where you can configure more precise and specific formatting rules. 

Removing Conditional Formatting 

Use the Clear Rules option from the menu to remove rules for selected cells or the entire sheet. 

Creating Charts 

Creating a Chart 

Excel provides chart buttons and controls on the Insert Ribbon. 

To create a chart: 

• Select the data that you want to use in your chart 

• Select the Insert tab 

• Click the relevant chart type button to display chart options 

Manipulating a Chart 

To resize a chart, single click in the chart area to display the border, then drag a corner. 

To move a chart, hover over the border until the pointer becomes a four‑arrow cross, then drag. 

Moving a Chart to a Chart Sheet 

• Right-click on the chart 

• Choose Move Chart 

• Select New Sheet or a different worksheet from the dropdown 

• Click OK 

Removing a Chart 

• Click in the chart area 

• Press Backspace or Delete