MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Show Up/Down Markers


May 27, 2019 - by Bill Jelen

Excel Show Up/Down Markers. Photo Credit: Casey Schackow at Unsplash.com

There is a super-obscure way to add up/down markers to a pivot table to indicate an increase or a decrease.

Somewhere outside the pivot table, add columns to show increases or decreases. In the figure below, the difference between I6 and H6 is 3, but you just want to record this as a positive change. Use SIGN(I6-H6) to get either +1, 0, or -1.

The pivot table has Monday in H, Tuesday in I, Wednesday in J. Off the the right, two new columns are calculating the change from the previous day. The Tuesday change is =SIGN(I6 - H6). This gets you either a negative 1, zero, or one.

Select the two-column range showing the sign of the change and then select Home, Conditional Formatting, Icon Sets, 3 Triangles. (I have no idea why Microsoft called this option 3 Triangles, when it is clearly 2 Triangles and a Dash, as shown below.)


Now that you've generated some values of -1, zero, and one, select those helper cells and apply, Home, Conditional Formatting, Icon Sets, 3 Triangles. Note that the name called "3 Triangles" is a lie - the icon set has a green up icon, a yellow "no change" icon, and a red "down" icon. Technically, it should be called two triangles and a rectangle.

With the same range selected, now select Home, Conditional Formatting, Manage Rules, Edit Rule. Check the Show Icon Only checkbox.

Manage the rules, and check the box for Show Icon Only. This prevents the 1, 0, -1 from appearing in the cells.

With the same range selected, press Ctrl+C to copy. Select the first Tuesday cell in the pivot table. From the Home tab, open the Paste dropdown and choose Linked Picture. Excel pastes a live picture of the icons above the table.

Copy the range of icons. Select the first Tuesday cell in the pivot table. On the Home tab, open the Paste Dropdown. The very last icon is called Linked Picture (I). Select this.

At this point, adjust the column widths of the extra two columns showing the icons so that the icons line up next to the numbers in your pivot table, as shown below.

Now, you have overlaid pictures of the Up/Flat/Down icon next to the numbers in the pivot table.

After seeing this result, I don‘t really like the thick yellow dash to indicate no change. If you don‘t like it either, select Home, Conditional Formatting, Manage Rules, Edit. Open the dropdown for the thick yellow dash and choose No Cell Icon, and you get the result shown below.

Up and down indicators still remain, but the yellow rectangles indicating "no change" are gone.

Title Photo: Casey Schackow at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.