How to Create an
Interactive Dashboard
using EXCEL
Presentation is everything when using data.
Since THECB’s 60x30 TX interactive became
available, Institutional Research has data to
use. Using Excel to create dashboards from
this data will enhance readability as well as
comparing measures using Excel
techniques.
Higher Education typically has high data
reporting needs with limited budgets. Using
Excel with THECB interactive data can be a
win situation for everyone.
Dashboard using Excel
THECB Web site.
[Link]
acctpublic/
Select Interactive (hi-lighted in red)
Dashboard using Excel
Interactive Reports
[Link]
AcctPublic/InteractiveReport/ManageRe
ports
Able to build custom reports from the
THECB database and download data to
Excel
Dashboard using Excel
Interactive Reports
Click on Create a Report
Dashboard using Excel
Interactive Reports
Select other report criteria, I selected
“Annual Unduplicated Enrollment by
Gender, Ethnicity and Type Major (2-Year)
Click “View Report”
Dashboard using Excel
Interactive Reports
Click “Create CSV” which will download
the file
Dashboard using Excel
Click to open downloaded file
Save file as Excel Workbook
Delete the first row, click on the ‘1’ , right
click and click delete
Dashboard using Excel
After you have downloaded the
THECB data and saved as an Excel
workbook, we will:
Create a table, pivot tables and pivot
charts.
Copy Pivot charts to a sheet we will
name “Dashboard”
Add slicers to the Pivot charts on the
“Dashboard” sheet. Slicers are visual
filters. Using a slicer, you can filter your
data by clicking on the data you want
Let’s Begin
Dashboard using Excel
This is my Dashboard
Slicer Pivot Chart Pivot Chart
Dashboard sheet Pivot Chart
Format Data as Table
Click on a cell within your EXCEL
spreadsheet
Click the HOME tab, arrow beside
“Format Data as Table”
Select your color scheme
Format Data as Table
Click OK on the pop up box
An EXCEL table has been created
A Design tab will be displayed that
shows attributes checked for the
table
Format Data as Table
Header Row – Column headers
remain visible as you scroll down in
your spreadsheet
Banded Rows – Shading alternate
rows in a large spreadsheet to better
distinguish the data
Format Data as Table
Filter Button – Allows you to view
specific rows in an Excel spreadsheet,
while hiding the other rows. A drop
down menu appears in each cell of a
header row
Create a Calculated Column
By entering a formula in one cell in a
table column, you can create a
calculated column in which that formula
is instantly applied to all other cells in
that table column
By typing in the column immediately
to the right of the table, Excel will
automatically extend the table for you.
Place your cursor in the active cell after
the last column in the table
Create a Calculated Column
For this example, we are going to use
the MID formula to obtain the course
subject. In the active cell after the last
column in the table, type =mid(Column
name, starting position, length)
Press enter and a new column is
created. Change the column heading
to “Major Type” instead of Column1
Create a Calculated Column
Creating columns expands the
available data for Pivot Tables and
Pivot Charts
Using the Formula Tab, If option, add a
column for Web Courses
Pivot Table
A pivot table is a program tool that
allows you to reorganize and
summarize selected columns and rows
of data in a spreadsheet table to obtain
a desired report. A pivot table doesn't
actually change the spreadsheet or
database itself
Pivot Table
To begin, Click in Column A, Row 2
(A2) in the Excel Spreadsheet to make
the cell active
From the Insert tab, select Pivot Table
The Create PivotTable dialog box will
appear, click OK
Pivot Table
A blank PivotTable and Field List will
appear on a new worksheet
Pivot Table
Drag or check data boxes to create a
pivot table by DimYear, Gender Desc,
(Rows) and count (Values), Major
Type Desc (Filters) Pivot Table
Pivot Chart
To begin, click inside the pivot table on
a data field. From the Insert tab, click
Pivot Chart
Along with your Pivot Table, you have
a Pivot Chart
Pivot Chart
A chart is displayed, but you can click
on different charts to see which chart
best illustrates your data. Click OK
once you like a chart
Pivot Chart
The pivot table and pivot chart are
shown together.
Customize Pivot Chart
Hide filled buttons, right click on hi-
lighted button and select “Hide all field
buttons on chart”
Customize Pivot Chart
Click inside of chart and the “+” will
appear, click the “+” and see chart
elements
See Chart elements clicked
Customize Pivot Chart
Change Chart and Axis Titles
Click on the title and change
Do the same with each Axis
Customize Pivot Chart
Alternative Chart styles
Click paint brush and alternative styles will
be displayed. Use the right scroll bar to
see different choices. Click on a chart style
Paint Brush Scroll for more options
Click on Chart to
Select
Customize Pivot Chart
Alternative Chart styles
See the Chart Selected
Create Dashboard
Add a new blank sheet clicking the “+”
along the bottom of the workbook
Right click on tab and rename to
“Dashboard”
Move the sheet to be the first
Create Dashboard
On the Dashboard sheet, select a
background fill color for the entire sheet
you can view
Create Dashboard
Copy pivot chart to the Dashboard
sheet. I use Ctrl + V to paste. Slicers
do not always recognize other ways to
paste
Create Dashboard
Add slicers to the dashboard. Slicers
which are visual filters
Click on one of the pivot charts on the
dashboard
Select the slicer option from the insert tab
Create Dashboard
Data columns from the THECB table
sheet are available to use as slicers
Create Dashboard
Move and re-size slicers and/or pivot
charts
Initially Slicers are connected to only 1
pivot chart on your dashboard
Pivot Chart (Copy of) Slicers
Create Dashboard
I created additional pivot tables/charts
and copied these to the “Dashboard”
sheet
Create Dashboard
To connect the Slicers to each Pivot
Chart
Right click inside each slicer, and select
“Report Connections”
Create Dashboard
Click to check all Pivot Tables listed
which correspond to each Pivot Chart
on the Dashboard sheet
Repeat for each slicer. In this example, I
would do this 4 times once for each slicer
Create Dashboard
Clicking on slicer combinations will
change the 3 pivot charts
Slicer data which are blue are active.
Below I selected just the “Academic” type
Create Dashboard
Changing to “Technical” type, the 3
pivot charts reflect just that major type
To select all data inside an individual
slicer, hold the shift key as you select
Create Dashboard
Selecting data from the THECB interactive
option and creating a Dashboard, you can:
Visually analyze the data in multiple ways
Group data fields separately in pivot
tables/charts and use slicers to connect
the information
Slicers are extremely useful when you
create a dashboard page and you want
one click to affect multiple items on the
page.
Conclusion
View the 3 videos listed below,
especially part-3 which demonstrates
the pivot table / pivot chart / dashboard
/ slicer process