DATA ANALYSIS WITH PIVOT TABLES
What is a Pivot Table? • Pivot table is an excel tool. It helps prepare reports and get insights from huge data sets with just a few clicks. • It can help summarize data, reorganize data, group, sort and filter data quickly. • If we have a large data set and want to get quick insights, it is important to know the Excel Pivot Table feature.
Email: Support@skillfinlearning.com
Mobile: 9650010925
What to ensure before inserting a Pivot Table?
• Before inserting a Pivot table, you need to ensure that the data is in proper format The data set should not have any empty rows/ columns All the columns in the data set should have headers There are no totals/ sub totals in between
• Once all the three conditions are met. Your data set is good for the Pivot table.
Email: Support@skillfinlearning.com
Mobile: 9650010925
How to inserting a Pivot Table? – Example (1/4)
• •
•
To insert a Pivot table, click anywhere on the data. Go to the Insert tab and click Pivot table. You get a dialogue box.
Email: Support@skillfinlearning.com
•
Mobile: 9650010925
The table range is automatically selected. It again asks if the Pivot table report is to be placed in the New worksheet or existing worksheet. We have selected new worksheet. Then click on OK. Once you click on OK. You get a Pivot table set up in another sheet.
How to inserting a Pivot Table? – Example (2/4) •
Pivot table
On top you have the pivot table fields. The field names are the headers of the data set (Journal, Month, Salesperson etc. in our example). This is the reason why we should have a header for each column in the data set.
• •
Email: Support@skillfinlearning.com
Mobile: 9650010925
Below is the area for the fields we want to see in our Pivot table. You must drag the fields to the relevant section (rows, columns, values etc.) based on the type of report you want to create.
How to inserting a Pivot Table? – Example (3/4) For example, if you drag the region in the rows section and revenue in the Values section
Email: Support@skillfinlearning.com
You get this Pivot Table. It summarizes the revenues by region
Mobile: 9650010925
How to inserting a Pivot Table? – Example (4/4) Similarly, if you want to see the sales by region and salesperson, just drag the Salesperson field in the column section. You get the following table.
You can select the month from the dropdown list. If you want to get the sales by month, drag the month in the filter section.
Email: Support@skillfinlearning.com
Mobile: 9650010925
There are a number of options available to change the report format Number Format
Field Headers
Pivot Table Design
Inserting/removing Grand Totals
Sorting Data
Calculations
Layout
Email: Support@skillfinlearning.com
Pivot Table Slicer
Mobile: 9650010925
Number Format
• To change the number format, Right click on the pivot table and Select Number Format and choose the format you want. • You can insert/remove decimals, insert 1000 separator, convert to percent etc.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Field Headers Field Headers
• To remove the field headers, Click on the Pivot Table and select Pivot Table Analyze Tab. • Then Deselect Field Headers. • The row label and Column label description gets removed from the Pivot table.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Pivot Table design
• To change the design of the table, click on the Pivot table, then go to the design tab and select the design from multiple options available.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Inserting/ Removing Grand Totals • The default Pivot table has Grand Totals both in rows and in columns. We have the option to remove either or both. • To add or remove Grand Totals Click on the Pivot Table so that the Design Tab gets activated. Click on the Design tab, Then Click on Grand Totals and select the relevant option for your report.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Sorting Data
• If you want to sort the data by regions, just right click on the Pivot Table and select Sort data.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Calculations • The default value in the Pivot table is Sum of total sales made by each salesperson by region. • In case we want to change it to average sales, just right click on the Pivot Table and select Summarize Values by option. • You get a range of options- Sum, Count, Min, Max, Average etc. You can select the option based on your requirement.
• If instead of total revenues, you want to show the revenues by %, Right click on the table and Select Show Value As option. • You can select from the list of available options - % of Grand Total, % of Row Total, column Total etc. Email: Support@skillfinlearning.com
Mobile: 9650010925
Changing the layout of the Pivot Table
• If we want to change the layout of the Pivot Table, just click on the table and then in Pivot Table fields, make the changes you want. • For example, if we want to see the revenues by regions and salesperson in rows, drag the salesperson field from Columns section to Rows section. Your table will appear as follows.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Slicer • Pivot table slicer is a tool to filter data and make the Pivot Table report interactive. • To insert a Pivot Table slicer, Click on the Table. Then Go to PivotTable Analyze Tab. Select Insert Slicer.
• You get an insert Slicer Dialogue Box. Choose the relevant option. • In this example, selecting the Salesperson. Then Click OK. We get the Slicer besides out Pivot Table. We get the full list of Salesperson. The Pivot Table data gets filtered based on the chosen option.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Data Update for future reports
• In case there is any change in the Pivot Table source data, the Pivot table does not get updated automatically. • Right click on the table and then click on Refresh so that the changes in the data get reflected in the table.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Conclusion
• This document is a brief illustration of how we can prepare interactive reports using Pivot table feature in Excel.
• If you like playing with Data sets, pivot table is a great tool. For you to get a good understanding just keep exploring the different features of the Pivot table. It will help you further enhance your data analysis skills in excel.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Want to learn more?
Explore our “Data Analysts Skills Training” (DAST) course Go from beginner to an Expert in Data analytics Skills in Excel. Join this program to build your Excel analytical skill quickly through hands on project Skills you will master: Data analytics functionalities in Excel Data visualization through charts Logical analysis driven decision-making Data cleaning and improvisation methods Pivot Table analysis Professionally format datasheets Excel tips and tricks using shortcuts Excel skills for business purposes Apply data analysis on real life datasets
Coaching includes: Annual (1 year) Subscription 100+ Live Interactive Sessions Unlimited Access to Video Content Weekly Mentorship Assistance Individual Assignments Evaluation Access on Mobile and Tablet also Certificate of Completion For more details please visit Data Analyst Skills Training (DAST)
Email: Support@skillfinlearning.com
Mobile: 9650010925
Our Training approach ensures You Learn Effectively and in Less Time
Email: Support@skillfinlearning.com
Mobile: 9650010925
THANK YOU
Email: Support@skillfinlearning.com
Mobile: 9650010925
Visit us: www.skillfinlearning.com Chat with us: +91 96500 10925
Email us: support@skillfinlearning.com