Peltier Tech Excel Charts and Programming Blog

Hour by Hour Flow Chart

Monday, February 3, 2020 by Jon Peltier 5 Comments

Monday, February 3, 2020 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

My colleague Frédéric LeGuen, an Excel MVP from France who runs the ExcelTutorial website and the ExcelExercise YouTube channel, asked me a tricky question last week. He wanted a way to visually track visitors to a museum, knowing how many arrived and left during a given hour. He tried a candlestick chart first, which is great for monitoring stock data, and a waterfall chart, which is great for tracking a quantity subject to up and down changes. The problem is that both charts show only up or down values in a given time period, while we want to show up and down for each time period.

Museum Visitor Data

We have the following data that lists the number of visitors entering (Entrée) and leaving (Sortie) during the hour, and the net visitors. The formula in cell D2, filled down the list, is

=SUM(B$2:B2)-SUM(C$2:C2)

Hourly arrivals and departures, with running totals.

So let’s plot the data in a clustered column chart to see what we have.

Clustered Column Chart of Museum Visitors

No, that doesn’t show us anything. How about a stacked column chart?

What we want will look like this, but have blue arrows before each column showing visitors who have arrived and orange arrows after each column showing visitors who have left.

There is an easy way to do this, using all columns and the sometimes tricky custom error bars. And there is a hard way, using XY series and tricky custom error bars. There’s another hard way, which uses easier error bars but more complicated stacked columns that require a complicated data layout, so I think I’ll avoid that one.

The Easy Way

Starting with the same initial data as above, I made a clustered column chart using time (Heure) as X values and Visitors as Y values. I’ve changed the series overlap from the default of -27% to 0% and the gap width from the default of 219% to 100%. I don’t know who thought up those defaults.

I copied the Visitors data, selected the chart, and pasted. Two series with the same values and name, that’s what I wanted. Really.

Then I repeated the copy and paste so I have the same data in the chart three times. This will give me the total visitors as the middle column, with a column before and after this where I can draw my arrows.

I changed the colors of the bars, because I want the totals to be gray, and I want blue and orange to represent increases and decreases. And I’ve renamed the outer bars Before and After.

Easy Flow Chart Step 4 - Reformatted Column Chart

I added error bars to the Before and After series, using the little plus-sign icon floating beside the chart.

I don’t need to display the blue and orange columns anymore, so I’ve formatted them with no fill, to be transparent.

Time to format the error bars. Be sure to choose the No End Caps option, and then under Error Amount, select Custom, and then click the Specify Value button.

I used the data in the Entrée and Sortie columns as custom values. When you click the Specify Value button, you get this tiny little dialog, with even tinier little edit boxes for selecting the range. The values are initially ={1} for both: change the positive value to ={0} and select the Entrée values for the negative.

I’ve been complaining about the tiny Custom Error Bars dialog for a long time. Here’s what a more reasonably sized dialog would look like. See that, Microsoft? I did it in 5 minutes in MS Paint.

Note, for the Sortie error bar values, they are off by one row, so you need to select the range starting in the second cell, so use $C$3:$C$11. Here’s how the custom error bars look.

Easy Flow Chart Step 7 - Custom Error Bars

Format the error bars with the blue and orange colors. Make the lines a bit thicker; I’ve used 1.5 pt which is 2 pixels. Give the blue error bars a starting arrowhead, and the orange error bars an ending arrowhead.

Easy Flow Chart Step 8 - Formatted Error Bars

A little clean up. I deleted the legend, and adjusted the chart data range to leave out the meaningless blank at 18:00.

Easy Flow Chart Step 9 - Cleaned Up Nicely

You have some leeway in the spacing of the columns, by adjusting the gap width. In the chart above, the gap width is 100, meaning the space between the clusters is 100% as wide as a single column. The hidden columns that the arrows occupy are 100% of this width as well, so there is a reasonably wide space between arrows.

If you want to decrease the spacing, you can decrease the gap. The chart below has the minimum gap width of 0%.

If you had kept the default gap width of 219%, the columns and arrows would be rather far apart.

You can set the gap width as high as 500%, which looks so ridiculous that I’m not showing it..

The Hard Way

The easy approach used two column chart series to hang the error bars on. The hard approach uses two XY scatter chart series. I’ve inserted a column of X values before the Y values (Visitors) in my data.

I selected the time (Heure) and Visitors, and created a column chart. I’ve recolored it gray, and applied overlap of 0% and gap width of 150%.

I copied the X and Visitors data in columns D and E, selected the chart, and used Paste Special (Ribbon > Home tab > Paste dropdown > Paste Special) to add the data to the chart as a new series in columns, series name in first row, categories in first column.

Paste Special as New Series in Columns, Series Names in First Row, Categories in First Column

The result is a second set of columns with the same values as the first. I’ve recolored the columns blue and renamed them Entrée.

I right-clicked on the new series and chose Change Series Chart Type from the pop-up menu. I changed the chart type for Entrée to Scatter (arrow 1), and I unchecked the Secondary Axis box beside it (arrow 2).

The result is a combination chart with a set of gray columns and a set of blue markers, which I have reformatted into a large blue circle with no fill color.

The X values I’ve used, 1, 2, 3, etc., align with the bars at the first, second, third, etc. categories. What I need is to subtract a little bit from these X values to move the blue circles to the left of the columns, and add the same amount to these X values so another set of circles will sit to the right of the columns.

So I’ve added two more columns to my data, X-Before and X-After. I put a small value into cell D13. The formulas in the added columns are:

Cell E2: =D2+$D$13
Cell F2: =D2-$D$13

Keeping my original X values in their own column and using a cell to hold my small amount to add and subtract make it easier to make adjustments later, to cells I can see, rather than to formulas where the amounts are hidden.

I changed the X values of my Entrée series to X-Before.

Hard Flow Chart Step 4 - Combo Chart Realigned

Looking good. So I copied my X-After and Visitors data, and used Paste Special as before to add a third series to the chart. Since I’ve already changed the previous series to a scatter type on the primary axis, Excel made my new series also a scatter type on the primary axis. I’ve renamed the new series Sortie, and made it a large orange circle with no fill.

Hard Flow Chart Step 5 - Combo Chart with Mickey Mouse ears

So far so good, as long as Disney doesn’t come after my for trademark infringement.

So let’s bring this puppy home. Add error bars to the Entrée and Sortie series, using the plus icon floating beside the chart.

Select and delete the horizontal error bars.

Hard Flow Chart Step 7 - Vertical Error Bars

Let’s format the error bars. Choose the No End Caps option, and under Error Amount, select Custom, and then click the Specify Value button.

Use the data in the Entrée and Sortie columns as custom values for the error bars. You get this tiny little dialog I showed before, with microscopic edit boxes for selecting the range. The error bar values are initially ={1} for both: change the positive value to ={0} and for negative select the Entrée values.

Isn’t this Custom Error Bars dialog difficult to use? Here’s a better sized dialog. See that, Microsoft? I fixed it in 5 minutes using MS Paint.

Note that the Sortie error bar values are off by one row, so you need to select the range starting in the second cell, so use $C$3:$C$11. Here’s how the custom error bars look.

Hard Flow Chart Step 8 - Custom Error Bars

Format the error bars with the blue and orange colors and the lines thicker; I’ve used 1.5 pt (2 pixels). Give the blue error bars a starting arrowhead, and the orange error bars an ending arrowhead.

Hard Flow Chart Step 9 - Formatted Error Bars

Format the Entrée and Sortie series to use no markers.

Hard Flow Chart Step 10 - Hide unneeded markers

Finally a little clean-up. Delete the legend. Make all of the chart series one point shorter to remove the meaningless 18:00 category (stop at row 10 instead of row 11). Set the Y axis minimum to zero (the arrows between 12:00 and 13:00 actually drop slightly into negative territory, but that’s not crucial to the story).

Hard Flow Chart Step 11 - Cleaned up nicely

The chart above used a gap width of 125% and a value of 0.333 to add or subtract from X to position the Entrée and Sortie arrows. You can adjust these together to change the chart’s appearance. Below, for example, I’ve applied a gap width of 75% and used an X increment of 0.41.

The hourly flow chart in this tutorial uses a similar approach to that in Revenue Chart Showing Year-Over-Year Variances.

There is a somewhat dated but still relevant tutorial on this blog that tells you all about Custom Error Bars in Excel Charts. Here is the cramped Custom Error Bars dialog in Excel 2007 and Windows 7, even narrower than Excel 365’s.

Tiny Excel 2007 Custom Error Bars dialog

Here is a complete list of error bar related articles on the Peltier Tech blog.

Posted: Monday, February 3rd, 2020 under Combination Charts.
Tags: Error Bars.
Comments: 5

Jon’s Toolbox – A New Utility from Peltier Tech

Thursday, January 23, 2020 by Jon Peltier 19 Comments

Thursday, January 23, 2020 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Jon’s Toolbox is a simple Excel VBA add-in that provides a handful of useful functions. I use it when I am preparing or teaching classes, writing blog posts, and working on projects.

Jon’s Toolbox is not a replacement for Peltier Tech Charts for Excel, nor does it require Peltier Tech Charts for Excel in order to operate properly. The two utilities run independently, and I have both installed on each of my computers.

Click Below to Get Jon’s Toolbox

Jon’s Toolbox is available for $35. Click this button to make your purchase, or scroll down to read about its features.

Check your shopping cart with this button.

What Jon’s Toolbox Does

I’ll do a quick run-through of the menu controls to give you a sense of what Jon’s Toolbox is all about. Peltier Tech Charts for Excel is a large collection of advanced features, for creating custom chart types which are not native to Excel, for manipulating charts, and for controlling data that charts are linked to. Jon’s Toolbox is a self-contained set of functions that help with quickly inserting data and charts, and with quickly formatting charts and worksheets.

Jon’s Toolbox Ribbon Commands

This is a quick look at the custom ribbon tab of Jon’s Toolbox. Click on the image to see it full size in a new browser tab.

The ribbon includes groups of controls, such as Insert Data – Chart – Pivot, Chart and Font Size, Chart Tools, Range Tools, Range and Chart, plus a teaser at the end, From the Blog.

Groups of Controls on the Jon's Toolbox Ribbon

Insert Data – Chart – Pivot

Insert Data – Chart – Pivot has controls to quickly insert data, charts, or a whole data-pivot table-pivot chart ensemble. This is nice when I’m running a demo of my other software, preparing for a class, writing documentation or a blog post. It’s especially nice if I’m running a class and I want to demo a feature of Excel and I want some data for a chart quickly, and I don’t want to make everyone watch me mistype a bunch of numbers.

Data Range

Insert Data Range inserts a handy dummy data set, ready for me to demonstrate Excel’s features, or the features of some software I’ve written. If the active sheet is blank, it puts the data there, otherwise it inserts the data onto a clean worksheet.

Nothing fancy, but it’s a good simple data range for demonstration purposes.

Column Chart, Bar Chart, etc.

There are chart buttons for all of the chart types I routinely use. Yes, it includes pie charts, get over it.

Each of the buttons is a split button. I’ll illustrate the functionality with the Column Chart button.

Column Chart SPlit Button on Insert Tab of Jon's Toolbox Ribbon

If you click on the icon (left), the program creates the chart type shown. If you click on the dropdown (middle), you get a dropdown list with the options for that chart type (right).

When you use one of the options from the dropdown, the split button icon changes to that chart type, which is created next time you click on the icon.

The way a chart is inserted varies, depending on what is selected.

If no data is selected, the program inserts a new worksheet, it inserts the dummy data range (see Data Range above), and it inserts a chart one row below the data range using this data.
If a data range is selected, or if a cell in a block of data is selected, a new chart of the selected type is inserted, one row below the data range.
If a chart is selected, the program inserts a new chart of the selected type, offset slightly from the original chart, using the same data as the previously active chart.

The charts created here are essentially the same as those from Excel’s own Insert tab, the same size and same series formatting, but they follow my own preferences for that chart type.

For example. Excel’s column charts have too much space between clusters, and unneeded space between columns in a cluster. Jon’s Toolbox fixes this.

Comparison Between Column Charts Created by Excel and by Jon's Toolbox.

Excel’s bar charts draw the axis in the opposite order of how the data appears in the worksheet. Jon’s Toolbox fixes this, while also changing to an aspect ratio that better accommodates the chart.

Comparison Between Bar Charts Created by Excel and by Jon's Toolbox.

In addition to being spelled with too many silent letters, Excel’s doughnut charts have too fat a white line surrounding the individual points, the default hole size of 75% is ridiculously large, and the legend is placed below the chart, shrinking the doughnut’s height and leaving wide swaths of white space on either side of the doughnut. Jon’s Toolbox creates a donut chart spelled with no excessive letters; with a thin white outline around the points; with a default hole size of 66% for one ring (one series), 50% for two rings, and 33% for three or more rings; and with its legend on the right side of the chart, allowing the donut to fill more of the space occupied by the chart. In addition, Jon’s chart is closer to square, which means the chart takes up less space.

Comparison Between Do(ugh)nut Charts Created by Excel and by Jon's Toolbox.

Data, Table, and Chart

Data, Table, and Chart inserts a new worksheet. The worksheet contains a Table of data, a Pivot Table constructed using the data in this Table, and a Pivot Chart based on this Pivot Table. Click on the screenshot to view in a new window.

Insert Data Table, Pivot Table, and Pivot Chart

This is a great way to quickly show someone about Tables and Pivot Tables and Charts.

Chart and Font Size

Are the default charts in Excel the right size for your purposes? In the US, the defaults are 5 inches wide and 3 inches tall, and frankly, that’s way larger than most of my charts need to be. Also, that 14-point chart title is much too large.

The Chart and Font Size group of controls provides the following predefined set of alternative sizes for charts and their fonts.

The different chart and font sizes look something like this. Note that they are independently applied to each chart. For my blog posts, I can easily fit two of the small charts, usually with medium font, side by side within a column of text.

Like many of the features of Jon’s Toolbox, if you select multiple charts before clicking the button, the formatting is applied to all selected charts.

Chart Tools

Jon’s Toolbox has a handful of useful chart tools.

The default legend in an Excel chart is pretty widely spaced out (below left). Condense Legend closes it up (below right).

You can easily Reverse Series (and then unreverse them) in a chart, for example in a clustered column chart…

Reversed Series in a Clustered Column Chart

…or in a stacked column chart.

Reversed Series in a Stacked Column Chart

By default, Excel plots a bar chart with the vertical axis in exactly the opposite order as the data appears in the worksheet. This makes sense on some level, but not from the standpoint of usability. Click Flip Bar Chart and fix your bar charts instantly. (Click it again to get the upside-down version back.)

Note that if you use Jon’s Toolbox to insert a bar chart, it is automatically plotted the right way.

The default Excel color palette is not too bad, but if large areas of your chart are filled with the colors, the chart may seem oversaturated (below left). In Excel’s color pickers, the next lighter shade is 40% brighter, which may make the charts washed out. Apply 20% Brightness applies an intermediate shade to the selected series (below right). Note that you have to select and format each series separately.

Finally, the Copy Bitmap button copies the active chart to the clipboard in bitmap format, saving you several clicks (Home tab > Copy dropdown > Copy as Picture > select Appearance and Format). Simply switch to PowerPoint or your image editing software and Paste.

Range Tools

Jon’s Toolbox contains a set of range formatting tools.

(Format as) Chart Data

When you select a chart or a series in a chart, Excel highlights the associated data in the worksheet.

Chart Data applies this formatting more permanently to the worksheet. The colors are the same, it only lacks the small square handles on the corners of the shaded regions. Great for tutorials and illustrations, though.

Click the Chart Data icon, and different things happen depending on what is selected.

If a chart is selected, the data range for the whole chart is formatted.
If a series in a chart is selected, the data range for that series is formatted.
If a range is selected, the range is formatted as if it were used in a chart. If more columns that rows are selected, the range is formatted as if series are plotted by row, otherwise it is formatted as if series are plotted by column.

If you need more control over the formatting, click the Chart Data dropdown.

You can format a range including X and Y values and series names as if plotted by row or by column. You can format a range as if it contains only series names, X values, or Y values.

In addition, you can format a range so it looks like a selected range (below left), as a copied range (below center), or by clicking the two buttons in succession, as a selected range that’s been copied (below right).

Format as Selected, as Copied, or as Selected and Copied

I have found these very useful when writing blog posts, class notes, or documentation for my software.

Borders

Excel has an extensive built-in Borders menu.

And the borders it creates are stark, in-your-face dark black. Sometimes I even want to pluck out my own eyeballs. (My daughter once determined that any statement is funnier if you say “eyeballs” than if you just say “eyes”, and I agree. She’s a scientist working on visual perception using primates.)

Below are Outside Borders, Thick Outside Borders, All Borders, and a combination of All Borders and Thick Outside Borders. Pretty harsh.

Nice Borders applies more visually appealing borders to a selected range. Medium is a medium gray, darker than the default cell boundaries, but not as irritating as black, while light is a light gray similar to the default cell boundary color. “Nice” borders means a medium gray outline with light gray internal boundaries, and the other options format internal borders and outlines, or just outlines, with medium or light gray borders.

These are illustrated below with cell gridlines (Gridlines on the View tab) displayed on the left and turned off on the right.

Medium Borders applies medium gray borders to each cell in the selection.

Medium Outline applies a medium gray border around the outer cell boundaries of the selection.

Light Borders applies light gray borders to each cell in the selection. The light gray is indistinguishable from the default cell gridlines. I use this to put gridlines only on a certain range, while turning off gridlines elsewhere.

Light Outline applies the light gray border around the outer cell boundaries of the selection.

Nice Borders applies a medium gray outline around the selection and light gray borders to cell boundaries within the selection.

If I am making screenshots for documentation, I will often either hide the default gridlines and use Light Borders (first screenshot below), or I will show the gridlines and use Nice Borders (second screenshot below).

Light Grid Formatting With Chart for Blog Screenshot

"Nice" Border Formatting With Chart for Worksheet

When you select one of these Border options, it becomes the new icon and default action for the command.

Clear Formats

Clear All Formats will clear all formats, except for number formatting, from a selected range. This is nice, because sometimes you need to get rid of some ugly border and fill colors, but recreating number formats is a chore.

In the Clear All Formats dropdown, the options are to

Clear border formatting,
Clear border and fill formatting,
Clear all formatting except for number formatting.

Page Breaks

Hide Page Breaks simply removes those unsightly page break dashed lines that make me crazy.

Range and Chart

The Range and Chart group has some controls that help you control how the chart interacts with the worksheet.

Select Data allows you to quickly select a new source data range for the active chart.

Cover with Chart lets you select a range for the active chart to cover. The chart is repositioned and resized to cover the selected range.

Stretch Chart resizes the active chart (or all selected charts) to fill the cells it partially covers.

Chart Before and After Stretching to Fill Range It Covers

Center Chart moves the active chart (or all selected charts) so it is centered within the range of cells it covers.

Chart Before and After Being Centered Within the Range It Covers

From the Blog

Finally, Jon’s Toolbox includes some nice little programs that were previously presented in the Peltier Tech Blog.

These programs were featured in the following blog posts:

Add Totals to Stacked Column Chart (but it works on stacked bar, stacked area, and stacked line charts, too)
Trendline Calculator for Multiple Series
Built In and Custom Lists in Excel

Get Jon’s Toolbox Here

Jon’s Toolbox is available for $35. Click this button to make your purchase.

Check your shopping cart with this button.

Bulk Discount

If you purchase multiple licenses, you qualify for a volume discount, shown in the table below. The discount is automatically applied in the shopping cart when you enter your quantity.

Volume discounts on Peltier Tech software.

Guarantee

Like all of Peltier Tech’s software products, Jon’s Toolbox is guaranteed for 90 days from the date of purchase. If you are unsatisfied for any reason, contact Peltier Tech directly to request a refund. Any feedback is appreciated, but none is necessary.

License

Each user of Jon’s Toolbox requires a license. Each license allows the user to install Jon’s Toolbox on up to two computers of which they are the primary user. If a user gets a new computer, the software can be uninstalled from the old machine and reinstalled onto the new one.

Installation

Jon’s Toolbox is a simple Excel VBA add-in, which can be installed in Excel for Windows or Excel for Mac. Follow instructions at Install an Excel Add-In, and you’ll be working in no time.

Questions?

Contact Peltier Tech with any questions about Jon’s Toolbox.

Posted: Thursday, January 23rd, 2020 under Utilities.
Tags: .
Comments: 19

Bar lengths on a chart, what do they even mean?

Thursday, January 9, 2020 by Jon Peltier 12 Comments

Thursday, January 9, 2020 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

The Misleading Chart

My friend and colleague Patrick Matthews, a former Excel MVP, posted a screenshot of an unusual bar chart on his Facebook page. The chart was taken from What does the public say about impeaching Trump?, the last section of a Washington Post article titled What happens next in the impeachment of President Trump? Patrick’s comment says it all: “Bar lengths on a chart, what do they even mean?”

At the risk of opening a torrent of political comments, I’ve reproduced the chart here.

Take a close look at the bar lengths in the first chart. The 12% bar is over half as long as the 85% bar, where in a bar chart with proportional bars, the 12% bar should be about 1/7 as long. But at least the 49% bar is slightly longer than the 47% bar, and they are in between the 12% and 85% bars. The same holds true of the bar lengths in the second chart.

Someone responded to Patrick’s post, wondering how they came up with those bar lengths. After the analysis in the previous paragraph, I replotted the data, set the axis scales to -100% to +100%, and set the vertical axis to cross at -100% on the horizontal axis. Nailed it!

Guess at the derivation of unusual bar lengths.

Well, not exactly. As I sometimes do, I overanalyzed the charts. I’ve stripped most of the text from the WaPo graphic, replaced the outlines of my charts with red lines, and stretched my charts so they overlaid the WaPo plot.

It turns out that the axis minimum was really -92%, so my wild guess of -100% was pretty good. I’ve set the gridline spacing so that 0% and +92% are shown on the chart, and the far right edge of the plot area is at +100%.

Exact determination of unusual bar lengths.

I don’t think the graphic artist really used an axis minimum of -92%. I’m sure they started with 0%, then decided to fill in some white space by dragging the left edges of each bar while keeping the right edge in place. They filled in the space, all right. But by doing so, they obscured the differences between the values.

It’s the same issue that occurs when people start their axis at a value greater than zero, so the differences between values are accentuated. But now the axis and the bars start well below zero, and the differences are minimized.

Fixing the Chart

My next step was to take my two charts, and set their axis minimum to 0%. These two charts now accurately show the relative percentages.

Bar charts with appropriate bar lengths.

Improving the Chart

Those last two charts were a big improvement. But if we’re expected to compare the values, shouldn’t the bars all be in a single chart? Below I plotted the negative of one set of data, so the bars stretch in opposite directions, the way they do in population pyramids. Let’s call this a diverging bar chart.

Then I remembered why I dislike population pyramids, as I discussed ages ago in Tornado Charts and Dot Plots. It’s hard to compare bars that reach away from each other. It would be easier to compare the values of any two bars if they start at one horizontal position (the vertical axis) and stretch in the same direction (to the right). So I created this clustered bar chart:

An alternative is to plot one set of bars from left to right, and the other from right to left. It’s a converging rather than a diverging bar chart. This makes individual bars more difficult to compare, as in the population pyramid lookalike above. But the white spaces clustered between the colored bars represent the percentages of each category who have no opinion.

What do you think? Not about the topic of the chart, but about the construction of the chart. Do you prefer the diverging bar chart, the clustered bar chart, the converging (stacked) bar chart, or something else entirely?

Posted: Thursday, January 9th, 2020 under Charting Principles.
Tags: Bar Charts.
Comments: 12

Order of Series and Legend Entries in Excel Charts

Tuesday, December 31, 2019 by Jon Peltier 3 Comments

Tuesday, December 31, 2019 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Chart Series and Legends

In Excel charts, series are drawn in a particular order and legend entries are listed in their own particular order, based on series number, series chart type, the axis a series is plotted on, and other features, like axis category order and whether series are stacked. People often ask how to move series within a chart or within a legend. This article should help explain what is even possible.

Single Chart Type

If a chart has one type of series, all on the primary axis, the series are plotted in the order that they are added to the chart. The legend usually lists them in this order as well.

Line Charts

The line charts below illustrate the layering of series. Whether lines with markers or just lines, the second series in red is always plotted in front of the first series in blue. Even to the extent that the red line in the bottom right chart covers the blue markers. The legends all list the blue series first, then the red series.

An interesting inversion occurs if one series has markers and no lines. The markers only series is plotted in front of the line series (top row of charts below) or in front of the lines with markers series (bottom row). In both right-hang charts, the blue markers of the first series or drawn in front of the red series. The legends still list blue first, then red.

XY scatter charts show the same behavior as line charts, with series plotted in order except in the case where one series has markers and no lines.

Column and Bar Charts

In unstacked horizontal bar charts, legend entries appear in the same order that the series appear in the chart. Below left, red bars and legend entries appear before blue.

Below right, the categories have been reversed in the vertical axis. Now blue bars and legend entries appear before red.

Legend entries in bar charts appear in the same order as the data in the chart. When reversing the axis categories switches the positions of the bars, it also reverses the legend.

This swapping of the legend to match the arrangement of the bars helps with interpretation of the chart. You might expect column charts to show the same alignment of legend entries with plotted points.

In the original chart, below left, the blue series 1 columns and legend entry appear before the red series 2 columns and legend entry. Reversing the categories in the horizontal axis does reverse the positions of the bars, but the legend keeps its original order, so the legend entries are now out of sync with the columns.

Legend entries in column charts don't always appear in the same order as the data in the chart. While reversing the axis categories switches the positions of the bars, it does not reverse the legend.

Effect of Stacking

In stacked charts (area, bar, and column charts), each series is stacked on the previous series, so the chart shows totals of all series at each category. The legends are rearranged to list series in the order they appear in the stack. This helps the viewer interpret the plotted data.

Note: You can also stack line chart series, but it’s not a good idea to do so; stacked line charts can be very confusing.

In the unstacked area chart below left, series 1 in blue is plotted first, and series 2 in red is plotted in front, partially obscuring series 1. The legend lists the series in this order.

In the stacked area chart below right, the blue area is plotted first and the red area is stacked on top. The legend is reversed, so that series 2, plotted at higher Y values than series 1, is also listed higher in the legend.

Order of Stacked Area Chart Legend Matches Order of Stacking

In an unstacked bar chart (below left), the legend lists series in the order they appear: red above blue. In a stacked bar chart (below right), the legend also lists series in the order that they are stacked, red to the right of blue.

Order of Stacked Bar Chart Legend Matches Order of Stacking

In an unstacked column chart (below left), the legend lists series in the order they are plotted: blue before red. In a stacked column chart (below right), the legend lists series in the order that they appear, red above blue.

Order of Stacked Column Chart Legend Matches Order of Stacking

Mixed Chart Types (Combination Charts)

The order in which data is plotted in the chart and listed in the legend becomes more complicated when multiple chart types are used in the same chart. The rules are straightforward, but they aren’t documented anywhere, so people get confused.

Series Order by Chart Type

The order that series are plotted in the chart and listed in the legend follows this order of chart types: Area, Column and/or Bar, Line, and XY Scatter. Changing the plot order (by rearranging series in the Select Data Source dialog or by changing the last argument in the Series Formula) will rearrange series within a type, but will not move series out of their plot type order.

Start with this simple data and insert a clustered column chart (usually the default type). Then right click on any series and select Change Series Chart Type from the pop up menu. Change series “Area 1” to an area, keep “Column 2” as a column, and change “Line 3” to a line. The blue area series is drawn behind the other series and listed first in the legend, the red columns are drawn in front of the blue area and listed in the middle, and the gold line is plotted in front of the others and listed last.

Area series are plotted in back of other series, line series are plotted in front, and bar and column series are plotted in between.

Big deal, you may think, that’s the order that the data was arranged in the worksheet. Reverse all that, and the line will be drawn first, behind the others, while the area will be drawn last, obscuring the rest.

Below is the data in reverse order and the resulting column chart. Again, right click on any series and select Change Series Chart Type. Change “Line1” to a line, keep “Column 2” as a column, and change “Area 3” to an area. The gold area series is drawn behind the other series and listed first, the red columns are drawn in front of the gold area and listed next, and the blue line is plotted in front of the others and listed last.

So we see that the chart type dictates the order in which series are drawn and listed, regardless of the order of series data within all of the data in the chart.

We can extend this further to show that an XY Scatter series will be plotted in front of all the other series, regardless of where it falls in the chart source data.

Area series are plotted in back of other series, line series are plotted in front (and XY scatter series in front of lines), and bar and column series are plotted in between.

Primary and Secondary Axes

Earlier I wrote that the order of series by chart type was Area, Column and/or Bar, Line, and XY Scatter. I placed Column and Bar together because if your chart contains both column and bar series, they are plotted in the same layer between areas and lines, and they are listed together in the legend. Their precise order depends on which axis each is assigned to.

Below I’ve added a bar chart series to the first combination chart above. A bar chart series cannot be plotted on the same axis group as another chart type, so in the chart below, the area, column, and line series are plotted on the primary axis, and the bar is plotted on the secondary axis, so that the gray bars are in front of the red columns. Note that the legend order is area first, line last, and column and bar in the middle. Because the column series is on the primary axis, it is listed before the bar series.

Next I’ve added a bar chart series to the second combination chart above. I’ve plotted the bar on the primary axis, while the area, column, and line series are plotted on the secondary axis. Note that the gray bars are behind the red columns. The legend order is still area first, line last, and column and bar in the middle. Because the bar series is on the primary axis, it is listed before the column series.

The following chart has no bar series, but area, column, and line series on each of the primary and secondary axes. All areas are listed first, then all columns, then all lines; within each chart type, the primary series are listed before the secondary series.

Series are plotted and listed in the legend in a particular order, based on series type, axis, and other factors.

Legend ordering can be even more intricate. Below is four series of data and a column chart.

In the chart below left, series 2 and 3 have been moved to the secondary axis. Note that the legend lists series 1 and 4 first for the primary axis, then series 2 and 3.

In the chart below right, series 2 and 3 on the secondary axis have been changed to stacked columns. The legend still lists primary axis series 1 and 4 in that order, but lists secondary series 3 and 2 in the order they re stacked.

Earlier I showed how a line chart series with markers and no line will be plotted in front of a later line chart series with a line (with or without markers). This happens if both series are on the same (e.g., primary) axis.

Below left: red series 2 markers plotted in front of blue series 1 line and markers. Below right: blue series 1 markers plotted in front of red series 2 line and markers.

If the series with markers only is on the primary axis and the series with the line (with or without markers) is on the secondary axis, the primary markers will not be plotted in front of the secondary series.

Below left: blue secondary series 1 plotted in front of red primary axis series 2. Below right: red secondary series 2 plotted in front of blue primary series 1.

Legends with Many Entries

Legends can get complicated when there are many charted series and many entries in the legend.

Legends with Multiple Rows and Columns

The following chart has a legend across the bottom, listing all 8 series in a horizontal row.

If you shrink the width of the chart, eventually the legend will no longer fit. Excel converts the legend to two rows. Shrink the chart further, and the legend will change to three rows, then four.

Excel tries to place the same number of entries into each row: 8 entries in the original legend, then 4+4 entries, then 3+3+2, and finally 2+2+2+2.

It’s a little different with a vertically aligned legend. Below left is the same chart as above, with the legend listing the series along the right edge of the chart. Shrink the chart so that the legend no longer fits, and Excel does not convert it to two columns, instead it simply drops items off the list.

In its automatic legends, Excel doesn’t like the entries to get too close together, but you can manually change the height of the legend, and more entries will fit. In the chart below left, the chart is the same size, and so is the font, but I’ve slightly stretched the legend and all eight entries appear. You can even shrink the legend, and push the legend entries together. (Shrink it too much, and again, you will lose items off the bottom of the list.)

Alternatively, you can widen the legend, and Excel will add a column of legend entries (below right).

If your series names have different lengths but they fit in one row, Excel will position legend entries so the spaces between them are equal.

When you shrink the chart so that the legend reverts to multiple rows, Excel gives all legend entries the same amount of room. Note the wide spaces between the short legend entries below. I’ve used a light gray border on the legend to help illustrate this behavior.

This effect continues with more rows of legend entries.

Partial List of Series

I was working on a research project management dashboard with a client, and he was showing a stacked area chart that showed data about his various projects over time. Like many analyses of this sort, most of the total was due to a small number of items. He wanted to show a list of the top N projects in the legend. He was able to do it once, but then couldn’t remember how he did it.

In my mock-up below, I have eight “projects” stacked up, and I want to show the largest four contributors to the total. These two charts have all eight series listed in the legend.

In the next two charts, I have reduced the sizes of the legends so that half of the legend entries have disappeared. The left chart has the vertical list I want, except it shows the four smallest series. The right chart shows the four largest series, but they are in a horizontal list. But I can start with the horizontal legend to produce the vertical list I want.

Below left, I have moved the legend to the top right corner of the plot area. Below right I have made the legend taller and narrower to force Excel to list the entries in one column.

Finally a little cleanup and I have the desired list of the four largest series.

The client was thrilled when I showed how he could reproduce his top ten list of projects by starting with his legend at the bottom of the chart instead of the right side.

Invert if Negative Formatting in Excel Charts

Thursday, December 5, 2019 by Jon Peltier 9 Comments

Often the positive and negative values in a chart are formatted differently to make it visually easier to distinguish these values. In Excel column and bar charts, this can be done using a feature called Invert if Negative. This is commonly done in Waterfall Charts (using a different technique).

Invert if Negative easily applies different colors for positive and negative data in bar or column charts.

Invert if Negative is often applied incorrectly, but this article will show you the right way to use it.

Here is a simple data set with positive and negative values. All of the bars have the same color.

Side Tip #1: Move Axis Labels

The first thing you might notice about the previous chart is that some of the category (X axis) labels are hard to read because they overlap with the bars.

A simple axis setting moves the labels to the bottom of the chart, to eliminate this overlap. Select the axis and press Ctrl+1 to open the Format Axis task pane, click Labels, and in the Label Position dropdown, change from the default Next to Axis to Low.

Label position low to avoid overlapping with data

Side Tip #2: Ctrl+1

People love learning new Excel shortcuts. When they ask me for my favorite shortcut, I tell them Ctrl+1. Whatever you select any object in Excel (a cell or range, a shape, or a chart element), pressing Ctrl+1 opens the Format dialog or task pane for that object. I use Ctrl+1 a lot, and when I use it in PowerPoint or Word I get mad that it doesn’t help me format what I’ve selected there.

Actually, I’m lying about my favorite Excel shortcut. My real favorite Excel shortcut, the shortcut I use more than any other, is Ctrl+Z, which undoes my last action (i.e., my last mistake). Ctrl+Z is great, because it also works in PowerPoint, Word, and many other programs.

Side Tip #3: Don’t Move the Axis

In some kinds of charts, you can actually move the horizontal axis away from its default position at Y=0 to the bottom of the chart. This is done by assigning a Y axis value to where the horizontal axis crosses.

Axis crosses setting to move the horizontal axis

This might be fine in a line chart, to move the axis labels out of the way of the data. In a bar or column chart, this isn’t a good idea. Since Excel draws bars and columns starting from the axis, you get excessively long positive bars for positive values, and short positive bars for negative values.

Alternative Method: Invert Formats Using Different Series

You can use the approach in Conditional Formatting of Excel Charts to format the bars differently. This requires extra columns in the data range for each different format and formulas to place applicable values in each column.

Invert series colors using conditional chart formatting

This is a fine technique, and it’s suitable for more than just positive vs. negative formatting, any condition that you’re smart enough to write a formula for. But it requires extra columns in the data range.

Invert if Negative: Not the Right Way

The Invert if Negative setting is easy to find. Select the series and press Ctrl+1 to open the Format Data Series task pane. The Invert if Negative checkbox is near the bottom of the list of fill options.

If you just check the box while the Automatic fill option is selected, you can only format the color of the positive bars with the paint can icon, while the negative bars are white with a black border.

People think Invert if Negative doesn’t work right when they use it this way, but really, the right technique is easy.

Invert if Negative: The Right Way

Select the Solid Fill option when you check the Invert if Negative box, the task pane has two paint can icons, one for positive bars, and one for negative bars.

You can go back anytime and select Solid Fill later to get the double paint cans.

Invert if Negative for Old Excel Versions

Old versions of Excel had Invert if Negative, with a default negative fill color of white. In Excel 2003 and earlier, you had to apply a pattern temporarily to select a specific color for negative bars, then unapply the pattern and the color would stick. In Excel 2007, you had to apply a gradient fill with an insane gradient setting. But if you’re using Excel 2010 or later (and you should be), the Solid Fill option provides two paint cans for easy selection of positive and negative colors.

Invert if Negative for Pivot Tables

In a Pivot Chart, Invert if Negative may lose the custom negative formatting you’ve created, after you save and reopen the workbook.

The old Excel 2007 workaround that hacks the gradient fill would be good here, since it gives the appearance of Invert if Negative, though it’s a bit tedious to apply. However, that hack was based on Excel 2007’s faulty color management, and when that was fixed in 2010 or 2013, the gradient hack no longer worked. So you’ll have to rely on the even older Excel 2003 workaround that uses pattern fills.

Select the series in your chart and press Ctrl+1 to open the Format Series task pane. Select Pattern Fill and Invert if Negative. Select the positive color (blue below) using the Foreground paint can, and the negative color (orange) using the Background paint can. And choose the Dotted 90% pattern

It’s not a smooth looking as the regular chart without patterns, but at least it captures about 90% of the experience, and the Pivot Chart’s pattern fill seems to keep its inverted formatting robustly.

Pattern-Filled Inverted if Negative Pivot Table

Invert if Negative for Tables

If you create a chart from a Table, and then filter the Table (and therefore to filter bars in the chart), then Invert if Negative may also lose its negative formatting.

According to limited testing I’ve carried out, if any (but apparently not all) negative bars were filtered out of a chart, they may lose their negative formatting, and revert to the default white bar with a black outline.

Ah-HA! you say, we’ll just apply the patterned fill approach that works so well for Pivot Charts. And that will seem okay, until you filter the table, save the workbook, reopen the workbook, and refilter the table. And the formerly hidden negative bars have white fill again. Sometimes playing with the slicer may restore the inverted fill color, but not reliably.

The remedy is to unfilter the table, so all bars are visible (even if their reversed formatting isn’t totally correct), and then save the workbook. When you reopen the workbook, the negative bars are inverted correctly, and the formatting survives subsequent filtering, until the next save.

Peltier Tech Articles About Conditional Formatting of Excel Charts

Invert if Negative is one specific form of conditional formatting in Excel charts, unique in that it is built into Excel. Here are other approaches which are not built into Excel but which can be implemented with formulas and/or VBA.

Posted: Thursday, December 5th, 2019 under Formatting.
Tags: invert if negative.
Comments: 9