Report Automation in Modern Excel
Learn a powerful methodology to automate any report & dashboard in Excel (with no VBA) & watch it update all by itself every minute.
This course teaches you a practical approach to automate Reporting from a simple dataset in Excel using a modern approach.
Here's what you'll learn...
Requirements & How to Complete the Course
FREE PREVIEWHow to use the Platform
FREE PREVIEWCourse Overview
FREE PREVIEWTrainer Profile
FREE PREVIEWDownload Exercise Files
Before we Begin
Ask the Trainer
The Reporting Infrastructure
Lab 01 - How Excel Works
Reporting Framework & the 7 Golden Rules of Data
The role of the Control & Report Sheets
How Excel Works
FREE PREVIEWManaging Dates, Validations & Format Styles
Chapter Quiz
Get Data with Power Query
Lab 02- Get Data with Power Query
Getting External Data into Excel
FREE PREVIEWConnect to Excel Files in a Folder
Consolidate Excel Workbooks
FREE PREVIEWTransform Data Types
Loading your Query to Excel
Chapter Quiz
Report Building Blocks
Lab 03 - Report Building Blocks
Simple Report Template
Identify your Report Criteria
Automate with SUMIFS
SUMIFS & the Date Criteria Hack
Chapter Quiz
Building a Control Infrastructure
Lab 04 - How to Build a Control Infrastructure
Why We Need Date Controls
FREE PREVIEWFramework for Date Controls 1of2
Framework for Date Controls 2of2
How to Activate a Combo Box Form Control
Inserting Combo Box Controls
Linking Combo Boxes To Excel
Extract Combo Box Data with the INDEX Function
Chapter Quiz
Build a Dynamic Date & Report Selector
Lab 05 - Build a Dynamic Date & Report Selector
Extract End Date From Combo Boxes
Create a Flexible Financial Year Start Month
Automate Start Month with CHOOSE & ROUNDUP
FREE PREVIEWComplete the Date Automation Process
Plug the Combo Box into Your Report Sheet
Chapter Quiz
The Report Automation Process
Lab 06 - The Report Automation Process
Automate your Report with the Controls FrameWork
Create Report Headings & Labels with CONCATENATE
Variance Analysis & Number Formating
Insert a Variance Analysis Chart
Report Update With Power Query
FREE PREVIEWChapter Quiz
Visualising your Performance Reports
Lab 07 - Visualizing your Performance Reports
Overview of MoM Change Report
Overview of MoM Change Report 2
Building a Rolling 12 Month Report Table
Building a Rolling 12 Month Report Chart
Build a Monthly Volatility Chart
Insert Reference lines into a Chart
MoM Change in Revenue vs Units Sold
Chart MoM Change in Revenue vs Units Sold
Insert Benchmark Bands Into a Chart
FREE PREVIEWChapter Quiz
Top N Reports & Cards
Lab 08 - Visualizing Top N Reports & Cards
Top & Bottom 5 Report Part 1
Top & Bottom 5 Report Part 2
Chapter Quiz
Building your Dashboard
Lab 09 - Building your Dashboard
Overview
Dashboard build Secrets
Using the Camera Tool
Edit & Shape Images for your Dashboard
Finalise Dashboard Design
Activate Auto Refresh For New Data
FREE PREVIEWFinal Review, Protect WorkBook & WorkSheet
Chapter Quiz
Instructions
Before you go...
50 Excel Keyboard Shortcuts for Windows
50 Excel Keyboard Shortcuts for Mac
How to Attach your Certificates to your LinkedIn Profile
“Your course on Report Automation in Modern Excel is indeed an eye opener as to the many possibilities in Microsoft Excel. The training is superb and very informative with great content. I enjoyed every minute of the videos and it has provided me with a greater level of understanding, additional skills and knowledge, to aid my numerous tasks in the office. Report Automation in Modern Excel is suitable for all who analyze data. The course delivery is excellent. DBrown Consulting, please keep it up.”
“Excellent, I Would Surely Recommend This Another Person very detailed and insightful, solved real life problems Clearly explained the concept in simple terms such that it was easy to learn and apply the concepts ”
“Mr David was very interactive in his teaching style and he helped create a desire to work more proficiently, using what we have at our disposed. ”