Search Results for “titles” – Depict Data Studio

Line Chart to Small Multiples – Remaking a NSF Graduate Research Fellowship Program Graph

Ann K. Emery — Fri, 11 Apr 2025 21:10:25 +0000

I recently saw this graph on LinkedIn.

The topic caught my attention, but I was worried that the internet weirdos would ignore it because the axis doesn’t start at zero.

People will create all sorts of excuses to ignore data and facts.

I also wanted to try a column chart.

And someone else commented on LinkedIn that the number of honorable mentions – which increased this year – could be helpful to see.

In all my “spare” time on a Friday afternoon, I re-made it:

The dot dot dot title for small multiples graphs… swoon. <3

I love using takeaway titles that extend across the page (to help readers see how the graphs are connected).

Behind the Scenes

More info about my thought process, and some Excel and PowerPoint how-to’s.

What’s Inside

0:00 Welcome
0:20 The original line chart
1:38 The edited column chart
2:18 What about honorable mentions?
3:00 Ann’s drafts
4:30 Finished product – the small multiples column chart
6:23 Behind the scenes in PowerPoint
6:40 Questions? Comments?

Download the Excel File

It’s here: https://depictdatastudio.kit.com/remaking-nsf-graph

My 6 Data Visualization Workshops: What’s Covered in Each Class??

Ann K. Emery — Mon, 12 Aug 2024 15:09:00 +0000

I offer full-day workshops on 6 different topics. All of these classes fall under the broad data communications umbrella.

You can choose from these 6 classes:

Simple Spreadsheets
Great Graphs
Great Graphs in Excel
Report Redesign
Dashboard Design
Powerful Presentations

One class focuses on data analysis (cleaning and tabulating our raw datasets to get them ready for graphs).

Two classes focus on core data visualization skills (translating technical information for non-technical audiences; making sure we don’t just have bar charts; and using colors and fonts that are branded, accessible, and intuitive).

Three classes are advanced, sort of. I call them deep dives because they focus just on reports, or dashboards, or presentations. You’ll need to have your graphs created and edited first (skills taught in previous classes) so they can feed into those reports, dashboards, and presentations.

In this article, you’ll learn more about each of the classes.

Simple Spreadsheets

How to Analyze Data from Start to Finish in Excel

Need to make sense of spreadsheets? Not sure where to start? Chances are, there’s a faster and easier way to get it done.

In this class, you’ll practice a step-by-step process for exploring, cleaning, analyzing, and tabulating your dataset. These spreadsheet skills will save your time, energy, and sanity.

You’ll learn how to:

organize your brand new datasets by adding filters, freezing panes, and keeping raw data separate from clean data;
merge disparate spreadsheets together with lookup formulas;
clean and recode messy data (by checking for missing data and duplicates, and by transforming variables);
run descriptive statistics and frequencies; and
explore data more fully through pivot tables.

This workshop is highly interactive. Each section begins with a demonstration followed by a break for hands-on practice. For example, in the Analyze Data with Pivot Tables module, the instructor will show how to insert a pivot table and drag-and-drop variables. Then, you’ll practice right away, and you’ll be expected to ask questions whenever you get stuck.

Learn More

Learning Objectives

How to Keep Headings in View When You Print or PDF (So Your Excel Table is Easier to Read)

Ann K. Emery — Tue, 18 Jun 2024 15:08:00 +0000

Are you printing or PDFing that Excel table? If it’s a long table, and spills onto a second page, then you’ll need to keep the table headings in view.

Here’s how you can repeat a few rows at the top of each page.

What’s Inside

0:00 Welcome to Dataviz On The Go

0:08 The Visual Appendices

0:22 The PDF/Print Issue, Oops!

0:42 The Best Solution

1:42 3… 2… 1…

Transcript

[00:00:00] I’m Ann Emery. You’re watching Dataviz On The Go, the series where I make quick tutorials for you as I’m racing around between my workshops. I was giving a dataviz workshop recently and we were making these visual appendices for the back of a report. And they look nice in Excel. They’ve got some light visuals here, like the bars to help us find key patterns.

And then you go to print and you’re like, this almost looks right. You know, this, this would be like PDFed or printed or something. Imagine this as the appendix of your report. It looks nice on the first page. You can tell what’s what, the column headers are there, but then on the second page, it’s like, Oh, what’s even going on?

What’s what? Okay. How do you repeat the column headers when your table breaks across multiple pages? There are a couple of ways to do this. I’m going to show you the way that I think is the fastest. I don’t want to waste your time with any like, so, so mediocre solutions. I think the best way is you go to.

Page layout. You go to print titles [00:01:00] and look at this rows to repeat at the top when it’s printed or pdf. You click on the little arrow which minimizes your window and then you go select the row that you want to repeat. Not the whole thing. Because that would be redundant. That would be unnecessary. It’s probably just this juicy row, row six.

And then you go back to your little arrow. It expands the window. You can say, okay, I’m going to click print preview here while we’re in here, just to show you what it would look like. Look, column headers there as usual, right where you expected. And they’re also on the second page. Isn’t that nice? You just repeat a couple of rows at the top of your page.

Three, two, one. Don’t forget to like, subscribe, and share. Not sponsored. What did you say?

How to Visualize Population Projections with Small Multiples Population Pyramids

Ann K. Emery — Mon, 12 Feb 2024 16:08:00 +0000

A few months ago, one of my favorite students brought this graph to Office Hours:

(These aren’t the exact numbers, age ranges, or years. But you get the idea.)

She already knew how to make population pyramids in Excel. Population pyramids require Level 4 vizardy skills. Woohoo!

But she wanted to take the population pyramid a step further, and she wanted to show how the population might change over time, especially for older adults.

So, she followed this online tutorial to add those curvy lines. (Yep, it’s made with a combo chart in Excel.)

Bare-Minimum Edits

As usual, we tackled the bare-minimum edits first:

We removed the legend and directly labeled the data, which helps the graph pass “Big A Accessibility” rules (a.k.a. 508/ADA compliance).
We nudged the bars closer together by reducing the gap width to make more of a smoothed-out histogram.
We outlined the touching filled-in rectangles in white, a small detail that helps with grayscale printing.
We used fewer colors. Before, there were 4 different hues: for 2020 males, 2030 males, 2020 females, and 2030 females. That’s a lot to keep track of! Instead, we color-coded by category (one hue for males, and another hue for females).

We also tried color-coding by year, instead of color-coding by sex, like this:

We were getting closer!

But we wanted to make sure that the forecasted numbers for 2030 were extremely obvious to viewers. For most of us, this isn’t a chart type that we see every day.

I was hesitant to keep the combo chart. This was an apples-to-apples comparison, so I wanted to use all bars.

(The bars/lines combo seemed more like an apples-to-oranges comparison, which this isn’t.)

Small Multiples Population Pyramids

Here’s a Dataviz Rule of Thumb:

Anytime your graph feels too dense… try creating more graphs.

Small multiples layouts can save the day!

The Traditional Version

We tried traditional and storytelling versions of a small multiples population pyramid.

Here’s what the traditional version would look like inside her report.

You’ll notice the topical titles, color-coding by category (one hue per year), and how the graphs are all one color.

The Storytelling Version

And here’s what the storytelling version would look like inside her report.

This version draws attention to the 65+ age group, which she wanted.

You’ll notice the takeaway title, the color-coding by category (one hue per year), the dark-light contrast (highlighting the residents who are ages 65+), and the annotations.

The Bottom Line

Even with the bare-minimum edits, I wasn’t a fan of the original combo chart. The bar-line combo was suggesting an apples-to-oranges comparison.

Instead, anytime your chart feels too dense (when there are literally lines and bars combined within one chart), try a small multiples layout instead!

We can format the chart as a traditional or storytelling version. In this case, storytelling was a perfect fit because we especially wanted to highlight the 65+ age group.

How to Make Interactive Dashboards in Excel

Ann K. Emery — Mon, 18 Sep 2023 15:08:00 +0000

Want to make an interactive dashboard in Microsoft Excel?

Interactive (a.k.a. dynamic) dashboards are a great option for technical audiences that have the time and interest to explore the data for themselves.

They’ll look something like this:

Interactive dashboards are easy to create — sort of. It depends on your existing skill level.

You’ll need four pieces:

A Clean, Contiguous Dataset (maybe stored as an Excel Table)
Pivot Tables
Pivot Charts
Slicers

Are you already using these four features regularly? Great! Linking them together in a dashboard will be easy for you.

Are you new to Excel Tables, pivot tables, pivot charts, or slicers? Be patient with yourself. You’ll need to be fluent in the building blocks before you can put them together seamlessly.

Let’s walk through each of the four pieces in more detail.

Step 1: Build the Clean, Contiguous Dataset

From previous blog posts, you know that table is a tricky term.

There are several different types of tables, like datasets vs. tabulations. In short, a dataset is the underlying numbers, and the tabulation is the summary table.

The Raw Dataset

To build our interactive dashboard, we’ll start with our raw dataset.

This is semi-fictional data. We’re pretending that we’ve downloaded YouTube stats directly from YouTube.

The raw dataset would look something like this, with one entry per date and traffic source.

It’s raw because this is exactly what it looks like when downloaded from YouTube. We haven’t made any changes (yet!).

The Clean Dataset

Next, we’d clean the dataset.

We might check for and deal with duplicates.

We might check for and deal with missing data.

We might add lots of new columns of recoded data. For example, if I want to make a donut chart comparing the internal and external traffic sources, then I’ll need a column (a variable) that categorizes each traffic source as being internal or external.

If I want to make a graph that compares YouTube visits by day of the week (Monday vs. Tuesday views), then I’ll need a column that turns MM/DD/YYYY into weekday. And so on.

(You can learn more about cleaning, recoding, and transforming datasets inside Simple Spreadsheets, my prerequisite course. Again, you’ll need to be 100% fluent in these skills. Otherwise, dashboards will feel daunting.)

Years ago, a coworker taught me to turn all my new variables red so my Future Self could find them. As you can see, I still follow that advice today.

The clean dataset would look something like this:

Required: A Contiguous Dataset

As usual, my clean dataset is contiguous.

In other words, all the cells are touching or sharing a border.

I don’t have dozens of mini datasets (like one per month, or one per traffic source).

You can learn more about contiguous datasets and why they’re necessary for dataviz in this blog post.

Optional: An Excel Table

Next, we might transform our clean dataset into an Excel Table. This step is optional.

As explained in this blog post, Excel Tables are helpful when we need to append tables (that is, when we’ll be adding more rows over time).

Alright, that’s it for the first piece! We’ve got a single, clean, contiguous dataset as our base. We might store it as a regular ol’ table/dataset. Or, we might turn it into an Excel Table for easy appending.

Step 2: Tabulate the Dataset with Pivot Tables

We can tabulate our dataset with either (1) formulas or (2) pivot tables. You can learn more about the pros and cons of each approach in this blog post.

In short, if we’re aiming to build an interactive dashboards… which has to involve slicers… which have to involve pivot charts… then we simply have to use pivot tables.

Again, interactive dashboards are easy — sort of. You have to understand all the nuances of when to use regular ol’ tables vs. Excel Tables, and when to formulas vs. pivot tables, in order to work both backwards and forwards and put everything together quickly and correctly.

Our pivot tables will look something like this:

Interactive dashboards involve pivot tables — plural.

We’ll need one pivot table for each of our charts.

In the finished example, there were four charts + a sum of the total views. That means there are five separate pivot tables behind the scenes.

If you’re familiar with pivot tables, great! Building a few pivot tables for your dashboard will be easy.

If you’re brand new to pivot tables, no worries! I’ve got plenty of beginner-level blog posts to get you started.

Step 3: Build (and Format) the Pivot Charts

Next, we’ll simply add a pivot chart to each of our pivot tables.

In case you’re brand new to pivot charts, here’s how you add them:

Click on the pivot table to activate it.
Go to the Insert tab.
Choose which chart type you’d like (bar, line, donut, etc.).
That’s it!

Please, don’t forget the formatting!!!

Our unformatted chart — which doesn’t pass 508/ADA compliance guidelines — would look like this:

The formatted chart would look like this.

Do you notice the binary color-coding, white outlines around touching shapes, and the direct labels?

Once we’ve built and formatted each of the charts, we’ll simply cut and paste them together into a new sheet. That’s where our soon-to-be-completed dashboard will live.

Step 4. Add a Slicer(s)

Finally, we’ll add a slicer(s) to the first pivot chart.

A slicer is just a fancy name for a filter. They’ve existed in Excel since 2010 (!!!). But, don’t worry if you haven’t seem them or used them before. It takes years for new features to be widely adopted. (Hence the point of blog posts like these — to introduce you to features you might not have discovered before.)

Connect the Slicer to the First Chart

In case you’re brand new to slicers, here’s how you add them:

Click on one of the pivot charts to activate it.
Go to the Insert tab.
Click on the Slicer option.
You’ll see a list of all the variables. In this example, our variables from the clean dataset are Date, Weekday – Number, Weekday – Name, Month – Number, Month – Name, Traffic Source, Traffic Source – Internal or External and Views. If I want viewers to be able to slice and dice by month, then I’d select Month – Name to feed into the slicer.
That’s it!

Connect the Slicer to the Rest of the Charts

The slicer won’t automatically be connected to all of our charts.

We’ll need one more step:

Click on the slicer to activate it.
Go to the Slicer tab.
Click on the Report Connections button.
We’ll see a list of all our pivot tables. Check all the boxes.
That’s it! Now, when we filter data with the slicer, all the charts will correctly filter and change, too.

Final Formatting

As usual, we’ll make sure to follow dataviz best practices.

We’ll need to:

Add words. We’ll need a title, date, subtitles, and explanatory text. Yes, there’s concatenation behind the scenes that automatically writes the sentences for me.
Use brand colors and brand fonts.
Color-code by category. (One brand color per category/section/chart.)
Leave plenty of white space between the charts. My rule of thumb: A thumb’s width (a half-inch or inch of white space between each chart).

Now it’s time to sit back, relax, and let our colleagues have fun exploring the dashboard for themselves.

Learn More

If this tutorial is easy for you, then congrats!!! You’re all set. Go forth and build magnificent, accessible, interactive dashboards for your technical-minded colleagues.

If this tutorial was jargony for you, don’t worry!!! You can walk through each of the steps in more detail, and download the spreadsheets to follow, and come to live Office Hours inside the Dashboard Design course.

How to Make a Series of Matching Dashboards in Excel

Ann K. Emery — Mon, 11 Sep 2023 15:08:00 +0000

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.