Chart Plotting #N/A Values (X-Axis)

Doogals · Aug 10, 2011

Very limited Excel user having a difficult time plotting values on a line chart...

I have a column of data being plotted on a line chart (cells H8:H52). Out of these cells only 5 of them currently contain data, the other 40 return #N/A so that they are not plotted on the chart. (Eventually these cells will contain data as it is added.)

The problem is even though the #N/A values are not plotted on the chart, the X-Axis still uses them for the labels. So even though I only have 5 entries, my x-axis goes all the way up to 45.

How can I set the x-axis to automatically adjust its length based on the number of data entries, and not include the #N/A values?

Appreciate any help, this problem is driving me up the wall! Thanks.

GlennUK · Aug 11, 2011

Use a dynamic range to calculate your chart data:

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

Doogals · Aug 15, 2011

Still having problems getting it to work, will post a copy of the file, but not sure how many posts I need in order to upload attachments?

Doogals · Aug 15, 2011

Looks like I can't upload attachments on this forum? Really?

xenou · Aug 15, 2011

This forum does not support attachments. Alternatives are listed in this sticky:
http://www.mrexcel.com/forum/showthread.php?t=508133

For what it's worth, a dynamic range name is a good solution to this kind of problem (I've used this approach myself). Putting the problem in words may be halfway towards solving it. Another link on dynamic ranges is here (although the one provided by GlennUK to Peltier's site is excellent):
http://www.contextures.com/xlnames01.html#Dynamic

xenou

Doogals · Aug 17, 2011

Wondering if part of my problem might be having the data stored on separate worksheet tabs? I have three of them: Summary, Vehicle 1, and Vehicle 2.

What I would like is a line chart on the first worksheet (Summary) that plots the fuel economy data from each vehicle in one chart (each vehicle's fuel consumption data is stored on its respective worksheet where data is collected).

Could this be part of my problem?

Steve=True · Aug 17, 2011

That is not the problem. Even if you try and make a chart and not show a point in a column chart, you will still see the xaxis for that label.

You still need to make your chart more dynamic by making a new data area that you will graph.

Check out this post where Aladin shows you how to make a dynamic list.

Then setup dynamic formulas that look at that list area to make the chart.

http://www.mrexcel.com/forum/showthread.php?t=572210

Steve=True
www.exceldashboardtemplates.com

xenou · Aug 17, 2011

One trick is to prepare the chart data so it works better for your requirements. As an example, in this chart my original data has labels and data - the labels are all there but some of the data is #N/A. With a simple formula, I can set up a parallel range that has the same information except the labels are also #N/A when the data is #N/A:

<img alt="chart" src="http://northernocean.net/etc/mrexcel/20110817_chart2.png" />

Formula in D4 is:
=IF(C4,B4,NA())

Formula in E4 is (obviously):
=C4

You somewhat lost me going from your first post with data in H8:H52 to now having data on three tabs. I'm not sure I've tried combining data from different tabs but I guess you probably could source one series from a range on one tab and another from a range on another tab, as long as they work with the same axis. Another option would be to collate the data on one tab first - as above, sometimes just preparing the data you want to chart makes it very easy.

Dashnick · Aug 2, 2016

xenou said:
One trick is to prepare the chart data so it works better for your requirements. As an example, in this chart my original data has labels and data - the labels are all there but some of the data is #N/A. With a simple formula, I can set up a parallel range that has the same information except the labels are also #N/A when the data is #N/A:

Formula in D4 is:
=IF(C4,B4,NA())

Formula in E4 is (obviously):
=C4

You somewhat lost me going from your first post with data in H8:H52 to now having data on three tabs. I'm not sure I've tried combining data from different tabs but I guess you probably could source one series from a range on one tab and another from a range on another tab, as long as they work with the same axis. Another option would be to collate the data on one tab first - as above, sometimes just preparing the data you want to chart makes it very easy.

Does this still work in Excel 2016?

Chart Plotting #N/A Values (X-Axis)

Doogals

New Member

Excel Facts

GlennUK

Well-known Member

Doogals

New Member

Doogals

New Member

xenou

MrExcel MVP

Doogals

New Member

Steve=True

Well-known Member

xenou

MrExcel MVP

Dashnick

New Member

Similar threads

Forum statistics

Share this page

Chart Plotting #N/A Values (X-Axis)

New Member

Excel Facts

Well-known Member

New Member

New Member

MrExcel MVP

New Member

Well-known Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock