Line chart that excludes entries that are "0"

R

rwilliams09

New Member
Joined
Jun 18, 2017
Messages
48
I am building a template that has the revenue forecast over time and multiple different scenarios will be run through this ONE single template. There are some scenarios where revenue cuts off at 2022 for example and some where it cuts off at 2052. Thus, I have to have the same chart, but since clients will be seeing this chart and altering scenarios, it looks much prettier to only have the line chart show the last year with revenue.

Is there any way to do this? Thanks in advance and Happy Thanksgiving!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Re: How to make line chart that excludes entries that are "0"

Thanks! Is there any way to do this without changing the name manager?
 
Upvote 0
Re: How to make line chart that excludes entries that are "0"

Hi,
What I did was modify my formula and worked well for me.
Example
=IF(A1=0,NA(),"your formula")
That did not display the 0 in the graphs.
Hope it works for you.
 
Upvote 0
Re: How to make line chart that excludes entries that are "0"

It removes the 0s from the graph, but I am still stuck with the x-axis (the years) which I would love to not show up at all. The first answer with the name ranges works, but I would like to do it without that or without macros.
 
Upvote 0
Re: How to make line chart that excludes entries that are "0"

You can do this without named formulas and without VBA. You have to have a range of cells for the chart data that is distinct, or separate, from the original data. I've posted an example worksheet you can download at: https://www.dropbox.com/s/7vkyubj997ol3la/dynamic_charts_by_worksheet_formulas.xlsx?dl=0

I used three scenarios:


<tbody>
[TD="class: xl67"]Alpha scenario[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]Beta scenario[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]Gamma scenario[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl64"][/TD]
[TD="class: xl65"]Alpha[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"]Beta[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"]Gamma[/TD]

[TD="class: xl66, align: right"]2017-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2017-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2017-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]

[TD="class: xl66, align: right"]2018-06-30[/TD]
[TD="class: xl67, align: right"]75[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2018-06-30[/TD]
[TD="class: xl67, align: right"]125[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2018-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]

[TD="class: xl66, align: right"]2019-06-30[/TD]
[TD="class: xl67, align: right"]50[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2019-06-30[/TD]
[TD="class: xl67, align: right"]150[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2019-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]

[TD="class: xl66, align: right"]2020-06-30[/TD]
[TD="class: xl67, align: right"]25[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2020-06-30[/TD]
[TD="class: xl67, align: right"]175[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2020-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]

[TD="class: xl66, align: right"]2021-06-30[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2021-06-30[/TD]
[TD="class: xl67, align: right"]200[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2021-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]

[TD="class: xl66, align: right"]2022-06-30[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2022-06-30[/TD]
[TD="class: xl67, align: right"]225[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2022-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]

[TD="class: xl66, align: right"]2023-06-30[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2023-06-30[/TD]
[TD="class: xl67, align: right"]250[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2023-06-30[/TD]
[TD="class: xl67, align: right"]0[/TD]

[TD="class: xl66, align: right"]2024-06-30[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2024-06-30[/TD]
[TD="class: xl67, align: right"]275[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2024-06-30[/TD]
[TD="class: xl67, align: right"]0[/TD]

</tbody>

You can select the scenario to graph using a data validation dropdown, cell C5 in my example. The data for the chosen scenario appears in cells B8:C16.

I used a helper cell, M5, to count the non-zero cells in C9:C16, the y-values for the line chart.
=COUNTIF($C$9:$C$16, "<>0")

The first chart uses two multi-cell INDEX+MATCH array formulas to calculate the data to chart. INDEX+MATCH returns #N/A when it fails, and these values won't be plotted on the x-axis. Cells L8:M16 –

[TABLE="class: grid, width: 156"]
<tbody>[TR]
[TD][/TD]
[TD]Alpha[/TD]
[/TR]
[TR]
[TD="align: right"]2017-06-30[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2018-06-30[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD="align: right"]2019-06-30[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2020-06-30[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

Single multi-cell array formula (Ctrl+Shift+Enter) entered in L9:L16 –
=$B$9:INDEX($B$9:$B$16, $M$5)

Single multi-cell array formula (Ctrl+Shift+Enter) entered in M9:M16 –
=$C$9:INDEX($C$9:$C$16, $M$5)


An alternative is to use IF formulas to determine the points to chart. I show this for the second chart; the data is in cells L25:M32. The helper cell, M5, is used again.

In L25 and copied downward:
=IF(ROW()-ROW($L$24)<=$M$5, B9, NA())

In M25 and copied downward:
=IF(ROW()-ROW($M$24)<=$M$5, C9, NA())


IF formulas and array formulas can cause slow worksheet calculation when used with large data sets. This may or may not be noticeable with your data. The helper cell was used, rather than incorporating the non-zero count formula into the plotted data formulas, to reduce the total number of calculations Excel has to perform.

I used linked cells to change the chart titles as the chosen data set changes.
 
Last edited:
Upvote 0
Re: How to make line chart that excludes entries that are "0"

I am trying this, but for some reason my X-axis is still showing the #N/A and i cant figure out why it is not just excluding it entirely.
 
Upvote 0
Re: How to make line chart that excludes entries that are "0"

Okay i actually have it working now, but the only thing it does NOT do is re-adjust to fill out the entire chart. There is just an empty area where the years and data points no longer exist.
 
Upvote 0
Re: How to make line chart that excludes entries that are "0"

Are you using year numbers only, with no months or days?

The x-axis values should be actual Excel dates formatted to show as years only. Excel treats a line chart with dates on the horizontal axis as a special case of xy-chart.

To leave your original data unchanged (years only), you could change the INDEX+MATCH formula for the charted data years to a date, December 31 in these examples:

=DATE($B$9:INDEX($B$9:$B$16, $M$5), 12, 31)

The similar IF formula would be:

=DATE(IF(ROW()-ROW($L$24)<=$M$5, B9, NA()), 12, 31)

Then use a custom format for the horizontal axis. From the chart format pane:
Format Axis > Axis Options > Axis Options(the column chart icon) > Number.
Select "Custom" in the Category dropdown. In the Format Code box, type "yyyy" then click on the Add button.

5x9UjdG.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
1K
Alex Blakenburg
A
K
  • Solved
VBA Macro 100+ Line Chart
Replies
3
Views
860
Domenic
D
E
  • Question Question
Pivot chart joining multiple series
Replies
0
Views
1K
Exceloway
E
C
  • Question Question
Automate a data table into a Gannt Chart
Replies
1
Views
490
Cantrecallmyusername
C
WildBurrow
  • Solved
Export/Save selected charts(pictures) as image, if file exists add unique number
Replies
1
Views
2K
WildBurrow
WildBurrow

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top