Codecademy Logo

Next Steps

Print Cheatsheet

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.

A screenshot of Google Sheets. The numbers 1, 2, and 3 are in the cells A1, A2, and A3. The cell below that column contains the formula =MAX(A1:A3). There is a tooltip above the formula saying 3, which is the maximum.

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.

Screenshot of Google Sheets. A cell with the value 2 is selected. There is a blue box in the bottom right to click and drag.

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).

Screenshot of Google Sheets. The header button Data is selected, revealing a menu with Sort Sheet and Sort Range options. Sort Range is selected, opening a menu with options "Sort range by column A (A to Z)", "Sort range by column A (Z to A)", and "Advanced range sorting options." The last is highlighted.

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.

A screenshot of Google Sheets. A cell with the value "header" is selected, and a filter icon (a long line over a medium length line over a short line) is selected. This reveals a menu with the options "Sort A to Z", "Sor Z to A", "Sort by color", "Filter by color" and "Filter by condition". The last is selected, revealing a dropdown with various filter conditions, including "Greater than" which is selected.

Pivot Tables

Pivot tables can be created in Google Sheets by selecting the table to pivot and then select Insert -> Pivot Table.

Screenshot of Google Sheets. The `Insert` tab at the top is selected, revealing a menu with options Cells, Rows, Columns, Sheet, Chart, and lastly Pivot Table. Pivot Table is highlighted.

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.

Screenshot of Google Sheets. At the top, the Add button next to Rows is selected, revealing a dropdown of headers from a table. Country is highighted.

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.

A screenshot of google sheets. At the top is the word Values with an Add button. Below is a grey box titled Book titles per capita. Below that is the label Summarize As above a dropdown with AVERAGE displayed.

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.

Screenshot of google Sheets. On the right is the pivot table menu, with Sheet2!A1:E24 at the top and a table icon to the right of that text. After clicking on the table icon, a Select a Data Range dialogue is open to the left, with a text-entry field containing with Sheet2!A1:E24 and Cancel and OK buttons.

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.

Screenshot of Google Sheets. The Format tab is selected, a menu with options beginning Theme, Number, and Text. Further down the menu, the Conditional Formatting option is highlighted. Next to this, the Conditional format rules dialogue is pictured. There are two tabs: Single color and Color scale. Color scale is selected. Below that, there is an "Apply to range" box with the range C2:C24 selected, followed by the Format Rules, set to Default.

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.

A screenshot of the Conditional format rules dialogue in Google Sheets. The "Single color" tab is selected, and below that are the "Apply to range" text entry box containing C2:C24 and the Format rules, currently set to "Format cels if...." with a dropbox reading "Is not empty".

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,…)

Screenshot of Google Sheets. The "Insert" tab is selected. In that dropdown menu, the "Chart" option is highlighted. The "Chart editor" opens up to the right, with the "Setup" tab selected. Below that is a "Chart type" dropdown with "Scatter chart" selected.

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.)

The Chart Editor in Google Sheets. There are two tabs at the top: Setup and Customize. Customize is selected. There are several expander sections, the "Chart & axis titles" one is opened up. A dropdown at the top reads "Chart Title". Below that is text reading "Title text" and then a text box with "Enter title here" written in it (we wrote that.)

Import a File

Select File -> Import.

Screenshot of Google Sheets. The "File" menu is selected, with the "Import" option in the dropdown highlighted.

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)

Screenshot of google Sheets. The "Format" tab is selected. In the Format dropdown, "Number" is selected, revealing options like: automatic, plain text, number, percent, scientific.

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.

Screenshot of Google Sheets. The "Data" tab is selected, revealing a dropdown starting with "Sort Sheet". The "Protect sheets and ranges" option in the dropdown is selected, revealed the "Protected sheets and ranges" dialogue to the far right.

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.

Screenshot of "Protected sheets & ranges" in google sheets. The "Sheet" tab is highlighted. Below that is a dropdown currently reading "Sheet 1". Below that is a checkbox that is checked, with label "Except certain cells". The cell selected below is A1.

Hiding Sheets

Google Sheets can hide sheets like Microsoft Excel. Select the arrow on the sheet name, and then Hide Sheet.

A screenshot of google Sheets. At the bottom is "Sheet1" with the triangle next to the sheet name selected. This reveals a popup menu with the "Hide sheet" option highlighted.