1. Templates

• Set up an Excel template that will save you an hour each time you create a new workbook
• Create new default workbook and worksheet templates
• Learn how modular templates can make your spreadsheets more consistent and much quicker to set up
• Your Excel, your way: customize the Excel interface to put the tools that you need at your fingertips.

2. Efficiency and Risk

• How to build in checks and controls from the outset
• Key techniques that reduce risk and increase automation and efficiency
• Introduce standards to help your team
• Documentation and review tools

3. Data Validation

• Extended uses of Data Validation
• Working with validation formulae
• Lists and lists that depend on other list selections
• Other methods of tracking down invalid entries

Advanced Techniques and Problem Solving

4. Functions and Superpowers

• Finding the right functions and how they work
• Functions with hidden powers – MOD() for patterns, OFFSET() for simple choices, INDIRECT() to manipulate formulae
• Making references to ranges of cells adapt automatically for new data

5. Array Formulae

• One formula, one million calculations
• SUMPRODUCT() - all the gain of an array formula with less of the pain

6. Tables

• Why Tables are so much more than just a new format
• Make your spreadsheets more automatic with a single command
• Table formulae – instant readability
• Data tools in Tables
• It’s not all good

7. Advanced Range Names

• Use the same name on different sheets
• Using the Name Manager
• Names in formulae – efficient inclusion of Names, the use of intersections
• Formulae in Names – how to allocate a formula directly to a Range Name and why you might need to

8. What if?

• Use Goal Seek to find where you need to start to get where you need to go
• Use an Excel Data Table to calculate dozens of possible outcomes
• Create and manage alternative scenarios
• Make more profit or incur less expense by using Excel Solver to identify the best solution

9. Problem solving

• Calculations that make decisions – understanding TRUE ad FALSE
• What Boolean Logic is and why it’s useful in practice
• Combining logic and arrays to solve complex problems
• Practical examples

10. Form Controls

• Use a Spin Button to choose a value easily
• Use an Option Button to choose with a single click
• Choosing from lists

11. Visual Basic and Macros

• A macro that performs one or more actions on selected cells
• Write your own Excel functions with VB code
• Understanding volatile functions
• Trigger a macro when a particular cell is changed
• Handling errors elegantly

12. Practical Interactivity with VBA

• Displaying a Message box
• Asking for user input using an Input box
• Create an Excel form with a List box containing values from a range of cells
• Sample VB projects: an automatic index to sheets, printing selected ranges
• Avoiding macros when they’re not really necessary

13. Conditional Formatting

• Basing conditions on a formula and deciding whether a batsman is out or not
• Choose currency symbols for a whole sheet by changing a single cell
• Getting your rules in the right order and knowing when to stop
• Graphical Conditional Formats – the detailed options
• Using invisibility to your advantage

14. Charts that Inspire

• What makes a good chart – is it really a 6.5 cucumbers?
• Simple steps to make your charts clearer
• Why small can be better than large
• Are pie charts evil?
• Why 3D charts can be 50% worse
• Mixed chart types, trendlines and projections
• Advanced chart techniques: break-even lines and waterfall charts
• Pictures in chart columns

15. Sparklines

• Careful with that Axis
• The different types of Sparkline: lines, columns and win loss
• Sparklines based on a dynamic data range

16. Graphics Tricks and Techniques

• Taking dynamic pictures with the Excel
• Use the Excel to combine areas from multiple sheets on the same sheet of paper
• Formatting Excel Camera pictures
• Power View – using the Excel 2013 data visualisation add-in including plotting values on maps and ‘playing’ bubble charts

17. Working with External Data

• Understand relational databases in 10 minutes and liberate your data
• Excel 2013 Data Model create relationships within Excel

18. Advanced Uses of PivotTables

• Calculated Fields and Calculated items
• Using PivotTables as the calculation engine behind management reports
• Using GETPIVOTDATA() and CUBE formulae to create flexible reports
• Excel 2010 and 2013 Slicers and Timelines - make your PivotTables more interactive
• Working with Pivot Charts
• Building and interactive dashboard using PivotTables and Slicers

19. PowerPivot

• PowerPivot data tools
• Calculations in Power Pivot – an introduction to Data Analysis Expressions (DAX)
• DAX in table columns
• DAX to create new measures and calculated fields
• Understanding advanced DAX expressions – functions that combine calculations and database techniques
• The Time Intelligence DAX functions – why you need a table containing all possible dates

20. Reporting with PowerPivot

• Hierarchies, Perspectives and Sets
• Adding Key Performance Indicators (KPIs) to a Power Pivot report
• Creating CUBE formulae to report on your data in just the way you want to
• Use all these techniques to turn millions of rows of data into a dashboard that supports better decisions