If you’ve recently made the transition from Microsoft Office to Google Sheets, you might think you’re missing some treasured features. You just need to know where to look and perhaps a few differences in how they work.
You’ll find the pivot table tool in Sheets on the Data dropdown menu. If you remember the old Classic Pivot Table layout, it more closely resembles what you’ve been used to seeing in the newer versions of Office.
Instead of dragging and dropping fields or checking boxes, in the four sections of the layout area in the Report Editor task pane on the right, you click Add Field to populate the report. To remove a selected field, click the X in the upper right corner of the section.
Before moving any fields onto the pivot table, check out the Suggested section of the task pane. Also you’ll find the full complement of tools available in Sheets for pivot tables right there.
Looking for calculated fields? Click Add field in Values and select Calculated Field. You will need to type in or copy and paste the field names into the Calculated Field area. It’s a bit clunky since you can just select from a field list. One more “gotcha:” If your field name contains spaces, you’ll have to remember to enclose it in single quotes.
You will also find Conditional formatting in Sheets. For the uninitiated, Conditional formatting changes the appearance of fonts based on some pre-established conditions. You’ve seen this in other places when you type in a negative number, and it turns red. Begin by selecting the text you want to conditionally format. Then, on the Format dropdown menu, choose Conditional formatting. The Conditional format rules pane appears on the right side of the screen. First, double check that the range shown at the top of the pane is correct. Next, click into the list under Format cells if….
Once you select the type of condition, additional fields will appear below this section where you can complete the conditional definition. Below that, you’ll choose the appearance type. Select from the list or choose your own in the Custom format choices. Add an overlapping rule to the same range by selecting Add another rule.
You could also define a new range to format here the same way. Just change the range your formatting choices will affect at the top. To apply a color scale, rather than a single-color shading to a cell, choose the Color scale tab at the top of the pane.
Text to columns
This handy tool lets you break up a combo field into separate columns (for example, if you have cities, states and ZIP codes all in one column). Split text to columns will give you three separate columns, one for each data type. Begin by selecting the column you wish to break apart. Then, from the Data dropdown menu, choose Split text to columns. By default it will detect the separator. You may also choose another one or specify a custom separator other than those listed.