A dashboard is useful for displaying timely information on key variables that can be seen on one page or screen. A dashboard connects the viewer to the information, and when that information changes so does the dashboard; this time delay can be seconds, every quarter, or yearly, but you shouldn’t have to create a new dashboard when you need updated information. Dashboards can include information on one child, one school/program, an entire system, or a combination, and they are useful for measuring progress.
Microsoft Excel, one of the most widely used platforms for analyzing and charting data, can also be used to develop data dashboards. You can create the dashboard in Excel, linked to a spreadsheet with the selected data for the dashboard. If you design the data spreadsheet to allow for data updates, you will be able to automatically update the dashboard as you update the data.
Accessing the Tool
Microsoft Office, including Excel, is a licensed suite of software that requires purchase (Office 2015 or earlier) or a subscription (Office 365). To get a free 1-month trial or purchase a monthly subscription or license, go to Microsoft Office products website.
An interactive dashboard in Excel starts with a data table, which can be called the source data, and a list of the graphs, charts and tables that have been chosen to be included on the dashboard. Each of the graphs, charts and tables to be included in the dashboard will be generated from the source data using pivot tables. For more information on pivot tables view the 23 things you should know about Excel pivot tables.
- Items to be included in the dashboard: A data set for the dashboard should be built with the end in mind. You will need to know what the answers to the six data consideration items discussed earlier are. With that in mind, you will generate the source data table and a list of all the interactions across variables and whether they will be depicted via graphs, charts, tables or other formats in the dashboard.
- Source data table: The source data to be used for the dashboard should be organized into one data sheet in tabular format. That means the data set should be organized with one row of headers which describe the data in the column below each title and should have no blank columns, no blank rows, and no merged cells.
- Pivot tables: Once the source data is organized, and you know all the required interactions across variables (list of charts, graphs and tables), you will start generating pivot tables. Generate the first pivot table for the first item on your list of graphs, charts and tables to be included on the dashboard. Repeat the pivot table process for each of the items on your list, which will then, each of them, be saved on a separate worksheet.
- Charts and Tables: For each of the tables you generated using the pivot tables command, on their individual worksheets, generate the appropriate chart or data table. Format consistently across all visual displays to be included in the dashboard (colors, notations, fonts, etc.).
- Creating the Dashboard: After all charts and tables have been generated from each pivot table of data, it is time to create the dashboard itself. Open a new worksheet on the Excel file. Eliminate all gridlines on this worksheet and use the background design that is compatible the design you selected for all tables and charts. Use white background and no lines framing each chart. Once you prepared the background for the dashboard on this worksheet, copy and paste each of the charts into this space. Make sure the graph, chart or table that reflects the most important data point of the dashboard has a prominent space in the dashboard (please refer back to the principles of an effective dashboard for suggestions). Use the format tab in the ribbon to size and align the graphs and charts to what makes sense. Please note, in the format tab you will find the size function. Select the “move but don’t size with cells” option. This will prevent graphs and charts from changing their sizes even when cells change size, if you decide to write a title and other information on the dashboard.
Once you have the charts and tables organized, aligned and sized appropriately in this worksheet, you can copy and paste the information into a word document or link the worksheet to a website as is appropriate for the use and audience planned for the dashboard.
Tips for Creating Effective Graphs in Excel with Examples
The general instructions drafted here describe the basic steps to generate a dashboard using Excel. It does not include the details on how to use pivot tables, and it assumes you will select and design charts or tables based on information from other sections of this document. Excel allows for you to generate dashboards with more features than the ones described here. A quick search about generating dashboards using Excel on “Youtube” should generate a good amount of videos that will explain in more detail each of the steps we described here.
Excel 2013 and earlier versions do not have a native real-time/online dashboards feature. In that case, this would be more of a static dashboard (like a template) in which data could be imported but changes to the data should be edited to the data source manually. Nonetheless, Excel offers a solid platform to creating dashboards, and you can make changes as frequently as new data can be generated. It will require some effort, but once you created one dashboard, you can make as many copies of the file as you wish, and as long as the source data format is maintained, you can generate a new dashboards.
Several web-based applications generate dashboards, such as Salesforce, Dundas, SAP, and IBM among various others. For example, iDashboard uses data from Excel, SQL, Salesforce, Google and other data warehouses to generate dashboards. Here is an example iDashboard that uses K-12 school district data.
For an analysis on how these several applications can help your organization develop dashboards, the Business Intelligence Software has a web-based tool to compare applications (Note that you can sort the organizations based on their application to areas of education, healthcare, or social services by selecting the appropriate “industry” found in the dropdown search option.)
Suggest Additional Tools or Resources
Are there additional dashboard tools or resources that you use to present data effectively? Suggest tools or resources for inclusion in the toolkit.