Excel Dashboards

Course number: CTS-1-6-2016

Course length: 1.0 day(s)

Course Description Course Objective: The goal of this course is to create a dynamic “Dashboard” or “Report”. You will create Graphs, Tables, PivotTables, Form Buttons, Macros and Functions that work together and connects the data that display information relevant for a presentation or decision maker.

Target Student: This course is designed for students who desire to gain the necessary skills to create dynamic Dashboards/Reports.

Prerequisites: To ensure your success, we recommend that you first take the following Computer Training Source courses or have equivalent knowledge:

  • Microsoft® Office Excel® 2016: Level 1
  • Microsoft® Office Excel® 2016: Level 2

Course Objectives

To create our dashboard, we will use the following functions and techniques:

  • Create Charts from Data in a Matrix Format.
  • Create PivotTables & PivotCharts from Data in a Database Format.
  • Create Form Tools to Create Radio Buttons.
  • Use Validation Rules to Create a Drop Down List.
  • Use Index() and Match() in an Array Formula to pull information from a Database.
  • Choose() to Assign Text to Numbers.
  • Vlookup() to pull Information from a Database.
  • Use The Macro Recorder.
  • Create Macros by Typing Code in the VBA Editor.

Use The Camera Tool that Allows us to Capture an Image of Data that is Automatically Updated in the Image.

Note: Most importantly, we will connect all the data gathering techniques together using macros to make effortless reports.

Course Content

Exercise 1: Workshop Preparation

  • Step A: Downloading the Excel Workshop File
  • Step B: Displaying the Developer Tab
  • Step D: Adding the Camera Tool to the Ribbon
  • Step E: Saving your Excel file Macro Enabled
  • About Macro Security
  • Accessing the Security Settings
  • Trusted Locations
  • About the VBA Macro Editor
  • Editor Navigation
  • Inserting a Module
  • Debugging Your Code

Exercise 2: Update a Chart using Form Controls (Database Structure)

  • How it Works
  • Step A: Creating the Small Table
  • Step B: Creating the Companies Options
  • Step C: Programming the Option Buttons
  • Step D: Use Choose() to Display Company Names Rather than Numbers
  • Step E: Creating the List for the Financials Drop Down
  • Step F: Creating the Drop Downs for Financials
  • Pulling the Information from the Database: Array Formula with Match() & Index()
  • Match()
  • Using Match() as an Array Function (CONTROL + SHIFT + ENTER)
  • Using Index() to Get the Financial from the Selected Row
  • Step G: Type the formula (and Don’t forget Absolute Addresses)
  • Step H: Labeling our Table
  • Step I: Creating the Chart
  • Step J: Testing the Interactive Chart
  • Step K: Naming the Chart: TopChart

Exercise 3: Macro to Update Chart Data Based on Company Name Selected

  • Step A: Creating the Profit Margin Chart
  • Step B: Creating the Price Earnings Ratio Chart
  • About the Macro to Change the Profit Margin Data
  • Step C: Creating the Macro to Swap the Profit Margin Data
  • Using an IF THEN Structure
  • Making our Macro More Efficient by Not Repeating Lines Unnecessarily
  • Making our Macro More Efficient by using Variables
  • Not Using Variables: Easier to Understand but More Typing
  • Shorter Method Using Variables: Less Typing but Slightly more Difficult to Understand
  • USC Marshall School of Business wilmeth@uscedu Dashboards_and_Reportsdocx 5/15/2013 Page 3 of 50
  • Step D: Testing the Macro 29
  • Step E: Assign the Macro to the Radio Buttons 29

Exercise 4: Macro to Update the Price Earnings Ratio Chart

  • Understanding the Macro to Update the Price Earnings Ratio Chart
  • Step A: Creating the Macro to Update the PE Ratio Chart
  • More on the Concatenation Operator
  • Step B: Stringing the Macros Together with “Call”
  • Testing the Macro

Exercise 5: Creating the Earnings per Share Chart (Pivot Table Chart & Macro)

  • Step A: Creating the PivotTable • Step B: Creating the PivotChart • Step C: Copying the PivotChart to the “Dashboard” Sheet
  • Step D: Naming the Earnings Per Share Chart Located on the Dashboard Sheet: “EPSChart”
  • Step E: Creating a Macro to Update the Earning Per share Chart to Match the Company Selected
  • Step F: Testing the code
  • Step G: Calling the “ChangePivotChartSeries” Macro

Exercise 6: Vlookup() and the Camera Tool to Display Company Information

  • How Vlookup() Works
  • Step A: Using Range Names for P7 and Our Database Range (A8:E15)
  • Step B: Writing the Vlookup()s to Populate the Table
  • Step C: Testing Your Vlookup() Formulas
  • Step D: Using the Camera Tool to Display the Table on the Dashboard Sheet
  • Step E: Testing the Camera Table

Exercise 7: Adding Buttons to Change All Charts to Lines or Columns

  • Step A: Alternative One – Creating the Two Macros for Line and Column Charts
  • Step A: Alternative Two – Coding Using a For Each …Next Loop
  • Step B: Testing your Macros
  • Step C: Making the Button to Run the ChangeChartsToColumn Macro
  • Step D: Making the Button to Run the ChangeChartsToLine Macro
  • Step E: Test the Buttons

Exercise 8: Creating a Macro Button to Print the Dashboard

  • Step A: Record Printing
  • Step B: Making a Button to Run the PrintDashboard Macro

Exercise 9: Format the Report as Desired

Share Our Website