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

Data Analysis

  • How To Create Frequency Distribution in Excel
  • Excel Bar Chart
  • Remove Duplicates Example in Excel
  • How to create Checklist in Excel
  • Example of COUNTIFS with variable table column in Excel

References

  • Offset in Excel
  • How to use Excel MATCH Function
  • How to use Excel LOOKUP Function
  • How to get last column number in range in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation must not contain
  • Prevent invalid data entering in specific cells
  • Data validation must not exist in list
  • Excel Data validation date in next 30 days
  • Excel Data validation no punctuation

Highlight data by quartile in Excel

by

This tutorial shows how to Highlight data by quartile in Excel using the example below;

Formula

=A1>=QUARTILE(data,quart)

Explanation

To highlight cells by quartile, you can apply conditional formatting with a formula that uses the QUARTILE function.

In the example shown, we are using 4 different conditional formatting rules. Each rule highlights a quartile in the range B4:F11 using the QUARTILE function. The formulas look like this:

=B4>QUARTILE(B4:F11,3)
=B4>=QUARTILE(B4:F11,2)
=B4>=QUARTILE(B4:F11,1)
=B4>=QUARTILE(B4:F11,0)

Note that we are highlighting quartiles in a specific order, starting with quartile 4 and ending with quartile 1.

It’s important that the formula be entered relative to the upper left most cell in the data, in this case cell B4.

How this formula works

The QUARTILE function is automatic, and will calculate the 1st quartile with an input of 1, the 2nd quartile with an input of 2, and the 3rd quartile with an input of 3. With an input of 0, quartile returns the minimum value in the data.

The trick in this case is to arrange the conditional formatting rules so that they run in the same direction. The first rule highlights values greater than the 3rd quartile. The second rule highlights values greater than the 2nd quartile, the 3rd rule highlights data greater than the 1st quartile, and the last rule highlights data greater than the minimum value.

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

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

  • AND function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel NOT Function
  • IF with boolean logic in Excel
  • IF, AND, OR and NOT Functions Examples in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • DATE function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate Quarter of Date in Excel
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • Get work hours between dates custom schedule in Excel
  • How to calculate nth day of year in Excel

Grouping

  • Map text to numbers in Excel
  • How to randomly assign data to groups in Excel
  • Categorize text with keywords in Excel
  • If cell contains one of many things in Excel
  • Group numbers with VLOOKUP in Excel

General

  • Currency vs Accounting Format in Excel
  • Convert column number to letter in Excel
  • How to get amount with percentage in Excel
  • How to fill cell ranges with random text values in Excel
  • How to calculate percent change in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning