Software

10 steps to adding a gradient background to your Excel data points

It's easy to turn an Excel chart into a visually compelling object. Susan Harkins walks through the steps for jazzing up data points with a simple formatting trick.

Excel charts don't have to be boring. There are a number of chart formats and effects you can use to turn something ordinary into something extraordinary. One of these effects is a gradient fill. Using this effect, a data point or a data series becomes interesting, and in the right circumstances, even playful -- in just 10 steps.

Note: This article is also available as a PDF download.

1: Create the chart

You can use any bar or column chart you like, but this article uses a simple graph (Figure A) that rates adult preference for sherbet. To create the chart, select A1:D2, click the Chart Wizard on the Standard toolbar, and then click Finish.

Figure A

You need a column or bar chart.

2: Select a data point

Next, select a single data point; it doesn't matter which one. The hard part is selecting just one. When you click any data point, Excel selects the full series. You can tell by the small selection handles that appear in the center of each column. That's not what you want. Click the data point's border and Excel will display selection handles around the border, as shown in Figure B (the vanilla data point). That's what you want.

Figure B

Click a border to select a single data point.

3: Launch the format

To format the data point, right-click it and choose Format Data Point from the resulting context menu, shown in Figure C. Click the Patterns tab, if necessary; it's the default. The resulting dialog box, shown in Figure D, offers many format options, but this technique relies on Fill Effects.

Figure C

Launch the Format Data Point dialog box.

Figure D

The way to a gradient fill is through the Patterns tab.

4: Experiment with fill effects

Click the Fill Effects button at the bottom of the Area section to display the Fill Effects dialog box. There's a lot to offer:

  • The Colors section lets you choose one or two colors or a predefined set of colors. You can also choose the strength of your color choice(s).
  • In the Shading Styles section, you can choose from several design effects.
  • The Transparency options let you create see-through effects. (We won't use this option.)

The key is to experiment with the different colors, shades, and effects. With just a little practice, you'll be comfortable with this feature.

5: Choose a color

The selected data point represents lime sherbet, so choosing green makes sense. From the Colors section, click One Color. From the Color 1 drop-down list, choose light green, as shown in Figure E. Then, slide the Dark/Light bar toward the light end. Don't go all the way, but as you slide to the right, watch the sample (bottom right). Figure F shows the saturation at about 75 to 80 percent light.

Figure E

Choose light green to represent lime sherbet.

Figure F

The lighter end works best for this effect.

6: Choose an effect

With the color and lightness set, choose an effect. There are several, but keep the source in mind. You're filling a single data point on a chart and this particular data point is small. Therefore, a horizontal fill probably won't be all that effective. Again, experimentation is the key. For now, click the From Center option and choose the Variant on the right, as shown in Figure G. Check out the sample; you don't have to return to the chart to see the effect. Click OK twice to return to the chart shown in Figure H. Notice that the legend matches the data point -- Excel is so clever!

Figure G

http://b2b.cbsimg.net/gallery/435150-500-552.jpg

Choose a design effect.

Figure H

The lime data point will definitely attract attention.

7: Format orange

Now that you know the basics, apply what you've learned to format the orange data point as follows:

  1. Select and right-click the orange data point.
  2. Choose Format Data Point.
  3. Click the Patterns tab.
  4. Click the Fill Effects button.
  5. Choose Two Colors.
  6. Select Orange from Color 1's drop-down list and Ivory from the Color 2 drop-down list.
  7. Choose Vertical from the Shading Styles section and select the bottom-left variant, as shown in Figure I.
  8. Click OK twice to return to the chart, shown in Figure J.

Figure I

This time, choose a different variant with two colors.

Figure J

This variation looks like a sherbet dream-sickle.

8: Format pineapple

Now you're ready to format the pineapple data point. To do so, repeat items 1 through 5 in the previous step. Then, continue as follows:

  1. After clicking Two Colors, choose Ivory from the Color 1 drop-down list.
  2. Excel will probably choose white for Color 2, but if not, choose white.
  3. From the Shading Styles, choose Horizontal.
  4. Click the top-left variant.
  5. Click OK twice to return to the chart, shown in Figure K.

Figure K

A horizontal fill works well in a tall data point.

9: Try a preset

You might think you're done, but there's more to offer. Specifically, you might want to use a preset option (in the Color section). There's an orange preset that's interesting:

  1. Repeat items 1 through 4 in step 7.
  2. In the Color section, click Preset.
  3. From the Preset Colors drop-down list, choose Wheat.
  4. In the Shading Styles section, select the Vertical option and click the bottom-left variant.
  5. Click OK twice to return to the chart, shown in Figure L.

Figure L

The preset options offer even more variety!

10: Try one last trick

There's still one final trick: to apply the same gradient background across the entire series. The method is basically the same; where you start is the only difference. Instead of selecting just one data point, select them all. Remember, you want all three data points to display a single selection handle in the center, as shown in Figure M. Simply click inside any of the data points. The rest of the technique is similar:

  1. Right-click any of the points and choose Format Data Series.
  2. Click the Patterns tab and then click the Fill Effects button.
  3. In the Color section, choose Preset.
  4. From the Preset Colors drop-down list, choose Rainbow II.
  5. In the Shading Styles section, click Horizontal and then click the top-right variant.
  6. Click OK twice to return to the chart, shown in Figure N.

Figure M

Select the data series.

Figure N

Apply an effect to the data series.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

3 comments
Marshwiggle
Marshwiggle

Since I've seldom had clients that wanted charts, they have long been my weakest point in Excel, so thanks for starting from scratch.

mark.ivanowski
mark.ivanowski

Can we have a chart that changes color according to the value set without using any vba?

ssharkins
ssharkins

...but I'll shop around and see what I can come up with.