Making Google Sheets look less like… Google Sheets

This is a guest post from Josh Cottrell-Schloemer.

Google Sheets is an incredibly powerful spreadsheet tool for pulling, processing, and presenting data. But many people don’t realize that you can also use it to build interactive dashboards.

With a bit of creativity we can go from this:

To this:

The skills to build this type of dashboard aren’t difficult to learn and you can get started with a basic knowledge of Google Sheets.

Here’s a walkthrough of the dashboard shown above:

Build your foundation with Insert > Drawing tool

The underlying visual layout of these dashboards is built using the “Drawing” feature in Google Sheets. Getting familiar with this tool is your first step toward building more complex dashboards. It lets you draw a design using shapes, images, and text and then inserts that drawing on top of your spreadsheet.

Here are a few tips for creating your layouts:

If you don’t have any experience with design, I suggest starting with an existing dashboard or a nice PowerPoint slide and trying to replicate the layout. It’s a good way to learn all the features available in the drawing tool and will help you start to understand how to organize a page.

It’s important to use the alignment and distribution tools (under the “Actions” menu) to make sure your sections are equally spaced and organized. Disorganized layouts are distracting and make your work look unprofessional.

Try to keep most of your layout in a single drawing, as opposed to creating a separate drawing for each section. It’s much easier to update, align, and adjust when you have everything in the same drawing.

Learning to use this drawing tool is going to be the base for building the rest of your dashboard. Take the time to build up your confidence using all the features. With a bit of work, you can build almost anything that you could build in a slide deck.

Break free from the grid layout

Now that you’re familiar with the drawing tools, you can think of your dashboard as a series of layers. At the base we have the spreadsheet itself, on top of that are your drawings, and on top of that, you will place your metrics and charts.

Many people struggle to design dashboards because they try to fit everything into that base layer. They format each cell by changing its color, size, or outline. The problem is that this limits you to a rectangular design that is very rigid and hard to adjust.

So we leave that base layer for basic background colors and for situations where we need to use a tabular format. All of our other formatting happens in our drawings or in our individual metrics/charts.

Individual metrics are added using the scorecard chart (one of the default options in Sheets) and every other visualization uses the standard chart features. For more detailed guides on adding charts and scorecards, check out Ben’s other posts.

Here are a few tips for optimizing your visualizations to fit better in a dashboard format:

• Set the charts and scorecards to have a transparent background and no outline (select your chart, then click the menu Customize > Chart Style). This lets them sit on top of your “drawing” without blocking out any elements in the background.
• Remember to make sure you update your fonts and font colors to match the rest of your report. Keep your colors consistent.
• When placing your charts and metrics, make sure to make sure they are aligned, and consistently sized, and that you have padding between each element — in other words, organize and align everything. Messy dashboards are distracting and do not make you seem credible.
• Test each section to see what it looks like with the smallest possible value and the highest possible value. If you only have 3 months of data, will it break when you have 12 months of data? If you have a metric that is in the 10s or 100s, will it not fit when you get a huge sale and it jumps to 1,000,000s?

Choosing metrics & visualizations

If you’re designing a section of your dashboard and don’t know how to start then you can follow this simple recipe. I use this as the first step on many of my own projects. It’s based on the hierarchy of information a person needs to understand a piece of data.

The recipe is easy:

1. What is the metric name? Avoid confusing abbreviations or corporate jargon. Stick to clear titles.
2. How would you describe this metric to someone with no technical skills? You’ve got one short sentence to help your viewer understand what this metric is and to add any important context that they will need to understand it. If your users know all your technical terms, then you can skip this step.
3. Show the metric. Make it big, easy to read, and format it properly. If it’s a metric that changes over time it can be worthwhile to pair it with a secondary metric showing how much it has increased or decreased since the previous period.
4. If it’s a time series, then you should also show the metric over time. This doesn’t necessarily mean you need anything complex, a simple trendline will do. The first thing that people ask when seeing a metric is whether it’s higher or lower than usual. This quickly addresses that question.
5. If it’s a metric with sub-categories, then you can show the categories as well. In other words, if you’re showing impressions and those impressions come from Twitter, Facebook and Tik Tok. Then show the breakdown of the metric for each social network in a pie chart or bar chart.

There’s always the option to add more but this basic exercise is a great way to handle the majority of simple data that you’ll encounter on your own projects.

Make it interactive using pivot slicers or dropdown menus

To take your dashboard from a static report to a dynamic and interactive tool, you need to add filtering options. I think of filters as the lens that we use to examine our data. It lets us compare different regions, check different date ranges, change our metrics, and dig deeper when we see something unusual.

There are two options for adding filters:

1. Data Validation: This lets you build drop-down menus that users can interact with. Find them under the menu: Data > Data validation
2. Pivot Slicers: If you have structured your data using pivot tables then you may be better off using a “slicer”. This is just another way of saying “filter” for a pivot table. Find them under the menu: Data > Add a Slicer

Visual Design Dashboard Template

One of the most effective ways to learn is to pull apart somebody else’s template. You can make a copy of this one and look at each section to understand how it’s built:

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings.

In this case, right-click the link to open it in an Incognito window to view it.

Here are a few things to look at when you’re deconstructing this template:

• Get familiar with the source data – this powers the whole dashboard so it will help to understand how it’s all structured.
• Start with the base layer – what elements are actually part of the cell-grid layout and which are layered on top?
• Click into each drawing (there is more than one) – try editing them, think about why they are structured this way, see if you can replicate them in a separate drawing.
• Look at each of the chart settings – go through everything on the “Setup” and “Customize” sections to understand each formatting choice.
• Don’t be afraid to make changes – what would you improve? How would you change the layout?

Summary

It can feel overwhelming at first but there are really just 5 key lessons you need to get started building better dashboards in Google Sheets:

1. Test out the “Drawing” feature and get familiar with inserting and styling shape/images/text.
2. Break free from the grid layout and start using layers.
3. Use consistent colors and fonts.
4. Use clear descriptions for your metrics and avoid using jargon or acronyms.
5. Learn to use pivot slicers and dropdown menus. It’s the key to building dynamic dashboards.

---

Written by

Josh Cottrell-Schloemer is a performance dashboard specialist that has worked on projects with brands like Google, Microsoft, Lego, Gatorade and Philips. His free newsletter teaches people all over the world how to build better worksheets using the overlooked design features in Excel and Google Sheets.