IT Basics

Conditional Formatting in Excel: 6 Rules on One Stock Sheet (2026)

A worked example of conditional formatting in Excel: build six rules on one stock sheet to flag low stock, near-expiry dates, duplicate codes and top sellers, then learn how rule order, formula references and Manage Rules decide what you see.

Conditional formatting in Excel featured image with a color-coded stock sheet

Quick answer

Select your data, open Home > Conditional Formatting and pick a rule. Use Highlight Cells for fixed limits, Top/Bottom for best sellers, Duplicate Values for repeats, data bars or color scales for size, and a formula such as =$C2

Key takeaways

  • • Conditional formatting changes a cell's look only when its value meets a condition, and it updates automatically.
  • • A formula rule like =$C2
Table of contents 13 sections
  1. The Example: A Small Wholesaler's Stock Sheet
  2. What Is Conditional Formatting in Excel?
  3. Rule 1: Highlight Cells Below a Number
  4. Rule 2: Color the Whole Row With a Formula
  5. Rule 3: Flag Stock That Expires Within 30 Days
  6. How Do You Highlight Duplicates in Excel?
  7. Rule 5: Add Data Bars to Monthly Sales
  8. Rule 6: Spot the Top 10 Sellers
  9. Which Rule Wins When Two Rules Clash?
  10. Copy, Edit and Clear Rules Safely
  11. Mistakes That Make Conditional Formatting Go Wrong
  12. Learn Excel Properly in PSTA's Office Automation Course
  13. Build This Sheet Tonight

Quick self-test before you read on. Your shop's stock sheet has 300 rows. Can you spot, in under ten seconds, which items are below their reorder level, which medicines expire this month and which product code was typed twice? If the honest answer is no, conditional formatting in Excel is the fix, and it takes about fifteen minutes to set up once.

This post walks through one realistic example of conditional formatting in Excel from start to finish. You will build six rules on a single sheet and learn why each one works. Along the way you will see:

  • The built-in rule families and when each one fits
  • How to color a whole row with a formula rule
  • How to flag duplicates and near-expiry dates automatically
  • How rule order decides which color wins
  • The mistakes that make formatting slow or wrong

The Example: A Small Wholesaler's Stock Sheet

This is an example business, not a real client. Picture a small medicine and general goods wholesaler in Peshawar. The owner keeps stock in one Excel sheet with these columns:

Column Heading Sample Value
A Item code PK-1042
B Item name Paracetamol 500 mg (box)
C Quantity in stock 8
D Reorder level 20
E Expiry date 30-Dec-2026
F Units sold this month 146

Data starts in row 2 and runs to row 300. The owner wants the sheet to answer four questions at a glance: what to reorder, what expires soon, which codes are duplicated and which items sell best. Every rule below answers one of them.

What Is Conditional Formatting in Excel?

Conditional formatting in Excel changes how a cell looks only when its value meets a condition you set. The formatting updates by itself whenever the data changes, so you never recolor cells by hand. According to Microsoft's conditional formatting guide, the main families are:

  • Highlight Cells Rules for comparisons such as greater than, between, text that contains, dates and blanks
  • Top/Bottom Rules for the top or bottom items, percentages, or values above and below average
  • Duplicate or unique values for spotting repeats
  • Data bars, where bar length shows each value's size
  • Color scales, two- or three-color gradients from low to high
  • Icon sets of three to five icons that sort values by thresholds
  • Formula rules for anything the built-in options can't express

All of them live under Home, then Conditional Formatting, in the Styles group. Microsoft's own quick reference card shows the basic flow: select the range, open the menu, pick a rule.

Conditional formatting in Excel rule types: highlight cells, top/bottom, duplicates, data bars, color scales, icon sets and formula
The seven rule families under Home, Conditional Formatting.

Rule 1: Highlight Cells Below a Number

Start simple, because the easiest conditional formatting in Excel needs no formula at all. Any quantity under 10 should turn red, because the shop is nearly out.

  1. Select C2:C300.
  2. Go to Home, Conditional Formatting, Highlight Cells Rules, Less Than.
  3. Type 10 in the box.
  4. Choose Light Red Fill with Dark Red Text and click OK.

That's the fastest way to highlight cells in Excel. The weakness is the fixed number. Ten boxes might be plenty for a slow item and far too few for paracetamol. Rule 2 fixes that.

Rule 2: Color the Whole Row With a Formula

The owner really wants each row compared with its own reorder level in column D. That needs a conditional formatting formula.

  1. Select A2:F300, starting from A2 so A2 is the active cell.
  2. Go to Conditional Formatting, New Rule.
  3. In the rule type list, pick the option that begins "Use a formula."
  4. Type =$C2<=$D2
  5. Click Format, choose a light orange fill and click OK twice.

Microsoft's page on using a formula to apply conditional formatting shows the same idea: the formula starts with an equal sign, and cells are formatted when its test is met. The dollar signs matter here. $C locks the column, so every cell in the row checks column C. The row number 2 has no dollar sign, so it moves down as Excel checks rows 3, 4, 5 and onward.

Get the dollar signs wrong and you'll see odd stripes of color. If only column A turns orange, or the colors appear one row off, check which cell was active when you typed the formula.

Rule 3: Flag Stock That Expires Within 30 Days

Dates are where these rules save real money. Medicines and packaged food can't sit past their expiry date. Select E2:E300 and add another formula rule:

=AND($E2>=TODAY(),$E2<=TODAY()+30)

Give it a yellow fill. TODAY() returns the current date each time the file recalculates, so the rule stays current without edits. Add a second rule, =$E2<TODAY(), with a dark red fill for anything already expired. A blank expiry cell would count as a very old date, so wrap the test as =AND($E2<>"",$E2<TODAY()) if some rows have no date.

How Do You Highlight Duplicates in Excel?

Select A2:A300 and choose Highlight Cells Rules, Duplicate Values. Excel colors every code that appears more than once. That's usually enough to catch a typing slip where two products share one code.

For more control, use COUNTIF as a formula rule. Microsoft's own formula example uses this pattern, and the COUNTIF function page explains how it counts matches in a range:

=COUNTIF($A$2:$A$300,$A2)>1

Apply it to A2:F300 and the entire duplicated row lights up, which is easier to see than one small cell. The range has dollar signs on both column and row because every check should look at the same list.

Rule 5: Add Data Bars to Monthly Sales

Numbers alone hide patterns. Select F2:F300 and choose Conditional Formatting, Data Bars, then any gradient fill. Each cell now shows a bar whose length matches its sales. Fast movers jump out without sorting.

Data bars in Excel work best on one column of similar numbers. On a column that mixes boxes, cartons and loose units, the bars compare things that aren't comparable, so keep the unit consistent first.

If you prefer a heat-map look, try color scales in Excel instead. A three-color scale shades low sales red, middle values yellow and high sales green. Microsoft notes that the midpoint of a three-color scale defaults to the 50th percentile.

Rule 6: Spot the Top 10 Sellers

Select F2:F300 again and choose Top/Bottom Rules, Top 10 Items. You can change 10 to any number from 1 to 1,000, or use Top 10% instead. Pick a bold green font so it doesn't clash with the data bars already in the column.

Now the sheet answers all four questions, and you haven't recolored a single cell by hand. That is the real payoff of conditional formatting in Excel. Orange rows need reordering, yellow and red dates need attention, duplicate codes stand out, and the best sellers carry both a long bar and green text.

Flowchart for choosing a conditional formatting rule in Excel
Work down the questions to pick the right rule type.

Which Rule Wins When Two Rules Clash?

Open Home, Conditional Formatting, Manage Rules, and set "Show formatting rules for" to This Worksheet. You'll see every rule for that sheet in one list. Microsoft explains that rules are evaluated from top to bottom, and a new rule is added at the top by default.

When two rules don't conflict, both apply. A cell can have an orange fill from one rule and green text from another. When they do conflict, such as two different fill colors, the rule higher in the list wins. Use the Move Up and Move Down arrows to set the order you want.

There's also a Stop If True box beside some rules. Microsoft says it exists mainly for compatibility with older Excel versions, and it isn't available for data bars, color scales or icon sets. For a sheet like this, ordering the rules is enough.

Order Rule Applies To Format
1 =AND($E2<>"",$E2<TODAY()) E2:E300 Dark red fill
2 =AND($E2>=TODAY(),$E2<=TODAY()+30) E2:E300 Yellow fill
3 =COUNTIF($A$2:$A$300,$A2)>1 A2:F300 Purple fill
4 =$C2<=$D2 A2:F300 Orange fill
5 Top 10 Items F2:F300 Bold green text
6 Data bars F2:F300 Gradient bar

Expired stock sits at the top because it is the most urgent. Duplicates come next because they make every other number unreliable.

Copy, Edit and Clear Rules Safely

To reuse rules on next month's sheet, select a formatted cell, click Format Painter and brush it over the new range. Press Esc to stop painting. Microsoft also notes that conditional formatting overrides manual formatting, and removing a rule leaves the manual format underneath.

To start again, go to Conditional Formatting, Clear Rules, and choose either Selected Cells or Entire Sheet. To find every formatted cell on a large sheet, use Go To Special and pick Conditional formats.

The Quick Analysis button, or Ctrl+Q, offers a short menu of formats with a live preview when you select data. It's a handy shortcut for data bars and color scales while you are learning.

Do and do not habits for conditional formatting in Excel
Small habits that keep conditional formatting fast and readable.

Mistakes That Make Conditional Formatting Go Wrong

Most problems with conditional formatting in Excel come from a handful of habits. Check these first when colors look wrong or the file feels slow.

  • Selecting whole columns. Rules on C:C check over a million rows. Select only the data range, or format the range as a Table so the rules grow with it.
  • Typing the formula for the wrong active cell. The formula must match the top-left cell of your selection.
  • Too many colors. More than four or five meanings on one sheet turns into noise. Keep a small legend above the header row.
  • Stacking duplicate rules. Copying and pasting cells can multiply rules. Open Manage Rules every few weeks and delete repeats.
  • Formulas that return errors. Microsoft notes that a rule whose formula returns an error doesn't format the cell. Wrap risky formulas in IFERROR.

If you're preparing for a computer operator or office assistant test, these rules pair well with the Excel IF function, VLOOKUP vs XLOOKUP and pivot tables in Excel. Our list of Excel interview questions also covers how employers test these skills.

Learn Excel Properly in PSTA's Office Automation Course

Conditional formatting in Excel is one small part of a bigger skill set. Excel work, from tables and formulas to formatting and charts, is one block of the Office Automation course at PSTA (Professional Skills & Tech Academy). The same 12-week beginner program also covers Word, PowerPoint, file management and English and Urdu typing, and one of its projects is a monthly sales sheet with formulas and a chart.

The course page lists the fee as Rs. 12,000, which can be split into two installments. Classes run at the Abbottabad campus, live online for learners elsewhere in Pakistan, or as a hybrid mix, and a new batch opens every month. Compare options on the courses page or apply through admission. If you want a longer route, the one-year DIT diploma includes office skills alongside programming basics.

Build This Sheet Tonight

Reading about conditional formatting in Excel only goes so far. Open Excel, type ten rows of your own stock, fees or expenses, and add Rule 2 and Rule 3 first. Change a few numbers and dates and watch the colors move. Once that works, add the other four rules and check their order in Manage Rules. Stuck on a formula? Send a screenshot on WhatsApp at +92 314 5424399.

Frequently asked questions

How do I apply conditional formatting to an entire row in Excel?

Select the whole data range starting from the first data row, then choose New Rule and the formula-based rule type. Write the formula for the top-left cell and lock the column with a dollar sign, for example =$C2

Why is my conditional formatting not working?

The most common causes are a formula written for the wrong active cell, missing or extra dollar signs, numbers stored as text, or another rule higher in Manage Rules overriding yours. Open Manage Rules, check the Applies To range and the order, and test the formula in a spare cell to see whether it returns TRUE.

Can conditional formatting use another sheet or cell?

Yes, a formula rule can compare a cell with another cell on the same sheet, such as a target in a fixed cell like $H$1. Microsoft notes that rules cannot use external references to a different workbook. If the value lives elsewhere, bring it into the current workbook with a simple link first.

How do I remove conditional formatting from cells?

Select the cells, then go to Home, Conditional Formatting, Clear Rules, Clear Rules from Selected Cells. To remove everything from the sheet, choose Clear Rules from Entire Sheet instead. Manual formatting you applied yourself stays in place, because conditional formatting only sits on top of it.

Does conditional formatting slow down Excel?

It can on large files, especially when rules cover entire columns or the same rule has been copied many times by pasting. Limit each rule to the actual data range, convert the data to a Table if it grows, and clean up repeated rules in Manage Rules. Volatile functions such as TODAY() recalculate often, so use them only where needed.

Is conditional formatting asked in computer operator tests in Pakistan?

Many office and computer operator tests include practical Excel tasks, and formatting data by condition is a common one alongside formulas, sorting and charts. The exact syllabus depends on the employer or testing body, so read the advertised syllabus. Practising rules like the six in this guide prepares you for most practical questions.

What is the difference between data bars and color scales?

Data bars draw a bar inside each cell, so a longer bar means a bigger value and you can compare sizes at a glance. Color scales shade cells with a gradient, such as red for low and green for high, which works like a heat map. Use bars for amounts and scales for spotting high and low zones.

Sources & references

  1. Use conditional formatting to highlight information in Excel (Microsoft Support) support.microsoft.com
  2. Use a formula to apply conditional formatting (Microsoft Support) support.office.com
  3. Quick Reference Guide: Apply conditional formatting (Microsoft) cdn-dynmedia-1.microsoft.com
  4. COUNTIF function (Microsoft Support) support.microsoft.com

Last reviewed & updated on by Umar Qureshi. Written by the PSTA team in Abbottabad, Pakistan.

Share this article
Umar Qureshi
About the author

Umar Qureshi

Computer Science Teacher & Full-Stack Developer at Professional Skills & Tech Academy

Umar Qureshi, known to his students as Sir Umar Qureshi, is a computer science teacher, full-stack developer and SaaS builder from Abbottabad, Pakistan, with 10+ years in the classroom. At PSTA he teaches web development, Python and data science, AI and automation, Power BI, digital marketing and office automation, on campus and in live online classes. He built two live platforms end to end: the PSTA website and learning platform (admissions, student login and verifiable certificates) and OnePlace (oneplace.com.pk), a multi-vendor marketplace and e-commerce SaaS for Pakistani sellers. He teaches the tools he uses to build real products, so every lesson ends with a project students can show. Official website: https://sirumarqureshi.com

Certified Office Automation Course: Master MS Office & Computer Basics course at PSTA
Recommended course

Certified Office Automation Course: Master MS Office & Computer Basics

Turn this guide into a career: hands-on training with real projects, online across Pakistan or on-campus in Abbottabad · 12 Weeks.

Rs. 12,000 Rs. 15,000

Comments

No comments yet — ask a question or share your experience.

Leave a comment

Comments are moderated. Please be respectful — no links to spam.

Chat with us