Skip to main content

Up and Down Markers using Conditional Formatting



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.

If you don‘t like it , 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.
Labels:

Comments

Post a Comment

Popular posts from this blog

3D Map in Excel

3D Maps ( Power Map) is available in the Office 365 versions of Excel 2013 and all versions of Excel 2016. Using 3D Maps, you can build a pivot table on a map. You can fly through your data and animate the data over time. 3D Maps lets you see five dimensions: latitude, longitude, color, height, and time. Using it is a fascinating way to visualize large data sets. 3D Maps can work with simple one-sheet data sets or with multiple tables added to the Data Model. Select the data. On the Insert tab, choose 3D Map. (The icon is located to the right of the Charts group.) If you have Excel 2013 you might have to download Power Map Preview from Microsoft to use the feature. Next, you need to choose which fields are your geography fields. This could be Country, State, County, Zip Code, or even individual street addresses. You are given a list of the fields in your data set and drop zones named Height, Category, and Time. Hover over any point on the map to get details such as last sale date and a...
Post a Comment
Read more

CAGR Dax Measure

CAGR stands for  C ompound  A nnual  G rowth  R ate.  It describes the rate at which an investment would have grown over several years if it had grown at the same rate every year on a compounding, rather than simple, basis.   The CAGR metric is calculated using the following formula: If we were to fit this entire formula into a single measure it may get messy and confusing for other users, so let’s step it out. We will need to create several measures to calculate the individual pieces the CAGR formula.  Of course, this is not the only way to calculate CAGR in Power Pivot, but this is the way we’ve decided to go about it.  So, let’s break it down, we will need the following measures to calculate CAGR: A measure to retrieve the First Year in the data set A measure to retrieve the Last Year in the data set A measure to calculate the Number of Years between the First and Last Years in the data set A measure to aggregate the total sales in the data set...
Post a Comment
Read more

Turn Data Sideways

Someone built this lookup table sideways, stretching across C1:N2. I realize that I could use HLOOKUP instead of VLOOKUP, but I prefer to turn the data back to a vertical orientation. Copy C1:N2. Right-click in A4 and choose the Transpose option under the Paste Options. Transpose is the fancy Excel word for “turn the data sideways.” I transpose a lot. But I use  Alt+E ,  S ,  E ,  Enter  to transpose instead of the right-click. There is a problem, though. Transpose is a one-time snapshot of the data. What if you have formulas in the horizontal data? Is there a way to transpose with a formula? The first way is a bit bizarre. If you are trying to transpose 12 horizontal cells, you need to select 12 vertical cells in a single selection. Start typing a formula such as  =TRANSPOSE(C2:N2)  in the active cell but do not press Enter. Instead, hold down  Ctrl+Shift  and then press  Enter . This puts a single array formula in the selected cells. T...
Post a Comment
Read more