Thread by @benlcollins

Mega thread of #GoogleSheets #productivity tips & tricks coming up...

Starting with this humdinger

Type “sheet.new” or "sheets.new" into your browser to instantly create a new Google Sheet

(1/n)

From inside a folder of @GoogleDrive press

Shift + S

To create a new Google Sheet in that folder
(2/n)

Quickly freeze panes in your #GoogleSheet by hovering over the border of the sheet (just under the column letters) until you see the hand icon.

Drag down as many rows as you want to freeze.

Works for columns too!
(3/n)

Clear all formatting on a cell or range in #GoogleSheets with this shortcut:

On Mac: ⌘ + \
On PC/Chrome: Ctrl + \

Probably my favorite shortcut!
(4/n)

Another great shortcut. Insert the current date in a cell

On Mac: ⌘ + ;
On PC/Chrome: Ctrl + ;

(5/n)

And another great shortcut. Select all the data in a table

On Mac: ⌘ + A
On PC/Chrome: Ctrl + A

(6/n)

Yet another great shortcut. Toggle between relative and absolute cell and range references with F4 (i.e. quickly add or remove the $ signs)

F4

(7/n)

Ok, one more super useful shortcut. Find and Replace in #GoogleSheets

On Mac: ⌘ + Shift + H
On PC/Chrome: Ctrl + H

(8/n)

Suppose you have a column of values with a formula or value in the top cell of the adjacent column.

Quickly fill down columns by double-clicking the blue button in bottom R corner of cell, or using Ctrl + D (Cmd for Mac) or Ctrl + Enter (Cmd for Mac)

(9/n)

Use the CHAR function to insert special characters into cells. It converts numbers into a character according to the Unicode table. E.g.

CHAR(8594) produces the right arrow.
CHAR(10) produces a carriage return (new line).
CHAR(8595) produces a down arrow.

(10/n)

Here’s a few more interesting CHAR function examples:

(11/n)

Want to round to the nearest 10, 100, 1000 etc. ?

Use the ROUND function with negative numbers to round numbers to the nearest 10, 100 etc.

For example, this formula would round a number in cell A1 to the nearest ten:

= round( A1, -1 )

(12/n)

For cells containing formulas, press the F2 key to enter into the formula.

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

(13/n)

The F2 key has another useful property.

Per previous tweet, press F2 to enter formula. If you position your cursor over a range of data in your formula and then press the F2 key again, it will highlight that range of data for you!

(14/n)

With the #GoogleSheets function helper pane, press the “X” to remove the pane.

Minimize/maximize with the arrow in the top right corner.

Click the blue question mark next to the equals sign of your formula to restore the function helper pane.

(15/n)

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

Scroll up and down the list with the Up and Down arrows, and then click on the function you want or press the TAB key to select.

(16/n)

Big formula? No problem!

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.

(17/n)

Another tip for big formulas: make them multi-line!

Press Ctrl + Enter inside the formula editor bar to add new lines to your formulas, to make them more readable.

(18/n)

And another tip for big 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.

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

(19/n)

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).

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

(20/n)

Array Formulas are powerful! Work with ranges of data in #GoogleSheets formulas

Pressing Ctrl + Shift + Enter (PC/Chromebook) or Cmd + Shift + Enter (on a Mac) when you finish your formula

Or type in the word ArrayFormula and add brackets to wrap your formula.

(21/n)

Array literals create ranges. Construct them with curly brackets: { }

Commas separate the data into columns on the same row (in Europe it’s back-slash instead of commas).

Semicolons create a new row in your array.

= { 1 , 2 ; 3 , 4 }

(22/n)

Complex formulas are like onions on two counts: they have layers and they make you cry 🤣

Build complex formulas in steps with 1 change per step.

To understand complex formulas, peel back the layers. Then, build back up in steps. #GoogleSheets

(23/n)

Working with #data in #GoogleSheets? Complex project?

Document the steps you take!

You’ll be glad of some notes about where your data came from, what assumptions you made, what calculations you decided to do, why and how you did them!

(24/n)

With new datasets, tell the story of one row.

Read across one row of your data so you understand what’s in every column, before rushing into the work.

(25/n)

The #GoogleSheets chart tool expects data in a “wide-format” table rather than a traditional “tall-format” table (how data is stored in databases).

This “wide-table” format makes it easy for the chart tool to parse the data correctly

(26/n)

Use data validation for data entry in #GoogleSheets, to control what a user can enter into a cell.

E.g. drop-down menu of choices or restrict to numbers only.

E.g. use the ISEMAIL function in data validation to ensure only valid email addresses can be entered
(27/n)

Even better, use Google Forms for data entry.

Google Forms are an even more robust way to collect user inputs, because it separates the data collection from the data storage/analysis. They pair seamlessly with #GoogleSheets

(28/n)

One cell = one piece of information

Each cell should contain just one piece of information. Don’t be tempted to put more than one datapoint into a cell.

Cells with single data points can be used in formulas and charts. Those with multiple data points can’t.
(29/n)

Add color to highlight calculation columns you add to datasets. It’s helpful to be able to distinguish the original raw data from any columns you’ve added in the course of your analysis.

(30/n)

Add an index column before sorting & filtering so you can always get back to the original order.

It’s a simple numeric counter on each row, starting from 1 and going up to the last row of your dataset

(31/n)

Rules to format the header row:

Center your column headings.

Wrap header text.

Make header text bold.

(32/n)

Turn formulas into static values after use in large #GoogleSheets to improve performance.

Highlight the formulas and copy.

Now Paste-Special-As-Values

Right click and select Paste special > Paste values only

On Mac: ⌘ + Shift + V
On PC/Chrome: Ctrl + Shift + V

(33/n)

Keep copies of your formulas

You’ll thank yourself later when you need to re-use it or when someone asks you how you derived a certain column.

Put a single quote ‘ in front of a formula to change it to text and then keep a copy somewhere in your #GoogleSheet
(34/n)

Use named ranges for your datasets

It’ll reduce errors from incorrect range references or mixing up relative/absolute references, and has the added benefit of making your formulas clearer to understand.

Find it in the Data menu: Data > Named ranges...

(35/n)

Merged cells should be avoided in datasets.

They cause all sorts of problems in datasets: they break formulas, nobody will know which column or row they relate to, your data gets overwritten, etc…

(36/n)

Know the size limits of #GoogleSheets (as of Jan 2020)

5 million cells per workbook

Max of 18,278 columns

Max of 50,000 characters in a single cell

Add max of 40,000 new rows at a time

(37/n)

Delete unused rows and columns

Blank cells slow down performance.

Delete so you reduce the number of cells #GoogleSheets is holding in memory.

Ideally you should remove any large numbers of blank rows under your datasets, or columns to the right of your datasets.

(38/n)

Be aware of volatile functions!

NOW(), TODAY(), RAND() and RANDBETWEEN(), are volatile functions, which means they recalculate every time there’s a change to the Sheet.

All dependent formulas must also be recalculated too. They can hamper performance.

(39/n)

Manage expensive formulas with a control switch

Use data validation to create a drop-down menu in a single cell, the control cell, with values “Process” and “On Hold” (or whatever makes sense for you).

Then use an IF formula to control.

(40/n)

Phew, that’ll do. If you use #GoogleSheets and want to get better at, sign up for my newsletter where I share a new #GoogleSheets tip every Monday:

benlcollins.com/google-sheets-…

(41/n)

Some other resources for you:

Advanced Formulas 30 Day Challenge courses.benlcollins.com/p/advanced30

benlcollins.com/spreadsheets/g…

benlcollins.com/spreadsheets/d…

benlcollins.com/spreadsheets/s…

(42/n)

Thank you all and have a great day! 🤓👋

(43/n)