GSheets: Beyond Hacks Part 2 

Google Sheets: Quick Hacks & Best Practices

Whether you're new to Google Sheets or you're looking for new ways to make the most of it, there’s a lot more you can get out of the seemingly simple app than you think.  

As Part 2 of the previous #Hack-it GSpreadsheets, we will go through the hacks you implement right away, extract genuinely meaningful insights for your business, and manipulate data to make better decisions.

Let’s dive into the formulas and hacks that’ll make your life easier, help you find out exactly what you need and shave hours off your workload.

#1. Quick Counts with the COUNTIF Formula

Let's assume that you are looking through a list of contacts for people whose job title includes the word executive. If job titles are listed in column B, your formula might look something like =COUNTIF(B2:B500, "executive").

Simple, the setup is =COUNTIF(RANGE, "your criteria") 

Similarily,  If you were looking for items in your inventory that had sold fewer than 10 units, and unit numbers were listed in column E, you might write =COUNTIF(E2:E500, "<10").

Way better to see this in action along with other extremely useful Googlesheets Formulas to a FolderGooglesheets Formulas

#2. IMPORTRANGE: Data from one sheet to another

SME? Perhaps handling a small data related project. We all know the struggle of having too many different spreadsheets for different things. Hands down, we want to be able to move data from one spreadsheet to another with a quick formula. 

=IMPORTRANGE("spreadsheet_url", "range_string")

Range String: 

  1. Define the name of the sheet that is being imported from (eg. “Sheet 1”),
  2. Declare the range of data being imported.

#3. Translate Languages with a Formula

As you know, Google has plenty of different apps for doing various tasks. This includes Google Translate, which helps you to translate languages. It might not be perfect, but it can often provide a fairly accurate translation, especially if you're translating something back into English.To use this function:

=GOOGLETRANSLATE("je ne sais quoi", "fr", "en")

Moreover, you can simply name the cell range you want to be translated and skip the writing in the formula. 

However, unless you are sure that you require one of the other options, it is recommended that you leave your document as Private and just share with selected people.

#4. GExplore: Ask the right questions

Did you know? Google Sheets has a powerful feature that allows you to ask it questions and extract data based on your spreadsheet.

Simple. just use the 'Explore' button on the bottom right corner of your spreadsheet, you can quickly get answers to questions like “Which recruiter has the highest time to hire rate” in a matter of seconds.

The Sheet uses AI to quickly calculate a sum of each recruiters positions filled and feed through an answer. Here’s how this looks: 

Of course, similarily with Excell you can easily drag and drop the pivot table and chart it produces to any point in your spreadsheet.

#5. Use the Query Function 

One of the strongest assets of Google sheets is its Query functionality, it is so powerful. 

=QUERY(data, query, [headers])

It allows you to use database-type commands (a pseudo-SQL, Structured Query Language, the code used to communicate with databases) to manipulate your data in Google Sheets. This single-function does the job of many other functions and can replicate most of the functionality of pivot tables.

Our Goal Query Formula
SELECT specific columns only

=QUERY(countries,"SELECT B, D",1)

WHERE certain conditions are meet

=QUERY(countries,"SELECT B, C, D WHERE C = 'Europe' ",1)

ORDER BY 

=QUERY(countries,"SELECT B, C, D ORDER BY D ASC",1)

Arithmetic functions

=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100",1)

GROUP BY 

=QUERY(countries,"SELECT C, count(B) GROUP BY C",1)

Additional Resources: Google Queries

#6. Connect Sheets with GForms

 For the user that leverages Google Forms on a daily basis, this integration is a must. Simple, on the response section of the GForm just opt for the data being gathered into a spreadsheet. 

It makes it really easy to keep all the information neatly organized and in result, speeds up the process of putting together an expert roundup. Additionally, the data can then be leveraged through pivot tables and then carefully crafted into meaningful reports for the decision makers of your business whether it is a Candidate Screening or your e-commerce report. 

#7. Keep Tabs on your Competition

Running an Employer Branding campaign and you want to be in the loop for what your competitors are up to? 

Simple,  use IMPORTFEED or IMPORTXML to grab the RSS feed of their CMS. Expand to multiple competitors to cover your entire market of interest-based on location and other criteria of your interest. 

=IMPORTFEED("https://blog.competitor.com/rss.xml")