# Next Steps

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