Google Sheet tips to help you get work done faster

One of the best tools to store data, track performance, automate calculations, and create custom reports is Google Sheets. With its plethora of features and functions, it might be a bit overwhelming to comprehend. Not to worry. We’ve got your back. 

Here are 10 uses of Google sheets, sliced and diced for every need of an entrepreneur or small business. 

What makes Google Sheets different from Excel? Short answer: price, collaboration, and flexibility. Slightly longer answer: 

• While Excel requires a software subscription Google Sheets are free for anyone with a Gmail account. 
• While Excel needs you to save and send a new version every single time you make a change, Google Sheets updates automatically and instantly. 
• Google Sheets can accommodate different integrations, while standalone software solutions like Excel lack that flexibility.

Conditional Formatting

Conditional formatting helps make sense of data rich spreadsheets. You can keep it as simple or complex as you want. Even minor color changes can be effective. Using a color scale to emphasize lower and higher numbers in your data is a simple and easy way to make your data more visible.

Conditional formatting is an effective tool to create heatmaps, with the key data points highlighted. It allows you to emphasize specific numbers, outliers, and errors.

Simple test case- Assume that your sales force sells a certain amount of product each month. With just three clicks, you can find out who the top 10% of salespeople are. 

Data Validation

Validate data in cells to ensure that specific cells only contain selected data. You can, for example, specify that certain cells only accept integers or a value from a specified list.

Use it to sort, count, total, or average data from a single large spreadsheet. You can display the data sliced and diced as per your requirements in a new table if you want. You can define the limits of your search to sales figures for specific countries, product lines, or marketing channels.

Once you have validated the data for a range of cells by setting a predefined list of values, a drop-down list will appear within the sheet.

Filters

Filtering effectively hides information that isn't relevant, which when working with huge quantities of data, can be a godsend. You can filter by descriptive text like ‘Blue Umbrella’ or number values like ‘greater than’, ‘above 10%’, etc.

Simply navigate to Data on theGoogle Sheets toolbar and choose Filter Views->Create new filter view.  Click on the Filter icon in the first column of your sheet to choose the value(s) you want to include. 

Easy Imports from Google Editors Suite

Whether it is curating Google Form responses onto a sheet, or inserting a chart created with Google sheet data into a Google doc, all it takes is a few simple clicks. 

To curate the responses received from Google Forms, simply click on Responses within the form and click on the Google Sheets icons to create a sheet with the data. The best part, both existing and future responses will be captured on the sheet for you to analyze. 

To insert a chart created on Google Sheets into a Google Doc, simply click Insert on the Google Doc toolbar, and choose Chart->From Sheets. Any changes made to the data on Google Sheets will be automatically updated on the chart in Doc, if you choose Update in the document. 

Similarly, Google Sheets can be easily integrated with the other applications in the Google Editors Suite.

Import data from other sheets

You can easily import data from one Google sheet into another which means you are free of the time-consuming hassle of maintaining data in different sheets. You can simply update the data on one sheet.

To know more about the IMPORTRANGE function of Google Sheets, read…

Import data from websites and RSS feeds

Whether it is HTML table and lists, RSS entries, or a web-based CSV file, you can import it all onto Google Sheets. You can even import a customizable section of a webpage by identifying it with Xpath. 

Here’s an example for the tea-aficionados, you can import all the data related to Darjeeling Second flush, its different estates, launches, regulations from with a simple import from Wikipedia.

Split

If you ever need to split data from a single cell into multiple cells, navigate to Data on the Sheet’s toolbar and select the Split text to columns option.

This comes in extremely handy when cleaning up data, separating first and last names into distinct columns, etc,.

Sparkline

For those of us who are staggered by the sheer amount of data that a sheet can capture and need to visualize the data, sparklines are a boon.

Sparklines are a simple and effective way to spot trends in your data. This is particularly beneficial if you're comparing data (such as Google Analytics statistics) or converting your spreadsheet into a dashboard.

To know more about the SPARKLINE function of Google Sheets, read…

QR Codes

Need to create QR codes for your vendors and/or franchise for an event? Add QR codes to a promotional sale? Want to curate the details of the different QR codes? Google Sheets has just the thing for you.

Click on the cell you want to create the QR Code in and navigate to the formula bar and type in 

=IMAGE("https://chart.googleapis.com/chart?chs=200x200&cht=qr&chl="&{TK}&"")  Here {TK} is the cell that you want to pull the data from.

Here's a Google Sheet with both the QR Code formula and a few examples, for you to start experimenting and creating QR Codes.

Add Emails

Collaborate with your team in real-time by adding a comment or assigning a task on Google Sheets. Simply add a comment and type their email address (or name) and they’ll receive an email with your message or query.

You can also add a note with the Insert note option for your teammates to refer to when in doubt. 

Bonus Tip: Email address validation

If you have a mailing list for a promotional exercise or email marketing exercise, Google sheets can be a convenient tool to not only curate the list, but verify the email addresses as well. 

In fairness, Google sheets isn’t as sophisticated as other email authentication solutions in the market. However, for a quick and dirty check to make sure that all email addresses within the sheet adhere to a valid structure (with @ and .com), Google sheets is a convenient option.

To know more about the ISEMAIL function of Google Sheets, read…

Here are a few Keyboard Shortcuts to get you started...

While there are loyalists on both ends of the Microsoft Excel and Google Sheets debate, freelancers, small business owners, and entrepreneurs have been ardent supporters of Google Sheets. Its flexibility and robust upgrades have made Google Sheets a favorite among growing businesses.