In that before/after transformation, our goal was to make the years along the horizontal x-axis legible. Before, the labels were too small (size 9) and they were diagonal (which is slower to read than plain old horizontal text). We freed up space by abbreviating the years (1985 to ’85). We also opted to label just four points along the line to emphasize key milestones.
We also re-wrote the slide’s title. For slides, I aim for short titles (a couple key words). When the presenter is physically present, the presenter’s voice can elaborate on the cool parts of the graph. The fewer words, the better. You don’t want to lose your audience’s attention, i.e., you don’t want them to be reading full sentences on your slides while you’re speaking. For other types of materials (like reports, handouts, and infographics), I use storytelling titles. Storytelling titles might be 6 to 12 words long. Storytelling titles give you room to elaborate on the cool parts of the graph, like the takeaway finding or “so what?”
Measure Your Text’s Reading Grade Level
While redesigning this graph, it took me a while to understand the slide’s title. There were a lot of technical terms that I wasn’t familiar with. I was also concerned about the title’s length.
I wanted to test my gut instinct. There are several free and low-cost tools for objectively measuring text readability.
I’ve used https://readable.io/ for years and love it. You’ll need to enter your email address. Then, you can access the free portion of the tool. You get 15 minutes of free usage each day. Or, you can pay $4/month for the pro version. I don’t get paid to promote Readable, but I probably should! I love sharing this tool with others.
I pasted the before title into Readable’s website: “Stage 3 (AIDS) Classifications and Deaths of Persons with Diagnosed HIV Infection Ever Classified as Stage 3 (AIDS), among Adults and Adolescents, 1985-2014 US and 6 Dependent Areas.”
The before title scored a D, yikes! The before title was a 14.2 grade level, which is equivalent to a high school diploma and two years of college.
Yes, this graph was designed for people who had college degrees. Just because your viewers can read at a 14.2 grade level doesn’t mean they want to read at a 14.2 grade level. Reading at or above your level would feel like homework. Do you want your graph to feel like homework??
(P.S. Readable’s website got a makeover since I wrote this blog post, so your view will look a little different.)
Measure Your Text’s Reading and Speaking Times
Readable also measures reading and speaking times. Our title took 6 seconds to read and 11 seconds to speak! The epidemiologist could lose her audience for an entire 6 seconds.
On another recent project, I measured a company’s entire draft report with Readable’s website. The report was around 100 pages long, and Readable told us it would take someone 16 hours to read! The report was designed for state-level policymakers. Are we really expecting a busy policymaker to set aside two full working days to read a report? We’re all inundated with information. Cut down your document’s length by focusing on essential content. Push non-essentials to an appendix. Then, make the remaining text faster and easier to read.
Edit Your Writing and Try Again
I read the title again. And again. And again. And I finally realized that it was just talking about AIDS diagnoses and deaths.
I tried that new title—AIDS Diagnoses and Deaths. It scored an average grade level of 6.5. Hooray!
Your audience may not be the general public, of course. Your audience might be your boss, or your Board of Directors, or state-level policymakers. Their reading levels may be much higher. Your audience doesn’t want to read at their peak ability for hours. Don’t make your graph feel like homework!
This shortened title will only take the audience a second to read—six times faster than the original.
Still Not Convinced? Here’s When It’s Time to Care About Reading Levels…
Look for these clues from your readers:
“Well.. I can tell that really smart people worked on this project.” The first few times I heard this feedback, I mistook it for a compliment. I thought, “YES!!!! I used all the terms from my grad school stats classes correctly!” Now, if I hear that feedback, I cringe. This “compliment” is a sign that your documents are too technical. It’s time to revamp your graphs and your writing style.
“The report was really… comprehensive.” Ouch! This “praise” is a sign that your documents are too dense.
“Thanks for sending the report to us. We’ll let you know if we have any questions.” Ouch! This “engagement” is a sign that readers aren’t connecting with your documents.
Lowering your document’s reading grade level won’t solve all your reporting problems, but it’s a first step.
7 Practical Tips for Improving Your Text’s Reading Level
Sold??? Ready to improve your readability???
I love Readable because it gives you practical suggestions for improving your writing. For example, in our before title, the website highlighted the word classifications in a darker color. It also told me I was using too many long words and that the sentence was too long.
Here are practical tips for lowering your text’s reading level:
Find synonyms for technical terms. In this project, we changed classifications to diagnoses. In another project, we changed the counterfactual to comparison group and described the nitty gritty details of the counterfactual analyses in the appendix. In another project, we changed at baseline to when people enrolled in the program.
Avoid acronyms. I don’t care if you define the acronym the first time you use it. I shouldn’t have to flip back to page 1 while I’m trying to understand page 10. I shouldn’t have to memorize acronyms in addition to understanding your content and visualizations. When in doubt, spell it out.
Use shorter words. Look for words with lots of letters and lots of synonyms, and then find replacements.
Write shorter sentences. One of my personal weaknesses is writing run-on sentences. As I edit my own writing, I break long sentences into several short sentences. Search for your commas and semi-colons. Replace them with periods.
Write shorter paragraphs. Several short paragraphs > one long paragraph.
Write narrower paragraphs. I was That Nerd who took a speed-reading course during high school. I learned about how our eyes scan a page from left to right to read a line of text. I was surprised to learn that we don’t actually look alllllll the way to the left or allllll the way to the right. Instead, our eyes stay towards the center and we take advantage of our peripheral vision to scan the words on the far left and far right. Accordingly, there are studies about how long we should make each line of text (i.e., how wide or narrow our paragraphs should be). Results are somewhat mixed; readers have personal preferences about exactly how many inches wide they prefer. Nobody can speed-read a super long line of text, though. In portrait layouts, I use one or two columns of text. In landscape layouts, I use two or three columns of text.
Remove redundancies. Say what you need to say—once! Avoid repetition across your sentences, graphs, and tables. I’ve got an upcoming before/after makeover blog post where I’ll show you how to remove redundancies.
I also have a personal preference for active voice and first-person writing. I can’t connect with reports that sound like they were written by robots.
Lowering your graph’s reading level is not the same as dumbing-down your graph. Lowering your graph’s reading level shows that you respect your audience. You recognize that these are busy, important people. Busy, important people have lots of busy, important priorities. Your graph is one of many, many pieces of information to come across their desk each day. Give your audience the information they want in a format that doesn’t take all day to decipher. Then, your audience can make informed decisions and move on.
Have you used other readability tools? Share them here!
You want to display a lot of historical data–great! But sometimes we have so many points in time that our graph’s labels get smooshy. In this post, I’ll show you a before/after data visualization makeover in which we selectively labeled a few key milestones in order to tell our story (and make the graph more legible).
Here’s an epidemiologist’s before slide.
There are a lot of things going well in this slide already. For example, the graph already contained direct labels, the words classifications and deaths, rather than having a separate legend. Direct labels save viewers time because viewers don’t have to zig-zag their eyes across the slide and hunt for information. Direct labels are also better for people with colorblindness and for grayscale printing scenarios.
Another feature that was already working was the branding. This epidemiologist’s center used these exact shades of turquoise, blue, green, purple, red, and yellow across all publications. They also used Calibri in all publications. We didn’t need to change the colors or fonts.
Our priority on this graph was to make the line graph’s years easier to read. We wanted to avoid diagonal text, which takes longer to read than horizontal text. We also wanted to avoid the 9- point font. The years were barely legible for people sitting in the front row, let alone the back row. If nobody can read your graph, why bother?
Here’s our makeover. We:
Transformed the title into a subtitle;
Wrote a new title, which is purposefully short for a live conference presentation (we didn’t want the audience to be reading a lengthy title while the epidemiologist was speaking);
Deleted the vertical label on the y-axis;
Deleted the horizontal label on the x-axis;
Abbreviated the years (1985 to ’85) to save a little extra space;
Only labeled a few milestone years;
Added a few numeric labels along the line (e.g., 77,173 diagnoses in 1993);
Increased all font sizes to 18 (and a 32 title); and
Removed the logo.
Two techniques fixed our smushed labels. First, abbreviating years—from 1985 to ’85—saves some space. Second, only labeling a few years—’85, ’93, ’96, and ’14—saves space and focuses attention on the milestones of your choosing.
We intentionally drew the audience’s attention to ’93 and ’96 to tell our story and match the epidemiologist’s talking points, as described below.
Storyboarding for Live Presentations
Here’s how I envision the epidemiologist presenting the information. She would storyboard the information across three separate slides.
First, she would present this slide, and say: “We track how many people in the U.S. are diagnosed with and die from AIDS. This graph shows how many people were diagnosed and died between 1985 and 2014. In 1985, nearly 12,000 people a year were diagnosed with AIDS. In 2014, closer to 20,000 people a year are diagnosed with AIDS. And of course, there have been some ups and downs over the years, which we’ll talk about next.”
Second, she would present this slide, and say: “I’m sure you noticed this peak right away. Seventy-seven thousand people were diagnosed in 1993. In 1993, there was a peak in diagnoses, probably because we expanded our definition.”
Third, she would present this slide, and say: “1996 was another milestone year. Beginning in 1996, we’ve seen fewer deaths, probably because of the success of highly active antiretroviral therapies.”
In our makeover, the horizontal axis is actually legible, hooray!
We also transformed a single slide into three slides. The epidemiologist’s speaking points would be identical in both cases. Her pace would also be identical in both cases. She doesn’t need to talk any faster just because she’s got three slides instead of one. Only the visuals would change. Adding more slides doesn’t make my presentation any longer. Adding more slides guarantees that her audience’s attention is focused on the right part of the graph at the right time.
Have you encountered smooshed line graph labels in your own projects? How have you fixed this common problem?
I met Mia Schmid in 2017 when she enrolled in one of my Dashboard Design workshops. Later, she reached out and shared her own before/after makeover based on what she learned in the training. Her makeover was so dramatic that I wanted to share it with you, too. Great work, Mia! –Ann
In my current position, I am responsible for compiling quarterly data across our six programs as part of organization-wide internal reporting. In the past, I populated the tables (below) to report out on progress towards annual goals.
This table did a terrible job of communicating what we needed to know. The multiple columns of numbers didn’t make clear what, if anything, staff should take away from this. And it didn’t effectively communicate progress over time to inform our decision-making.
Last summer I took an online course with Ann on dashboard design in Excel. Using what I learned in the course, I transformed the ugly tables into a dashboard (below). I created data bars to communicate percentage progress towards annual goals and sparklines to show trends over time. I also integrated my organization’s branding using colors for each of the four issue areas we work on.
In the original table, we had included separate columns to compare how each program did in 2016 to 2017. Now, with this revised dashboard, we can show change over time much more effectively with sparklines and are able to include data from when the organization started in 2013.
This is a huge improvement to how we have tracked organization-wide goals! The dashboard is so much easier to read compared to the table format and is a much more engaging way to communicate our progress than merely throwing a bunch of numbers into a table and expecting staff to make sense of it.
This dashboard also communicates more than just progress towards goals. When I first put this dashboard together I was struck by how many of our programs either exceeded or under-achieved on their goals for 2017. Goal setting is one area we have been working on with each program and this dashboard has also enabled us to communicate to leadership why appropriate goal setting is so important—achieving a goal by 349% signals to me that the target set by the program is questionable. Historically, program staff set goals based on anecdotes and chose numbers that “sounded good” rather than targets that were grounded in past performance and conscious of any expected growth in the coming year.
What was an annoying task is now a fun opportunity to create something meaningful for my organization and wow my colleagues—and it’s possible with just Excel.
Mia Schmid has worked with non-profit organizations for the past seven years. She started working in monitoring and evaluation by accident when she became frustrated by how much data organizations collected and how little they used it to inform decision-making. She’s now on a mission to help non-profits discover how monitoring and evaluation can best fit within their organizational capacity and culture, ideally striking a balance between accountability to donors and learning for program improvement. Mia is passionate about making data useful and actionable and currently resides in Washington, D.C.
I worked in several university research centers before transitioning to applied settings. My favorite position was as a researcher in a longitudinal study of adolescent development. We followed the same cohort of ~175 participants from their preteen years through adulthood. This is and was a major initiative funded by the National Institutes of Health, so we were careful and methodical during every step of the data collection, analysis, and reporting process.
In those formal research settings, I was taught to analyze the data first and visualize the data second. We crunched our numbers and poured through dozens of pages of tables during staff meetings (SAS print-outs in tiny font, which was oh-so-readable). Much later on, while prepping for a conference presentation or journal article submission, we designed graphs to showcase key findings.
Although I learned data analysis and data visualization as two distinct phases, nowadays I mix and mingle them a lot. Our brains can spot patterns in a visualization much faster than we can read through pages of numbers in tables.
I use exploratory visualization techniques early and often. My favorite exploratory visualization techniques are sparklines and data bars. I use these techniques daily, and I want to share them with you, too.
Here’s a two-minute tutorial about sparklines, which are mini line graphs.
I use sparklines during early exploratory analyses when I’m trying to spot juicy patterns. Are my numbers generally going up? Going down? Are there lots of peaks and valleys? Or are the numbers generally flat? I insert these quickie visuals and then sit back and reflect on the patterns I see.
I also use sparklines in dashboard projects, like when I need a one-pager to show to others.
Here’s how to create sparklines:
How to Insert Sparklines - YouTube
Here’s another two-minute tutorial about data bars, which are mini bar charts:
How to Insert Miniature Bar Charts Called Data Bars - YouTube
Want to Learn More? Enroll in Simple Spreadsheets: From Spreadsheet Stress to Superstardom
Simple Spreadsheets is the course that I wish was available earlier in my career. It goes broad and deep: I included nine videos about sparklines and seven videos about data bars in the full course.
I mastered SPSS and SAS (and dabbled in STATA) during my undergraduate and graduate programs. But then I went into the real world. And even though I worked for a large research firm, I didn’t have access to the same statistical software packages that I learned about in my university courses. I had to teach myself to analyze data in spreadsheet programs like Excel.
I learned lots from coworkers. I borrowed books from the library. I went to every seminar, brown bag, and conference presentation about analytical techniques that I could find. But it still took forever to feel as comfortable in Excel as I did in SPSS and SAS. That’s why I designed Simple Spreadsheets–to make your transition smoother.
Whether you’re visiting a web site or a listening to a presentation, you’ve probably thought about data visualization as a helpful way to digest complex information. At its best, “data viz” communicates data simply and efficiently using a combination of graphs, graphics, and other forms of design.
What makes a good graph great? Let’s answer that question by taking a walk down memory lane.
Earlier in my career, I was a researcher for a large nonprofit in Washington, DC. We gathered for all-staff meetings every few months, all 100 of us standing like sardines inside our largest conference room. In those meetings, we needed to know more about the teenagers and families we were working with in the community and whether our afterschool programs were effective.
When it was my turn to report on the data, I would stand in front of my fellow sardines and show a slide like this on our tiny, grainy projector screen.
Staff squinted, straining their eyes to read small, diagonal text. I mumbled through a few talking points, like how we worked with a lot of Latino teenagers. They clapped, sort of, when I was finished, and the next person on the agenda began her presentation.
Nowadays, I know better. Here’s how I would revamp that slide if I were to design it today.
First, I would remove a lot of unnecessary ink. I would delete the logo and the redundant sentences and remove the gray background shading. I would also remove the 3D effect, which was distorting the data—making the columns look taller or smaller than they really were.
Next, I would more intentionally use text to help tell a story. As an organization, we spent a lot of time serving Latino and African American youth in the surrounding neighborhoods. In the original column chart, the names of the ethnicities were too close together to be legible to staff in the back of the room. I would rotate the vertical column chart into a horizontal bar chart, then reorder the categories so that the Latino and African American youth would be listed first.
Finally, I would apply strategic branding and design choices. This includes incorporating the organization’s fonts and color palette and using dark colors and an annotation to help key numbers pop.
1. Remove a lot of unnecessary ink. At a bare minimum, we needed to remove unnecessary design elements. In the example below, we removed the graph’s border, generic title, and vertical and horizontal lines. We also adjusted the wording and the colors.
2. Intentionally use text to help tell a story. As data visualization becomes more popular, I often see people focus so much attention on creating good images that they forget about the text. Both can work together to tell your story. Here’s how we translated the bullet points from our spreadsheets into full sentences.
3. Apply strategic branding and design choices. We started with the NHVRC color palette. You might recognize these colors from the NHRVC web site. The “before” version of an NHVRC state profile is displayed below on the left. It’s colorful, sure, but the colors are primarily used to decorate the page. In the “after” version (displayed below on the right), we intentionally color-coded items by category. Information about children has turquoise text and graphs, while information about families uses purple text and graphs. We wanted readers to spot the two colors—and therefore distinguish between the two buckets of data—instantly.
We also used dark colors intentionally within sections. Do you see the turquoise bar chart depicting age ranges? Light turquoise corresponds to less age (i.e., younger children) while dark turquoise corresponds to more (i.e., older children). We applied darker colors intentionally in the waffle chart at the bottom of the page, too.
In the visual perception world, we call dark color a preattentive attribute. Our brains are wired to notice dark colors without any conscious thought. We can instantly see the difference between dark and light colors without even having to think about it!
This is one of the happiest days of my career. I’ve been running my own data design company for four years. And for four years, I’ve been itching to build online courses. I’ve delivered dozens of in-person workshops to thousands of people around the globe. But I haven’t taken that training online… until now!
Today, I’m launching Simple Spreadsheets: From Spreadsheet Stress to Superstardom in 12 Weeks, which is the first of several online courses. This course is all about data analysis time-savers. Spreadsheet skills are the foundation of good data visualization. You need to be able to pour through huge datasets and find the gems that are worthy of visualizing. You need to manipulate your tables, rows, and columns to get formatted juuuuuust right. And most importantly, you need to be able to analyze data quickly and without mistakes.
I’ve spent the past 10 years teaching myself all of these tricks. I took classes. I checked out books from the library. I searched through hundreds of (really bad) videos. I learned the hard way so you don’t have to. The Simple Spreadsheets course is a shortcut. This is the course I wish I took earlier in my research career. In 12 weeks, I’ll teach you what it took me a decade to learn on my own.
What We’ll Cover: 8 Hours of Content Over 12 Weeks (The Equivalent of Taking a Two-Day Workshop)
We’ll go broad and deep. You’ll learn about everything from concatenate to sparklines to pivot tables to vlookup. There’s even an entire bonus module on data visualization!
You’ll get access to 8 hours of training, which is the equivalent of getting a two-day workshop. Or, put another way, you’ll get 30- to 60-minutes of brand new content each week, which is the equivalent of getting a webinar a week for 12 weeks.
As soon as you register, you get access to your first bonus module, The Absolute Beginner’s Guide to Spreadsheets. You’ll get comfortable with the lingo. You’ll insert icons, which have been shown to make our numbers more memorable. You’ll save time with keyboard shortcuts.
Week 1 is Navigate Your Spreadsheets. You’ll learn exactly what to do when you start a new project–like building a Data Dictionary, naming your files well, and converting CSV files to Excel files. You’ll also learn the five questions I ask myself at the beginning of every project.
Week 2 is Merge Variables Together and Pull Them Apart. Most datasets come to us in pieces. You might have a few variables in one file and a few other variables in another file. You’ll combine all these disparate data sources into a single, master dataset that you can use for your analyses and for your visualizations. You’ll join tables with lookup functions and concatenation, and then peel them apart with a technique called text to columns. I wish I learned about these skills sooner! I can’t tell you how many days I wasted doing it the long (wrong) way.
Want to take a sample class? Here’s one of the Week 2 lessons about concatenation, which is a shortcut to merging variables from different cells together into a single cell. I use concatenation on a daily basis on my visualizations, e.g., to combine both numbers and percentages in my graph’s labels.
Combine Content from Several Cells into a Single Cell with Concatenate and & - YouTube
Week 3 is Format, Clean, and Recode the Data. You’ve built a master dataset. Now it’s time to clean up that messy data! You’ll learn my favorite techniques for transforming variables like dates, names, numbers, percentages, and currencies into formats you can actually use. You’ll also recategorize values with techniques =if, which are must-have skills for anyone dealing with data.
Here’s a sample class from Week 3 about text to columns, which is one of the techniques I use regularly during the data cleaning phase:
Place First and Last Names into Separate Columns - YouTube
Weeks 4 and 5 are Explore the Data with Sparklines and Data Bars. These exploratory data visualization skills will change the way you look at spreadsheets! New to sparklines? You’ll create them for the first time. Familiar with sparklines? You’ll adjust the colors, axes, and data source. These might be your favorite weeks of the entire course.
Weeks 7 and 8 are Summarize the Data with Formulas. Most data courses deliver a totally random smattering of formulas. In this course, I’m focusing on the analytical techniques that you’ll need most often, especially if you’re a scientist, researcher, or evaluator. You’ll see familiar faces like average and median. You’ll also meet new friends like kurtosis, quartiles, countif, and sumif. I’ll explain what these terms are, how to create the formulas, and how to apply the techniques to your own projects. You’ll practice along with the spreadsheets provided.
Week 9 is Summarize the Data with Pivot Tables. If you’re brand new to pivot tables–prepare to have your mind blown! And if you’re familiar with pivot tables, prepare to learn some lesser-known features that you may have missed. This will probably be one of your favorite topics from the entire course because it’ll be the biggest game-changer and time-saver.
Weeks 10, 11, and 12 are Putting It All Together. These three weeks transform a good course into a great course. You’ve learned the basics. Now it’s time to apply your skills in new ways! We’ll walk through three datasets–one a week for three weeks–from start to finish. You’ll have the materials and can practice along with me. I’ve got a sense of which spreadsheets we should cover, but I’m open to your requests, too!
Bonus! Week 13 is Getting Started with Charts. After you’ve crunched your numbers, it’s time to showcase key findings with great visualizations. You’ll create, declutter, color, label, re-size, and export your chart.
Limited Openings & Limited Enrollment Window
Enrollment is limited. You deserve to have your questions answered, and you’ll get my full attention in a small setting. I’m only taking 50 students. The last time I offered this content, we sold 230 seats! I do expect Simple Spreadsheets to sell out.
And I’m only offering Simple Spreadsheets once a year. When it’s open, it’s open! But when it’s closed, it’s closed. This is your only chance all year.
Data visualization is fast and easy for me because I have a solid foundation in spreadsheet skills. I feel totally at home in spreadsheets. Nerd alert: I actually enjoyed doing taxes this year (and FINALLY finishing and getting a refund didn’t hurt, either).
I initially learned about spreadsheets in the early ’90s from my nerdy economist dad. I was approximately 4 feet tall and was supposed to be studying for a 2nd grade spelling test. My dad convinced me that learning formulas early and practicing them often would change my life. As a 2nd grader, that sounded ridiculous. As an adult, I’m so thankful for those lessons. We sat together in the home office, staring at the glowing Lotus 1-2-3 screen, and practiced averages and medians. I didn’t know what a median was. But I was promised that it would be important someday. Turns out, my nerdy dad was right. This is the same guy who got me a toolbox for my 8th birthday that same year. I asked for an American Girl doll. Go figure. But I used that hammer and screwdriver just last weekend, so… thanks, dad.
Nowadays I spend the vast majority of my time teaching others about data visualization. But all too often, my students run into roadblocks because they don’t have a solid foundation in spreadsheet skills. A graph that should take them 5 minutes to design ends up taking an hour. They haven’t been exposed to the time-saving shortcuts yet. They’re not sure how to transform their raw dataset into summary tables (which would then feed into their graphs). Data visualization feels overwhelming because the data analysis is overwhelming.
Today, let’s relieve some of those data analysis burdens! I’m going to share my favorite two techniques for categorizing values.
You might need to categorize values if:
You have a list of zip codes but you really just care about the states.
You have a list of states but you really just care about the regions where those states are located.
You have a list of countries but you really just care about regions of the world.
You have a list of ages (0, 1, 2, 3, 4, 5, etc.) but you really just care about age ranges (0-9, 10-19, 20-29, etc.).
You have a list of schools but you really just care about which district the school is located within.
You have a list of test scores (40%, 55%, 70%) but you really just want to focus on students who passed or didn’t pass the exam.
You have a list of body mass indices (19, 24, 29, 32, etc.) but you want to categorize the raw numbers into underweight, normal weight, overweight, and obese.
You have a list of languages spoken but you really want to divide people into those who speak Mandarin and those who don’t.
You have a list of countries where people were born but you really just want to divide people into born in U.S. and not born in U.S.
… and so on.
The raw dataset you’re given is rarely the one you actually want for your analyses. Clean, re-code, and re-categorize until you’ve got the groupings you need.
Recategorize Values with =IF()
There are a couple ways to categorize your values: with if or vlookup.
This first option uses if. In this example, we’ll categorize a full list of produce types (apples, bananas, carrots, or watermelon). In the video, I’m demonstrating these formulas on a PC with Excel 2016. The formulas aren’t unique to Excel, though. You can use these formulas in any spreadsheet program (like Google Sheets, which I actually use more often than Excel these days).
First, we’ll categorize the individual values as either being either an apple or other type of food. I use these simple two-category breakdowns in projects a lot. No, I’m not usually categorizing apples. That would be a fun job though–an apple analyst! But I use this same technique to handle a lot of the transformations in the bullet point list above. For example, you could use this exact approach to categorize a long list of countries into being either United States or other.
Think of if formulas as if-then statements. You have to fill in three pieces of the formula:
A logical argument (a mini statement that includes the greater than symbol, less than symbol, and/or equal to symbol)
What your computer should do if that mini logic statement is true
What your computer should do if that mini logic statement is false
Second, we’ll categorize the individual values as being either a fruit or a vegetable. This formula is a little more complex. It’s called a nested if formula because there are if statements inside of if statements inside of if statements inside of if statements! You’ll feel like a million bucks the first time you get a nested if formula right. But nested if statements are also a lot of work… which brings us to the second option, using a lookup formula like vlookup.
Categorize Values with If - YouTube
Recategorize Values with =VLOOKUP()
Your second option for categorizing your values is using a lookup function like vlookup.
I use lookup functions to join tables together. You can use vlookup in all sorts of scenarios. That’s why it’s one of my all-time favorite features of spreadsheets.
In this scenario–using vlookup to categorize values–we’ll need to build a separate lookup table. In this video, I’ll show you how I have the regular table that I’m trying to fill in. That’s the table where I want to turn my apples, bananas, carrots, and watermelons into apples or others and then into apples or vegetables.
Once we’ve built our lookup table, then we can use vlookup to help those tables talk to each other. I show you how in this video.
Categorize Values with Vlookup - YouTube
You can use either if or vlookup to sort your values into categories. You’ll get the same result either way. If tends to work best when you’ve only got a couple of categories. Vlookup tends to work best when you’ve got lots and lots of categories. Vlookup also tends to work better when your dataset is especially messy (apple and apples and typos like appel and aple). But, to some extent, it’s also a matter of personal preference. Both techniques take a while to master, so go easy on yourself if you have to practice a few times before you’re fluent.
Download the Spreadsheets
Want to practice if and vlookup? Download the materials and practice along with me.
Share some real-life examples. What types of values have you needed to categorize in your own projects? I tried to add a ton of examples to the bullet point list above, but there are infinite ways to apply these techniques.
Like These Techniques? Then You’ll Love Next Week’s Announcement
I’ve been working on something very special for the past few months. I’ve got an exciting announcement coming your way next week. This is a project I’ve wanted to launch for the entire four years that I’ve been running my own data company. And it’s finally ready!!!
Hi! My name is Nick Visscher, I’m an internal evaluator with Denver Zoo. Our data work spans the gamut from collecting guest satisfaction insights to observing preschool kids in our nature play programming. With everything our team does we aim to help our staff improve their programs and our guest’s experience. It’s important for us to disseminate our findings in ways that make our stakeholders (mainly internal staff) want to dig in and explore. Clear, concise, and well visualized reporting is super important and something we are passionate about.
There are so many options when it comes to reporting software, but one familiar face I often find myself turning to is Microsoft PowerPoint. You might not immediately think of PowerPoint when it comes to creating formal reports, but I love using it in lieu of more common applications like Word. Positioning new images and text boxes into a document is just easier in PowerPoint. You don’t need to worry about anchoring items or how inserting new text might change the position of items you’ve already included on a page. It’s a blank canvas without many limitations.
Here are some screenshots from a report that I wrote in PowerPoint. These pages come from our Lorikeet Adventure: Guest Experience Research Brief.
Here are a few tips I keep in mind when using PowerPoint to design my evaluation reports. Note: I’m working in MS Office 2016.
Changing slide orientation: I like a traditional page layout for a report I know my readers will likely print, not the default slide size (16:9 aspect ratio). To change it, I go to the Design pane and create a custom slide size, change the settings to 8.5” by 11”, and select a Portrait orientation.
Making use of the page ruler, gridlines, and guides: In the View pane I always select Ruler and Guides (and sometime Gridlines) so I can make sure key text and objects are in the same position on each page. I also know my readers will likely print and use a staple in the upper left corner so I make sure to keep one guide line at a half inch from the left. This lets me position headers and text where I know a staple won’t get in the way.
Using built in arrangement options for multiple text boxes or images: Don’t spend too much time clicking that left arrow or right arrow to “nudge” objects into just the right place on a page. Select all the objects you wish to align, go to the Home pane, select Arrange, and then select Align. There are built in arrangement options there which perfectly align or evenly distribute everything at once. This is one feature that consistently saves me time and makes everything look better.
Inserting shapes and lines as design elements: I love using basic shapes and lines in different variations on a page to give the design of a report a sleek and professional feel. I avoid predefined slide design templates at all costs, they don’t often follow the principles of good design.
Using fill color and transparency: I’m not always confident in knowing which colors compliment each other so to avoid having to pick different colors, but still give some visual variation, I increase the degree of fill color transparency on key shapes or objects. I also like doing this on cover pages when most often the entire slide background is a photograph. Inserting overlapping shapes with 50% transparency creates a sophisticated visual effect and also adds some darker space on the page perfect for a title or text header to stand out.
Copying page design for the whole report: Most of the time I like my background design and title text to be consistent on each page. Once I’ve created a page layout I like I simply copy and paste that slide for the remainder of the report pages. This is a quick solution to having to re-create the most common design elements in your report over and over again. I’ve used a more elegant solution lately by editing the slide master in the View pane and editing the default fonts and colors in the Design pane.
Editing dataviz directly in PowerPoint: I used to spend a lot of time editing charts directly in Excel, then copy/pasting them into PowerPoint. This would sometimes cause size and formatting issues so I’d have to do a few edits there too. PowerPoint has the same chart editing and layout features as Excel does so now I create a basic default chart in Excel using my data, copy/paste that right away into PowerPoint, and do all of my editing there.
Letting your copy editor do their thing: I always need a copy editor when I finish a report. PowerPoint provides similar review and comment features as Word does. It’s not quite as extensive but it does the trick. Under the Review pane you can select text, add comments, and select “start inking” to highlight areas is the report that need further attention.
Saving your report: Save your report as a PDF or XPS document when you’re ready to send to your readers. They’ll be dazzled by the beauty of your work and be none the wiser that you used trusty ole PowerPoint to design it.
Here’s another report that I designed within PowerPoint. These pages come from our Zoo Lights: Guest Experience Survey Report.
Have you tried writing your reports in PowerPoint instead of in Word? If so, please share your tips in the comments section below.
Nick Visscher is the Audience Research & Evaluation Manager at Denver Zoo in Colorado. His academic background is in Museum Education, and Zoo & Aquarium Science. Before the Zoo he was an instructor in the Museology Graduate Program at the University of Washington where he taught courses in Audience Research & Evaluation and fell in love with the process of data visualization, and presentation design. He wishes to thank Ann for this blog and the continued inspiration it provides! You can email Nick at email@example.com or follow him on Instagram @nickvsppt.
Nothing bothers me more than watching important information decay inside of Dusty Shelf Reports. Most “professional” reports are long, dense, unused, and forgotten about.
Unfortunately I’ve got plenty of personal experience with Dusty Shelf Reports…
I used to write hundred-page reports for everyday people that had that were filled with statistical jargon that only a college professor could understand. I was trained to write formal academic reports in college and graduate school. But I wasn’t sure how to transition my reporting skills in the real world.
Now, I design briefs, summaries, slideshows, dashboards, and infographics instead of Dusty Shelf Reports. My audience can read and understand the information, which means my data actually gets used.
Like this annual report for Technoserve…
…and dozens of state profiles for the National Home Visiting Resource Center…
… and this survey report makeover for a foster care agency…
… and this slideshow makeover for a school district.
Last week, I launched a free online course to help you Soar Beyond the Dusty Shelf Report. I learned the hard way so you don’t have to.
In this seven-day jumpstart, you’ll see before/after makeovers from real projects. Whether you’re revamping an entire report or starting small with a single graph, there’s only one way to eat that elephant–one bite at a time!
I selected my favorite techniques. These are the strategies I wish I knew sooner. You’ll learn about data visualization, dashboards, and designing rockstar reports inside of software programs that you already have.
I also selected a a variety of topic areas: public health, accounting, zoos (!), human services, satisfaction surveys, and juvenile justice. You can apply these techniques to any topic area that you’re working in.
After my sessions, the clients who sponsored the workshop ask their participants to rate my session. Sometimes the satisfaction surveys are electronic. Other times, the satisfaction surveys are conducted with good ol’ paper and pencil. The paper surveys get scanned and a software program automatically compiles the results.
Survey software tools have come a long way. During college, I spent ten soul-crushing hours each week entering data from paper surveys into SPSS in exchange for course credit. I paid the university for the privilege of entering data! (And yes, I learned a ton along the way, and still use those statistics and research methods skills daily.) When I was a research assistant, the survey scanning tools hadn’t been invented yet. Nowadays, research assistants probably have more meaningful tasks than data entry. It’s a different world.
I’m grateful for this automated reporting technology. But, at the same time, survey scanning tools drive me crazy. Their designs still live in the dark ages—it’s 2018 software with a 1998-era knowledge of graphic design and brain science.
Here’s the most recent survey report that I received. The four-page report comes from a Dashboard Design workshop that I led at a conference last fall.
This software program’s design drives me crazy because:
The title—Integrated Item Analysis Report—is a mouthful. Yes, I know what an item analysis is. I took an entire graduate course about psychometrics. There were dozens of workshop instructors at this conference. We came from a variety of academic backgrounds. Let’s not assume that everyone knows what an item analysis is.
It’s dense. There are borders and outlines around everything. There are even double outlines around the open-ended comments. There’s almost no white space. The information is straightforward—here’s how people responded to each survey question—but the dense design makes it feel more complicated than it is.
The colors and fonts are lacking a soul. The conference had its own logo, fonts, and colors. The organization that sponsored the conference had its own logo, fonts, and colors. Let’s breathe some life and identity into this report.
So many decimal places. I would never change my workshop approach because a tool told me that 88.89% of people answered a certain way. That’s 89%. I’m the audience for this report. I know exactly what’s useful for me and what isn’t. Decimals won’t change my life.
Let’s revamp this report. Tiny edits will make a big difference. This is the step-by-step process that I teach in all of my Dashboard Design workshops, and today I’m sharing the process with you, too.
Start with a Table
Tables are the heart of every quantitative report. We tabulate our numbers and build visuals from those tables. I re-typed the survey questions and the responses into my spreadsheet.
Make sure that you declutter your table by removing unnecessary ink. Tables rarely need all the borders, horizontal lines, and vertical lines that we’re accustomed to seeing. Sometimes I add a few horizontal lines back at the very end of the design process.
I ignored the mean values for each of the survey questions. Agree/disagree scales are ordinal. We can only calculate means for interval or ratio variables.
I also ignored the Neither Agree Or Disagree category. I’m the audience for this report. I won’t change my workshop content based on a middle category. Here’s how I read this report: “Did anyone hate the workshop? Are there are disagree responses? Darn, one person disagreed with every statement. Curmudgeon Effect? A handful of people will be dissatisfied no matter what I do. Maybe that person was having a bad day. Or, maybe they accidentally checked the strongly disagree boxes instead of the strongly agree boxes? I’ll never know for sure. Okay, let’s move on. Did anyone love the workshop? Are there any strongly agree responses? Phew, that’s almost everyone. So now I need to compare the agree and the strongly agree responses. What was holding someone back from checking the strongly agree box? How can I go from good to great?” In most projects, participants tend to be satisfied, so we’re usually comparing the top two choices. The middle categories rarely matter.
One of the most common mistakes that I see among aspiring data visualizers is a lack of text. People get really excited about data visualization. They start churning out more (and better!) visuals than ever before. Sometimes they forget that text still plays an important role. At the top of your report, add plain language that introduces your viewers to what you’re about to show them.
For the title, I changed “Integrated Item Analysis Report” to “Dashboard Design with Ann K. Emery.” At this particular conference, there were dozens of workshops and dozens of evaluation survey reports. The title’s job is to distinguish one report from another. The contents of the report are an item analysis (a question-by-question analysis). But the title of the report needs to contain the workshop’s name and the instructor’s name.
For the subtitle, I wrote, “This report shows the results from the Dashboard Design workshop evaluation survey. If you have questions about this report, please contact So-in-So.” I typically keep my dashboard subtitles to two sentences. The first sentence tells you what you’re about to learn. The second sentence tells you who to contact if you want to learn more.
Apply a Text Hierarchy
A text hierarchy tells your viewers which text is at the top of the food chain. The title should be large, dark, and bold so that it instantly grabs your viewers’ attention. You could also apply ALL CAPS to the title or section headers. Use ALL CAPS sparingly, please. It takes longer for our brains to read ALL CAPS than Sentence case or Title Case. We like having a mix of tall and short letters. This is especially true for people with learning disabilities.
I also made each of the survey questions bold. Later on, I tweaked the font sizes and colors again. The idea is the same. The important information needs to stand out in large, dark, and bold text.
Read Full Article
Read for later
Articles marked as Favorite are saved for later viewing.
Scroll to Top
Separate tags by commas
To access this feature, please upgrade your account.