Creating dashboard with excel
Download
1 / 26

Creating Dashboard with Excel - PowerPoint PPT Presentation


  • 135 Views
  • Uploaded on

Creating Dashboard with Excel. ISYS 650. Pivot Table Demo. Creating a query from Northwind data warehouse that shows: OrderYear, Quarter, CategoryID, Sales Then download to Excel and create a pivot table and pivot chart to analyze sales based on OrderYear, Quarter and CategoryID.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Creating Dashboard with Excel' - sheldon


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Pivot table demo
Pivot Table Demo

  • Creating a query from Northwind data warehouse that shows:

    • OrderYear, Quarter, CategoryID, Sales

  • Then download to Excel and create a pivot table and pivot chart to analyze sales based on OrderYear, Quarter and CategoryID.


Pivot chart demo
Pivot Chart Demo

  • Click the pivot table to show the PivotTable tools, and then click Pivot Chart.

  • Demo:

    • Drill down/RollUp

    • Reaggregation

    • Row lables’ filter


Monitor business performance products yearly sales
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
From Data warehouse to analytic tool

Data Warehouse

Local Management Database

Analytic Tools




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
Download to Excel to Create Pivot Table/Chart query to compare actual sales and projected sales


Customized with controls
Customized with controls query to compare actual sales and projected sales


Getpivotdata function to extract data from an excel pivot table
GetPivotData Function query to compare actual sales and projected salesTo extract data from an Excel Pivot Table

  • To enable this function:

    • Place the pointer inside the pivot table

    • Click the PivotTable tools button

    • Click Pivot table option

    • Select Generate GetPivotData

  • To generate a GetPivotData function automatically

    • 1. Enter a “=“ in a cell

    • 2. Select the pivot table data you want to extract


Getpivotdata example 1
GetPivotData Example 1 query to compare actual sales and projected sales


Getpivotdata example 2
GetPivotData Example 2 query to compare actual sales and projected sales


Note 1: Allow uer to enter category and year. query to compare actual sales and projected sales

Note 2: Modify the generated GetPivotData function to reference the cells with entered category and year: =GETPIVOTDATA("Sum of ActualSales",$A$3,"OrderYear",B20,"CategoryID",B19)


Excel s activex controls

Excel’s Activex Controls query to compare actual sales and projected sales


Choose controls
Choose Controls query to compare actual sales and projected sales

  • 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
Example query to compare actual sales and projected sales


Spin button
Spin Button query to compare actual sales and projected sales

  • Properties:

    • Max: 500000

    • Min: 100000

    • SmallChange: 10000

    • Value

    • LinkedCell: C9


Option button
Option Button query to compare actual sales and projected sales

  • 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 LinkedCell and use it to display True/False for the option button.

  • Assuming D1 is the LinkedCell for 15-year term option:

    =IF(D1=TRUE,15,30)


Listbox
ListBox query to compare actual sales and projected sales

  • Properties:

    • ListFillRange: Source of listbox’s items

      • A2:C5

    • ColumnCount: Number of columns in ListFillRange

      • 3

    • Linked Cell

      • C11

    • BoundColumn: The column that contains the return value.

      • 2 for 15-year and 3 for 30-year rates

      • Need VBA code:

Private Sub OptionButton1_Change()

If OptionButton2 Then

ListBox1.BoundColumn = 3

Else

ListBox1.BoundColumn = 2

End If

End Sub


Check box
Check Box query to compare actual sales and projected sales

  • CheckBox can work individually.

  • Property:

    • Linked Cell


Exact match vlookup function
Exact Match VLookUp Function query to compare actual sales and projected sales

. What if P4 is entered in B1?

. To do an Exact Match look up:

VLookUp(SearchValue, TableRange, TableColumn#, False)


Other look up functions
Other Look up Functions query to compare actual sales and projected sales

  • 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.


Example1
Example query to compare actual sales and projected sales

  • Sales by products and years:


Recording editing macro
Recording/Editing Macro query to compare actual sales and projected sales

  • Recording macro:

    • Developer/Code/Record macro

  • Stop recording:

    • Developer/Code/Stop recording

  • View the macro:

    • Developer/Code/Macros


Learning vba with macro
Learning VBA with Macro query to compare actual sales and projected sales

  • Spreadsheet operations:

    • Copy/Paste/Fill/Clear/Delete

    • Format cells

    • Adding a worksheet

    • Insert/Delete rows, cols

    • Window scroll

  • Command Bar commands


ad