Chart Data

Good Chart Data

Thursday, November 18, 2021 by Jon Peltier 6 Comments

Thursday, November 18, 2021 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2021, All rights reserved.

Tips and Tricks

Everybody loves their Excel tips and tricks. I know I’m famous for my extensive collection of Excel charting tricks. But the most important charting trick is Get The Data Right. The secret of successful Excel charting begins and ends with Good Chart Data. I teach it in all of my charting workshops and seminars, so I was surprised that I did not have a blog post dedicated to good chart data. But now I do.

Good Chart Data

What do I mean by “good” data? Of course, “good” has to do with the quality of the data. Where is the data from? How was the data measured or collected? Is the source reliable and trustworthy?

All of this is important, for any data you consume in Excel or in any other program. But right here, “good” data means data that can easily be rendered in a chart, without having to make excessive adjustments to the chart. Good chart data may not be good display data, but data that has been optimized for display, say in that annual shareholder’s report, is almost guaranteed to be bad for charting.

“Good” data has a layout that makes chart creation easy for you, so Excel knows how to partition the data between the important parts of the chart. X values or categories. Y values. Series names.

“Good” data also has as little formatting as possible. Enough formatting to make it readable, but not so much that it causes retina pain.

You can make good charts from bad data, but it will take longer, and you will have to muck around with the chart for a while to make it work. It’s better to spend five minutes with the data now than to spend five hours trying to clean up a chart later.

Tl;dr Good Chart Data

Good chart data has the following characteristics:

The data exists in a contiguous range: no blank rows or columns
The data is minimally and consistently formatted: easy to validate visually
The data is aligned with Y values in columns (shaded blue below)
X values are located in the leftmost column (purple)
Series names are located in the topmost row (red)
The top left cell may provide some magical behavior (gold)
The data may exist in an Excel Table

A Good Tabular Display is not Good Chart Data

What if you want to plot your data but also display it in an optimally formatted way? The good new is, you can. The bad news is, it takes more work. You should have two different data ranges: one arranged for best charting outcomes, the other formatted for visual consumption. Both of these ranges should link to the same original source, to make sure changes are reflected everywhere, and you maintain one version of the data.

Good Chart Data is Contiguous

A good chart data range is contiguous, that is, it is comprised of data in a single block of cells. There are no completely blank rows or columns separating the data into separate areas.

Why is contiguous data important? Because Excel tries to be smart when you click a button. If you select any single cell in the range shown above and click any chart button to insert a chart, Excel tries to determine the extent of your data. Excel will go from the active cell up, down, left, and right until it finds either the edges of the worksheet or a blank row or column, and it will include all of the data within this range for the chart. This is so much easier than having to select the entire data range, especially if the data extends beyond the first visible screen.

Excel does the same hunt for the current region when you convert a range to a Table, or create a Pivot Table, or do any number of other things in Excel.

What About Blank Cells?

It’s perfectly fine to have blank cells within the data range, as long as there are no completely blank rows or columns.

Keep in mind that successful charting means that these “blank” cells should be completely empty. A cell with a formula that returns “” is not blank, because it contains a formula. If you have the formula return NA() instead of “”, the cell will not look blank, it will contain the #N/A error. This looks ugly, but a chart treats most #N/A values as blank cells, while it treats “” as text. Text might be plotted as a zero, or it might mess up an axis.

A cell that contains a space character is also not blank, because it contains that space character. Sometimes people get in the habit of typing the space instead of just skipping the cell. This kind of non-blank cell is hard to fix because both the cell and the formula bar appear blank.

Good Chart Data is Lightly Formatted

You should only use as much formatting as you need to help you quickly scan the data.

The range below is a bit over-formatted. The header row is bold and multi-colored, and the red and yellow are distracting and may eventually cause eyestrain. Bold text and light gray fills might be okay for headers, though they are unnecessary if the headers follow one or more blank rows.

The dark borders on all of the cells break up the appearance of the data range and the dark lines compete for attention with the numbers. The default light gray cell borders are sufficient. I’ve programmed some buttons in my general purpose and charting software to apply light and medium gray borders to selected ranges.

The green shading can be distracting. (See my arbitrary pattern? All cells with 3 are shaded, while b and d are shaded because they have no 3 in their rows. I don’t know why.) If you find shading helps to identify certain values, don’t manually color the cells: use Conditional Formatting instead, so the shading goes away if the condition is no longer met.

Good Chart Data is Not Centered

You should avoid centering your data. It might look “nice”, but centering hides important characteristics of the data. By default, text is left-aligned in cells and numbers are right-aligned. A common feature of “bad” data is numbers stored as text, and centering everything hides this distinction.

Here is a data range with all of its cells centered. There are some small triangular flags in some cells that indicate a possible error, but I think most of us have learned to ignore warnings like this.

When we uncenter the data, it’s easy to detect the cells with numbers stored as text, even if we’ve ignored the green flags.

We can select the flagged cells, click the little warning dropdown, and convert them to numbers.

Now that we’ve converted text to numbers, we still have difficulty parsing the numbers, because they have inconsistent numbers of decimal digits. Some numbers look larger than others, but they merely have a longer tail after the decimal point.

When we apply a consistent format, we can tell that the larger numbers are in fact longer.

It’s now very easy to identify the largest numbers in each column. You might want to check that value of 345 in the “beta” column since it’s 100 times as large as the rest of the column.

Good Chart Data is in Columns

Excel charts can work with data in columns or in rows. You can use either arrangement and sometimes one just works better than the other. If a chart’s source data has more rows than columns, Excel creates the chart with series in columns. A pivot chart always plots the pivot table with series in columns.

It is probably good practice to get used to using data in columns, because of the way a database table is structured. A database has fields and records. A field is a variable or measurement, such as date, eye color, serial number. A record is a single instance of a set of values for these fields.

When printed on paper or viewed onscreen (or imported into Excel), a database table is shown as a grid of rows and columns. Each column is a field or a variable, and each row is a record. The first row of the database table is a header row that contains the field label. Usually one column of the table, often the first column, contains a unique identifier or key for that particular record.

In the table above, we have fields for TLC, alpha, beta, and gamma. We have records for a, b, c, d, e, and f.

Good Chart Data Has a Header Row

Like a database table, chart data should have a header row. When data is plotted with series in columns, this header row is used for series names, that is, the labels that appear in the legend of the chart.

Database tables have one header row only. Chart data ranges usually only have one row, but you can use more, often to good effect.

Good Chart Data Has no Subtotal or Total Rows

Subtotals and totals help to understand data, but they have no place in the source data for a chart. Suppose I have monthly values I want to plot.

If I put subtotal rows into my source data, it breaks up the visual appearance, so it’s hard to scan the individual values for discrepancies.

The quarterly subtotals disrupt the flow of data in the chart, and the much larger magnitude of the subtotals shrinks the monthly values.

Chart Monthly Values and Quarterly Subtotals

If I include yearly totals, the quarterly subtotals shrink, and the monthly values are lost in the weeds.

Chart Monthly Values, Quarterly Subtotals, and Yearly Totals

Note that you can filter the quarterly and yearly categories from the chart in recent versions of Excel. Also note that a pivot chart will only show values from the pivot table and not totals and subtotals.

Good Chart Data Has a Header Column

Just as a database table has a unique key field, the data range for a chart should have a header column identifying each row. This column is generally used for labels or values which are plotted along the X-axis of a chart.

This X-axis column should be the first column, to the left of any Y-axis values. This makes charting easier because Excel looks to the left for X values. But you would be amazed at how many people have trouble plotting their data when they have placed their Y values to the left of their X values. You can always tell Excel which data comes from where, but it is a lot more work, especially if you have to do it repeatedly, again and again, ad nauseam (can you feel the tedium?).

Another trick to help Excel identify your X values is to make the column of X values different from the Y values.

First Column Different: Text

Probably the most common way to make the first column different is by filling it with text. While month names are a component of a date, a list of month names is text, as shown here.

When you select the data range (or one cell inside the data range), Excel uses the text labels in the first column as X-axis (category axis) labels, it uses the other columns as Y-axis (value axis) values, one column per series, and it uses the labels in the header row as series names. Here are line and column charts using this data (and area charts work the same way). Months from the first column are automatically placed along the category axis, headers from the first row are automatically used as legend entries (series names). and each column of Y values is plotted as a distinctly formatted series.

Line and Column Charts Made from Data with Text in First Column

It’s the same with a bar chart, except that the category and value axes are switched. That’s right, in a bar chart, the X-axis is vertical and the Y-axis is horizontal. But the origin of the axes is at the bottom left in all of these charts, so values increase from left to right (like months advance from left to right in a line or column chart). Similarly, months advance from Jan to Jun moving bottom to top in a bar chart (like values increase from bottom to top in a line or column chart).

Bar Chart Made from Data with Text in First Column

Confused? Since the months are listed from top to bottom in the worksheet, it would make sense to plot them from top to bottom in the chart. But there is logic in how Excel does it, and it is also pretty easy to fix when you know how. See Excel Plotted My Bar Chart Upside-Down for the simple technique.

XY Scatter charts are different: they have numerical axes for both X and Y (category and value) axes. When you plug in text for the X values, the chart doesn’t know what to do. Normally text is considered to have a value of zero, but for X values in an XY chart, Excel substitutes the counting numbers 1, 2, 3, up to the number of points. (Excel also does this if no X values have been specified.)

XY Scatter Chart Made from Data with Text in First Column

What if ever have an XY chart with the numbers 1, 2, 3, etc. along the axis, and you know you selected numerical data for the X values? Check the range of X values: there is probably a number stored as text, or an actual text label, somewhere in the range.

First Column Different: Dates

Using dates in the first column is another way to make the first column different. Excel recognizes the date formatting of the cell and parses the column as X values. An added bonus is that line, column, area, and bar charts have a special date type of axis (as opposed to the text axis shown above) that provides unique formatting options. This range has dates in the first column; notice that the dates are not uniformly spaced, but are taken on the 1st and 8th of each month.

Here are line and column charts made from this data. The line chart looks great and shows some of the enhanced date axis features. The data points are not equally spaced but reflect the non-uniform spacing of the data. Also, there is a tick mark and axis label on the first of each month, despite the nonuniform month lengths.

Line and Column Charts Made from Data with Dates in First Column

The column chart is wacky though. To accommodate the nonuniform data spacing, the chart has a slot for each day, even days without data. So each column chart series has to appear within the slot for its given day, and there are lots of days in between. This column chart should really be called a toothpick chart.

You can always change the axis type to text, and the column chart will look normal. But you lose the non-uniform nature of the dates and the first-of-the-month labels.

Line and Column Charts Made from Data with Dates in First Column, but with Text Axis

An XY scatter chart treats dates for X values just like any numerical X values. You can see the non-uniform spacing of the data.

XY Scatter Chart Made from Data with Dates in First Column

But the axis is not labeled as naturally as the line chart above. Using the value axis algorithm, Excel picked an axis that begins at 44,180 and increases to 44,280 in steps of 20. (Excel stores dates as whole numbers starting on 1 January 1900, so 44,180 is 15 December 2020 and 44,280 is 25 March 2021.) You can format the XY chart’s axis to begin on the 1st of a month, and have a tick label on the 1st of the next month. But for a non-trivial number of months, it’s impossible to repeat this pattern with Excel’s default axis labels.

First Column Different: Numbers

If you have numerical X values in the first column, they won’t be different from the numerical Y values in the other columns. You know those values in the first column are years, and I know they are years, and the header label even says “Year”, but Excel simply recognizes them as numbers.

This doesn’t matter for an XY Scatter chart: Excel almost always treats the first column of numbers as X values. This XY chart shows years along the X-axis, as intended.

XY Scatter Chart Made from Data with Numbers in First Column

When Excel created this line chart, it saw the numbers in the first column and decided to plot them as Y values. There are two consequences of this: First, there is a series of values near 2000 floating far above the intended Y values in the chart; second, no X values were specified, so Excel simply used the counting numbers 1, 2, 3, etc.

Line Chart Made from Data with Dates in First Column

You could avoid this by selecting just the Y values when the chart is created, and specifying the X values later. You could also do this with years by entering dates in the first column (1/1/2015, 1/1/2016, etc.) and formatting them using a custom number format of YYYY, which will display just the year numbers. Since the column now is formatted as dates, Excel will plot them the way you want.

You cannot avoid this by formatting the first column of numbers as text, Excel is too smart, and it converts the numeric text as numbers, and therefore as Y values.

First Column Different: Top Left Cell

You may have noticed the label “TLC” in the header row of the first column. TLC stands for Top Left Cell, and it is a little piece of magic for Excel chart data.

One way to make the first column different from the rest is to clear the contents of the Top Left Cell, as shown below.

Data with Numbers in First Column and Top Left Cell Blank

We already know that an XY Scatter chart will plot the first column of numbers as X values with a label in the top left cell. But it also works if the top left cell is blank.

XY Scatter Chart Made Using Data with Numbers in First Column and Top Left Cell Blank

The top left cell works its magic in line (and area, column, and bar) charts. This line chart was generated automatically with the column below the blank top left cell as X values, and the columns below actual labels as Y values. Finally, a way to plot numbers as X values in a line chart without worrying about formats (text or dates).

Line Chart Made Using Data with Numbers in First Column and Top Left Cell Blank

Here is an important difference between XY scatter charts and line charts. Just because you can trick Excel into plotting numbers as X-axis values in a line chart, you can’t trick Excel into plotting those X values as numbers. The next section shows a few different sets of data that will help illustrate this difference.

Spacing and Order of X Values: Numbers and Dates

Evenly Spaced Numbers

Evenly spaced X values seem to be similarly plotted in XY and line charts. In the XY chart, the X-axis begins at zero and extends beyond the highest X value. In the line chart, the first X-axis label is the first X value and the last X-axis label is the last X value, without the padding found in a scatter chart’s default axis values.

Unevenly Spaced Numbers

Unevenly spaced X values are plotted differently in XY and line charts. In the XY chart, the X-axis begins at zero and extends beyond the highest X value, and data points are plotted unevenly, reflecting the unevenness in the X values. In the line chart, the first X-axis label is the first X value and the last X-axis label is the last X value, data points and X-axis labels are uniformly spaced regardless of their apparent numerical values, and there are no X-axis labels for missing X values.

It is obvious that the line chart treats the X values as non-numeric text labels, ignoring any apparent numerical values. This is because the default axis for non-date X values is a text axis (below left). We can change this to a date axis (below right), and we see the data points are now plotted according to their unevenly-spaced numerical values. The first X-axis label is still the first X value and the last X-axis label is still the last X value. (Excel treats the numbers as dates with a format of “D”, so only the day shows.)

Different Treatment of Unevenly Spaced Numbers by Text Axis and Date Axis in a Line Chart

Numbers Out of Order

When numbers are out of order, the above behavior is even more different. The XY scatter chart draws the points and the lines connect them in order, moving left or right as the X values decrease or increase. The line chart shows the points in the order they appear in the data range: the first X-axis label is still the first X value and the last X-axis label is still the last X value, so the labels are out of order.

When we convert the text axis (below left) to a date axis (below right), we see that not only have the points been spaced according to their non-uniform values, but that the dates have been internally sorted prior to plotting. This internal sorting is a feature of charts with a date axis. The first X-axis label is the smallest X value and the last X-axis label is the largest X value, so the labels are in order and the points are plotted left to right.

Different Treatment of Numbers Out of Order by Text Axis and Date Axis in a Line Chart

Evenly Spaced Dates

Let’s look at the same charts with dates instead of regular numbers. The XY scatter chart (left) and line chart (right) plot evenly spaced dates along the X axis in a similar way. The data is evenly spaced, so the data points are plotted evenly. The XY chart extends its X-axis a little bit below to a little bit above its data range, while the line chart uses the earliest date as the first axis label and the latest date as the last axis label.

Unevenly Spaced Dates

The XY scatter and line charts also plot unevenly spaced dates similarly. The points are spaced unevenly to reflect the pattern in the data. As before, the XY chart extends its X-axis a little bit below to a little bit above its data range, while the line chart uses the earliest date as the first axis label and the latest date as the last axis label.

Dates Out of Order

Out of order dates are plotted in an XY scatter chart in the order they appear in the worksheet. The lines connecting the points start at the first point, and move left or right for earlier or later dates. In the line chart, the lines always connect from left to right, reflecting the internal sorting that takes place. As always, the XY chart extends its X-axis a little bit below to a little bit above its data range, while the line chart uses the earliest date as the first axis label and the latest date as the last axis label.

Problem with Numbers and Dates Out of Order

Perhaps it’s convenient that Excel’s line charts sort by date prior to plotting, and there are a few tricks that rely on this internal sorting. But this sorting can also lead to problems with data labels.

Here is a simple data set, with dates, sorted values, and days of the week corresponding to the dates. The chart uses dates as X values and sorted values as Y values, and the days of the week were used to label the points, using the Values as Cells option. The labels are shown in order from Monday through Friday, as expected.

Data Labels work nicely when dates are sorted in the worksheet.

Below is a data set, with the same information as above, but with the dates out of order. The chart looks the same as above, since the X and Y values are sorted by date before plotting. But look closely at the data labels. These were not sorted prior to plotting, and are no longer in order from Monday to Friday.

Data Labels don't sort when out-of-order dates are sorted in the chart.

In addition to this problem with data labels, it is likely that someone who is using the data for other purposes may misinterpret the data because they don’t notice or understand Excel’s internal sorting. For these reasons, it is a best practice to sort the worksheet data before plotting your data.

Thanks to alert reader Jim Chisholm for reminding me of this problem.

Top Left Cell Plus

We’ve seen how a blank top left cell can help Excel to parse data correctly into X and Y values and series names.

A Blank Top Left Cell Helps Excel Parse the Chart Data Range

But this concept of blank cells is more magical than that. Suppose you have two columns of category labels, for years and quarters. Each of these label columns has a blank cell in its header (blank cells are shaded gold). Each year only appears once, next to the first quarter, and there are blank cells next to the other quarters.

Excel sees the two blanks in the top left, and uses both columns as category labels. There are two rows of labels, with quarter labels in the first row and years in the second. Each year label is centered under the corresponding quarter labels, and a vertical tick mark extends from the axis to help delineate the labels.

Multiple Blank Cells in the Category Labels Region of the Chart Data Range Can Produce Multiple-Tier Labels

This is a very nice effect, only available for line, column, area, and bar charts that are using a text style X-axis. It will not work for a date axis or for an XY scatter chart’s X-axis: in those cases you will get the numbers 1, 2, 3, etc.

You may have seen this kind of data arrangement in a Pivot Table, with multiple fields in the rows area, and noticed the multiple-tier category labels in the corresponding Pivot Chart. But you’re not stuck needing a Pivot Table, you can build this data layout yourself. You are not limited to two tiers of labels, either: below you can see a three-tiered axis, and I’ve seen it used for 5 or 6 tiers.

The Multiple-Tier Axis Effect is not Limited to Two Tiers

What if you indicate to Excel that you have two rows of series names? The same effect applies. The North label is combined with the alpha and beta series names, What if you indicate to Excel that you have two rows of series names? The same effect applies. The North label is combined with the alpha and beta series names, while the South labels is combined with the gamma and delta series names, to generate compound names.

Multiple Blank Cells in the Series Names Region of the Chart Data Range Can Produce Compound Series Names

You can combine the effects of multiple-tier category axis labels and compound series names in the same chart, as shown below.

The Multiple-Tier Axis and Compound Series Names Effects Can Be Used Together

Note that these blank cells must be totally blank, and not just look blank like a formula that returns a value of “”. And while a chart can treat #N/A as a blank in a chart’s Y values, #N/A will be treated as a text label, not as a blank, when used in the series name and category labels regions of the data range.

The Select Data Source Dialog Recognizes Good Chart Data

When a chart’s data doesn’t conform to these definitions of “good” data, the Select Source Data dialog shows only a blank for the chart data range. Below the range selection box, you are told “The data range is too complex to be displayed.” This means that the data is irregular. Not all series start and end at the same row, perhaps, or the series have different numbers of points. Perhaps the series names are misaligned from the Y values, or the series are plotted out of order. Anything that prevents Excel from indicating a nice rectangular block of data.

Select Data Source Dialog for Bad Chart Data

When the data does conform to our “good” data definition, the Select Source Data dialog happily displays the address of the data range.

Select Data Source Dialog for Good Chart Data

In fact, the Select Source Data dialog is a bit more forgiving that my rules. If the data is separated by complete blank rows or columns, but otherwise fits within a rectangular range, the dialog shows the addresses of the various areas of the data range.

Select Data Source Dialog for Discontiguous but Otherwise Uniform Rectangular Chart Data

Good Chart Data May Be in a Table

An Excel Table is a special data structure which provide advanced data handling capabilities; I have converted my data into a Table below. The header row has buttons for filtering and sorting of the Table. You can add a total row if desired. There are numerous styles you can apply, some of them approaching hideous; the Table below shows the default style.

There are many benefits to storing your chart data in a Table. The major benefit is that when you add data to the row below or the column to the right of a Table, the Table automatically expands to include the added data. What’s more, any formulas that reference a Table column or row will update automatically if the Table expands or contracts. This includes chart SERIES formulas and the Chart Data Range formula in the Select Source Data dialog. So if your chart uses a Table like this for its source data, when you add rows of data, each series in the chart will add the corresponding points; when you add columns to the Table, your chart will add series. Dynamic charts made easy!

One drawback to using a Table for your chart’s source data is that the header row of a Table can contain no blank cells. This means that a lot of the blank-cell based data parsing, especially the top left cell magic, may not work with a Table. But if your first column(s) are not numerical, Excel will still automatically parse them into X values. And you could still select just part of the Table, create your chart, then manually specify the X values.

Posted: Thursday, November 18th, 2021 under Charting Principles.
Tags: Chart Data.
Comments: 6

Check Your Data: Is It Numeric or Text

Monday, January 29, 2018 by Jon Peltier 7 Comments

Monday, January 29, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2021, All rights reserved.

Is Your Data Numeric or Text?

It’s important that you check your data before charting it. One common problem with data is that it may look like numbers but get treated as text.

I’ll illustrate with a simple example provided by a customer. He emailed me to say he was having trouble with my software, and none of his charts looked right.

This is a snapshot of the worksheet he provided (the data has been changed, but not enough to affect this discussion). There are two features of this data: first, everything is left-aligned. Second, many of the cells with numbers also have small green triangles in their top left corners: these are error flags. I’ll discuss each of these in turn.

One of your first steps to validate your data, even if you don’t see such clues as left-alignment or error flags, is to make a quick chart with it. In the chart below, we see that only the first category shows visible bars. This corresponds to the first numeric row which does not have error flags. Since Excel plots text values as zero regardless of any apparent numbers in that text, I suspected that the error flags had to do with numbers stored as text.

In addition to numbers stored as text, a quick diagnostic chart made this way can uncover blank cells, rows, and columns. It can help highlight keying errors; for example, deep in a table, you might not notice a misplaced decimal point in 12345.6789, which is supposed to be 1234.56789, but the chart will show one point wildly out of scale with its neighbors.

Fix Horizontal Alignment

The first thing I usually do is clean up any gratuitous formatting. This usually means inconsistent fonts and font sizes, bold, italic, colors, borders, and the like. You can format your output tables, the ones used in reports and on web sites, however you like. Or however your boss likes. But your working data should be as clean as possible.

One important formatting element is horizontal alignment. Forcing left alignment, or even more commonly, center alignment, can mask characteristics in the data. A better choice is general alignment, because this will align all text to the left, all numbers to the right, and errors in the center. This general alignment by data type is a great visual indicator of possible issues in the cells.

The selected text below is horizontally left-aligned, forced by the left-aligned icon highlighted in the ribbon.

To change to general alignment, simply click once on the highlighted alignment icon. This changes to general alignment, and unhighlights all of these icons.

You can also format the cells, and on the Alignment tab of the dialog, under Horizontal, choose General.

Below you can see the effect of applying general horizontal alignment. The cells which plotted correctly are now right-aligned the way we expect numbers to look. The cells with error flags are still left-aligned, indicating that the numbers are somehow stored as text.

Fix the Errors

Sometimes a person will disable Error Checking because of all those unsightly green flags littering their worksheet. And maybe the flags are ugly, but nothing is as ugly as an undetected error that causes other problems in your model. Hiding error flags to make your spreadsheet pretty is like trying to sweep all those error flags under the industrial carpet of your cubicle. What’s more attractive is a worksheet that has Error Checking enabled and also has no error flags.

First, you should ensure that Error Checking is enabled. Click the Excel File tab, choose Options, then click Formulas. Make sure the “Enable background error checking box” is checked.

When Error Checking is enabled, not only do you get a green flag in the top corner of the cell, but if you select the cell, Excel pops up a little traffic symbol (a yellow diamond icon with an exclamation point) telling you to examine the active cell.

When you mouse over this error icon, it pops up a description of the error. And as I suspected from the start, “The number in this cell is formatted as text”.

When you click on the icon, a menu drops down which provide some options for you. The best option in this particular list is “Convert to Number.”

That Was Easy!

When you click Convert to Number, the cells all lose their green flags and become right-aligned. The chart looks nice, too, without all those text values being plotted as zero.

Posted: Monday, January 29th, 2018 under Data Techniques.
Tags: Chart Data, Data Techniques.
Comments: 7

Open CSV File and Chart CSV Data

Thursday, February 9, 2017 by Jon Peltier 7 Comments

Thursday, February 9, 2017 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2021, All rights reserved.

Often data becomes available to us in formats other than Excel workbooks, such as CSV data files. A CSV (Comma-Separated-Value) file is a simple text file, with each row being a record, and the field values within each record are separated by commas; often the first row contains field names. Excel can open CSV files readily enough, and then you can work with the data like any worksheet data. But if you receive a lot of these files, it might be nice to have a VBA routine that will handle them for you.

A while back I wrote a couple of posts that showed how to plot data from CSV files (see links at end of this article). A reader had some issues implementing those techniques. They were complex approaches, one contained a dialog for the user to select columns to plot and the other was a full-blown add-in. I realized that I didn’t have a good introductory tutorial for opening and processing CSV files, so here it is.

I’ll start with a simple routine that opens a CSV file, saves it as an Excel workbook, then plots all the data in the file, letting Excel use whatever defaults it sees fit. Then I’ll show a few variations, to help you figure out how to work with your own special data.

Here is a zipped Excel workbook that contains all of this code, with buttons on the worksheet to run each routine. The zip file also contains a folder with a number of dummy CSV files.
VBA_Routines_to_Process_CSV_Files.zip

Simple CSV File – Open and Chart All CSV Data

I’ll use this simple CSV data. It has headers in the first row, X values in the first column, and Y values in the next three columns. Of course it doesn’t look like columns, since the values are separated by commas. But when opened in Excel, the data will be placed into columns.

The code has to do these things:

Open the CSV file
Insert an XY Scatter chart
Populate the chart with the applicable data
Save the file with its chart as an Excel workbook

And here’s the simple code:

Sub CSV_OpenAndChart_1()
  ' declare variables
  Dim sCSVFile As String
  Dim sWorkbook As String
  Dim ws As Worksheet
  Dim cht As Chart

  ' hard-coded CSV filename
  sCSVFile = "C:\csv_data\csv_data_1.csv"
  ' workbook name: strip off everything after last "." and add "xlsx" extension
  sWorkbook = Left$(sCSVFile, InStrRev(sCSVFile, ".")) & "xlsx"

  ' open CSV file
  Workbooks.Open sCSVFile
  ' assign variables
  Set ws = ActiveSheet

  ' insert and populate chart
  Set cht = ws.Shapes.AddChart2(240, xlXYScatterLines).Chart
  cht.SetSourceData Source:=ws.Range("$A$1:$D$8"), PlotBy:=xlColumns

  ' move chart to left, under data, and make it smaller
  With cht.Parent
    .Left = 10
    .Top = ws.Range("$A$1:$D$8").Height + 10
    .Width = 275
    .Height = 200
  End With

  ' save as Excel Workbook
  ActiveWorkbook.SaveAs sWorkbook, xlWorkbookDefault

End Sub

After the code runs, here’s what the Excel worksheet looks like. The data up at the top is no longer comma-separated, it’s arranged into rows and columns. And the chart is right below the end of the data.

Improvements

Before you even customize the code to accommodate any special features of your data, there are a couple of tricks that you ought to know about.

FileDialogFilePicker

In our procedure, the file name is hard-coded. I’m sure I don’t have to say why that’s a bad idea, except to say that you’re making a huge assumption that you’ve typed in the only file name that you’re ever going to need. And if the file isn’t there, well…

And you can’t just tell your users to type in a new file name in the code, because you can’t trust that they won’t break the code.

Using the file dialog isn’t much more difficult than hard-coding the file name. You need a few more variables and a few more lines of code:

  ' declare variables
  Dim fd As FileDialog
  Dim fdSelected As FileDialogSelectedItems
  Dim sInitial As String
  Dim sCurDir As String
  Dim sCSVFile As String

  ' initial folder to open in dialog
  ' if file name included, it is preselected
  sInitial = "C:\csv_data\csv_data_1.csv"
  sCurDir = CurDir
  
  ' create the dialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    ' apply dialog settings
    .AllowMultiSelect = False
    .InitialFileName = sInitial
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Title = "Select CSV File"

    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure

    ' get the selected file
    Set fdSelected = .SelectedItems
    sCSVFile = fdSelected(1)
  End With

  ' clean up
  Set fdSelected = Nothing
  Set fd = Nothing

  Exit_Procedure:
  ChDrive sCurDir
  ChDir sCurDir

UsedRange

In the code, we can assign the value of a range variable as follows:

  Dim rUsed As Range
  Set rUsed = ws.UsedRange

The used range of a worksheet is the rectangular region that encloses all of the data. In our example, it’s A1:E8. Using UsedRange means I don’t need to know the address of the data range, and I don’t need to count how many rows or columns are in the data, I can just pass the used range to the chart.

Suppose my CSV data had more columns. My code using UsedRange doesn’t care, since it uses the whole used range of data in the file. The following starts with a CSV file with six columns instead of four, and all of the Y value columns are plotted.

AddChart2

In the procedure above I used the following to insert the chart:

  Set cht = ws.Shapes.AddChart2(240, xlXYScatterLines).Chart

How did I know to use AddChart2? I can never remember the exact syntax, so I opened the CSV file and recorded a macro while I inserted the chart, and this is what the Macro Recorder recorded. That’s fine, AddChart2 gives us the nicely formatted charts which were introduced in Excel 2013. The 240 inside the brackets tell Excel to apply style 240, and of course xlXYScatterlines is the chart type. But Excel 2007 and 2010 don’t understand AddChart2: you’ll get a compile error, and Excel won’t even run any of the procedure. For these earlier versions of Excel, we need to use this code:

  Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart

No style number, and you end up with the uglier default formatting, Even in Excel 2013 or 2016. For reference, the output of Excel 2007 and 2010’s AddChart is shown as the top chart below, with the output of Excel 2013 and 2016’s AddChart2 as the bottom chart below.

So how do you write code that does not crash in Excel 2007 and 2010, but still provides the nice chart styles of Excel 2013 and 2016? Excel 2013 and 2016 still can use AddChart, even though the preferred syntax is AddChart2, but you’ll get the uglier old formats. You can’t apply style 240 in the earlier releases, because it will not be recognized. So you need to test for Excel version, and apply the style in later versions.

  Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart
  If Val(Application.Version) > 14 Then
    cht.ChartStyle = 240
  End If

Application.Version returns a text string, for example, “12.0” for Excel 2007 or “16.0” for Excel 2016. We need to convert the string to a numerical value; this happens automatically if your system uses a period as a decimal separator, but it misbehaves if it expects a comma. Val(Application.Version) returns the numerical value, that is, 12 or 16.

Simple CSV File – Open and Chart All CSV Data – Enhanced Code

Here’s our simple procedure from before, incorporating the enhancedments described above:

Sub CSV_OpenAndChart_1A_FileDialog_AddChart()
  ' variables for File Dialog
  Dim fd As FileDialog
  Dim fdSelected As FileDialogSelectedItems
  Dim sInitial As String
  Dim sCurDir As String

  ' declare variables
  Dim sCSVFile As String
  Dim sWorkbook As String
  Dim ws As Worksheet
  Dim rUsed As Range
  Dim cht As Chart

  ' initial folder to open in dialog
  ' if file name included, it is preselected
  sInitial = "C:\csv_data\csv_data_1.csv"
  sCurDir = CurDir
  
  ' create the dialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    ' apply dialog settings
    .AllowMultiSelect = False
    .InitialFileName = sInitial
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Title = "Select CSV File"

    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure

    ' get the selected file
    Set fdSelected = .SelectedItems
  End With

  sCSVFile = fdSelected(1)
  sWorkbook = Left$(sCSVFile, InStrRev(sCSVFile, ".")) & "xlsx"

  ' open CSV file
  Workbooks.Open sCSVFile
  ' assign variables
  Set ws = ActiveSheet
  Set rUsed = ws.UsedRange

  ' syntax for 2007-2010-2013-2016
  Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart
  If Val(Application.Version) > 14 Then
    cht.ChartStyle = 240
  End If
  cht.SetSourceData Source:=rUsed, PlotBy:=xlColumns

  ' move chart to left, under data, and make it smaller
  With cht.Parent
    .Left = 10
    .Top = rUsed.Height + 10
    .Width = 275
    .Height = 200
  End With

  ' save as Excel Workbook
  ActiveWorkbook.SaveAs sWorkbook, xlWorkbookDefault

Exit_Procedure:
  ChDrive sCurDir
  ChDir sCurDir

End Sub

CSV Data Structure: Skip First Column

Input data is not always (not usually, in fact) laid out for a simple input and chart routine. What if the first column of the CSV file is some kind of index or description. If we try to plot it, we will get a messy chart. So we want to skip the first column; using similar logic you can skip the first three columns, or the third column, or whatever data you don’t want in your chart.

Here’s a CSV file like the one used above, but the first column is an index.

We’ll build on the previous routine, with its file dialog and AddChart/ChartStyle approach, and we’ll modify the chart data range.

Sub CSV_OpenAndChart_2A_SkipFirstColumn()
  ' declare variables
  Dim fd As FileDialog
  Dim fdSelected As FileDialogSelectedItems
  Dim sInitial As String
  Dim sCurDir As String
  Dim sCSVFile As String
  Dim sWorkbook As String
  Dim ws As Worksheet
  Dim rUsed As Range
  Dim cht As Chart

  ' variables to help modify chart data range
  Dim nRows As Long, nCols As Long
  Dim rChart As Range

  ' initial folder to open in dialog
  ' if file name included, it is preselected
  sInitial = "C:\csv_data\csv_data_2.csv"
  sCurDir = CurDir
  
  ' create the dialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    ' apply dialog settings
    .AllowMultiSelect = False
    .InitialFileName = sInitial
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Title = "Select CSV File"

    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure

    ' get the selected file
    Set fdSelected = .SelectedItems
  End With

  sCSVFile = fdSelected(1)
  sWorkbook = Left$(sCSVFile, InStrRev(sCSVFile, ".")) & "xlsx"

  ' open CSV file
  Workbooks.Open sCSVFile
  ' assign variables
  Set ws = ActiveSheet
  Set rUsed = ws.UsedRange

  ' modified data range
  nRows = rUsed.Rows.Count
  nCols = rUsed.Columns.Count
  Set rChart = rUsed.Offset(, 1).Resize(, nCols - 1)

  ' syntax for 2007-2010-2013-2016
  Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart
  If Val(Application.Version) > 14 Then
    cht.ChartStyle = 240
  End If
  cht.SetSourceData Source:=rChart, PlotBy:=xlColumns

  ' move chart to left, under data, and make it smaller
  With cht.Parent
    .Left = 10
    .Top = rUsed.Height + 10
    .Width = 275
    .Height = 200
  End With

  ' save as Excel Workbook
  ActiveWorkbook.SaveAs sWorkbook, xlWorkbookDefault

Exit_Procedure:
  ChDrive sCurDir
  ChDir sCurDir

End Sub

After the code is run, the new workbook looks like the following. The chart is selected, and you can see that the first column of data is not highlighted as part of the chart’s data range.

CSV Data Structure: Combine Date and Time Columns into Chart-Friendly Date-Time

The previous data treatment was pretty easy. What if, instead of a nice column of X values, we have a column of dates and another column of times.

To review quickly, Excel treats a date as a whole number (the number of days since 1-Jan-1900, give or take an erroneous leap day, but that’s another story, and it’s not even Microsoft’s fault), and it treats a time as a fraction (the fraction of 24 hours that has elapsed since the previous midnight). You can’t plot by separate date and time values, but if you add them together you get a date-time value that can be treated as a numerical value on the X axis of a scatter chart.

So we need to insert a new column, and add the dates and times to get chart-ready date-time values. Here is a sample, shown for simpliciy in a worksheet rather than in a text file reader. Like the previous data set, the first column is an index which will be ignored by the chart. Columns B and C are the dates and times which must be combined in a new column inserted after column C.

Sub CSV_OpenAndChart_3A_CombineDateTime()
  ' declare variables
  Dim fd As FileDialog
  Dim fdSelected As FileDialogSelectedItems
  Dim sInitial As String
  Dim sCurDir As String
  Dim sCSVFile As String
  Dim sWorkbook As String
  Dim ws As Worksheet
  Dim rUsed As Range
  Dim cht As Chart

  ' variables to help modify chart data range
  Dim nRows As Long, nCols As Long
  Dim rChart As Range

  ' initial folder to open in dialog
  ' if file name included, it is preselected
  sInitial = "C:\csv_data\csv_data_3.csv"
  sCurDir = CurDir
  
  ' create the dialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    ' apply dialog settings
    .AllowMultiSelect = False
    .InitialFileName = sInitial
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Title = "Select CSV File"

    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure

    ' get the selected file
    Set fdSelected = .SelectedItems
  End With

  sCSVFile = fdSelected(1)
  sWorkbook = Left$(sCSVFile, InStrRev(sCSVFile, ".")) & "xlsx"

  ' open CSV file
  Workbooks.Open sCSVFile
  ' assign variables
  Set ws = ActiveSheet
  Set rUsed = ws.UsedRange

  ' insert column to combine date (column 2) with time (column 3)
  ws.Columns(4).Insert xlToRight
  Set rUsed = ws.UsedRange
  nRows = rUsed.Rows.Count
  nCols = rUsed.Columns.Count
  With rUsed.Columns(4).Offset(1).Resize(nRows - 1)
    .Formula = "=B2+C2"
    .NumberFormat = "m/d/yyyy h:mm"
  End With
  Set rChart = rUsed.Offset(, 3).Resize(, nCols - 3)

    ' syntax for 2007-2010-2013-2016
  Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart
  If Val(Application.Version) > 14 Then
    cht.ChartStyle = 240
  End If
  cht.SetSourceData Source:=rChart, PlotBy:=xlColumns

    ' format axis tick labels
  cht.Axes(xlCategory).TickLabels.NumberFormat = "m/d"

  ' move chart to left, under data, and make it smaller
  With cht.Parent
    .Left = 10
    .Top = rUsed.Height + 10
    .Width = 275
    .Height = 200
  End With

  ' save as Excel Workbook
  ActiveWorkbook.SaveAs sWorkbook, xlWorkbookDefault

Exit_Procedure:
  ChDrive sCurDir
  ChDir sCurDir

End Sub

Multiple CSV Files, Processed Separately

If you get a dozen CSV files all at once, you could run the code a dozen times, selecting each file in turn. But wouldn’t it be better to select all the files in one trip to the file dialog, then let the code run all of the files?

You may have noticed the AllowMultiSelect property of the file dialog, and that we set its value to False. We can set this property to True, and the dialog will allow us to select multiple files using the Shift or Ctrl key as we’re used to. Each selected file will be a member of the file dialog’s SelectedItems. We can wrap the file-processing code in a For-Next loop to process each file in turn.

Note that if you select one file in the dialog, this routine will process that one file just fine.

Sub CSV_OpenAndChart_4A_MultipleFiles()
  ' declare variables
  Dim fd As FileDialog
  Dim fdSelected As FileDialogSelectedItems
  Dim sInitial As String
  Dim sCurDir As String
  Dim sCSVFile As String
  Dim sWorkbook As String
  Dim ws As Worksheet
  Dim rUsed As Range, rChart As Range
  Dim cht As Chart
  Dim nRows As Long, nCols As Long

  ' file counters
  Dim iFile As Long, nFiles As Long

  ' initial folder to open in dialog
  ' if file name included, it is preselected
  sInitial = "C:\csv_data\"
  sCurDir = CurDir
  
  ' create the dialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    ' apply dialog settings
    .AllowMultiSelect = True
    .InitialFileName = sInitial
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Title = "Select CSV File"

    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure

    ' get the selected file
    Set fdSelected = .SelectedItems
    nFiles = fdSelected.Count
  End With

  ' do all the files
  For iFile = 1 To nFiles
    sCSVFile = fdSelected(iFile)
    sWorkbook = Left$(sCSVFile, InStrRev(sCSVFile, ".")) & "xlsx"

    ' open CSV file
    Workbooks.Open sCSVFile
    ' assign variables
    Set ws = ActiveSheet
    Set rUsed = ws.UsedRange

    ' insert column to combine date (column 2) with time (column 3)
    ws.Columns(4).Insert xlToRight
    Set rUsed = ws.UsedRange
    nRows = rUsed.Rows.Count
    nCols = rUsed.Columns.Count
    With rUsed.Columns(4).Offset(1).Resize(nRows - 1)
      .Formula = "=B2+C2"
      .NumberFormat = "m/d/yyyy h:mm"
    End With
    Set rChart = rUsed.Offset(, 3).Resize(, nCols - 3)

    ' syntax for 2007-2010-2013-2016
    Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart
    If Val(Application.Version) > 14 Then
      cht.ChartStyle = 240
    End If
    cht.SetSourceData Source:=rChart, PlotBy:=xlColumns

    ' format axis tick labels
    cht.Axes(xlCategory).TickLabels.NumberFormat = "m/d"

    ' move chart to left, under data, and make it smaller
    With cht.Parent
      .Left = 10
      .Top = rUsed.Height + 10
      .Width = 275
      .Height = 200
    End With

    ' save as Excel Workbook
    ActiveWorkbook.SaveAs sWorkbook, xlWorkbookDefault
    ActiveWorkbook.Close
  Next
Exit_Procedure:
  ChDrive sCurDir
  ChDir sCurDir

End Sub

Multiple CSV Files, Combined Into One Output Workbook

The final variation I’ll talk about here is processing multiple CSV files (and it works with just one CSV file), and placing each output worksheet into one output workbook; the worksheet tabs will reflect the names of the input CSV files. We’ll use an additional file dialog, the msoFileDialogSaveAs dialog, to ask the user for a filename for this output workbook.

Sub CSV_OpenAndChart_5A_CombineMultipleFiles()
  ' declare variables
  Dim fd As FileDialog
  Dim fdSelected As FileDialogSelectedItems
  Dim sInitial As String
  Dim sCurDir As String
  Dim sCSVFile As String
  Dim sWorkbook As String
  Dim wbOutput As Workbook
  Dim ws As Worksheet
  Dim rUsed As Range, rChart As Range
  Dim cht As Chart
  Dim nRows As Long, nCols As Long
  Dim iFile As Long, nFiles As Long

  ' initial folder to open in dialog
  ' if file name included, it is preselected
  sInitial = "C:\csv_data\"
  sCurDir = CurDir
  
  ' create the file dialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    ' apply dialog settings
    .AllowMultiSelect = True
    .InitialFileName = sInitial
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Title = "Select CSV File"

    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure

    ' get the selected file
    Set fdSelected = .SelectedItems
    nFiles = fdSelected.Count
  End With

  ' open output workbook
  Set wbOutput = Workbooks.Add

  ' do all the files
  For iFile = 1 To nFiles
    sCSVFile = fdSelected(iFile)
    sWorkbook = Left$(sCSVFile, InStrRev(sCSVFile, ".")) & "xlsx"

    ' open CSV file
    Workbooks.Open sCSVFile
    ' assign variables
    Set ws = ActiveSheet
    Set rUsed = ws.UsedRange

    ' insert column to combine date (column 2) with time (column 3)
    ws.Columns(4).Insert xlToRight
    Set rUsed = ws.UsedRange
    nRows = rUsed.Rows.Count
    nCols = rUsed.Columns.Count
    With rUsed.Columns(4).Offset(1).Resize(nRows - 1)
      .Formula = "=B2+C2"
      .NumberFormat = "m/d/yyyy h:mm"
    End With
    Set rChart = rUsed.Offset(, 3).Resize(, nCols - 3)

    ' syntax for 2007-2010-2013-2016
    Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart
    If Val(Application.Version) > 14 Then
      cht.ChartStyle = 240
    End If
    cht.SetSourceData Source:=rChart, PlotBy:=xlColumns

    ' format axis tick labels
    cht.Axes(xlCategory).TickLabels.NumberFormat = "m/d"

    ' move chart to left, under data, and make it smaller
    With cht.Parent
      .Left = 10
      .Top = rUsed.Height + 10
      .Width = 275
      .Height = 200
    End With

    ' put into output workbook
    ws.Move After:=wbOutput.Worksheets(wbOutput.Worksheets.Count)
  Next

  ' delete blank first worksheet
  ' display alerts -> false to hide warning
  Application.DisplayAlerts = False
  wbOutput.Worksheets(1).Delete
  Application.DisplayAlerts = True

  ' create new file dialog
  Set fd = Application.FileDialog(msoFileDialogSaveAs)
  With fd
    ' apply dialog settings
    .InitialFileName = sInitial
    .Title = "Enter Output Workbook Name"
    
    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure
    
    ' get the selected filename
    Set fdSelected = .SelectedItems
  End With

  sWorkbook = fdSelected(1)
  wbOutput.SaveAs sWorkbook, xlWorkbookDefault

Exit_Procedure:
  ChDrive sCurDir
  ChDir sCurDir

End Sub

Other Examples Using CSV Files on Peltier Tech Blog

VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User
This tutorial shows development of a VBA program to open a CSV file, save it as an Excel workbook, and let the user select which data to plot.

VBA: An Advanced Add-in to Open a CSV File and Create Chart with Data Specified by User
This tutorial shows development of an advanced VBA add-in to open a CSV file, save it as an Excel workbook, and let the user select which data to plot.

Posted: Thursday, February 9th, 2017 under VBA.
Tags: Chart Data, CSV Files, Data Techniques, VBA.
Comments: 7

Copy Chart to New Sheet and Link to Data on New Sheet

Wednesday, July 6, 2016 by Jon Peltier 13 Comments

This is a quick post to answer this rather common question from the forums:

I have a worksheet that has data in a particular arrangement and a chart that plots this data. I have another worksheet with the identical data arrangement. How can I copy the chart to the new worksheet and have the chart link to the data on the new worksheet?

Here is my simulation of the problem. Worksheet “Original Data” has a particular data arrangement with a “nice” chart that plots this data.

If I click on a series in the chart and peek at the formula bar, I can see that it plots data from “Original Sheet”. Here are the two series formulas:

=SERIES('Original Data'!$C$2,'Original Data'!$B$3:$B$8,'Original Data'!$C$3:$C$8,1)
=SERIES('Original Data'!$D$2,'Original Data'!$B$3:$B$8,'Original Data'!$D$3:$D$8,2)

Great, just what I expect.

I went to a lot of trouble drawing this chart exactly to the boss’ specifications, and I don’t want to spend another hour and a half duplicating the chart for the data on the worksheet named “New Data”. So I copy the chart, and paste it on “New Data”. But that looks like it plots the numbers from “Original Data”.

I can verify this behavior by checking the series formulas in the chart:

=SERIES('Original Data'!$C$2,'Original Data'!$B$3:$B$8,'Original Data'!$C$3:$C$8,1)
=SERIES('Original Data'!$D$2,'Original Data'!$B$3:$B$8,'Original Data'!$D$3:$D$8,2)

Most people don’t expect this behavior (though it does make sense, given the history and nature of charts).

In this simple case it wouldn’t take too long to actually edit the series formulas to make the new chart match the new data. But in real life, the chart contains 37 series, and you need to put the chart onto 47 different worksheets. (It’s not too hard to write some VBA to help you edit series formulas, but that’s not today’s lesson.)

So I’m going to show you an easier way to make sure the chart links to the data on the new sheet.

The original chart links to the data on the original sheet, right? If we make a copy of that original sheet, call it “Original Data (2)”, we can verify that the chart on this copied sheet links to the data on the copied sheet. See, series formulas:

=SERIES('Original Data (2)'!$C$2,'Original Data (2)'!$B$3:$B$8,'Original Data (2)'!$C$3:$C$8,1)
=SERIES('Original Data (2)'!$D$2,'Original Data (2)'!$B$3:$B$8,'Original Data (2)'!$D$3:$D$8,2)

I didn’t even have to touch the chart. Now, copy the new data from “New Data”, and paste it on top of the data in “Original Data (2)”. The data arrangements are identical on the two worksheets, so we should be fine. I usually use Paste Special – Values, but it doesn’t seem to matter.

Look, the chart now is showing the data we just pasted on top of the copied data:

All we need to do is remove the current “New Data” sheet, and rename this copied sheet to “New Data”.

It’s a small amount of work, but isn’t copying and pasting data a lot easier than futzing with a chart’s series formulas and source data?

Posted: Wednesday, July 6th, 2016 under Data Techniques.
Tags: Chart Data.
Comments: 13

VBA: Assign Names to Chart Series

Tuesday, July 7, 2015 by Jon Peltier Leave a Comment

If you select a well-defined worksheet range and insert a chart, Excel parses the range and assigns values (Y values), categories (X values), and series names based on its analysis of the range.

For example, if you select the range C2:F8 shown below, Excel notices that the top left cell C2 is blank, so Row 2 and Column C will be treated differently. Excel also notices that there are more rows than columns, so the data will be plotted by column, with categories in the first column and series names in the first row. When you insert a chart, the worksheet is highlighted showing that Column C (purple highlights) is used as category labels (X values), Row 2 (red highlights) is used as series names, and the rest of the area (blue highlights) is used as Y values.

If you always arrange your data nicely like this, you will rarely have to fix up what Excel uses for X values, Y values, and series names.

Assign Names from Cells in Front of Y Values

But sometimes your chart hasn’t been no nicely assigned to the worksheet range. The chart below has series plotted by row, but the first column has not been used for series names. Instead the series have the dreaded “Series1”, “Series2”, etc., as their names.

Similarly, this chart has series plotted by column, but the first row has not been used for series names. Again, the series are labeled with the dreaded “Series1”, “Series2”, etc.

You can manually name the series, using the Select Data command from the ribbon or from the right click menu, or editing the series formula. But it’s not too much trouble to write a little code to find the appropriate cells to name the series in a chart.

I’ll start with a routing that works on one chart series. It gets the series formula, parses out the argument that specifies the series Y values, and finds the range containing those values. (If the Y values are not from a range, for example, they are hard-coded as an array right in the formula, the code skips the series.) If the Y values range is a row, the code finds the cell to the left of this range in the same row; if the Y values range is a column, the code finds the cell above this range in the same column. This cell is used to create a link for the series name.

Sub Series_AssignNameToCellBeforeYValues(srs As Series)
  Dim sFmla As String
  Dim vFmla As Variant
  Dim sYVals As String
  Dim rYVals As Range
  Dim rName As Range
  
  sFmla = srs.Formula
    ' e.g. =SERIES("Name",Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1)
  sFmla = Replace(sFmla, "=SERIES(", "")
  sFmla = Left$(sFmla, Len(sFmla) - 1)
    ' e.g. "Name",Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1
  vFmla = Split(sFmla, ",")
  
  If UBound(vFmla) + 1 - LBound(vFmla) = 4 Then
    sYVals = vFmla(LBound(vFmla) + 2)
      ' third element (first + 2)
      ' e.g. Sheet1!$B$2:$B$10
    On Error Resume Next
    Set rYVals = Range(sYVals)
    On Error GoTo 0
    If Not rYVals Is Nothing Then
      If rYVals.Cells.Count > 1 Then
        On Error Resume Next
        If rYVals.Columns.Count > rYVals.Rows.Count Then
          ' by row, take cell to left
          Set rName = rYVals.Resize(1, 1).Offset(, -1)
        Else
          ' by col, take cell above
          Set rName = rYVals.Resize(1, 1).Offset(-1)
        End If
        On Error GoTo 0
        If Not rName Is Nothing Then
          srs.Name = "=" & rName.Address(, , , True)
            ' e.g. "=Sheet1!$B$1"
            ' use formula notation so it links to cell
        End If
      End If
    End If
  End If
End Sub

That’s great for one series. Use the following code to call the above procedure for each series in a given chart:

Sub Chart_AssignNameToCellBeforeYValues(cht As Chart)
  Dim srs As Series
  For Each srs In ActiveChart.SeriesCollection
    Series_AssignNameToCellBeforeYValues srs
  Next
End Sub

Use this to assign names for the active chart:

Sub ActiveChart_AssignNameToCellBeforeYValues()
  If Not ActiveChart Is Nothing Then
    Chart_AssignNameToCellBeforeYValues ActiveChart
  End If
End Sub

Use this to assign names to every chart in the active sheet:

Sub AllCharts_AssignNameToCellBeforeYValues()
  Dim chtob As ChartObject
  For Each chtob In ActiveSheet.ChartObjects
    Chart_AssignNameToCellBeforeYValues chtob.Chart
  Next
End Sub

Here is our first chart, plotted by row, with its series names now defined: note the red highlighted range containing the same labels now displayed in the legend.

Here is the second chart, plotted by column, with names assigned.

In fact, the alignment of all series need not be the same, since the routine works series-by-series. The top chart below uses the first two rows of data from the first chart above and the last two columns from the second chart, with no series names assigned. The bottom chart shows how the code has correctly assigned names.

Assign Names from an Arbitrary Range

What if the series names are in a range that isn’t connected to the rest of the chart data? Here I want to use series names from the range below the chart. The highlights in the worksheet only show X and Y values for the selected series.

Here is a routine that assigns names from a given range to series in a given chart:

Sub Chart_AssignNamesFromRange(cht As Chart, rng As Range)
  Dim iSrs As Long
  For iSrs = 1 To cht.SeriesCollection.Count
    If iSrs > rng.Cells.Count Then Exit For
    cht.SeriesCollection(iSrs).Name = _
        "=" & rng.Cells(iSrs).Address(, , , True)
  Next
End Sub

This routine asks the user to select a range, then calls the routine above to assign names from this range to the active chart:

Sub ActiveChart_AssignNamesFromRange()
  Dim myRange As Range
  On Error Resume Next
  Set myRange = Application.InputBox( _
      "Select a range containing series names for the active chart.", _
      "Select Range", , , , , , 8)
    ' Type 8 to input a range
  If Not myRange Is Nothing Then
    Chart_AssignNamesFromRange ActiveChart, myRange
  End If
End Sub

Here is the dialog, which I’ve already used to select a range containing labels.