⬇ Download PDF

Unit 2 · Topic 07 · Data Analysis and Representation (Google Sheets)

Filtering data by Values

Hook

Anaya's finger was already on the Delete key.

Kabir grabbed her wrist. "Wait — should we? Those are the library's only records."

Pixel's fan made a long, worried noise.

The Story

The school library had given Class 4 a job: find out which books belonged to Class 4's reading shelf. The list sat in Google Sheets. Six books, and beside each one the class it belonged to and how many copies there were.

Panchatantra, Class 4. Malgudi Days, Class 5. Rainbow Rhymes, Class 3. Space Facts, Class 4. Cricket Stars, Class 5. Ant Diary, Class 4.

"Easy," said Anaya. "I'll delete every row that isn't Class 4."

That was when Kabir grabbed her wrist.

Ms. Rao heard the fuss. "Kabir is right. Delete those rows and the librarian loses her Class 3 and Class 5 books forever. You do not want to remove them. You want to stop looking at them."

She showed them the Data menu, then Create a filter. At once a tiny funnel icon appeared in every heading cell.

Anaya clicked the funnel in the Class column. A panel opened with a section called Filter by values. Inside was a list of every value in that column — 3, 4 and 5 — each with a tick box, all ticked.

She unticked 3. She unticked 5. She clicked OK.

Three rows vanished. Panchatantra, Space Facts and Ant Diary stayed.

"They're deleted!" said Anaya, going pale.

"Look at the row numbers," said Ms. Rao.

They read 2, then 5, then 7. Three numbers were missing.

"The rows are still there," said Kabir slowly. "They're just hidden."

Anaya ticked 3 and 5 again. All six books came back, exactly as before.

The library sheet with a filter on the Class column. Only the value 4 is ticked in the Filter by values panel, so three rows are hidden and the row numbers read 2, 5 and 7. Anaya's library sheet with the Class filter on #BookClassCopies 2Panchatantra46 5Space Facts45 7Ant Diary42 hidden — Malgudi Days · Class 5 · 3 copies hidden — Rainbow Rhymes · Class 3 · 8 copies hidden — Cricket Stars · Class 5 · 4 copies Filter by values 3 — unticked, so hidden 4 — ticked, so shown 5 — unticked, so hidden Row numbers skip: 2, 5, 7 The gaps prove the rows are hidden, not deleted.
One tick box changed, three rows hidden, nothing lost.

So What Just Happened?

A filter hides rows. It does not delete them.

Start with Data ▸ Create a filter. A funnel icon appears in each header cell. Click the funnel above the column you care about and use Filter by values: every different value in that column is listed with a tick box. A ticked value is shown; an unticked value is hidden.

The proof that nothing was deleted sits in the row numbers down the left side. When rows are hidden the numbers skip — you might see 2, 5, 7. A skipped number means a row is still there, out of sight. Tick the values again, or turn the filter off, and every row comes back. Deleting is permanent. Filtering is just a pair of sunglasses.

Two paths from the same six-book list: filtering hides three rows and can be undone by re-ticking the values, while deleting removes those three rows for ever. 6 books 28 copies in the sheet FILTER ✓ DELETE ✗ 3 rows hidden from view 3 books visible, 13 copies Row numbers read 2, 5, 7 3 rows gone from the sheet 3 books left, 13 copies Row numbers read 2, 3, 4 Tick 3 and 5 again All 6 books return. 28 copies again. Nothing to tick. The books are gone for good.
Both paths show three books, but only one path can be undone.

Remember This

  • Data ▸ Create a filter puts a funnel icon in every header cell.
  • Click a funnel, then use Filter by values to tick or untick values.
  • Ticked means shown. Unticked means hidden.
  • Filtering hides rows. Deleting destroys them. They are not the same.
  • Skipping row numbers, like 2, 5, 7, tell you rows are hidden.
A four-step cheat card of the filter menu path, with a reminder that ticked means shown, unticked means hidden, and skipping row numbers mean rows are hidden. Filter cheat card — four steps 1. Data ▸Create a filter 2. Click the funnelin the header 3. Filter by valuestick / untick 4. Read the rowsthat stay ☑ ticked = shown ☐ unticked = hidden Nothing is ever deleted. Clue: row numbers 2, 5, 7 Gaps mean rows are hidden. No gaps means they were deleted.
The path, the tick boxes, and the row-number clue.

Try It Yourself

Make a sheet of eight snacks with a column called Taste, filled in as sweet, salty or sour.

Turn on Data ▸ Create a filter, click the Taste funnel, and leave only "sweet" ticked.

Write down which row numbers still show, then tick everything again and check all eight rows return.

Word Bank

Filter
A tool that shows only the rows you choose and hides the rest.
Funnel icon
The small filter button that appears in each header cell.
Filter by values
The tick-box list of every value found in that column.
Hidden row
A row that is still in the sheet but not on the screen.
Delete
To remove data for good — a filter never does this.

Questions

Loading questions…