Line on Combo Graph in Pivot Chart

Alex D

New Member
Joined
Apr 28, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
My Pivot Chart has 5 sets of data....I have graphed 3 of them as bars and 2 of them as lines....I have slicers for "months" so when I have more than one month selected, I see all 5 sets of data...when I select only one month on my slicers, the 2 lines go away....I know why, but, is there any way other than drawing a line, to have my lines appear even if only one month is selected? I have attached a word doc to help you see what my issue is...any help is greatly appreciated.
 

Attachments

  • sample.JPG
    sample.JPG
    70.8 KB · Views: 6

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,025
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Alex D, How do you suppose to make a line out of a single data point? Perhaps too obvious, but would it be satisfactory to have a marker? That way the data point shows up even if you select only 1 month.
 

Alex D

New Member
Joined
Apr 28, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
I know why it's happening, just trying to see if anyone knows anything that can be done to fix----I can't figure it out---I have seen crazier things get fixed in EXCEL so I thought I'd ask :)
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,025
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have seen crazier things get fixed in EXCEL
That's very true, but magic it ain't. ;)
The question was genuine. What would that single data point line be like and what purpose does it serve? Why is a marker not okay?
 

Alex D

New Member
Joined
Apr 28, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Have never used markers...at least not yet....do tell, please
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,025
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That would be like this
1588083784714.png


Format markers:

1588084208348.png


FYI: Without using a pivot chart, but with slicer on month, you can make the solution like this
1588083725292.png


It all depends on what you actually need. In short NA() does not plot data on the chart. So checking if the month is selected via the slicer (a simple match) return a position, else na(). In your chart series you return NA() when the month is not selected, except for the data points making the line.
 

Forum statistics

Threads
1,148,157
Messages
5,745,105
Members
423,923
Latest member
yuvaraj859

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
Top