DIY: Google sheets dashboard

When documenting a project a lot of data builds up over time. In our company, project managers deal with tracking and taking note of everything that’s happening in the software development life cycle every day and combine that information in different reports, to oversee the entire project even better.

That can sometimes get tiring.

Over the years, they picked up a habit of making dashboards in google sheets every once in a while. Even more work, you say? Well, let’s find out are you right.

All in One reports

Data is useless if not interpreted correctly. To do that, you must find an effective way to present acquired information with keeping in mind the person you are presenting to. In business, charts had become a kind of universal language, and we rely on them to speak louder than our words.

Google Sheets are great for organizing and analyzing your data, and even better, you can build a dynamic Google Sheets dashboard to really understand the numbers.

google sheet dashboard

The dashboard can contain different types of charts or tables but the most important feature is that it’s updating automatically whenever you update your data. So, with little time invested in organizing one sheet into a dashboard, you’ve actually saved a lot in the long run. Not to mention that it’s all there in one place and you can never lose track of project results you’re following.

How to make a dashboard in google sheets?

You don’t have to be an expert to build a dashboard out of your data. Nonetheless, here are a few useful tips:

Use LOOKUP formulas

In its simplest form, the VLOOKUP function says:

=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE)

LOOKUP formulas

The HLOOKUP is the horizontal lookup implementation of the vlookup formula.

Add interactivity with data validation

Data validation adds interactivity to your dashboards in the following sense: you can create a drop-down menu from which the user can select a parameter, and the data in the table will change based on this choice, so any charts will update automatically. On the picture below, there’s an example of adding data validation to your sheet.

data validation

Present trends with sparklines

Sparklines are small, simple charts without axes, which exist inside a single cell. They’re a wonderful, quick way for visually showing a result, without needing the complexity of a full-blown chart.

To get a sparkline in your sheet use this formula: =sparkline(data,[options], where data refers to a range of values to plot the sparkline. The options argument is used to specify things like chart type (line, bar, column or winloss), color and other settings.

sparkline sheets

To dashboard, or not to dashboard?

Our PMs say: both!

Sheets are a powerful business tool, there’s no doubt about it. Some extra work is required at the start, but if you connect and arrange the data in the right way, everything after is pretty much automated.

As a software company, it’s in our nature to automate work as much as possible, so to track, for example, budget consumption and project overall progress we developed our own Jira add-ons: Horreum Project Budget Manager and Horreum Earned Value Management. You can download Horreum PBM here, and use it absolutely free of charge to see what we’re talking about.

But, that doesn’t mean that we don’t enjoy to look at a good Google sheets dashboard once in a while. We hope we helped you get the best out of both worlds.


You may also like

Leave a Reply

Your email address will not be published. Required fields are marked *