6 useful changes in new Google spreadsheets

See some of the new features and functions in Google Sheets before deciding whether to upgrade your account.

Updating Google Sheets
Updating your Sheets

Google has updated its Drive spreadsheet application with faster performance, more cells, offline editing, no limits on how many cells you can copy & paste, and some added features we'll outline on the next pages.

If you decide to try the new Google Sheets, your new spreadsheets will use the updated interface going forward but existing sheets won't (as of now). Note that if you need API support, protected sheets and ranges, notifications or spell checks, don't upgrade now, since Google says these aren't ready yet.

To switch, head to the Editing tab in your Google Drive settings.

New filter view in Google Sheets
Filter views

As the name suggests, these allow you to create specific filtered views of your data that you can then save and share, without having to duplicate the entire sheet and make changes to a copy. You can create a new filter view by using the filter icon at the top right corner of Google Sheets.

Using filter in Google Sheets
Filter example

Here is a simple example: Using test data specifying the percentage of tech workers in the workforce by state, I created a view by manually selecting just the New England states. In the future, any time I want to see that slice of the data, I can choose "New England" from the filter dropdown list. You can see the filter name, as well as the darkened frame area around the spreadsheet data; this indicates a filter view is active.

Filter-view choices are fairly basic and don't include filtering by formula.

Expanded function help in Google Sheets
More help with functions

Google Sheets now comes with more detailed explanations about functions, including an example of the function in action and a link that takes you to more information about that specific function. (The earlier Sheets UI had a link that showed all functions, not the one you were using.)

There's also more color coding when writing formulas, which makes it easier to see things like open and closed quotation marks.

Custom formulas in conditional formats

Adding custom formulas to conditional formatting means that you can now include values of other cells in a row within your rules for conditional formats. In this example, I added two custom formulas to column A: =percentrank($B:$B, B1)>=0.9 is set to green and =percentrank($B:$B, B1)<=0.1 is set to red. This color-codes the state names using column B values that are in the top (green) and bottom (red) 10%.

In addition, wildcard searching in conditional formating allows for partial matches: ? to match any one character and * to match 0 or more.

Custom formats in Google Sheets
Custom formats

Finally, if Sheets doesn't have the format your want for dates and numbers, you can create your own by choosing Format > Number > More Formats or by clicking on the 123 dropdown and selecting More Formats.

For dates, you can build your own format by selecting various blocks such as year, month, date, hour, minutes and seconds; you can then decide how to display each and type in the separators you want.

For numbers, you can type the format you want directly into the custom format text box. More info here.

COUNTIFS example in new Google Sheets
New if functions

Google has added what it says were "highly requested new functions" including SUMIFS, COUNTIFS and AVERAGEIF. These make it easy to add, count or average only those cells that meet certain conditions.

In this example, I can count the number of states with a tech workforce greater than 5% and a margin of error less than 0.5 with the formula:

=COUNTIFS(B2:B53, ">5", C2:C53, "<0.5")

Working offline in Google Sheets
Working offline

You can now add spreadsheets to the list of Google Drive file types available to view and edit offline -- but only those created after switching to the new version of Sheets. Desktop/laptop access works in Chrome after installing the browser's Drive app.

It's simple to install it while viewing Google Drive in Chrome: Just click the More button at the bottom of the left-hand navigation menu and then click on the Offline button to enable the feature.