Dashboards are a report technique that visually presents critical metrics or a data summary to allow for quick and effective business decisions. Excel is capable of handling complex statistical calculations, many of which are built-in as Functions and can be easily displayed on a dashboard. Excel dashboards allow for quick overviews of data reports rather than sifting through large amounts of data. Overviews aid in making quick and urgent decisions by allowing users to skim through a large amount of information at once and in a short period of time. The user can select a report from a variety of reports contained within the model and then determine where on the page to place it using the dynamic dashboard.
Creating Dynamic Excel Dashboards using Picklists
The following are the steps for building dynamic Excel Dashboards with Picklists:
Step 1: Create a Data Set.
Step 2: Create a Pick List:
- To create a Pick List, first place your cursor in any cell, such as cell G1, and then click the Data Validation button on the Data Ribbon.
- The Data Validation dialog box will appear, In the Settings tab, select the List option in the Validation Criteria area.
- A Source field will be displayed. Select the spreadsheet range containing your pick list value in this field and click on OK.
- If you click on Cell G1, you will now notice a dropdown arrow; if you click on it, you will see your data validation picklist of values.
Step 3: Create a chart.
- First, highlight the horizontal axis categories (years and Profit series):
- Then, on the Insert Ribbon, click the Column button and choose the 2-D Clustered Column chart.
- Now, this is how your chart should look.
Step 4: Change the Vertical Axis.
- Select Format Axis when you right-click on the Vertical Axis:
- The following dialog box will appear: Format Axis.
Step 4: Create a Named Range for the Chart.
- Navigate to the Formulas Ribbon and select the Name Manager option:
- The dialog box below will appear, then click on the New option.
- In the name option, enter ChartColumnSeries as the file name and input the following formula in the Refers to option:
=OFFSET(Sheet1!$A$3,0,MATCH(Sheet1!$G$1,Sheet1!$B$2:$D$2,0),9,1)
Note: The offset formula is described as follows:
=Offset(beginning point, shift starting point down how many rows, move the starting point how many rows right by matching the value in cell G1 to the range of B2:D2, how many rows in the range, how many columns in the range )
Step 5: Update chart series with Dynamic Named Range.
- Right-click on the chart and click on the Select Data dialog box:
- Then select the Profit legend series and click on the Edit option:
- Then, in the Series Values field, enter the name of the named range you defined in the earlier step.
- Finally, Click OK