## Create a Heat Map Using Excel’s Conditional Formatting

A reader of my post Excel 3D Charts: Charts with No Value asked how to arrange his data to display a variable on a grid, thinking I’d acquiesce to his desire for a 3D chart. My initial thought was “No way”, but I first asked what he was plotting. He was doing an agricultural experimental design with a 2×5 grid of plots planted with various plants, and wanted to see whether there was a positional variation to the results which would not have been seen in a standard ANOVA analysis. His output values typically ranged from 1000 to 5000 pounds per acre.

I thought this could be visualized in 2D without the usual 3D issues, using a heat map. Now I’ll construct such a heat map using Excel’s Conditional Formatting Feature.

I set up a 2 column by 5 row grid, and inserted random numbers between 1000 and 5000.

To represent a 2×5 grid of square plots, I adjusted the rows and columns to be the same size, in this case 50 pixels. This was an assumption on my part, but whatever the dimensions of the actual plots, you can fudge the row and column dimensions to represent these dimensions.

I selected this range, and clicked on Conditional Formatting on the Home tab of the Excel 2013 ribbon, and hovered over Color Scales. As far as I recall, this part of the color scales mechanism worked the same in Excel 2007 and 2010.

There are a dozen built-in color scales, and if this isn’t enough you can pick More Rules.

Below are the five built-in options I’d be most likely to use for such a heat map, highlighted in the popup menu and displayed in theworksheet. These are a red-yellow-green 3-color diverging scale, a red-white-green 3-color diverging scale, a red-white-blue 3-color diverging scale, a white-green 2-color sequential scale, and a yellow-green 2-color sequential scale.

I decided to use the red-white-green diverging scale, because I thought it would be the most color-vision-friendly of the three diverging scales.

To change to a better scale, I selected the range and selected Manage Rules at the bottom of the Conditional Formatting dropdown menu, and the Formatting Rules Manager dialog appeared.

If I’d selected a larger range with multiple sets of rules, like the range shown above with five color scales applied, this dialog would show all of the rules.

I selected the color scale I wanted to change, and clicked Edit Rule, to pop up the Edit Formatting Rule dialog. You can see that the options for this type of rule include 2- and 3-color scales, which are useful for heat maps, as well as data bars and icon sets, which are not.

You can select three colors for minimum, midpoint, and maximum, and you can choose to define these points in several ways. Here I’ve kept the default lowest value, 50th percentile (median), and highest value, but all could be defined by percentiles, values, or formulas.

To select my colors, I went to my favorite source for color schemes at ColorBrewer2.org, by Cynthia A. Brewer at Pennsylvania State University. I decided on a color-vision safe, 9-color, purple-to-green diverging scale, and this is what ColorBrewer showed me. Click on the image to visit this selection at ColorBrewer2.org.

Click on the image to visit this selection at ColorBrewer2.org.

ColorBrewer also lets you export the colors in various ways, including as an array of RGB values:

{118, 42, 131; 153, 112, 171; 194, 165, 207; 231, 212, 232; 247, 247, 247; 217, 240, 211; 166, 219, 160; 90, 174, 97; 27, 120, 55}

I used the second (purple) and eighth (green) elements of this color scheme for the min and max colors, and kept the white central value (the ColorBrewer midpoint is about 5% gray).

Below is the purple-white-green 3-color diverging scale (left), a purple-green 2-color sequential variation (center), and a white-green 2-color sequential variation (right). Note that these intermediate colors are not ColorBrewer’s, but are calculated by Excel.

The three-color purple-to-white-to-green scale seems to show the variation better than the two-color purple-to-green scheme, because the latter doesn’t show much variation within the greens or within the purples. The two-color white-to-green might be the best overall choice, especially if the heat map has to be photocopied.

## Colors in Excel

When you know how Excel’s color system works, you can do some great work. We’ll take a brief look backwards at the rudimentary color palette of Classic Excel, then explore the enhanced capabilities of the color system introduced in Office 2007.

## The Color Palette (Excel 2003 and Earlier)

You may remember the default color palette of Excel 97 through 2003, shown below. The palette a mishmash of blindingly bright tiles amidst a multitude of dark gloomy colors. I think it was designed by someone who only came out of his dungeon at night.

There were 56 colors in the palette, and you could independently modify any of them. But you could not use any colors beside these 56 in your workbook, except in AutoShapes. Each workbook could have its own palette (but only one palette), and you could assign one workbook’s palette to another workbook. The sixteen tiles in the bottom two rows were not always available for general editing, but were reserved for chart lines (bottom row) and chart fills (second row from the bottom).

What I miss about Excel 2003 is not so much the available colors in the palette, but the fact that you could tear them away from the toolbars and drag them to where you were working. They stayed open until you closed them, which saved hundreds of clicks a day (or hour), and their proximity to your work saved miles and miles of mouse travel.

In fact, Classic Excel contained a variety of formatting tearaway toolbars, and I miss all of them. Here is a screenshot of many of them, in case you’re nostalgic. (Sniff.)

The best thing that Microsoft could do for Excel 2016 would be to reintroduce this kind of old-school functionality, and restore our lost productivity.

The default palette was pretty grim, and the defaults made for some butt-ugly charts. But it was easy enough to tailor your own palette, as I had done with guidance from the color picker at ColorBrewer2.org, thanks to Cynthia A. Brewer, Pennsylvania State University. Below are shown the default palette (left) and one of my custom palettes (right). My bottom three rows were medium, light, and very light shades of 8 colors; I lightened up the grays in the last column, I replaced the dark colors in the top row, and made one of the dull greens in the middle into one with more life.

When a workbook’s color palette is changed, all colors in the workbook are subject to change.

As attached as we were to Excel 2003′s color system, we welcomed the new color system that came with Excel 2007.

## The Color Chooser (Excel 2007-2013)

The Classic palette has been replaced with a similar color chooser, which shows all of the colors in the chosen theme. The colors are more organized, where each column of the grid of colors has shades of the same base color. This shows the default Office 2007-2010 theme. The colors are somewhat dull, but they are a major improvement over the Excel 2003 colors, and reportedly these colors are distinguishable by those with the most common color vision deficiencies.

As you mouse over the color chooser, names of the colors pop up. Names like “Black, Text 1, Lighter 35%” and “Aqua, Accent 5, Darker 25%”.

This table shows the names for all color tiles in the default Office 2007-2010 theme. The name consists of the color name in the first row, “Aqua”, then the label from above the table, “Accent 5″, and finally the adjustment of the color, “Darker 25%”.

For Text 2 and the Accent colors, the sequence of shades goes Lighter 80%, Lighter 60%, Lighter 40%, Lighter 0%/Darker 0% (the baseline shade), Darker 25%, and Darker 50%. There’s a large jump between the baseline color and the next color at Lighter 40%. I wish they’d decided to use Lightness values of 25%, 50%, and 75% instead of the chosen 40%, 60%, and 80%. I also wish there was a way via the user interface to adjust this percentage, but you need VBA if you want to keep a color associated with the theme.

## Theme Colors

We’ve been looking at the default Office 2007-2010 color theme, but there are many more to choose from, which you can access from the Page Layout tab. Office 2013 has a new theme, which is a little livelier than the old 2007-2010 theme, further improving on the Classic Excel colors. There are a slew of other themes too, but it seems most of them are skewed to much towards a particular color to be good for general use.

Below are the color choosers for the Office 2007-2010 default theme (left) and the Office 2013 default theme (right). The 2010 theme’s colors are somewhat drab, although a major improvement over the default Excel 2003 palette, and reportedly friendly to those with color vision deficiencies. The Office 2013 colors are brighter, and look very good in charts. I’ve actually grown to like working in Excel 2013, much more than in 2010 and especially in 2007; I suspect the happier colors have contributed to my acceptance of 2013.

Here are the names of the colors in the Office 2013 theme. The color names have changed, but the percentages are all the same. Also, the standard colors are unchanged.

At the bottom of the list of color themes is a button labeled Customize Colors, which leads to this dialog. It is populated below with the Office 2013 colors. There is a preview of how the colors look together, and each color dropdown opens a color picker that lets you access all 16+ million combination of red, green, and blue pixels. then you can name and save your custom theme.

Here I’ve populated the accent colors with pure, fully saturated versions of the Office 2007-2010 theme.

Whoa, that’s pretty bright…

Here are the color names for my custom palette. Notice that Excel tries to guess the names of the colors, or it probably has a lookup table. Turquoise, for example, is somewhat deeper than Aqua.

Notice also the percentages. These are locked in. You can’t change them in the theme color dialog above, you can only change the main colors in the first row.

As with Classic Excel, each workbook has its own theme, which may be shared with other workbooks through the custom theme mechanism. But you could use other colors, including the standard colors from the bottom row, and using the color dialog (More Colors) you could access all the millions of colors available to Windows.

When a workbook’s color theme is changed, all colors that were selected from the theme are subject to change, but any colors defined using More Colors will remain unchanged.

## More Colors (Custom or “Recent” Colors)

At the bottom of the color picker is a button labeled More Colors.

This opens the familiar old Colors dialog used in Classic Excel and a gazillion other applications. The Standard tab shows a hexagonal pattern of colors, with the color of the selected object highlighted in the hex and along the bottom if the color lies on the white-to-gray-to-black axis.

If you select a different color, the New/Current graphic in the bottom corner lets you compare the newly selected color with the original color.

If the hex doesn’t provide just the proper shade, click on the Custom tab. You can change the color by dragging the white crosshairs in the hue-saturation rectangle to the left, or by dragging the black triangle up and down the luminance slider. Or if you know the RGB values you can simply type them into the boxes. The New/Current graphic updates as you adjust the color.

You can also select the HSL color model and enter hue, saturation, and luminance values directly.

When you’ve selected a new color, it appears in a new category of Recent Colors. You can select as many different colors as you need, but only the ten most recently used colors stay in the color chooser.

## Chart Colors

Remember those Excel 2003 charts? Here’s a simple stacked column chart.

Professor Tufte’s nightmare.

It’s a little better when I use my custom Excel 2003 palette, but there are still too many dark lines everywhere.

If you clean up the dark lines, the Excel 2003 chart doesn’t look too bad.

Here’s the chart using the default Office 2007-2010 color theme. Legible but pretty drab except for the aqua bars on top. Much better than the default 2003 chart, though.

Here is the same chart after changing to the Office 2013 theme. I can’t say that it’s any more legible, but less drab is good.

Here I’ve applied my bright and bold custom theme.

Ouch! It’s a good reminder to use a reputable source for your color scheme. As mentioned earlier, one good source is the color picker at ColorBrewer2.org, thanks to Cynthia A. Brewer, Pennsylvania State University.

### Formatting Chart Colors

Once you have your chart, there are many ways to format its elements. I’ll illustrate with formatting the fill color of one of the series in the column chart.

One of the most useful shortcuts in Excel is Ctrl+1 (that’s the numeral one). I use it so much I find myself trying to use it in other programs, and it never does anything.

What Ctrl+1 does in Excel is open the formatting dialog or task pane for the selected object. Of course, you could right click on the object and choose the Format [Object Name] button from near the bottom of the pop-up menu, but Ctrl+1 is cooler.

So select the series in the chart and click Ctrl+1 and in Excel 2013 the Format Data Series task pane opens. Its default position is docked to the right edge of the workbook window. I’ve clicked the Fill Color dropdown, and the familiar color chooser pops up.

The taskbar can be undocked by clicking on its top edge and dragging it to where you want to use it. In this way it somewhat resembles the long lost floating tearaway toolbars from Classic Excel. Somewhat.

I’ve floated the task pane over the chart and clicked the Fill Color dropdown to reveal the color chooser.

The formatting task pane was introduced in Excel 2013. In Excel 2007 and 2010 there is a formatting dialog, which floats modelessly over the worksheet. When you select the Solid Fill option, the Fill Color dialog appears, and clicking it shows the color chooser.

In Excel 2013 and 2010, but not in 2007, when you right click on a chart element, in addition to the standard pop-up menu, there is a small floating formatting mini-toolbar which has context-relevant formatting buttons (Fill and Outline are displayed below) and a chart element dropdown that identifies the selected element and allows you to select any other element in the chart.

Click on the Fill button, and the color chooser appears.

Now for the cool part. When you mouse over a color tile in the chooser, the selected element temporarily takes on the color of that tile, giving you a preview of how the element will look with that color applied. The formatting task pane in Excel 2013 and the formatting dialog in Excel 2007 and 2010 do not give you this preview.

You can also use the controls on the Chart Tools > Format tab to format the chart elements. Select the series and click the Shape Fill button to reveal the color chooser.

Mouse over a color tile, and as before the selected series is previewed with that fill color. Nice.

You can even format some elements of the chart using controls on the Home tab. Even though it’s in the Font group of controls, the Fill dialog works on the selected series. Click on the paint can and the color chooser pops up.

Mouse over a color tile, and the series is filled with that color.

That preview feature is pretty cool, a good reason to use anything but the format task pane or dialog for most formatting actions. These protocols work the same for line or border colors, font properties, etc., and the previews are featured when controls on the right-click mini-toolbar or ribbon are used.

## Using Copied Custom Colors

Let’s return to our original stacked column chart. We’re going to change the series colors so they range from red-orange at the bottom to blue at the top.

How do you get the colors into the chart? Well, you can define other colors by entering RGB values, but if you have more than a few, you’ll get pretty bored. I have a method that’s much more fun.

I have several worksheets on which I have stored various color sequences, based on the work at ColorBrewer2.org, thanks to Cynthia A. Brewer, Pennsylvania State University. If that name sounds familiar, it’s because I keep mentioning it, and you should visit the site and find some decent color schemes.

Here is a small view of one of my color worksheets. The columns are color schemes (red-blue, orange-purple, etc.), the rows are groups of three colors, five colors, seven colors, etc. each cell is shaded with the particular color, and the RGB values are shown in the cell for reference.

I usually start by copying the block of cells with the colors I want, and paste them somewhere in the workbook.

The color chooser in a new workbook shows the default color theme and standard colors, and no recently used colors. Here’s an easy way to get the colors from the copied range into the Recent Colors section of the chooser. Remember there’s a limit of ten recent colors.

Select one of the colored cells. Click on the Fill dropdown, and then click More Colors at the bottom of the color chooser.

The Colors dialog pops up. When a color-theme-formatted object is selected, the current color in the Colors dialog is black and the standard tab is active. However, when an object formatted with a custom color is selected, its RGB values are plotted on the Custom tab, and the Current/New graphic shows this color.

Don’t make any changes in this dialog, just click OK to close it. Now when we check the color chooser, the color that we saw in the Colors dialog appears in the Recent Colors list.

That was easy, we didn’t have to type any numbers or anything.

Now we can select the series we want to format and click the Fill dropdown to reveal the color chooser. Yes, the newly added custom color is still there.

Mouse over the new color, and behold the series previewed with that color.

Release the mouse and the chart series has been filled with the new custom color.

Select the next colored cell, click the Fill dropdown, and click More Colors.

The new cell’s fill color is plotted in the Colors dialog.

Click OK to insert this color into Recent Colors.

Return to the chart, right click the next series click the Fill button, mouse over the new color in the chooser, and the color is previewed in the series.

Here’s the chart, 2/5 of the way done.

Lather, rinse, repeat until all of the colors have been added to Recent Colors.

Here is the chart with our desired color scheme applied.

The workbook will remember these colors when it is closed and reopened. The recent color tiles will rearrange themselves, with the most recently used color furthest to the left. This makes the most recent color easy to find when you’re applying the color to many objects.

## Further Investigation

### Windows 2007 Colors

This article has covered most of the user interface part of Excel’s colors. With some VBA and the patience to flush out details of the object model, you can further expand Excel’s capabilities. Of course, the object model that came out with Office 2007 was somewhat half-baked, and there were problems with some combinations of color and percentage brightness or darkness not being obtainable. Some new VBA elements were introduced in 2010 that fixed a few problems and seemed to have added some of their own.

For further coverage of Office 2007′s color mechanisms, check out Tony Jollans’ article, Colours in Word 2007. Tony has spend lots of time figuring out and documenting Office 2007′s colors in excruciating detail.

Echo Swinford shows how to hack the XML code of the theme to add your own Custom Colors to the color chooser in PowerPoint 2007 Custom Colors. These are different from Recent Colors and appear in a Custom Colors section above the Standard Colors section.

Mr. Spreadsheet himself, John Walkenbach, covered Office 2007 colors in Excel 2007 VBA ChallengeMore On Office 2007 Colors, and Exploring Theme Colors, in which he describes some of the glitches he’s encountered.

### Choosing Colors

I’ve mentioned several times that good colors can be found at ColorBrewer2.org, thanks to Cynthia A. Brewer, Pennsylvania State University. There are innumerable color scheme designers on the web, but I prefer ColorBrewer’s complete schemes.

Perceptual Edge hosts an article by Maureen Stone, Choosing Colors for Data Visualization, as well as Stephen few’s articles Practical Rules for Using Color in Charts and Uses and Misuses of Color.

Juice Analytics has some advice about color use in Color Has Meaning and other pages linked from that one.

NASA has an extensive library of pages about Using Color in Information Display Graphics.

UXmatters has a series of articles about Color Theory for Digital Displays: A Quick Reference: Part I, Part II, and Part III.

To check your colored images for color vision deficient viewers, visit Vischeck.

## Color Vision Issues with Heat Map Donut Charts

I’ve reviewed a colorful yet ineffective graphic in Wow! Heat Map Donut Chart!. One of the features that makes this graphic ineffective is its use of colors. While unfriendly to those with normal color vision, the colors used are particularly unfriendly to the 8% of men and 0.4% of women with color vision deficiencies.

## Color Vision

In the normal human retina, their are four types of photoreceptors, neurons that detect light. One type, called rods, respond to low light levels, producing monochromatic vision in near darkness. In brighter light, three types of cones respond to incoming blue, red, and green light. The mechanisms of color vision are fascinating and miles beyond the scope of this article. For this discussion it is enough to know that our perception of color is a function of how these color receptors, other retina neurons, and the visual cortex of our brains process the signals from these cones.

Color blindness occurs when one of the three types of cones are partially or totally disabled. The most common color vision deficiencies are deuteranomaly and deuteranopia, respectively partial and total loss of function of the green photoreceptors. Protanomaly and protanopia are the partial and total deficiency of the red sensing neurons. Much less common than either of these conditions are tritanomaly and tritanopia, partial and total loss of activity of the blue cones.

Deficiencies in either the red or the green photoreceptors leads to difficulties distinguishing red and green. Ironically, the most common pair of colors used to indicate good and bad are red and green. Think of traffic lights with red and green lights indicating stop and go, and innumerable bad dashboards with arrays of red and green symbols. Better combinations would be read and blue, or orange and cyan (which must be familiar from the hundreds of Tableau graphics we’ve seen over the last few years).

Most mammals only have two types of photoreceptors, and have the type of color vision that protanopes and deuteranopes experience. Early during primate evolution, a mutation split one of these receptors into the green and red cones that provide richer color vision. Some reptiles, birds, and fish may have four, five, or even more types of cones, extending into the ultraviolet and infrared ranges of the spectrum we can only imagine.

## Testing Images for Color Vision Issues

There are numerous programs and web sites that simulate the effects of color vision deficiencies on your images. One popular and easy-to-use site is Vischeck, which allows you to upload images, and uses various algorithms to convert them into representations of how they would look under various types of color blindness. The simulations here were generated using Vischeck.

### Comparison of Heat Map Donut Chart Images

Here is the original heat map donut chart, in all of its visual glory.

This is how deuteranopia (green cone deficiency) renders the donut.

This is how protanopia (red cone deficiency) renders the donut.

These two conditions result in similar images: a blob of blue in the top right, and a smear of yellowish brown elsewhere. The red-orange-yellow-green gradients are mostly eradicated, rendering the heat map nearly useless.

### Comparison of Pie Chart Images

Here is my depiction of the heat map donut data, in a properly sorted pie chart, using the default Excel 2007/2010 color palette. In full color:

Here is the pie as seen by a deuteranope (simulated).

Here is the pie as seen by a protanope (simulated).

As above, the two color vision deficiencies result in similar simulations. Without a gradient, though, the wedge colors are much more readily distinguished. Labeling the wedges themselves does help greatly, as some color pairs are very close; a legend would not be so helpful.

It turns out that the default Excel 2007/2010 color theme is relatively friendly to viewers with color vision deficiencies.

### Comparison of Bar Chart and Dot Plot Images

Here is a bar chart of the same data, shown in regular color vision and simulated deuteranopia (protanopia was omitted because it is less common and the effects are similar).

Here is a dot plot of the same data, shown in regular color vision and simulated deuteranopia (protanopia was omitted because it is less common and the effects are similar).

We can see that color vision deficiencies have no effect on the readability of bar charts and dot plots, because neither rely on color to encode quantitative information.

## Recommendations

Graph types such as bar charts and dot plots again prove their superiority over pie and donut charts, in this case by not relying on colors to depict numerical data.

Gradients are particularly problematic when combined with color vision issues. In general, the guidelines for gradients are that they should proceed from a dark shade of a color to a light shade of the same color, or from a dark shade of a color through a light shade (close to white) to a dark shade of a distinct second color. The gradient used in the heat map example proceeded through highly saturated chades of several colors.

Colorbrewer is an online tool designed to help select good color schemes for maps and other graphics. You can select from among different styles of color schemes, and you can choose to generate only color-vision-friendly schemes. In conjunction with a colov-vision-deficiency simulator like Vischeck, you have no excuse for producing hostile graphics like the donut heat map.

## Column Chart Gridlines Cutting Through Bars

An astute reader asked how to achieve the gridline effect in the following chart. The gridlines are not gray lines running across the whole chart, behind the bars. Instead, the bars themselves appear to be cut into sections where the gridlines would have gone. There are in fact gridlines in the chart, with a line color to match the background, in front of the bars.

## Gridlines

The thing is, gridlines are always in back of the bars, as shown in this chart. I’ve lightened the bars, and darkened and widened the gridlines, to show their normal relative positions. Don’t let me EVER catch you making gridlines like this.

If gridlines are used at all, they should be thin light gray lines, as unobtrusive as possible.

But I digress.

## The Data and Chart

Here is the population data for this exercise. Column B has numerical years. Column A has formulas as follows. The even decades (1800, 1820, etc.) simply link to the year; A3 has the formula =B3. The odd decades (1790, 1810, etc.) insert a line feed before the year; A2 has the formula =CHAR(10)&B2. The reason for this will become apparent when we get to it.

Here is the default chart created from the data in columns B and C.

Let’s apply some formatting. That default title is way too large, perhaps the only concession to my over-50 eyes in all of the Office suite. The gap width is set to 50% so the bars are more prominent. And I’ve changed the source data for the chart to column A. The line feed in alternating cells staggers the X axis labels so the years are easier to read. The vertical axis is removed, because we’ll add our own later.

And now to add the gridlines. And how do we get gridlines in front of the data? Well, there are several ways to make horizontal lines in Excel charts. The crudest is simply to draw lines from the Shapes command on the Insert tab, but they’re hard to line up, and they don’t stay where you put them. What’s the trick?

Error bars. Error bars are positioned in front of bars in a chart. We’ll apply error bars, and color them white, so they appear to cut through the bars.

We’ll add a series of points along the right vertical edge of the chart, draw error bars to the left of these points, and add data labels to the right. Here is the data.

To get the millions to show without all of those extra zeros, apply a number format of “0,,” (without the quotes, duh). The zero by itself means use the number format with no decimal digits, and each comma after the zero means slice off one group of three zeros, one for thousands, another for millions.

But that 23.5? People always ask me where that came from. The answer is easy once you know it. Let’s look at the chart without the year labels, and just count the bars. They are numbered 1 to 23 from left to right. The tickmarks on the axis between the bars are at the halves: the chart starts at 0.5, the next tickmark is at 1.5, all the way up to the tick at 23.5 at the right edge of the chart. We’ll add an XY series with the points directly above this tickmark, at X=23.5.

Copy the gridline data (two columns and all rows including the “million” label). Select the chart and use Paste Special to add the data to the chart. Make sure the Paste Special dialog has these options selected: New Series, Values in Columns, Series Names in First Row, Categories (X Labels) in First Column, and DON’T Replace Existing Categories.

And here is the chart with the added data. It’s in column chart form, but we’ll fix that.

Right click on the added bars, choose Change Series Chart Type from the pop-up menu, and choose the XY Scatter type with markers only.

Excel moved the series to the Secondary axis, and dumped an extra set of axes into the chart. No problem, Format the XY series, and on the first tab of the Format Series dialog, choose Primary Axis.

Now it’s time to add the error bars. Select the XY Series, and on the Chart Tools > Layout tab, click on Error Bars, More Error Bar Options.

The horizontal error bars are easily visible: note how they lie in front of the bars. You can’t see the vertical error bars, because they are only one unit (one person) tall, but they are selected when you add the error bars. Close the Format Error Bars dialog and press delete to remove the vertical error bars.

Format the horizontal error bars. Choose the Minus direction, No Cap as the End Style, and a Fixed Value of 23 (see the explanatory chart above: the error bars reach from X=23.5 to X=0.5, so they are 23 long). Here’s how the Format Error Bars dialog should look:

And here’s the chart with the appropriately sized error bars.

Select the XY series, and on the Chart Tools > Layout tab, choose Data Labels > Right. This adds the default data labels, which show the value. The data labels have the same number format as the cells containing the values. You’ll probably have to select the plot area and drag the right edge inward to make room for the labels.

Format the XY series, and choose None for the Marker Option.

Finally, format the error bars, so the line color matches the chart background, in this case, white.

## Lessons Learned

In this tutorial I’ve shown how to use gridlines that seem to cut the bars in a bar or column chart. Among the tricks I’ve used are:

• Line feeds in the category axis labels to stagger labels and prevent overlap,
• Using error bars to draw horizontal lines in a chart (error bars are great for vertical lines, too),
• Using data labels for custom axis labels.

You can accomplish many things with these tricks in your charting arsenal.

For an Excel workbook that contains this step-by-step tutorial, click on this link:  Gridlines_Splitting_Bar_Chart.xlsx

## Conditional Formatting of Excel Line and XY Chart Lines

I’ve written a lot about conditional formatting of charts. One topic I have not covered is formatting of lines in line charts and XY charts, because this isn’t as easy to do.

## Formula-Based Conditional Formatting

In Conditional Formatting of Excel Charts and Conditional XY Charts Without VBA, I showed examples of a formula-based technique to apply conditional formatting in Excel Charts. Essentially there are as many series in the chart as there are distinct formats you want to show. There is one point (i.e., one bar or marker) displayed where a point should be displayed, and the formulas control which series displays each point, with which format. Using formulas is more reliable than using VBA, because updating is dynamic without needing to run code whenever the data changes, and it’s easier to maintain. This approach can be applied to bar or column charts…

… to markers in line charts…

… and to markers in XY charts…

This conditional formatting is applied to each individual point, but unfortunately it cannot be applied in this way to the individual line segments connecting markers in a line chart.

## VBA-Controlled Conditional Formatting

Sometimes you don’t have the luxury of using formulas for your conditional formatting. Here are a few articles showing VBA techniques for applying conditional formats to a chart:

## VBA-Controlled Conditional Formatting of Line Chart Lines

You might want to format lines differently if they represent rising or falling values. Green line segments for increasing trends or red for decreasing. Actually, a more color-vision-friendly combination is blue for increasing and orange for decreasing. The chart at the top of this article is a good example.

Unfortunately, line segments in a line or XY chart cannot be turned on or off using formulas, the way markers or columns and bars can be. So we’ll have to use VBA to format them.

For this tutorial. let’s dig out some web site statistics. This chart of weekly visits show some week-to-week fluctuations, with occasional flat segments which we will not want to color.

I decided to record a macro so I can figure out the syntax. The help files are pretty sketchy about this.

I started with a boring dummy chart with three series.

I turned on the macro recorder while I applied the following formatting to the dummy chart. I changed line colors, and I didn’t change the marker color but shrunk the marker size

Here’s the recorded macro. It’s no uglier than any other recorded macro.

```Sub RecordedMacro1()
'
' RecordedMacro1 Macro
'

'
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent5
.ForeColor.Brightness = 0.400000006
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 2.25
End With
Selection.MarkerSize = 4
ActiveChart.SeriesCollection(2).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent6
.ForeColor.Brightness = 0.400000006
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 2.25
End With
Selection.MarkerSize = 4
ActiveChart.SeriesCollection(3).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.Brightness = -0.349999994
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 2.25
End With
Selection.MarkerSize = 4
End Sub```

Well, yeah, it is kind of ugly. It selects everything, then works on the selection object, and includes all that default stuff I never touched.

Long story short (long code short), I used constants for the formatting settings, streamlined the code. I also did some magic to compare whether adjacent points showed increasing or decreasing values to decide what colors to use.

```Sub ColorLinesBasedOnSlope_ThemeColor()
Dim srs As Series
Dim iPoint As Long
Dim vValues As Variant

Const thmclrBlue As Long = msoThemeColorAccent5
Const briteBlue As Double = 0.4
Const thmclrOrange As Long = msoThemeColorAccent6
Const briteOrange As Double = 0.4
Const thmclrGray As Long = msoThemeColorBackground1
Const briteGray As Double = -0.4
Const dWeight As Double = 2.25
Const lMarkerSize As Long = 4

Set srs = ActiveChart.SeriesCollection(1)
vValues = srs.Values
For iPoint = 2 To UBound(vValues)
With srs.Points(iPoint).Format.Line.ForeColor
Select Case vValues(iPoint) - vValues(iPoint - 1)
Case Is > 0
.ObjectThemeColor = thmclrBlue
.Brightness = briteBlue
Case Is < 0
.ObjectThemeColor = thmclrOrange
.Brightness = briteOrange
Case Else
.ObjectThemeColor = thmclrGray
.Brightness = briteGray
End Select
End With
srs.Points(iPoint).Format.Line.Weight = dWeight
srs.Points(iPoint).MarkerSize = lMarkerSize
Next
End Sub```

I selected my chart (actually, I was smart and selected a duplicate chart) and ran this code. Here’s what happened:

Oof! Not only did the code format the line segments, it also formatted the marker lines. I never did that while recording the macro.

Turns out, the object model for Excel 2007/2010 charts does not distinguish between the connecting lines and the lines that draw the markers. Well, that’s no good.

Fortunately I still can use the old tired and true Excel 2003 VBA syntax. Instead of .Format.Line.Whatever, I reverted to .Border.Whatever, and I took out that weird ThemeColor drivel, and replaced it with the familiar RGB color definitions. I present the updated code.

```Sub ColorLinesBasedOnSlope_RGBColor()
Dim srs As Series
Dim iPoint As Long
Dim vValues As Variant

Const rgbMyBlue As Long = 14536083
Const rgbMyOrange As Long = 9486586
Const rgbMyGray As Long = 10921638
Const lWeight As Long = xlThick
Const lMarkerSize As Long = 4

Set srs = ActiveChart.SeriesCollection(1)
vValues = srs.Values
For iPoint = 2 To UBound(vValues)
With srs.Points(iPoint).Border
Select Case vValues(iPoint) - vValues(iPoint - 1)
Case Is > 0
.Color = rgbMyBlue
Case Is < 0
.Color = rgbMyOrange
Case Else
.Color = rgbMyGray
End Select
.Weight = lWeight
End With
srs.Points(iPoint).MarkerSize = lMarkerSize
Next
End Sub```

I selected another copy of the chart, and ran this new procedure.

Nice. It colored the lines orange and blue, except for a couple horizontal segments that were colored gray. It also kept the markers gray, but shrunk them so they don’t overwhelm the lines.

The markers aren’t really necessary. I like them, so I know for sure where there is actual data, but you can judge where the lines change their angle or color to know this. I made yet another copy of my original chart, removed the markers, and ran the formatting procedures.

Which procedure did I use? Doesn’t matter. Both produced the same result, since there were no markers for the newfangled code to mess up.