Google spreadsheet best practices to get and make the most out of our data. We need to practice these tips and tricks to increase data flexibility, accuracy and speed up data processing. This blog post is about how to make our data and sheets error-free.
Spreadsheets are often used as a multipurpose tool for data entry, storage, analysis, and visualization. We should design spreadsheets that are tidy, consistent, and resistant to mistakes. The more data we manage the more it’s difficult to process and keep it accurate.
We have to manage data in such a way that it could be easy to process for humans and machines (computer offline/online applications).
As we get experienced we get to know how we can organize our data to make it easy to process for functions, formulas, pivot tables, and get real value from the data.
It’s important to organize data in such a manner that it could be easy to make sense out of data. With well-organized data, we can create dashboards, and pivot tables. With Google Sheets, we can improve our project management and productivity.
So lets discuss some best practices and features to manage your data and sheets effectively.
1 Have Centralized Data – The truth of the source
In information systems design and theory, a single source of truth is the practice of structuring information models and associated data schema such that every data element is mastered in only one place. Wikipedia
The first rule is always to follow the truth of the source and keep your data centralized. It’s easy to create pivot tables, charts and do analysis with centralized data where all information is available.
With Importrange we can fetch data from one sheet to another. It syncs all data and keeps the centralized data intact and accessible to authorities only. If anyone wants to make changes they have to make changes on centralized data to sync it across other sheets/references.
If we have centralized data we have fewer chances of duplicate entries and have synergy between many data sources.
2 – Separate sheets for reporting, dashboards, and calculations
Calculations and charts in raw data make data messy. We often need to move data here and there to make sheets more appealing. Dashboards, Reports, and charts look appealing without unnecessary data. That’s why it’s important that visualization is done on separate sheets.
We should always create new worksheets/tabs to create reports, charts, pivot tables, and dashboards. This ensures data accuracy and avoids forthcoming problems such as hindrance while adding and removing data on the sheet.
Point 3 & 4 Best Practices are for novice users but some experts can make same mistakes
3 – Data Filtering
When applying filters, always select all columns or whole sheets to avoid mismatching the data. If we select a single column and sort it, then everything will be mismatched. That’s why it’s always advised to select all data via clicking on the top left corner or press ctrl + A to select all data while applying filters.
Note – Applicable for novice spreadsheet users.
4 – Copy Paste Values not format
Always paste copied data as values to avoid reference overlapping or intersection. Copy and paste causes misconfiguration within the conditional formatting range, which affects conditional formatting.
5 – Filter View
When we collaborate with our coworkers we can use Filter View, it only changes our data view. By using filter views we don’t interrupt each other and everyone works on the same worksheet.
When we don’t have edit access or there is some protected data we can create temporary filter views to filter out data.
We can also name and save filter views so we can access them later on.
Never reposition rows or delete rows when you are working in a filter view.
6 – Unique, Formatted and Freez Headers
Always use unique headers or column names to avoid confusion. Same headers can cause confusion while creating pivot tables. Distinguishing columns with unique names make dynamic referencing easy.
It also causes problems when we connect sheets with Google Data Studio.
- Format and Freeze Headers
Always format your headers to give them an edgy look and make them stand out. Also, don’t forget to freeze the first row (headers) to keep it floating while you scroll a large amount of data.
When you use dynamic column referencing because of duplicate headers formulas will return the first value instead of our desired column. It can also return #ref because of the array result.
Do not merge header or first row cells, if there are merged columns we can’t apply filters and sort the data.
7 – Collect data in a tabular format
Keep our data in tabular format so it’s easy to maintain and process for other features like pivot tables.
Avoid special characters such as * which works as a wildcard and can create a lot of trouble while working with data. But we can use underscores and hyphens or none or null for blank values.
Tabular data is easy to integrate with Google Data Studio.
Tip: Use Google Forms to collect data in tabular format. It increases data accuracy and efficiency.
8 – Conditional Formatting
Use conditional formatting to trigger a course of action and highlight cells, rows, and columns. We can use conditional formatting to detect duplicate values, errors, and data visualization.
Google Sheets align numbers to the right and text to the left, so while working with numbers we should align them to right. If numbers are aligned to the center it’s hard to determine whether it is number or text.
We should avoid highlighting cells to convey information and keep formatting to reports only. Also, avoid highlighting similar color highlights as we have in conditional formatting.
9 – Data Validation
We need to be careful about extra spaces and double letters which create problems with formulas like Filter, Vlookup and Index Match, etc.
To avoid such errors we can use data validation, it not only makes data uniform but also makes data filling easy and faster.
Data validation ensures numbers are being added in the number column. For example, in the age column, all entries should be numerical 1 and not alphabetical “one”. Data validation ensures everyone follows the same format.
10 – Google Forms
Use Google forms for data entry, form dropdown minimizes errors, and wrong date formats.
Forms are quite handy, you can bookmark all necessary forms and sheets to access them anytime anywhere.
Google Forms can be created and edited collaboratively which makes it more effective.
11 – Consistency and Uniformity
In my last blog, I’ve talked about the importance of consistency and uniformity. You can read the blog post here.
When it comes to data organization, consistency and uniformity are important. Whenever creating new documents use consistent names and layouts to ensure uniformity. Do not write “Mr” or “Mrs” for some and skip for others, do not use “M”, sometimes “male”, and sometimes “Male”
Also, the date, number, and currency format should be the same. For example, write dates as “YYYY-MM-DD”.
Always use the same abbreviations, format numbers as numbers, and text as text.
12 – Referencing (absolute, relative, and mixed referencing)
Use absolute, relative, and mixed referencing to avoid formulas malfunctioning. Whenever you need to drag formulas make sure you have given proper referencing.
Quick Overview
| Reference Type | Cell Reference | How references work |
| Relative Reference | A1 | Moves row and column as we drag the formula |
| Absolute Reference | $A$1 | Locks reference to a cell |
| Mixed Reference | $A1 | Moves row and makes column static as we drag the formula |
| A$1 | Makes row static and moves column as we drag the formula |
Following a similar layout and naming format makes dynamic referencing easy.
If you have used formulas and it’s not going to change then the best practice is to make it static and remove formulas.
13 – Name Range
Name ranges referencing is easy and can make complex formulas easy to execute. We can refer to named ranges without selecting ranges. With named ranges, we can make dynamic dashboards, pivot tables, and filters.
For example instead of B:B to we can use ColumnB name as a range.
14 – Resources
Use a resource worksheet to maintain named ranges and sheet keys. On the resource sheet, we can maintain data sources and which sheets are dependent on each other.
Maintain abbreviations and full forms to make new employee onboarding easy. In this way, we will follow consistency across the organization.
You can also keep a log of edits, which you can check later on. Google Sheets’ see edit history feature can also be helpful in this case.
15 – Add-Ons and App Scripts
Google Sheets add-ons like SuperMetrics, Zapier, and Yet Another Mail Merge make tasks easier and automated.
Add-ons like Zapier automate and sync across CRMs, project and customer management software. It is an effective way to manage things with G Suite.
We can also create our own add-on, function, or script to automate tasks. So whenever possible use addons and App Scripts.
16 – Templates
I’ve written a blog on the importance of Google Sheets templates which you can check out.
We can get started with Google Workplace templates like to-do list, annual budget, Gantt Chart, Project Tracking, etc.
With a simple Google search, we can find out many free and paid Google Spreadsheets and Excel Templates.
If we don’t find any Google Spreadsheets template, we can use many MS Excel templates as well. We just need to import the template in Google Sheets.
If we don’t find any template, we can create our own template. It will keep our data consistent and easy to follow for our colleagues and clients.
17 – Hide Sensitive Information
With conditional formatting, we can make specific data stand out but we can also hide sensitive information. If we keep the font and background color the same then no one can see the information. By this method, you can keep your sensitive information secure.
You can also protect and hide sheets so only you can access them when you need that information.
18 – Protect Range, Rows, and Columns
Sheets and ranges can be protected if they have advanced formulas and functions. If you want to disable edits for a specific range, you can use the protect range feature to avoid errors.
19 – Back Ups
We should back up our Google Sheets on a weekly or monthly basis so if anything goes wrong you always have backups that we can restore. We can also create a named version like system restore to restore our sheets whenever anything uncertain happens.
If we have CSV backups we can upload and retrieve our lost data, so we should always practice backing up our Google Sheets.
20 – Temporary Access with Expiration Date
We can set an end date for documents that we want to restrict after project completion. These documents will be shared temporarily with our clients and outside organizations.
It ensures people won’t be accessing and making unnecessary changes. It’s the best way to keep your data safe and secure.
21 – Google Sheets Notifications
Some sheets need timely attention, we can turn on notifications so we will be getting notified on edits and new information is filled within the document.
We can also get notified on new form entries.
22 – Analysis with Explore and Column Stats Feature
Get Data Insights from Google Sheets AI Feature ‘Explore’. To get quick statistics about a column we can use the column stats feature.
23 – Begin with the end in mind
We must have a vision about what we want to achieve from a spreadsheet. Once we know what we want, we can organize data to integrate with formulas, pivot tables, data studio, and other sheets.
24 – Add Drive Bot in Hangout
Add Drive Bot in our Hangout, once we add it we will be notified whenever someone share any sheet with us. If someone assigns a comment in any Google Doc we will be notified via Google Hangout, isn’t it a cool hack?
25 – CANI: Constant And Never Ending Improvement (Kaizen)
Learn Google Sheets formulas and functions to make formulas simpler and faster. Explore communities on Reddit, Excel Forums, Google Sheets Forums, and other platforms. There are plenty of things to learn and share on these platforms.
The best way to improve your Google Sheets skills is to take courses on Coursera, Skillshare, and Udemy.
You can also subscribe to my channel to get the latest updates on Google Sheets and many more things.
Conclusion
Spreadsheet programs such as Google Sheets, Microsoft Excel, LibreOffice Calc, and Numbers (Apple) are valuable tools to organize and manage data for dashboards, pivot tables, project management, customer relationship manager, and a lot of other things.
Individuals, Small and Medium Scale Businesses, and Big Companies use data for various purposes.
To get valuable insights from data and make decisions, it’s important to organize and manage data in the most accurate and error-free manner. By following the above best Google Sheet practices, data processing and managing would be easier for colleagues and customers.
Pingback: Google Sheets VS MS Excel | Everything You Need to Know - Yogesh Shinde