MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Up/Down Markers

August 18, 2017 - by Bill Jelen

Up/Down Markers

Icon Sets debuted in Excel 2007

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. Asking for the SIGN(I6-H6) will provide either +1, 0, or -1.

Sample Pivot Table and Helper Columns
Sample Pivot Table and Helper Columns

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 Three Triangles, when it is clearly Two Triangles and a Dash.)

Icon Sets
Icon Sets

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

Show Icon Only Option
Show Icon Only Option

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.

Paste Linked Picture
Paste Linked Picture

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.

Pivot Table with Icons
Pivot Table with Icons

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.

The Result
The Result

Watch Video

  • Icon Sets debuted in Excel 2007
  • In Excel 2010, they added a set with Up, Flat, Down
  • But an icon set can only look at one cell! How can you use it to compare two cells?
  • Copy two helper columns off to the side
  • Use a calculation to show the change
  • Type that formula without using the mouse or arrow keys to prevent =GETPIVOTDATA
  • Use the SIGN function to convert that to +1, 0, -1
  • Add the Three Triangles icon set to the helper cells
  • Why do they call it Three Triangles, when it is really 2 triangles and a dash?
  • Manage Rules, Edit the Rule. Show Icon Only.
  • Manage Rules, Edit the Rule. Change from percent to numbers
  • Use > 0 for Green up arrow
  • Use >=0 for yellow dash
  • all of the negative numbers will get the red down arrow
  • Copy the column widths from the original cells to the helper cells using Paste Special Column Widths
  • Copy the Helper Cells
  • Paste Linked Picture (this used to be the camera tool)
  • Turn off the gridlines with View, Gridlines
  • If you don't like the yellow dash for "No Change", manage rules, edit the rule, use No Cell Icon
  • To force the camera tool to update, click the camera tool. Click in the Formula Bar. Press Enter

Download File

Download the sample file here: Podcast2007.xlsx

Title Photo: geralt / pixabay

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.