excel - need to create salary data with salary bands -

excel - need to create salary data with salary bands -

looking create salary chart employees. should xy scatter plot salary info employees grouped title. want floating bar graph representing salary range title.

salary data:

employee,title,salary joe, eng 1, 15000 mike, eng 1, 16000 kelly, eng 3, 25000 steve, eng 2, 20000 jane, eng 3, 30000 michelle, eng 5, 60000 anan, eng 5, 70000

eng level salary band

title,min, max eng 1, 10000, 20000 eng 2, 15000, 30000 eng 3, 25000, 40000 eng 4, 30000, 60000 eng 5, 50000, 80000 eng 6, 60000, 100000

note wont have employees in every level, want show level on chart, levels should shown left right on graph eng 1 eng 6

i having hard time figure out how in excel...your help appreciated

we'll create floating bar chart salary bands, overlay xy scatter points individual data.

first, insert column between min , max salary in bands table, , utilize formula compute span between max , min, shown below. select shaded range , insert stacked column chart. looks top chart below.

format chart follows: remove title (or come in useful). remove legend. alter number format of vertical axis 0,"k" (the comma knocks off set of 3 zeros). format min series no border , no fill, invisible. format span series utilize lighter fill color. alter gap width of span series 75.

insert column contains number of salary band (or alter "eng x" "x") shown below left. re-create shaded range, select chart, take paste special paste dropdown on home tab of ribbon, , utilize options shown in dialog below right (add cells new series, series in columns, series names in first row, category labels in first column). chart looks got new set of stacked bars; we'll prepare shortly.

right click added series, take alter series chart type, , take xy scatter style markers , no lines (below left). select new series markers, press ctrl+1 shortcut format it, take primary axis, aligns nicely existing floating bars, , take format stands out. used dark bluish marker border , white marker fill (bottom left). add together labels using excel 2013 option, label contains value cells, or in older versions of excel, install rob bovey's chart labeler add-in (free http://appspro.com) add together arbitrary labels. also, should stretch chart vertically add together resolution (below right).

excel graph charts scatter-plot