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