Google Sheets: Best practices to prepare your data for publishing in your dashboard
Depending of your business and the information systems that you use, your data can be in many places. You probably have data in management systems (ERPs), SaaS applications, and in spreadsheets. There’s also a good chance your data will pass through spreadsheets before it goes to the dashboard. In this posts, we’ll cover some best practices to prepare your data for use in your dashboard.
Before you can even analyze your data you need to ensure that your data has an analyzable data structure and that your data is always up to date. The ideal structure to analyze data is to organize it in a database format and configure it to update automatically from the data source. Below we’ll show you exactly how to do these two things:
With Google Sheets, as with any spreadsheet software, you can select the cell position where you start your table (e.g., you can start your database on the fifth row and third column). However, the ideal structure to make it easy to analyze the data in another sheet is to start in the first row and first column, with labels in row one and data vertically below.
Each column should be formatted to host the same data type (e.g., all data in column A is date format, in column B currency, and so on). We recommend setting the format via the menu rather than letting Google guessing it for you and applying automatic formats.
Automating the data transfer
Typically the data that you want to bring to your dashboard is not all housed in a single spreadsheet, it's not uncommon to find companies with more than 100 different spreadsheets with data to consolidate and analyze. And a usual procedure is copy and paste the data between the spreadsheets. But this practice can be dangerous and introduce errors in the data while wasting a lot of time.
There are two good options to connect your data with Google Sheets and eliminate this risk. The first is the function ImportRange and the second a Google Sheet add-on called Sheetgo. ImportRange is recommended as a quick solution to work with small volumes of data. It’s a great way to bring information from one spreadsheet to another for spot analysis. However if connecting spreadsheets becomes a routine in your work and you depend on these connections for analysis, reports, and decision making, you can use an application designed for data transfer, like Sheetgo.
You can find more information about the ImportRange function here:
Infinite and automatic formulas
Google Sheets also allows you to contemplate future data insertions with a single formula so that you don’t have to copy a single formula thousands of times and don’t have to worry about new data entering outside of your formula range and not being analyzed. By using =ARRAYFORMULA() you automatically replicate the desired formula for the entire array.
Tip 1: When this formula is applied to an infinite range, it will apply your calculation until the last row, independent of blank rows. This may cause some errors when it executes formulas based on blank cells but this can easily be solved with an IF statement to check if that row has data to calculate, and if not display a blank.
Tip 2: Be careful with the range symmetry, because if you put an incorrect data range where the you check the row above and output the results in a row below, the formula can cause the spreadsheet to attempt to add infinite rows, causing the spreadsheet crash.
If you want to analyze “How many sales were made last week” and you need that information updated weekly, how can you automatically update it? Below we’ve included two tips that will help to create dynamic sample of data based on you raw data:
For example, if the data you are analyzing has 1,000 rows, when you create the analysis for the first time you may insert into your formulas the range A1:A1000. But if you are receiving every day new rows you need to update the range. To avoid the maintenance, just remove the number of the end of the range, like A1:A. That way the spreadsheet will understand there is an infinite range, that is, no matter how long the data set is, the formula will always account for new rows.
Rolling periods of time
Let’s assume that you need to create an analysis of sales from the last 7 days. You can included dates, day by day, in the first column and will insert the formula to count the sales by day in the second column. But, as time passes, you will need to come back to the spreadsheet to adjust the range or adjust the dates. To cut out this manual work, just insert a dynamic date as base, and make the other dates relative to this base. For instance, in the cell A7 insert the formula =TODAY()-1 (the output will be the date of yesterday) and in the previous cell (A6) insert a reference to the A7 minus 1 (=A7-1), and so on until you are showing the last 7 days. That way, every day this data range will automatically change and your dashboard will always display the last 7 days.
There are many other tips and tricks that will help to better prepare your data, but the ones mentioned above will give a good base to start to make your dashboard update automatically without maintenance.
Originally published October 23, 2017, updated Jun, 13 2019