Formatting cells that contain the most or least frequent values

You can format cells in the worksheet that contain the most frequent or least frequent values by rank or by percentage.

Most frequent values

Use this option to format the cells that contain the most frequent values by rank.

The unique values in the column are ranked from most frequent to least frequent. The format is applied to cells that contain the X most frequent values, where X is the specified number. If the Xth most frequent value is a tie, then the format is applied to all cells that contain that value.

  1. Do one of the following to open the dialog box:
    • Choose Data > Conditional Formatting > Pareto > Most Frequent Values.
    • Click in the column, then choose Editor > Conditional Formatting > Pareto > Most Frequent Values.
    • Click in the column, right-click, then choose Conditional Formatting > Pareto > Most Frequent Values.
  2. In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
  3. In Number of unique values to format, select a number.
  4. From Style, select the format for the cells that meet the condition.
  5. If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Most frequent percentage

Use this option to format the cells that contain the most frequent values by percentage.

The unique values in the column are ranked from most frequent to least frequent. The format is applied to the cells that contain the most frequent values up to at least X% of the observations, where X is the specified percentage. For example, if you specify 20%, and if the two most frequent unique values account for 18% of the observations and the three most frequent values account for 25% of the observations, then the format is applied to the cells that contain the three most frequent values. If the least frequent value included is a tie, then the format is applied to all cells that contain those values.

  1. Do one of the following to open the dialog box:
    • Choose Data > Conditional Formatting > Pareto > Most Frequent Percentage.
    • Click in the column, then choose Editor > Conditional Formatting > Pareto > Most Frequent Percentage.
    • Click in the column, right-click, then choose Conditional Formatting > Pareto > Most Frequent Percentage.
  2. In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
  3. In Percentage of cells to format, select a percentage.
  4. From Style, select the format for the cells that meet the condition.
  5. If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Least frequent values

Use this option to format the cells that contain the least frequent values by rank.

The unique values in the column are ranked from least frequent to most frequent. The format is applied to cells that contain the X least frequent values, where X is the specified number. If the Xth least frequent value is a tie, then the format is applied to all cells that contain that value.

  1. Do one of the following to open the dialog box:
    • Choose Data > Conditional Formatting > Pareto > Least Frequent Values.
    • Click in the column, then choose Editor > Conditional Formatting > Pareto > Least Frequent Values.
    • Click in the column, right-click, then choose Conditional Formatting > Pareto > Least Frequent Values.
  2. In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
  3. In Number of unique values to format, select a number.
  4. From Style, select the format for the cells that meet the condition.
  5. If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Least frequent percentage

Use this option to format the cells that contain the least frequent values by percentage.

The unique values in the column are ranked from least frequent to most frequent. The format is applied to the cells that contain the least frequent values up to at least X% of the observations, where X is the specified percentage. For example, if you specify 20%, and if the two least frequent unique values account for 18% of the observations and the three least frequent values account for 25% of the observations, then the format is applied to the cells that contain the three least frequent values. If the most frequent value included is a tie, then the format is applied to all cells that contain those values.

  1. Do one of the following to open the dialog box:
    • Choose Data > Conditional Formatting > Pareto > Least Frequent Percentage.
    • Click in the column, then choose Editor > Conditional Formatting > Pareto > Least Frequent Percentage.
    • Click in the column, right-click, then choose Conditional Formatting > Pareto > Least Frequent Percentage.
  2. In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
  3. In Percentage of cells to format, select a percentage.
  4. From Style, select the format for the cells that meet the condition.
  5. If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.
By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy