Talk to US

Do you store your data in Google Sheets? Have you created multiple spreadsheets for your organization’s data? But, when it comes to extracting insights from them, often it’s difficult because either the numbers don’t tally, or the information is stored in multiple sheets and there is be no way you can link the two sheets?

Sounds familiar? If so, this post will help you make the most of Google Sheets and maintain data in a way that so that you can leverage it for analytics.

Google Sheets are a great way of maintaining small data (GSheets has a limitation of X cells), without hassling with the worry of hosting databases and engaging a database administrator. However, Google Sheets’ usage can lead to a number of pitfalls. Often the information may become disorganized, data points may be missed, information may get edited by mistake, and the list goes on. This results in collected data not getting used. Follow the below best practices to save yourself from trouble.

  • Create CSV (Comma-separated values) style sheets. To use Google Sheets with any other BI tool or data management tool, it’s advised that headers are not created using merged rows and/or columns. Otherwise, it becomes hard for an end-user to extract data from the google sheet.
  • Format the header, and freeze it. Bold the header so that everyone knows notices them. Use either CamelCase or Camel_Case type formatting to name the columns. Each column name should be self-explanatory and not be overly long. Also, freeze the header so that when the user scrolls the data, the header remains intact.
  • Create masters for each unique item aka entity. For example, create masters for your customers, employees, suppliers, machines, etc. ie. anything that can be a list of unique items. This will help in ensuring when you are referring an entity in a table, you are not creating duplicates or doing spelling mistakes. Use these masters as a source for data validation when the user enters data.
  • Create an ID for each entity. Create an Alpha-numeric ID number that reveals what that ID is about – don’t just create “20191001” or a random text generator (xafsde) for any item. Use alpha-numeric that means something so that internal people can look at the ID and realize what it is. Ideally, have this generated automatically for a new row using AppScripts and lock this column.
  • Use data validation for columns that will have options – avoid manual entry as much as possible. Use dropdowns wherever possible. Try and give value from either a list stored on another sheet (if the dropdown can change), or manually inputted static list to the user so that no typing is required. This helps to avoid any duplications or spelling errors.
  • Use Google Forms for data entry. To ensure proper user interface for data entry, you can also use Google forms which can ensure that fields are not missed, and each field can also have data validation.
  • Set user permission to edit various sheets; lock Cells. Who gets to edit a sheet, or a particular cell also matters. Ensure that only relevant people can edit any cell, as per permissions defined. Lock certain cells/columns if required.
  • Link sheets to ensure no repeat entry of the same information. If you are using the list of your customers in multiple data entry tables. Do not let the user enter names in the two tables. Rather create a customer master, and then let the user select the customer from a dropdown.
  • Use data formats correctly. If you are asking the user to input dates, make sure the column is formatted accordingly. Otherwise, users might input invalid data formats.
  • Create tables such that each row represents either 1 transaction, or 1 event, or 1 entity. Make sure in each row, it should represent 1 new transaction, event or entity. Give an ID to this row, and ensure a date-time stamp is also taken while entering this row.
  • Use colors to distinguish user entry columns and autogenerated columns. Help data entry person by making locked cells, formula-generated information colored in a different way.
  • Use data formatting to give feedback to users. Use data formatting rules to tell users whether they have missed filling the required information or if they’re doing something wrong.

Do you use Google Sheets? Don’t know how to correct it for your business? We are happy to help. Register for your FREE consultation here.

Leave a Reply

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

Privacy Settings
We use cookies to enhance your experience while using our website. If you are using our Services via a browser you can restrict, block or remove cookies through your web browser settings. We also use content and scripts from third parties that may use tracking technologies. You can selectively provide your consent below to allow such third party embeds. For complete information about the cookies we use, data we collect and how we process them, please check our Privacy Policy
Consent to display content from Youtube
Consent to display content from Vimeo
Google Maps
Consent to display content from Google
Consent to display content from Spotify
Sound Cloud
Consent to display content from Sound