.png)
Google Sheets is a fantastic tool for creating custom dashboards. It’s also great for collaboration. Which are just some of the reasons we built LiveFlow Google Sheets Add-on.
However, spreadsheets are usually used to collect data and run calculations. Which means that they’re not the most user friendly things in the world. So, when you need to share the information you’ve stored and used there, the people you share with might not be able to make head or tail of the information.
There are several solutions to this problem. You could create reports from that data and send them by email. However, that would probably mean converting them to PDFs, which aren’t very interactive. Or you could hide some of the more confusing lines and columns of data – but there’s always a chance someone might unhide them accidentally. We’ve all had accidental clicks that resulted in chaos!
The third option, and the one we usually recommend, is to create a financial dashboard on Google Sheets. This allows you to share the sheet with people who need to see the information presented there, without requiring them to sift through rows and rows of information. You can also create interactive features that allow users to view different scenarios.
Let’s take a closer look at how you can create a custom financial dashboard in Google Sheets, to improve efficiency and bring value to your team and clients.
1. Decide What You Want to Present
The whole concept of a financial dashboard in Google Sheets is to present a lot of complex data in a simplified format, so that it can be viewed and understood by someone who is not an accountant or CFO.
Which means that you need to be selective about what you include in your dashboard. Too much information, and you will make something that is too confusing, and might not bring value to the reader.
Spend some time thinking about what you really want people to be able to take away from your dashboard. Focus on just a few key pieces of valuable information and keep it as easy and user friendly as possible. In this case, less is almost always more!
If you really need to present more data, it might be useful to put the information on different sheets, so that each one is easier to view and interpret.
2. Decide How to Present It
There are many ways to present data from Google Sheets. You could choose to use a pie chart, a graph, or a scorecard. Think about how your data will be easiest to view and understand and remember that often graphical representations like pie charts are easier to understand.
3. Create Your Charts
Once you know what you want to present, and how you want to present it, it’s time to start building a dashboard in Google Sheets.
Navigate to the spreadsheet that contains the data you want to include and highlight the range of rows and columns that you need to use.
Click “Insert" and then Chart”. Google Sheets will usually suggest a recommended chart type, based on the type of data you have selected. You can choose to use that format or change it, as necessary.
You can experiment with other chart formats if you like, by using the style and range editor and selecting Setup. This will allow you to view your data in various different chart configurations, so you can choose the one that is going to be the most user friendly for your dashboard.
4. Create Dashboard Sheets
Next, you will want to move your chart to its own sheet, so you can create your user dashboard.
Click the three dots in the top right corner of the chart. Select “Move to own sheet” from the dropdown menu options, and then choose to maximize from the customization options. This will convert your chart to full screen viewing and make it easy to see and read.
You can also add what is known as a “Sparkline” which is a mini chart that tracks changes over time – provided that data is included in the chart range.
Simply choose the cell you want to display it in and use the “SPARKLINE” formula to set the parameters for the affected range.
5. Add More Dashboards
If you need to present a variety of different information on your dashboard, it might be worth creating several different dashboard tabs in your Google Sheet. Simply follow the process outlined here and put each chart on its own named sheet. That way, your users can toggle between sheets to see the information they need.
6. Hiding Sheets
Finally, if you are going to be sharing your Google Sheet with various people, and you don’t want to confuse them with your raw data and calculation spreadsheets, you can hide the sheets you don’t want them to be able to access easily.
Simply open your Google Sheet and click on the sheet you want to hide. Click the down arrow while on the sheet and click hide. This will hide the applicable sheet, but it will be easy to unhide when you need to adjust information there.
Get More Out of Your Google Sheets
There’s no denying that Google Sheets is a great tool – and fantastic for sharing information like this with your team. However, you can bring even more value, by using a tool like the LiveFlow Google Sheets Add-on to easily import and use data from a variety of sources in your Google Sheets.
Make the process of creating reports and preparing data for your dashboards quicker and easier. It’ll improve your efficiency and give you easy tools to bring value as an accountant without having to do twice the work.
Whether you choose to use this tool or not, however, there’s no denying that creating a financial dashboard on Google Sheets is a great way to share information quickly and easily. It’s accessible everywhere, completely customizable, and will give people who aren’t accountants quick and easy access to key financial information. Check our step-by-step tutorial about creating a Consolidated P&L report in Google Sheets here.
LiveFlow has the entire template library to make your life easy. There you can find a a free Google Sheets Budgets vs Actuals template, free Consolidated P&L Template and a Marketing P&L template.
Try out LiveFlow to import live data into Google Sheets.
.png)
.png)
.png)
.png){kind=small}
