Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Use Data Form to input, edit and delete records in Excel
  • Working With Tables in Excel
  • How to calculate average last N values in a table in Excel
  • How To Sort One Column or Multiple Columns in Excel

References

  • Excel Advanced Lookup using Index and Match Functions
  • How to get address of named range in Excel
  • Merge tables with VLOOKUP in Excel
  • Perform case-sensitive Lookup in Excel
  • How to get last row in mixed data with blanks in Excel

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation exists in list
  • Excel Data validation require unique number
  • Data validation must not exist in list
  • Excel Data validation must begin with

Category: Grouping

Group numbers at uneven intervals in Excel

by

This tutorial shows how to Group numbers at uneven intervals in Excel using the example below; Formula =LOOKUP(value,intervals,groups) Explanation To group numbers into intervals of unequal size, you can use the LOOKUP function. In the example shown, the LOOKUP function is used to group people by age into at intervals of unequal size. The formula …

Continue Reading

Group times into 3 hour buckets in Excel

by

This tutorial shows how to Group times into 3 hour buckets in Excel using the example below; Formula =FLOOR(time,”3:00″) Explanation If you need to group times into buckets (i.e. group by 2 hours, group by 3 hours, etc.) you can do so with a rounding function called FLOOR. In the example shown, we have a …

Continue Reading

Group numbers with VLOOKUP in Excel

by

This tutorial shows how to Group numbers with VLOOKUP in Excel using the example below; Formula =VLOOKUP(value,group_table,column,TRUE) Explanation If you need to group by number, you can use the VLOOKUP function with a custom grouping table. This allows you to make completely custom or arbitrary groups. In the example shown, the formula in F7 is: =VLOOKUP(D5,age_table,2,TRUE) How …

Continue Reading

Categorize text with keywords in Excel

by

This tutorial shows how to Categorize text with keywords in Excel using the example below; Formula {=INDEX(categories,MATCH(TRUE,ISNUMBER (SEARCH(keywords,text)),0))} Explanation To categorize text using keywords with a “contains” match, you can use the SEARCH function, with help from INDEX and MATCH. In the example shown, the formula in C5 is: {=INDEX(categories,MATCH(TRUE, ISNUMBER(SEARCH(keywords,B5)),0))} where “keywords” is the named …

Continue Reading

Group arbitrary text values in Excel

by

This tutorial shows how to Group arbitrary text values in Excel using the example below; Formula =VLOOKUP(value,table,column,0) Explanation IF you want to group or classify data based on arbitrary text values, you can use VLOOKUP instead of a long series of nested IF statements. The trick is to build a custom table that will map …

Continue Reading

Map inputs to arbitrary values in Excel

by

This tutorial shows how to Map inputs to arbitrary values in Excel using the example below; Formula =VLOOKUP(input,map_table,column,0) If you need to map or translate inputs to arbitrary values, you can use the VLOOKUP function. In the example, we need to map the numbers 1-6 as follows: Input Output 1 10 2 81 3 17 …

Continue Reading

If cell contains one of many things in Excel

by

This tutorial shows how to calculate If cell contains one of many things in Excel using the example below; Formula {=INDEX(results,MATCH(TRUE,ISNUMBER (SEARCH(things,A1)),0))} Explanation To test a cell for one of several things, and return a custom result for the first match found, you can use an INDEX / MATCH formula based on the SEARCH function. In the …

Continue Reading

Map text to numbers in Excel

by

This tutorial shows how to Map text to numbers in Excel using the example below; Formula =VLOOKUP(text,lookup_table,2,0) Explanation To map or translate text inputs to arbitrary numeric values, you can use the VLOOKUP function with a simple table. In the example, we need to map five text values (statuses) to numeric status codes as follows: …

Continue Reading

Running count group by n size in Excel

by

This tutorial shows how to Running count group by n size in Excel using the example below; Formula =CEILING(COUNTA(expanding_range)/size,1) > Explanation To creating a running count of groups of a variable size, you can use the COUNTA and CEILING function. In the example shown, C5 contains this formula: =CEILING(COUNTA($B$5:B5)/size,1) where “size” is the named range F4. How …

Continue Reading

Group times into unequal buckets in Excel

by

This tutorial shows how to Group times into unequal buckets in Excel using the example below; Formula =VLOOKUP(time,bucket_table,column,TRUE) Explanation This formula is a great example of how you can use VLOOKUP to group data in completely custom ways. If you need to group times into buckets, but the buckets are not equal (i.e. 12 AM-7 …

Continue Reading

How to randomly assign people to groups in Excel

by

To randomly assign people to groups or teams of a specific size, you can use a helper column with a value generated by the RAND function, together with a formula based on the RANK and ROUNDUP functions. Formula =ROUNDUP(RANK(A1,randoms)/size,0) Explanation In the example shown, the formula in D5 is: =ROUNDUP(RANK(C5,randoms)/size,0) which returns a group number …

Continue Reading

How to randomly assign data to groups in Excel

by

To randomly people (or anything) to groups you can use the RANDBETWEEN function with the CHOOSE function. Formula =CHOOSE(RANDBETWEEN(1,3),”Group A”,”Group B”,”Group B”) Explanation In the example shown, the formula in F3 is: =CHOOSE(RANDBETWEEN(1,3),”A”,”B”,”C”) When copied down the column, this formula will generate a random group (A, B, or C) for each person in the list. …

Continue Reading

Calculate conditional mode with criteria in Excel

by

To calculate a conditional mode with one or more criteria you can use an array formula based on the IF and MODE functions. Note: this is an array formula and must be entered with control + shift + enter. Formula {=MODE(IF(criteria,data))} Explanation In the example shown, the formula in F5 is: {=MODE(IF(group=E5,data))} where “group” is the named range B5:B14, …

Continue Reading

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • Not Equal To ‘<>‘ operator in Excel
  • How to use Excel AND Function
  • How to use Excel XOR Function

Date Time

  • How to enter Today’s Date or Static Date and Time in Excel
  • How to calculate next anniversary date or birthday in Excel
  • Convert date string to date time in Excel
  • How to join date and text together in Excel
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group numbers at uneven intervals in Excel
  • Calculate conditional mode with criteria in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign data to groups in Excel
  • Map text to numbers in Excel

General

  • How to fill cell ranges with random text values in Excel
  • How to set or clear a print area in Excel Worksheet
  • Freeze and Unfreeze Panes in Excel
  • How to get original price from percentage discount in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning