Making a Bar Graph from Unsual Data Format

[Valy]

I am trying make a bar graph of the data shown below. The x-axis should be "Years" and the y-axis, should be "INCOME," "SPENDING," and "TIPS." In my attempt below, it can be seen that "TIPS" are not following the correct "Years" input.

I created this graph be using the Graph Wizard in Excel.

Is there any way to fix this?

Thank you.

 

[GraH]

Hello,

Simplest way would be to use a pivot chart, so your legend series and axis categories are created as Column/Row coordinates.

Or use a simple SUMIFS to create the correct chart data layout

Book1
	F	G	H	I
1		Income	Spending	Tips
2	2010	5000	2000	900
3	2011	5500	1200	0
4	2012	3500	8700	3000
5	2013	6500	5000	0
6	2014	7800	9500	0
7	2015	6500	1300	1100
Sheet2

Cell Formulas
Range	Formula
G2:I7	G2	=SUMIFS($B$2:$B$16,$A$2:$A$16,$F2,$C$2:$C$16,G$1)

 

[Valy]

Thank you very much.

 

[Valy]

@GraH I was trying to read up on the

SUMIFS

function and I wanted to clarify that you indeed use it to reorganize my table. From what I read, the application of this function returns a set of result, not a table.

 

[GraH]

Would this work on your end? Using dynamic array functions, it returns a table.

Book1
	A	B	C	D	E	F	G	H	I
1	Year	Dollar	Type				Income	Spending	Tips
2	2010	5000	Income			2010	5000	2000	900
3	2011	5500	Income			2011	5500	1200	0
4	2012	3500	Income			2012	3500	8700	3000
5	2013	6500	Income			2013	6500	5000	0
6	2014	7800	Income			2014	7800	9500	0
7	2015	6500	Income			2015	6500	1300	1100
8	2010	2000	Spending
9	2011	1200	Spending
10	2012	8700	Spending
11	2013	5000	Spending
12	2014	9500	Spending
13	2015	1300	Spending
14	2010	900	Tips
15	2012	3000	Tips
16	2015	1100	Tips
Sheet1

Cell Formulas
Range	Formula
G1:I1	G1	=TRANSPOSE(UNIQUE(C2:C16,FALSE,FALSE))
F2:F7	F2	=UNIQUE(A2:A16,FALSE,FALSE)
G2:I7	G2	=SUMIFS(B2:B16,A2:A16,F2#,C2:C16,G1#)
Dynamic array formulas.