Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel 2010 Pivot Tables...Preparing Data

Posted on 2014-12-10
9
Medium Priority
?
148 Views
Last Modified: 2014-12-11
I wish to prepare a pivot table so I can prepare a cash-flow of expenditure over each year for different criteria.
The data I have been given shows the expenditure each year. (refer Table 1 in 'Data' sheet on attached Example file)
But Im not sure how I can create a pivot table from the raw data without having to reorganise it as shown in Table 2 in 'Data' sheet on attached Example file.
There is lots of data (not shown in Example)  so need to avoid reorganising the raw data.
Any suggestions how to do this.
Example-Pivot-Table-data.xlsx
0
Comment
Question by:gh_user
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1500 total points
ID: 40490718
It's really not that difficult to rearrange your source data (even simpler if you have PowerQuery) and it will probably make your life a lot easier - although you haven't really given much indication of what you intend to do with your pivot table.
0
 

Author Comment

by:gh_user
ID: 40490976
Hi Rory
The 2nd sheet of the file I uploaded gives an idea of one of the ways I want to present the information using the pivot table.
I use Microsoft Office Home & Business 2010.  MS says Power Query requires Microsoft Office 2010 Professional Plus with Software Assurance. Hmm
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 1500 total points
ID: 40491013
You can produce that pivot table very easily from the first table of data, which is why I was asking what else you want to do.

If you can't use PQ, I'd suggest some code that simply creates one table from all the sheets of data and you can then pivot from that consolidated table. Would that suit you?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gh_user
ID: 40491161
That would be good Rory
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40491204
Is your source data always in that exact format with the merged header cells?
0
 

Author Comment

by:gh_user
ID: 40492536
Hi Rory
The file iI attached contained simplified information.  I can get rid of merged headers,,ie remove 'cost' cell and just have the years as the headers for the columns with costs.
I can see how I can do pivot table straight from raw data.
So all I need is to be able to produce one pivot table for each sheet of data (I have one for each site).
Each sheet will be the same format, but could have extra rows as may be more activities for one site vs another.
Note sure if you can provide code without having the exact spreadsheet and data structure Im using.
0
 

Author Comment

by:gh_user
ID: 40492899
Hi Rory.  I ended up getting Power Query which seems like it will create pivot table from multiple sheets.  So most likely can get by without code.
The pivot table will structure the data that I wish to show.  But I need to make some adjustments to the format headings etc to make it present well.  Is there a way to do this?
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 1500 total points
ID: 40493475
You can change field names either in PQ or in the pivot table, if that's what you mean?
0
 

Author Closing Comment

by:gh_user
ID: 40494825
Thanks for offer of coding, and addressing follow up queries
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question