Frequency is the total number of text in a given dataset. Sometimes, you may have a large dataset that you need to check the number of occurrences of a given text. To check the frequency of text in a larger dataset is tedious and may be time-consuming. Several methods and functions can ease counting the frequency of text or numbers in Excel. This article will discuss simple ways of counting text frequency in Excel.
Using the COUNTIF Function
Steps to follow:
1. Open the Excel application.
2. Open the Workbook containing the worksheet containing the texts you need to count.
3. Click on the cell that will hold your results. Then, type the Equal sign and then type the COUNTIF function. That is, =COUNTIF (
4. Select the cells that contain your dataset in Excel. That is, =COUNTIF ( $A$1:$A$10, A1). Finally, hit the Enter Button.
Using the SUM and IF functions
Below are the steps to follow:
1. Open the Excel application.
2. Open the Workbook containing the worksheet containing the texts you need to count.
3. Click on the cell that will hold your results. Then, type the Equal sign and then type the SUM function. That is, =SUM(
4. Then, add the IF function. That is =SUM( IF(
5. Select the cells that contain your dataset in Excel. That is, =SUM( IF(($A$1:$A$10=A1,1,0)). Finally, hit the Enter Button.
Using the Duplicate Tool
Steps to follow:
1. Open the Excel application.
2. Open the Workbook containing the worksheet containing the texts you need to count. Highlight the cells with your dataset that you need to count the frequency of texts.
3. Click the Home tab on the Ribbon, and locate the Styles section. Under this section, click the Conditional Formatting drop-down button.
4. Hover the cursor over the Highlight Cells Rules button, and choose the Duplicate Values button from the menu.
5. From the drop-down menu, choose the Duplicate option; in the Values with Options, select the color you need to use. Finally, hit the OK button.
Using Conditional Formatting Tool
This tool can highlight the text you are checking and thus can ease the process of counting the frequency of a text.
Steps to follow:
1. Open the Excel application.
2. Open the Workbook containing the worksheet containing the texts you need to count. Highlight the cells with your dataset that you need to count the frequency of texts.
3. Click the Home tab on the Ribbon, and locate the Styles section. Under this section, click the Conditional Formatting drop-down button.
4. Choose the New Rule button from the menu to open the Edit Formatting Rule dialogue box.
5. In the Edit Formatting Rule dialogue box, choose the “Use a Formula to determine which cells to format” option in the “Select a Rule Type” section.
6. In the Edit Rule Description section, type the formatting formula you want to use in the workbook. For example, let us use this formula =COUNTIF ( $A$1:$A$10, A2)>1
7. Click the Format button to open the Format Cells dialogue box. In the dialogue box, click the Fill tab and select the color. Finally, click the OK button.