Invert if Negative in Excel 2007

In Invert Excel Chart Format if Negative I showed the technique that allows you to select which colors for positive and negative points in a column or bar chart. This technique works in Excel versions up to and including Excel 2003, but doesn’t work in Excel 2007. Up until now I thought you couldn’t get two colors of your own choosing in Excel 2007 unless you used two series, or colored each point manually or programmatically based on its value.

Column Chart with Invert if Negative Setting

Well, I was wrong. Invert if Negative can be used with any two arbitrary colors in Excel 2007. You just need to know the trick.

In my Area Chart – Invert if Negative post, I went through a convoluted protocol to color an area chart differently for positive and negative areas. In a comment under that post, alert reader Vincent wrote the following, speaking of column and bar charts:

I have been looking for an option in Excel 2007 to do the invert if negative option just like I did in 2003. On every website it states that it cannot be done unless you use two series. I couldn’t.

Then I found this:
http://www.hichert.com/de/community/foren?func=view&catid=6&id=150

It works! And I like to share this with all of you looking for this solution!

The protocol uses Excel 2007’s gradient fill formatting of the bars, with an unusual and nonintuitive gradient definition. This tutorial is a reworking of the technique described by Andrej Lapajne in the citation above.

The Chart

Let’s start with a simple column chart using arbitrary positive and negative values. Never mind for this exercise the category labels located in the negative bars.

Column Chart without Invert if Negative Setting

Invert if Negative in Excel 2007 Dialog

We can invoke Invert if Negative using the checkbox in the Fill panel of the Format Data Series dialog in Excel 2007.

Excel 2007 Dialog Showing Invert if Negative Setting

What we get is the same as the initial Invert if Negative chart in Excel 2003: the positive bars stay the same, the negative bars are filled with white. Excel 2007 adds the border, otherwise the bars would not be visible. The technique that adds a second color in Excel 2003 fails in Excel 2007.

Column Chart with Invert if Negative Setting

Gradient Fill Formats

In general, applying a gradient fill pattern in a chart adds that special kind of chart junk that attracts attention while obscuring the data. However, this technique requires the gradient feature, though by the end there will be no gradient, just two colors of bars.

Let’s make a simple gradient, defined by two “stops”, that is, the colors at the two endpoints. Select the Gradient Fill option, then set Stop 1 at position 0% to show the first color, green for positive.

Excel 2007 Dialog Showing Simple Gradient Step 1

Set Stop 2 at position 100% with the second color, red for negative.

Excel 2007 Dialog Showing Simple Gradient Step 2

What we get is the default gradient, changing gradually from green to red. Invert if Negative has been checked, so the positive and negative bars show the gradient in opposite directions.

Column Chart with Simple Gradient and Invert if Negative Setting

The Magical Gradient

I have to admit that I do not understand why this magical gradient works. If I did, I probably wouldn’t call it magical. Mr. Lapajne is either a genius or a madman, or perhaps he swiped the gradient from another genius or madman like I’ve swiped it from him. In any case, the gradient requires four stops, described below:

Gradient Settings for Invert if Negative

Where do the 1% and 99% stops come from? I don’t know, and it must have taken a madman or someone with finely honed obsessive-compulsive disorder to have determined them. For “fun”, I experimented with a wide variety of similar and different settings, and I would never have found the correct ones, even by accident.

The result is just what we hoped for, green bars for positive values, red for negative values.

Column Chart with Funky Gradient and Invert if Negative Setting

Why Haven’t I Been Writing?

Good question. I’ve been busy and distracted.

Last week I had the honor of addressing the 5th annual Excel Experience Day, sponsored by Dutch publishing and information services provider Kluwer. In the morning I spoke about making effective visualizations in Excel, dispensing with the usual tedious “Do this – Don’t do that” approach in favor of a discussion of human visual and cognitive systems. Knowing a bit about these systems makes it easier to understand the rules for effective visual presentations. In the afternoon I led a couple plenary sessions that dealt more specifically with what to do in Excel, following on my earlier discussion of human cognition, and getting into the rules. The day after Excel Experience Day was Excel Experts Day, where I split a day of hands-on training with my colleague and fellow Microsoft Excel MVP Jan Karel Pieterse, who has graced the Excel world with such useful, in fact, indispensable utilities as the Name Manager and AutoSafe. I had nothing but positive experiences in Holland and in all of my interactions with the Dutch people, despite having only a working knowledge of English and no ability to speak Dutch.

Over the past few months I have not spent my customary hour a day on my blog. It dawned on me recently that this hour has been spent practicing guitar, which I’m still not very good at, but which is still a lot of fun. I’m going to have to find another hour somewhere so I can do both. I’d sacrifice an hour of sleep, but already I’m not getting enough of that.

 

Peltier Tech Charts for Excel

Programming Excel 2007 and Excel 2010 AutoShapes with VBA (Guest Post)

Today I am pleased to present a guest post written by my colleague Nicholas Hebb. Nick appears online frequently, commenting on Excel-related blogs and corresponding in the various forums.

Programming Excel 2007 and Excel 2010 AutoShapes with VBA

By Nicholas Hebb

With the release of Office 2007, Microsoft rewrote the drawing tools from the ground up. On the plus side, this meant new shapes, new styles, and the addition of SmartArt. On the downside, they didn’t have time to incorporate AutoShape operations in their macro recorder prior to release. Thankfully, Excel 2010 has added macro support back, but like all macros the code generated is often bloated and relies heavily on the Selection object, which tends to hide the core objects in use. For the most part, the online help is good, but there is some information that doesn’t get explained in detail. This article attempts to provide a basic overview of working with AutoShapes using VBA and touch on some areas that are not covered extensively in the help documentation.

Definitions

Two properties of the Shape object will be used in the code samples below – Shape.Type and Shape.AutoShapeType. Excel has a broad range of shape Types consisting not only of AutoShapes, but also connectors, lines, pictures, charts, comments, and many other graphical items. For AutoShapes, the AutoShapeType property lets you get/set the type of shape as shown in the gallery image below.

Excel 2010 Autoshapes Gallery

Knowing when to check the Shape.Type property versus the Shape.AutoShapeType is very useful. For example, if the AutoShapeType value is -2, then for all practical purposes the shape is not an AutoShape. If the value is greater than 1, then the shape is one of the types display in the Shapes gallery. The tricky part comes when the AutoShapeType is 1, which equals the AutoShape constant msoShapeRectangle. It could be a Rectangle AutoShape, but it could also be anything shaped like a rectangle, such as a text box, a comment, or even a picture. So if the AutoShapeType evaluates to 1, then you also need to check the Type property.

Callouts are another special type of shape that can cause confusion. They are discussed more in the Miscellaneous Issues section below.

Accessing a Shape Object

Each worksheet contains a Shapes collection consisting of Shape objects. Like other collections in VBA, the Shape object is accessed either via its name or index number, as in:

ActiveSheet.Shapes("SHAPE_NAME")

or

ActiveSheet.Shapes(1)

Or, using the For…Each syntax:

Dim shp as Shape
For Each shp in ActiveSheet.Shapes
  MsgBox shp.Name
Next

Adding an AutoShape

The syntax for adding a shape is:

Worksheet.Shapes.AddShape(AutoShapeType, Left, Top, Width, Height)

The AutoShapeType is a constant that ranges from 1 to 137 for Excel 2003 and earlier versions. Excel 2007 added shapes 139 through 183. AutoShapeTypes 125-136 are special AutoShapes. The online help file states that they support mouse over and click events, but that only applies when they are used in PowerPoint presentations. You can use them in Excel but they don’t have any special properties.

To see what the AutoShapeType constant is for each AutoShape, you can copy and paste the following code into the Excel Visual Basic Editor and run it (or download the sample file and run the macro). Not all the AutoShapes are available in the Shapes gallery, so this will also give you a look at some of the hidden ones.

Sub CreateAutoshapes()
  Dim i As Integer
  Dim t As Integer
  Dim shp As Shape

  t = 10
  For i = 1 To 137
    Set shp = ActiveSheet.Shapes.AddShape(i, 100, t, 60, 60)
    shp.TextFrame.Characters.Text = i
    t = t + 70
  Next
  ' skip 138 - not supported
  If CInt(Application.Version) >= 12 Then
    For i = 139 To 183
      Set shp = ActiveSheet.Shapes.AddShape(i, 100, t, 60, 60)
      shp.TextFrame.Characters.Text = i
      t = t + 70
    Next
  End If
End Sub

The Left, Top, Width, and Height parameters of AddShape() are specified in points. The origin is the top left corner of the worksheet, with the Left and Top values increasing to the right and down, respectively. Dealing with points on a worksheet isn’t intuitive, so if you prefer you can add a shape to a given range address by using code like this:

Function AddShapeToRange(ShapeType As MsoAutoShapeType, sAddress As String) As Shape
  With ActiveSheet.Range(sAddress)
    Set AddShapeToRange = ActiveSheet.Shapes.AddShape(ShapeType, _
        .Left, .Top, .Width, .Height)
  End With
End Function

Adding Text to an AutoShape

The Shape object has both a TextFrame and TextFrame2 members. The TextFrame2 member was added in Excel 2007 and gives better control over the formatting of the text. Because it is not backward compatible, I would recommend using the TextFrame object, as shown in the following code.

Sub AddFormattedTextToShape(oShape As Shape, sText As String)
  If Len(sText) > 0 Then
    With oShape.TextFrame
      .Characters.Text = sText
      .Characters.Font.Name = "Garamond"
      .Characters.Font.Size = 12
      .HorizontalAlignment = xlHAlignCenter
      .VerticalAlignment = xlVAlignCenter
    End With
  End If
End Sub

Setting Border and Fill Styles

If you take advantage of the built-in styles for Excel 2007 and Excel 2010, setting the AutoShape formatting is ridiculously easy compared to Excel 2003 and previous versions. Excel 2007 introduced the ShapeStyle property with the 42 preset styles shown below.

The style numbers can be set using a simple line of code:

Shape.ShapeStyle = msoShapeStylePresetXX

Where Shape is the shape object and XX is the style number. The style numbers are shown in the image gallery in order from left to right, top to bottom. For example, the red button in the second row is msoShapeStylePreset10.

Adding Connectors and Lines

Connectors and lines are different objects in Excel. Connectors are special lines  that “connect” to shapes, and if the shape is moved the connector stays connected and reroutes accordingly. Connectors cannot connect to other connectors, but they can connect to the end point of a line.

The syntax for adding a line is straightforward:

Worksheet.Shapes.AddLine(BeginX, BeginY, EndX, EndY)

…with all coordinates as Singles. Adding a connector is a bit more complex, since you typically want it to connect two shapes. The code below calculates the begin and end points, creates the connector, attaches the connector to the two shapes, then finally does a reroute to ensure the shortest path.

Function AddConnectorBetweenShapes(ConnectorType As MsoConnectorType, _
    oBeginShape As Shape, oEndShape As Shape) As Shape

  Const TOP_SIDE As Integer = 1
  Const BOTTOM_SIDE As Integer = 3
  Dim oConnector As Shape
  Dim x1 As Single
  Dim x2 As Single
  Dim y1 As Single
  Dim y2 As Single

  With  oBeginShape
    x1 = .Left + .Width / 2
    y1 = .Top + .Height
  End With
  With oEndShape
    x2 = .Left + .Width / 2
    y2 = .Top
  End With
  If CInt(Application.Version) < 12 Then
    x2 = x2 - x1
    y2 = y2 - y1
  End If

  Set oConnector = ActiveSheet.Shapes.AddConnector(ConnectorType, x1, y1, x2, y2)
  oConnector.ConnectorFormat.BeginConnect oBeginShape, BOTTOM_SIDE
  oConnector.ConnectorFormat.EndConnect oEndShape, TOP_SIDE
  oConnector.RerouteConnections

  Set AddConnectorBetweenShapes = oConnector

  Set oConnector = Nothing 
End Function

Several points worth mentioning are:

  • The ConnectorType can be one of three constants – msoConnectorCurve, msoConnectorElbow, or msoConnectorStraight.
  • The calculations for the beginning and ending points are not normally needed. You could put any values in for the AddConnector() function because once you call BeginConnect and EndConnect, the connector is attached to the shapes and the begin and end points get set automatically.
  • How the end coordinates are specified is not consistent between Excel versions. Prior to Excel 2007, the end coordinates were relative to the begin coordinates. Starting in Excel 2007, the function now uses absolute coordinates.
  • When you route a Connector to an AutoShape, you need to specify the side using a connection site constant. The constants are different for each AutoShape type, but generally they start with the top side = 1 and go counter-clockwise. For example, most rectangular shapes have connection site constants where top = 1, left = 2, bottom = 3, and right = 4.
  • When you call the RerouteConnections() function, it sets the connection sides automatically in order to create the shortest path between the two shapes. So, unless you want a specific routing, you can get usually just guess at the connection site values then call RerouteConnections().

Formatting Connectors and Lines

Like AutoShapes, formatting Connectors and Lines is fairly straightforward in Excel 2007 and 2010. Here is a comparison of two formatting routines for older versions of Excel versus the newer versions:

Sub FormatConnector2003(oConnector As Shape)
  With oConnector
    If .Connector Or .Type = msoLine Then
      ' rough approximation of the Excel 2007 preset line style #17
      .Line.EndArrowheadStyle = msoArrowheadTriangle
      .Line.Weight = 2
      .Line.ForeColor.RGB = RGB(192, 80, 77)
      .Shadow.Type = msoShadow6
      .Shadow.IncrementOffsetX -4.5
      .Shadow.IncrementOffsetY -4.5
      .Shadow.ForeColor.RGB = RGB(192, 192, 192)
      .Shadow.Transparency = 0.5
      .Visible = msoTrue
    End If
  End With
End Sub

Sub FormatConnector2007(oConnector As Shape)
  With oConnector
    If .Connector Or .Type = msoLine Then
      .Line.EndArrowheadStyle = msoArrowheadTriangle
      .ShapeStyle = msoLineStylePreset17
    End If
  End With
End Sub

The Connector property, used above, returns a Boolean indicating whether the shape is a connector. The Type=msoLine statement checks if the shape is a line. In this case the code will format both connectors and lines the same way, but at times you may want handle them separately. (NB: The Insert Shapes gallery of Excel 2007 only lets you add Connectors, not Lines. So unless you are dealing with legacy files or add Lines via code, testing Type=msoLine may never be an issue for you.)

Like the shape styles, you can format the line style by setting the ShapeStyle to one of the msoLineStylePresetXX values, where XX matches the order they appear in the style gallery (below) from left to right, top to bottom.

The Line object has several other members worth mentioning. In addition to the EndArrowheadStyle shown above, there is a corresponding BeginArrowheadStyle property, a DashStyle property, and also a Style property that lets you create double lines.

Miscellaneous Issues

Here are a few Excel 2007 issues with AutoShapes that are good to be aware of :

  • If you copy a set of shapes, older versions of Office gave the option to Paste Special as editable AutoShapes in other Office applications. This option no longer exists in Office 2007. I haven’t tested this in Office 2010 beta yet.
  • In Excel 2007 and the 2010 beta, changing the AutoShape type will disconnect any incoming or outgoing Connectors to a shape.
  • Some print drivers (including PDF export handlers) do not handle printing thick Connectors well, e.g., elbow connectors may straighten. If this happens, either change the line thickness or try grouping all the shapes together prior to printing.
  • The Arc is an AutoShape but needs to be treated as a line when setting the ShapeStyle property.
  • Most of the styles are backward compatible except for styles 37-42 (the glossy button look in the bottom row of the style gallery). Styles 31-36 (the second row from bottom) do not render very well in Excel 2000.
  • You can add Callouts using the AddShape() or AddCallout() methods, but the AddCallout() method will only let you add four types, two of which are actually the same. Callouts have AutoShapeType values in the range of 105-124. Even though they have AutoShapeType values, the Shape.Type property will return msoCallout – not msoAutoShape. Confused? Wait, there’s more. The callouts with AutoShapeTypes from 105-108 actually return a Shape.Type = msoAutoShape – not msoCallout.

Sample File

The sample file includes three demo sheets. The ShapeDemo sheet contains a macro to add two shapes, format them, then add a connector and format it. The Animation sheet has a simple macro showing how to move a shape around the sheet. The CreateAutoShapes sheet has a macro to create all AutoShapes available in your version of Excel.

The ShapeDemo routine has two function calls that are commented out – IlluminateShapeText2003() and IlluminateShapeText2007(). These subs add some gaudy formatting to the first letter of each text block, but they serve to highlight some of the differences between Excel 2007 and previous versions. Two parts of the code worth looking at are the HasText property and the formatting properties of TextFrame2. With the old TextFrame object, you would have to try accessing the TextFrame.Characters.Count property, which throws an error if no text exists. As for the formatting, the font colors in Excel 2003 and previous were limited to the colors in the pallet. In Excel 2007 and 2010, you can add reflections, drop shadows, and glow as well as set the color to whatever RGB value your heart desires.

Lastly, there is a module MCommon containing a sub that deletes all the AutoShapes on a sheet. In order not to delete the command buttons on the sheet (which are shapes too), it creates a ShapeRange of the AutoShapes and deletes that. The online help file shows the syntax for creating a ShapeRange when you know the names of the shapes at design time, but the syntax is a bit tricky when creating one dynamically. The DeleteAllAutoShapes() sub in the sample file shows you how to do this.

Click Here to Download the Sample File

About the Author

FlowBreeze Flowchart SoftwareNicholas Hebb is the founder of BreezeTree Software and author of FlowBreeze Flowchart Software, an add-in that automates flowcharting in Excel.

 

Peltier Tech Charts for Excel

Error Bars in Excel 2007 Charts

Microsoft has made a number of changes to how you use error bars in Excel 2007. I wrote an article some time ago, which covered how to use Error Bars in Excel Charts, for versions 97 through 2003. Now it’s well past time for an update.

Warning: This is a long post. It contains a lot of pictures though to keep you entertained, and near the end there’s a cool utility you can download and use for free.

Review of Error Bars in Classic Excel

It’s easy to add error bars in Excel 97 through 2003. Bring up the Format Series dialog, by double clicking on the series, by right clicking on the series and choosing Selected Object, by selecting the series and choosing Selected Data Series from the Format menu, or by selecting the series and clicking the shortcut, Ctrl+1 (numeral one). The dialog has a tab for Y Error Bars, and if it’s an XY data series, there is also a tab for X error bars.

Classic Excel insert error bar dialog

From this dialog you can assign values to the error bars, such as a fixed value, a percentage of the data point value, a couple of statistical measures, and custom values from a worksheet range. You can also assign the direction of the error bars (plus, minus, both, or neither). One nice feature is that the custom error bar value selection controls are right on the same dialog as everything else.

To format error bars, you first select them, then bring up the dialog with a double click, a right click, the Ctrl+1 shortcut, or the Format menu. In the chart below I have clicked once, which selects both X and Y error bars in an XY chart.

Classic Excel default error bars

The dialog has three tabs. Any changes you make on the Patterns tab affects both X and Y error bars.

Classic Excel format error bar dialog

The other two tabs allow you to reapply error bar values.

Classic Excel format Y error bar dialog

Rather than selecting both sets of error bars in this chart, I have clicked once to select both X and Y error bars, then again to reduce my selection to just the Y error bars.

Classic Excel select just Y error bars

The Format Error Bars dialog now only has a Patterns tab and a Y Error Bars tab. If you change any format on the Patterns tab it only affects the error bars you selected before opening the dialog.

Classic Excel format just Y error bars

Changes to Error Bars in Excel 2007

It is harder to apply error bars in Excel 2007. There is no convenient tab on the Format Series dialog. The Error Bar tab(s) as well as the tabs for Data Label, Up/Down Bars, High/Low Lines, and other features have been removed to make them more discoverable, at least that’s what we were told. To discover these features in Excel 2007, select the chart and navigate to the Chart Tools > Layout contextual tab. Click on the Error Bars button, and scratch your head while you try to decipher the options.

Excel 2007 error bars in the ribbon

Finally, select the More Error Bars Options at the bottom of the list. X (if it’s an XY chart) and Y error bars are added to the chart series, with the Y error bars selected, and the Format Error Bars dialog is displayed with the Vertical Error Bars tab showing. (If the chart has more than one series, and you had not specifically selected one series, there is an intermediate dialog asking which series to work with.)

Excel 2007 format vertical error bars

This dialog doesn’t look too unfamiliar. There is no obvious way to switch to the horizontal error bars. We are used to having not just a tab for the vertical error bars, but also one for the horizontal error bars.

Hmm, oh yeah, modeless dialog. Click on the horizontal error bars in the chart, problem solved.

Excel 2007 format horizontal error bars

Unlike Excel 2003 and earlier, you cannot select both X and Y error bars in Excel 2007. This means that if you want the same formatting to both, you have to apply it twice, and the F4 function key shortcut for Repeat Last Action only repeats the last single itty bit of formatting (if you change line color, thickness, and dash type in that order, for example, only dash type is repeated).

An inconvenient part of the user interface for error bars in Excel 2007 is that you need to click a button to enter custom error bar values, and this pops up a small dialog. This dialog is modal, so if the Format Error Bars dialog was partially covering the range you want to use, you have to clear the small child dialog, move the main dialog, and bring up the Custom Error Bars dialog again.

Custom error bars are described in more detail in Custom Error Bars in Excel Charts.

Excel 2007 custom error bar select values dialog

By default, each field contains a one element array with the element value equal to one. You can enter another constant value, and you don’t need to type the equals sign or curly brackets; Excel will insert them. More likely you want to select a range. Make sure you delete the entire contents of the entry box before selecting a range, or at least select it all, or Excel will think you meant to enter something like

={1}+Sheet1!$D$5:$D$13

If you want the value to be zero, enter zero. Don’t completely clear an entry box. because Excel will think you simply forgot and it will retain the previous value.

Excel 2007 custom error bar select values dialog: ={1} OOPS!

For all of the problems, using error bars in Excel 2007 isn’t completely terrible. You have some nice formatting options available to you. One that appeals to me is an alternative to the regular end cap appearance of the error bars.

Excel 2007 error bars with default caps

You can change to the end-cap-less variation, then format the error bar line style so they end with an arrow head.

Excel 2007 error bars with arrowheads

I don’t remember if this is the most useful, or the only useful, new formatting feature available in Excel 2007 charts.

Error Bar Utility

To make it easier to use error bars in Excel 2007 (and in Classic Excel), I’ve built a small utility, which you can download and use for free. It’s found in ErrorBars.zip. This zip file contains two versions, ErrorBars.xls for Excel 97 through 2003, and ErrorBars.xlam for Excel 2007. Install this utility by following the instructions in Installing an Excel Add-In or in Installing an Add-In in Excel 2007.

In Classic Excel, the utility places a new item, Add Error Bars, at the bottom of the chart series context menu. All you have to do is right click on the series and select Add Error Bars.

Peltier Tech error bar utility in Classic Excel

Despite all the assurances that context menus work the same in Excel 2007 as in earlier versions, you cannot add an item to an Excel 2007 chart-related context menu. What I’ve done instead is to add an Error Bars item to the end of each of the three Chart Tools contextual ribbon tabs. I know the new philosophy of Office is to place a command in only one place in the whole user interface. I prefer the old style philosophy, however, which is to place the command in every place it may be relevant. I never know where I may be when I want to use a command, and some people remember different hiding places than I do.

Peltier Tech error bar utility on Excel 2007 ribbon

Whether in Excel 2007 or in earlier versions, click on the added command, and the utility behaves the same. Up pops a simple dialog with four data entry boxes, for plus and minus Y error bars, and for plus and minus X error bars.

Peltier Tech error bar utility dialog for XY chart

If the chart type is not XY, the X error bar entry boxes are disabled.

Peltier Tech error bar utility dialog for non-XY chart

You can select a range or enter a constant into the entry boxes.

Peltier Tech error bar utility dialog: select range or enter value

I hope that this tutorial and the associated utility will make your life easier when working with error bars in Excel 2007.

 

Peltier Tech Charts for Excel

Excel 2007 Regression Error – Fixed in SP1

In Calculation Bug Fixed, an old but ongoing thread in the Daily Dose of Excel blog, I mentioned a problem with Excel 2007’s trendline regression formulas. Apparently small regression coefficients were treated like small errors that occur in the insignificant digits when converting from binary to decimal. I had documented the error in May of 2007, and while following up in Daily Dose, I tested in Excel 2007 SP1, and the error has been corrected. I’m here not to raise unnecessary alarms among users who have already updated to SP1, but to report correction of this problem, and to describe the errors for those who have still not updated. [Read more…]

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0