Introduction The goal of this module is to create a dynamic "dashboard" or "Report". A partial image of what we will be creating is shown below. It contains graphs and tables that display information relevant for a presentation or decision maker.
The dashboard is also dynamic. If you click a company's radio button, the data in all of the charts and tables on the dashboard update to display data for the company selected. It also contains drop down lists that allow you to select the financials you wish to view (top chart only), macro buttons to display charts as either lines or columns, and a button to print the dashboard as a report.
Exercise: Excel Dashboard 1.
Displ aying the Developer Tab
The optionally displayed "Developer" tab makes such actions as recording macros and accessing the VBA editor easier and is necessary to access the forms tools. To display the Developer tab: 1. 2. 3.
Right click any existing menu tab and select "Customize the Ribbon". If necessary, select "All Tabs" from the list. Check "Developer" then click "OK".
Update a Chart using Form Controls (Database Structure) The chart shown is based on the data in the small table below it. As you are probably aware, if you change the data a chart is based on the chart will update automatically. In this example, we have provided radio buttons and drop down lists that allow the user t o select what data appears in the table. When they select a different company or change which financials to display, the data in the table will be swapped out to match their choice and the chart will update.
To accomplish this, we need to use the following tools:
Form Tools Choose() Match() Index()
2.
C r e at i n g t h e C o m p a n i es O p t i o n s
We will now create the radio buttons shown to the right. We will be placing these inside an option group so the radio buttons will work as a group. Note the following as you work:
1. 2. 3. 4. 5. 6. 7. 8. 9.
3.
Control click an object to get the grips around it. Right click an object to view its properties or delete it. From the menu, click the "Developer " tab. Click "Insert " and then click "Option Group". Click and drag a box in the location shown in the image to the right. (If you need to reselect it, Control click its name.) Click "Insert " again. Select the Form Control "Option Button". Click inside the option group. Highlight its default text and type " ACME ". (If necessary, Control click to reselect ) Repeat steps 4-7 to create the option buttons for the other companies, typing their name as appropriate. Also, be sure to create them in the order shown. If you desire, you can change the name of the option group or delete it. Highlight the option groups name and either type something else o r delete it.
Programm ing the Option Butto ns
When options buttons are working as a group, clicking one of the option buttons outputs a number that corresponds to that option button. Typically the first option button is 1, the second is 2, and so on. In this section we will tell the buttons to store that number in cell P6. 1. 2. 3. 4. 5. 6. 7. 8.
Control click each option button until all are selecte d. Right click any one of the selected buttons and select "Format Object ". Set the "Cell Link " to P6. Check "3-D shading". Click "OK ". (Optional) Click " Format – Group – Group" to group your selected option buttons. Click in a cell to deselect your option buttons. Click the different buttons and watch cell P6 . It should get a different number for e ach button.
4.
U s e C h o o s e ( ) t o D i s p l a y C o m p a n y N a m e s R at h e r t h a n N u m b e r s
To pull the correct company name out of our database on the "DBFormat" tab, we need to use actual company names, not numbers. We could use Vlookup() or a nested IF() to do this but because it is a relatively small list of companies, we will use Choose(). Choose() is used to assign an item to a number based on its position on a list. Syntax: =Choose(Index Number, Item1, Item2, Item3, etc.) Example: =CHOOSE(2,"ACME","Mom Corp.","Oceanic Airlines","Stark Industries","Tyrell Corp.","Umbrella") Returns Mom Corp. because Mom Corp. is in the second item (Item2) on the list. 1.
2.
5.
In cell P7 type: =CHOOSE(P6,"ACME","Mom Corp.","Oceanic Airlines","Stark Industries","Tyrell Corp.","Umbrella") Select different option buttons and both the number in P6 should the company name in P7 should change. C r e at i n g t h e L i s t f o r t h e F i n a n c i a ls D r o p D o w n
The other interactive part of our dynamic chart is the drop down that allows users to select the financials they wish to view. To populate the choices for the drop down list, the choices must exist as a list in cells as the image shown to the far right. We could simply type them but we already have them on our "DBFormat" sheet but there are duplicates. This technique shows you how to extract unique entries from a list. 1. 2. 3. 4. 5. 6. 7. 8. 6.
Go to the "DBFormat " tab. Click the letter "B" to highlight all of column B. From the menu, click "Data" then the " Advanced " button. Click "Copy to another location". List Range should already be filled in. Copy the list to G1 on the same sheet. Check "Unique records only ". Click "OK ". C r e at i n g t h e D r o p D o w n s f o r F i n a n c i al s
We will now create the drop down choices for our financial data. 1. 2. 3. 4. 5. 6. 7.
Go to the "Dashboard " sheet and click cell K21. From the menu, click the "Data" tab, then "Data Validation – Data Validation…". At " Allow " select "List ". At "Source", select the unique company names on the "DBFormat ". Click "OK ". You should now have a working drop down list in cell K21 on the Dashboard sheet. Copy cell K21 to cell K22 to create the second drop down list. Format K21 &K22 as desired.
7.
T y p e t h e f o r m u l a ( an d D o n ' t f o r g e t A b s o l u t e A d d r e s s e s ) 1. Type the formula shown in cell B21 of the Dashboard sheet and press Control + Shift + Enter
=Index(DBFormat!$A$2:$D$309, MATCH(1,(DBFormat!$A$2:$A$309=Dashboard!$P$7)* (DBFormat!$B$2:$B$309=Dashboard!$K21)* (DBFormat!$C$2:$C$309=Dashboard!B$20),0),4) Unless you use range names (which we should have), absolute addresses never go away. We intend to copy our formula to the other years and down one row so we need to make sure we lock in most of the cell addresses in the formula above. 2. 3.
8.
Copy the formula left and then down to fill in the rest of the table. Change the radio buttons and drop down financials to test the data table.
Labelin g our Table
Now we need to label our chart table. Create the three links in cell B19, A21 and A22. 1. 2. 3.
9.
Click cell B19 and type =P7 Click cell A21 and type =K21 Click cell A22 and type =K22
Creating the Chart
We are now ready to create a chart. 1. 2. 3. 4.
On the "Dashboard " sheet, highlight A20:I22. From the menu, click "Insert ". Click the "Insert Line Chart " button and then the plain line chart button. Drag handle to Move and Resize the chart. We will now link the Chart Title to the company name displayed in P7.
5. 6. 7. 8. 9.
Change chart layout: insert Chart Title element and change position of Legend element Click Chart Title element to select it In the formula bar type =P7 . Press Enter key. Testing: select different companies and financials to see the chart change.
