Do you need a series of matching dashboards?

One per program, school, or state?

Copying and pasting is tedious and destined for typos.

Instead, produce a series of matching dashboards through the magic of lookup functions and drop-down menus.

Save time with my automation process.

You’ll create one template and then auto-magically populate it with the rest of the data.

Want to give it a try?! Here’s how.

Step 0: Get Your Dataset into Excel

Let’s pretend we want one dashboard per program.

Our dataset might look something like this.

(These are fictional numbers, and they don’t add up to the correct denominators, so don’t look toooo closely, ha!)

Step 1: Build the Drop-Down Menu

Click on the cell where you want to create a drop-down menu.

Go to the Data tab.

Click on Data Validation.

Allow a List.

Choose the Source (e.g., the first column of the Data sheet).

It’ll look like this:

Step 2: Build the List of Variables

In the Variable Name column, use Paste Special to transpose the headers from the Data sheet into this Charts sheet:

In the Column # column, tell Excel where that variable lives in the Data sheet.

For example, the Program name is in the first column of the Data sheet, so type 1.

In the Value column, use vlookup to transfer the information from the Data sheet into the Charts sheet.

Step 3: Build the Charts

The charts are simply linked to the values off to the left, like this:

We’re obviously not limited to bar charts.

In real-life examples, I’ve used waffles, icon arrays, lines, donuts, lollipops, histograms, and choropleth maps.

I just wanted to keep the charting piece as simple as possible for this example (so your brain could focus on the links between the drop-downs, lookup formulas, and charts).

Once the charts are finished, use concatenation to write sentences, like this:

Time for the final touches. You’ll add a title and subtitles; color-code by category; and set everything to be printer-friendly and PDF-friendly, like this:

Everything is linked!

When you select the program name from the drop-down menu…

That program’s data feeds into the Values column (thanks to the lookup formula)…

And that program’s data feeds into the charts.

Don’t worry; the recipients won’t see the formulas behind the scenes. And they won’t see the Page 1 watermark-ish mess.

They’ll see their own PDF, with their own data, like this:

In real-life projects, we sometimes add all these dashboards to the appendices of technical reports (simply by using Acrobat to combine PDFs).

Work Hard Once

With this process, you can create one template and auto-magically populate dozens or hundreds of matching dashboards.

No typos!

No tedious copying-and-pasting from Excel into Word or PowerPoint!!

Work hard once!!!

Create one template, and then let the drop-down menus do the heavy-lifting.

Real-World Case Studies

I’ve used this process in consulting projects to:

• Design matching 2-pagers for every state, territory, and tribal area that offers home visiting services (State A had its own 2-pager, State B had its own 2-pager, etc.)
• Design matching 4-pagers for each grantmaking area for a foundation’s board meetings (Focus Area A had its own 4-page dashboard with key metrics, Focus Area B had its own dashboard, etc.)
• Design matching 10-page survey results tables for every university that responded to a survey (University A saw their own survey results, University B saw their own survey results, etc.)
• …and a dozen more over the past decade.

Your Turn

What sorts of how-to questions do you have for me?

Comment below and I’ll answer as many as I can.

Want to get started with Tableau?

Here’s a step-by-step guide to making your very first Tableau dashboard.

In less than an hour, you’ll be able to install the free version of Tableau, add your dataset, create a few charts, and then combine then into a dashboard. You’ll still have time left for formatting, too.

Step 1: Figure Out Which Version of Tableau You Need

There are a few options:

• Public: Free BUT your datasets and graphs will be publicly available.
• Desktop: Paid; about USD $1,000/year per user.
• Server: Paid.

This article compares the versions in more detail.

Step 2: Download Tableau Public

If this is your very first dashboard, I’d recommend starting with the free version, Tableau Public.

Here’s how you’ll install it:

• Visit https://public.tableau.com/en-us/s/download
• Enter your email address and click Download the App.
• The download begins automatically.
• It takes about 5 minutes.

Step 3: Import or Connect Your Dataset

Now, you’ll need to connect your dataset to Tableau.

You can add datasets from Excel, Sheets, QuickBooks, etc.

In this example, let’s pretend we’re visualizing demographic data from an Excel spreadsheet, like this:

Here’s how you’ll add your dataset to Tableau:

• Open Tableau Public.
• Connect to a File: Microsoft Excel. Open your file.
• You’ll see the names of the Excel Sheets on the left.
• Drag in the sheet(s) you want to use for your Tableau dashboard.
• Pre-filter if needed.
• Connect multiple tables if needed. Joins combine the columns of two datasets, and unions combine the rows of two datasets. Joins are most similar to lookup functions in Excel. Unions are most often used when more data is coming in, and you need to add more rows to the bottom of the dataset.

The preview of your spreadsheet will look like this inside Tableau:

Step 4: Drag and Drop Variables to Create One Visual at a Time

We’ll build one chart at a time. Then, in a moment, we’ll combine them into a dashboard.

First, let’s visualize the Work Setting data in Sheet 1. We would drag Work Setting into Rows and Demographics (Count) into Columns. Congrats! You’ve got your first bar chart.

Next, we’ll drag and drop variables to create a Years of Experience histogram.

Finally, we’ll create a color-coded (“choropleth”) map by State.

We’ll create a bar or column chart first, and then simply change it into a map with the Show Me menu.

Step 5: Combine the Sheets into a Dashboard

Now, let’s combine our three individual graphs into a single dashboard.

Here’s how:

• Along the bottom, click on the icon to create a New Dashboard.
• Then, in the Dashboard tab on the left:
  • Choose the device (Desktop, Tablet, phone, etc.).
  • Adjust the size as needed.
  • Drag the Sheets into the main dashboard area.
  • Add Objects as needed (e.g., Blank to add some white space between visuals).
  • The individual graphs can be tiled or floating. I prefer tiled.
  • Check the box to Show dashboard title.

Step 6: Format, Format, Format!

It’s easy to create graphs in Tableau.

Like many software programs, it’s not so easy to format them to be Accessible (508/ADA compliant) and accessible (intuitive).

Here are some bare-minimum edits to get you started.

Apply brand fonts. Go to Format –> Workbook to change all the fonts in your file at the same time (rather than changing one visual at a time). Here’s an article with more info.

Apply brand colors. I personally just go to Marks –> Colors –> More Colors and enter the RGB or HEX codes. Or, here’s an article with a coding option.

I love color-coding by category (i.e., one brand color per graph). This is an easy way to make a dense dashboard feel not-so-dense.

Apply a text hierarchy. Make sure the dashboard title is largest, boldest, and darkest. You’ll also need Heading 1s, and you might need Heading 2s.

As usual, left-aligned text is faster to read then centered or justified text.

As usual, horizontal text is faster to read than diagonal or vertical text.

Don’t forget to turn bulleted tooltips into complete sentences for an extra boost of accessibility.

Your formatted dashboard might look something like this:

Step 7: Share the Dashboard

That’s it! Let’s share it with colleagues.

In the upper left corner, click on File. Choose Save to Tableau Public. Log in. Or, create a username.

Once the dashboard is saved to Tableau Public, you can save the dashboard as an image file, a PDF, a PowerPoint slide, etc.

Learn More

If you’d like to learn more about getting started with Tableau, check out Dashboard Design. This 4-course bundle includes a half-day course on Tableau.