CONSOLIDATE DATA WITH PIVOT WIZARD
What is a Pivot Table Wizard?
• Pivot table is an excel tool. It helps prepare reports and get insights from huge data sets with just a few clicks. • In excel, there is also an inbuilt Pivot Table Wizard function which helps consolidate data across multiple sheets and prepare quick reports
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example - Pivot Table Wizard (1/7)
• We have multiple data sheets showing revenues by regions.
• Each sheet has name of products on rows and months listed in the columns. • We have to prepare a summary showing performance by countries.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example - Pivot Table Wizard (2/7) Check Points Before starting to consolidate the data, we should ensure that Each sheet has the same structure. Meaning, that apart from the data the number of rows, columns, column headers should be the same. There are no blank rows or columns in between. There are no totals, subtotals in between Each column has a header. Email: Support@skillfinlearning.com
Mobile: 9650010925
Example - Pivot Table Wizard (3/7)
• To activate the Pivot Table Wizard, Press Alt & D to activate the Office Access Key. • Then Press P to activate the Pivot Table Wizard.
With Pivot table wizard, we can create a data summary with Just 3 steps Email: Support@skillfinlearning.com
Mobile: 9650010925
Example - Pivot Table Wizard (4/7) Step 1 • Select from the following options for the data source Microsoft Excel list or database, or External data source or Multiple consolidation ranges. • We will select Multiple consolidation ranges, as all the data is present in multiple sheets in a single file. • Select the type of report required Pivot table PivotChart report • We will select Create a Pivot Table. Then Click Next Email: Support@skillfinlearning.com
Mobile: 9650010925
Example - Pivot Table Wizard (5/7)
Step 2a • It asks if we want to create a Pivot Table report that uses ranges from one or more worksheets sheets. Also, how many page fields are required.
• Here we will choose the option, I will create the Page Fields. • Then Click Next.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Example - Pivot Table Wizard (6/7) Step 2b • •
In this step we must select the data range Click on range and then select the data range from the first sheet.
• •
• •
•
Then Click Add. Repeat this step for all the data sheets. Email: Support@skillfinlearning.com
Mobile: 9650010925
Give input on, how many page fields do we want? We will select the option 1. Then, Select the ranges (one by one) in the Range section, and then give a name to them in Field one (as shown in the above screen shot). Once we give a name to all the data ranges, Click Next.
Example - Pivot Table Wizard (7/7) Step 3
• This is the last step to consolidate data. • We must give input on where we want the pivot table report output. Whether in a new worksheet or The existing worksheet?
• We will select the first option (New worksheet) and then Press Finish. • We get the Pivot table report in a new sheet. Email: Support@skillfinlearning.com
Mobile: 9650010925
Data consolidation report • This pivot table has all the features of a normal Pivot table. Some of them are. We can change the report layout by changing the selection of the Pivot table fields. We can change the number format Sort the products by totals Calculate the percent of total sales, or average sales based on the requirement Change the Pivot table design Remove Grand totals and Pivot table headers. Use the Pivot table slicer to create dynamic outputs. • There are multiple other options available which can be explored for further refinements as needed. Email: Support@skillfinlearning.com
Mobile: 9650010925
Want to learn more?
Explore our Business Presentation Skills Training (BPST) in PowerPoint Become a PowerPoint specialist to create client-ready business presentations from scratch through a real-life case illustration
Includes: Annual (1 year) access 100+ Live interactions Unlimited access to vide content Weekly mentorship assistance Individual assignments evaluation Access on Mobile and Tablet Certificate of Completion
Skills you will Master Create meeting presentations in PowerPoint Present insights and data logically Synthesizing key messages on slides Story-telling using PowerPoint presentations Presentation design techniques Client facing communication skills Develop powerful executive summary Build a real-world presentation from scratch
For more details please visit Email: Support@skillfinlearning.com
Business Presentation Skills Training (BPST)
Mobile: 9650010925
Our Training approach ensures You Learn Effectively in Less Time
Email: Support@skillfinlearning.com
Mobile: 9650010925
THANK YOU
Email: Support@skillfinlearning.com
Mobile: 9650010925