Hello. I am Barbara Klugman, strategy and evaluation practitioner based in Cape Town, South Africa.

Under the guidance of the inestimable Ann Emery’s “Report Redesign” course, I had a go at making the contents page of a previous evaluation report meaningful.

Here are the steps I followed.

For anonymity, I have renamed the evaluand as (gender org) and the funder as (funder).

The Original

Here’s what the initial Table of Contents looked like:

Draft 1

I cut my multi-page contents page from three heading levels to only Heading 1s.

Draft 2

I changed some headings to be more meaningful.

For example,

• from ‘Summary Report’ to ‘Highlights’
• from ‘Methodology’ to ‘The Outcome Harvesting Approach’; and
• from ‘Contributions that influenced the outcomes’ to ‘The role of (gender org), gender specialists and (funder)’.

Draft 3

I grouped the headings, named the groups, and set it up in landscape.

I also enlarged ‘Contents,’ in response to one of Ann’s ongoing exhortations to “double the size of the headings from what you currently use.”

I moved from Word into PowerPoint.

Draft 4

I created a section divider in my brand colours and added icons.

Ann proposes use of such dividers for short reports, with a different colour for each section – in long reports you’d use a whole page for each section.

I used her ’20-minute page cover’ method by layering a cylinder shape in my brand colour, somewhat transparent, over a Word Cloud, and ‘Contents’ on top.

I added an icon to each section and recoloured the sections to colours I will use for the divider page and headings of each section, taken from my brand colours.

Draft 5

I re-coloured the section divider and put in page numbers.

As ‘gender’ in the Word Cloud overwhelmed the heading, I used the ‘textures’ option in ‘format colours’ to create a grey textured layer over the Word Cloud, and changed the colour of ‘Contents.’

To me this shift from a pro-forma contents page to this version invites the reader to find what they’re looking for in the report.

Going forward, I would plan this out before writing the report, to help organise my own thinking about the contents and how to communicate it.

Thanks to Ann.

Wake up without an alarm… 

Exercise, eat breakfast with the family, work on energizing projects for a few hours… 

… and then call it a day. 

Take the kids to gymnastics. Volunteer at field day.

Take off the entire summer for bucket list adventures.

Months-long road trips around the United States. 

Cruises through Europe.

Passports full of stamps.

Meg K. Wheeler from The Equitable Money Project invited me to speak on her podcast about building a thriving business around your life — not building a life around your business.

Listen to the Podcast

Search for the “Disrupt Your Money” podcast on your favorite podcast app.

Or, listen on Spotify here:

Watch the Podcast

Or, you can watch our conversation:

Learn More

Meg asked me to write a blog post about my experiences, too. You can read that post here.

Inside, you’ll see how my business has evolved over the past decade. I also share behind-the-scenes revenue and expenses info. And, you’ll see five tips for transitioning from burnout to summers abroad.

Your Turn

What types of questions do you have about building a business that fits your family? Comment below!

In May 2023, I led a 45-minute session at Good Tech Fest about using pivot tables to analyze a dataset from start to finish.

The conference recording wasn’t perfect — I got kicked out of the conference session midway, and again towards the end. The audio sounds funky; it sounds… mechanical? Static-y? Unfortunately the conference platform was glitchy for many, many sessions. Lots of presenters got kicked out and had to re-load and re-join. Sigh.

I was hesitant to share the recording and materials at all. But, maybe you’re brand new to pivot tables and something is better than nothing? Fingers crossed that this blog post reaches the right people!

What are Pivot Tables?!

Pivot tables are a great option for our recurring analyses, like weekly, monthly, or quarterly updates to our boss; or for annual reports to our funders.

You’ll create the pivot tables you need just once, so you can save precious time and avoid having to re-do your analyses over and over again for each report deadline.

My Goal

Let’s move you up a half-step within just 45 minutes!

What’s your current familiarity with pivot tables?

• Beginner: “I’ve never heard of pivot tables.” “I heard of pivot tables years ago, but never used them.” “Everything in spreadsheets takes forever.” “Using Excel is the least favorite part of my job.”
• Intermediate: “I used pivot tables a couple times, but I don’t have the hang of them yet.” “I need a refresher.” “I get stuck sometimes.” “I’m not sure if I’m doing it right; sometimes I’m guessing.”
• Advanced: “I use pivot tables daily.” “I could teach others to use pivot tables.”

Did you move up a half-step after participating in the session?

Comment below and let me know!

Download the Spreadsheet

This session was interactive; I demonstrated a skill, the attendees practiced, and so on.

Here’s the spreadsheet: https://depictdatastudio.gumroad.com/l/GoodTechFest

Watch the Conference Session

What You’ll Learn about Pivot Tables

Here’s what we covered during the conference session.

Step 0: Optional: Use An Excel Table

How familiar are you with Excel Tables? Comment below and let me know!

Excel Tables help to future-proof our analyses, making it easier to add new rows to the bottom of our spreadsheet (“append” the table).

Here’s how to add Excel Tables:

• Go into your dataset and click on the upper left corner.
• Go to the Insert tab.
• Click on the Table button.
• In the pop-up window, click OK.
• That’s it! You’ve got an Excel Table.

You probably recognize the banded rows?

You’re probably familiar with filters?

My favorite part of Excel Tables is that they expand easily to handle new columns and rows; i.e., our new entries will feed easily into our Pivot Tables (as long as you “refresh” the pivot table).

Step 1: Make Sure Pivot Table Prerequisites Have Been Met

There are two prerequisites:

• All columns must be labeled. If you add a new column, make sure to give the variable a name. Otherwise, you’ll get an error message later.
• We need contiguous cells. We can’t have any completely empty columns or rows. A “Swiss cheese pattern” with a few empty cells here and there is normal and fine.

Luckily, having an Excel Table makes this step unnecessary!

In other words, if you add an Excel Table, then you’ll automatically have labeled columns and contiguous cells, phew!

In the video, you’ll see me demonstrate what type of errors can arise from non-contiguous cells that aren’t stored in Excel Tables. Knowing what not to do is just as important as knowing what to do.

Step 2: Add a New Pivot Table

Let’s insert a brand new pivot table from scratch!

In the video, you’ll see me go to the dataset… go to the Insert tab… and click on the Pivot Table button.

You’ll see a pop-up window, which can be confusing for newbies. It’s just (1) verifying that your correct columns and rows are selected and (2) asking where you’d like your pivot table.

You can put your new pivot table in:

• a brand new sheet, or
• an existing sheet (like if you’d want multiple tables, linked to multiple graphs, which is great for interactive dashboards).

(In this example, I simply added a pivot table to a brand new sheet.)

Then, you’ll re-name your sheet to stay organized. Don’t leave your sheets as Sheet1, Sheet2, or Sheet3! This is a red flag. It tells me your Excel file is a disorganized disaster.

On your new sheet, you’ll see a few boxes on the right side. Here’s an overview of what they mean:

• Fields: The names of all the variables in your dataset. (The column headers.)
• Filters, Columns, Rows, and Values: The boxes where you’ll drag-and-drop your variables into.

Step 3: Drag and Drop Variables!

You’ll drag-and-drop variables to build a summary table (so you don’t have to learn formulas like countifs and sumifs).

Here are some Rules of Thumb:

• If your dataset has unique IDs, then you’ll drag those into the Values box, and you’ll change them to a Count. (You’ll see me demonstrate this in the video.) By default, Excel gives us Sums, which don’t make any sense for variables like ID numbers.
• Start your analysis by dragging one variable at a time into Rows. (In the video, you’ll see me drag Q1 into Rows.) Then, we get a summary table, like how many people selected Strongly Agree or Agree on our fictional survey.
• Pivot your table! Drag variables into Rows… and then into Columns! This is where the pivot table gets its name. You can rearrange your summary table as many times as you need. I encourage you to trust your gut instinct here!!! You’ll find summary tables that are naturally easier to read then others.
• Then, try crosstabs — where you keep one variable in Values (the ID numbers) and drag two variables into Rows and/or Columns. For example, you might look at how people responded to Q1, by cohort. This is my personal max for pivot tables: one variable in Values, and two in Rows or Columns. Once you add three, four, or five variables into Rows or Columns… it gets impossible to read.

Optional: Re-order variables within the pivot table. For example, you can sort the variables from Strongly Agree to Agree to Disagree to Strongly Disagree (instead of the default setting, which is alphabetical).

Optional: Show percentages in addition to, or instead of, your counts. In the video, you’ll learn how to Show Values As a percentage. Finally, you’ll learn how to round the percentages with the Decrease Decimal button.

Behind the scenes, Excel is calculating countifs and/or sumifs for us. I personally love love love formulas… but I love that pivot tables can bypass these formulas entirely. In the video, you’ll see me create a countifs formula.

Optional: Add quick vizzes, like Color Scales, to your pivot table. Color Scales help the patterns jump off the screen and into our brains.

Step 4: Refresh Your Pivot Table

Anytime you update your dataset… you’ll need to refresh the linkage with your Pivot Table.

You might add new columns. Or new columns. Or make tiny adjustments within the body of your dataset.

You’ll simply click on your Pivot Table, right-click, and choose Refresh. That’s it!

Phew! Right as I was describing the Refresh button, the conference platform kicked me out for a second time. I was definitely crying inside because all my hard prep work was getting flushed down the drain. Sigh.

Did you know… that I teach six different classes?

All of these classes fall under the broad data communications umbrella.

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.

Class 1: 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:

1. organize your brand new datasets by adding filters, freezing panes, and keeping raw data separate from clean data;
2. merge disparate spreadsheets together with lookup formulas;
3. clean and recode messy data (by checking for missing data and duplicates, and by transforming variables);
4. run descriptive statistics and frequencies; and
5. 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.

Learning Objectives

After the two-day workshop, participants will be able to:

• merge data from multiple sheets into a single master dataset;
• organize spreadsheets by adding filters and freezing panes;
• check for duplicates and missing data;
• clean and recode messy data to get it ready for analysis;
• run basic descriptive statistics and frequencies; and
• explore data more fully through pivot tables.

Target Audience

Researchers, evaluators, scientists, graphic designers/communications staff, and admin staff who need to use Excel spreadsheets as part of their job–and it’s not your favorite part of your job.

If you already teach Excel at conferences or in seminars, you don’t need this class. It’s for those of us who didn’t take graduate courses specifically on Excel formulas.

If formulas and pivot tables have always been easy for you, you don’t need this course. It’s for those of us who feel like we’re missing something; that “I don’t know what I don’t know” feeling.

If you’re looking for a dataviz course, this isn’t it. Simple Spreadsheets is about: You’re opening a brand new dataset for the first time. Now what?! You’ll tabulate nominal, ordinal, interval, and ratio variables with the right formulas (you’ll need different formulas based on the type of variable/dataset). You’ll learn the in’s and out’s of pivot tables — and when to use formulas vs. pivot tables. These are the skills that precede graphs, dashboards, reports, and slideshows.

Level

Beginner/intermediate.

Prerequisites

None.

(But this course is a suggested prerequisite for all the other classes.)

Equipment Needed

A desktop or laptop computer (not a tablet or phone).

PCs preferred over Macs.

Software Programs Used

Excel is required.

Newest version preferred (called “Microsoft 365”).

Materials Included

• 2 days of live instruction (two days on-site, or four half-days virtually).
• For virtual trainings: Access to recordings for 1 year so you can re-watch anything you’d like.
• ~6 different Excel files with step-by-step instructions typed out for you.

What Participants Are Saying

“As a 20+ year Excel user I can not BELIEVE the things I didn’t know, and that I learned to master in moments from Ann’s brilliant and engaging teaching. This course is by far the most well prepared and executed online course I have ever taken… The course materials are actual Excel files embedded with the skills, tricks and formulas… As far as who this course is best suited to, I think it could help Excel users of all levels, from beginners to advanced (which is what I would have called myself before taking this course and realizing my knowledge gaps!). For beginners it would be a one-stop shop of all you need to know to excel at Excel. For long-time users it will provide short-cuts, helpful formulas and other tricks you might not have known were hiding in this program. I truly can’t say enough about it, and have already recommended it to research colleagues, data/social indicator experts and policy folks.” – Lynn Davey, Ph.D, Davey Strategies

“I am a university researcher and have a lot of familiarity with data collection and statistical analysis programs/platforms (e.g. SPSS, SAS, etc.), but needed a low-cost, widely-used data collection and analysis tool I could recommend and teach to the community partners with whom I conduct research. I have known for a long time that Excel was likely the solution to my problem, but could never find time to learn to use it, except in the most basic ways (sort, sum). Last summer, I took Ann Emery’s Simple Spreadsheets course and dramatically improved my Excel acuity. *Plus* the course was fun, straightforward, and immediately useful… This course was more than worth the time and money I put into it, and I continue to learn and benefit from it (almost a year later). I highly recommend this course!” – Sarah V. Suiter, Vanderbilt University

Case Studies from Past Participants

You can view participants’ before-after transformations here:

• 6 Data Cleaning Steps to Save You Millions by Hudson Kelley, HOPE International

Class 2: Great Graphs: A Software-Agnostic Introduction to Accessible Data Visualization

Are your takeaway findings getting lost? Busy graphs can slow down the viewer’s comprehension, increase cognitive load, and fail to inform decision-making processes. With intentional editing, we can design graphs that inform and inspire.

During this class, you’ll walk through a step-by-step process that you can apply to your own projects.

You’ll learn how to:

1. customize your visualizations for each of your audiences (technical vs. non-technical, internal vs. external, etc.);
2. choose the right chart type for your dataset (hex maps vs. choropleth maps, donuts vs. waffles, spaghetti lines vs. small multiples, etc.);
3. select an appropriate software program for your needs;
4. declutter your visuals so that viewers’ attention is focused on the data; and
5. use colors and fonts that are branded, accessible, and intuitive.

Learning Objectives

After the two-day workshop, participants will be able to:

• explain when they would use traditional graphs vs. storytelling graphs;
• weigh the pros and cons of presenting data through various chart types (e.g., clustered bar charts vs. dot plots, and choropleth maps vs. hex maps);
• explain how to declutter visuals to make sure the viewers’ attention is focused on the key patterns (e.g., by removing redundant text);
• select graph colors that are branded, accessible, and intuitive; and
• write graph text (titles, annotations, tooltips, etc.) that is branded, accessible, and intuitive).

Target Audience

Researchers, evaluators, scientists, graphic designers/communications staff, and admin staff who need to share data with others through accessible graphs.

Level

Beginner/intermediate.

Prerequisites

None.

(But this course is a suggested prerequisite for Great Graphs in Excel, Report Redesign, Dashboard Design, and Powerful Presentations.)

Equipment Needed

A desktop/laptop computer (not a tablet or phone).

PCs preferred over Macs.

Software Programs Used

None.

This course covers software-agnostic best practices that can be applied to any software program you might be using, from Excel to Tableau to R.

You don’t need to learn coding or graphic design to communicate data more effectively, and you’ll be encouraged throughout the class to continue using whichever software you’re already using.

Most of the examples shown throughout the course have been made with everyday software like Excel.

Materials Included

• 2 days of live instruction (two days on-site, or four half-days virtually).
• For virtual trainings: Access to recordings for 1 year so you can re-watch anything you’d like.
• Handout
• Ebook
• PDF’d slides

What Participants Are Saying

“I would often see charts, graphs, and other data visuals in journal articles, and think to myself, ‘Wow, this is horrible; I have no idea what I am supposed to learn from this!” But I had no idea how to make it better or offer constructive suggestions.  After Great Graphs, I learned about how to select the best type of graph and how to make it visually appealing to the intended audience.  My own publications and posters have also benefitted—no more hard-to-read charts filled with clutter!” – John R. Heberger, Epidemiologist, Centers for Disease Control and Prevention

Case Studies from Past Participants​

You can view participants’ before-after transformations here:

• A Before-After One-Pager Makeover by Lillian Haley, Haley Evaluation & Research Services

Class 3: Great Graphs in Excel: How to Make Beginner, Intermediate, and Advanced Graphs from Start to Finish

You’re familiar with all the research-based dataviz best practices. Now, let’s get down to business and make those graphs in Excel!

During this class, you’ll make beginner, intermediate, and advanced graphs in Excel.

You’ll walk through four levels of Excel vizardry:

1. First, in Level 1, you’ll explore your dataset with sparklines, data bars, and heat tables.
2. In Level 2, you’ll learns the in’s and out’s of overused native charts (bar charts, pie charts, line charts, and more).
3. In Level 3, you’ll make underused native charts, like tree maps, b’arc charts, sunburst diagrams, and geographic maps.
4. Finally, in Level 4, you’ll make non-native charts–charts that require sophisticated workarounds to produce in Excel, like dot plots, small multiples bar charts, population pyramids, waffle charts, and more.

Learning Objectives

After the two-day workshop, participants will be able to:

• explore preliminary patterns by adding sparklines, data bars, and heat tables;
• create and edit classic chart types (bars, lines, pies, etc.) to make sure they’re Big A Accessible (508-compliant) and little a accessible (intuitive);
• practice creating newer chart types that are now available in Excel, like geographic maps and tree maps; and
• transform Regular Tables into Magic Tables to fully harness Excel’s power and make dot plots, population pyramids, and more.

Target Audience

Researchers, evaluators, scientists, graphic designers/communications staff, and admin staff who need to share data with others through accessible graphs.

Level

Intermediate.

Prerequisites

You should complete a one- or two-day data visualization best practices class first (like Great Graphs).

You should already have strong spreadsheet skills (being able to transform your raw data into clean, tabulated data (skills covered in Simple Spreadsheets).

Equipment Needed

A desktop/laptop computer (not a tablet or phone).

PCs preferred over Macs.

Software Programs Used

Excel is required.

Newest version preferred (called “Microsoft 365”).

Materials Included

• 2 days of live instruction (two days on-site, or four half-days virtually).
• For virtual trainings: Access to recordings for 1 year so you can re-watch anything you’d like.
• 28+ Excel files with step-by-step instructions and templates for making beginner, intermediate, and advanced graphs.
• 2-page Chart Chooser.
• 1-page cheat sheet on Transferring Graphs from Excel into Word or PowerPoint.
• Instructions for setting up Theme Colors & Theme Fonts.

What Participants Are Saying

“Before the course, I presented numbers narrated by text…yawn. Now, I present numbers as a story with visuals, sometimes as simple as sparklines, which engages staff and invites their insights! The Great Graphs in Excel course helped me see and share what story our data is telling. This course is best for people who learn through interaction, not just watching videos and reading tutorials. Ann K Emery responds to students’ questions and specific data scenarios, has us try the solutions real-time, and engages and encourages her students (probably the most impactful part of the course…) I especially appreciate her walking us through her thought process as she demonstrates the procedures she’s developed. It’s like having an officemate who’s both a whiz and mentor, which helps make me a better analyst.” – Ellen Shepherd, Program analyst at a nonprofit

“This course helped me to see Excel as a flexible tool for a wide range of data management and analysis tasks… Much more than just tables and calculations!” – Bob Coulter, Missouri Botanical Garden

“My trend is definitely upward in this course. I’m learning EVERYTHING about graphs and #dataviz. Even the first couple lessons taught me so much useful formatting information about Excel that are already saving me so much time and ensuring my #professionalbranding consistency. And I’m looking forward to making graphs and charts that can convey complex information in an effective way!” – Sue Griffey, Founder, SueMentors

“Enrolling in courses at Depict Data Studio is one of the best professional developments I have ever made… After eight months with Ann, I was able to turn my blah capabilities statement into something I am happy to share! I could go on and on about the many design and data visualization lessons I’ve learned from Ann. Instead, I’ll end by saying that books and blogs are excellent resources. I have several books and subscribed to many blogs. However, they do not compare to live and on-demand instruction of Depict Data Studio.” – Lillian Haley Ph.D., MSW, ChFC®, President and Owner, Haley Evaluation & Research Services (HERS)

Case Studies from Past Participants​

You can view participants’ before-after transformations here:

• 3 Simple Steps that Took My Graph from Good to Great by Maia Werner-Avidon, evaluator

Class 4: Report Redesign: How to Transform Text-Heavy Technical Reports into Visual, Skimmable Reports

Were you trained to write lengthy technical reports with methodological details? Technical reports are a great fit for technical audiences, like our peers working in public health. But what about our non-technical audiences, like policymakers or the general public?

In this class, you’ll learn how to translate technical data for non-technical audiences. You’ll walk through 3 layers of the Report Redesign pyramid.

Here’s what you’ll learn and practice:

• In “Level 1: Go Beyond the Report,” you’ll learn how to add a variety of dissemination formats to your project (not just reports) in order to meet a variety of audiences’ needs. You’ll also learn about the 30-3-1 Approach to Reporting, visual appendices, and the 8 ingredients for designing one-pagers.
• In “Level 2: Structure Your Report,” you’ll learn how to design a 20-minute cover. Then you’ll visually chunk your data with color-coded chapters. These are the big-picture, structural edits that make data-dense reports easier to skim and navigate.
• Finally, in “Level 3: Design Each Page,” you’ll fine-tune the graphs and paragraphs included on each page. You’ll see a checklist of 15 Ideas for Visuals, and then practice adding more visuals to each page. You’ll also edit your writing to ensure that it’s accessible and inclusive.

Learning Objectives

After the two-day workshop, participants will be able to:

• name a few dissemination formats (beyond technical reports) that would be ideal for non-technical audiences;
• describe the 30-3-1 Approach to Reporting;
• name 8 ingredients that should be included in one-pagers;
• create a 20-minute report cover in Word;
• create color-coded divider pages in Word; and
• transform one text-heavy page into a visual, skimmable page.

Target Audience

Researchers, evaluators, and scientists who are preparing technical reports (peer-reviewed articles, etc.) and need to translate those scientific details for non-technical audiences.

Or, graphic designers/communications staff and admin staff who are helping scientists prepare those non-technical reports or infographics.

Level

Intermediate/advanced.

This training is ideal for staff who regularly work on reports of any type or length, and who might already be thinking about adding one-pagers or infographics to their project.

In other words, you’ll gain the most from this session if you can bring your own draft reports to work on. It’ll be harder to participate if you don’t have any documents to work on during the hands-on portions.

Suggested Prerequisites

This course is about designing reports and one-pagers for non-technical audiences. It’s not a data visualization course. We suggest that you complete a half-day or full-day data visualization class first. Then, those well-designed graphs and maps can go into the reports that you’ll fine-tune during this course.

Equipment Needed

A desktop/laptop computer (not a tablet or phone).

PCs preferred over Macs.

Software Programs Used

You’ll learn both reporting best practices and Word how-to’s.

Materials Included

• 2 days of live instruction (two days on-site, or four half-days virtually).
• For virtual trainings: Access to recordings for 1 year so you can re-watch anything you’d like.
• Ebook
• PDF’d slides

What Participants Are Saying

“Investing in the Report Redesign course from Depict Data Studio was one of the best decisions I have made in my career. Before the course, I was writing long, wordy reports that I thought were visually appealing because I included tables and bar charts; I had no idea the options I had within software I already had access to. Now, I create impactful one-pagers and reports that are visually appealing and even more importantly, get read by my colleagues. I know that there are more than 15 types of visuals to include in reports, how to utilize my company’s brand to my advantage, and more. I have been complimented on the new look of my reports by coworkers and external stakeholders, and continue to have “aha” moments of how I can continue to apply Ann’s advice as access to the course never expires. I cannot recommend this course enough. Thanks Ann and Depict Data Studio!” – Olivia Power, Data and Reporting Specialist, National FFA Organization

Case Studies from Past Participants

You can view participants’ before-after transformations here:​

• Upping Your Reporting Game–with PowerPoint! by Ann Webb Price, Community Evaluation Services
• Three Ways Intentional Data Viz Has Elevated My Work by Kathy Dowell, The Evaluation Group

Class 5: Dashboard Design: How to Design Static and Interactive Dashboards in Excel

Why wait until the end of the year to write a lengthy report when you can share data early and often with dashboards? Your organization’s leaders have more important things to do than read lengthy reports. Dashboards get to the point so that leaders can understand the numbers and take action.

During this class, you’ll make both static and interactive dashboards in Excel.

First, you’ll see sample dashboards from a dozen organizations like yours. You’ll hear share the story behind each dashboard so that you can learn about each dashboard’s audience and goals. For example, some of the dashboards were designed to track progress towards goals. Other dashboards were designed to help organizations compare their different program areas. You can decide which elements of each dashboard would be most applicable to your own work.

Then, you’ll design a few static dashboards in Excel. You’ll create sparklines and uncover some of Excel’s best kept secrets, like Conditional Formatting. These dashboards will live inside of Excel and get shared with stakeholders as PDFs through email or as printed handouts during meetings. Static dashboards are a great fit for non-technical audiences who only have time to skim a one-page email attachment.

Finally, you’ll design an interactive dashboard in Excel. You’ll turn your regular table into an Excel Table; you’ll tabulate your dataset with pivot tables; you’ll design pivot charts to showcase your key findings; and you’ll link everything together with slicers. Interactive dashboards are a great fit for technical audiences who have time to explore the data themselves.

Learning Objectives

After the two-day workshop, participants will be able to:

• describe when static vs. interactive dashboards are most useful (e.g., for technical vs. non-technical audiences);
• create sparklines, data bars, and heat tables;
• adjust their dashboard to be printer- and PDF-ready to create static dashboards; and
• insert Excel Tables, pivot tables, pivot charts, and slicers to create interactive dashboards.

Target Audience

Researchers, evaluators, and scientists who want to create monthly, quarterly, or annual dashboards inside no-code software you already have, like Excel.

Level

Intermediate/advanced.

You don’t need to have any prior dashboard experience.

You *do* need to have prior data visualization and Excel experience (skills covered in Simple Spreadsheets and Great Graphs).

Suggested Prerequisites

You’ll gain the most from this course if you’ve already taken two other courses from this instructor: (1) Simple Spreadsheets (to start practicing formulas and pivot tables) and (2) Great Graphs (to start practicing data visualization skills, like chart-choosing, branding, and accessibility).

Equipment Needed

A desktop/laptop computer (not a tablet or phone).

PCs preferred over Macs.

Software Programs Used

Excel is required.

Newest version preferred (called “Microsoft 365”).

Materials Included

• 2 days of live instruction (two days on-site, or four half-days virtually).
• For virtual trainings: Access to recordings for 1 year so you can re-watch anything you’d like.
• Ebook
• PDF’d slides
• ~15 templates with step-by-step instructions for making static and interactive dashboards in Excel

What Participants Are Saying

“This course helped me to design a visually engaging and easy to interpret surveillance report for our State Health Department. This course offers so many great Excel tips and techniques in such an organized way. The skills I learned from this course were extremely easy to apply to an actual project. Furthermore, the course examples provided me with so many ideas and inspiration for future projects.” – Melissa Lurie, MPH, Epidemiologist/Research Scientist, New York State Department of Health

“When I started my position, I was tasked with developing a better way to track performance data across multiple programs. With this course, I was able to transform the old system into a dashboard that is efficient, makes good use of a single page, and looks great. We now get lots of compliments on our dashboard, thanks to this course.” – Shawna Rohrman, Associate Director, Cuyahoga County Office of Early Childhood

Case Studies from Past Participants

You can view participants’ before-after transformations here:

• Creating Reports for Grant Deliverables Using Excel Dashboards by Josephine Engels, Mental Health America of Greater Houston
• Better Storytelling with the Same Data: Upgrade that Board Packet! by Kristen Summers, Saint Luke’s Foundation

Class 6: Powerful Presentations: How to Design and Deliver Presentations for Maximum Impact

Do you need to give presentations, either in-person or online? You might need to design slides for public health conferences. Or, you might need to give updates at your staff meetings.

In this class, you’ll learn how to avoid Death by PowerPoint–those slides with Text Walls, grainy images, and run-on content. Our audiences are busier than ever, and they’re relying on us to communicate our data clearly and concisely.

You’ll gain presentation best practices and practical how-to’s in PowerPoint.

First, in the “Slidedecks vs. Slidedocs” module, you’ll learn the five graphic design features that make presentations stand out from reports. You’ll see examples of slidedecks and slidedocs from real CDC projects, and we’ll pause to make sure you know whether you need slidedecks and/or slidedocs for your own projects.

Second, in the “Message” module, you’ll design a Visual Framework to help organize your presentation into manageable chunks of information. You’ll also write the takeaway tweets for your presentation in advance to make sure it’s concise and actionable.

Third, in the “Design” module, you’ll swap out your bullet points and bar charts for a variety of visuals. You’ll receive our Chart Chooser and our checklist of 15 Ideas for Visuals, and we’ll practice adding visuals to some of your real slides.

Fourth, in the “Delivery” module, you’ll learn how to storyboard you slides, which is a technique for breaking up dense data over multiple slides and explaining it piecemeal to our non-technical audiences. You’ll also learn about public speaking skills, body language, and tech set-up for virtual presentations.

Learning Objectives

After the two-day workshop, participants will be able to:

• name five characteristics that should differentiate slidedocs (handouts made in PowerPoint) from slidedecks (presentation slides made in PowerPoint);
• narrow down a presentation’s content to just 3-5 “buckets” of information;
• draft a Visual Framework (a diagram) that shows how those 3-5 buckets are related (e.g., a venn diagram, step-by-step process, or repeating cycle);
• write a 1-2 sentence “takeaway tweet” that summarizes the main message from the presentation;
• re-design one text-heavy slide so that it includes accessible, skimmable visuals; and
• storyboard one graph (break up the graph over multiple slides to match your speaking points and keep the audience engaged).

Target Audience

Researchers, evaluators, and scientists who are preparing their own slides/handouts for upcoming presentations.

Or, graphic designers/communications staff and admin staff who are preparing slides/handouts that their supervisors will be presenting.

Level

Intermediate/advanced.

This class is designed for staff who are already giving presentations (informal staff meetings, or formal conference presentations) and want to take their slides and public speaking skills to the next level.

In other words, you’ll gain the most from this session if you can bring your own draft slides to work on. It’ll be harder to participate if you don’t have any slides or upcoming presentations to work on during the hands-on portions.

Suggested Prerequisites

This course is about presentations, slide design, and public speaking skills. It’s not a data visualization course.

We suggest that you complete a half-day or full-day data visualization class first (like Great Graphs). Then, those well-designed graphs and maps can go into the presentations you’ll design in this course.

Equipment Needed

A desktop/laptop computer (not a tablet or phone).

PCs preferred over Macs.

Software Programs Used

You’ll learn both presentation best practices and PowerPoint how-to’s.

Materials Included

• 2 days of live instruction (two days on-site, or four half-days virtually).
• For virtual trainings: Access to recordings for 1 year so you can re-watch anything you’d like.
• Ebook
• PDF’d slides

What Participants Are Saying

“Not only have my presentation skills and setup improved, but so have my slides. There are so many great tips and tricks I could highlight, but I will keep it to my three favorites: color coding, increasing readability, and storyboarding. The best part is, they don’t take that much extra time! These are simple changes that take your slidedecks to a new level and allow you to really impress your audience.” – Kelsey Waterson, Evaluator, Centerstone Research Institute

“A client asked me to report the results at their meeting and I used so many of your suggestions in the slidedeck, it was the most impressive PowerPoint I have ever made. But as the meeting progressed, they were running out of time, [but] luckily, I had also created a slidedoc and was able to share that document. I have never been so grateful that I had signed up for your class!” – Kristin Wright

Case Studies from Past Participants

You can view participants’ before-after transformations here:

• A Tip, a Trick, and a Thing to Try in Your Next Presentation by Elizabeth Dove, University of Montana
• Creating a Powerful Presentation: 3 Easy Changes to Revamp Your PowerPoint by Kelsey Watterson, Centerstone Research Institute

Learn More

If you’d like to explore private training options, you can learn more here.

Smaller teams may prefer group rates for online courses.