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.

Dates in ascending order starting in B2 and going down the column. High Temperature is in C2. Over in D2, use =SIGN(C3-C2) to show if today is cooler, even, or hotter than yesterday. Add the icon set with up, flat, down to column D. — Figure 1271. Use the SIGN function to generate -1, 0, or 1.

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.

In the Icon Set dialog, choose Show Icon Only. — Figure 1272. Hide the results of the helper column.

This article is an excerpt from Power Excel With MrExcel

Title photo by Rosie Steggles on Unsplash