Making a Bar Graph from Unsual Data Format

Valy

New Member
Joined
Dec 25, 2020
Messages
3
Platform
  1. Windows
  2. MacOS
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.

enter image description here
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
708
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,

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


Or use a simple SUMIFS to create the correct chart data layout
Book1
FGHI
1IncomeSpendingTips
2201050002000900
32011550012000
42012350087003000
52013650050000
62014780095000
72015650013001100
Sheet2
Cell Formulas
RangeFormula
G2:I7G2=SUMIFS($B$2:$B$16,$A$2:$A$16,$F2,$C$2:$C$16,G$1)

1608985216692.png
 
Solution

Valy

New Member
Joined
Dec 25, 2020
Messages
3
Platform
  1. Windows
  2. MacOS
@GraH I was trying to read up on the
Excel Formula:
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

Well-known Member
Joined
Mar 22, 2020
Messages
708
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Would this work on your end? Using dynamic array functions, it returns a table.
Book1
ABCDEFGHI
1YearDollarTypeIncomeSpendingTips
220105000Income201050002000900
320115500Income2011550012000
420123500Income2012350087003000
520136500Income2013650050000
620147800Income2014780095000
720156500Income2015650013001100
820102000Spending
920111200Spending
1020128700Spending
1120135000Spending
1220149500Spending
1320151300Spending
142010900Tips
1520123000Tips
1620151100Tips
Sheet1
Cell Formulas
RangeFormula
G1:I1G1=TRANSPOSE(UNIQUE(C2:C16,FALSE,FALSE))
F2:F7F2=UNIQUE(A2:A16,FALSE,FALSE)
G2:I7G2=SUMIFS(B2:B16,A2:A16,F2#,C2:C16,G1#)
Dynamic array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,714
Messages
5,637,940
Members
416,993
Latest member
ant8989

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top