Column Charting Question - Group by Year, Area

rjbinney

Active Member
Joined
Dec 20, 2010
Messages
291
Office Version
  1. 365
Platform
  1. Windows
It gets more complicated, but for the sake of learning:
  • I have three sales reps
  • I have multiple territories
    More on that in a second
I was thinking I wanted to show a chart that showed, by year, whether or not a sales rep was in each territory. Binary - he was in or he was out.

I was thinking that I could just do a table:
YearTerritorySalesman 1Salesman 2Salesman 3Salesman 4
And then just put an "X" in the corresponding cell if they sold in that territory in that year. (It's pulling from another sheet).

Then I could do a column chart, grouped by year, with territories on the Y- axis:
20231102_135626 (Small).jpg


Note I don't just want to stack, but I want to show gaps (for example, in Territory 1 in 2019, two salesmen were in there, while all four were mucking about in Territory 4).

I feel like this should be easy - but I just can't get it to work!

My brain has become a black hole of graphing knowledge lately. Any help would be helpful.

Thanks

ETA = The "more on that"? In 2018 there were seven territories, it went up to 13 in 2019 and 2020, and dropped down to eight every year since.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try this:
Book1
ABCDEFGHIJKLMNO
1YearTerritorySales 1Sales 2Sales 3Sales 4
220181xxxx
320182xx
420183xx
520184xxx
620185xx500xx
720191xxx4xxx0
820192xx3x00x
920193xx20xx0
1020194xx1xxxx
1120195xxx1234
122018
Sheet1
Cell Formulas
RangeFormula
K6:K10K6=XLOOKUP($K$12&J6,Table1[Year]&Table1[Territory],Table1[Sales 1])
L6:L10L6=XLOOKUP($K$12&J6,Table1[Year]&Table1[Territory],Table1[Sales 2])
M6:M10M6=XLOOKUP($K$12&J6,Table1[Year]&Table1[Territory],Table1[Sales 3])
N6:N10N6=XLOOKUP($K$12&J6,Table1[Year]&Table1[Territory],Table1[Sales 4])
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K6:K10,L6:L10,M6:M10,N6:N10Cell Value=0textNO
K6:K10Cell Valuecontains ""textNO
L6:L10Cell Valuecontains ""textNO
M6:M10Cell Valuecontains ""textNO
N6:N10Cell Valuecontains "x"textNO


In the table is where you would put the number of territories
 
Upvote 0
I was thinking something like that as a workaround - thanks!

Then copy and paste-as-picture in PowerPoint. I was hoping to graph it - to have things like "shadow", etc.

Thanks
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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