Creating Dashboard with Excel ISYS 650 Pivot Table
Creating Dashboard with Excel ISYS 650
Pivot Table Demo • Creating a query from Northwind data warehouse that shows: – Order. Year, Quarter, Category. ID, Sales • Then download to Excel and create a pivot table and pivot chart to analyze sales based on Order. Year, Quarter and Category. ID.
Pivot Chart Demo • Click the pivot table to show the Pivot. Table tools, and then click Pivot Chart. • Demo: – Drill down/Roll. Up – Reaggregation – Row lables’ filter
Monitor Business Performance: products’ yearly sales • The management has an annual sales plan: – A projected sales for each product category – A total annual sales projection • Compare actual sales with planned sales.
From Data warehouse to analytic tool Data Warehouse Local Management Database Analytic Tools
Management Plan Database
Query the data warehouse to get actual sales by year and category
Download actual sales data to the management DB and create query to compare actual sales and projected sales
Download to Excel to Create Pivot Table/Chart
Customized with controls
Get. Pivot. Data Function To extract data from an Excel Pivot Table • To enable this function: – Place the pointer inside the pivot table – Click the Pivot. Table tools button – Click Pivot table option – Select Generate Get. Pivot. Data • To generate a Get. Pivot. Data function automatically – 1. Enter a “=“ in a cell – 2. Select the pivot table data you want to extract
Get. Pivot. Data Example 1
Get. Pivot. Data Example 2
Note 1: Allow uer to enter category and year. Note 2: Modify the generated Get. Pivot. Data function to reference the cells with entered category and year: =GETPIVOTDATA("Sum of Actual. Sales", $A$3, "Order. Year", B 20, "Category. ID", B 19)
Excel’s Activex Controls
Choose Controls • Command button, listbox, textbox, check box, option button, etc. • Developer/Design Mode/Insert • Select and drag controls to worksheet • Right click control to: – Use control’s property window to set property values. – Use View Code to add VBA code.
Example
Spin Button • Properties: – Max: 500000 – Min: 100000 – Small. Change: 10000 – Value – Linked. Cell: C 9
Option Button • For Option Button to work, a worksheet must have more than one Option Buttons that forms a group. • Use the Properties window to enter the Linked. Cell and use it to display True/False for the option button. • Assuming D 1 is the Linked. Cell for 15 -year term option: =IF(D 1=TRUE, 15, 30)
List. Box • Properties: – List. Fill. Range: Source of listbox’s items • A 2: C 5 – Column. Count: Number of columns in List. Fill. Range • 3 – Linked Cell • C 11 – Bound. Column: The column that contains the return value. • 2 for 15 -year and 3 for 30 -year rates • Need VBA code: Private Sub Option. Button 1_Change() If Option. Button 2 Then List. Box 1. Bound. Column = 3 Else List. Box 1. Bound. Column = 2 End If End Sub
Check Box • Check. Box can work individually. • Property: – Linked Cell
Exact Match VLook. Up Function. What if P 4 is entered in B 1? . To do an Exact Match look up: VLook. Up(Search. Value, Table. Range, Table. Column#, False)
Other Look up Functions • Match: Returns the relative position of an item in an range that matches a specified value in a specified order. – Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. • Index: Returns the reference of the cell at the intersection of a particular row and column.
Example • Sales by products and years:
Recording/Editing Macro • Recording macro: – Developer/Code/Record macro • Stop recording: – Developer/Code/Stop recording • View the macro: – Developer/Code/Macros
Learning VBA with Macro • Spreadsheet operations: – Copy/Paste/Fill/Clear/Delete – Format cells – Adding a worksheet – Insert/Delete rows, cols – Window scroll • Command Bar commands
- Slides: 26