Conditional styles

This post focusses on the data bars which came as a new feature for conditional formatting in MSXL 2007. With some work it is possible to emulate them in the older versions, by the way without any conditional formatting at all. Sounds strange, huh? The picture below shows what we want to emulate:

Let's start by firing up MSXL 2003. Enter the data of columns A and B of the example image above into your new spreadsheet.

Select the range from A1 to B11 with the mouse and click the chart icon  or select Insert - Chart via the menu.

Click on the bar chart type and click finish.

Now right click the X-axis (vertical) - the cities - and from the scale tab choose "Categories in reverse order" to have a matching chart with our list of cities

Right click in your chart and select the chart options. Here you have to get rid of all the additional stuff as described in the post about custom made sparklines. For all the readers who don't want to go through the whole post from Thursday here is a short list of what to deactivate (or untick) ordered by the tabs where you will find the checkboxes:

• Titles: Empty the "chart title" text box
• Axes: Untick "Category (X) axis" and "Value (Y) axis"
• Gridlines: Untick "Major Gridlines" in the "Value (Y) axis" section
• Legend: Untick the "Show legend" checkbox

Click OK in the chart options dialog box.

Right-click in the "grey" area of your chart and from the context menu select "Format Plot Area". Select the "None" radiobuttons for both the Border and the Area section

Now right-click on one of the data bars (only ONCE!) and select Format Data Series. Choose None for the border. Click the Fill effects button in the Area section and in the appearing window select "Two colors" from the Colors section. The first color could be one of your choice. I choose the RGB value of 255 / 85 / 90 to get as close to the MSXL 2007 original color as possible (see the posts on how to add custom colors - text / video - to see how you can adjust the MSXL color tables). Make sure, that the second color is white (!) because we want to have this nice little fade effect. I use a second color with the values of 255 / 221 / 222 which should result in a similar effect as with MSXL 2007 (Thanks to Jon Peltier for the hint in the comments!:-) ) From the Shading styles radiobuttons select the vertical one and click the top left example in the variants section (see screenshot below).

After confirming these settings with OK you should be still in the Format Data Series dialog box. Choose the Options dialog box and set the Gap width to something like 20 which will reduce the gaps between data bars. Adjust this setting to your needs.

Now right-click as close to the border as possible on your chart and choose Format Chart Area. This can be a little tricky as you have to make sure that you do not right-click the plot area but the chart area.

From the dialog box choose None in both sections, border and area.

Don't worry, we're nearly there

Now click the chart and make sure that you have the eight little black squares that are used for resizing your chart (not the one with the dotted line). Cut your chart by using CTRL+X or the scissors icon and move the chart to a new / another tab, pasting it there with CTRL+V.

Because we will work with the camera tool again as in the sparklines post - switch of the grid. This is done by going to Tools - Options and deselect the Gridlines checkbox in the Windows section of the View tab (lower third). Make sure that you have deselected your chart prior to that, otherwise this option will not be available.

Select the cells "below" your chart and make sure that you have all of the chart "in it". Click the camera tool (refer to the sparklines post mentioned above if you don't have it available). Go back to the tab / spreadsheet with your table and click once besides the table to "insert" the picture of your chart. Right click the picture and in the Colors and Lines tab, select "No Fill" for the Fill section and "No Line" for the - you guessed it - Line section.

With the picture still active, make sure that your picture toolbar is available. Use the crop tool to do get rid of unnecessary white space in the picture of the chart. Now move the picture to the desired location and adjust it in size just as you would do with any other picture. This is it... :-) See my result below:

I must admit, it is not totally the same, but hey, it comes close, what do you think? If you want me to make a video of this post, leave a comment (I think it's a good topic for a video explanation). If you would like to play around with my file, here is it: Emulate ConditionalFormatting (ZIP - 14 kB).

Discuss this blog post in the forums (click the blue button)