Highlights
- This course covers :
- Using advanced graphs and presentation techniques to maximise impact
- How macros and VBA automate your spreadsheets and increase interactivity
- How superpower functions can solve complex problems
- Using PivotTables and PowerPivots to turn raw data into clear information that supports key decisions
Syllabus
Section 1: Design and Risk
Get a head start with 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: customise the Excel interface to put the tools that you need at your fingertips
Make spreadsheets more efficient and less risky ● 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
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
Section 2: Advanced Techniques
Functions with 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
Array formulae ● One formula, one million calculations ● SUMPRODUCT() - all the gain of an array formula with less of the pain
Tables – structure comes to Excel ● 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
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
Using Excel to help you make decisions ● 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
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
Section 3: Excel Interactivity
Use Form controls to make life easier for users ● Use a Spin Button to choose a value easily ● Use an Option Button to choose with a single click ● Choosing from lists
Create macros by writing Visual Basic code ● 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
Section 4: Spreadsheet Impact
Exchanging information with VB code ● 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
Conditional Formatting – beyond simple Conditional Formats ● 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
Create charts to 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
In-cell charts – showing 12 times as much information in the same amount of space ● Careful with that Axis ● The different types of Sparkline: lines, columns and win loss ● Sparklines based on a dynamic data range
Further graphics tips and techniques ● Taking dynamic pictures with the Excel camera ● Use the Excel camera 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
Understanding Data Structures ● Microsoft Query: From Database to Spreadsheet ● Data Model
Section 5: Turning Data into Decisions
Use advanced PivotTable techniques to do more with your data ● 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
The Excel 2010 and 2013 Power Pivot add-in ● 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
Creating powerful reports 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