Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to categorize values into buckets in Excel and Google Sheets
Topic:IF, IFS & nested conditionsExcel & Google Sheets
=IF(B2<50,"Low",IF(B2<100,"Medium","High"))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Item | Value | Bucket |
| A | 30 | |
| B | 75 | |
| C | 110 | |
| D | 48 | |
| E | 200 |
=IF(B2<50,"Low",IF(B2<100,"Medium","High"))→Low
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Decide on your bucket thresholds (e.g., <50 = Low, 50–99 = Medium, 100+ = High).
- 2In a category column type =IF(B2<50,"Low",IF(B2<100,"Medium","High")) and adjust thresholds to match your data.
- 3Copy down all rows. For many buckets, build a small lookup table and use VLOOKUP with approximate match instead to keep the formula short.
Tips
- For five or more buckets, a VLOOKUP lookup table is far easier to maintain than deeply nested IFs — put the lower bounds and labels in a two-column table and use =VLOOKUP(B2,$E$2:$F$7,2,TRUE).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if some values are negative and some positive?
Just add a condition at the start: =IF(B2<0,"Negative",IF(B2<50,"Low",IF(B2<100,"Medium","High"))).
Can I bucket text values instead of numbers?
Yes — use IFS or nested IF with exact text comparisons or a VLOOKUP with exact match (FALSE).
More on IF, IFS & nested conditions
See all →How-to
How to hide #N/A and other formula errors
Replace #N/A, #DIV/0!, and other errors with a blank or a friendly message using IFERROR or IFNA. Works in Excel and Google Sheets.
Error fixFix Nested IF
Nested IF formulas not working in Excel and Google Sheets — fix bracket mismatches, wrong evaluation order, overlapping conditions, and the 64-nesting limit.
How-toHow to sum if a cell contains specific text
Use SUMIF with wildcard criteria (*text*) to sum values in a column whenever a related column contains a specific substring.
How-toHow to write a nested IF formula
Chain multiple IF functions inside each other to test several conditions and return different results for each scenario.
How-toHow to write an IF with multiple conditions
Combine IF with AND or OR to test multiple conditions simultaneously and return a result based on whether all or any are true.
How-toHow to combine IF and AND
Use IF with AND to return a value only when all specified conditions are simultaneously true in Excel or Google Sheets.