Breaking line charts based on categories on horizontal axis

bobbomipmip

New Member
Joined
Sep 22, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi all, I'm new to mrexcel! (Using Office 365 PropPlus)

I am creating a combined excel chart to compare financials across different stores within the same entity.
Specifically, I want to display revenues (clustered columns) and EBITDA margin (line w/ markers) for 2016-2018 for 3 different stores.
However, I want the line chart displaying EBITDA margin to 'break' for each store, meaning I want it to be discontinuous and only show 3 and 3 values at the time.

This is my input data (dummy figures, but has the same characteristics the actual (and bigger) dataset I'm working on).
1600768065500.png


I currently have the following chart:
1600767997108.png


However, I want it to be displayed like this:
1600768020792.png

Keep in mind: this is created using the grey input data in the first image, but I want to do it in another way as is it is both time consuming when I have a larger data set than this and also work bad on the legends (as it shows one legend for each series).

Any suggestions are highly appreciated!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
What you can do is put the EDITBA data into two rows, with stores 1 and 3 in the first row and store 2 in the second. You end up with two separate series for EDITBA, but they don't connect across the blanks. Format the two line series the same, and nobody will ever know.

Bonus: you can remove the redundant EDITBA legend entry easily. Click once to select the legend, select again to select the legend entry, then click the Delete key.

Column Line Combo 2.png
 

bobbomipmip

New Member
Joined
Sep 22, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
What you can do is put the EDITBA data into two rows, with stores 1 and 3 in the first row and store 2 in the second. You end up with two separate series for EDITBA, but they don't connect across the blanks. Format the two line series the same, and nobody will ever know.

Bonus: you can remove the redundant EDITBA legend entry easily. Click once to select the legend, select again to select the legend entry, then click the Delete key.

View attachment 22907
Thanks, I know this is a solution. It's just that I have actually seen someone do "break" the lines with only one series of input, but I can't seem to figure out how...

Thanks again!
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Well, you can format each data point and each like segment individually. Click once to select the entire series, then again to select the like segment or marker, then format as usual, but select No Line to break the series.
 

Forum statistics

Threads
1,141,740
Messages
5,708,236
Members
421,553
Latest member
Geeyj

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