Next Steps
Google Sheets Cheat Sheet
This cheatsheet will help you get started with Google Sheets, describing the Google versions of all the tools we teach in Learn Microsoft Excel for Data Analysis.
Mathematical Formulas
Most of the standard mathematical formulas are the same across spreadsheet programs, including =MAX, =MIN, =AVERAGE, and =SUM.
Dragging Formulas
Like Microsoft Excel, Google Sheets let you drag formulas by selecting the bottom-right corner of the cell you want to drag. In some tables, Google Sheets may offer an “autofill” option before you drag the formula down.
Dollar Signs
Google Sheets uses the same dollar sign syntax as Microsoft Excel when writing draggable formulas. A $ before the column letter stop the column from changing when dragged, while a $ before the row number stops the row number from changing when dragged.
=A4 becomes =A5 when dragged down one row=A$4 remains =A$4 when dragged down one row
Sorting Data
To sort data in Google Sheets, select the data you want to sort, then select
Data -> Sort Range -> Advanced Range Sorting Options.
If your range has headers, select the has headers box so that those stay at the top. Then, select the column you want to sort and the direction of sorting (i.e. increasing/decreasing).
Filtering Data
To filter data in Google Sheets, select the data you want to filter including the headers, then select Data -> Create a Filter. This will add filter icons to the header of each column of the range. Click the filter icon on the column you want to filter by. Use Filter by Condition to filter a range of values by selecting an option from the dropdown.
Pivot Tables
Pivot tables can be created in Google Sheets by selecting the table to pivot and then select Insert -> Pivot Table.
Customizing Pivot Tables
To customize a pivot table in Google Sheets, click Add next to Rows to select row labels, click Add next to Columns to select column labels.
Pivot Table Calculations
To customize the values in a pivot table in Google Sheets, click Add next to Values to select the column to use in the calculation. Use the Summarize by dropdown to switch between count, average, max, etc.
Refreshing Pivot Tables
Unlike Microsoft Excel, Google Sheets refreshes pivot tables automatically.
Pivot Table Source
To change the source of a pivot table in Google Sheets, select a cell of the pivot table to see the pivot table menu. The current source range is listed at the very top of the menu. Click the icon next to that range to alter the range.
Create a Heatmap
To create a heatmap in Google Sheets, select the data. Then select Format -> Conditional Formatting and click Add another rule. Select the Color Scale tab. Click the color scale under Preview to select a color scale.
Create a Cell Rule
To color cells based on a rule in Google Sheets, first select the data. Then select Format -> Conditional Formatting and click Add another rule. Make sure you are on the Single Color tab. Use the Format cells if dropdown to add the rule (e.g. if <26) and click the preview color to select a color.
Create a Chart
To create a chart in Google Sheets, start by selecting the entire table of data, then select Insert -> Chart. Make sure you are on the Setup tab of the chart menu, and click the Chart Type dropdown to select the type of chart (pie, column, histogram, scatter, line,…)
Modify Chart Titles
To add titles to a chart in Google Sheets, first select the chart. Then select the Customize tab of the chart menu. Select Chart and Axis Titles to modify the chart and axis titles (note, you’ll have to use the Chart Title dropdown to view and modify the axis titles.)
Import a File
Select File -> Import.
Text Functions
Google Sheets has the same basic text-cleaning functions as Microsoft Excel, including =LEFT, =LOWER, and =TRIM.
If cell A3 contains the value "Test "=LEFT(A3,2) is "Te"=TRIM(A3) is "test"=LOWER(A3) is "test "
Formatting Cells
Like Microsoft Excel, Google Sheets can apply formatting to numeric and text cells. Select the data you want to format, then select Format->Number to format numbers, currency, and dates (or Format -> Text to format text)
Protecting Sheets
Google Sheets can protect sheets from being edited. Select Data -> Protect Sheets and ranges and then Add a sheet or range. Select the Range or Sheet tab depending on which you want to protect. Name the rule, and then select the sheet or range to protect. Select Change/Set permissions to modify what users can do on the sheet or range.
Unprotecting Specific Cells
Google Sheets can uprotect specific cells on an otherwise protected sheet. When you protect the sheet, check the box Except certain cells and then select the unprotected cells.
Hiding Sheets
Google Sheets can hide sheets like Microsoft Excel. Select the arrow on the sheet name, and then Hide Sheet.
