About this Course

Data visualization is one of the key elements of report/MIS presentation nowadays, which can be done through a modern tool like interactive dynamic dashboards.

The easiest way to building your interactive dynamic dashboard is to use Excel software which is widely used all over the world to keep day-to-day data on marks.

Here we are coming with “Dashboard Foundation Course”. It will be helpful to the person who wants to enhance their skills and how to create a dashboard in excel. To make a dashboard some advanced tools are used like Pivot Table, Pivot charts, etc.

Schedule

• Date: 11th April to 15th April 2021 (5 Days)
• Timing: 7:30 PM to 9:00 PM (IST)

Benefits

• 2 Practical Dashboard from Scratch
• Experienced faculty
• Hands-on experience
• Soft-copy of Material
• Email support after training
• E-Certificate after clearing exam

Register Now

Content

1. 1. Introduction to Dashboard Foundation
      • Introduction to Excel Dashboard Foundation Course
      • New Excel Dashboard Project Checklist
   2. Dashboards – Things to be consider before you start
      • First Steps for your Excel Dashboard Project
      • Create a Mockup (Wireframe) in Excel
      • Useful Excel Keyboard Shortcuts
      • Some Time-Saving Tips
      • Proper Spreadsheet Architecture & Workflow
   3. Dashboard Report Design Principles
      • Layout – Best Practice for Dashboard Design
      • Color – Best Practice for Dashboard Design
      • A Color Scheme that fits with Excel Report
      • Design Tips for Better Excel Reports
      • Bonus: Setting a Default Theme for New Workbooks in Excel
   4. Source Data & Formats
      • Where is Your Source Data?
      • Source: Excel Spreadsheet
      • Source: Microsoft Access
      • Source: Text / Data / CSV File
      • Source: Excel Add-ins
   5. Brushing up Excel Dashboard Formula
      • Excel Formulas for Impressive Dashboards
      • Index Match for Complex Lookups – Basics
      • Index Match for Complex Lookups – Advanced
      • Index for Dynamic Drop-down List Selection
      • Realistic Case: Index for Dependent Drop-down Lists in Excel
      • SUMIFS, COUNTIFS, AVERAGEIFS to Handle Multiple Criteria
      • LARGE and SMALL for Sorting Excel Data
      • ROW(S), COLUMN(S) for Indexing
      • ROW as Unique Identifier for Lookups
      • CHOOSE for Flexibility
      • Excel Text Function for Formatting
      • Excel N Function for Tracking
      • GetPivotData: Extract data Efficiently from Pivot Tables
      • INDIRECT for Flexible References in Excel
      • Choose & Excel Name Manager for Flexible Ranges (Alternative to Indirect)
   6. Form Controls for Interactive Excel Dashboards
      • Introduction to Form Controls – Active X vs. Form Controls
      • Combo Box – Select From a Drop Down List in Excel
      • Check Box – Check / Uncheck Option in Excel
      • Spin Button – Move Up & Down Lists in Excel
      • Option Button – Choose One Option Only
      • List Box – Select From a List of Options in Excel
      • Scroll Bar – Scroll Through Excel Graphs & Tables
      • Bonus: Dependent Combo Boxes (2nd list automatically resets)
   7. Advanced Excel Chart Techniques
      • Chart Overview & When to Use Which Graph
      • Excel Chart Basics
      • Add Total Values to Stacked Excel Column Charts
      • Better Charts: Add Dynamic Series Labels Inside Excel Charts
      • Excel Chart Secret: Error Bars for Additional Control
      • Pro Excel Chart Technique: Changing Chart Ranges
      • Info-graphics in Excel: Non-standard but Impressive Bar Chart
      • Dynamic Excel Map Chart with Drop-down
   8. *** 1st Dashboard (KPI Excel Dashboard) ***
      • Excel KPI Dashboard Overview
      • Dashboard Wireframe in Excel
      • Spreadsheet Setup
      • Setup of Raw Data Sheet
      • Setup of Excel Calculation Sheet
      • Download Dashboard till here & practice along – Draft01
      • KPI Dashboard – Scrolling Table & Bars
      • Setup a Combo Box to Ease Selection
      • Complex Lookup with Index & Match in Excel
   9. Scrolling Data Table in Excel
      • Conditionally-Formatted Data Bars
      • Arrows for Deviations in Excel
      • Formatting of Scrolling Data Table
   10. KPI Dashboard – Dynamic Headers
       • Conditional Comments in Header
       • Adding Shapes & Text boxes in Excel
   11. KPI Dashboard – Top/Bottom KPI Based on Selection
       • Show Top 6 Matches with Unique Identifier
       • List Box to Select KPI for Top 6 Matches
       • Option Button to Toggle Between Top OR Bottom 6
       • Actual to Budget Comparison – Sorted Bars with Error Bar Technique
       • Finalize Top / Bottom Report on Dashboard
   12. KPI Dashboard – Scrolling Line Chart with Check Boxes
       • Complex Excel Lookup to Prepare data for Scrolling Chart
       • Scroll-able Line Chart
       • Trick to Avoid Crashing Lines in an Excel Line Chart
       • Check Box to Control Series Visibility in Excel (Hide or Show)
       • Excel Line Series Tweaks for Better Readability
       • Finalize Profit Comparison Section on Dashboard
   13. KPI Dashboard – Final Touches
       • Restrict Excel Dashboard Area and Format for Printing
       • Excel Dashboard Protection, Hyperlinks and Final Touches
   14. *** 2nd Dashboard – Regional (Pivot / Slicer) Dashboard *** (Self Practice)
       • More Interactivity – Pivot Slicers as Buttons (also without Pivot Tables)
       • Column Chart Controlled by Pivot Slicer Buttons
       • Pivot Slicer Custom Sorting & Design
       • Check to Ensure Single Pivot Slicer Button is Selected
       • Dynamic Excel Comments with Pivot Slicer Selection
   15. Regional Dashboard – Advanced Excel Pivot Table Techniques
       • Variances in Excel Pivot Tables
       • Conditional Formatting in Excel Pivot Tables
       • Pivot Table as Part of Excel Dashboard Report
       • Pivot Charts in Excel Dashboards
       • Automatically Refresh Pivot Table with Simple VBA code
   16. Regional Dashboard – Impressive Charts Using Shapes
       • Info Charts: Adding Shapes to (Pivot) Charts
       • Info Charts: Overcoming Pivot Chart Limitations in Excel
   17. Dashboard Secrets for Impressive Excel Reports
       • Excel Camera for Improved Design
       • Advanced Excel Camera – Lookups on Pictures
       • Dashboard Complete – Final Touches
   18. Conclusion and Exam

Register Now