Tag: Google Sheets

The FLATTEN Function in Google Sheets flattens all the values from one or more ranges into a single column.

For example, in the following image, the four columns of data are stacked on top of each other by the single FLATTEN function in cell A6:

FLATTEN Function Syntax

It takes one or more range arguments.

It doesn’t require them to be the same dimensions, so you can happily mix single cells, rows, columns, and even static values, and the FLATTEN function won’t bat an eyelid. E.g.:

Note that this behavior is different to other functions like the FILTER function or the MMULT function that require specific matching dimensions with the input ranges.

FLATTEN is part of the Array family of functions in Google Sheets.

FLATTEN Function Notes

Entering the data as one big range or as separate columns affects how the FLATTEN function combines the data.

For example, if we pass in a single range as the argument to a FLATTEN function, it combines the data by stacking each row in turn, so that the data alternates:

Compare this to the output when we enter the columns of our data table as separate ranges:

In this case, we can see that the columns are stacked atop each other, i.e. all the values from column A first, then all the values from column B, etc.

FLATTEN Function Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.

You can also read about it in the Google Documentation.

FLATTEN Formula Example

The FLATTEN function is handy for tidying up messy data.

For example, suppose we have the following dataset with emails spread across columns A, B, and C, and we want to consolidate them into a single column:

The FLATTEN function is an easy, succinct method:

which gives an output:

Advanced FLATTEN Formula Examples

Unpivot Data With FLATTEN

Probably the most useful application I’ve seen for the FLATTEN function is to unpivot in Google Sheets.

Unpivot is a method to turn wide data into tall data.

It’s the opposite action to a pivot table, which turns data from the tall format (database) to a wide table format (good for humans and charts to read).

Unpivoting data used to be difficult in Google Sheets before the FLATTEN function arrived, but thankfully it’s now possible with a single array formula.

The unpivot is achieved by this array formula that joins the data together, flattens it, and then splits it into multiple columns.

Now I know you’re wondering about that planet emoji “🪐” in the formula…

For the SPLIT function, we can use whatever character you like as the delimiter, provided it’s not in our dataset already. Hence why I chose the 🪐 emoji!

Learn more about the unpivot method here: Unpivot In Google Sheets With Formulas (How To Turn Wide Data Into Tall Data)

Find Unique Items In A Grouped List

Suppose you want to find unique values from data that looks like this:

You want to extract a unique list of items from the column containing grouped words, which are separated by commas.

Use this formula to extract the unique values:

Read more about this technique in this post: Get A Unique List Of Items From A Column With Grouped Words

The TRANSPOSE function in Google Sheets interchanges the rows and columns of an array or range of cells in Google Sheets.

For example, suppose you are given data in a horizontal format like this:

It’s awkward to read because it’s too wide to fit on a single screen. We’re also better at scanning down columns than across rows.

The TRANSPOSE function will flip the horizontal arrangement to a vertical arrangement:

like so:

Much easier to use!

And it works better with other formulas like QUERY, SORT, FILTER, etc.

TRANSPOSE Function in Google Sheets Syntax

It takes a single argument: an array or range of cells.

Transposition works by swapping the row and column positions of each value. For example, a value in position row 3 column 10 will be put into position row 10 column 3 by the transposition operation.

As a result, ranges of size X rows and Y columns will become ranges of size Y rows and X columns.

In the screenshots at the top of this screen, the horizontal range with 3 rows and 21 columns became a vertical-oriented range with 21 rows and 3 columns.

It’s part of the Array family of functions in Google Sheets.

TRANSPOSE function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.

The TRANSPOSE function is also covered in the Day 25 lesson of my free Advanced Formulas 30 Day Challenge course.

You can also read about it in the Google Documentation.

TRANSPOSE Formula Examples

The TRANSPOSE function is useful in a wide variety of projects that make use of advanced formulas.

It pairs well with other text or array formulas, like the SPLIT function.

For example, maybe your data contains strings of comma-separated values in a cell that you want to present in column format. E.g. suppose you have an array of numbers in cell A1:

1,2,13,24,35,15,71

Then you can combine the TRANSPOSE function with the SPLIT function to achieve this:

This post on how to alphabetize comma-separated strings uses this TRANSPOSE + SPLIT technique to break apart a string, before going on to sort the data and recombine.

More examples of TRANSPOSE formulas

Etch A Sheet In Google Sheets

Running Total Array Formulas Using The MMULT Function

The CHAR function in Google Sheets is a nifty function that converts a number into a special character according to the current Unicode table.

It’s super easy to use and is a great way to add some fun to your Google Sheets.

What is the CHAR Function?

It’s a function that turns numbers into special characters, e.g. emojis, in your Google Sheets.

For example, suppose you want to add a smiling emoji to your Sheet.

Of course you can copy-paste it into your Sheet, but you can also enter it via a formula:

Adding it via a formula has the advantage that you can generate and use these special characters in other formulas more easily than if they are text characters.

As another example, maybe you want some fish in your Google Sheet? Sure, here you go:

Or perhaps a picture of a lion next to the Statue of Liberty:

The point is, you can use the CHAR function to generate thousands of special characters in your Google Sheets and add some fun and self-expression.

There are lots of more practical examples of the CHAR function further down this post.

CHAR Function in Google Sheets Syntax

It takes one argument that is a number. It can be entered directly as a number, reference another cell containing a number, or contain a nested formula that outputs a number.

E.g.

or

or

It converts the given number into a special character according to the current Unicode table.

It’s part of the TEXT family of functions in Google Sheets.

How To Get The Numbers For The CHAR Function

I use a tool called Graphemica to find the number of special characters.

The workflow is:

1. Search for a character, e.g. music
2. Select the character you want, e.g. quarter note ♩
3. Scroll down to the Code section
4. Copy the numbers after &# from the HTML Entity (Decimal) section, e.g. 9833. The HTML Entity number is the decimal representation of the unicode number, which the CHAR function requires.
5. Add to the CHAR function in your Google Sheet, e.g.
   =CHAR(9833)

Here is what this process looks like:

You can also explore the CHAR formula output by putting this formula into cell A1:

And dragging it down as far as you dare!

Or open this Sheet where I’ve done that for you 😉

The first 32 rows are blank and the emoji characters start around row 129292…

CHAR Function Template

Click here to get a copy of the CHAR Function Template

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.

The CHAR formula is also covered in the Day 30 lesson of my free Advanced Formulas 30 Day Challenge course.

You can also read about it in the Google documentation.

Interesting CHAR Function Examples

Star Rating

You can use the CHAR function with the REPT function to create a star rating system.

The formula is:

Header Tricks

The formula for this is:

Here’s an alternative header trick, using text rotation:

The formula for this example is:

Set the text rotation to -35 or -45 to achieve the same look.

Superscript and Subscript Example

Use the CHAR function to add superscripts and subscripts to words, which is especially useful for creating math or science equations in Google Sheets:

See this post: How To Add Subscript and Superscript In Google Sheets

Playing Cards

Here’s a nice example of the CHAR function used to show playing cards in your Google Sheet, as part of an explanation as to why a shuffled deck of cards is unique.

Fruit Machine

Naturally, you can and should build something silly with the CHAR function, like this fruit machine.

Toggle the checkbox to shuffle the fruits to see if you hit the jackpot!

How does this work?

Firstly, we create a lookup table with the fruits, shown here with their character codes:

Then we create the fruit machine algorithm with the RANDBETWEEN function, the INDEX function, and array literals:

The RANDBETWEEN function randomly selects a number between 1 and 9. The INDEX function returns the fruit at that position from the fruit array.

The curly brackets (array literals) create an array of the three fruits next to each other.

Every time the checkbox is toggled, it causes the RANDBETWEEN to recalculate.

Finally, we use this IF function to check if it’s a jackpot:

There are thousands more CHAR characters to explore, so I encourage you to go and experiment.