Microsoft Excel 2010 Expert Level 
Training Courseware

MANIPULATING DATA, NAMED RANGES & TEMPLATES

• Paste Special Techniques
• Importing a text file and delimiting

TEMPLATES

• Using, creating, opening and editing templates

NAMED RANGES

• What does naming a cell range mean?
• Rules for naming cells and ranges
• Naming cell range(s) in a worksheet
• Navigating through workbooks using named ranges
• Creating named ranges automatically based on cell values

SUB-TOTALING

• Creating subtotals
• Removing subtotals

ADVANCED FORMATTING & DISPLAY TECHNIQUES

• Cell Styles
• Table Styles
• Conditional Formatting
• Custom number formats
• Freezing row and column titles
• Hiding and un-hiding rows and columns
• Hiding / un-hiding worksheets

SORTING AND QUERYING DATA

• Sorting data by multiple columns
• Custom sorts
• Using AutoFilter
• Using advanced query / filter options

LINKING & EMBEDDING DATA

• Linking data or a chart within a worksheet
• Linking data or a chart between worksheets [within a workbook]
• Linking data or a chart between spreadsheets [workbooks]
• Linking data or a chart into a word processing document
• Consolidating data in adjacent worksheets using a 3D sum function

CHARTS FORMATTING TECHNIQUES

• Changing pie slide angles
• Formatting chart axis fonts and scales
• Chart title
• Chart legend
• Modifying a data series in a chart

WORKBOOK PASSWORD PROTECTION

• Password for opening a workbook
• Read-only recommended

PROTECTING / UN-PROTECTING A WORKSHEET WITH A PASSWORD

• Protect worksheet elements
• Removing workbook protection

FUNCTIONS

• Getting help with using a particular function.
• TODAY
• DAY
• MONTH
• YEAR
• SUMIF
• ROUND
• COUNT
• COUNTA
• COUNTIF
• PROPER
• UPPER
• LOWER
• CONCATENATE
• FV
• NPV
• PMT
• PV
• RATE
• HLOOKUP
• VLOOKUP
• IF
• AND
• OR
• ISERROR
• DSUM
• DMIN
• DMAX
• DCOUNT
• Using nested functions

USING ONE-INPUT OR TWO-INPUT DATA TABLES / WHAT-IF TABLES

• One input Data Table command
• Two input data table command

A FIRST LOOK AT PIVOT TABLES

• Creating a PivotTable
• Dropping data into the Pivot Table
• Modifying data and refresh the Pivot Table
• Grouping data within a Pivot table

SCENARIOS

• Creating named Scenarios from defined cell ranges
• Viewing an alternative scenario
• Creating a scenario summary

AUDITING

• Tracing precedent cells
• Tracing dependants of a cell
• Displaying all formulas within a worksheet
• Using comment

MACROS

• Displaying the Developer tab
• Recording and running a macro
• Lowering your macro security level
• Customising the Quick Access Toolbar
• Changing the Quick Toolbar Macro icon
• Removing a macro icon from the quick access toolbar
• Raising your macro security level