Use the SIGN Function for Up/Flat/Down Icon Set
August 14, 2023 - by Bill Jelen

Problem: I want to show if a value is up or down compared to the previous reading. How can I use the poorly-named Three Triangles icon set to show up, down, or flat?
Strategy: Add a helper column next to the values. Instead of using =C3-C2
, wrap the calculation in the SIGN
function. Any increases are shown as 1, decreases are shown as negative 1 and unchanged will appear as 0.
Apply the Three Triangles icon set. Since the only values are -1, 0, and 1, the icons will correctly show up, flat, or down.

Gotcha: You manager will complain about the 1, 0, -1 appearing in those cells. You can hide the number using (a) a white font, (b) a custom number format of ;;; or (c) use Home, Conditional Formatting, Manage Rules, Edit Rule, and check the box for Show Icon Only.

This article is an excerpt from Power Excel With MrExcel
Title photo by Rosie Steggles on Unsplash