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!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,922
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,922
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,703
Messages
5,541,306
Members
410,545
Latest member
Upsindustrial20
Top