If you’re well-versed in Microsoft Excel, making the switch to Google Sheets shouldn’t cause you too many problems. However, there are some essential functions exclusive to Google Sheets that will make your life much easier once you get to know them.
1
QUERY: Manipulate Your Data for Quick Analysis
The QUERY function is as useful in Google Sheets as the Power Query tool is in Microsoft Excel. However, where Power Query in Excel operates through a separate window, queries in Google Sheets are powered through a series of clauses in a formula.
QUERY: Syntax
The QUERY function in Google Sheets has three arguments:
=QUERY(a,b,c)
where
- a is the range of cells containing the data to be manipulated,
- b is the query to perform, written using Google Visualization API Query Language, and
- c (optional) is the number of headers at the top of the data, which, if omitted or set to -1, forces Google Sheets to work this out automatically by analyzing the structure and content of your dataset.
QUERY: Examples
If you’ve never used structured query language (SQL) before, the QUERY function might seem daunting at first. However, once you try using the function in some straightforward scenarios, the sky’s the limit!
In this example, let’s say you want to produce a list of employees from the T_Staff table who have completed their training (in other words, those who have “Y” in column D.
To do this, in a separate cell or spreadsheet, type:
=QUERY(T_Staff,"SELECT B, C WHERE D = 'Y'")
where
- T_Staff is the table where the data you want to extract is stored,
- SELECT B, C tells Google Sheets that you want to extract certain data from columns B and C of the T_Staff table, and
- WHERE D = ‘Y’ tells Google Sheets that you only want to extract the rows where there’s a “Y” in column D.
Notice how the whole query is in double quotes, and individual text criteria are in single quotes.
In a second example, suppose you want to know which employees have completed training and earned over $30,000 in profit. The formula for this query is as follows:
=QUERY(T_Staff,"SELECT B, C WHERE D='Y' AND E>30000")
where D=’Y’ AND E>30000 combines the two logical arguments to only extract values from column B and C where there’s a “Y” in column D and the value in column E is more than £30,000.
Now, let’s explore some slightly more advanced scenarios. Let’s imagine that this time, you want to count the number of employees who have and haven’t completed their training, and group those totals into individual tallies.
In this case, the formula is:
=QUERY(T_Staff,"SELECT D, COUNT(D) GROUP BY D")
where SELECT D first extracts the information from column D, COUNT(D) counts the number of entries in the same column, and GROUP BY D collates the count according to the common values (in this case, “Y” and “N”).
Finally, you now want to analyze whether those who have attended training have turned a greater profit than those who haven’t. To do this, type:
=QUERY(T_Staff,"SELECT D, AVG(E) GROUP BY D")
where SELECT D extracts the data from column D, AVE(E) averages the values in column E according to the common values in the previously selected column, and GROUP BY D aggregates the averages according to their common values.
As a result, you can see that those who attended training made, on average, a profit that is around $5,000 greater than those who didn’t.
The aggregation functions you can use in a QUERY formula are avg(), count(), max(), min(), and sum(). The clause types are select, where, group by, pivot, order by, limit, offset, label, format, and options.
2
IMPORTRANGE: Import Data From One Google Sheets File Into Another
In Microsoft Excel, if you wanted to import data from one file to another, you would most likely use Power Query. However, in Google Sheets, you’ll need to use the IMPORTRANGE function.
IMPORTRANGE: Syntax
The IMPORTRANGE function has a straightforward syntax with two arguments:
=IMPORTRANGE(a,b)
Argument a directs Google Sheets to the file containing the range you want to import, either in the form of a URL (enclosed in double quotes) or a reference to a cell containing the URL.
Argument b is the reference to the range within the identified file. This can be cell references, a sheet name followed by cell references, a named range, a table name, or a column within a table—all of which must be in double quotes. It can also reference a cell containing the cell references of the source data, and this doesn’t need quotation marks.
IMPORTRANGE: Examples
Let me show you IMPORTRANGE in action.
In this first example, your aim is to import the list of names from File 1 to File 2.
To do this, select the cell in File 2 where you want the list to be imported to, and type the following formula:
=IMPORTRANGE("Note how the URL and cell references are placed inside double quotes.
The example formulas only show a part of the URL for demonstration purposes. In your case, when typing your formula, paste the whole URL.
The first time you make the connection between the two files, you may see a #REF! error. In this case, hover over the cell containing one of the following two errors.
If you own both spreadsheets, Google Sheets requires you to grant permission to link the files. To do this, click “Allow Access.”
Alternatively, if you’re not the owner of the source file and haven’t already been given edit access, you’ll be told that you don’t have permission to import the identified range using the IMPORTRANGE function. In this case, copy and paste the source file’s URL into the browser address bar, and press Enter. Then, click “Request Edit Access,” and wait for the owner to grant permission.
As soon as the correct permissions are activated, the range is imported to File 2.
One benefit of the IMPORTRANGE function is that if the contents of the referenced cells in File 1 change, this is reflected in the imported data in File 2. However, if the source data expands or changes location, you’ll need to adjust the formula accordingly.
By default, IMPORTRANGE imports from the source file’s first worksheet. To import a range from another worksheet in the source file, for argument b, type the sheet name, followed by an exclamation mark, and then the cell references.
In this example, the formula will import the data from cells A2 to A11 in Sheet 2:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d...","Sheet2!A2:A11")One issue with the above formulas is that they’re lengthy and difficult to parse. So, to make the formula tidier, for argument a, you can reference a cell containing the source URL. For example, typing:
=IMPORTRANGE(A1,"C2:C11")
imports the data in cells C2 to C11 in the Google Sheets file linked in cell A1.
You could do the same for argument b of the formula by referencing a cell that contains a reference to the range you want to import. Here, typing:
=IMPORTRANGE(A1,B1)
into cell B2 takes the URL from cell A1, and the cell references from cell B1:
If your source data is a named range or formatted as a Google Sheets table, for argument b, type the range name, table name, or table name and column header (all inside double quotes).
3
GOOGLETRANSLATE: Translate Text From One Language Into Another
As its name suggests, the GOOGLETRANSLATE function takes some text in your spreadsheet, and translates it into another language, much like the TRANSLATE function in Microsoft Excel.
GOOGLETRANSLATE: Syntax
Here’s how the GOOGLETRANSLATE function works in Google Sheets:
=GOOGLETRANSLATE(a,b,c)
where
- a is either the text to be translated (enclosed in double quotation marks) or a reference to a cell containing the text to be translated,
- b (optional) is the source language code, and
- c (optional) is the target language code.
If you omit argument b, Google Sheets will attempt to detect the language automatically. Omitting argument c translates the text into the language selected in your system settings.
GOOGLETRANSLATE: Examples
Let’s start with a straightforward example before we explore how you can use this function in a real-life scenario.
Let’s say you’re sent this Google Sheets file containing four cells containing sentences in four different languages, and you need to translate all the text into English, your system’s default language.
First, to translate the text in cell A1 into English in cell B1, you need to type:
=GOOGLETRANSLATE(A1)
and press Enter.
Because you didn’t specify a source language (argument b), Google uses its translation tool to work out that the text in cell A1 is Chinese. Also, since you didn’t identify a target language (argument c), the text is automatically translated into your system’s language.
Now, double-click the fill handle in the bottom-right corner of cell B1 to apply this formula to cells B2, B3, and B4.
Notice how, even though the source languages in cells A1 to A4 differ, the GOOGLETRANSLATE function still picks them up correctly.
Now, let’s see this function in action in a practical scenario. Imagine you’re creating a sheet where you need individuals to enter some personal details in column C. However, you want Google Sheets to translate the text when the respondent selects a different language in cell B1.
First, you need to import the language codes into a separate spreadsheet in your Google Sheets file. To do this, head to the list of codes on Google’s website, select all the languages and their corresponding codes, and press Ctrl+C to copy them. Then, head back to the new tab in Google Sheets, and in cell A1, press Ctrl+V.
Next, you need to translate the English names of these languages into the target language to make it easier for the person selecting a language to find their own. So, in cell C1, type:
=GOOGLETRANSLATE(A1,"en",B1)
and press Enter. At this point, Google Sheets will offer to autofill the formula to the remaining cells in the range, so click the checkmark to accept.
Now, head back to the spreadsheet where the text needs to be translated, and create a drop-down list of all the translated languages you just generated via the Data Validation option in the Data tab on the ribbon.
You’re now ready to use the GOOGLETRANSLATE function to translate the fields in column A into the chosen language in column B. In cell B4, type the following formula, which I’ve split up into separate lines for easier understanding:
=IFERROR(
GOOGLETRANSLATE(A4,"en",
XLOOKUP($B$1,Codes!$C$1:$C$55,Codes!$B$1:$B$55)),
"")
where
- Line 2 translates the text in A4 from English,
- Line 3 uses the XLOOKUP function to take the language cell B1, look it up in the list of languages in the Codes spreadsheets (cells C1 to C55), and return the corresponding language code (cells B1 to B55), and
- Lines 1 and 4 use the IFERROR function to return a blank cell if no language is selected in cell B1.
Then, use the fill handle in cell B4 to apply the formula to the remaining cells in the range, and select a language in cell B1 to see your work in action.
Next, in cell B3, type:
=B1
so that the selected language heads the translated column, and in cell C3, type:
=IF(ISBLANK(B1),"Answer",GOOGLETRANSLATE("Answer","en",XLOOKUP($B$1,Codes!$C$1:$C$55,Codes!$B$1:$B$55)))so that the word “Answer” in cell C3 is also translated if a language is selected in cell B1.
4
SPARKLINE: Create Miniature In-Cell Charts
Charts are a great way to visualize your data, but they can take up lots of real estate in your spreadsheet, especially if you insert more than one. Luckily, the SPARKLINE function in Google Sheets creates a miniature chart within a cell.
The main difference between sparklines in Microsoft Excel and Google Sheets is that in the former, they’re inserted and modified on the ribbon, while in the latter, everything is done within the formula. What’s more, there are more modification options for sparklines in Sheets than in Excel.
Sparklines in Google Sheets automatically adjust to fill the whole cell. So, to make sparklines more readable, increase the size of the cells that accommodate them.
SPARKLINE: Syntax
Although the SPARKLINE syntax initially appears quite simple, as you add modifications, it becomes more complex:
=SPARKLINE(a,{b})where a references the cells containing the data you want to visualize, and b are optional settings you can apply to your sparklines, enclosed within curly braces. If you want to create a minimal in-cell line chart, you can omit argument b altogether. However, to use other types of charts and modify how they appear, argument b becomes necessary.
SPARKLINE: Line Charts
As I mentioned above, if you want a basic line sparkline, you only have to input the cells containing the data into argument a in your formula:
=SPARKLINE(B2:E2)
However, you can modify the layout of these lines through argument b, which is a series of pairs of settings and their associated values. A setting and its value are separated by a comma, and each setting-value pairing is separated by a semicolon:
=SPARKLINE(a,{b¹,b²;b¹,b²;b¹,b²})This table shows you the different types of settings you can apply:
Setting | Definition | Values | Example Formula |
|---|---|---|---|
“xmin” | The minimum value along the x-axis | Any numerical value (no quotation marks) | =SPARKLINE(B2:E2,{"xmin",50}) |
“xmax” | The maximum value along the x-axis | Any numerical value (no quotation marks) | =SPARKLINE(B2:E2,{"xmax",200}) |
“ymin” | The minimum value along the y-axis | Any numerical value (no quotation marks) | =SPARKLINE(B2:E2,{"ymin",50}) |
“ymax” | The maximum value along the y-axis | Any numerical value (no quotation marks) | =SPARKLINE(B2:E2,{"ymax",200}) |
“color” | Sets the color of the line | Color names (like “green”) or color hex codes (like “#52D978”) | =SPARKLINE(B2:E2,{"color","green"}) |
“empty” | Defines whether empty cells are counted as zeros or ignored | “zero” visualizes blank cells as zero values in the sparkline; “ignore” discounts blank cells from the result | =SPARKLINE(B3:E3,{"empty","ignore"}) |
“nan” | Defines what happens to values that are not a number | “convert” turns non-numeric values into zeros; “ignore” discounts non-numeric values from the result | =SPARKLINE(B2:E2,{"nan","convert"}) |
“rtl” | Determines whether the sparkline is rendered to the right or left | true (no quotation marks) renders the sparkline to the left; false (no quotation marks) renders the sparkline to the right | =SPARKLINE(B2:E2,{"rtl",false}) |
“linewidth” | Determines the thickness of the line | Any numerical value (no quotation marks) | =SPARKLINE(B2:E2,{"linewidth",4}) |
So, in this example, the sparkline is red, ignores empty cells, and is slightly thicker than the default:
=SPARKLINE(B2:E2,{"color","red";"empty","ignore";"linewidth",2})
SPARKLINE: Column and Win-Loss Charts
To turn your sparkline into a column chart, use the following for argument b:
=SPARKLINE(B2:E2,{"charttype","column"})
Similarly, to insert a win-loss sparkline, type:
=SPARKLINE(B2:E2,{"charttype","winloss"})
Here are the other optional setting-value pairings you can include in argument b for column or win-loss sparklines:
Setting | Definition | Values | Example Formula |
|---|---|---|---|
“color” | Sets the color of the columns | Color names (like “green”) or color hex codes (like “#52D978”) | =SPARKLINE(B2:E2,{"charttype","column";"color","green"}) |
“lowcolor” | Sets the color of the lowest value | Color names (like “green”) or color hex codes (like “#52D978”) | =SPARKLINE(B2:E2,{"charttype","column";"lowcolor","green"}) |
“highcolor” | Sets the color of the highest value | Color names (like “green”) or color hex codes (like “#52D978”) | =SPARKLINE(B2:E2,{"charttype","column";"highcolor","green"}) |
“firstcolor” | Sets the color of the first column | Color names (like “green”) or color hex codes (like “#52D978”) | =SPARKLINE(B2:E2,{"charttype","column";"firstcolor","green"}) |
“lastcolor” | Sets the color of the last column | Color names (like “green”) or color hex codes (like “#52D978”) | =SPARKLINE(B2:E2,{"charttype","column";"lastcolor","green"}) |
“negcolor” | Sets the color of all negative columns | Color names (like “green”) or color hex codes (like “#52D978”) | =SPARKLINE(B2:E2,{"charttype","column";"negcolor","green"}) |
“empty” | Defines whether empty cells are counted as zeros or ignored | “zero” visualizes blank cells as zero values in the sparkline; “ignore” discounts blank cells from the result | =SPARKLINE(B2:E2,{"charttype","column";"empty","ignore"}) |
“nan” | Defines what happens to values that are not a number | “convert” turns non-numeric values into zeros; “ignore” discounts non-numeric values from the result | =SPARKLINE(B2:E2,{"charttype","column";"nan","convert"}) |
“axis” | Determines whether axes are shown | true (no quotation marks) shows the axes; false (no quotation marks) hides the axes | =SPARKLINE(B2:E2,{"charttype","column";"axis","false"}) |
“axiscolor” | Sets the color of the axes (if the “axis” setting is included) | Color names (like “green”) or color hex codes (like “#52D978”) | =SPARKLINE(B2:E2,{"charttype","column";"axiscolor","green"}) |
“ymin” | Sets the minimum value along the y-axis (column sparklines only) | Any numerical value (no quotation marks) | =SPARKLINE(B2:E2,{"charttype","column";"ymin",50}) |
“ymax” | Sets the maximum value along the y-axis (column sparklines only) | Any numerical value (no quotation marks) | =SPARKLINE(B2:E2,{"charttype","column";"ymax",100}) |
“rtl” | Determines whether the sparkline is rendered to the right or left | true (no quotation marks) renders the sparkline to the left; false (no quotation marks) renders the sparkline to the right | =SPARKLINE(B2:E2,{"charttype","column";"rtl","false"}) |
Here, this formula uses a column sparkline whose columns are colored light gray, except for the highest value which is colored green, and sets a y-axis minimum of 50:
=SPARKLINE(B2:E2,{"charttype","column";"color","#D3D3D3";"highcolor","green";"ymin",50})
SPARKLINE: Bar Charts
Bar chart sparklines don’t work the same way as a usual bar chart, as they don’t have variables on the y-axis and values on the x-axis. Instead, they group a single variable’s values into one bar, allowing you to compare their proportions. For this reason, bar chart sparklines work best when you don’t have too many values to plot.
For example, typing:
=SPARKLINE(B2:C2,{"charttype","bar"})into cell D2 and extending the formula to the other cells in the range means you can quickly see that team E accumulated the greatest proportion of its points in week 1, while team B did better in week 2.
Here are some other settings you can apply to bar chart sparklines:
Setting | Definition | Values | Example Formula |
|---|---|---|---|
“max” | Sets the maximum value along the horizontal axis | Any numerical value (no quotation marks) | =SPARKLINE(B2:C2,{"charttype","bar";"max",100}) |
“color1” | Sets the first color used for bars | Color names (like “green”) or color hex codes (like “#52D978”) | =SPARKLINE(B2:C2,{"charttype","bar";"color1","red"}) |
“color2” | Sets the second color used for bars | Color names (like “green”) or color hex codes (like “#52D978”) | =SPARKLINE(B2:C2,{"charttype","bar";"color2","blue"}) |
“empty” | Defines whether empty cells are counted as zeros or ignored | “zero” visualizes blank cells as zero values in the sparkline; “ignore” discounts blank cells from the result | =SPARKLINE(B2:C2,{"charttype","bar";"empty","zero"}) |
“nan” | Defines what happens to values that are not a number | “convert” turns non-numeric values into zeros; “ignore” discounts non-numeric values from the result | =SPARKLINE(B2:C2,{"charttype","bar";"nan","convert"}) |
“rtl” | Determines whether the sparkline is rendered to the right or left | true (no quotation marks) renders the sparkline to the left; false (no quotation marks) renders the sparkline to the right | =SPARKLINE(B2:C2,{"charttype","bar";"rtl","false"}) |
To allow for easier comparison across and within variables, I recommend always using the “max” setting.
Here, as well as comparing each team’s overall score, I can easily compare their weekly totals. I’ve also chosen green and yellow as the two bar colors:
=SPARKLINE(B2:C2,{"charttype","bar";"color1","green";"color2","yellow";"max",200})
Whether you should use Google Sheets or Microsoft Excel depends on several factors, including the complexity of the tasks you want to perform, whether you want to collaborate with others, and if you like working on a desktop app or your web browser. Many people prefer the advanced analysis tools of Excel, while others prefer the more intuitive interface of Google Sheets.