ThisCalculator

Everyday

Filter by Value: Meaning, Use & Examples

Updated · Standard formula, shown below

Approx. rows matching 10
Approx. match percentage
1.00 %
Result
Value to filter for
100
You entered
Number of rows
1000
You entered

Filtering by value means narrowing a dataset down to only the rows or entries that match a specific number, text, or date you specify. You enter the value you care about, and the tool or code returns only the matching records, hiding everything else. This is a core operation in spreadsheets, databases, and programming, and it is the fastest way to find a needle in a haystack.

Formula

The calculator above estimates how many rows will match a filter when values are uniformly distributed. The formula is:

Approximate matching rows = total rows × (1 / value)

Where:

  • value (v) – the filter value you are looking for (unitless in this example, but in real data it could be a product ID, a category number, or a price point).
  • total rows (c) – the total number of rows in your dataset.
  • Approximate matching rows (p) – the expected number of rows that equal that value, assuming a uniform distribution of possible values.

This is a rough estimate. Real data is rarely uniform, so the actual count will differ.

Worked Example

Using the defaults from the calculator: you have 1,000 rows and you filter for the value 100. The estimate is:

  1. Take the total rows: 1,000.
  2. Divide by the filter value: 1,000 ÷ 100 = 10.
  3. So you expect about 10 rows to match.

The calculator shows 10 rows and 1.00% match. That matches the formula exactly.

Where Filter by Value Is Used

Filtering by value appears in nearly every data tool you use:

  • Spreadsheets (Excel, Google Sheets): Use the filter dropdown on a column header, uncheck “Select All,” then check the specific value you want. Or use “Filter by Selected Cell’s Value” in Excel’s right-click menu.
  • Databases (SQL): The WHERE clause does it: SELECT * FROM orders WHERE status = 'shipped' returns only rows where the status column equals the text “shipped.”
  • Programming (Python, R): In pandas, df[df['age'] == 30] filters to rows where the age column equals 30. In R, filter(df, age == 30).
  • E-commerce and dashboards: Clicking a category or a price range on a shopping site is a filter by value behind the scenes.

How to Filter by Value in Common Tools

The exact steps differ by tool, but the logic is the same everywhere.

In Excel or Google Sheets:

  1. Select your data range.
  2. Click the filter icon (Data → Filter).
  3. Click the arrow in the column header.
  4. Uncheck “Select All,” then check the value you want.
  5. Click OK. Only matching rows remain.

In SQL:

SELECT * FROM table_name WHERE column_name = 'value';

Replace column_name and 'value' with your own. Use single quotes for text, no quotes for numbers.

In Python (pandas):

filtered_df = df[df['column_name'] == value]

This works for numbers and strings. For dates, make sure the column is a datetime type.

Common Mistakes and How to Avoid Them

  • Text vs. number mismatch: Filtering for 100 (number) will not match "100" (text) in some databases. Check the column data type.
  • Exact match only: Filter by value is exact. If you want partial matches, use “contains” or a LIKE query in SQL.
  • Hidden rows: In spreadsheets, filtering hides rows but does not delete them. If you copy visible cells, you may accidentally copy hidden ones too. Use “Go To Special → Visible Cells Only” before copying.
  • Case sensitivity: Some tools (like SQL Server) are case-insensitive by default, others (like PostgreSQL) are case-sensitive. “Shipped” and “shipped” may not both match.

Filter by Value vs. Filter by Condition

Filtering by value is the simplest type of filter: you pick one exact value. Filtering by condition is broader—you define a rule, like “greater than 100” or “contains ‘apple’.”

For example, in a sales table:

  • Filter by value: Show only rows where region = “West.”
  • Filter by condition: Show rows where sales > 5000, or where product name contains “Pro.”

Both are useful. Filter by value is fast for categorical data; filter by condition is better for numeric ranges or text patterns.

Limits of the Formula

The calculator’s estimate assumes every possible value appears equally often. Real datasets have skewed distributions. For example, if you have 1,000 rows of customer ages, filtering for age 30 might return 40 rows if that age is common, or 2 rows if it is rare. The uniform assumption is only a starting point.

Also, the formula only works when the filter value is a positive number. It does not apply to text filters or to ranges (like “between 10 and 20”). For those, you need a different approach.

Frequently Asked Questions

Does filtering by value delete my data?

No. Filtering only hides rows that do not match. The data remains in the sheet or table. To permanently remove rows, you must delete them after filtering, or use a query that deletes matching rows (careful with that).

Can I filter by multiple values at once?

Yes. In spreadsheets, you can check multiple values in the filter dropdown. In SQL, use WHERE column IN ('a', 'b', 'c'). In pandas, use df[df['col'].isin(['a','b'])].

Why does my filter not find a value I can see?

Often it is a data type issue: the cell may contain a number stored as text, or have extra spaces. Try trimming spaces or converting the column to the correct type. Also check for hidden characters like line breaks.

What is the difference between filter and sort?

Filter hides rows that do not meet criteria; sort reorders all rows without hiding any. You can use both together: filter first, then sort the visible rows.

How do I filter by a date value?

In most tools, you select the date in the filter dropdown. In SQL, use WHERE date_column = '2025-01-01'. Make sure the column is a date type, not text, or the comparison may fail.

Related

Formula used
in v | Value to filter for | - | 100 | 0
in c | Number of rows | - | 1000 | 1
out p | Approx. rows matching | - | c * (1 / v) | 0
out pct | Approx. match percentage | % | (1 / v) * 100 | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.