Import data

• Import the two types of text file: fixed-width and delimited.
• Import dates correctly.

Split, join and manipulate columns

• Concatenate columns with &.
• Split columns with LEFT, MID and RIGHT.
• Convert case with UPPER, LOWER and PROPER.
• Split at a particular character with FIND and TRIM.

Create PivotTables

• Create a PivotTable.
• Format a PivotTable.
• Update a PivotTable with the latest values.
• Show data as a % of column total.
• Display selected items only, remove subtotals and remove a grand total.

Calculate rank order

• Use RANK and LARGE.

Apply advanced sorts and AutoFilters

• Sort and Filter by colour and icons in Excel 2007 and later.
• Sort into non-alphabetical order (such as month names).
• Filter by top 10 and complex criteria.

Insert Subtotals and create Outlines

• Insert subtotals into sorted tables.
• Use Outlines to quickly hide and display data.

Structure for expansion: use a Table/List, range name

• Make PivotTables, formulas and print areas expand automatically to include all data.
• Use range names for quick range selection and maintenance.
• Use the GenieMini add-in for superior range name management plus much else.

Discover other useful features (optional topics)

• Use DSUM to total data that has complex criteria selection.
• Delete duplicates.

Prerequisites

Excel users who can already create basic SUM and arithmetic formulas. Those who want to analyse data arranged in tables, which are often imported from other systems. Prior attendance at the Best-practice essentials and Powerful formulas and clear reporting courses is not mandatory, although it would be useful.

• Be assured the advice you get is well-proven and utilised.
• Feel free to ask questions.
• Get time away from the office and constant interruptions.
• Enjoy a friendly environment where you don’t feel you’re holding others up.
• Instantly apply what you’ve learned on return to work.