BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//Computer Training Source - ECPv6.17.1//NONSGML v1.0//EN
CALSCALE:GREGORIAN
METHOD:PUBLISH
X-WR-CALNAME:Computer Training Source
X-ORIGINAL-URL:https://computertrainingsource.com
X-WR-CALDESC:Events for Computer Training Source
REFRESH-INTERVAL;VALUE=DURATION:PT1H
X-Robots-Tag:noindex
X-PUBLISHED-TTL:PT1H
BEGIN:VTIMEZONE
TZID:UTC
BEGIN:STANDARD
TZOFFSETFROM:+0000
TZOFFSETTO:+0000
TZNAME:UTC
DTSTART:20200101T000000
END:STANDARD
END:VTIMEZONE
BEGIN:VEVENT
DTSTART;TZID=UTC:20210301T083000
DTEND;TZID=UTC:20210301T160000
DTSTAMP:20210115T205819Z
CREATED:20201216T005718Z
LAST-MODIFIED:20210115T205819Z
UID:2034-1614587400-1614614400@computertrainingsource.com
SUMMARY:Excel Dashboards
DESCRIPTION:Course number: CTS-1-6-2016 \nCourse length: 1.0 day(s) \nCourse 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. \nTarget Student: This course is designed for students who desire to gain the necessary skills to create dynamic Dashboards/Reports. \nPrerequisites: To ensure your success\, we recommend that you first take the following Computer Training Source courses or have equivalent knowledge: \n\nMicrosoft® Office Excel® 2016: Level 1\nMicrosoft® Office Excel® 2016: Level 2\n\nCourse Objectives  \nTo create our dashboard\, we will use the following functions and techniques: \n\nCreate Charts from Data in a Matrix Format.\nCreate PivotTables & PivotCharts from Data in a Database Format.\nCreate Form Tools to Create Radio Buttons.\nUse Validation Rules to Create a Drop Down List.\nUse Index() and Match() in an Array Formula to pull information from a Database.\nChoose() to Assign Text to Numbers.\nVlookup() to pull Information from a Database.\nUse The Macro Recorder.\nCreate Macros by Typing Code in the VBA Editor.\n\nUse The Camera Tool that Allows us to Capture an Image of Data that is Automatically Updated in the Image. \nNote: Most importantly\, we will connect all the data gathering techniques together using macros to make effortless reports. \nCourse Content\nExercise 1: Workshop Preparation  \n\nStep A: Downloading the Excel Workshop File\nStep B: Displaying the Developer Tab\nStep D: Adding the Camera Tool to the Ribbon\nStep E: Saving your Excel file Macro Enabled\nAbout Macro Security\nAccessing the Security Settings\nTrusted Locations\nAbout the VBA Macro Editor\nEditor Navigation\nInserting a Module\nDebugging Your Code\n\nExercise 2: Update a Chart using Form Controls (Database Structure)  \n\nHow it Works\nStep A: Creating the Small Table\nStep B: Creating the Companies Options\nStep C: Programming the Option Buttons\nStep D: Use Choose() to Display Company Names Rather than Numbers\nStep E: Creating the List for the Financials Drop Down\nStep F: Creating the Drop Downs for Financials\nPulling the Information from the Database: Array Formula with Match() & Index()\nMatch()\nUsing Match() as an Array Function (CONTROL + SHIFT + ENTER)\nUsing Index() to Get the Financial from the Selected Row\nStep G: Type the formula (and Don’t forget Absolute Addresses)\nStep H: Labeling our Table\nStep I: Creating the Chart\nStep J: Testing the Interactive Chart\nStep K: Naming the Chart: TopChart\n\nExercise 3: Macro to Update Chart Data Based on Company Name Selected  \n\nStep A: Creating the Profit Margin Chart\nStep B: Creating the Price Earnings Ratio Chart\nAbout the Macro to Change the Profit Margin Data\nStep C: Creating the Macro to Swap the Profit Margin Data\nUsing an IF THEN Structure\nMaking our Macro More Efficient by Not Repeating Lines Unnecessarily\nMaking our Macro More Efficient by using Variables\nNot Using Variables: Easier to Understand but More Typing\nShorter Method Using Variables: Less Typing but Slightly more Difficult to Understand\nUSC Marshall School of Business wilmeth@uscedu Dashboards_and_Reportsdocx 5/15/2013 Page 3 of 50\nStep D: Testing the Macro 29\nStep E: Assign the Macro to the Radio Buttons 29\n\nExercise 4: Macro to Update the Price Earnings Ratio Chart  \n\nUnderstanding the Macro to Update the Price Earnings Ratio Chart\nStep A: Creating the Macro to Update the PE Ratio Chart\nMore on the Concatenation Operator\nStep B: Stringing the Macros Together with “Call”\nTesting the Macro\n\nExercise 5: Creating the Earnings per Share Chart (Pivot Table Chart & Macro)  \n\nStep A: Creating the PivotTable • Step B: Creating the PivotChart • Step C: Copying the PivotChart to the “Dashboard” Sheet\nStep D: Naming the Earnings Per Share Chart Located on the Dashboard Sheet: “EPSChart”\nStep E: Creating a Macro to Update the Earning Per share Chart to Match the Company Selected\nStep F: Testing the code\nStep G: Calling the “ChangePivotChartSeries” Macro\n\nExercise 6: Vlookup() and the Camera Tool to Display Company Information  \n\nHow Vlookup() Works\nStep A: Using Range Names for P7 and Our Database Range (A8:E15)\nStep B: Writing the Vlookup()s to Populate the Table\nStep C: Testing Your Vlookup() Formulas\nStep D: Using the Camera Tool to Display the Table on the Dashboard Sheet\nStep E: Testing the Camera Table\n\nExercise 7: Adding Buttons to Change All Charts to Lines or Columns  \n\nStep A: Alternative One – Creating the Two Macros for Line and Column Charts\nStep A: Alternative Two – Coding Using a For Each …Next Loop\nStep B: Testing your Macros\nStep C: Making the Button to Run the ChangeChartsToColumn Macro\nStep D: Making the Button to Run the ChangeChartsToLine Macro\nStep E: Test the Buttons\n\nExercise 8: Creating a Macro Button to Print the Dashboard \n\nStep A: Record Printing\nStep B: Making a Button to Run the PrintDashboard Macro\n\nExercise 9: Format the Report as Desired
URL:https://computertrainingsource.com/event/excel-dashboards/
LOCATION:CA\, United States
ORGANIZER;CN="Jim Rose":MAILTO:Jrose@ComputerTrainingSource.com
END:VEVENT
END:VCALENDAR