VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User

In a recent Mr Excel post, a member asked how to convert a CSV File to Excel File with Dynamic Graphing Capability. This is a great topic for a tutorial, but too long for a forum answer.

Problem Statement

The simple statement was this:

  • Convert a CSV file to an Excel workbook
  • Create a chart based on
    • User-specified chart type
    • User-specified columns for X and Y

I expanded on this a little bit:

  • Open a user-selected CSV file
  • Save as an Excel workbook
  • Display dialog for user to select
    • Chart type
    • Columns containing X and Y values for chart
  • Create the desired chart

This is a pretty easy project, which would generally take me half a day or less, depending on other distractions.

Approach

Essentially, I started with this simple VBA procedure, and a similarly simple UserForm (dialog).

Sub OpenCSVFileAndPlotData()
' 1. Get CSV file name
' 2. Open CSV file
' 3. Save as workbook
' 4. Parse file
' 5. Show dialog (select chart type, X values, Y values)
' 6. Draw chart
' 7. Save file
End Sub

My first time through development, I did the least I had to do to make it work. The second time through, I added a bunch of nice embellishments that should make things easier for users to just pick it up and run with it.

For both levels, the following sections have pieces of code describing what is happening, the complete code, and an example workbook.

Building a Simple Solution

Code Workbook

I’m using a macro-enabled Excel workbook named My_CSV_Data_Processor.xlsm. It has a single worksheet names Parameters.

Saved Information: Named Range Containing Chart Types

The worksheet contains a list of chart types that our program will create. These are the simplest and most useful charts built into Excel. The list has two columns, the first being the integer values of Excel’s chart type constants (e.g., xlArea = 1, xlXYScatter = -4169), the second being the chart type names that will appear in the dialog.

The range A2:B12 has been named ChartTypes, so it will be easy for the code to put these values into the dialog.

Starting the Program: Form Control Button

To add a button, the Developer tab must be visible on the ribbon. If you don’t see it, right-click on a blank place in the ribbon, choose Customize the Ribbon. In the list on the right (Main Tabs), click the box in front of Developer.

On the Developer tab, in the Controls group, click the Insert dropdown, then under Form Controls, click on the first icon, Button. Draw a button on the worksheet.

A dialog will pop up asking which VBA procedure to assign to the button; you can revisit this dialog by right-clicking on the button. Select OpenCSVFileAndPlotData and press OK. Then type a caption on the button and resize it as needed.

Code Module

We need to put the code into a regular code module. In the VB Editor, find the workbook’s project in the Project Explorer pane, right click anywhere on it, and choose Insert > Module. When a new code module opens up in the editor, rename it to something useful like MChartFromCSVFile using the Properties pane. The exact name you use isn’t critical, but it should be somewhat descriptive.

On the View menu, click Project Explorer (Ctrl+R) or Properties (F4) if these panes are not visible.

Getting Ready

The first line of the module should be

Option Explicit

If this statement doesn’t appear, type it in, then go to Tools menu > Options, and check the Require Variable Declaration box. While you’re in the Options dialog, uncheck Auto Syntax Check.

After a blank line or two, type Sub and the name of the procedure. The VB Editor automatically skips another line and types End Sub for you:

Sub OpenCSVFileAndPlotData()

End Sub

The main program will be typed between Sub and End Sub. All the variables will be declared at the top using Dim statements. Variables don’t need to be declared at the top of a routine, but VBA offers no just-in-time benefit to declaring them right where you start using them, and I like having them in one place so I can find their declarations easily. In the sections below I will insert a simple Dim statement for the variables used.

1. Get CSV File Name

We can use Excel’s GetOpenFileName to allow the user to select a CSV file from his computer.

  Dim sCSVFullName As String
  sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
      "Select a CSV file", , False)

We’ve specified the CSV file filter, indicated a title for the Open File dialog, and said False to selecting multiple files.

2. Open CSV file

This part is easy, we just need to open the file named in the previous step. Then we’ll set a workbook object variable to this file, so it’s easy to reference later in the code.

  Dim wb As Workbook
  Workbooks.Open sCSVFullName
  Set wb = ActiveWorkbook

3. Save as workbook

We need to remove the file extension from the CSV file name (which includes the file path), which we do by retaining only text up to the last period in the file name. Then we add the Excel workbook file extension, and save the file, specifying the default workbook format.

  Dim sWbkFullName As String, sFileRoot As String
  sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
  sWbkFullName = sFileRoot & ".xlsx"
  wb.SaveAs sWbkFullName, xlWorkbookDefault

4. Parse file

Now the file is open, so we’ll extract some information to place in the dialog. We’ll use a two-column display, where the first column has the column number, and the second a simple comma-delimited list of the first few values in that column.

  Dim ws As Worksheet, rng As Range, vRng As Variant
  Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long
  Dim sTemp As String
  Dim vChartData As Variant

  Set ws = wb.Worksheets(1)
  Set rng = ws.UsedRange
  vRng = rng.Value2
  iRows = rng.Rows.Count
  iCols = rng.Columns.Count
  
  '' info to display: column number, first few rows of column
  ReDim vChartData(1 To iCols, 1 To 2)
  For iCol = 1 To iCols
    vChartData(iCol, 1) = iCol ' column number
    sTemp = ""
    For iRow = 1 To 4 ' first 4 values
      sTemp = sTemp & vRng(iRow, iCol) & ", "
    Next
    sTemp = Left$(sTemp, Len(sTemp) - 2) ' remove last comma
    vChartData(iCol, 2) = sTemp
  Next

5. Show dialog

We need to instantiate the UserForm (i.e., load it into memory), pass in the array of column numbers and initial column values, and display the form. At this point, code stops and waits for the user to make his selections and dismiss the form.

When the form is dismissed, we need to get the user’s selections: chart type, whether the CSV file has header labels in the first row, and the columns to be used for X and Y values in the chart. Then we remove the UserForm from memory.

  Dim bFirstRowHeaders As Boolean
  Dim myChartType As XlChartType
  Dim vX As Variant, vY As Variant
  Dim frmChartFromCSVFile As FChartFromCSVFile

  Set frmChartFromCSVFile = New FChartFromCSVFile
  With frmChartFromCSVFile
    '' pass in information we know
    .ChartData = vChartData
    
    .Show
    
    '' get information selected by user
    myChartType = .ChartType
    bFirstRowHeaders = .FirstRowHeaders
    vX = .Xcolumns
    vY = .YColumns
  End With
  Unload frmChartFromCSVFile

6. Draw chart

We need to figure out how to separate the data into separate series, then we need to get the data into the chart.

In XY charts, we let the user create one or more series, where (a) all series share the same set of X values (or no X values if the user didn’t specify them, and Excel will use 1, 2, 3, etc. as X values) and each series has a unique set of Y values, (b) all series share the same set of Y values and each has a unique set of X values, or (c) each series has its own unique sets of X and Y values.

For other chart types, the only relevant combination of X and Y is (a), since Excel uses the same X values for all series regardless of how we specify them. We will deal with this in the Advanced version of this program.

Excel 2013 introduced AddChart2 as an improvement over Excel 2007’s AddChart method. AddChart is hidden in Excel 2013, but AddChart2 will crash Excel 2007 and 2010, so we will use AddChart. In the Advanced program, we will enhance the code to use Excel 2013’s improved default styles while still using the error-free AddChart method.

After adding the chart, we add one series at a time, adding its Y values, X values, and name separately.

  Dim iX As Long, iY As Long, iSrs As Long
  Dim nX As Long, nY As Long, nSrs As Long
  Dim rCht As Range, cht As Chart, srs As Series

  '' define some series parameters
  If IsEmpty(vX) Then
    nX = 0
  Else
    nX = UBound(vX, 1) + 1 - LBound(vX, 1)
  End If
  nY = UBound(vY, 1) + 1 - LBound(vY, 1)
  nSrs = nY
  If nX > nY Then nSrs = nX
  If bFirstRowHeaders Then
    Set rCht = rng.Offset(1).Resize(iRows - 1)
  Else
    Set rCht = rng
  End If
  
  '' select blank cell before inserting chart
  rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select
  
  Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+
  
  '' chart type
  With cht
    If myChartType <> 0 Then
      .ChartType = myChartType
    End If
    
    '' add series
    For iSrs = 1 To nSrs
      Set srs = .SeriesCollection.NewSeries
      With srs
        ' X values
        If nX = 0 Then
          ' no X values specified
        ElseIf nX = 1 Then
          ' all series share X values
          .XValues = rCht.Columns(CLng(vX(0, 0)))
        Else
          ' each series has unique X values
          .XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
        End If
        ' Y values
        If nY = 1 Then
          ' all series share Y values
          .Values = rCht.Columns(CLng(vY(0, 0)))
        Else
          ' each series has unique Y values
          .Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
        End If
        ' series name
        If bFirstRowHeaders Then
          If nSrs = nY Then
            .Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))). _
                Address(True, True, xlA1, True)
          ElseIf nSrs = nX Then
            .Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))). _
                Address(True, True, xlA1, True)
          End If
        End If
      End With
    Next
  End With

7. Save file

Simple: save changes.

  wb.Save

UserForm

Right click on the workbook’s project in the Project Explorer pane, click Insert > UserForm. When the UserForm appears, give it the name FChartFromCSVFile in the properties pane. This name is descriptive, and is how your code references the UserForm.

UserForm Controls

The Userform contains the following important controls, with the important properties shown below:

btnOK – OK button
Default: True

lstChartType – Listbox with two columns, one hidden.
ColumnCount: 2
ColumnWidths: 0 pt;112 pt
Width: 120
Height: 150

lstChartData – Listbox with two columns, extended multiselect
ColumnCount: 2
ColumnWidths: 42 pt;145 pt
Width: 195
Height: 150
MultiSelect: 2 - fmMultiSelectExtended

lstX, lstY – simple listboxes

lblbtnX, lblbtnY, lblbtnReset – “Label Buttons” or Labels with raised appearance (like regular buttons)
SpecialEffect: 1 - fmSpecialEffectRaised

chkFirstRowHeaders – Checkbox

Plus a few labels that help the user understand the dialog.

UserForm Code

Right click on the UserForm in the Project Explorer, and click on View Code. The Dialog’s code module will appear. Much of this code responds to events on the UserForm, events like clicking buttons. This code also includes properties, which allow the calling procedure to pass information into the UserForm and get information back from it.

As with the regular code module, the UserForm module should begin with

Option Explicit

If a control on the UserForm is to have code associated with it, double-click on the control and the VB Editor will insert a short stub of code into the module. For example, if you double-click on the OK button (named btnOK), the Editor will insert this:

Private Sub btnOK_Click()

End Sub

To make the code useful, we only need to insert our statements within this stub.

When the user clicks the OK button, we want the form to be hidden but remain in memory:

Private Sub btnOK_Click()
  Me.Hide
End Sub

When the UserForm is first loaded, we want the information from the named range ChartTypes to appear in the listbox lstChartTypes. The UserForm_Initialize code runs when the UserForm is loaded, and the code shown here does the magic:

Private Sub UserForm_Initialize()
  Dim vChartTypes As Variant
  
  vChartTypes = ThisWorkbook.Names("ChartTypes").RefersToRange.Value2
  Me.lstChartTypes.List = vChartTypes
End Sub

The button-formatted labels need some simple code attached, so that clicking them will populate the X and Y column lists. Click the lblbtnX label button to populate the lstX listbox:

Private Sub lblbtnX_Click()
  Dim iLst As Long
  
  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Me.lstX.AddItem iLst
    End If
  Next
End Sub

Click the lblbtnY label button to populate the lstY listbox:

Private Sub lblbtnY_Click()
  Dim iLst As Long

  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Me.lstY.AddItem iLst
    End If
  Next
End Sub

Click the lblbtnReset lable button to clear the X and Y listboxes and start over:

Private Sub lblbtnReset_Click()
  Me.lstX.Clear
  Me.lstY.Clear
End Sub

We need the ChartData property to pass the information to display in the lstChartData listbox of the UserForm:

Public Property Let ChartData(vData As Variant)
  Me.lstChartData.List = vData
End Property

We also needproperties to let us extract information from the UserForm: whether the first row of the CSV file has header labels, the selected chart type, and the X and Y columns to be plotted:

Property Get FirstRowHeaders() As Boolean
  FirstRowHeaders = Me.chkFirstRowHeaders.Value
End Property

Public Property Get ChartType() As XlChartType
  With Me.lstChartTypes
    If .ListIndex > -1 Then
      ChartType = CLng(.List(.ListIndex, 0))
    End If
  End With
End Property

Public Property Get Xcolumns() As Variant
  Xcolumns = Me.lstX.List
End Property

Public Property Get YColumns() As Variant
  YColumns = Me.lstY.List
End Property

The Simple Tool

Code Module MChartFromCSVFile

Here is the complete listing of MChartFromCSVFile:

Option Explicit

Sub OpenCSVFileAndPlotData()
  Dim sCSVFullName As String, sWbkFullName As String, sFileRoot As String
  Dim wb As Workbook, ws As Worksheet, rng As Range, vRng As Variant
  Dim rCht As Range, cht As Chart, srs As Series
  Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long
  Dim sTemp As String
  Dim vChartData As Variant
  Dim bFirstRowHeaders As Boolean
  Dim myChartType As XlChartType
  Dim vX As Variant, vY As Variant
  Dim iX As Long, iY As Long, iSrs As Long
  Dim nX As Long, nY As Long, nSrs As Long
  Dim frmChartFromCSVFile As FChartFromCSVFile
  
  ' 1. Get CSV file name
  sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Select a CSV file", , False)

  ' 2. Open CSV file
  Workbooks.Open sCSVFullName
  Set wb = ActiveWorkbook
  
  ' 3. Save as workbook
  sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
  sWbkFullName = sFileRoot & ".xlsx"
  wb.SaveAs sWbkFullName, xlWorkbookDefault
  
  ' 4. Parse file
  Set ws = wb.Worksheets(1)
  Set rng = ws.UsedRange
  vRng = rng.Value2
  iRows = rng.Rows.Count
  iCols = rng.Columns.Count
  
  '' info to display: column number, first few rows of column
  ReDim vChartData(1 To iCols, 1 To 2)
  For iCol = 1 To iCols
    vChartData(iCol, 1) = iCol
    sTemp = ""
    For iRow = 1 To 4
      If iRow > iRows Then Exit For
      sTemp = sTemp & vRng(iRow, iCol) & ", "
    Next
    sTemp = Left$(sTemp, Len(sTemp) - 2)
    vChartData(iCol, 2) = sTemp
  Next
  
  ' 5. Show dialog (get chart type, X values, Y values)
  Set frmChartFromCSVFile = New FChartFromCSVFile
  With frmChartFromCSVFile
    '' pass in information we know
    .ChartData = vChartData
    
    .Show
    
    myChartType = .ChartType
    bFirstRowHeaders = .FirstRowHeaders
    vX = .Xcolumns
    vY = .YColumns
  End With
  Unload frmChartFromCSVFile
  
  ' 6. Draw chart
  '' define some series parameters
  If IsEmpty(vX) Then
    nX = 0
  Else
    nX = UBound(vX, 1) + 1 - LBound(vX, 1)
  End If
  nY = UBound(vY, 1) + 1 - LBound(vY, 1)
  nSrs = nY
  If nX > nY Then nSrs = nX
  If bFirstRowHeaders Then
    Set rCht = rng.Offset(1).Resize(iRows - 1)
  Else
    Set rCht = rng
  End If
  
  '' select blank cell before inserting chart
  rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select
  
  Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+
  ''Set cht = ws.Shapes.AddChart2.Chart '' Excel 2013 only
  
  '' chart type
  With cht
    .ChartType = myChartType
    
    '' add series
    For iSrs = 1 To nSrs
      Set srs = .SeriesCollection.NewSeries
      With srs
        ' X values
        If nX = 0 Then
          ' no X values specified
        ElseIf nX = 1 Then
          ' all series share X values
          .XValues = rCht.Columns(CLng(vX(0, 0)))
        Else
          ' each series has unique X values
          .XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
        End If
        ' Y values
        If nY = 1 Then
          ' all series share Y values
          .Values = rCht.Columns(CLng(vY(0, 0)))
        Else
          ' each series has unique Y values
          .Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
        End If
        ' series name
        If bFirstRowHeaders Then
          If nSrs = nY Then
            .Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))).Address(True, True, xlA1, True)
          ElseIf nSrs = nX Then
            .Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))).Address(True, True, xlA1, True)
          End If
        End If
      End With
    Next
  End With
  
  ' 7. Save file
  wb.Save
  
ExitProcedure:
  
End Sub

UserForm Module FChartFromCSVFile

Here is the complete listing of FChartFromCSVFile:

Option Explicit

Private Sub btnOK_Click()
  Me.Hide
End Sub

Private Sub lblbtnReset_Click()
  Me.lstX.Clear
  Me.lstY.Clear
End Sub

Private Sub lblbtnX_Click()
  Dim iLst As Long
  
  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Me.lstX.AddItem iLst
    End If
  Next
End Sub

Private Sub lblbtnY_Click()
  Dim iLst As Long

  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Me.lstY.AddItem iLst
    End If
  Next
End Sub

Public Property Let ChartData(vData As Variant)
  Me.lstChartData.List = vData
End Property

Property Get FirstRowHeaders() As Boolean
  FirstRowHeaders = Me.chkFirstRowHeaders.Value
End Property

Public Property Get ChartType() As XlChartType
  With Me.lstChartTypes
    If .ListIndex > -1 Then
      ChartType = CLng(.List(.ListIndex, 0))
    End If
  End With
End Property

Public Property Get Xcolumns() As Variant
  Xcolumns = Me.lstX.List
End Property

Public Property Get YColumns() As Variant
  YColumns = Me.lstY.List
End Property

Private Sub UserForm_Initialize()
  Dim vChartTypes As Variant
  
  vChartTypes = ThisWorkbook.Names("ChartTypes").RefersToRange.Value2
  Me.lstChartTypes.List = vChartTypes
End Sub

Workbook with Working Code

You can download the simple workbook My_CSV_Data_Processor.xlsm to see all of this code in one place, and to see how it works.

Advanced Version

The next blog post has a number of enhancements that make things easier for the user and prevent various warning messages. Stay tuned…

 

Peltier Tech Chart Utility

Stick a Shape to a Point in an Excel Chart

This article was inspired by the Mr Excel forum. A member asked Charts: Any way to make a shape stick to a point in a scatterplot, not an absolute location on the chart?

The answer, of course, is Yes, but you have to know the trick, which I’m about to share. Not for the first time.

The Problem – Shapes Won’t Stick to Points in the Chart

Suppose I have some simple X-Y data:

With this data I make a simple X-Y chart:

Now I draw a couple shapes on the chart to highlight two points with larger values than the others.

So far, so good. But when I add some more data…

… the chart’s axes rescale. The points move to stay at the same numerical position along the axis, but the shapes stay where they were originally placed.

So now I have to drag those shapes around whenever the chart axis scales change.

The Solution – Making Shapes Stick to Points

There is actually a pretty simple way to make the shapes stick to the points. The trick is to add a second series to the chart, with data duplicating only the points you want to draw attention to, and use the desired shape as the markers for this series.

Here is the data, with a third column containing the Y values I want to highlight. Also visible is the shape I’ll use.

Here is the original X-Y chart, with blue circles as markers.

Here is the same chart with the second series added, as orange squares that indicate which points will be highlighted.

The trick is simple. Copy the shape, select the series that I want to use the shape as its markers, and paste using a simple Ctrl+V.

Now when I add data, including another highlighted Y value in column C…

… the chart’s axis rescales, but the highlighted points keep their highlighting shape, and the added Y value is highlighted as well.

Enhancements to the Technique

You could use formulas in column C to place values where highlighting is desired, and #N/A errors where it isn’t desired. Below the formula in cell C2 might be

=IF(B2>4,B2,NA())

or better, the threshold for highlighting might be placed into another cell, say E2, to make it easier to modify the highlighting:

=IF(B2>$E$2,B2,NA())

These formulas are then copied down column C as far as needed.

You don’t even need to draw a special shape if all you want to do is circle selected points. Instead of copying the shape and pasting it onto a series, simply format the series to use large circles as markers. Below the circular markers are size 17, with 2.25 point borders and no fill.

Peltier Tech Update

It’s been a busy few months for me, and I haven’t blogged very much lately. I spent a week in Amsterdam at the Excel Summit, where I met a lot of smart Excel users and Excel MVPs for the first time, and caught up with some old friends. I also did some on-site Excel VBA training for a couple companies, and this has reminded me that I want to do more activities like that.

I’ve also spent some time planning and working on new Peltier Tech products and services. Excel 2016 for both Windows and Macintosh will be released by Microsoft in the next several months, and I will release a major upgrade to the Peltier Tech Chart Utility.

Since traveling to training sites is a pain for me and for trainees, I’ve begun work on some live hour-long video training topics. Topics will include “Tricks to Make Excel Charting Less Painful” (based on a session at the Amsterdam Excel conference), “Getting Started with Excel VBA” (based on my recent training workshops), and advanced topics in charting, programming, and programming charts.

Peltier Tech Chart Utility

Removing Recent Colors from Microsoft Excel

Recent Colors

When you customize a color and apply it to an object in Microsoft Office, the color is stored as part of the Office file, and presented in a “Recent Colors” category on the various colors dropdowns. Up to ten of these custom colors are displayed, with the most recent at the left of the list and the oldest moving further to the right until a new color pushes it off the list.

The screenshot below shows three custom colors applied to cells B4:B6 of a worksheet, and where they appear in the color dropdown. The pink color was the latest applied, so it is furthest left. It is highlighted with a thin red outline because it is also the fill color of the active cell.

The numerical and alphanumeric codes in columns C and D are the decimal and hexadecimal representations of these colors. They were generated using the following VBA procedure:

Sub OutputColorInfo()
  Dim c As Range
  
  For Each c In Selection.Cells
    c.Offset(, 1).Value = c.Interior.Color
    c.Offset(, 2).Value = WorksheetFunction.Dec2Hex(c.Interior.Color)
  Next
End Sub

Colors are the combination of the three color channels (red, green, and blue), and each channel can have a value between 0 and 255 (or between 0 and FF in hexadecimal notation). The yellow color above has a blue channel value of 66, and red and green channel values of FF, so the code for this yellow is 66FFFF. Note that VBA presents the channels in BGR order, but it’s still called “RGB”.

A customer asked how to get rid of some but not all recent colors used in Excel. This is not too hard to do, but you should remember that any time you format an object with a new custom color, this new color becomes a new Recent Color.

Where are Recent Colors Stored?

Recent Colors appear in the color dropdowns only when the file they were defined for is the active file in its Office application (in this case, the active workbook in Excel). This means they must be stored somewhere within the document.

The first step in modifying the Recent Colors is to close the Office file. Below we see the workbook “recent colors.xlsm” in a Windows Explorer window.

Office files are really zip files which contain xml and other files within a well-defined directory structure. You can access this structure by simply adding the file extension “.zip” to the file name, and dismissing the warning about changing file extensions.

Double click the zip file to see the top-level structure of the file.

Double click on the “xl” directory within the file.

Finally, copy the “styles.xml” file, navigate out of the zip file to the directory the Office file was saved in, and paste the xml file here.

Open “styles.xml” in your favorite text editor. I use Notepad++, which can apply syntax-based color coding to files it recognizes. This is the entire content of my particular styles.xml file. Note the <mruColors> tag near the end of the file. This contains three <color rgb="blahblah"> tags that contain the definitions of our MRU (Most Recently Used) colors.

Changing Recent Colors

Microsoft Office doesn’t bother making the XML easy to see by using line feeds and spaces, but you can go ahead and apply your own, as I’ve done below.

Note that the RGB values in the <color> tags begin with an extra FF (for the transparency channel), and list the red, green, and blue pairs in the RGB order, not the BGR order produced by our VBA routine above. No big deal, Office uses the XML RGB for its user interface and VBA uses BGR, and they don’t talk to each other, so they don’t confuse each other, they only confuse us.

Delete the <color> tag(s) you don’t want (you could also change the color or add new ones if you can determine the desired RGB values). Save the xml file, and copy it back into the “xl” folder in the zip file. Delete the “.zip” extension you added to the Office file earlier, so it is left with its original Office extension (“.xlsm” for our workbook), and reopen the file.

The screenshot below shows the three colored cells, but only the two non-pink Recent Colors in the dropdown. We have successfully removed the recent color from the workbook’s remembered list, but we haven’t removed the color from any objects we formatted with it.

Note that reapplying the pinkish custom color to any object in the workbook will reestablish it as one of our Recent Colors.

Thanks to PowerPoint MVP Steve Rindsberg for showing me the <mruColors> tag I was too busy to notice the first time I looked. Check out Steve’s PowerPoint FAQ Pages and his PPTools – PowerTools for PowerPoint.

Peltier Tech Chart Utility

Select Data to Display in an Excel Chart With Option Buttons

The Problem

A member of the MrExcel forum wanted to know about using form controls to select columns from a data table to populate a graph. Essentially he wanted his users to be able to choose between two charts, one comparing their scores to district and regional scores, the other comparing their scores to the top 20%. This is a retelling of my suggested approach to his question.

Here is some dummy data I used for his problem, and a chart showing all of the data.

Data and Preliminary Line Chart

My suggested approach will add some columns (F:I) to the existing data range with formulas that show or don’t show data based on which of two option buttons is selected, and my chart will use all of this data. When the formulas don’t show the values, they will not appear in the chart.

The Data

The extended data range is shown below. At first the formula in cell F2 (and copied into the entire range F2:I13) is simply

=B2

To give the user a choice, add two option buttons and label them Chart A and Chart B. Right click on one of them (doesn’t matter which) and select Format Control, and on the Control tab, click in the Cell Link box, and select cell K1. This cell shows which of the buttons is selected, 1 or 2.

Extended Data and Option Buttons

You don’t need to show all of this to the user. You could put the chart and option buttons on the active sheet, and all of the data (and the option button linked cell) can go onto another sheet, and you can hide this other sheet if you want. Or you can place the original data on the same sheet as the chart and option buttons, and the formulas onto another sheet, a hidden sheet if desired. You could replace the two option buttons with one listbox that shows both options; the linked cell works the same way.

The Chart

Set up the chart first, before adjusting the formulas to show the result of the option button selection.

Select A1:A13, then hold Ctrl while selecting F1:I13, and insert a line chart.

Preliminary Line Chart

Delete the legend, because it will always show all series, even if some don’t appear because of the formulas. Instead, label the last point in each series with the series name, and use label text that matches each series color. This is more user-friendly than using a legend, anyway. (I also shortened the axis tick labels so they could all be displayed horizontally.)

Cleaned Up Line Chart

The Formulas

Now adjust the formulas. Keep the formulas in column F the way they are, since this series appears whichever option is selected. The formula in G2 (copied into G2:H13) is

=IF($K$1=1,C2,NA())

This means if the first option button is selected, show the corresponding value, otherwise show #N/A. #N/A isn’t plotted in a line or scatter plot.

Similarly the formula in I2 (and filled into I2:I13) is

=IF($K$1=2,E2,NA())

That is, if the second option button is selected, show the value, otherwise show #N/A.

The Finished Product

Here is the data and chart if the Chart A option button is selected:

Data and Chart 'A'

Here is the data and chart if the Chart B option button is selected:

Data and Chart 'B'

You can download a sample workbook by clicking on this link: MrExcelOptionButtonChart.xlsx.

Peltier Tech Chart Utility

Floating Bars in Excel Charts

Floating bars can be used to plot many types of data sets. (“Bars” in this usage means “bars”, as Excel calls horizontally oriented bars, as well as “columns”, as Excel calls vertically oriented bars.) In a waterfall chart, floating bars (usually vertical) show how contributing factors affect a cumulative total. In a Gantt chart, horizontal floating bars along a horizontal date scale help program managers plan task start and end dates and durations, and track progress towards completion of these tasks. Floating bars can be useful to show running highs and lows in a data set, such as daily high and low temperatures, stock prices, diastolic and systolic blood pressure readings, etc.

There are numerous ways to create floating bars in an Excel chart. There are so many ways that I should write more than one post, but I’m going to cram them all into this one. I’ve divided the techniques into the following:

  • Stacked Column and Bar Charts
  • Line Chart Tricks
  • Error Bars
  • XY Chart Line Segments

Stacked Column and Bar Charts

Stacked column and bar charts are probably the most obvious way to create floating bar charts. This approach is pretty flexible, and allows individual floating bars to be formatted differently, but will require some calculations to get the bars to appear as desired.

Stacked Column Charts (Vertical Bars)

This tutorial will show simple floating columns, stacked floating columns, floating columns that span the horizontal axis, and overlapping floating columns, all using stacked column charts.

Floating Columns

In the data set below, there are several high and low values for the categories in a column chart. The clustered column chart shows the values we want to highlight: we want a floating column to connect each low value to its corresponding high value.

We achieve this by inserting a column in the worksheet which has a simple formula to calculate the difference between high and low (“Delta” in the table below). Adjusting the data range and changing from clustered to stacked columns shows all we need. The floating column is resting on top of the Low value column.

A little formatting gives us all we need. The Low series is formatted to be invisible: no border and no fill. The vertical axis has been rescaled to zoom in on the data and remove some of the white space below the floating bars.

You can change the relative width of the columns and gaps between by selecting them and changing the Gap Width property; a Gap Width of 100 means that the gap will be 100% as wide as a bar. I like to use gap widths of 50% to 100%, and I used 75% in most of the charts here.

With this technique, each column can be selected (it may take two single clicks) and formatted independently of the rest, for purposes of highlighting one or more specific values.

The gold and purple colors above may show extreme highlighting, and were selected to clearly show the different colors. In general, subtle highlighting, like the slightly darker shade of point C or the outlined column of point F are usually sufficient.

Stacked Floating Columns

The stacked column chart allows multiple items to be stacked, since each floating column rests on the lower columns. This table and chart show low, medium, and high values.

Insert two columns for the two sets of calculations of floating column heights, and plot these with the minimum value.

As before, format the lowest column to be invisible, and adjust the axis scale, if desired.

As before, individual columns can be formatted independently of the others.

Floating Columns Crossing the Horizontal Axis

If you want to show floating columns that span negative and positive values, you will encounter problems, as shown by this sample data. It all looks fine when we examine the unstacked columns that show the minimum and maximum values.

However, when we plot the minimum values and stack the differences on top, we see that the stacking doesn’t work the way we would have liked. Excel plots columns with negative values below the X axis and columns with positive values above the X axis. Even though the Delta begins below the X axis, the Delta column has a positive value, and is plotted starting at zero or at the top of the minimum, if that value is positive.

Our simple formulas are not adequate, and we need a different approach. We’ll add three columns to the data sheet: one for the blank columns on which the floating columns will rest, one for whatever part of the floating column is positive (above the X axis), and one for whatever part of the floating column is negative. Using pseudo-references, the formulas we need are:

Blank:  =IF(High<0,High,IF(Low>0,Low,0))
  Pos:  =IF(High>0,High-MAX(Low,0),0)
  Neg:  =IF(Low<0,Low-MIN(High,0),0)

When we plot these values, we get the floating columns spanning the ranges we expect. Note that the floating columns may consist of two pieces, one (orange) below and one (blue) above the X axis, if necessary separated from the axis by the blank series (shown gray in the chart below).

As always, format the blank series to be blank (no border and no fill), and format the floaters as desired.

As before, individual floating columns can be formatted independently; the positive and negative portions can be formatted the same or differently.

Overlapping Floating Columns

You may want to show two sets of floating columns. For example, you may want to compare high and low temperatures for a set of dates with the average historical high and low temperatures for those dates. The way to handle this is to have one set of data on the primary axis, and the other set on the secondary axis.

The table and chart below show two sets of high and low values. The blue will eventually be shown on the primary axis and the orange on the secondary.

Insert two columns in the sheet, to capture the differences between high and low in the two sets of data. Here are the low and delta of each set in a stacked column chart.

Here is the same stacked column chart, with the orange series moved to the secondary axis. Each axis has its own Gap Width setting. Here I’ve used 50 for the primary axis (blue columns in back), and 150% for the secondary axis (orange columns in front).

More formatting: Hide the low columns (no border or fill) and adjust the Y axis. Also, delete the secondary vertical axis and if present the secondary horizontal axis. The chart will keep the series for each axis separate (so they have separate gap widths and so they don’t go stacking on each other), but will plot them on the primary axis scales.

Individual columns can always be formatted separately.

Stacked Bar Charts (Horizontal Bars)

The techniques described above for Vertical Column Charts work the same for Horizontal Bar Charts. This tutorial will show simple floating bars, stacked floating bars, floating barsthat span the vertical axis, and overlapping floating bars, all using stacked bar charts.

Floating Bars

In the data set below, there are several high and low values for the categories in a bar chart. The clustered bar chart shows the values we want to highlight: we want a floating bar to connect each low value to its corresponding high value.

To get the vertical axis labels in your chart to be in the same top-to-bottom order as in the worksheet, follow the approach described in Why Are My Excel Bar Chart Categories Backwards? and in Excel Plotted My Bar Chart Upside-Down.

Note also that in an Excel Bar Chart the vertical axis is the X axis (for the independent variable), and the horizontal axis is the Y axis (for the dependent variable). This confuses a lot of people, so I usually stick to “horizontal” and “vertical” instead of “X” and “Y”.

We achieve this by inserting a column in the worksheet which has a simple formula to calculate the difference between high and low (“Delta” in the table below). Adjusting the data range and changing from clustered to stacked bars shows all we need. The floating bar is resting to the right of the Low value bar.

A little formatting gives us all we need. The Low series is formatted to be invisible: no border and no fill. The horizontal axis has been rescaled to zoom in on the data and remove some of the white space to the left of the floating bars.

You can change the relative width of the bars and gaps between by selecting them and changing the Gap Width property; a Gap Width of 100 means that the gap will be 100% as wide as a bar. I like to use gap widths of 50% to 100%, and I used 75% in most of the charts here.

With this technique, each bar can be selected (it may take two single clicks) and formatted independently of the rest, for purposes of highlighting one or more specific values.

Stacked Floating Bars

The stacked bar chart allows multiple items to be stacked, since each floating bar rests on the lower bars. This table and chart show low, medium, and high values.

Insert two columns for the two sets of calculations of floating bar lengths, and plot these with the minimum value.

As before, format the lowest bar to be invisible, and adjust the axis scale, if desired.

As before, individual bars can be formatted independently of the others.

Floating Bars Crossing the Vertical Axis

If you want to show floating bars that span negative and positive values, you will encounter problems, as shown by this sample data. It all looks fine when we examine the unstacked bars that show the minimum and maximum values.

However, when we plot the minimum values and stack the differences on top, we see that the stacking doesn’t work the way we would have liked. Excel plots bars with negative values to the left of the X axis and bars with positive values to the right of the X axis. Even though the Delta begins to the left of the X axis, the Delta bar has a positive value, and is plotted starting at zero or at the right end of the minimum, if that value is positive.

Our simple formulas are not adequate, and we need a different approach. We’ll add three columns to the data sheet: one for the blank bars on which the floating bars will rest, one for whatever part of the floating bar is positive (above the X axis), and one for whatever part of the floating bar is negative. Using pseudo-references, the formulas we need are:

Blank:  =IF(High<0,High,IF(Low>0,Low,0))
  Pos:  =IF(High>0,High-MAX(Low,0),0)
  Neg:  =IF(Low<0,Low-MIN(High,0),0)

When we plot these values, we get the floating bars spanning the ranges we expect. Note that the floating bars may consist of two pieces, one (orange) to the left of and one (blue) to the right of the X axis, if necessary separated from the axis by the blank series (shown gray in the chart below).

As always, format the blank series to be blank (no border and no fill), and format the floaters as desired.

As before, individual floating bars can be formatted independently; the positive and negative portions can be formatted the same or differently.

Overlapping Floating Bars

You may want to show two sets of floating bars. For example, you may want to compare high and low temperatures for a set of dates with the average historical high and low temperatures for those dates. The way to handle this is to have one set of data on the primary axis, and the other set on the secondary axis.

The table and chart below show two sets of high and low values. The blue will eventually be shown on the primary axis and the orange on the secondary.

Insert two columns in the sheet, to capture the differences between high and low in the two sets of data. Here are the low and delta of each set in a stacked bar chart.

Here is the same stacked bar chart, with the orange series moved to the secondary axis. Each axis has its own Gap Width setting. Here I’ve used 50 for the primary axis (blue bars in back), and 150% for the secondary axis (orange bars in front).

If you used the Upside-Down-Bar-Chart trick to plot the primary vertical axis labels in the same order that they appear in the worksheet, you’ll have to display the secondary vertical axis and apply the same trick to it.

More formatting: Hide the low bars (no border or fill) and adjust the Y axis. Also, delete the secondary horizontal axis and if present the secondary vertical axis. The chart will keep the series for each axis separate (so they have separate gap widths and so they don’t go stacking on each other), but will plot their values using the primary axis scales.

Individual bars can always be formatted separately.

Line Chart Tricks

Excel’s line charts have a few built-in features that can be used to generate floating columns. These include Up-Down Bars and High-Low Lines, which can be combined to create Open-High-Low-Close (OHLC) Stock Charts, and also Drop Lines. Being tied into line charts, these features can only be used to generate vertical floating bars.

Up-Down Bars

Up-Down Bars connect the values of a chart’s first line chart series and last line chart series with floating bars. There are actually two sets of bars: Up Bars, which connect a lower first value to a higher last value (the value goes Up), and Down Bars, which connect a higher first value to a lower last value (the value goes Down).

Floating Columns

For simple floating bars, you need to plot two data series in a line chart.

In Excel 2013, click the Plus icon next to the chart, and check the Up-Down Bars box; alternatively, on the Chart Tools > Design ribbon tab, click the Add Chart Element dropdown, and select Up-Down Bars. In Excel 2007 or 2010, on the Chart Tools > Layout ribbon tab, use the Up/Down Bars dropdown.

The chart below specifically has Down Bars, since all of the values in the last series are lower than all those of the first series.

Unlike the Stacked Column Chart technique, we need to carry out no calculations to determine how tall the floating bars have to be, and we don’t need a hidden set of bars on which to balance the floating bars.

Now apply a little formatting. Format the lines to have no markers and no lines, and give the up-down bars a suitable fill color.

You can change the relative width of the up-down bars and gaps between by selecting them and changing the Gap Width property of one of the line chart series; a Gap Width of 100 means that the gap will be 100% as wide as a bar. I like to use gap widths of 50% to 100%, and I used 75% in most of the charts here.

Mixed Formats: Up vs. Down

You can’t format individual up-down bars with their own colors, but since there are Up Bars and Down Bars, you can at least format some with one color and the rest with another color.

In the data below I’ve switched the First and Last values for points C and D. The line series cross, so the Last series is greater than the First for these points.

When Up-Down Bars are added, the black Down Bars for most of the points are replaced by Up Bars for points C and D.

In this way we can assign different formats to highlight selected points.

Up-Down Bars: First to Last

As mentioned before, Up-Down Bars connect the first line chart series to the last, ignoring values in between. In this data set, the earlier First and Last series have had intermediate series Second and Third inserted between them.

When Up-Down Bars are inserted, they connect First and Last, ignoring any values of Second and Third that may extend beyond First and Last.

Floating Columns Crossing the Horizontal Axis

When dealing with the Stacked Column Chart technique, if you recall, we couldn’t simply use a floating column to span values below and above the horizontal axis. Let’s try this with Up-Down Bars.

The lines show where we want the bars to appear, and when we add the Up-Down Bars…

… they go where we want. Again, no calculations required.

Overlapping Floating Columns

We can use Up-Down Bars to generate overlapping sets of floating bars, using primary and secondary axis groups. Here are two pairs of values plotted in a line chart. The Blue series are plotted on the primary axis, and the Orange series on the secondary. The secondary vertical axis has been deleted so that all values are plotted along the primary axis.

One of the primary series is selected, and Up-Down Bars are added.

One of the secondary series is selected, and again Up-Down Bars are selected.

The Up-Down Bars are formatted with different colors, and the line chart series are formatted to use different gap widths as well as no lines and no markers.

As before, individual bars can be formatted as Up Bars among a field of Down Bars. In the table below, first and last values have been switched for points C and D for both primary and secondary series. The line chart series cross…

… and the bars have been formatted differently to highlight points C and D.

High-Low Lines

High Low Lines connect the highest and lowest values of a chart’s line chart series using vertical lines. The order of series makes no difference to these lines.

Floating Columns

For simple floating bars, you need to plot two data series in a line chart.

In Excel 2013, on the Chart Tools > Design ribbon tab, click the Add Chart Element dropdown, click Lines, and select High-Low Lines. In Excel 2007 or 2010, on the Chart Tools > Layout ribbon tab, use the Lines dropdown, and select High-Low Lines.

Format the plotted line series with no lines and no markers to hide them, and you’re left with boring black vertical lines.

In classic versions of Excel (2003 and earlier) you had limited ability to format such lines, but Excel 2007 introduced the ability to make the lines pretty much as wide as desired. The high-low lines in the chart below are 13.5 points thick, and look a lot like the floating columns produced by the other techniques above.

These lines can be assigned colors and thicknesses, but are not actually rectangular shapes, so they don’t have both border and fill colors.

High-Low Lines: Highest to Lowest

High-Low Lines work by connecting the lowest and highest values at each category. It doesn’t matter where in the sequence the high or low value occurs. In the data below, the highest (blue) and lowest (red) values are highlighted: they are distributed among the columns of data.

When High-Low Lines are added, they connect high and low, regardless of which series includes the extremes.

Note that no values are located above or below the formatted High-Low Lines.

Floating Columns Crossing the Horizontal Axis

The Stacked Column Chart technique required tricky formulas to allow a floating column to span values below and above the horizontal axis, while the Up-Down Bar approach did not. Let’s try this with High-Low Lines.

The lines show where we want the bars to appear, and when we add the High-Low Lines…

… and format them…

… they go where we want. As with Up-Down Bars, it works easily, no calculations required.

Overlapping Floating Columns

We can use High-Low Lines to generate overlapping sets of floating bars, using primary and secondary axis groups. Here are two pairs of values plotted in a line chart. The Blue series are plotted on the primary axis, and the Orange series on the secondary. The secondary vertical axis has been deleted so that all values are plotted along the primary axis.

A primary axis series is selected, and High-Low Lines are added.

A secondary axis series is selected, and again High-Low Lines are selected.

The High-Low Lines are formatted with different colors and line widths (13.5 and 8 pts), and the line chart series are formatted to use no lines and no markers.

Stock Charts

Up-Down Bars and High-Low Lines were probably introduced into Excel to enable Open-High-Low-Close type Candlestick stock charts. Open and Close are the First and Last series from the Up-Down Bars examples above, and High and Low are, well, High and Low from the High-Low Lines examples. When applied together with the data columns in the appropriate order, simple line charts can be formatted into OHLC charts.

Line Chart Plus

Here is a table with two weeks of stock data and the corresponding line chart.

Add High-Low Lines…

… add Up-Down Bars…

… and do a little formatting to create a candlestick chart. Use no markers or lines for the plotted series, and use your favorite colors and gap width for the Up-Down Bars. I always forget whether black or white bars signify up or down, and green and red make a bad combination for color vision deficient viewers, so I usually use blue for up and red or orange for down.

In “Classic” versions of Excel (97 through 2003), the line chart with High-Low Lines and Up-Down Bars was smart enough to recognize itself as a stock chart type, but since Excel 2007, such a chart only considers itself a line chart.

In Excel 2007 and later you cannot combine a stock chart with any other chart type, so you can’t add another series to show how, say, some market index varies in comparison. But you can make your stock chart using a line chart like I did here, then add whatever index lines you want.

Built-In OHLC

Using the same data set, you can directly insert an OHLC stock chart.

A little formatting, and it’s identical the the line chart stock chart above.

The only difference is that the “official” stock chart can’t be combined with any other data.

Drop Lines

Drop Lines are lines that drop from data points to the horizontal (category) axis in line charts and area charts. Each line or area series in a chart can have Drop Lines, and every point in a series with Drop Lines has a Drop Line. Drop Lines cannot produce floating bars, since they by definition start at the axis, but they are related to these other techniques, and it’s useful to know about them, even if you use them only infrequently.

Column Chart

Here is the simple data and line chart we’ll use for our Drop Lines example.

In Excel 2013, on the Chart Tools > Design ribbon tab, click the Add Chart Element dropdown, click Lines, and select Drop Lines. In Excel 2007 or 2010, on the Chart Tools > Layout ribbon tab, use the Lines dropdown, and select Drop Lines. The lines are thin vertical black lines.

Like any lines in Excel 2007 and later, we have great flexibility in how we want to format Drop Lines. Here I’ve hidden the line series (no markers and no lines) and I’ve given the Drop Lines a width of 13.5 points and a blue/cyan line color, to produce what appears to be a standard column chart.

Error Bars

Line Charts with Error Bars

Line charts can have vertical Error Bars, oriented upwards or downwards of the data points, or both. This technique will work with column and area charts as well, but it’s easier to illustrate with line charts.

Floating Bars

We can use Error Bars with custom lengths as floating bars. Here we have high and low values, shown together in a line chart. There is another worksheet column with formulas that compute the differences (Delta).

We only need one of the line chart series for our Error Bars. We can plot the High values, and use Minus Error Bars for our floating bars (left), or we can plot the Low values, and use Plus Error Bars for our floating bars (right).

To assign custom values for error bars, first add Error Bars (in Excel 2013, use the Plus icon floating beside the chart; in Excel 2007 or 2010, use the Error Bars control on the Chart Tools > Layout tab). Then under Error Bar Values in the formatting dialog or task pane, select Custom and click Select Values. In the dialog, click in the Plus or Minus box, and select the range of cells with the Error Bar values. If you are only using one of the boxes, you have to explicitly type a zero in the other box, or Excel will not recognize your selection. Stupid Excel.

Hide the line chart series (format with no lines or markers), and format the Error Bar lines to use No End Caps, and appropriate width (here I’ve used 13.5 points) and line color.

Stacked Floating Bars

Here are High, Mid, and Low values along with the computed differences between adjacent points (Upper and Lower). We want stacked floating bars showing the distance between Low and Mid and between Mid and High.

“Aha!” you say, “I’m way ahead of you this time.” Plot the Mid series with Plus and Minus Error Bars (below left), then format the Error Bars as above (below right). But wait, the Plus and Minus Error Bars cannot be independently formatted?

Too bad, but it’s not a big deal. We just need two series, one for each independently formatted set of Error Bars. Here, I’ll plot the High and Low series.

I’ll add Minus Error Bars to the High series, then Plus error bars to the Low series.

Then I’ll hide both series and format the Error Bars.

Floating Columns Crossing the Horizontal Axis

When using Stacked Column Charts to generate floating bars, if you recall, we couldn’t simply use a floating column to span values below and above the horizontal axis. But Up-Down Bars and High-Low Lines didn’t care if they crossed the axis. Let’s try this with Error Bars, using the same High and Low values as before.

So we try the High series with Minus Error Bars, and the Low series with Plus Error Bars. Both allow the Error Bars to cross the category axis.

Format away.

Error Bars as Drop Lines

Error Bars can also be used to create Drop Lines. Here’s a simple line chart using the Drop Lines data from above.

Instead of adding Drop Lines, we can add Error Bars, choose the Minus direction, and a Percentage Value of 100.

No lines and markers for the data series, no end caps but thick lines and a nice line color for the Error Bars.

XY Scatter Charts with Error Bars

As with the line charts in the preceding section, XY scatter charts can support vertical Error Bars. They can also support horizontal Error Bars. Every trick that works with line chart vertical Error Bars will also work with XY scatter chart vertical and horizontal Error Bars.

Vertical Floating Bars

Okay, we already know it’s going to work, but let’s run through the exercise for completeness. Here is the same High and Low data as before, with numeric rather than alphabetic X values.

Plot the High series with Minus Error Bars or the Low series with Plus Error Bars…

… a little formatting, and it’s the same as with the line chart Error Bars.

Horizontal Floating Bars

Plot the same data, but exchange X and Y in the chart.

Plot the High series with Minus horizontal Error Bars or the Low series with Plus horizontal Error Bars.

The same as before, but horizontal.

Vertical Drop Lines

We can use vertical error bars on an XY scatter chart to create drop lines. Same data as before, but in a scatter plot.

Add Minus Error Bars, using the Percentage Value option, and 100%.

Hide the plotted series and format the Error Bars.

This is the answer to two common Excel-related forum questions:

  • How do I get Drop Lines on my scatter plot?
  • How can I get a column chart on a value X axis?

Horizontal Drop Lines

Taking the previous data, but switching X and Y…

Add horizontal Minus Error Bars, using the Percentage Value option, and 100%, then hide the plotted series and format the Error Bars.

XY Chart Line Segments

A very powerful technique for creating floating bars is using XY chart series line segments. You can make vertical and horizontal floating bars, but you are not constrained to these simple orientations. You can position endpoints of your bars pretty much anywhere in you chart, so the possibilities are limitless. In addition, line segments can be independently formatted, even within the same series of points.

I will use simple examples to illustrate the technique, then set you free.

Floating Columns and Bars

Here are plots showing the same High and Low values for vertical and horizontal floating bars.

XY chart segments connect points in the same series, not in different series as in several of the techniques covered so far. So we need to arrange the data to plot points in one series, not two.

We could use that data, but then we’d have to format the in-between (slanted) segments to use no line color. Pretty tedious after the second or third line segment. But if we insert a blank row between each pair of values, Excel will not plot a line segment across the gap, so formatting will be easy.

As with the previous techniques using thick formatted lines, we’ll start with out XY scatter chart, and thicken up the lines. The vertical lines are 13.5 points, the horizontal lines, 10.5 points. But we’ve hidden the markers; why are there circles at the endpoints?

It turns out that Excel’s richly formattable lines have three different “Cap Type” styles. I’ve illustrated them here with small red markers to illustrate their appearance. The Round Cap style has a circular end shape extending beyond the endpoint of the line segment, where the marker is at the center of the circle. The Square Cap style has a square end end shape extending beyond the endpoint of the line segment, with the markers at the center of the square. And the Flat Cap stops exactly at the end of the segment, with the line squared off right at the marker.

We didn’t have to worry about this with the Error Bars, Drop Lines, and High-Low Lines, because their default “Cap Type” is Flat Cap.

The default “Cap Type” for chart series line segments is Round Cap, which makes for nice-looking polygonal plotted lines. But for floating bars, we most likely will want to use the Flat Cap style.

Here are floating bars using vertical and horizontal XY segments and the Flat Cap style.

We can select individual line segments (click once to select the entire series, and again to select the particular line segment), and format them independently of the others.

Stacked Floating Columns and Bars

Here is our stacked floating bar data, plotted as separate series for vertical and horizontal floating bars.

Here is the same data rearranged to facilitate XY series line segments for vertical and horizontal floating bars, including the blank rows between pairs of points.

Hide the markers and fatten up the lines, and we’ve got stacked floating bars.

As before, individual bars (line segments) can be formatted independently of the rest.

Summary

Here is a summary of the Floating Bar techniques discussed in this article.

Most techniques provide vertical bars, a couple horizontal bars, and XY Scatter Line Segments alone produce bars at any orientation.

A few techniques provide actual rectangular bars, which have a border and fill, while many approximate the appearance of a rectangle with thick line segments.

Some techniques allow individual bars in a set to be formatted independently, and some allow easy creation of stacked bars through the use of multiple series.

Stacke column and bar charts do not permit floating bars to cross the category axis, at least not without using tricky formulas to split bars into positive and negative components. The other techniques allow floating bars above, below, or across the axis.

Peltier Tech Chart Utility

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.