0

I am trying to review the budget for our department at work, but it is an insane amount of data that is organised as followed:

enter image description here

I am trying to use a pivot table to see the changes per month for each cost center, but I can't get Excel to do this for me. I would like to plot to this in a graph like this:

enter image description here

I am using office 365 excel with windows 10.

Improve this question
1
  • 2
    you will need to un pivot the data, will write up a full answer when I have time
    – PeterH
    May 28, 2019 at 13:11

1 Answer 1

Reset to default
1

You can try to use Power Query to get the result:

  1. Select the Range- go to Data- From Table/Range: enter image description here

2.In Power Query Editor- select Columns from Month 1 to Month 7- Go to Transform- Select Unpivot Column- select Cost Column and Value Column- Select Pivot Column and Value Column option is Value: enter image description here

  1. Go to Home- Select Close & Load- Import Data select Pivot Chart: enter image description here

  2. Add Attribute to Axis (Categories) and X,Y to Values- change chart type from Column to Line: enter image description here

Improve this answer
3
  • Is PowerQuery free and usable without admin rights?
    – Michthan
    May 29, 2019 at 14:48
  • What version of Office did you use?
    – Lee
    May 30, 2019 at 7:23
  • I used Office 365
    – Michthan
    Jun 4, 2019 at 7:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.