6 changes in the new Google Sheets

See some of the new features and functions in Google spreadsheets 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 going forward will use the updated interface but existing sheets won't (as of now). Note that if you need API support, protected sheets and ranges, notifications and 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 get to the filter creating option using the filter icon at the top right of Google Sheets.

Using filter in Google Sheets
Filter example

In this simple example using test data of percent 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, to indicate a filter view is active.

Filter-view choices are fairly basic and don't include filtering by formula. See more on filter views.

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 as well as a link to learn more about the specific function (the earlier Sheets UI had a link to see 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 close for 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) 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

At last, 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 -- deciding how to display each and typing in the separators you want.

For numbers, you can type the format you want directly into the custom format text box. See more.

COUNTIFS example in new Google Sheets
New if functions

Google added what it says were "highly requested new functions" including SUMIFS, COUNTIFS and AVERAGEIF. These make it easy to add, count or average only cells that meet certain conditions. Here, I can count number of states with with greater than 5% tech workforce 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 Drive Chrome Web app. To install while viewing your Google Drivein Chrome, click the More button to expand left nav options and then select Offline. Then  enable offline editing in Drive.