Peltier Tech Excel Charts and Programming Blog

Add a Horizontal Line to an Excel Chart

Tuesday, September 11, 2018 by Jon Peltier 19 Comments

Tuesday, September 11, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A common task is to add a horizontal line to an Excel chart. The horizontal line may reference some target value or limit, and adding the horizontal line makes it easy to see where values are above and below this reference value. Seems easy enough, but often the result is less than ideal. This tutorial shows how to add horizontal lines to several common types of Excel chart.

We won’t even talk about trying to draw lines using the items on the Shapes menu. Since they are drawn freehand (or free-mouse), they aren’t positioned accurately. Since they are independent of the chart’s data, they may not move when the data changes. And sometimes they just seem to move whenever they feel like it.

The examples below show how to make combination charts, where an XY-Scatter-type series is added as a horizontal line to another type of chart.

Add a Horizontal Line to an XY Scatter Chart

An XY Scatter chart is the easiest case. Here is a simple XY chart.

XY Scatter Chart Without Horizontal Line

Let’s say we want a horizontal line at Y = 2.5. It should span the chart, starting at X = 0 and ending at X = 6.

This is easy, a line simply connects two points, right?

We set up a dummy range with our initial and final X and Y values (below, to the left of the top chart), copy the range, select the chart, and use Paste Special to add the data to the chart (see below for details on Paste Special).

XY Scatter Chart With Ugly Horizontal Line

When the data is first added, the autoscaled X axis changes its maximum from 6 to 8, so the line doesn’t span the entire chart. We have to format the axis and type 6 into the box for Maximum. We probably also want to remove the markers from our horizontal line.

Paste Special

If you don’t use Paste Special often, it might be hard to find. If you copy a range and use the right click menu on a chart, the only option is a regular Paste, and Excel doesn’t always correctly guess how it should paste the data. So I always use Paste Special.

To find Paste Special, click on the down arrow on the Paste button on the Home tab of Excel’s ribbon. Paste Special is at the bottom of the pop-up menu.

You can also use the Excel 97-2003 menu-based shortcut, which is Alt + E + S (for Edit menu > Paste Special).

The tooltip below Paste Special in the menu indicates that you could also use Ctrl + Alt + V, but this shortcut doesn’t do anything for charts.

When the Paste Special dialog appears, make sure you select these options: Add Cells as a New Series, Y Values in Columns, Series Names in First Row, Categories (X Values) in First Column.

Click OK and the new series will appear in the chart.

Add a Horizontal Line to a Column or Line Chart

When you add a horizontal line to a chart that is not an XY Scatter chart type, it gets a bit more complicated. Partly it’s complicated because we will be making a combination chart, with columns, lines, or areas for our data along with an XY Scatter type series for the horizontal line. Partly it’s complicated because the category (X) axis of most Excel charts is not a value axis.

As with the XY Scatter chart in the first example, we need to figure out what to use for X and Y values for the line we’re going to add. The Y values are easy, but the X values require a little understanding of how Excel’s category axes work. Since the category axes of column and line charts work the same way, let’s do them together, starting with the following simple column and line charts.

Column and Line Charts Without Horizontal Lines

Note in the charts above that the first and last category labels aren’t positioned at the corners of the plot area, but are moved inwards slightly. This is because column and line charts use a default setting of Between Tick Marks for the Axis Position property. We can change the Axis Position to On Tick Marks, below, and the first and last category labels line up with the ends of the category axis. The line chart looks okay, but we have cut off the outer halves of the first and last columns.

Let’s focus on a column chart (the line chart works identically), and use category labels of 1 through 5 instead of A through E. Excel doesn’t recognize these categories as numerical values, but we can think of them as labeling the categories with numbers.

Now let’s label the points between the categories. Not only do we have halfway points between the categories (1.5, 2.5, 3.5, 4.5), we also have a half category label below the first category (0.5) and another after the last category (5.5).

Column Chart With Categories 1-5 and Mid Categories

If the Axis Position property were set to On Tick Marks, our horizontal line starts at 1 (the first category number of 1) and ends at 5 (the last category number of 5). This would be wrong for a column chart, but might be acceptable for a line chart.

Column Chart With Desired Horizontal Line

Here is our desired horizontal line, stretching from 0.5 to 5.5

So let’s use this data and the same approach that we used for the scatter chart, at the beginning of this tutorial.

Copy the range, and paste special as new series. We’ve added another set of columns or another line to the chart.

Column and Line Chart With Horizontal Line

Right click on the added series, and choose Change Series Chart Type from the pop-up menu.

In the Change Chart Type dialog, select the XY Scatter With Straight Lines And Markers chart type. We’re using markers to temporarily mark the ends of the line, and we’ll remove the markers later; in general we will change directly to XY Scatter With Straight Lines.

The new series don’t line up at all, though, because Excel decided we should plot the scatter series on the secondary axes. We could rescale the secondary axes, then hide them, but that makes a complicated situation even more complicated.

So we need to uncheck the Secondary Axis box next to the Scatter series in the Change Chart Type dialog.

Change Series Chart Type And Axis Dialog

And now everything lines up as expected: the markers on the horizontal lines are at the edges of the plot area.

We should remove those markers now, and in the future select the chart type without markers.

“Lazy” Horizontal Line

You may ask why not make a combination column-line chart, since column charts and line charts use the same axis type. And many charts with horizontal lines use exactly this approach. I call it the “lazy” approach, because it’s easier, but it provides a line that doesn’t extend beyond all the data to the sides of the chart.

Start with your chart data, and add a column of values for the horizontal line. You get a column chart with a second set of columns, or a line chart with a second line.

Column and Line Charts With Lazy Horizontal Lines

Change the chart type of the added series to a line chart without markers. Doesn’t look very good for the column chart (left) since the horizontal line ends at the centerlines of the first and last column. You could probably get away with it for the line chart, even though the horizontal line doesn’t extend to the sides of the chart.

If we change the Axis Position so the vertical axis crosses On Tick Marks, the horizontal lines for both charts span the entire chart width. In the column chart, this comes at the expense of the outer halves of the first and last columns. The line chart looks okay, though.

Add a Horizontal Line to an Area Chart

As with the previous examples, we need to figure out what to use for X and Y values for the line we’re going to add. The category axis of an area chart works the same as the category axis of a column or line chart, but the default settings are different. Let’s start with the following simple area chart.

Notice that the first and last category labels are aligned with the corners of the plot area and the filled area series extends to the sides of the plot area. This is because the default setting of the Axis Position property is On Tick Marks. We can change it to Between Tick Marks, which makes the area chart look a bit strange.

Below is the data for our horizontal line, which will start at 1 (the first category number of 1) and end at 5 (the last category number of 5), without the half-category cushion at either end. Copy the data, select the chart, and Paste Special to add the data as a new series.

Right click on the added series, and change its chart type to XY Scatter With Straight Lines And Markers (again, the markers are temporary). The resulting line extends to the edges of the plotted area, but Excel changed the Axis Position to Between Tick Marks.

Change the Axis Position setting back to On Tick Marks, and remove the markers from the line.

“Lazy” Horizontal Line

In the column chart, and perhaps for the line chart, the “lazy” approach did not give a suitable horizontal line, since the line did not extend to the edges of the plot area. Let’s see how it works for an area chart.

Make a chart with the actual data and the horizontal line data.

Right click on the second series, and change its chart type to a line. Excel changed the Axis Position property to Between Tick Marks, like it did when we changed the added series above to XY Scatter.

Change the Axis Position back to On Tick Marks, and the chart is finished.

For the area chart, the appearance of the lazy horizontal line is identical to the more complicated line that uses an XY Scatter series. Since it’s easier and just as good, it’s probably better to use the lazy approach.

Follow-Up

An alert reader noted in the comments that the line produced by this method is placed in front of the bars, and it might be better to place such reference lines behind the data. I have written a new post describing an approach that does just this: Horizontal Line Behind Columns in an Excel Chart.

Horizontal Line Behind Columns in an Excel Chart

Historical

I showed similar approaches in an old post, Add a Target Line.

Step Charts in Excel

Thursday, July 19, 2018 by Jon Peltier 17 Comments

Thursday, July 19, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Step Charts are useful for showing values that don’t change steadily from one point to the next, but that instead are constant for a period of time, then jump to the next level, and are constant for another period of time. For example, step charts are good for showing how things like postal rates or interest rates change over time.

Step charts are not native to Excel, but it’s not hard to build a step chart, with a bit of data tweaking and some smoke-and-mirrors formatting. There are two ways to build step charts, one geared towards timelines (line charts with X-axis dates) which uses duplicate points, and the other towards XY Scatter charts which uses horizontal and vertical error bars.

If it seems like too much work to build your own step charts, I recently included this chart type in Peltier Tech Charts for Excel, my commercial Excel charting add-in.

Step Charts in Excel Line Charts

In an Excel line chart, points are usually connected by straight lines (and I can tell you why you shouldn’t use smoothed lines). But the tendency between points is to visually interpolate the values, while in some cases the data is constant from one point to the next, where it jumps to a new value.

Here is our postal rate data and a line chart showing the rates over time. We know from the table that in 2004 the postal rate was 37¢, even though the line in the chart passes 38¢ sometime during the year.

We need to construct our chart using horizontal and vertical lines. Line charts only allow vertical error bars, not horizontal ones, so that approach is not available. But all we need are extra points to help define the horizontal then vertical line segments connecting adjacent points. This approach, and the similar approach involving Names which is covered later, is based on a discussion many years ago with Michel Gerday of Belgium.

Let’s start with our original data, in the blue shaded range below left. In fact, I’ve added a point for 1-Jan-2000 and another for 31-Dec-2018, so we have horizontal lines extending beyond the data to the edges of our chart.

I have duplicated this data as the yellow shaded range, below right.

In order to get our extra points, we need intermediate points with the old rate and the new date, to make the treads and risers of our steps. The easiest way to do that is to delete the first date (X value) and last rate (Y value) in the top half of our data, the red shaded cells below left. To delete one cell, right click on it, select Delete…, then select the Shift Cells Up option, and press Enter. The result is shown below right.

Strictly speaking we don’t need to do the next step, because Excel line charts sort the data by date when it is plotted. But I have sorted the data by date, below left, because this makes it easier to understand what the transformed data looks like. Note that sorting has shuffled the data into alternating yellow and blue shaded rows.

The resulting chart is a plain line chart, below right.

Between 1-Jan-2000 and 7-Jan-2001, the postage is constant at 33¢, forming a horizontal line. Then on 7-Jan-2001, the postage jumps from 33¢ to 34¢, resulting in a vertical line. And we see alternating horizontal and vertical segments in the table and in the resulting chart.

The data wasn’t too hard to transform, once you figure out how, and once you make the chart, you’re done.

Step Charts in Excel Scatter Charts

You could make step charts from scatter charts by manipulating the data as we did for the line chart above. However, it’s not neccessary to change the starting data to make step charts from scatter charts, but you need to add some data.

Here is some simple data, the US marginal tax rate Y vs. income X, for a single filer in 2018. It starts at 10% for zero income (after subtracting the standard or Schedule A deduction), and rises to 37% for an income exceeding $500,000. But the simple XY chart with straight lines between points does not show the stepwise manner of the increase.

Let’s start by adding one point to the Income-Rate data, 37% at $800,000 in row 10, so the chart ends with a horizontal segment like the postal rate chart above. We don’t need to add a point at the beginning because it starts at an income of zero.

We’ll also add two columns. dX is the difference between the next income value and the current one (cell D3 has the formula =B4-B3). dY has the difference between the current rate and the previous one (cell E4 has the formula =C4-C3).

Start with an XY Scatter chart with no lines.

Select the chart, click the plus icon (“skittle”) next to the chart, and check the Error Bars box. This adds horizontal and vertical error bars using the calculated “Standard Error”. I’m never sure how Excel calculates these things, so I almost always use custom error bar values that I’ve defined with worksheet formulas.

So now we will apply custom error bar values, as I have described in Custom Error Bars in Excel Charts. Select the horizontal (X) error bars and press Ctrl+1 to format them. Under End Style, choose No Cap, and under Error Amount, choose Custom, and click the Specify Value button. Clear the Positive Error Value box and select the dX values, D3:D10. In late versions of Excel you can simply clear the Negative Error Value box, but in 2007 through early versions of 2016 you have to put ={0}, zero in curly braces.

Our horizontal error bars now reach from each point to the income value of the next.

Select the vertical (Y) error bars and press Ctrl+1 to format them. Under End Style, choose No Cap, and under Error Amount, choose Custom, and click the Specify Value button. Clear the Negative Error Value box and select the dY values, E3:E10. In late versions of Excel you can simply clear the Negative Error Value box, but in 2007 through early versions of 2016 you have to put ={0}, zero in curly braces.

Now the vertical error bars reach from each point down to the end of the previous point’s horizontal error bar.

All you need to do is format the seroes to use no markers, then format the lines of the horizontal and vertical error bars.

Not too complicated once you are familiar with custom error bars.

Advanced Step Charts in Excel Line Charts

In a couple comments when this post was first published, Lori reminded me that there are ways to make line chart step charts which do not require changing the existing data or creating new dedicated data ranges. These require a working knowledge of how line charts deal with X axis dates, i.e., that the data are sorted by date before being plotted. You can refer back to the color-coded illustrations of the data in the first section above.

SERIES Formula Adjustment

The first approach is just a tweak to the series formula in the step chart. Let’s start with the following data and chart.

The series formula in the chart is:

=SERIES('Step Chart'!$C$2,'Step Chart'!$B$3:$B$15,'Step Chart'!$C$3:$C$15,1)

The X values (purple text) consist of the column of dates in B3:B15, and the Y values (blue text) are the values in C3:C15 next to the dates.

In our first approach above, we used the dates twice, except for the first date, and we used the Y values twice, except for the last Y value. We can select multiple ranges for X and Y values in a series formula, if we separate them by commas and enclose them in parentheses. The modified series formula is:

=SERIES('Step Chart'!$C$2,('Step Chart'!$B$4:$B$15,'Step Chart'!$B$3:$B$15),
  ('Step Chart'!$C$3:$C$14,'Step Chart'!$C$3:$C$15),1)

The dates and values are not in order, but Excel line charts sort them by date, so our chart with the new series formula shows the nice steps:

Advanced Line Chart Step Chart - Chart with New SERIES Formula

I’ve written a tutorial that shows how you can Convert a Line Chart to a Step Chart with VBA. Essentially it hacks into the SERIES formula, converting the line chart formula to the step chart formula.

This simple change to the series formula works great for lines charts because of the sorting, but not for XY Scatter charts, which do not sort the data. Here is a scatter chart with the same series formula as the line chart above:

Advanced Scatter Chart Step Chart - Chart with Wrong SERIES Formula

You can use a modified series formula for a scatter chart, but the modification needs to reorder the data:

=SERIES('Step Chart'!$C$2,('Step Chart'!$B$3:$B$4,'Step Chart'!$B$4:$B$5,'Step Chart'!$B$5:$B$6,
  'Step Chart'!$B$6:$B$7,'Step Chart'!$B$7:$B$8,'Step Chart'!$B$8:$B$9,'Step Chart'!$B$9:$B$10,
  'Step Chart'!$B$10:$B$11,'Step Chart'!$B$11:$B$12,'Step Chart'!$B$12:$B$13,'Step Chart'!$B$13:$B$14,
  'Step Chart'!$B$14:$B$15,'Step Chart'!$B$15)
  ('Step Chart'!$C$3,'Step Chart'!$C$3:$C$4,'Step Chart'!$C$4:$C$5,'Step Chart'!$C$5:$C$6,
  'Step Chart'!$C$6:$C$7,'Step Chart'!$C$7:$C$8,'Step Chart'!$C$8:$C$9,'Step Chart'!$C$9:$C$10,
  'Step Chart'!$C$10:$C$11,'Step Chart'!$C$11:$C$12,'Step Chart'!$C$12:$C$13,'Step Chart'!$C$13:$C$14,
  'Step Chart'!$C$14:$C$15),1)

When the data is ordered properly, the scatter chart shows the right stepping pattern:

Advanced Scatter Chart Step Chart - Chart with Corrected SERIES Formula

Using Names

If you’re familiar with Names, you can define Names for the X and Y values of the chart:

Name: LineX

Refers To:
=('Step Chart'!$B$4:$B$15,'Step Chart'!$B$3:$B$15)

Name: LineY

Refers To:
=('Step Chart'!$C$3:$C$14,'Step Chart'!$C$3:$C$15)

These ‘Refers To’ formulas are the same as the individual X and Y value parts of the line chart Series Formula above. (Note: you would need more complicated Names if you’re using scatter charts.)

You can insert the Names into the SERIES formula:

=SERIES('Step Chart'!$C$2,'Step Chart'!LineX,'Step Chart'!LineY,1)

And the step chart works as above.

Advanced Line Chart Step Chart - Chart with Names

Using Tables

Excel’s powerful Tables let you build dynamic formulas and charts, because any formulas that reference all rows or columns of a table will update to keep including all rows or columns if rows or columns are inserted or deleted. For example, I can select the data range and press Ctrl+T to convert the range into a Table. The Table has special formatting, but also special sorting and filtering capabilities, and there are special ways to reference cells and ranges within the Table. I won’t go into them here in detail, but you could read Structured Referencing to Identify Parts of Excel Tables for some examples.

Here is a table with a chart of dates and values from the table:

If I extend the table by two rows, so the last point changes from 1-January to 1-March, the chart updates without my having to adjust the series formula or make any other changes:

Regular Line Chart from an Expanded Table

This works when all rows of a table are involved in the formula. However, the series formula we generated for our step chart uses all rows plus all rows except one:

=SERIES('Step Chart'!$C$2,('Step Chart'!$B$4:$B$15,'Step Chart'!$B$3:$B$15),
  ('Step Chart'!$C$3:$C$14,'Step Chart'!$C$3:$C$15),1)

Therefore the formula only partially updates (in bold):

=SERIES('Step Chart'!$C$2,('Step Chart'!$B$4:$B$15,'Step Chart'!$B$3:$B$17),
  ('Step Chart'!$C$3:$C$14,'Step Chart'!$C$3:$C$17),1)

The original chart…

… doesn’t update properly when the table is extended:

Step Chart Line Chart Improperly Updated from an Expanded Table

In fact, you can’t use a simple series formula adjustment with a Table and expect the step chart to work out. The Names used above also will not work, but you can use a definition that makes use of the Table’s Structured Referencing and offset functions:

Name: LineX

Refers To:
=(OFFSET(Table1[Date],1,0,ROWS(Table1[Date])-1,1),Table1[Date])

Name: LineY

Refers To:
=(OFFSET(Table1[Value],0,0,ROWS(Table1[Value])-1,1),Table1[Value])

In Structured Referencing, Table1 is the name of the Table, while [Date] and [Value] refer to the whole columns of dates and values, not including the header row.

As before, insert the Names into the SERIES formula:

=SERIES('Step Chart'!$C$2,'Step Chart'!LineX,'Step Chart'!LineY,1)

The original step chart with dates up to 1 January 2019 using these Names…

Step Chart Line Chart from a Table using Names

… updates properly when the table is extended to March 1 2019:

Properly Extended Step Chart Line Chart from a Table using Names

The Software Solution

Excel is flexible enough to make step charts (and many other non-native chart types), but all the data arranging and chart formatting can become tedious.

I’ve recently added step charts to Peltier Tech Charts for Excel, my commercial Excel charting add-in.

It’s easy: select the data, click the button, choose your options in the dialog…

… and out pops a nice step chart.

Posted: Thursday, July 19th, 2018 under Chart Types.
Tags: Step Charts.
Comments: 17

Chart with Actual Solid Lines and Projected Dashed Lines

Wednesday, July 11, 2018 by Jon Peltier 9 Comments

Wednesday, July 11, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Show Projected Data with Dashed Lines

This tutorial shows how to create a chart with historical (actual) data and future (projected) data, with the historical data formatted with a solid line and the future data with dashed lines.

It’s not hard to do, you just need to know some data layout and chart formatting tricks.

Background

I came across this tweet, where Max wanted help with an Excel chart:

I have a population data for 2001 to 2018 and projected population data for 2025, 2030 and 2040 only. What I would like to do is a full line from 2001 to 2018 and a dotted line from 2018 to 2025 to 2030 to 2040. Is that possible ?

— Max_Habs (@Max_Habs) July 10, 2018

Basically he wants to plot all of his data, with dashed lines for the projected data.

First Attempt: Tedious Reformatting Bit by Bit

I don’t have Max’s data, so I made up my own. In my Excel worksheet, I’ve formatted the predicted values with gray italic text. I made a simple chart of this data, using a chart type of Scatter with Straight Lines.

Then I selected the first line segment for the projected data (select one line segment by selecting the entire line with one click, and selecting the specific line segment with a second click), and I formatted it to have dashed lines. So far, so good, except that legend went a bit funny. When individual points re formatted differently, and there is only one series in the chart, Excel helpfully changes the legend to show each individual point, taking up half the chart.

No big deal, the legend is redundant anyway, so delete it. Then reformat the rest of the projected line segments.

This chart wasn’t too hard to do, but imagine if you had dozens of line segments to reformat? Hard enough to select each itty bitty one, then your brain begins to turn into mush from the repetition.

The Right Way: Separate Series for Actual and Projected

The better way to make the chart is to start with better data. Put the Actual data in one column and the Projected data into the next. Repeat the last Actual value in the Projected column.

Now plot the data. Above I simply jumped ahead to a scatter chart without explanation, but anyone who knows me knows I love to explain stuff.

Here’s what happens when you try to make a line chart, which is the first thought most users have, until they’ve been burned. Excel’s line charts don’t treat the X values as numerical data, but as text labels. So the first part of the chart has data every year, and it looks okay, but then the projected data comes at irregular intervals (7, 5, and 10 years). Treating these years as labels, the widely spaced projections are still plotted one slot apart, so the orange projected line does not extend smoothly from the actual data.

So use Scatter with Straight Lines as your chart type. I know you’d like to use Scatter with Smooth Lines, and for this example it wouldn’t make much difference. But there are reasons to avoid the smoothed lines.

At first, the fact that the projected line is formatted differently (orange) may seem like a drawback to this approach, but it’s actually a benefit. Here all you have to do is select the entire Projected series, and format it once, changing the color to blue and the line style to dashed.

A little formatting and it’s finished. I removed the legend, adjusted the X axis scale, and added data labels on the last visible point of each series.

The Tricks

I used a few simple concepts so my chart is easy to make and easy to maintain. In case you missed them, here they are:

Separate columns for actual and projected data
Repeat last actual value in projected column
Separate chart series for actual and projected lines
Scatter chart type, not line chart

Posted: Wednesday, July 11th, 2018 under Formatting.
Tags: predictions, projections.
Comments: 9

Series Order Effects When Applying a Chart Template

Thursday, June 28, 2018 by Jon Peltier 5 Comments

I was demonstrating how to save a chart template, so you can apply a lot of custom formatting to new charts. Maybe I should write a post about that sometime. But I noticed some strange behavior. I started with identical charts, then applied different custom formatting to them, then I assigned chart types from either built-in chart types or from custom chart templates. I noticed that the end result did not always look the same.

For example, I might start with a simple clustered column chart.

Original Clustered Column Chart

Then I might change one series to a line type.

Clustered Column Chart with One Line Chart Series

Or I might assign one series to the secondary axis. The blue bars of ‘alpha’ are plotted in front, partially hiding the orange and gray bars of ‘beta’ and ‘gamma’.

Clustered Column Chart with One Secondary Axis Series

What if I want to reverse these changes?

If I change the formatting (chart type or axis group) of the individual series that I initially changed, the chart reverts to its original appearance with no surprises.

If I go to Change Chart Type on the Chart Tools > Design tab, however, the resulting chart has its series plotted in a different order than the original.

Rearranged Series Order

It took me a long time and many sacrificial charts to figure out the behavior, but I think this is it. In the first case, when I changed series ‘alpha’ to a line chart series, Excel moved ‘alpha’ from first to last in the legend. This is because Excel draws areas first, then columns, then lines. In the second case, when I moved series ‘alpha’ to the secondary axis, Excel again moved ‘alpha’ from first to last in the legend. This is because Excel plots the primary axis series first, then the secondary axis series.

The final chart above still has ‘alpha’ last in the legend. So I think if you change one series at a time, Excel keeps track of each individual series. But when you change all series en masse, by applying a built-in chart type or a custom chart template, Excel applies formats to the series in order.

The order in which Excel applies formats is the legend series order, or rather, in the order the series are drawn, from back to front.

The order in which we might have expected Excel to apply formats is the plot order, or the order series are listed in the Select Data Source dialog.

I’ll say it again:

Formats are applied according to legend series order, not source data series order.

Here is a further illustration of this confusion. Start with the original chart, change series ‘alpha’ to a line, and save the chart as a template.

Save Chart As Template

Now assign this template to the chart just used to define the template. You wouldn’t expect it to change the chart, but the result is changed.

Apply Chart Template to Chart Saved As Template

Excel knows the first series (‘alpha’ in the template) is formatted as a line, so it took the first series in the legend of the chart it was changing (‘beta’) and made it the line series. The second series (‘beta’ in the template) is formatted as an orange bar, so the second series in the legend (‘gamma’) becomes an orange bar. Likewise with the gray bars.

This isn’t a big deal, but it is very confusing, and it explains why your series may appear in a different order than expected when you apply a chart template.

Posted: Thursday, June 28th, 2018 under Excel Oddities.
Tags: Chart Types, Templates.
Comments: 5

Microsoft MVP Logo

Importing and Cleaning Data with Power Query

Tuesday, June 19, 2018 by Jon Peltier 5 Comments

If you work with any type of data, then you know that rarely does it come perfectly in the format you need. Power Query (also called Get & Transform in Excel 2016) is the solution to this problem. Power Query is an amazing ETL tool (Extract, Transform, and Load). If you’re not using it then you’re missing out on the best new feature in Excel.

For a more detailed look at what Power Query is and what you can do with it, check out this Introductory Guide to Power Query.

Power Query can connect to many different types of data, but in this post we’ll take a look at importing data from an Excel workbook and doing some basic data cleansing transformations on some messy data.

The Data

For this post, I’ve prepared some fake sales data (download This Week’s Garbage Data File.xlsx to follow along with the example). It’s presented in a typical fashion which is human readable, but not great for any type of further analysis. In this post, we’ll explore how we can use Power Query to get this data into a standard tabular format which will help facilitate any analysis done later on.

There are a few problems with the data we want to import, and we’ll need to fix them before our data is ready to use.

The data has been summarized by date, but the date pertaining to each set of sales only appears once as a heading.
There are leading spaces in front of the sales person which will need to be removed.
The name of the sales person and their employee ID number have been concatenated together.
There are rows of spaces throughout the data that will need to be removed.
Sales for the different regions have been pivoted across four different columns. We will want our sales in one column with an extra column defining the region.
We have total rows for each of the sections of data that will need to be removed.

Importing Our Data

The first step we need to take is to import our data.

Go to the Data tab and press the Get Data command then choose From File and then From Workbook in the resulting menus.

A file picker menu will appear and we can then navigate to the Excel file we want to import.

Once we’ve picked the file to import, we will be presented with the Navigator window. This will allow us to select the Sales sheet from our workbook which contains the data we want to import. A preview of the data can be seen on the right hand side when selecting sheets.

If our workbook had more than one sheet of data which we wanted to import, we can select multiple sheets using the Select multiple items check box. This would create a separate query for each of the sheets selected and save going through the first couple steps individually.

Now we can either choose to Load the data or Edit it. If we choose to load it, then it will load into an Excel Table with no transformations applied. We want to edit it, this is where we’ll be able to apply our data cleaning transformations. Note: In some builds of Excel, the Edit button is labeled Transform Data.

Press the Edit button and this will open up the Power Query editor.

The Power Query Editor

We are now inside the Power Query editor and we can begin building our data transformation to get our data into proper tabular format.

Any transformations you apply to the data will appear as a step in the Applied Steps area. We can step forward and backward through the query by clicking on any of the steps listed in this area.

On the left side of each step is an X that can be used to delete a step and on the right there is a settings icon which can be used to edit the step.

Getting the Date into its Own Column

The first thing we’ll do in our transformation is get the date associated with each section into its own column. Notice that each of these date headings in our data is separated with a colon. Since no other data in this column apart from the dates have a colon, we can use it as a delimiter to split out the data.

To split this data out, right click on the column heading then select Split Column and then choose By Delimiter.

Select Colon from the list of delimiters and press the Ok button.

Now we have a new column with dates followed by a series of null values until the next date. Right click on the new column and select Fill then choose Down. This will fill the dates down the column replacing the null values with the date from above.

Split Name and Employee Number

In the next step we’re going to split out the sales persons name and their employee number. The name and employee number are always separated by an opening parenthesis so we’ll use that as the delimiter to split the column.

This assumes that an opening parenthesis will only ever appear in our first column when it is separating the employee number.

Now we right click on the first column and select Split Column then choose By Delimiter.

This time we need to use a Custom delimiter since the opening parenthesis is not a standard option. Add in the “(” and press the OK button.

Notice that our new column with the employee number still has the closing parenthesis. We’ll need to clean this up. We can right click on the column and select Replace Values from the menu. Then we can replace the “)” character with nothing. This will remove it from our data.

Remove the Leading Spaces for the Name

Now we can remove the useless leading spaces that were used for indentation in the presentation of the data.

We just need to right click on the column and select Transform and then Trim. This will remove any leading or trailing space characters from our data.

Remove Unnecessary Rows

We’re now ready to remove the unnecessary rows in our data including the total rows and blank rows from our original data file. We also have some rows that are now useless as we’ve already extracted the useful data from them. We can remove all of these rows in the one step.

In fact, we’ll be able to do this by filtering on the first column. Click on the filter icon on the right side of the column heading and then deselect the useless rows (null, Sales for, Sales Person, and Total).

You might be asking why we filtered out the row containing Sales Person. Doesn’t that row contain useful column heading information? Couldn’t we use that and promote it to the column headings?

Yes it does, but we can’t use it to promote to column headers because one of the values in that row is the date from the first step in our transformation. That value will likely change if we’re importing a different set of data with this query.

If we were to promote it to a column heading then we’d have a column heading with the label “2018-01-01”. Any further step that references this column, would be referencing this value which won’t exist. This is were our query would break.

Instead we’ll remove this row and rename our current generic column headings to avoid this problem.

Rename Column Headings

Now we can rename all our columns to their appropriate names. Double click on the current column name to rename a column. We’ll name these Sales Person, Employee ID, Date, North, East, South and West going from left to right.

Unpivot the Region Columns

Now we have an almost tabular set of data. The only thing that remains is to unpivot our four region columns. All the values in these columns represent the same metric of sales and should be in one column with another column that tells us what region the sales amount was from.

Select the North, East, South and West columns by holding Ctrl and clicking on each heading. You can also select the first (North) column then hold Shift and select the last column (West). Now right click on any of the column headings and select Unpivot Columns.

This will result in two columns called Attribute and Value. We can rename these to Region and Sales respectively.

Change Data Types

Now our data is just about ready. At the end of any data transformation, it’s good practice to change the data types of columns to the appropriate type.

For the most part our data has the right data types, but our Sales column can be changed to a currency type. Click on the data type icon on the left side of the column heading and select the Currency type from the menu.

Close and Load the Query

Now that our data transformation is complete, we are going to need to save our query. Go to the Home tab in the query editor and press the Close & Load button. Just remember, you’re still going to need to save the workbook when you’re done in the query editor.

Clicking on the top part of the close and load button will load the data into a new sheet in an Excel table. You can also click on the lower part of the button and select Close & Load To to open the Import Data dialog for more loading options like loading the data directly into a pivot table or into the data model.

For now, we’ll just load the data into a table.

Now we’ve got our data in a ready to use tabular format!

Updating the Query for New Data

Now what happens when we get new data? (Download This Week’s Garbage Data File.xlsx to try this.)

We can use this query again to transform that data. Go to the Data tab and press the Queries & Connections button. Then right click on the query and choose Edit.

Now we can navigate to the Source step in the Applied Steps area.

Replace the source location in the formula bar with the path and file name of the new data and press the Enter button to confirm the changes. Then we can close and load the query. Our data will update based on the new file!

Note, this will overwrite the previous data, so if we want to retain the old transformed data we’re going to need to create a copy of the workbook before performing these updating steps.

Conclusions

Everything we did in this transformation was using the basic point and click interface in the Power Query editor. Once you get the hang of it, making queries to shape your data into the format you want becomes super easy. No workbook formulas, no difficult VBA and no copying and pasting required.

The best part is, if we need to repeat this transformation with another similar data set, then we can update the file path in the query and press the refresh data button and our new data will be transformed and loaded into Excel. Our messy data problem has become a simple refresh.

This is why Power Query is the best new feature in Excel that you need to start using.

About the Author

John MacDougall is a Microsoft MVP and former actuary who currently works on data analysis and debugging code at an ad tech company. He shares his knowledge and passion for Excel and Power BI through his website How To Excel.

Posted: Tuesday, June 19th, 2018 under Data Techniques.
Tags: Guest Post, Power Query.
Comments: 5

Peltier Tech Excel Charts and Programming Blog

Add a Horizontal Line to an XY Scatter Chart

Paste Special

Add a Horizontal Line to a Column or Line Chart

“Lazy” Horizontal Line

Add a Horizontal Line to an Area Chart

“Lazy” Horizontal Line

Follow-Up

Historical

More Combination Chart Articles on the Peltier Tech Blog

Step Charts in Excel Line Charts

Step Charts in Excel Scatter Charts

Advanced Step Charts in Excel Line Charts

SERIES Formula Adjustment

Using Names

Using Tables

The Software Solution

Show Projected Data with Dashed Lines

Background

First Attempt: Tedious Reformatting Bit by Bit

The Right Way: Separate Series for Actual and Projected

The Tricks

The Data

Importing Our Data

The Power Query Editor

Getting the Date into its Own Column

Split Name and Employee Number

Remove the Leading Spaces for the Name

Remove Unnecessary Rows

Rename Column Headings

Unpivot the Region Columns

Change Data Types

Close and Load the Query

Updating the Query for New Data

Conclusions

About the Author