98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Data Analysis - Pivot Table
Data Analysis - Pivot Table
Resolved · High Priority · Version 365
Andrew has attended:
Excel Pivot Tables course
Data Analysis - Pivot Table
I have a file that I would be keen to share with you to obtain support. My aim is to pivot data from an existing excel table, but as the components are not necessarily straight forward, I am finding to difficult to translate into a workable format.
My email address is andrew.sharp@delifresh.co.uk
Thanks
Andrew
RE: Data Analysis - Pivot Table
Hi Andrew,
Thank you for your forum question.
Please can you send your Excel file(s) and further explanations to:
info@stl-training.co.uk
Kind regards
Martin Sutherland
(IT Trainer)
RE: Data Analysis - Pivot Table
Thanks Martin, I have emailed over...
RE: Data Analysis - Pivot Table
Hi Andrew,
Thank you for sending the Trays Driver Analysis spreadsheet and an explanation of what you want to achieve ie. % Trays Out by Trays In statistics per driver for each week.
This is no easy task looking at your data as the totals for each driver work across the multiple columns of daily trays in/out. Also, the drivers do not always do the same run. Ideally what you need is to have 3 columns showing Driver List, No. of Trays In and No. of Trays Out in order to analyse each drivers' total stats in a Pivot Table. Then you can create a calculated field to display % Trays Out by Trays In.
So to get your data structured to these 3 columns you will need to use Power Query which is available within Excel (new versions of Office 365 but also available in older versions as an add-in). This amazing tool can transform your data to how you need it to look and then you can output the changed structure to a Pivot Table to analyse your data.
Here is a Youtube link to an introduction to Power Query:
https://www.youtube.com/watch?v=L4BuUzccLpo
Also, STL provides training in Power Query which you may want to consider
Hope this helps
Kind regards
Martin Sutherland
(IT Trainer)
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Trace Dependents / Precedents without the blue arrowsRather than using the toolbar you can press CTRL+] which is the equivelent of trace dependants and CTRL+[ for precendants. Both of these ways though will not show the blue arrows but jump to the cell containing the formula. |