Visually Effective Excel Dashboards
Last updated 11/2022
MP4 | Video: h264, 1920x1080 | Audio: AAC, 44.1 KHz
Language: English | Size: 7.23 GB | Duration: 11h 1m
Last updated 11/2022
MP4 | Video: h264, 1920x1080 | Audio: AAC, 44.1 KHz
Language: English | Size: 7.23 GB | Duration: 11h 1m
Actionable Excel Tips (Templates Included) You Can Use Right Now to Create Eye-Catching Microsoft Excel Dashboards
What you'll learn
How to approach an Excel dashboard project from idea to delivery
How to add interactivity to your Excel reports - No VBA required
Impress management with reports that make the decision-making process faster
Clever use of Excel core functionality for responsive spreadsheets
Learn how to create a professional KPI dashboard from scratch
Create impressive, non-standard Excel charts using smart techniques
Build Pivot Table-based dashboards for easy interactivity
Use powerful visual techniques so your content has more effect
Requirements
A computer or laptop with Windows (some techniques might not work on Mac)
You are able to write basic formulas and are familiar with the Excel spreadsheet environment
Demonstration is done with Excel 2016 but the techniques work with any version of Excel - unless stated in the individual videos
Description
Creating an impressive, interactive Microsoft Excel Dashboard from scratch is not easy!I used to stare at my empty sheet and wonder where do I even start? You need a structured process in place to go from idea to outcome in a smooth way. You'll find out all about it in the course. We setup two fully functioning dashboards from scratch.This course doesn't just teach you a set of functions and features, but how you can use them together to create impressive reports. It teaches you how to approach a new Dashboard project and how to structure your work so maintaining the dashboard becomes very easy not just for yourself but for anyone else.★ Note: Course recently updated to include the OFFICE 365 & OFFICE 2021 version of the KPI dashboard. You can download that version if you have Office 365 and see how you can use FILTER, SEQUENCE, XLOOKUP and other newer Excel functions to create the dashboard ★What students are saying"This course provides a lot of great tips which you can immediately apply to your spreadsheets, no matter what standard of user you are. I have been working in excel for a number of years and I am amazed at the number of simple tips I have learnt in such a short time. Fantastic." – Carl Postians"I learned something new and very useful in the first 10 minutes…awesome!" – Nancy BowensWhat you learnThis course teaches you to create well-designed Excel dashboards so you and your colleagues can see trends and make quick decisions informed by data.This Microsoft Excel Dashboard course comes with templates, tools and checklists that can be applied directly to your Excel reports. Excel dashboard & Report templates Checklists and design guidelines Excel workbook to follow along What differentiates this course from the other Microsoft Excel Dashboard courses? It includes:The entire process from idea to delivery - every single step without skipping key points.Tried-and-trusted techniques that are not only applicable to Excel dashboards but any Excel report.Includes Excel templates, tools and checklists that you can use for your own Excel reports.Relevant and up-to-date with industry needs right now, as it’s based on feedback and suggestions from finance professionals. Lots of ideas and inspiration to help you come up with own report designComplex topics are taught in a simple way by an instructor with 17 years of experience.The course is designed to have an even balance between talking head and screen-cast.Content is clearly organised so more advanced learners can jump in at any point and follow along with the relevant project file.
Overview
Section 1: A Foundation in Dashboards
Lecture 1 Introduction to Excel Dashboard Course
Lecture 2 Get the Most from the Course
Lecture 3 Download Course Files Here (Excel Dashboards & Practice Files)
Lecture 4 Course Outline for Quick Reference
Lecture 5 A Dashboard Primer
Lecture 6 New Excel Dashboard Project? This is Your Checklist
Section 2: Dashboards - Before You Start
Lecture 7 First Steps for your Excel Dashboard Project
Lecture 8 Create a Mockup (Wireframe) in Excel
Lecture 9 Useful Excel Keyboard Shortcuts
Lecture 10 4 Time-Saving Tips for Excel
Lecture 11 Proper Spreadsheet Architecture & Workflow
Section 3: Report Design Principles
Lecture 12 Layout - Best Practice for Excel Dashboard Design
Lecture 13 Color - Best Practice for Excel Dashboard Design
Lecture 14 A Color Scheme that fits with your Excel Report
Lecture 15 10 Concrete Design Tips for Better Excel Reports
Lecture 16 Bonus: Setting a Default Theme for New Workbooks in Excel
Section 4: Dashboard Source Data & Formats
Lecture 17 Where is Your Source Data?
Lecture 18 Source: Excel Spreadsheet
Lecture 19 Source: Microsoft Access
Lecture 20 Source: Text / Data / CSV File
Lecture 21 Source: Excel Add-ins
Section 5: Dashboard Formula Toolbox
Lecture 22 Excel Formulas You'll Need for Impressive Dashboards
Lecture 23 Index Match for Complex Lookups - Basics
Lecture 24 Index Match for Complex Lookups - Advanced
Lecture 25 Index for Dynamic Drop-down List Selection
Lecture 26 Realistic Case: Index for Dependent Drop-down Lists in Excel
Lecture 27 SUMIFS, COUNTIFS, AVERAGEIFS to Handle Multiple Criteria
Lecture 28 LARGE and SMALL for Sorting Excel Data
Lecture 29 ROW(S), COLUMN(S) for Indexing
Lecture 30 ROW as Unique Identifier for Lookups
Lecture 31 CHOOSE for Flexibility
Lecture 32 Excel Text Function for Formatting
Lecture 33 Excel N Function for Tracking
Lecture 34 GetPivotData: Extract data Efficiently from Pivot Tables
Lecture 35 INDIRECT for Flexible References in Excel
Lecture 36 Choose & Excel Name Manager for Flexible Ranges (Alternative to Indirect)
Section 6: Form Controls for Interactive Excel Dashboards
Lecture 37 Introduction to Form Controls - Active X vs. Form Controls
Lecture 38 Combo Box - Select From a Drop Down List in Excel
Lecture 39 Check Box - Check / Uncheck Option in Excel
Lecture 40 Spin Button - Move Up & Down Lists in Excel
Lecture 41 Option Button - Choose One Option Only
Lecture 42 List Box - Select From a List of Options in Excel
Lecture 43 Scroll Bar - Scroll Through Excel Graphs & Tables
Lecture 44 Bonus: Dependent Combo Boxes (2nd list automatically resets)
Section 7: Advanced Excel Chart Techniques
Lecture 45 Chart Overview & When to Use Which Excel Graph
Lecture 46 Excel Chart Basics (for those who need to get familiar with basic features)
Lecture 47 Add Total Values to Stacked Excel Column Charts
Lecture 48 Better Charts: Add Dynamic Series Labels Inside Excel Charts
Lecture 49 Excel Chart Secret: Error Bars for Additional Control
Lecture 50 Pro Excel Chart Technique: Changing Chart Ranges
Lecture 51 Info-graphics in Excel: Non-standard but Impressive Bar Chart
Lecture 52 Dynamic Excel Map Chart with Drop-down
Section 8: *** KPI Excel Dashboard ***
Lecture 53 Excel KPI Dashboard Overview
Lecture 54 BONUS: IF YOU HAVE OFFICE 365 or OFFICE 2021
Lecture 55 Dashboard Wireframe in Excel
Lecture 56 Spreadsheet Setup
Lecture 57 Setup of Raw Data Sheet
Lecture 58 Setup of Excel Calculation Sheet
Lecture 59 Download Dashboard till here & practice along - Draft01
Section 9: KPI Dashboard - Scrolling Table & Bars
Lecture 60 Setup a Combo Box to Ease Selection
Lecture 61 Complex Lookup with Index & Match in Excel
Lecture 62 Scrolling Data Table in Excel
Lecture 63 Conditionally-Formatted Data Bars
Lecture 64 Arrows for Deviations in Excel
Lecture 65 Formatting of Scrolling Data Table
Lecture 66 Download Excel Dashboard till here & practice along - Draft02
Section 10: KPI Dashboard - Dynamic Headers
Lecture 67 Conditional Comments in Header
Lecture 68 Adding Shapes & Text boxes in Excel
Lecture 69 Download Excel Dashboard till here & practice along - Draft03
Section 11: KPI Dashboard - Top/Bottom KPI Based on Selection
Lecture 70 Show Top 6 Matches with Unique Identifier
Lecture 71 List Box to Select KPI for Top 6 Matches
Lecture 72 Option Button to Toggle Between Top OR Bottom 6
Lecture 73 Actual to Budget Comparison - Sorted Bars with Error Bar Technique
Lecture 74 Finalize Top / Bottom Report on Dashboard
Lecture 75 Download Dashboard till here & practice along - Draft04
Section 12: KPI Dashboard - Scrolling Line Chart with Check Boxes
Lecture 76 Complex Excel Lookup to Prepare data for Scrolling Chart
Lecture 77 Scroll-able Line Chart
Lecture 78 Trick to Avoid Crashing Lines in an Excel Line Chart
Lecture 79 Check Box to Control Series Visibility in Excel (Hide or Show)
Lecture 80 Excel Line Series Tweaks for Better Readability
Lecture 81 Finalize Profit Comparison Section on Dashboard
Lecture 82 Download Dashboard till here & practice along - Draft05
Section 13: KPI Dashboard - Final Touches
Lecture 83 Restrict Excel Dashboard Area and Format for Printing
Lecture 84 Excel Dashboard Protection, Hyperlinks and Final Touches
Lecture 85 Download Excel Dashboard till here - Final
Section 14: *** Regional (Pivot / Slicer) Excel Dashboard ***
Lecture 86 More Interactivity - Pivot Slicers as Buttons (also without Pivot Tables)
Lecture 87 Column Chart Controlled by Excel Slicer Buttons
Lecture 88 Pivot Slicer Custom Sorting & Design
Lecture 89 Check to Ensure Single Pivot Slicer Button is Selected
Lecture 90 Dynamic Excel Comments with Pivot Slicer Selection
Lecture 91 Download Excel Regional Dashboard till here - Draft02
Section 15: Regional Dashboard - Advanced Excel Pivot Table Techniques
Lecture 92 Variances in Excel Pivot Tables
Lecture 93 Conditional Formatting in Excel Pivot Tables
Lecture 94 Pivot Table as Part of Excel Dashboard Report
Lecture 95 Pivot Charts in Excel Dashboards
Lecture 96 Automatically Refresh Excel Pivot Table with Simple VBA Code
Lecture 97 Download Regional Dashboard till here - Draft03
Section 16: Regional Dashboard - Impressive Charts Using Shapes
Lecture 98 Info Charts: Adding Shapes to (Pivot) Charts
Lecture 99 Info Charts: Overcoming Pivot Chart Limitations in Excel
Lecture 100 Download Regional Dashboard till here - Draft04
Section 17: Regional Dashboard - Secrets for Impressive Reports
Lecture 101 Excel Camera for Improved Design
Lecture 102 Advanced Excel Camera - Lookups on Pictures
Lecture 103 Dashboard Complete - Final Touches
Lecture 104 Download the final version of the Slicer/Regional Dashboard
Section 18: What you Learnt
Lecture 105 Final Words
Lecture 106 BONUS - More from Leila
Business Professionals who use Excel to create dashboards and reports,Data analysts,You currently use Excel and want to improve your skills,You'd like to learn how to create interactive reports,You'd like to improve the design of your existing Excel reports