sodcalculator

10 Tech How-To

6 Small Everyday Jobs in Google Sheets

Guide 10-55431 · Updated · Step by step

This guide walks you through six small but common Google Sheets tasks that save time and reduce errors: freezing headers, creating drop-down lists, applying conditional formatting, using basic formulas, sorting and filtering data, and making simple charts. You’ll need a Google account and a sheet with some data to practice on. No prior spreadsheet experience is required.

What You’ll Need

  • A Google account (free)
  • A Google Sheets document with at least a few rows of data (e.g., a list of expenses or names)

Step-by-Step

  1. Freeze header rows – Click the row number below the row you want to keep visible (usually row 2). Go to View → Freeze → 1 row (or choose “Up to row 2” if you have two header rows). Now when you scroll down, the header stays put.
  2. Create a drop-down list – Select the cell or range where you want the list. Click Data → Data validation → under Criteria choose List from a range and select the range containing your options (e.g., “Paid”, “Pending”). Click Done. A small arrow appears in each cell; click it to pick a value.
  3. Apply conditional formatting – Highlight the cells you want to format (e.g., a “Status” column). Click Format → Conditional formatting. Under Format cells if, choose “Text is exactly” and type “Paid”. Set a formatting style (e.g., green fill). Click Done. Repeat to add rules for other values like “Pending” (yellow) or “Overdue” (red).
  4. Use a basic formula – Click an empty cell where you want the result. Type =SUM( then select the range of numbers (e.g., =SUM(B2:B10)). Press Enter. For an average, use =AVERAGE( instead. For counting non-empty cells, use =COUNTA(A2:A10).
  5. Sort and filter data – Select your data range (including headers). Click Data → Create a filter. Little filter icons appear in the header row. Click one to sort A→Z or Z→A, or to filter by values. To remove a filter, click Data → Remove filter.
  6. Make a simple chart – Highlight the data you want to chart (e.g., months in column A, sales in column B). Click Insert → Chart. Sheets will suggest a chart type; you can change it in the Chart editor on the right (e.g., bar, line, pie). Close the editor to place the chart on the sheet.

Common Mistakes

  • Freezing the wrong row (e.g., freezing row 1 when your header is in row 2).
  • Forgetting to select the entire range before applying data validation, so new rows don’t get the drop-down.
  • Using =SUM on text cells – it returns 0 or an error. Make sure your data is numeric.
  • Sorting only one column, which breaks the relationship between rows. Always select the full data range before sorting.
  • Creating a chart with blank rows or columns, which makes the chart look broken.

Tips

  • Use keyboard shortcuts: Ctrl+Shift+L (Windows/Chrome) or Cmd+Shift+L (Mac) to toggle filters quickly.
  • Name your ranges (e.g., “Expenses”) in Data → Named ranges to make formulas easier to read.
  • For conditional formatting, use “Custom formula is” to apply rules based on other cells (e.g., highlight a row if a checkbox is ticked).
  • When creating a drop-down, you can type the list directly in the validation box (comma-separated) instead of using a range – handy for short lists.
  • To copy formatting, use the paint format tool (paint roller icon) after selecting a formatted cell.

Troubleshooting

  • Drop-down not showing? Check that the cell is not protected (right-click → Protect range) and that data validation is applied to the correct range.
  • Conditional formatting not working? Make sure the rule’s range matches the cells you selected. Also check that the “Format cells if” condition matches your data type (e.g., “Text is exactly” vs “Greater than”).
  • Formula shows an error like #REF! or #VALUE! This usually means the range is invalid or contains text where numbers are expected. Double-click the cell to see the formula and correct the range.
  • Filter hides rows you want to see? Click the filter icon and uncheck “Select all” then check the values you want. To clear all filters, click Data → Remove filter.
  • Chart looks wrong? Right-click the chart and choose Select data range to adjust which cells are included. Also try changing the chart type in the Chart editor.

Frequently Asked Questions

Can I freeze more than one row?

Yes. Go to View → Freeze and choose “Up to row 2”, “Up to row 3”, etc., or select “Custom” to specify a number.

How do I remove a drop-down list?

Select the cells, go to Data → Data validation, and click Remove validation.

Why does my SUM formula show 0?

Your range probably contains text or blank cells. Use =SUM only on numeric cells, or use =SUMIF to sum based on a condition.

Can I sort by multiple columns?

Yes. With filters on, click the first column’s filter icon and sort, then hold Shift and click the second column’s icon to add a secondary sort.

How do I make a chart update automatically when I add data?

If you use a full column range (e.g., A:A) in the chart’s data range, it will include new rows. You may need to refresh the chart by clicking it and pressing Ctrl+R (or Cmd+R).

What’s the difference between a filter and a sort?

A sort rearranges the rows in a specific order. A filter hides rows that don’t meet criteria, without changing the order of the visible rows.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →