Skill Level

Intermediate/Advanced

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.

General How-To

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.

Limitations

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.

Skill Level

Medium

Accessing the Tool

Tableau Public can be downloaded for free on the Tableau public page

General How-To

See Tableau’s resources section for step-by-step tutorial videos, including initial data setup.

Tips for Creating Effective Displays

• Get comfortable with Tableau’s “shelves” system, a drag-and-drop interface for variables. Moving elements to different shelves can dramatically change the display; experiment until you find a good fit. See figure 9 below for an example of different graphics automatically created from one data source in Tableau.
• Tableau has background data layers from census data, such as U.S. population and other demographics, which can be automatically overlaid in any map visual.
• Tableau autogenerates categories like “measure names” that can be dragged into the Details shelf to create custom labels.
• Experiment with the Color shelf for your data, which includes categorical and gradient features in attractive (and not-Excel!) themes.

Limitations

• For the free Tableau Public software, any saved workbooks are available online to the public—so do not use it for confidential data!
• For very large data sets or private data services, Tableau requires the purchase of a Tableau plan.
• When embedding this application, be aware that applications headings may appear that could be distracting for users.

Figure 11: Same data, six ways in Tableau: Percentage of Students Using Free or Reduced-Price Lunch, 1969-1985