Free Business Intelligence (BI) Tool

Using Dashboards in Excel

By Heather Friesen, Director of Institutional Research & Effectiveness, Abu Dhabi University, United Arab Emirates

Dashboards and Business Intelligence (BI) have long been part of IR’s nomenclature, and have been developed in many institutions, and in many formats. Some solutions are relatively cost-effective while others cost millions, and some are implemented in an afternoon while others take years to bear fruit.

Those who work in IR offices without large budgets and/or robust Enterprise Resource Planning (ERP) support typically have limited access to these solutions. Fortunately, there are some built-in tools in Excel (2010 and above) that can function as quasi-BI tools, and can create dynamic visuals such as the Enrollment Dashboard below, which are very easy to set up using Slicers and Pivot Charts.

Example: Enrollment Dashboard

The following Enrollment Dashboard was provided as a shared Excel file to deans and chairs during heavy registration periods to assist with section size management and trend analysis.

 

aBI1.JPG
By clicking on the individual “Slicers” on the right hand side, users can filter and explore the data and view trends in the charts on the left, which is much more user-friendly than using column or normal pivot table filters.

How to create an Enrollment Dashboard in seven easy steps:

1) Prepare your data: extract or obtain a normal Excel/flat file of data with variables listed by columns and individual cases listed by rows. In the following case, each row contains one student record, and the columns include student ID, campus, gender, etc.

(Note: the below ID numbers are samples and are not actual IDs).

aBI2.JPG

2) Create a Pivot Chart on a new worksheet:

  • a) Ensure that each column has a title and that no blank columns exist
  • b) Click anywhere in the data (for example, Cell A1) so that Excel knows which data to pivot
  • c) Select Insert > Pivot Chart 
    aBI3.JPG
    aBI4.JPG
  • d) Next, populate the Pivot Chart Fields by bringing “Term” into the Axis (Categories) section, and any field that does not have any null entries, such as “Campus,” “College,” “Course Title,” (“Gender” may not be a good choice here if your institution leaves undisclosed genders as null values in your ERP) into the Values Section. Ensure that the function is COUNT and not SUM:

    aBI5.JPG
  • e) You should now see the following graph and table:

aBI6.JPG

3) Next, drag the graph over the top of the data on the cells in the upper left corner of the spreadsheet (these data can be represented on the graph by adding Data Labels). Once this is done, you can right-click on either of the gray buttons on the chart and select “Hide All Field Buttons on Chart.” Your sheet should now look like this, and you can format the chart in any way you wish (titles, legend, etc.) using the formatting options under Pivot Chart Tools > Format.

aBI7.JPG

4) Next comes the fun part: adding the Slicers. Ensure that you have selected/clicked on your chart (it should have a gray border around it), and select PivotChart Tools > Analyze > Insert Slicer:

aBI8.JPG
5) Once you have selected this, click on the selection boxes beside the variables you wish to include. In this case we have selected Campus, Gender, College, Course, Term, and Student Status. The Slicers will be created and will cascade onto your sheet:1)     

aBI9.JPG

6) Next, simply drag and drop to arrange the Slicers as preferred. You can also re-size the Slicers as appropriate by dragging the handles on the borders. You can also select various colors and formatting options by clicking on the Slicer and using the Slicer Tools to change the format of the Slicer.

 

aBI10.JPG

7) If you wish (and if your monitor real estate permits it), multiple charts can be added, which really expands the power of the tool. In the following screenshot, a second graph including Gender and Student Status has been added to increase the functionality of the data display. Additional charts can be easily added by repeating Step 2 above, but this time select “Existing Worksheet” to tell Excel to place the new graph on your existing “dashboard” worksheet. To connect the functionality of the Slicers to your new chart (so that Slicer selections are reflected in both charts simultaneously), simply right click on your new chart and select all options under “Pivot Chart Tools > Analyze > Filter Connections.”

aBI11.JPG

8) That’s it. Select variables as you wish and see what new relationships you uncover.

A few notes on Slicer behavior:

Slicers are very intuitive to use, but here are a few points to keep in mind:

aBI12.JPG

 

 Comments

 
To add a comment, Sign In
Total Comments: 6
 
Joe posted on 1/14/2016 6:02 PM
Thanks Heather! Love slicers. However, my Apple-user colleagues noted to me that they were unable to access them in files I shared containing slicers. Not sure if that has changed on the Mac side. Would be good to know as I am not a Mac user :-) --Joe
Chris posted on 1/15/2016 8:42 AM
This seems useful, and easy to implement. Thanks for the tip!
Marlene posted on 1/20/2016 10:59 AM
This is a great resource. Thank you for sharing!
Jeanne posted on 2/1/2016 4:04 PM
This is so useful to me, can't wait to play around with it - thank you!
Lee posted on 2/9/2016 2:53 PM
Great tip Heather! This opens up some new possibilities for using Excel in a more dynamic way. Thanks!
Heather posted on 2/11/2016 12:44 AM
Joe - you raise an excellent point. You are absolutely correct that Excel for Mac is highly limited and does not support slicers. It's sad that Microsoft has not supported the development of the Mac version of Office to keep it aligned with the Windows version. On my work Mac I run Parallels for exactly this reason - so I can flip over to a Windows environment when I need to.
Thanks for highlighting this.