Pivot Table For Beginners – Part 1


Pivot Tables in excel is backbone of data analysis in excel. If you want to perform various analysis in excel, then I would suggest, learn this amazing excel feature to complete your analysis with in few minutes

Common Queries For Pivot Tables

  • What is it
  • Why to use it
  • How to use it
  • When and Where to use it? And so on

Its name is enough to make you feel worried however; usage of Pivot Table is relatively simple. You would enjoy while learning Pivot Table with us.

We would take a simple example and detailed steps including the basics of Pivot Table to make you understand better so that, you can use Pivot Table efficiently

We would just brief you with answers asked above:

  • Pivot Table is very important tool for Microsoft Excel to manage database and make presentation of data
  • It helps to summarize the large or small data into meaningful information to make crucial management decisions
  • We have set of procedures that we need to follow to make Pivot Table, here we have complete guide for you
  • Whenever we required to prepare the summary of large data or to group the data into rows or columns, we can use Pivot Table
  • Pivot Table can be used in preparing projects for School, summary reports in Offices or Home, for personal as well as commercial use

Let's learn Pivot Table:

Pivot Table helps to summarize the data by grouping data in rows or columns to provide the consolidated results even for large database. This helps to read the data in few seconds and get insight of it

Preparation of PivotTable requires a VALID database and below is the requirement of Database:

Database Requirements
  • Database should have HEADERS in all the columns
  • No Columns Headers are BLANK
  • Complete SELECTION of Database is required for complete Pivot Table
Parameters of Database
  • Columns: Combination of cells in VERTICAL manner
  • Rows: Combination of cells in HORIZONTAL manner
  • Headers: DESCRIPTION of columns
  • Database: COMPLETE SET of rows and columns
Database Style for Pivot Table
Simple Case Study

Imagine you are the owner of a company and has global presence with branch offices in multiple countries. Company has sales figures for various regions and for different periods. Your boss asks you to summarize the data and answer below questions:

  • What is the total Sales figure, combining All countries for all Period
  • What is the total Sales figure, Country wise
  • What is the total Sales figure per Country wise and per Period

You can answer these questions in few seconds by creating pivot tables.

Let's create the pivot table:
  • Select the database which you want to summarize. You may go to first cell of the data i.e. Cell "A1" in below displayed data and then press "Ctrl + Right + Down" to select the database
Pivot Table Select Data
  • Then Select Insert from Menu Bar and click on Pivot Table
Pivot Table Option Menu
  • Below window will pop out and choose the parameters as shown below and click OK:
Pivot Table External Sources
  • Most important feature of the Pivot Table: Below screen will appear once you click OK as mentioned above. This is where you will be summarizing your data with few clicks.

    This plays an important role in analyzing the database and prepare the Pivot Table report which can be presented further

    You may use this feature to convert your data into meaningful information.
Pivot Table Fields and Layout
Let's answer the questions asked with the help of Pivot Tables:
  • Total Sales figure Period wise on consolidated level:

    Note: If you want to show multiple data summary in Values column. Simply drag the headers i.e. if you have Profit figures along with Sales figures, so just drag both the headers in values field and your data will show the values
Sales Data Pivot Table
  • What is the total Sales figure, Country wise
Pivot Table Country Wise Sales Data
  • Total Sales figure Country wise for each Period
Pivot Table Country Wise Sales Data

Hope this article helped you to create your first pivot table. You may download the practice sheet from below icon. Please comment if you have any questions or facing any challenge.

Are you enjoying learning excel with us? Subscribe Now for more updates

Leave a Reply

avatar
Excel Dashboard and Reporting
DATA ANALYSIS TRICKS
Best Excel Resources For 2020

While starting Excelsirji.Com, it is always been critical for me to find the best to amaze the viewer experience. So I spent many hours on web to read, explore amazing excel content which I really found helpful to grow as a perfect data analyst. I started learning new things and …

Pareto Chart Sample
DATA ANALYSIS TRICKS
1
Create Pareto Chart In Excel

What is Pareto Chart? Pareto principle was introduced by Italian Economist Vilfredo Pareto. He stated that 80% of the effects are caused by 20% of the causes. So if we closely monitor and solve 20% of the causes. It will significantly improves the affected area and you will see good results.  …

Excel VLOOKUP
DATA ANALYSIS TRICKS
1
What is VLOOKUP in Excel?

An ultimate guide for basic user to understand Excel Vlookup function. VLOOKUP is a vertical lookup which helps the user to extract the values from other columns (leftmost) basis on matching column string.

error

Like the Article? Please spread the word :)

Facebook
Facebook
YouTube
YouTube
Pinterest
Pinterest
LinkedIn