Excel’s conditional-formatting arrows can show whether values are relatively high or low, but they do not automatically calculate change over time. To show an increase or decrease, first calculate the difference between periods in a helper column, then apply an arrow icon set to those results and set its thresholds to match your meaning of positive, zero, and negative.
What conditional-formatting arrows show
Excel icon sets add a visual symbol to each cell based on rules. The built-in arrow sets commonly use upward, sideways, and downward arrows, with colors that help distinguish the categories. By default, Excel divides the selected values according to thresholds—often based on percentages of the range—rather than interpreting them as change. As a result, an upward arrow can simply mean “higher than other cells,” not “increased since last month.”
This distinction matters when you are reporting performance. If a row contains this month’s sales, an arrow on the sales value alone does not know last month’s sales. To show direction of change, compare the two values first. The arrow then communicates the result of that comparison. It is a display rule, not a calculation or a forecast.
Calculate the change before adding arrows
A helper column makes the logic visible and easy to check. For example, place an item’s previous-period value in B2 and its current-period value in C2, then put the change in D2. With that layout, enter =C2-B2 in D2 and fill it down. If sales moved from 120 to 135, the result is 15; if they moved from 120 to 110, the result is -10. A positive result means an increase, a negative result means a decrease, and zero means no change.
Keep the original values alongside the change where practical. That lets readers see both the comparison and its source numbers, and it makes mistakes in the period pairing easier to spot. Use consistent units in both columns: subtracting a percentage from a dollar amount, or comparing different time periods, will not produce a meaningful change.
Apply an arrow icon set in Excel
In Excel for Microsoft 365 on Windows, apply an icon set to the helper values as follows:
- Select the change cells, such as D2:D20. Exclude the heading and, if possible, blank rows.
- Choose Home > Conditional Formatting > Icon Sets, then select a three-arrow style.
- To make the arrows represent positive, neutral, and negative changes, choose Home > Conditional Formatting > Manage Rules. Select the rule and choose Edit Rule.
- Set the rule’s threshold Type to Number rather than Percent or Percentile. Set the upper threshold to the smallest change you want to count as a meaningful increase and the lower threshold to zero. The top icon applies at or above the upper threshold, the middle icon applies below that and at or above zero, and the bottom icon applies below zero.
- Check the icon order and preview. If the symbols do not match your intended direction, use Reverse Icon Order or choose a different icon style, then confirm the rule.
For whole-number changes, thresholds of 1 and 0 give an up arrow for values of 1 or more, a middle arrow for zero, and a down arrow for negative values. For money recorded to cents, you might instead use 0.01 and 0 so that a one-cent increase is treated as positive. Choose thresholds based on what counts as meaningful in your data, rather than copying an example blindly.
Choose thresholds that fit the data
Icon-set rules have multiple threshold choices, and the threshold type changes their meaning. A Number threshold compares each cell with a literal value. A Percent threshold uses a percentage of the distance through the selected range; it is not the same as a percentage change. Percentile thresholds compare values with a rank in the distribution. These relative approaches can be useful for spotting the strongest and weakest results, but they do not reliably mark zero as neutral.
For a direction-of-change report, numeric boundaries are usually clearest. Decide how to handle tiny movements before you set the rule. If a temperature reading changes by 0.001 degrees because of precision, that may not deserve a different arrow. A neutral band can keep insignificant noise from appearing important: for example, a report might show a sideways arrow for changes from -0.5 through 0.5, with arrows up and down outside that range. Use thresholds consistent with the data’s precision and the decisions readers need to make.
Remember that the icon set classifies each cell’s displayed numeric value; it does not know the context. A decrease in expenses may be favorable, while a decrease in revenue may be unfavorable. If the arrow is intended to mean “good” or “bad” rather than simply “up” or “down,” define that logic deliberately and label the report so readers understand it.
Show percentage change instead of absolute change
Absolute change answers “how many units did the value move?” Percentage change answers “how large was the movement compared with the starting value?” Those are different measures. If a value increases from 100 to 110, its absolute change is 10 and its percentage change is 10%. An increase from 1,000 to 1,010 is also 10 units, but only 1%.
With previous values in B2 and current values in C2, enter =(C2-B2)/B2 in D2 to calculate the percentage change, then format D2 as a percentage and fill the formula down. In the examples above, the results are 10% and 1%, respectively. Do not use this formula when the previous value can be zero without considering how you want to handle that case: division by zero produces an error, and percentage change from a zero baseline is generally not defined. In that situation, report the absolute change or create a separate, clearly explained rule for zero baselines.
If percentages are used as the change measure, icon thresholds should still use Number when you want fixed boundaries such as 0% and 2%. Excel stores 2% as 0.02, so a numeric threshold for two percent is 0.02. Check the threshold carefully; typing 2 as a number would mean 200%, not 2%.
Make the arrows easier to read
Arrows are compact, but they should reinforce rather than replace useful information. Keep the numeric change visible if readers need to know its size. In Edit Formatting Rule, the Show Icon Only option hides the cell value; use it only when the exact number is available elsewhere or is not needed. A column heading such as “Change vs. prior month” is more informative than “Status.”
- Use one consistent period comparison throughout a column; avoid mixing week-over-week and year-over-year changes.
- Use a clear number format, such as currency or percentage, for the helper values.
- Choose icons with adequate contrast and do not rely on color alone to convey meaning.
- Keep the rule’s Applies to range aligned with the data. If new rows are added later, check whether the range expands as intended.
Excel for Microsoft 365 on Mac also provides conditional-formatting icon sets from the Home tab. The names and layout of rule-editing controls can differ slightly from Windows. Excel for the web can display conditional formatting and offers icon-set options, but some detailed rule-editing controls may be more limited than in the desktop application. If you cannot set the exact thresholds in your current interface, open the workbook in desktop Excel.
Common problems and how to fix them
Every value has an arrow, but the direction seems wrong. The rule may still use percentage-based thresholds, or the icon order may be reversed. Edit the rule, choose Number for threshold types, check the boundary values, and inspect the icons in the preview.
Zero shows an up arrow. In a three-icon rule, a value exactly on a boundary is assigned according to the rule’s greater-than-or-equal comparisons. For whole numbers, thresholds of 1 and 0 separate positive values from zero. For decimals, select a suitable positive threshold, such as the smallest meaningful increment, and zero as the lower threshold. If exact handling of a neutral range is essential, test zero and boundary values in a small sample before applying the rule to the full report.
The arrows compare against the whole column rather than last period. An icon set applied directly to current values classifies those values against its thresholds; it does not pair each row with a prior-period value. Create a change column first, and apply the rule to that column.
Some rows show no useful result. Check for blank cells, numbers stored as text, formula errors, or an incorrect Applies to range. Text values cannot be compared as numeric changes in the same way as numbers. Also confirm that each current value is paired with the correct previous value and that formulas were filled down correctly.
New rows do not get arrows. Review Home > Conditional Formatting > Manage Rules and inspect the rule’s Applies to range. Expand it to cover the added data, or structure the source as an Excel table so ranges and formulas are easier to extend as rows are added.
When to use another visual method
Arrows work well when a table needs a quick direction cue, especially when readers scan many rows. They are less suitable when the magnitude matters more than direction, when there are many categories, or when the report needs to show a trend across several periods. A line chart can show a multi-period pattern; a data bar can emphasize size; and a simple numeric column may be clearest when changes are small or precise interpretation matters.
For a table that needs exact custom behavior, separate conditional-formatting rules can be created for positive, neutral, and negative values. That offers more control over which formatting appears, though it requires maintaining several rules and does not automatically provide the same built-in icon-set behavior. For most simple period comparisons, a calculated change column plus a carefully configured icon set is easier to audit and maintain.
Frequently Asked Questions
Can Excel show an arrow when a value changes?
Yes, but the arrow does not detect a change from an earlier value by itself. Calculate the difference between the current and previous values, then apply an icon set to that difference.
How do I make an up arrow mean an increase?
Apply a three-arrow icon set to a change column, edit its rule, and use numeric thresholds that distinguish increases, neutral values, and decreases. Confirm the icon order in the preview.
Can I show arrows without showing the change number?
Yes. In the icon-set rule, select Show Icon Only. Consider keeping the number available in another column or in the underlying cell so the report remains interpretable.
Why does Excel show an arrow for a value that did not change?
The threshold settings may treat zero as part of the upper category, or the rule may be based on the selected range rather than a zero boundary. Edit the rule and test its numeric thresholds, including zero.
Can arrows show whether a percentage increased?
Yes. Calculate percentage change where the starting value is nonzero, format the result as a percentage, and apply arrows to that result. Remember that a numeric threshold of 0.02 represents 2% in Excel.
Related
- Excel Conditional Formatting: Apply Rules to a Range
- Excel Always Not Responding? Causes and Fixes
- Excel Adjusting Print Area: Fit, Set, and Clear Pages
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Keyboard Shortcut to Insert a Checkbox in Microsoft WordIn Word for Microsoft 365 on Windows, type 2610 and press Alt+X to insert an empty checkbox symbol…
- MS Outlook Out of Office: How to Set It UpTo set an out-of-office reply in Microsoft Outlook for Microsoft 365 on Windows, open Outlook and select File…
- How to Make a Flowchart in Microsoft WordTo make a flowchart in Word, choose Insert > Shapes, add flowchart shapes to the page, and connect…
- How to Delete a Stubborn Page in Word Without Losing TextTo delete a stubborn page in Word, first turn on formatting marks with Ctrl+Shift+8 on Windows or Command+8…
- Excel Conditional Formatting for Drop-Down ListsTo color an Excel drop-down selection automatically, create the list with Data Validation, then add a conditional-formatting rule…
- How to Insert a Full-Width Horizontal Line in WordTo insert a line across the full width of your text area in Microsoft Word, place the cursor…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.