Spreadsheets Archives - Page 15 of 34 -

A Complete Guide To The CHAR Function In Google Sheets

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:

Smiley Face Emoji with CHAR function

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:

Fish formula with CHAR function

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

Lion Statue Liberty in Google Sheets

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.

=CHAR(128578)

=CHAR(A1)

=CHAR(IF(RAND()>0.5,128994,128308))

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:

Search for a character, e.g. music
Select the character you want, e.g. quarter note ♩
Scroll down to the Code section
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.
Add to the CHAR function in your Google Sheet, e.g.
=CHAR(9833)

Here is what this process looks like:

Graphemica Workflow

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

=CHAR(ROW())

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.

Star Rating in Google Sheets

The formula is:

=REPT(CHAR(11088),B2)

Header Tricks

Header with arrows in Google Sheets

The formula for this is:

="Cohort " & CHAR(8594) & CHAR(10) & "Months after signup "&CHAR(8595)

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

Header with text rotation in Google Sheets using the CHAR function

The formula for this example is:

="Sales Quarter" & CHAR(10) & REPT(CHAR(8211),15) & CHAR(10) & "Regions"

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:

Math Equation 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.

Deck of cards in Google Sheets with the CHAR function

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!

Fruit Machine Jackpot with the CHAR function

How does this work?

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

Fruits Array in Google Sheets

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

={ INDEX(B6:B14,RANDBETWEEN(1,9)), INDEX(B6:B14,RANDBETWEEN(1,9)), INDEX(B6:B14,RANDBETWEEN(1,9)) }

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:

=IF(PRODUCT(B20:D20)=343,"JACKPOT!!!","Try again...")

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

How To Save Data In Google Sheets With Timestamps Using Apps Script

In this post, you’ll learn how to save the data in Google Sheets with timestamps, so that you have a record of the data at set time intervals (e.g. once a day or once a week).

We use Apps Script to save the data and set it to run automatically in the background.

save data in google sheet

Continue reading How To Save Data In Google Sheets With Timestamps Using Apps Script

Etch A Sheet In Google Sheets

I was playing with my children the other day when one of them grabbed our Etch A Sketch toy and started drawing a treasure map with it.

Sitting in my office later that day I had a crazy thought “Could I build a working Etch A Sketch in Google Sheets?”

Two days later and boom! Here it is:

Etch A Sheet Game In Google Sheets

🔗 Grab your own copy of the template at the bottom of this article.

The game works using four techniques:

Checkboxes as buttons
Self-referencing formulas with iterative calculations
Dynamic array, or spill, formulas to generate coordinates
A sparkline formula to draw the line

It doesn’t use any code. In fact, it’s created entirely with the native built-in functions of Google Sheets.

Before I dive in though, I want to acknowledge a fellow Google Sheets aficionado…

Hat Tip To Tyler Robertson

Tyler Robertson is a Google Sheets wizard who’s built an amazing portfolio of spreadsheet games (described by some as the Sistine Chapel for spreadsheets) using only built-in formulas.

Thankfully, he hasn’t built an Etch A Sketch clone yet 😉

This Etch A Sheet game uses Tyler’s checkboxes as a button technique, and has similar logic to his moving-a-character-around-a-Sheet game.

So thank you, Tyler, for your amazing work!

How Does Etch A Sheet Work?

Etch A Sheet Game In Google Sheets

Just like the real Etch A Sketch game, there are controls to move the stylus left or right and up or down, to create lineographic images.

Since you can’t “shake” a Google Sheet (although I bet you wish you could sometimes!), there’s an additional reset checkbox to clear out the image and put the stylus back to the bottom left corner.

Etch A Sheet Formulas

There’s a button to open up the right side of the Sheet and see the formulas that generate coordinates for the sparkline function:

Etch A Sheet formulas

The buttons are regular checkboxes, which toggle a TRUE/FALSE value in the cell.

The checkbox in cell H30 is the reset checkbox and I’ve called it “reset” in the named ranges box.

Named Range in Google Sheets

Left Button Formulas

In cell V3:

=IF(F30<>V3,F30,V3)

In cell U3:

=F30<>V3

I also named U3 “right”.

Then in cell W3:

=IF(reset,0,IF(right,W3+1,W3))

With iterative calculation switched on (see File > Settings > Calculation) and set to a maximum of 1 iteration, these formulas let the checkboxes function as buttons (see Tyler Robertson’s post for more details of how this works).

Starting Coordinates

I put the value 1 in cell U16 and named it “startX”.

Similarly, I put a 1 in cell U19 and named it “startY”.

Current X Formula

The main IFS function, which controls the horizontal X coordinate, is in cell V16:

=IFS(reset, startX, AND(right,currentX<50), currentX+1, AND(left,currentX>1), currentX-1, TRUE, currentX)

This formula cell is labeled as a named range “currentX”.

Firstly, it checks if the reset button is checked and if it is, resets the value of the cell to the starting X value.

If the reset button is unchecked, i.e. FALSE, then it checks if the right button is pressed and the current X is less than 50, using an AND function, and if it is, adds 1 to the current value.

Otherwise, if the left button is pressed and the current X is greater than 1, it subtracts 1 from the current value.

Finally, there is a TRUE condition to act as a catch-all when the previous conditions fail. It sets the value back to the start value.

X Path Formula

This self-referencing formula is put into the adjacent cell, W16, and called “xPath”.

It appends each new current value to itself to create a string of x values as the buttons are pressed, i.e. “1”, “1,2”, “1,2,3”, “1,2,3,4” etc.

=IF(reset,,xPath & "," & currentX)

For this to work, the spreadsheet needs to have iterative calculations enabled with a max of 1 iteration.

The settings are found under the File > Settings > Calculation:

Google Sheet settings

X Number Formula

This formula turns the xPath string of values into a column of numbers, which is fed into the sparkline function in the next step.

=IFERROR(TRANSPOSE(SPLIT(xPath,",")),"")

The SPLIT function breaks up the previous string of data, by the comma separator.

The TRANSPOSE function turns the array from a row vector to a column vector.

The IFERROR function wrapper hides the error message when the xPath variable is empty.

Y Formulas

The same formulas are replicated to create a column of Y coordinates.

Sparkline Formula

=IFERROR(SPARKLINE(Q3:R,{"linewidth",2 ; "xmin",1 ; "xmax",50 ; "ymin",1 ; "ymax",50}),"")

Here, the sparkline formula takes the X Number and Y Number coordinates (two columns of numbers) and simply plots them as a line.

In the sparkline options, I’ve set the linewidth to 2 so it stands out more. I also set min and max values for the canvas, so that the drawing always starts from the bottom left.

Using Groups To Show/Hide Content

This is another interesting technique, used here to show or hide content that doesn’t need to be on display all the time. I’ve used the same technique for the “Formulas” section, shown in the GIF above.

The grouped row button below the Etch A Sheet board shows and hides the instructions section when it is toggled:

Etch A Sheet instructions

Finishing Touches

There are a few other steps to complete the Etch A Sheet:

Merge a big section of cells for the sparkline area
Add a thick red background around the outside of the Etch A Sheet
Add a heading, in a playful gold-colored font
Remove gridlines (one of the best tips to make your Sheets look good!)

And there you have it!

Improvements

Alternative Controls

To stay true to the original game, I put the horizontal checkbox buttons on the left side and the vertical controls on the right side.

However, these are awkward to press because you have to jump back and forth between them with your cursor. Of course, it doesn’t matter with the physical Etch A Sketch because the dials are positioned for each hand and can be operated simultaneously.

Perhaps a better approach in the Sheet version is to put the checkbox buttons close together so that the cursor movement is minimized.

Alternative Controls

Starting From The Previous Position

Every Etch A Sheet game restarts from (1,1).

However, when you turn a real Etch A Sketch upside down, shake it, and then restart, the line is drawn from wherever it last finished. It does NOT revert back to the bottom left.

So I’ll leave this as a challenge for you! Can you modify the formulas to match this behavior?

Formula Bug

If you look really closely at the GIFs in this post, you’ll notice that the line is one step behind the button press. I.e. when I press a button, it has to draw the previous step still before registering the new button action. Obviously, this is not ideal.

Again, I leave this as a challenge for you to explore…

Unfortunately, I have to get back to my actual work now, so I’m going to leave this fix for another day. This project exceeded my expectations (It was a lot of fun! It was intellectually challenging! I learned some new techniques!) so I feel satisfied with this outcome. I don’t feel the need to make it perfect.

Etch A Sheet Template

Open the Etch A Sheet template here.

Make your own copy: File > Make a copy

(Note: If you are unable to open this file, it’s probably because it’s from an outside organization and my Google Workspace domain is not whitelisted at your organization. You may be able to ask your Google Workspace administrator about this. In the meantime, feel free to open it in an incognito window and you should be able to view it.)

If it does not appear to work, check you have the iterative calculations enabled.

Go to File > Settings > Calculation

Make sure Iterative Calculations is ON and set to 1 iteration. See the image under “X Path Formula” for more details.

If you do make a copy, I’d love to see what you draw with it!

Dot Plots In Google Sheets

Dot plots are simple charts consisting of dots plotted on a simple scale, typically used to show small counts or distributions.

Dot plots are one of the simplest statistical charts, only suitable for small-sized data sets. They’re helpful for understanding the “shape” of your data by highlighting clusters, gaps, and outliers. (A histogram is better suited to showing the data distribution of larger datasets, e.g. > 30 datapoints.)

Here’s a table using dot plots to show the hypothetical number of meetings per day for these five employees:

How To Create Dot Plots In Google Sheets

You create dot plots in Google Sheets with formulas!

Suppose we have this data in row 1 of a Google Sheet, in cells A1 to E1:

Step 1:

Create a basic REPT function next to the data, e.g. in cell F1:

=REPT("*",A1)

Step 2:

Next, turn this REPT formula into an array formula:

=ArrayFormula(REPT("*",A1:E1))

Step 3:

Then use the JOIN function and CHAR function to combine the array output. CHAR(10) creates a carriage return, which we use as the delimiter:

=ArrayFormula(JOIN(CHAR(10),REPT("*",A1:E1)))

Step 4 (optional):

Convert the * into circles with the CHAR function:

=ArrayFormula( JOIN(CHAR(10),REPT(CHAR(9679), A1:E1)))

Step 5 (optional):

Rotate the cell up:

Format > Rotation > Rotate up

Here’s an image showing the outputs for these 5 steps in column F:

How To Create Multi-Colored Dot Plots In Google Sheets

Taking this idea one step further, we can add colored symbols to indicate the relative counts.

Here’s an example with green dots for large counts, then orange, and then red dots for the smallest counts:

Multi-Colored Dot Plot in Google Sheets

The formula is more complex and uses the IFS Function to categorize the inputs by relative size:

=ArrayFormula(JOIN(CHAR(10),REPT(IFS(A1:E1/MAX(A1:E1)>0.85,"🟢",A1:E1/MAX(A1:E1)>0.5,"🟠",TRUE,"🔴"),A1:E1)))

How does this formula work?

It’s an array formula that takes an input of the five numbers in columns A to E.

Inside the IFS function, the number (e.g. 7) is divided by the maximum number in the range (10 in this example) and compared to see if it’s bigger than the first threshold (0.85 in this example). If this is true, then the green dot is plotted, otherwise, the threshold is checked (0.5 in this example) If that’s true, then orange dot is used. If that is not true, then the red dot is used as the default.

The REPT function and the JOIN function perform the same way as step 3 above for the simpler single color example.

You can also replace the colored dots in this formula with their CHAR function equivalents, to keep it entirely formula driven:

=ArrayFormula(JOIN(CHAR(10),REPT(IFS(A1:E1/MAX(A1:E1)>0.85,CHAR(128994),A1:E1/MAX(A1:E1)>0.5,CHAR(128992),TRUE,CHAR(128308)),A1:E1)))

As a final step, don’t forget to rotate the cell text up, to get the dots plotted as columns rather than bars.

Notes

This Dot Plot technique first appeared in issue 188 of my weekly Google Sheets Tips newsletter. Signup here if you’d like to receive it!.

Thanks to reader Marcel L. for his sharing his idea for the multi-colored dot plot.

Slow Google Sheets? Here are 27 Ideas to Try Today 🐢

(Updated Jan 2022 to reflect the increase in the cell limit of Google Sheets to 10 million.)

Slow Google Sheets?

We’ve all been there, stuck watching the little loading bar creep slowly, frustratingly to its conclusion:

Slow Google Sheets loading bar

How can you speed up a slow Google Sheet?

First off, this is a difficult question to answer because there are so many factors that may or may not be causing you to have a slow Google Sheet.

What follows in this article is some suggested optimization strategies and some research into what causes slow Google Sheets.

Strategies to speed up Google Sheets

Continue reading Slow Google Sheets? Here are 27 Ideas to Try Today 🐢