Google Sheets : More Useful Than You Think

Hello Internet Neighbors!

Are you frustrated with spreadsheets?  Yeah, I know the feeling.  You probably know the basics, but there is just one little thing that you can't figure out... It sounds easy, but how the heck do you do it?

While I don't consider myself an expert, I know a few tips and tricks and have helped people with Excel, but it seems like Sheets is more popular online and I like the ability of looking up stock prices and sharing/embedded charts.

The guide covers just the most common features that you would use. The following infographic might help you out, (copyable text comes right after).

Let me know your favorite spreadsheet usages or if you have any questions, let m e know  in the comments below!

The Infographic

Get the guide!
A Word From the Sponsor

Try Out the Formulas

Head to sheets.google.com and try these formulas
  • =GOOGLEFINANCE("GOOG","price")
  • =COUNTIF(B:B, ">3")  - Count the number of items greater than 3
  • =COUNTA(B:B) - Count the number of non blank cells
  • =UNIQUE(B:B) - Show only unique items of column B
  • =IMAGE("the URL of image")
  • All the formulas (from the Google documentation)

Split Up Items

Do you ever run into situations where you want to make a comma separated into 2 or more columns?

Highlight the data, then go to: Data > Split Text To Columns

(you can specify another character to split on besides commas)

Filter Data

Once you have your data split into columns, you can assign names to each one of your columns

Now, go to Data > Filter

The Index-Match Formula

Q: You have two sheets mapping two different properties. How do you combine them?

A: Create a new column (in Sheet1) and use the “index match” formula

=index( [column to output (in table 2) ] , match( [ cell value you want to look up] , [column to look up the value in] , false) , 1)

If the second table is in another worksheet, use "Sheet name"!A:A to reference column A

Color the cells automatically

Select the column

Go to: Format > conditional formatting

You can highlight (and format text of) the cell with the following conditions :

  • cell value contains some text you specify
  • cell value is greater/less than (or between) certain dates/numbers or letters​

Count ALL THE THINGS!!!

So you have a list of items and want to count how many of each?

Select column A

Go to: Data > Pivot Table

  1. Under ‘Rows’, click Add field and select the column heading (e.g. ‘choice’)
  2. Under ‘Values’, click Add field and select the same column heading
  3. Select ‘COUNTA’ to count the number of entries

Embed Charts on Your Site

Select the columns/cells you want a chart for

Hold down Ctrl to select multiple cells

Go to : Insert > Chart

Did you know? You can get HTML embed code to publish the charts on your website!

Don’t have a site? Use sites.google.com create one quickly - if you can copy and paste text, then you can do this!

For website owners: Your Google Analytics in Charts

Using the Google Analytics spreadsheet add-on, you can: show visitors by country/browser or show # visitors for the past two weeks (these are just a few examples)

​The charts can be embedded and automatically updated!

Here are my charts

Leave a Comment!