What’s new for Excel 2007
- An overview of the new features in Excel 2007
Custom Formatting
Advanced Functions
Resolving common formula errors
- Using Smart Tags
- Using the IS functions to hide errors
- The Formula Auditing toolbar
- Using the Error Finder
- Tracing precedents and dependents
- Tracing an error
- Resolving a circular reference
The “What If?” tools
-
Scenarios, Goal Seek, Solver and Data Tables
-
How to set the answer and work backwards to one or more source values
-
Adding constraints
-
Keeping a solution or restoring original data
-
Save a Solver solution as a scenario
-
‘Creating a Solver Report
Custom Views
- What can be stored in a view?
- Creating, editing, displaying and deleting a view
Importing data into Excel
- Importing a CSV file
- Importing an entire or filtered Access database table
Consolidating worksheets
- Consolidating data by position
- Consolidating data by category
Macros
- Record a macro
- Running a macro from the Quick Access Toolbar or a graphic
- Exploring macro code using VBA
|
PivotTables
-
Creating a pivot table using the new 2007 interface
-
Exploring pivot-table components
-
Filtering data using report, column and row fields
-
Moving, adding and removing data from the pivot table.
-
Updating the original data and refreshing the pivot-table
-
Changing the data function
-
Renaming a field
-
Using the Show Pages tool
-
Showing how a data total was generated
-
Formatting a pivot-table
-
Exploring the pivot-table options
-
Collapsing and expanding data
-
Using calculated fields and calculated items
-
Grouping text items.
-
Grouping data into date or numerical bins
-
Sorting data manually and automatically
-
Creating and customising a PivotChart Report
Arrays (if time)
-
Creating an array formula
-
Understanding array rules and characteristics
-
Locating and editing an array formula
-
Using logic functions with arrays
-
Creating a Frequency Distribution
-
Creating a Histogram
On-line forms (if time)
- Exploring the Forms toolbar
- Adding labels
- Adding a drop-down list or list box
- Adding a spinner control
- Adding tick boxes or option buttons
- Grouping options together
- Adding a scrollbar
- Showing or hiding the gridlines
- Selecting and moving controls
- Save the form so it can be used
Sharing Workbooks (if time)
-
Allowing a workbook to be shared
-
Protecting worksheets and workbooks
-
Handling conflicts
-
Implications of sharing a workbook
- Sending a workbook for review by mailing or routing slip
|
