CODEWITHSUNDEEP
excelplotlinepivot-tablebreak
user974465
user974465

Reputation:

How to make PivotChart with line breaks

I have data like the following:

x   y   f
1   1   1.2
1   2   1.4
1   3   1.6
3   1   3.2
3   2   3.4
3   3   3.6
5   1   5.2
5   2   5.4
5   3   5.6

If you insert a pivot chart, you can plot f vs x and y using a line chart, and the plot has two stacked x-axes where the lower x-axes values are 1 3 5 corresponding to x, and the upper x-axes has values 1 2 3 for each value of the lower x-axes, representing x = 1 and y = 1 2 3, then x = 2 and y = 1 2 3, and x = 3 and y = 1 2 3. The plot should show a single continuous line from left to right. What I would like is for the line to break when x changes values, so there are three short lines showing the influence of y for constant values of x.

This link makes a chart similar to what I'm describing in the answer. In terms of that figure, what I want is for the link to break every time the year changes. But the answer they have, and discussion doesn't get what I'm looking for. The only approach that I can think of is to modify the PivotTable data by hand and add a row at the location the data breaks. I tried to do something like that at work, but before modifying the table, I copied the table as values to a separate location. With the new data table, I was not able to create the plot with two x axis. If I created the plot, I could put a second value in when y = 3, and for f have NA(), which should create the break in the proper location.

Upvotes: 0

Views: 2816

Answers (1)

pnuts
pnuts

Reputation: 59485

For something that looks like:

SO33451676 first example

Select each of the second and subsequent y 1 values (individually):

SO33451676 second example

and Format Data Point..., Line, No line.

(BTW IMO better suited to Super User.)

Upvotes: 3

Related Questions