Skip to main content

How to highlight cells above/below average in Excel?

Author: Sun Last Modified: 2025-04-15

Analyzing data in Excel often involves identifying trends and outliers. One effective way to gain insights is by highlighting cells that are above or below the average value in a dataset. This visual cue makes it easier to spot high or low performers at a glance, saving you time and effort. This guide will walk you through the steps to highlight above or below average values.

Highlight cells above/below average with Conditional Formatting

Highlight cells above/below average with Kutools AI Aide


Highlight cells above/below average with Conditional Formatting

In Excel, you can use the Conditional Formatting function to quickly highlight cells above/below average in a specified range.

1. Select the data range you need to highlight the cells above its average, and click "Home" > "Conditional Formatting" >" Top/Bottom Rules" > "Above Average". See screenshot:

select Above Average option from Conditional Formatting

2. Then in the popped out dialog, you can select the color you like to highlight the cells above average from the list box of for the selected range with. See screenshot:

select the color to highlight the cells

Tip: You also can select "Custom Format" to open "Format Cells" dialog to specify other color or other formatting for the cells above average.

custom the color you need

3. Click "OK" to apply the "Conditional Formatting" function. Then you can see the cells above average in the selected range are highlighted. See screenshot:

the cells above average in the selected range are highlighted

Note: If you want to highlight cells below average, you can click "Home" > "Conditional Formatting" > "Top/Bottom Rules" > "Below Average".

select Below Average option from Conditional Formatting

Tip: If the data in the range is changed, the highlighting will be changed, too.


Highlight cells above/below average with Kutools AI Aide

Struggling to spot above- or below-average values in large datasets? Let "Kutools AI Aide" do the heavy lifting! With just a few clicks, you can instantly highlight cells above or below average, making your data analysis faster, easier, and more accurate.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

Click "Kutools" > "AI Aide" to open the "Kutools AI Aide" pane, then do the following operations:

  1. Select the range that you want to highlight the cells which above or below average.
  2. Then, type the requirement in the chat box: such as:
    To highlight the cells above average: "Highlight cells above average in the selected range with a light blue color"
    To highlight the cells below average: "Highlight cells below average in the selected range with a light blue color"
  3. And then, press "Enter" key or click the "Send" button. Kutools AI will analyze the question, after finishing, please click "Execute" button to apply the result.

Highlighting cells above or below average in Excel is a powerful way to gain quick insights and make your data more actionable. Whether you use Conditional Formatting feature or Kutools for Excel, these techniques help you identify trends and outliers with ease. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.


Relative Articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!