• Home
  • Tech
  • 3 Google Sheets Tips to be More Efficient

3 Google Sheets Tips to be More Efficient

As you may know by now, here at Consolidata we LOVE Google Sheets. It’s been a great resource to many business owners for recording data on their business and getting a general overview of the status of their work.

So, we thought, “Hey, we’re pretty good at this Google Sheets thing, why not help others get good at it?” So, while we will be posting a Daily Google Sheet Tip of the Day on our Facebook page (which you can find here:   ) we figured we would give you guys some tips to start. So here are 3 Google Sheets Tips to help you be more efficient.

Color Blocking and Checklists

Whether you have tons of data on the spreadsheet or not, one of the best ways to keep track of certain information is to color block it. This simply means you’ll be assigning a color to a specific piece of information that stands for either a stage or relates to importance. For example, you could be using a Google Sheet for billing purposes. You can keep track of what invoices have been paid, what invoices have an upcoming due date or which ones are past due by color blocking each one. Green for Paid, Yellow for Upcoming and Red for Past Due.

Now in order to do this you’ll want to make sure you keep track of the data on separate sheets. Otherwise, your 1 spreadsheet will end up looking like a disco ball. Once you’ve separated the data you’ll want to click on the little arrow under each sheet on the bottom and select “Change Color”, from there select the color you feel best represents that data and do so for the others.

You can also color block the different columns and rows on the spreadsheet itself. You’re able to do this by selecting one cell you want to color block or highlight several cells or an entire column/row to color block. Once you’ve selected what you want to color block, you’ll want to select the little icon that resembles a can of paint on the tool menu that says “Fill Color”. Select the color you want and Ta-Da! You’ve color blocked your spreadsheet. 

Trust us when we say, this feels like such a small step in the grand scheme of your business but it is so crucial in making sure all your data is organized which will give you the peace of mind of knowing how your business is doing and what needs to be addressed. 

Create Professional Charts

This next one is a cool feature on Google Sheets which allows you to turn any data you have into a chart so that you can make a  professional looking report and I’ll show you with illustrations.

First, see the screenshot below, let’s say you have a Google Sheet filled with Data for Invoices that you’ve paid.

The first step here will be to click on any cell within the sheet that contains data and then press CTRL + A. This will then select the entire table of data and highlight it blue.

Once it’s selected, you’ll want to go to the toolbar on top and select Insert > Chart. This will then populate a chart on your screen. 

It may not be the chart you necessarily want for this type of report or data so you can easily select which chart you want. For this particular case I want a Table Chart, so that I can see all the data side by side. So I’ll just go to Chart Type > Select Table Chart and I now have all of my data in a nice looking chart for my report. 

One cool feature also, if you have tons of data, is that you can sort the data in whichever order you’d like. So if you want to see your data from Highest amount paid to the lowest, simply hover over one of the headings, like Amount or Paid Date in this case, click on either and it’ll populate a small drop down arrow that you can simply click and it’ll sort from Low to High or vice versa.

Validating Emails

If you’re running ads and having people fill out survey forms to filter out who better fits the prospect you’re looking for, then it’s possible you’re trying to also get some information such as phone numbers or email addresses. Nothing is worse than having someone fill out the forms with promising information but invalid contact information. So, thankfully there’s a way to validate these email addresses through Google Sheets and just sort out any bad data. 

Let’s start with organizing the sheet. You’ll want to have the proper columns, in this case you’ll want these 4: First Name, Last Name, Email, Valid Email? – this way the engine can filter out the information properly. 

Once you have all the information filled into its corresponding rows under their columns, you’re then going to head over under the Valid Email? Column where, on the very first cell, you’ll input this function: =ISEMAIL – and then select it. Once selected, click on the email in the cell next to it and it should populate either a TRUE or FALSE, indicating whether it’s a valid email or not.

Once done, hover your mouse over the bottom right corner of the cell where it says either TRUE or FALSE, you’ll see an icon that looks like a cross-hair and just select that bottom right corner and drag it all the way down to the end of your email list, highlighting all of those cells. Once you let it go you’ll see that it shows either a TRUE or FALSE next to each email.

Now you’ll never have to deal with those Email error messages again, thank you Google Sheets for being great.

Leave A Comment

Your email address will not be published.