We work closely with PG&E, Sandia National Labs and other clients. Our courses are open to all of our clients and we look forward to seeing you in class.

 

Upcoming Classes

 

Loading Events

« All Events

  • This event has passed.

Excel Dashboards

March 1, 2021 @ 8:30 am - 4:00 pm

$99.00

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

Details

Date:
March 1, 2021
Time:
8:30 am - 4:00 pm
Cost:
$99.00
Event Tags:

Venue

CA United States
View Venue Website

Organizer

Jim Rose
Phone
925-847-8262
Email
Jrose@ComputerTrainingSource.com

Tickets

The numbers below include tickets for this event already in your cart. Clicking "Get Tickets" will allow you to edit any existing attendee information as well as change ticket quantities.
Tickets are no longer available

Share Our Website