18 Google Sheets Formula Tips & Techniques You Should Know

How many of these Google Sheets Formula Tips & Techniques do you know?

Contents

  1. F4 Key
  2. F2 To Edit Cell
  3. Shift + Enter To Edit Cell
  4. Escape To Exit A Formula
  5. Move To The Front Or End Of Your Formula
  6. Function Helper Pane
  7. Colored Ranges
  8. F2 To Highlight Specific Ranges
  9. Function Name Drop-Down
  10. Tab To Auto-Complete
  11. Adjust The Formula Bar Width
  12. Quick Aggregation Toolbar
  13. Quick Fill Down
  14. Know How To Create An ArrayFormula
  15. Create Arrays With Curly Brackets
  16. Multi-line Formulas
  17. Comments In Formulas
  18. Use The Onion Approach

1. F4 Key

Undoubtedly one of the most useful Google Sheets formula shortcuts to learn.

Press the F4 key to toggle between relative and absolute references in ranges in your Google Sheets formulas.

F4 to switch between relative and absolute referencing

It’s WAY quicker than clicking and typing in the dollar ($) signs to change a reference into an absolute reference.

Back to top

2. F2 To Edit Cell

Have you ever found yourself needing to copy part of a Google Sheets formula to use elsewhere? This is a shortcut to bring up the formula in a cell.

Start by selecting a cell containing a formula.

Press the F2 key to enter into the formula:

F2 shortcut key to enter Google Sheets Formula

Back to top

3. Shift + Enter To Edit Cell

Shift + Enter is another shortcut to enter into the Google Sheets formula edit view.

Back to top

4. Escape To Exit A Formula

Have you ever found yourself trying to click out of your formula, but Sheets thinks you want to highlight a new cell and it messes up your formula?

Press the Escape key to exit the formula view and return to the result view.

Any changes are discarded when you press the Escape key (to save changes you just hit the usual Return key).

Back to top

5. Move To The Front Or End Of Your Google Sheets Formula

Here’s another quick trick that’s helpful for longer Google Sheets formulas:

When you’re inside the formula view, press the Up arrow to go to the front of your formula (in front of the equals sign).

Similarly, pressing the Down arrow takes you to the last character in your formula.

Up Down Arrow Keys in Google Sheets Formula

Back to top

6. Function Helper Pane

Learn to read the function helper pane!

Google Sheets Formula Helper Pane

You can press the “X” to remove the whole pane if it’s getting it the way. Or you can minimize/maximize with the arrow in the top right corner.

The best feature of the formula pane is the yellow highlighting it adds to show you which section of your function you are in. E.g. in the image above I’m looking at the “[headers]” argument.

There is information about what data the function is expecting and even a link to the full Google documentation for that function.

If you’ve hidden the function pane, or you can’t see it, look for the blue question mark next to the equals sign of your formula. Click that and it will restore the function helper pane.

Back to top

7. Colored Ranges

Helpfully Google Sheets highlights ranges in your formulas and in your actual Sheet with matching colors. It applies different colors to each unique range in your formula.

Google Sheets Function highlighting

Back to top

8. F2 To Highlight Specific Ranges

As mentioned in Step 2, you press the F2 key to enter the formula view of a cell with a formula in.

However, it has another useful property. If you position your cursor over a range of data in your formula and then press the F2 key, it will

F2 to highlight range in Google Sheets formula

Back to top

9. Function Name Drop-Down

A great way to discover new functions is to simply type a single letter after an equals sign, and then browse what comes up:

Google Sheets Function Drop-Down List

Scroll up and down the list with the Up and Down arrows, and then click on the function you want.

Back to top

10. Tab To Auto-Complete Function Name

When you’re using the function drop-down list in the tip above, press the tab key to auto-complete the function name (based on whatever function is highlighted).

Back to top

11. Adjust The Formula Bar Width

Google Sheets Formula Bar Width

An easy one this! Grab the base of the formula bar until you see the cursor change into a little double-ended arrow. Then click and drag down to make the formula bar as wide as you want.

Back to top

12. Quick Aggregation Toolbar

Highlight a range of data in your Sheet and check out the quick aggregation tool in the bottom toolbar of your Sheet (bottom right corner).

Quick Aggregation Toolbar

Quickly find out the aggregate measures COUNT, COUNT NUMBERS, SUM, AVERAGE, MIN and MAX, without needing to create functions.

Back to top

13. Quick Fill Down

Double click to copy formula

To copy the formula quickly down the column, double-click the blue mark in the corner of the highlighted cell, shown by the red arrow. This will copy the cell contents and format down as far as the contiguous range in preceding column (column A in this case).

An alternative way to quickly fill in a column is to highlight the range you want to fill, e.g.:

Quickly enter data in Google Sheets

Then press Ctrl + D (PC and Chromebook) or Cmd + D (Mac) to copy the contents and format down the whole range, like so:

Quickly enter data in Google Sheets

You can also do this with Ctrl + Enter (PC and Chromebook) or Cmd + Enter (Mac), which will fill down the column.

Back to top

14. Know How To Create An ArrayFormula

Array Formulas are powerful extensions to regular formulas, allowing you to work with ranges of data rather than individual pieces of data.

Per the official definition, array formulas enable the display of values returned into multiple rows and/or columns and the use of non-array functions with arrays.

In a nutshell: whereas a normal formula outputs a single value, array formulas output a range of cells!

We need to tell Google Sheets we want a formula to be an Array Formula. We do this in two ways:

  1. Hit Ctrl + Shift + Enter (PC/Chromebook) or Cmd + Shift + Enter (on a Mac) and Google Sheets will add the ArrayFormula wrapper
  2. Alternatively, type in the word ArrayFormula and add brackets to wrap your formula/li>

Find out how array formulas work in Google Sheets.

Back to top

15. Create Arrays With Curly Brackets

Have you ever used the curly brackets, or ARRAY LITERALS to use the correct nomenclature, in your formulas?

An array is a table of data. They can be used in the same way that a range of rows and columns can be used in your formulas. You construct them with curly brackets: { }

Commas separate the data into columns on the same row.

Semi-colons creates a new row in your array.

(Please note, if you’re based in Europe, the syntax is a little different. Find out more here.)

This formula, entered into cell A1, will create a 2 by 2 array that puts data in the range A1 to B2:

= { 1 , 2 ; 3 , 4 }

The array component (in this example { 1 , 2 ; 3 , 4 } ) can be used as an input to other formulas.

Back to top

16. Multi-line Formulas

Press Ctrl + Enter inside the formula editor bar to add new lines to your formulas, to make them more readable. Note, you’ll probably want to widen the formula bar first, per tip 11.

Multi-line formula in Google Sheets

Back to top

17. Comments In Formulas

Add comments to your formulas, using the N function.

N returns the argument provided as a number. If the argument is text, inside quotation marks, the N function returns 0.

So we can use it to add a comment like this:

=SUM(A1:A100) + N("Sums the first 100 rows of column A")

which is effectively the same as:

=SUM(A1:A100) + 0

which is just:

=SUM(A1:A100)

This tip is pretty esoteric, but it’s helpful for any really long formulas!

Back to top

18. Use The Onion Approach For Complex Formulas

Complex formulas are like onions on two counts: i) they have layers that you can peel back, and ii) they often make you cry 🀣

Use The Onion Method To Approach Complex Formulas

If you’re building complex formulas, then I advocate a one-action-per-step approach. What I mean by this is build your formula in a series of steps, and only make one change with each step. So if you start with function A(range) in a cell, then copy it to a new cell before you nest it with B(A(range)), etc.

This lets you progress in a step-by-step manner and see exactly where your formula breaks down.

Similarly, if you’re trying to understand complex formulas, peel the layers back until you reach the core (which is hopefully a position you understand). Then, build it back up in steps to get back to the full formula.

For more detail about this approach, including examples and worksheets for each case, have a read of this post:

Use The Onion Method To Approach Complex Formulas

Back to top

If Google Sheets And Microsoft Excel Went For A Drink…

This is a transcript of a conversation between two famous spreadsheet applications, Google Sheets and Microsoft Excel, who, in mid-2019, sat down together at a well-known beach bar, The Pivot & Chart Tavern, for a catch-up after a long WORKDAY.

For the DURATION of their meeting, SMALL and LARGE FISHERmen came and went, smelling of POISSON from the Sea.

It was a DAY to remember.

Google Sheets: “Excel! Dude! GAUSS who, yo? It’s been DAYS, MONTHs even, since we caught up. You made it. You crash so often I wasn’t sure you’d get here.”

XL: “Ah Google Sheets, you again. Rude, impetuous, cheeky. I see you’re still as mature as toddler in a COT. Still on a formula-only diet are you? Do you know FACT from fiction yet? Is this establishment to your satisfaction? I do hope it’s NOT out of your PRICE range.”

Sheets: “Woah, so aggressive. Nope, I’m all grown up now. IMREAL deal! I’m TRIM, check out my ABS. Still TRENDy and UNIQUE of course. I have so much going on right NOW, so many cool and COMPLEX features, and an ever growing, engaged, passionate community.

What about you, Excel, still hanging on? Haha.”

XL: “Hanging on? Totally FALSE! You should respect your elders.

NOW listen to me young man, I was doing advanced financial modeling whilst you were still popping zits on your funny little (inter)face. I may be over 30 YEARs old but I’m in the best health I’ve ever been. I continue to enjoy consistent product GROWTH.

Contrary to some of the marketing materials new-fangled upstarts put out, I’m very much alive and kicking, and still dominating the office data analytics scene, thank you very much. It seems you’re in rude health too Sheets, your voice is LOWER NOW, full of CONFIDENCE. Let me buy you a drink.”

Sheets: Sure, a beer please.

XL: So unsophisticated.

Turning to the barman…

XL: A beer, and I’ll have your finest aged red wine please. Put it all on the same TAB, thank you.

Barman: That’ll be 0.00091 Bitcoin please.

XL: Oh come on! Can you convert that TO_DOLLARS please?

Barman: As you CHOOSE, let me SWITCH the payment….that’ll be 10 Dollars EVEN at TODAY‘s price…

Excel hands over a 20 Dollar bill.

Barman: Is that DMIN bill you’ve got?

XL: Yes, I’m sorry for the trouble.

Barman: Ok, DMAX change I have is in 1 Dollar bills…

XL: That is no problem.

After a short SEARCH for an AREA to sit, and a brief interruption when they were INTERCEPTed by an errant ROMAN soldier, they took their seats at one of the PIVOT TABLES near the bar, to continue their rather KURT conversation…

Sheets: Do you think ISODD Excel? I MEAN, here we are in rude health, still the pre-eminent way the majority of knowledge workers manage and analyze their data.

XL: Yes, it’s TRUE! We have some sticking POWER that’s for sure. I take it as a good SIGN that our respective platforms continue to evolve and maintain their critical usefulness.

Sheets: Ok, let’s get down to business then. I want to share my theory of why we’re still the pre-eminent solution for many people…

XL: Ok, Sheets, the FLOOR is yours:

Sheets: First off, we’re ubiquitous. We’re everywhere. You’re in every office and I’m in every browser. So there’s that.

Second, we can solve most problems. Yes, there is ultra-specific software that will do certain tasks better, but nobody beats us for overall utility.

Third, we’re easy to use. Beginners can just dive right in, but we’re complex enough that even the most advanced users will never run out of things to discover.

XL: RIGHT, All TRUE, all good points. We’re definitely on the same FREQUENCY here.

Sheets: AND, we’re super flexible, so we can easily adapt to new tasks or new use cases.

XL: Yes, yes, indeed. Plus, almost all SaaS platforms have a button that exports data to Excel or Sheets. I suspect a lot of people use this, but of course that’s not a good metric for a SaaS company to divulge.

XL AND Sheets both have a little chuckle at this…

The conversation rambled on for several more HOURs. The EFFECT of the drinks made the conversation take an ODD turn:

XL:Have you ever BIN2OCT-oberfest, Sheets? You know the one I mean, the beer festival in Bavaria in the fall?

Sheets: Yeah, yeah I know the one, but no, I haven’t. Have you ever BIN2HEXham, XL?

XL: You mean the market town in the UK, right? Only once. And the airline lost my TRUNC on that trip! What a palava that was!

Sheets: TRUNC! Bwah! Now you’re showing your age. Haha. And definitely no chance of a TAN at that TIME of YEAR.

At a lull in the conversation, they both look down at their phones.

Sheets: Check this out, old man.

Sheets holds up his phone, with an app open called INDEX MATCH.

Sheets: It’s a dating service for spreadsheets. You right click on Sheets you like, left click on ones you don’t. It uses their IMPORTRANGE algorithm to MATCH you with other Sheets. Super cool.

XL: Bah, sounds like it’s just for HLOOKUPs to me. The more discerning spreadsheets look for love through a service called EDATE, all based around your star SIGN.

Sheets: Sounds like hokum to me…

You hungry Excel? Shall we get a PI?

Excel: You mean like a pizza PI? Could do, as long as we ADD spinach and ricotta, MINUS the mushrooms. Though I’d rather have CHAR-grilled steak.

Later, replete after dinner, it was time for the two friends to bid farewell…

XL: Right then Sheets, before you SLOPE off, let me tell you, it was good to catch up. TEXT me whenever you want to have a drink together again.

Sheets: ISEMAIL ok?

XL: As you wish. I’ll ask Numbers, LibreOffice, Airtable and maybe a few others to JOIN us next time, ok? They’re PROBably feeling LEFT out.

Sheets: Yep, I’ll be there. Catch up soon!

It certainly was a DAY to remember.

Resources

The full list of 400+ functions in Google Sheets

Your biggest competitor is a spreadsheet

My rather silly story was inspired by a similar, although much funnier, function-themed story from Mr Spreadsheet himself, John Walkenbach. Sadly I can’t find it online anymore, but if anyone can share the link, I’ll add it here.

Use The Onion Method To Approach Complex Formulas

Complex Formulas? The Onion Method? Huh?

I’m talking about the idea that complex formulas in Google Sheets are a lot like onions.

They have layers.

And they sometimes make you cry. 🀣

The Onion Method For Complex Formulas

If you’re building complex formulas, then I advocate following a one-action-per-step approach.

What I mean by this is that you build your formulas in a series of steps, and only make one change with each step.

The Onion Method is a framework by which to approach hard formulas, and consists of these three elements:

  1. Put each new step of the formula in a new cell
  2. Label each step with a simple “Step 1”, “Step 2”, etc. in adjacent cells
  3. Change the background color of each formula cell, so they can be easily found

This lets you see the formula progress in an incremental way and is really helpful when you’re building or tyring to understand complex formulas.

Sometimes a step might result in an error (typically a #N/A or #REF!), but that’s ok, provided it gets fixed in a subsequent step, as shown in this SUMPRODUCT example:

Advanced Formula steps example

Each of these intermediary formulas in the above image moves us forward incrementally, until the final answer is obtained in step 6.

Similarly, if you’re trying to understand complex formulas, peel the layers back until you reach the core (which is hopefully a function you understand!). Then, build it back up in steps to get back to the full formula.

Example 1: Building A Complex Formula With The Onion Method

Let’s look at importing data from the table on this Wikipedia page of largest cities by population.

Wikipedia country data table

Step 0

Open a new Google Sheet (bonus points for using the browser shortcut and just typing Sheets.new into your browser window).

Step 1

Step 1 is a standard IMPORTHTML function to retrieve the city table from Wikipedia. In cell A2:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2)

The data has some issues, but it’s a start.

Import Wikipedia table into Google Sheets with IMPORTHTML

Step 2

In Step 2, we use the INDEX function to grab just the population column.

Per The Onion Method, we insert this next step in a new cell, cell J2, to the right of the existing data, in this example:

=INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),,4)

Google Sheets Complex Formula Step 2

I’ve highlighted the new action step in red — adding the INDEX function wrapper.

Looking closely at that INDEX function wrapper, you’ll see that we’ve left the row argument blank, namely:

INDEX(data, ,4)

which returns the entire column.

We’ll deal with the population column on its own and come back to our main formula later.

Step 3

Hmm, that population column is messed up! Regex to the rescue!

Using the REGEXEXTRACT function, extract just the numbers and “,” from the data, before the citations in brackets:

=REGEXEXTRACT(INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),,4),"[0-9,]+")

Hmm, that gives us a #N/A error… πŸ€”

Step 4

Turn this into an Array Formula and get the column of population numbers!

=ArrayFormula(REGEXEXTRACT(INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),,4),"[0-9,]+"))

Google Sheets Complex Formula Step 4

We still have two problems to solve though: i) we need to convert the strings into actual numbers and ii) we need to fix the #N/A column heading…

Step 5

We can multiply by 1 (see the “*1” at the end of our formula), which coerces those text strings into actual numbers.

=ArrayFormula(REGEXEXTRACT(INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),,4),"[0-9,]+") * 1)

Google Sheets Complex Formula Step 5

Step 6

Use the IFERROR function to fix that pesky #N/A error at the top of our column heading, and replace the #N/A with the word “Population”:

=ArrayFormula(IFERROR(REGEXEXTRACT(INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),,4),"[0-9,]+")*1,"Population"))

Nice, now we have our population column as numbers:

Complex Formula using IFERROR in Google Sheets

Step 7

Pick the other columns we want, by wrapping the IMPORTHTML function with a QUERY function.

Note that we have to use the Col1 notation rather than the column letter in our Select statement, since we’re nesting another function as our data source in the QUERY function.

We also use the QUERY function rather than the INDEX function because we want to return multiple columns this time, which the INDEX function can’t do.

=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),"SELECT Col2, Col8",1)

Google Sheets Complex Formula Step 7

Step 8

All that’s left is to join these two ranges using the curly bracket notation, like this:

={

QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),"SELECT Col2,Col8",1)
,
ArrayFormula(IFERROR(REGEXEXTRACT(INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),,4),"[0-9,]+") * 1,"Population"))

}

(Shown with line breaks to illustrate the two ranges.)

Ok, we’re done πŸ€ͺ

The output is:

Google Sheets IMPORTHTML output

Example 2: Deconstructing With The Onion Method

If you’re trying to understand complex formulas in Google Sheets that someone else has shared with you, you can still approach it with this Onion Method.

Simply peel back the layers until you reach the innermost function. Copy that into a new cell and start from the inside and work out, building up to the full formula again.

Let’s see an example.

Suppose we’re given this worksheet with US State names:

dataset of US State Names

And we’re also given this formula:

=ArrayFormula(INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0))))

which gives an output of Texas.

But how does this formula work?

Applying The Onion Method, we peel back the layers to the core function, and then build it up in steps again.

Step 1

In a new cell, add the innermost MATCH function:

=MATCH(A1:A20,A1:A20,0)

Step 2

=ArrayFormula(MATCH(A1:A20,A1:A20,0))

which outputs an array of the position of the first occurrence of the words in column A. We see a 2 next to every occurrence of Texas for example, because the first time it occurred was in position 2.

Step 3

Now, we wrap it with the MODE function to find the most frequently occurring position:

=ArrayFormula(MODE(MATCH(A1:A20,A1:A20,0)))

By definition, the MODE function takes a range of numbers for an input and finds the most commonly occurring value.

However, what happens if we have a range of text values and want to find the most frequent?

In this case, the MATCH has been used to create a range of numbers for the MODE function.

By now, we’ve probably deduced that this formula finds the most frequent word in a list.

Step 4

Finally, we can retrieve the actual text value, i.e. the most frequent State name, by adding the INDEX function to get the full original formula, like this:

=ArrayFormula(INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0))))

This will give the output Texas in this specific example.

Nice!

Template For Your Use

Click here to open a read-only copy of the template >>

This template contains both examples from this tutorial.

To make your own editable copy, please go to File > Make a copy… under the File menu.

Conclusion

The Onion Method is a framework that allows you to approach complex formulas in a systematic way.

Even if you’re presented with an “impossible” challenge to answer or an “impossible” formula to decipher, just follow this framework. If required, peel back the layers and then work from the inside out in an incremental fashion.

You’ll be amazed at how quickly your understanding of challenging formulas broadens and deepens. You’ll encounter and understand brand new functions that you’ve never heard of before. Plus, you’ll find out all sorts of secret tricks with existing formulas.

Who knows, you might even cry tears of joy instead of despair…

I’ll leave you with this quote from businesswoman Belinda Johnson:

I like cutting through complexity and trying to get to the kernel of an idea.

Formula Challenge #2: Matching Terms

(This Formula Challenge originally appeared as part of Google Sheets Tip #52, my weekly newsletter, on 27 May 2019.

Sign up here so you don’t miss out on future Formula Challenges!

Find all the Formula Challenges archived here.)

Your Challenge

Start with this small data table in your Google Sheet:

Formula Challenge dataset

Your challenge is to create a single-cell formula that takes a string of search Terms and returns all the Results that have at least one matching term in the Terms column.

For example, this search (in cell E2 say)

Raspberries, Orange, Apple

would return the results (in cell F2 say):

One
Two
Five
Six
Seven
Nine

like this (where the yellow is your formula):

Formula Challenge expected results

Check out the ready-made Formula Challenge template.

The Solution

Solution One: Using the FILTER function

=FILTER(A2:A11,REGEXMATCH(B2:B11,JOIN("|",SPLIT(E2,", "))))

or even:

=FILTER(A2:A11,REGEXMATCH(B2:B11,SUBSTITUTE(E2,", ","|")))

These elegant solutions were also the shortest solutions submitted.

There were a lot of similar entries that had an ArrayFormula function inside the Filter, but this is not required since the Filter function will output an array automatically.

How does this formula work?

Let’s begin in the middle and rebuild the formula in steps:

=SPLIT(E2,", ")

splits out the three fruits in cell E2 into separate cells:

Raspberries    Orange    Apple

Next, join them back together with the pipe “|” delimiter with

=JOIN("|",SPLIT(E2,", "))

so the output is now:

Raspberries|Orange|Apple

Then bring the power of regular expression to the table, to match the data in column B. The pipe character means “OR” in regular expressions, so this formula will match Raspberries OR Orange OR Apple in column B:

=REGEXMATCH(B2:B11,JOIN("|",SPLIT(E2,", ")))

On its own, this formula will return a #VALUE! error message. (Wrap this with the ArrayFormula function if you want to see what the array of TRUE and FALSE values looks like.)

However, when we put this inside of a FILTER function, the correct array value is passed in:

=FILTER(A2:A11,REGEXMATCH(B2:B11,JOIN("|",SPLIT(E2,", "))))

and returns the desired output. Kaboom! πŸ’₯

Solution Two: Using the QUERY function

=QUERY(A2:B11,"select A where B contains '"&JOIN("' or B contains '",SPLIT(E2,", "))&"'")

As with solution one, there is no requirement to use an ArrayFormula anywhere. Impressive!

This formula takes a different approach to solution one and uses the QUERY function to filter the rows of data.

The heart of the formula is similar though, splitting out the input terms into an array, then recombining them to use as filter conditions.

=JOIN("' or B contains '",SPLIT(E2,", ",0))

which outputs a clause ready to insert into your query function, viz:

Raspberries' or B contains 'Orange' or B contains 'Apple

The QUERY function uses a pseudo-SQL language to parse your data. It returns rows from column A, whenever column B contains Raspberries OR Orange OR Apple.

Wonderful!

Click here to open a read-only version of the solution template (File > Copy to make your own editable copy).

I hope you enjoyed this challenge and learnt something from it. I really enjoyed reading all the submissions and definitely learnt some new tricks myself.

SPLIT function caveats

There are two dangers with the Split function which are important to keep in mind when using it (thanks to Christopher D. for pointing these out to me).

Caveat 1

The SPLIT function uses all of the characters you provide in the input.

So

=SPLIT("First sentence, Second sentence", ", ")

will split into FOUR parts, not two, because the comma and the space are used as delimiters. The output will therefore be:

First    sentence    Second    sentence

across four cells.

Caveat 2

Datatypes may change when they are split, viz:

=SPLIT("Lisa, 01",",")

gives an output of

Lisa    1

where the string has been converted into a number, namely 1.

See the other Formula Challenges here.

Formula Challenge #1: Repeating Images with Array Formulas

Introduction

I firmly believe that one of the most effective and rewarding ways to learn a skill is through practical application.

Solving problems you don’t know the answer to is arguably the best way to do this.

And that’s the idea behind these Formula Challenges.

I’ll post a challenge in my Monday newsletter — a question to be solved with formulas in Google Sheets — and a week later share solutions, both my own and those submitted by readers.

I’ll archive the challenges and solutions on my website here.

(This first Formula Challenge originally appeared in my Google Sheets Tips newsletter, on 25 February 2019.

Sign up here so you don’t miss out on future Formula Challenges!)

The Challenge

Start with a straightforward IMAGE function in cell A1, like this:

=IMAGE("https://www.google.com/favicon.ico")

Google Sheets Image Formula

Your Challenge

Your challenge is to modify the formula in cell A1 only, to repeat the image across multiple columns (say 5 as in this example), so it looks like this:

multiple images in Google Sheets

Rules

You’re only allowed to use a single formula in cell A1.

The problem is that the IMAGE function can’t be nested inside a REPT function, so you have to get a bit more creative.

The Solution

Solution One: using ROW or COLUMN counts

=ArrayFormula(IF(COLUMN(A:E),IMAGE("https://www.google.com/favicon.ico")))

How does this formula work?

The combination of ArrayFormula with COLUMN(A:E) will output an array of numbers 1 to 5: {1,2,3,4,5}

The IF statement treats the numbers as TRUE values, so prints out the image 5 times. For brevity, we can omit the FALSE value of the IF statement, since we don’t call it.

Solution Two: using REPT inside the IMAGE formula!

=ArrayFormula(IMAGE(SPLIT(REPT("https://www.google.com/favicon.ico"&"|",5),"|")))

How does this formula work?

As mentioned, the REPT function doesn’t work when wrapped around the IMAGE function. However, flip them around, with the REPT inside the IMAGE function, and it does work!

In other words the IMAGE function accepts arrays of URLs as an input.

Start with this formula in cell A1, which creates a single string of joined URLs, with a pipe ( | ) delimiter between them:

=ArrayFormula(REPT("https://www.google.com/favicon.ico"&"|",5))

Now, split these into an array of 5 separate URLs:

=ArrayFormula(SPLIT(REPT("https://www.google.com/favicon.ico"&"|",5),"|"))

Finally, wrap this with the IMAGE function to get the five images in a row:

=ArrayFormula(IMAGE(SPLIT(REPT("https://www.google.com/favicon.ico"&"|",5),"|")))

What I like about this solution is that you could put the number 5 into a different cell and reference it, so that you can easily change how many times the image is repeated.

You could even embed another formula to calculate how many times to repeat the image πŸ˜‰

See the other Formula Challenges here.