Excel Dimensions

This course covers aspects of Microsoft Excel beyond building basic workbooks and deals with workbook automation and productivity features. It includes areas such as workbook and worksheet consolidation and linking, data analysis tools, and automating workbook operations using macros.
For an Acrobat format PDF of the course outline, click here.

Nesting Functions Workshop

Having learned how to create “If” and “Lookup” functions, you have now added a powerful dimension to your worksheets but having them scattered throughout many cells can be a problem. The Nesting Functions Workshop shows you how to imbed functions within other functions to increase the power of your spreadsheets, so many of these functions can exist within a single cell.

1. Scoping A Formula
2. Developing A Nested Function
3. Creating A Nested Function
4. Editing A Formula With Nested Functions
5. Copying A Formula With Nested Functions
6. Concatenation

Advanced Filters

After using Excel’s Auto Filter tools, many users believe they must graduate to Microsoft’s Access database software package when they wish to perform database filtering involving more complex “and” and “or” structures. Most, though, aren’t aware this can be done with Excels built-in Advanced Filters, saving enormous time and resources in the process.

1. Advanced Filter Concepts
2. Using An Advanced Filter
3. Extracting Records With Advanced Filter
4. Using Formulas In Criteria
5. Using Database Functions

Validations

If you want to control the values that can be entered into a cell, Validation tools will help. They enable you to provide instructions about what can and can’t be entered into the cell and restrict the possibility of entering the wrong thing. Error messages will be displayed should a wrong value be entered or a restricted drop down list of alternatives can be created.

1. Validation Techniques
2. Data Validation By Number Range
3. Testing Data Validation
4. Input Messages
5. Creating Error Alerts
6. Creating Drop-Down Lists
7. Using Formulas As Validation Criteria
8. Creating Custom Validation Criteria
9. Number Formats With Built-in Logic
10. Conditional Formatting
11. Copying Data Validation Settings

Goal Seeking

One annoying problem you may encounter while using Excel is where you know the answer you want within a single formula cell, but you don’t know the input value the formula needs to determine the result. Goal Seek solves this problem. You tell it what answer you want and Goal Seek will tell you how to get that result.

1. Goal Seek Components
2. Using Goal Seeking

Data Tables

If you need to view the results of many input variables on a single formula, making many copies of a formula will help, but there’s a drawback – it can be long-winded, will take up a lot of screen space and can cause mistakes. Data Tables will save you a lot of time, by letting you calculate multiple versions of your formula in one operation, and view and compare the results of all of the different variations together on your worksheet, all by using a single formula.

1. Using A Simple What-If Model
2. Creating A One-Variable Table
3. Using One-Variable Data Tables
4. Creating A Two-Variable Data Table

Scenarios

One of the powerful what-if analysis tools, Scenarios allow you to create sets of values that Microsoft Excel saves, and can substitute automatically in your worksheet. You can use scenarios to forecast the outcome of a worksheet model and, having saved alternate scenarios, you can then switch to any of these new scenarios to view different results.

1. Creating A Default Scenario
2. Creating Scenarios
3. Using Names in Scenarios
4. Displaying Scenarios
5. Creating A Scenario Summary Report
6. Merging Scenarios

PivotTables

One of the most powerful business tools, Pivot Tables let you automatically sum, rearrange and filter your Excel database, giving you almost unlimited scope to display your data any way you wish. Its “drag-and-drop” tools are easy to use, and Pivot charts take this one step further, allowing you to chart your data. This single tool can reduce hours of data manipulation to seconds.

1. PivotTable Theory
2. Creating A Simple PivotTable
3. Adding A Row Field To A PivotTable
4. Using The Page Field In A PivotTable
5. Filtering Row And Column Values
6. Formatting A PivotTable
7. Counting With PivotTables
8. PivotTable Summary And Display Options
9. Show Data As Percentages In PivotTables
10. Calculated Fields In PivotTables
11. Calculated Items In PivotTables
12. Creating A PivotChart
13. Modifying A PivotChart Via The PivotTable

Linking Workbooks

Most Excel users confine their calculations to a single Spreadsheet. What can add power to your spreadsheets is the ability to link to cells within other worksheets, and even to cells within different workbook files. In Data Linking, we look at it’s benefits, and how to do it.

1. Linking Data In Excel
2. Linking Within A Workbook
3. Linking Between Workbooks
4. Updating Links Between Workbooks

Consolidation

Having learned how to link single cells from one Excel file to another, Consolidation takes you one step further by allowing you to link and automatically sum the cells from multiple files into a single cell within a target file. It’s a tool that updates your target file as soon as you open it, allowing you to avoid time wastage from performing multiple copy and paste operations.

1. Consolidating Data With Identical Layouts
2. Consolidating Data With Different Layouts
3. Consolidating Data Using A PivotTable

Excel On The Web

With the explosion of web and intranet sites, there’s often a need to include table-format data within existing web-pages. Using Excel’s simple web tools, you are able to create and publish instant spreadsheets, which can be used to both display your data and allow web users to interact with the data.

1. Previewing Workbooks As Web Pages
2. Publishing A Static Worksheet
3. Adding To An Existing Web Page
4. Publishing An Interactive Web Page

Summarising Data

Subtotaling data can be a lengthy process. The group of tools in this section will rapidly increase the time it takes for you to calculate single and multiple groups of data.

1. Creating Subtotals
2. Using A Subtotaled Worksheet
3. Creating Nested Subtotals
4. Copying Subtotals
5. Using Subtotals With AutoFilter
6. Installing The Conditional Sum Wizard
7. Using The Conditional Sum Wizard
8. Creating Relative Names For Subtotals
9. Using Relative Names For Subtotals

Recorded Macros

Performing repetitive tasks leads to tiredness and frequent mistakes. Macros will solve this problem for you by allowing you to “record” a series of steps, then play them back at the click of a button, or the press of a key. It can perform lengthy tasks instantly. Within this section we also look into Visual Basic for Applications (VBA), an enormously powerful programming language into which our macros are converted.

1. Setting Macro Security Levels
2. Recording A Simple Macro
3. Running A Recorded Macro
4. Relative Cell References
5. Running A Macro With Relative References
6. Viewing A Macro Module
7. Modifying A Recorded Macro

Recorder Workshop

In this section you’ll build further macro examples, including creating buttons on your spreadsheet that, when pressed, will run your macros.

1. Preparing Data
2. Recording Summation Macros
3. Recording Consolidations
4. Recording Divisional Macros
5. Testing Macros
6. Creating Objects To Run Macros
7. Assigning A Macro To An Object

Macro Virus Control

Before we create macros, you must understand their security aspects, and in this section we look at Excel’s options for enabling and disabling macro capability by adjusting it’s security levels.

1. Opening Workbooks With Macros
2. Setting Macro Security Levels
3. Trusting Publishers
4. Creating A Digital Signature
5. Removing A Trusted Publisher