Duration
1 day
What you'll learn
- You will learn about Pivot Tables and Pivot Chart
- You will learn to create 6 Dynamic Excel Dashboard
- You will learn how to Design, Format and Style Data Elements
- Learn the Concepts of How to Create and Design an Interactive Excel Dashboard for your department.
Introduction
- Dealing with Raw Data
- The Magic of an Excel Dashboard
Analyzing Data Effectively with Microsoft Excel
- DOWNLOAD THIS FILE FIRST
- Using Excels PROPER() Function on Text
- Create Consistency with Excels UPPER() Function
- Microsoft Excels Paste Special Feature
- Replace Meaningless Data with Excels CHOOSE() Function
- Extract Date Values Using Excels TEXT() Function
Building the Excel Dashboard - LOOK UP DATA
- Looking Up Customer Info
- Preparing the Data Using Format As Table
- Creating a Drop Down Menu with Data Validation
- Looking Up Data with Excels VLOOKUP() Function
- Cleaning Up Data with Excels IF() Function
- INDEX() and MATCH() an Alternative to VLOOKUP()
Building the Excel Dashboard - FILTERING DATA
- Adding the Order History Table
- Formatting Orders as a Table
- Using Excels Advanced Filter Feature
- Record Macro for Advanced Filter
- Filter Order Records On Change of Customer (VBA)
- Modify the VBA Filter Code
Building the Excel Dashboard - SUBTOTALS
- Why Use Excels SUBTOTAL() Function
- Implementing the SUBTOTAL() Function
Building the Excel Dashboard - PivotTables and Pivot Charts
- Why Use PivotTables
- Summarizing Order Info with PivotTables
- Prepare PivotTable for Customer Filter
- Creating the VBA Procedure
- Declaring VBA Variables
- Assigning Values to VBA Variables
- Connecting the Filter to the PivotTable
- Update Pivot Chart Based on Customer Selected (VBA)
- Customers with No Orders Error
Building the Excel Dashboard - Interactive Buttons
- Creating Interactive Charts with Slicers
- Modifying the Chart Slicer
Building the Excel Dashboard - Final Touches
- Hiding Extra Worksheets and Columns
- Cleaning Up the Excel Default Settings
- Hiding the Chart and Resetting the Slicer (VBA)
- Protecting the Dashboard
Programme scope, delivery arrangements and suitability are confirmed during enquiry.