Google Sheets: Best practices to prepare your data for publishing in your dashboard

Depending on 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 post, 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:

Database layout

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.

Data format

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 guess 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 to 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 is a Google Sheet add-on called Sheetgo. ImportRange is recommended as a quick solution to working 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:
https://support.google.com/docs/answer/3093340?hl=en

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 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 to crash.

Creating pre-analysis

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 a dynamic sample of data based on your raw data:

Infinite ranges

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 new rows every day, you need to update the range. To avoid maintenance, just remove the number at 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 include 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 a base, and make the other dates relative to this base. For instance, in 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.

Conclusion

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.