Comparing two countries workers income based on their highest degree and age

[Leighton Durham]

hi I have these two sets of data that i need to compare to each other. My desired outcome is to be able to determine the correlation between the degree affecting the income (dose the degree you have affect the amount of income you get). As far as i know the best way to do this is via scatter plot but when i try to use them it keeps displaying numbers instead of the degree it would be great if someone could help me out with this... here is the Australian data i'm using.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Australian Data
2	(Aus)School Only		(Aus)Diploma		(Aus)Undergraduate		(Aus)Masters		(Aus)PHD
3	Age	Yearly Income	Age	Yearly Income	Age	Yearly Income	Age	Yearly Income	Age	Yearly Income
4	30	$ 35,000.00	31	49000	31	$ 64,000.00	28	62000	38	81000
5	31	$ 48,000.00	32	39000	32	$ 71,000.00	29	76000	38	83000
6	32	$ 40,000.00	33	44000	34	$ 71,000.00	31	76000	38	91000
7	32	$ 49,000.00	33	45000	34	$ 72,000.00	33	68000	38	85000
8	33	$ 38,000.00	35	51000	36	$ 62,000.00	35	83000	38	93000
9	34	$ 40,000.00	35	62000	38	$ 71,000.00	35	76000	39	89000
10	34	$ 41,000.00	36	52000	38	$ 64,000.00	35	77000	39	89000
11	34	$ 41,000.00	38	48000	38	$ 63,000.00	35	68000	40	82000
12	34	$ 28,000.00	38	51000	39	$ 61,000.00	35	74000	41	81000
13	34	$ 49,000.00	39	54000	39	$ 70,000.00	36	69000	41	81000
14	35	$ 44,000.00	39	57000	40	$ 69,000.00	37	76000	41	90000
15	35	$ 49,000.00	39	63000	40	$ 64,000.00	37	81000	41	80000
16	35	$ 44,000.00	39	58000	40	$ 56,000.00	37	74000	42	81000
17	35	$ 38,000.00	40	58000	40	$ 67,000.00	38	67000	42	94000
18	35	$ 41,000.00	40	60000	41	$ 61,000.00	38	78000	42	87000
19	35	$ 50,000.00	40	57000	41	$ 72,000.00	38	74000	43	89000
20	36	$ 42,000.00	40	60000	41	$ 69,000.00	38	70000	43	80000
21	36	$ 47,000.00	41	59000	41	$ 60,000.00	38	80000	43	94000
22	36	$ 51,000.00	41	54000	42	$ 67,000.00	38	75000	43	94000
23	36	$ 35,000.00	41	65000	42	$ 67,000.00	39	73000	43	86000
24	36	$ 48,000.00	41	62000	42	$ 80,000.00	39	74000	47	105000
25	36	$ 45,000.00	41	57000	42	$ 71,000.00	39	76000	47	97000
26	36	$ 50,000.00	41	48000	42	$ 60,000.00	39	73000	47	105000
27	37	$ 46,000.00	42	57000	42	$ 76,000.00	40	91000	48	105000
28	37	$ 44,000.00	42	51000	42	$ 72,000.00	40	82000	48	100000
29	37	$ 43,000.00	42	55000	43	$ 67,000.00	40	80000	48	102000
30	37	$ 59,000.00	42	66000	43	$ 68,000.00	40	82000	48	106000
31	38	$ 49,000.00	43	64000	43	$ 68,000.00	41	83000	48	100000
32	38	$ 43,000.00	43	55000	43	$ 67,000.00	41	78000	49	108000
33	39	$ 45,000.00	43	52000	43	$ 79,000.00	41	79000	49	109000
34	39	$ 44,000.00	43	59000	44	$ 62,000.00	42	86000	49	103000
35	39	$ 74,000.00	43	62000	44	$ 64,000.00	42	84000	50	102000
36	40	$ 57,000.00	44	65000	45	$ 68,000.00	42	85000	50	100000
37	41	$ 71,000.00	44	52000	45	$ 75,000.00	43	90000	50	97000
38	42	$ 56,000.00	44	61000	45	$ 81,000.00	43	90000	50	97000
39	42	$ 50,000.00	45	43000	45	$ 76,000.00	43	90000	50	111000
40	43	$ 53,000.00	45	66000	46	$ 69,000.00	43	82000	51	109000
41	43	$ 50,000.00	46	54000	46	$ 82,000.00	43	85000	51	101000
42	43	$ 53,000.00	46	55000	46	$ 70,000.00	44	80000	51	101000
43	44	$ 44,000.00	46	59000	46	$ 82,000.00	44	94000	52	102000
44	44	$ 55,000.00	46	51000	46	$ 77,000.00	44	78000	52	99000
45	44	$ 51,000.00	46	62000	47	$ 72,000.00	44	85000	52	102000
46	44	$ 46,000.00	47	42000	47	$ 82,000.00	44	75000	52	91000
47	45	$ 66,000.00	47	56000	48	$ 93,000.00	45	87000	52	99000
48	45	$ 49,000.00	48	62000	48	$ 84,000.00	45	80000	53	104000
49	45	$ 63,000.00	49	41000	48	$ 94,000.00	45	89000	53	90000
50	45	$ 54,000.00	49	65000	48	$ 87,000.00	45	90000	53	93000
51	46	$ 59,000.00	49	63000	48	$ 86,000.00	45	85000	53	90000
52	46	$ 64,000.00	50	66000	49	$ 82,000.00	45	89000	54	101000
53	47	$ 70,000.00	50	53000	49	$ 91,000.00	45	94000	54	93000
54	47	$ 48,000.00	51	58000	49	$ 73,000.00	46	90000	54	104000
55	48	$ 65,000.00	51	49000	49	$ 88,000.00	46	84000	54	97000
56	48	$ 64,000.00	51	62000	49	$ 86,000.00	46	78000	55	100000
57	48	$ 56,000.00	52	59000	49	$ 84,000.00	46	91000	55	104000
58	48	$ 48,000.00	52	57000	50	$ 72,000.00	47	80000	55	96000
59	48	$ 57,000.00	52	49000	50	$ 90,000.00	47	84000	55	103000
60	49	$ 56,000.00	52	61000	50	$ 83,000.00	48	83000	55	96000
61	49	$ 48,000.00	53	61000	50	$ 80,000.00	49	90000	56	103000
62	49	$ 43,000.00	53	42000	50	$ 91,000.00	50	83000	56	93000
63	50	$ 49,000.00	53	50000	50	$ 86,000.00	51	94000	57	102000
64	50	$ 49,000.00	54	56000	51	$ 90,000.00	51	81000	60	102000
65	50	$ 76,000.00	55	66000	52	$ 85,000.00	52	91000	60	100000
66	51	$ 63,000.00	55	60000	52	$ 82,000.00	53	82000	61	99000
67	51	$ 35,000.00	55	57000	52	$ 93,000.00	53	85000	61	105000
68	51	$ 40,000.00	56	64000	52	$ 89,000.00	54	78000	61	99000
69	51	$ 63,000.00	57	64000	53	$ 87,000.00	54	72000	62	99000
70	51	$ 53,000.00	57	70000	54	$ 93,000.00	54	90000	62	100000
71	52	$ 37,000.00	57	60000	54	$ 84,000.00	54	79000	62	101000
72	52	$ 58,000.00	58	68000	56	$ 92,000.00	54	76000	62	93000
73	52	$ 46,000.00	58	64000	56	$ 94,000.00	55	91000	62	102000
74	52	$ 61,000.00	58	68000	56	$ 91,000.00	55	77000	62	100000
75	53	$ 60,000.00	58	58000	56	$ 89,000.00	55	92000	63	95000
76	53	$ 65,000.00	59	61000	57	$ 92,000.00	55	93000	63	99000
77	54	$ 58,000.00	59	63000	57	$ 83,000.00	55	84000	64	102000
78	54	$ 57,000.00	59	65000	57	$ 88,000.00	56	83000	64	99000
79	55	$ 55,000.00	59	62000	57	$ 69,000.00	56	78000	64	101000
80	55	$ 59,000.00	59	64000	57	$ 78,000.00	56	85000	64	99000
81	56	$ 60,000.00	59	64000	58	$ 91,000.00	56	92000	65	101000
82	56	$ 59,000.00	59	66000	58	$ 91,000.00	56	82000	65	97000
83	57	$ 59,000.00	59	60000	58	$ 96,000.00	57	77000	65	97000
84	57	$ 53,000.00	59	60000	58	$ 81,000.00	58	69000
85	57	$ 63,000.00	60	69000	58	$ 101,000.00	58	79000
86	57	$ 65,000.00	60	63000	59	$ 93,000.00	58	72000
87	57	$ 61,000.00	60	69000	59	$ 82,000.00	58	96000
88	58	$ 47,000.00	60	61000	59	$ 108,000.00	58	67000
89	58	$ 55,000.00	60	61000	59	$ 92,000.00	58	75000
90	58	$ 64,000.00	60	59000	59	$ 89,000.00	59	70000
91	58	$ 58,000.00	60	62000	59	$ 78,000.00	59	85000
92	59	$ 57,000.00	60	65000	60	$ 75,000.00	59	96000
93	59	$ 51,000.00	61	69000	60	$ 76,000.00	59	94000
94	59	$ 58,000.00	61	58000	60	$ 85,000.00	59	84000
95	59	$ 55,000.00	62	68000	60	$ 91,000.00	60	81000
96	59	$ 52,000.00	63	61000	60	$ 85,000.00	60	64000
97	60	$ 62,000.00	63	61000	60	$ 80,000.00	60	73000
98	60	$ 46,000.00	64	52000	60	$ 90,000.00	60	97000
99	60	$ 65,000.00	64	62000	60	$ 97,000.00	61	87000
100	61	$ 60,000.00	65	64000	61	$ 81,000.00
101	62	$ 71,000.00	65	54000	61	$ 74,000.00
102	63	$ 70,000.00			61	$ 84,000.00
103	64	$ 69,000.00			63	$ 102,000.00
Sheet1

 

[KSSC88]

Create another column next to each segment and use that as your Label cell reference. But I wouldnt recommend that as you see it gets really busy in the graph. Just going with the color coding is the correct approach for this (second graph)
For your reference see below search results on how to:

How to improve your Excel Chart

What's on this page Custom data labels Improve your X Y Scatter Chart with custom data labels How to apply custom data labels in Excel 2013 and lat

www.get-digital-help.com

 

[Leighton Durham]

View attachment 33805
Create another column next to each segment and use that as your Label cell reference. But I wouldnt recommend that as you see it gets really busy in the graph. Just going with the color coding is the correct approach for this (second graph)
For your reference see below search results on how to:

How to improve your Excel Chart

What's on this page Custom data labels Improve your X Y Scatter Chart with custom data labels How to apply custom data labels in Excel 2013 and lat

www.get-digital-help.com

no sorry i may not have been clear on this but i need to have the names of the degree at the bottom of the graph and have the data points collect in the spaces of the graph corresponding to the degree if that makes sense... like this

 

[KSSC88]

You can do a panel chart. Basically have to put everything in one long table and do a "insert Pivot table and chart" with Degree and Age in Rows, separator in Columns and Income in Values. Hope this helps