Once the formulas are solid, the work changes. Now it’s about keeping bad data out of shared workbooks, finding the answer in tens of thousands of rows, and producing the same report every month without rebuilding it.
Your team carries on with the sales and operations workbook from the Intermediate course and turns it into a protected, interactive dashboard with a one-click monthly report. On the way they cover advanced sorting and filtering, PivotTables and PivotCharts, charts that suit the message, linking to Word and PowerPoint, formula auditing, Scenario Manager and Goal Seek, and enough macros and VBA to automate the dull part.
By the end, participants can
- Design data validation rules, including formula-based rules, and protect worksheets
- Apply advanced sorting and filtering techniques, including custom lists, subtotals and advanced filters
- Create and customise combination charts and other advanced visualisations
- Build PivotTables and PivotCharts with grouping, slicers and timelines
- Design a one-page dashboard and present findings using data storytelling principles
- Link and embed Excel data and charts in Word and PowerPoint, and use hyperlinks for navigation
- Audit formulas and use what-if tools: scenarios, goal seek and data tables
- Record and edit macros, and use simple VBA and buttons to automate reports
- Describe how Power Query refresh can automate data preparation
Course outline
Day 1 · Control, Analyse and Visualise
01Data Validation and Workbook Protection
- Validating data entries, with drop-down lists
- Custom input messages and error alerts
- Advanced validation using formulas, and dependent drop-down lists
- Locking cells and protecting worksheets
02Advanced Sorting and Filtering
- Sorting by multiple columns, and using built-in sort options
- Creating custom sort lists
- Applying subtotals
- AutoFilters and advanced filters with a criteria range
03PivotTables and PivotCharts
- Creating and using a PivotTable
- Filtering and sorting in a PivotTable
- Automatic and manual data grouping for custom views
- Slicers and timelines for interactive filtering
- Visualising results with PivotChart
04Data Visualisation
- Creating combined charts
- Adding a secondary axis and switching chart type
- Formatting data labels and adjusting axis scales
- Adding images into chart columns
- Choosing the right chart for the message
Day 2 · Present, Test and Automate
05Data Storytelling and Dashboards
- Components of a data story
- Types of data story: choosing the approach for your audience
- Designing a one-page dashboard with PivotCharts, slicers and key figures
- Common dashboard mistakes to avoid
06Linking, Embedding and Hyperlinks
- Linking data within a worksheet, between worksheets and between workbooks
- Linking data and charts to Word and PowerPoint
- Updating and breaking links
- Inserting, editing and removing hyperlinks, and building a navigation menu
07Formula Auditing and What-If Analysis
- Tracing precedent and dependent cells
- Showing all formulas, error checking and Evaluate Formula
- Scenario Manager
- Goal Seek
- Data tables for sensitivity analysis
08Automating Excel Reports
- Recording and running macros, and saving a macro-enabled workbook
- Managing and organising macros, and macro security
- Simple VBA for reporting: reading and editing a recorded macro
- Button-driven automation and automating PivotTable refresh