Unusual chart design - possible?

[Gingertrees]

I've been asked to produce a chart based on sales data for 4 companies, across time. The design that was requested (sketched out for me, in fact) is unlike any I've ever seen in Excel. The idea is to have a bar chart, spanning 12 months time, with this year's data on top, and the prev. yr's data mirror-imaged below. So there would be POSITIVE numbers on both sides of the axis. For each calendar month, there would be a group of 4 bars representing the four companies. I tried to approximate it in the code below:

(the columns of X, W, P, and L represent sales from diff. companies.)
Say that the first column of Jan represents sales of $400 in 2012, $200 in 2011. 
In W, $200 in 2012, $300 in 2011.

2012
Jan                     Feb             Mar            A ...    Dec.
$4  $2    $4  $3        3  3  4  3
X         P                   P 
X         P    L        X  W  P  L
X    W    P    L        X  W  P  L
X    W    P    L        X  W  P  L
----------------------------------------------------------------------
X    W    P    L        X  W  P  L
X    W    P    L        X  W  P  L
     W    P             X     P
          P                   P 
$2   $3   $4  $2        3  2  4  2
Jan                     Feb            Mar             A ...
2011

Is this a possible chart design? How would I create it?

 

[kevinb11]

http://www.excelforum.com/excel-charting/481011-mirror-flip-axis-chart.html

found this in another forum

 

[JokelesComedian]

Excel 2007
	A	B	C	D	E	F	G	H
3	Jan-12				Feb-12
4	XXXX	WW	PPPP	LLL
5	XX	WWW	PPPP	LL
6	Jan-11				Feb-11
Sheet3

Cell Formulas
Range	Formula
A4	=REPT(J7,J11/100)
A5	=REPT(J7,J8/100)
B4	=REPT(K7,K11/100)
B5	=REPT(K7,K8/100)
C4	=REPT(L7,L11/100)
C5	=REPT(L7,L8/100)
D4	=REPT(M7,M11/100)
D5	=REPT(M7,M8/100)

Excel 2007
	I	J	K	L	M
7		X	W	P	L
8	Jan-11	200	300	400	200
9	Feb-11
10	Mar-11
11	Jan-12	400	200	400	300
12	Feb-12
13	Mar-12
Sheet3

On the table you can put the letter you want and the amount divided by a number and it will repeat that letter that many times.

On mine I right clicked the repeated letter cells and made it 90 degrees so it will go up. Then it will look like your diagram

 

[Steve=True]

Hi GingerTrees

Is this what you are looking for?

The steps to do this are:

1) to create another chart area that is the Negative value of the previous year and create your chart. It will then look like this:

2) Then right click on the negative series and move it to the Secondary Axis and it will look like this:

3) Now click on the right vertical axis and press your delete key. and it will look really close but your vertical axis will have a Negative values that you may want to make positive.

4) Right click on the Left vertical axis and change the number format to a custom number format with this #,##0;#,##0
and you will get the chart i posted at the top of this reply.

Please let me know if this helps!!

Thanks

Steve=True

 

[Steve=True]

Sorry Ginger Trees,

I looked at your request and I didnt see that you were looking to group 4 companies. Here is how your data would look to get exactly at your request.

Please let me know if this was helpful!

Thanks

Steve=True

 

[Gingertrees]

Steve=True, thanks! That's exactly what I needed. Also, what software/addin do you use for your Excel screenshots? I haven't found any links on this site that aren't deprecated.

 

[Steve=True]

Glad it worked. You are very welcome.

I managed to create a video tutorial on this for you as well. you can get it here. I missed a step in my example above, but this one details putting in a SPACE above your month in order to create a better axis grouping.
http://www.exceldashboardtemplates.com/?p=1339

Also, to answer your question, I am not exactly sure what you mean by "deprecated".

I typically will use Snag-it for my screen capture and some times just Alt+Shift+print screen then copy to Paint, then save as png file.

Let me know what you meant.

Thanks

Steve=true

 

[Gingertrees]

Wow, I made your blog! And got a video! Cool! The addition of the space is a neat trick, too.

Deprecated: obsolete, no longer updated, not recommended for use

Pictures: I thought all pictures had to have a web address - when I click the "Insert Image" button above, it asks for a URL. Is there some way around this?

 

[Steve=True]

Glad you like the space trick.

Hopefully you liked the post and video tutorial as well.

To post a picture, i load them onto a website and then post the url into the picture link you describe in the forum.

Not sure if there is another way, but there may be.

Thanks

Steve=True

 

[Gingertrees]

One more thing: with this format, how would I add data labels to it? I think I just need the top half, showing % change from last year. So if Company W's Jan. sales were $400, and last year was $300, I want Jan 2012 to appear with a 33% above it.

33%
W
W
W
W
----
W
W
W