Column Charting Question - Group by Year, Area

[rjbinney]

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:

Year

Territory

Salesman 1

Salesman 2

Salesman 3

Salesman 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:

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.

 

[Skybluekid]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Year	Territory	Sales 1	Sales 2	Sales 3	Sales 4
2		2018	1	x	x	x	x
3		2018	2		x	x
4		2018	3	x			x
5		2018	4	x	x	x
6		2018	5			x	x			5	0	0	x	x
7		2019	1	x		x	x			4	x	x	x	0
8		2019	2	x	x					3	x	0	0	x
9		2019	3	x		x				2	0	x	x	0
10		2019	4	x			x			1	x	x	x	x
11		2019	5	x		x	x				1	2	3	4
12											2018
Sheet1

Cell Formulas
Range	Formula
K6:K10	K6	=XLOOKUP($K$12&J6,Table1[Year]&Table1[Territory],Table1[Sales 1])
L6:L10	L6	=XLOOKUP($K$12&J6,Table1[Year]&Table1[Territory],Table1[Sales 2])
M6:M10	M6	=XLOOKUP($K$12&J6,Table1[Year]&Table1[Territory],Table1[Sales 3])
N6:N10	N6	=XLOOKUP($K$12&J6,Table1[Year]&Table1[Territory],Table1[Sales 4])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K6:K10,L6:L10,M6:M10,N6:N10	Cell Value	=0	text	NO
K6:K10	Cell Value	contains ""	text	NO
L6:L10	Cell Value	contains ""	text	NO
M6:M10	Cell Value	contains ""	text	NO
N6:N10	Cell Value	contains "x"	text	NO

In the table is where you would put the number of territories

 

[rjbinney]

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