Excel Dimensions

This course focuses on the more advanced features of Excel 2007. Advanced analysis tools, especially pivot tables, Solver, outlining and summarising are covered, as are some key automation features such as Macros.
For an Acrobat format PDF of the course outline, click here.

Lookup Functions

One of the most popular productivity tools within Excel, the Lookup series (including Choose, Vlookup and Index) will help you reach into a table or database, extract information and report on the extracted data to other cells in your spreadsheet. Along with Logical functions, these are the tools most used in higher level worksheets, yet are easy to build and modify.

1. Understanding Data Lookup Functions
2. Using CHOOSE
3. Using VLOOKUP
4. Using VLOOKUP For Exact Matches
5. Using HLOOKUP
6. Using INDEX
7. Using MATCH
8. Understanding Reference Functions
9. Using ROW And ROWS
10. Using COLUMN And COLUMNS
11. Using ADDRESS
12. Using INDIRECT
13. Using OFFSET

Setting Excel Options

Excel’s numerous options allow you to set the default appearance of your spreadsheets, adjust the default auto-save time, and determine your default file-saving location. We’ll look at how to change these, and the many items they control.

1. Understanding Excel Options
2. Personalising Excel
3. Setting The Default Font
4. Setting Formula Options
5. Understanding Save Options
6. Setting Save Options
7. Setting The Default File Location
8. Setting Advanced Options

Chart Object Formatting

In an earlier chapter you saw that charts were easy to create. Now you’ll see how all the elements in a chart (such as the bar and line type) are all objects, and can be changed individually. This will give you the ability to produce truly illustrative charts, conveying the message you need to send.

1. Understanding Chart Object Formatting
2. Selecting Chart Elements
3. Using Shape Styles To Format Objects
4. Changing Column Colour
5. Changing Pie Slice Colour
6. Changing Bar Colours
7. Changing Chart Line Colours
8. Using Shape Effects
9. Filling The Chart Area And The Plot Area
10. Filling The Background
11. The Format Dialog Box
12. Using The Format Dialog Box
13. Using Themes

Labels And Names

Labeling and Naming cells are powerful ways of making cells absolute in value (for example, as a substitute for $A$1) and for providing a quick and easy method for you to “jump” to particular cells. In addition, by using Labels, you can include descriptive English words as part of your formulas. Having easy to navigate and easy to read spreadsheets will greatly increase your ability to move around large spreadsheets.

1. Understanding Labels And Names
2. Creating Names Using Text Labels
3. Using Names In New Formulas
4. Applying Names To Existing Formulas
5. Creating Names Using The Names Box
6. Using Names To Select Ranges
7. Pasting Names Into Formulas
8. Creating Names For Constants
9. Creating Names From A Selection
10. Scoping Names To The Worksheet
11. Using The Name Manager
12. Documenting Range Names

Protecting Data

Many excel users experience the frustration of accidental deletion of parts of their spreadsheets. This may be caused by other people who have access to your spreadsheets, or you may have inadvertently done it yourself. You’ll see how to protect your worksheets, so that formulas or data can’t be accidentally over written, and how we can also password protect these areas.

1. Understanding Data Protection
2. Providing Total Access To Cells
3. Protecting A Worksheet
4. Working With A Protected Worksheet
5. Disabling Worksheet Protection
6. Providing Restricted Access To Cells
7. Password Protecting A Workbook
8. Opening A Password Protected Workbook
9. Removing A Password From A Workbook

Summarising And Subtotalling

Many Excel users unnecessarily create subtotals of data the long, slow way – manually. You’ll see a tool for creating subtotals automatically, without you having to build the formulas. You’ll also see how they can be just as easily removed.

1. Creating Subtotals
2. Using A Subtotalled 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

Data Linking

Most Excel users confine their calculations to a single spreadsheet. You can add power to your spreadsheets by linking cells across worksheets, and even to cells in different workbooks. In Data Linking, we look at the benefits of this, and how to do it.

1. Understanding Data Linking
2. Linking Between Worksheets
3. Linking Between Workbooks
4. Updating Links Between Workbooks

Data 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 wasting time performing multiple copy and paste operations.

1. Understanding Data Consolidation
2. Consolidating With Identical Layouts
3. Creating An Outlined Consolidation
4. Consolidating With Different Layouts

PivotTables

One of the most powerful business tools, Pivot Tables lets 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 this single tool can reduce hours of data manipulation to seconds.

1. Understanding Pivot Tables
2. Creating A PivotTable Shell
3. Dropping Fields Into A PivotTable
4. Filtering A PivotTable
5. Clearing A Report Filter
6. Switching PivotTable Labels
7. Formatting A PivotTable

PivotTable Techniques

Having covered the basics of Pivot Tables in the earlier section, you’ll now see how you can apply this tool to perform even more powerful data manipulation. Grouping, subtotals and grand totals are all covered, as well as how to “drill down” into your data to extract the data you need.

1. Using Compound Fields
2. Counting In A PivotTable
3. Formatting PivotTable Values
4. Working With PivotTable Grand Totals
5. Working With PivotTable SubTotals
6. Finding The Percentage Of Total
7. Finding The Difference From
8. Grouping In PivotTables
9. Creating Running Totals
10. Creating Calculated Fields
11. Providing Custom Names
12. Creating Calculated Items
13. PivotTable Options
14. Sorting In A PivotTable

PivotCharts

Excel’s Pivot Table will enable you to create automatic Pivot Charts, which are summarised and grouped instantly according to your data specifications. While they’re similar to standard charts in appearance, you’re able to “drag and drop” data series and instantly rearrange your chart.

1. Creating A PivotChart Shell
2. Dragging Fields For The PivotChart
3. Changing The PivotChart Type
4. Using The PivotChart Filter Pane
5. Moving PivotCharts To Chart Sheets

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 Seek

Grouping And Outlining

One of the most underused tools within Excel, Grouping and Outlining will allow you to instantly combine multiple columns or rows into single columns and rows. It has 2 advantages: it creates automatic subtotals, and it hides unnecessary data.

1. Understanding Grouping And Outlining
2. Creating An Automatic Outline
3. Working With An Outline
4. Creating A Manual Group
5. Grouping By Columns

Solver

The Solver is a high-level “what-if” analysis tool. With a standard formula, you put in the variables to a formula, and Excel will calculate the answer. With the Solver, you tell Excel what you want the answer to be, and it will modify the contributing variables, according to your constraint instructions. This is an extremely powerful tool.

1. Understanding How Solver Works
2. Setting Solver Parameters
3. Adding Solver Constraints
4. Performing The Solver Operation
5. Running Solver Reports
6. Refining Solver Answers

Recorded Macros

Performing repetitive tasks is both time consuming and prone to error. Macros will solve this problem for you by allowing you to “record” a series of steps, and then play them back at the click of a button, or the press of a key. allowing you to perform lengthy tasks instantly. In this section we also look into Visual Basic for Applications (VBA), a powerful programming language into which our macros are converted.

1. Understanding Excel Macros
2. Setting Macro Security
3. Saving A Document As Macro Enabled
4. Recording A Simple Macro
5. Running A Recorded Macro
6. Relative Cell References
7. Running A Macro With Relative References
8. Viewing A Macro
9. Editing A Macro
10. Assigning A Macro To The Toolbar
11. Running A Macro From The Toolbar
12. Assigning A Keyboard Shortcut To A Macro
13. Deleting A Macro
14. Copying A Macro
15. Tips For Developing Macros

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 For An Application
2. Recording A Summation Macro
3. Recording Consolidations
4. Recording Divisional Macros
5. Testing Macros
6. Creating Objects To Run Macros
7. Assigning A Macro To An Object