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