How to Calculate the Mode of Text in Excel

Step-by-Step Guide

1. 1

   Step 1: Have a basic understanding of Excel.

   The following tips will help you navigate the formula. "Range" refers to the cells you would like to include when finding the mode (most common) text.
   
   For example, if the names I am using are in cells A1 to A20, I would type in A1:
   A20 everywhere the word "Range" appears in the formula.
   
   Blank cells cannot be used.
   
   If needed, set aside a column solely for the text you intend to include.
   
   Merged cells cannot be used for this formula.
2. 2

   Step 2: Insert the following function into a blank cell where you would like your end result to appear: =INDEX(Range

   , The most common text will appear. , Next, the MATCH function matches up numerical values with each different string of text.
   
   Thirdly, the COUNTIF function determines how many times each word is used, and the MAX function determines the most commonly occurring word.
   
   There needs to be ONE most commonly occurring word, as two evenly popular words will give you an error message.
3. 3

   Step 3: MATCH(MAX(COUNTIF(Range

4. 4

   Step 4: Range))

5. 5

   Step 5: COUNTIF(Range

6. 6

   Step 6: Range)

7. 7

   Step 7: To make this formula work

8. 8

   Step 8: press Ctrl+Shift+Enter once you have entered in the above formula with your values in for the "Range".

9. 9

   Step 9: The way the formula works is the INDEX function picks out how many different words are used.

Detailed Guide

About the Author