How to Create a Pivot Table in Excel – Step by Step Easy Guide

Creating a Pivot Table : A picture table is a program tool which allows us to re organise and summarise the selected columns and rows of data in a spreadsheet or database table for obtaining a desired report . Pivot tables are generally used in data processing and are found in visualising data programs . A pivot table does not actually change the spreadsheet or database by it’s own . In database the pivot is used to turn the data to view it from a different perspective .

The pivot table is especially useful for the large amount of data like , a store owner or manager prepared a list of monthly sales total for the large number of merchandise items in an Excel spreadsheet but if the owner wanted to know that which item has been sold better in a particular financial quarter then it would be very time consuming for them to look over the pages and pages of figures for finding out the desired information . A pivot table will allow the owner to quickly and easily reorganise the data and creating a summary for each of the item for the quarter . A pivot table allows you several operation to perform like sorting , averaging or summing up the data in the first table . It also includes the grouping of the data .

There are basically four types of fields or layouts that are used in pivot table :

1 . Report filter : Report filter is used to apply a filter in entire pivot table . When any option is chosen from the drop down list then the table will be visible only in the data from those selected rows or columns .

2 . Column labels : Column labels are generally used for applying filters to one or more columns that are needed to be shown in the Pivot table . There will be a filter in the data column labels from which one can select  or deselect the particular column for the pivot table .

3 . Row labels : Row labels of pivot table are used for applying a filter to one or more rows which are have to be shown in the Pivot table . There will be a filter in the data column labels from which one can select or deselect the particular column for the pivot table .

4 . Summation labels : This generally takes a field which is having a numerical value that can be used for different types of calculations . However using the text values will also not be wrong instead of sum it will give you the count .

 

What is a Pivot Table Excel ?

In Microsoft a pivot table is a report that allows you to summarize , analyze , explore and presenting a summary of the data completed by you . The pivot table is very useful when you are having a very long list of figures to sum out and aggregate data or subtotals will help us to look at the data from different perspectives and comparing the figures of similar data . In other words a pivot table allows you to extract the meaning from that endless appearing list which lets you to group your data together in several different ways so that you can more easily and conveniently make the comparisons .

The pivot table is a part of a pivot table stems from the fact that you can easily rotate the data in the table in order of viewing it at different perspective . Just make it clear that you are not adding to , subtracting from or otherwise changing the data when you are making a picture table , instead of it you are simply reorganizing the data in pivot table so that you can extract the useful information from it easily .

Use of the pivot table

Use of a pivot table report is to summarize , analyze , explore and presenting summary data . You can use a pivot table or pivot chart report for visualizing the summary data in a pivot table report and for easily seeing the comparisons , patterns and trends . Pivot table report and pivot chart both are enable for you to make informed decisions about about the critical data in the enterprise . A pivot table report is an interactive way for quickly summarizing the large amount of data . You can use a pivot table report for analyzing numerical data in detail and to answer an unanticipated questions about the data . A pivot table report is specially designed for –

* Quering the large amounts of data in many of the user – friendly ways . Subtotaling and aggregating numeric data , for summarizing data by categories and subcategories and for creating custom calculations and formulas .

* For expanding and collapsing levels of data for focusing your results and drilling down the details from the summary data for areas of your interest to you .

* For moving rows to columns or columns to rows , to see the different summaries of the source data .

* It is used for filtering , sorting , grouping and conditionally formatting the most useful and interesting subset of data for enabling you to focus on the information that you want .

* For presenting concise , attractive and annotated online or printed reports .

 

Oftenly , when you use a pivot table report when you want to analyze the related totals , especially when you have a long list of figures to sum and you wanted to compare several facts about every figure .

 

How to create a Pivot chart ?

 

It will be hard of seeing the big pictures when you are having a very big pivot table or you are having a lot of complex worksheet data which includes that the text and numbers with column headings .

A pivot chart can help you in making the sense of a data . When a pivot chart shows the data series , categories and chart axes in the same way that a standard chartered does , it also gives you an interactive filtering controls right on the chart so that you can easily and quickly analyze the subset of your data.

For worksheet data you need not to create a Pivot table first you can create a Pivot chart . You can also create a recommended Pivot chart for your data , Excel will then automatically create a Pivot table for you . For creating a Pivot chart you just need to follow the below mentioned steps –

 

Step – 1 : First of all you need to click anywhere in the data .

how to create a pivot chart

Step – 2 : Now go to the insert tab option in menu bar then , click on the option of charts group . Here you will see some options from these you have to select the option of recommended charts .

how to create a pivot chart

 

Step – 3 : Now , on the recommended charts tab first of all pick any of the tab with the pivot chart icon at the top right corner of the sample chart . A preview of your pivot chart will appear in the preview pane .

how to create a pivot chart

 

Step – 4 : Next , when you found out the Pivot chart of your choice then click on OK button which is present at the bottom right side corner of your screen .

how to create a pivot chart

Step – 5 : Now , in the pivot chart which appears in front of you , click on any interactive control and then pickup any of the sort or filtering options that you want .

how to create a pivot chart

Another useful tutorial, we have in video is,

Learn Pivot Tables in 6 Minutes (Microsoft Excel)

How to Create Pivot chart from existing Pivot Table ?

Let us know that how we can create the pivot chart from the existing pivot table . Just follow the below steps for creating pivot chart from pivot table :

 

Step – 1 : First of all you need to click anywhere on the Pivot table to show the Pivot table tools on the ribbon .

how to create a pivot chart

 

Step – 2 : Now , you have to click on the analyze option present in the Pivot table tools . Now , in analyze option you will find out the another option of Pivot Chart here . Click on it .

how to create a pivot chart

 

Step – 3 : Now , in the insert chart dialog box you have to click on the chart type and the sub type of chart which you want . You are free to choose any of the chart type except an XY ( scatter ) , bubble or stock chart .

how to create a pivot chart

 

Step – 4 : Next , you have to click on the OK button available at the bottom right side of the screen .

how to create a pivot chart

Step – 5 : Now , in the appeared Pivot chart you can click on any of the interactive control and then pickup the sort or filtering options whichever you wants .

 

Create a pivot chart to connect to the external data

Follow the below mentioned steps if you want to connect your Pivot chart to the external data :

 

Step – 1 : Click on the option data in your pivot chart then go to the next option which will be present in data is from other sources which you want . Now , you can pickup the data source whichever you want . For example : You can pick up From analysis services , if you want to connect to an Online Analytical Processing ( OLAP ) cube file .

how to create a pivot chart

 

Step – 2 : Now , you have to follow the steps of Data connection wizard and then click on the Finish button .

 

Step – 3 : Now , from the import data dialog box pick the option of Pivot chart and the particular location where you want to put the data and then click on OK .

 

* An empty pivot chart will get appeared empty and the field list is shown so that you can add or rearrange fields in your pivot chart .

 

Step – 4 : Now , go to the fields list and pick up the field which you wanted to show in your Pivot chart

how to create a pivot chart

 

* After creating the pivot chart , you can easily customize it .

 

How to create a Pivot table in excel 2013 ?

 

Being able in quickly analyzing data can help you in making better business decisions . But sometimes it is hard to know that from where to start , especially when you are having a lot of data . As we know that pivot table is a great way for summarizing , analyzing or creating table in just few clicks . Pivot tables are highly flexible and can be quickly adjustable depending on that how you will be needing to display your results . You can also create the Pivot chart which is based on Pivot tables which will get automatically updated when your pivot tables do .

 

Let us know that how we can create the Pivot table in Microsoft Excel .

 

Steps to Make Pivot Table in MS Excel :

* Your data must be organized in a tabular format and should not have any blank rows or columns . Ideally you can also use an Excel table .

 

* Tables are the great Pivot table data source because the rows which are added to a table are automatically included in the Pivot table when you refresh your data and any of the new column will be included in the Pivot table fields list . Otherwise , you need to manually update the data source range or can use a dynamic named range formula .

 

* Data type presented in columns should be same . You should not mix dates and text in the same column .

 

* Pivot tables work on the snapshot of your data which is called cache so that your actual data does not get altered in any way .

 

Follow the below mentioned steps for creating the pivot table :

 

If you have limited experiences with the pivot tables or you are not sure how to get it started then a recommended Pivot Table will be a good choice for you . When you are using this feature Excel will determine a meaningful layout by just simply matching the data with the most suitable areas in the pivot table , this help will give you a starting point for the additional experimentation . After you create a recommended Pivot table you will be able to explore different orientation and rearrange the fields in achieving the specific results . The recommended Pivot table feature was added in the Excel 2013 so that if you have any earlier version , follow the below instructions for how to manually create a Pivot table .

 

Excel Pivot Table Tutorial for Beginners {Video Tutorial} 

Recommended pivot table :

 

Step – 1 : Click a cell in the source data or table range option .

pivot table in excel

 

Step – 2 : Go to insert option then go to the tables option , inside it you will find the recommended Pivot table option .

pivot table in excel

 

Step – 3 :  The Excel will analyze your data and will present you with several options like in the example by using the household expense data .

 

 

Step – 4 : Now , you can select the pivot table which looks best to you and after selecting the best one click on the OK . Excel will automatically create a new pivot table on a new sheet and will display the pivot table fields list .

pivot table in excel

Note : Above are the steps if you wanted to use the recommended pivot table now let us know that how we can create a pivot table manually .

 

Manually creating a pivot table :

 

Step – 1 : first of all click a cell in the source data or the table range .

pivot table in excel

Step – 2 : Next go to the insert option present on the menu bar . Then in the insert option you will find the option of table click on it . A list will get open in front of you with some options you have to go to the pivot table option . Click on it .

pivot table in excel

 

 

Step – 3 : Now , after clicking on the pivot table option you will see that excel will automatically display the Create Pivot table dialog with your preferred range or the selecting table name . In this case we are going to use the table called “ tbl_Householdexpenses” .

pivot table in excel

 

 

Step – 4 : Now , in the above image you are seeing an option of Choose where you want the Pivot table report to be placed . In this field you will see two options that are New worksheet or Existing worksheet . You have to click on the New worksheet . If you wanted to choose the option of Existing worksheet then you have to select both of the worksheet and the cell wherever you wanted to place your Pivot table .

Step – 5 : If you wanted to include the multiple tables or the data sources in your Pivot table then , you need to click on the small box present in front of the field Add this data to the Data model at the bottom of this dialog box . Check the box .

pivot table in excel

 

Step – 6 : After completing all of the above steps at last you need to click on the option OK which is present at the bottom right side of the box . when you click on OK excel will create a blank Pivot Table and will display the Pivot Table Fields list .

pivot table in excel

 

How to create a Pivot table in excel 2017 ?

 

Assume that we have the following sample data for which we have to make an Pivot table .

 

 

Now , let us create a Pivot table by using the above data just follow the below mentioned steps :

 

Note : You can use any data it is just a sample image for more clarification .

 

Step – 1 : First of all you need to make an excel file of the above mentioned data .

how to create a pivot table

 

Step – 2 : Now , open your prepared excel file and then go to the insert option on the menu bar . Then , go to the pivot table option .

how to create a pivot table

 

Step – 3 : You will be going to have the following window in front of you .

 

 

Step – 4 : Now , you have to click on the option of table / range button as shown in the image above .

how to create a pivot table

Step – 5 : Next , you will be going to have a mini window in front of you like this

how to create a pivot table

 

Step – 6 : Now , click on the box of cell address A1 . Next , you have to press Ctrl + A on your keyboard for selecting all of the data cells of the sheet .

how to create a pivot table

Step – 7 : After it a mini window will again appear in front of you like this on your PCs screen .

 

 

Step – 8 : Now click on the icon of close button to get back to the options window .

 

Now , click on the OK button which is present at the bottom right side of the screen .

 

Step – 9 : Now , you have to select on the all of these below mentioned fields which are present at the right side of the excel sheet .

 

* Sales person

* Region

* account

* order amount

* Sub total

 

 

Step – 10 : After , selecting all of these fields your worksheet will look like this as shown in below image .

 

 

* You should note that your data in above image has been grouped by customer company name , product name , unit price , sum of quantities and the sum of the subtotals .

 

* Notice that the drop down button which is next to the rows label , this button allows you to sort or filter your data .

 

– Just assume that you are only interested in the field Alfreds Futterkiste , then click on the row label drop down list as it is shown in the below provided image .

 

 

* Now , remove the tick from the front of select all option .

* Next , select the option of Alfreds Futterkiste .

* Now , click on the OK button .

* You will be going to get a new window with the following data as shown below in image .

 

 

These were the steps of creating a Pivot table . Now we will be going to know that how can we create a two – dimensional table .

 

How to create 2 – Dimensional pivot table

 

A two – dimensional pivot table is a table which has fields on both rows and columns . Let’s have an example we wanted to list the employee name in rows and wanted to use columns for representing customer names and fill in the cells with the total number of sales .

 

Step – 1 : First of all activate the sales data sheet .

 

Step – 2 : Now , you have to click on the insert tab .

 

Step – 3 : Then in the insert tab you will see an option of Pivot chart and table button .

 

Step – 4 : Next , select all of the data . Excel will now remember the previous range so you just have to click on the OK button no need to do anything else .

 

Step – 5 : After you click OK a new sheet will get created in front of you with pivot table tools .

 

Step – 6 : Next , you have to select the field in the manner as shown in below image .

 

 

Step – 7 : After completing all of these above steps a new picture table will be created and will look like as shown in below image .

 

Frequently Asked Questions ( FAQ’s of Pivot Table Excel)

 

Ques – 1 : In my table it is showing an error that Pivot table field name is not valid . What should I do ?

 

Ans : Sometimes , this error can be faced by some of the users but you can solve it by two methods –

 

Method – 1 : By unhiding the columns of pivot table :

 

You can solve this error by unhiding the columns of your Pivot table . For unhiding the column you can follow the below steps –

 

Step – 1 : First of all click on the Select All option which will be at the left side of the cell A1 .

 

 

Step – 2 : Now , right click on any of the column heading and select the unhide option .

 

Step – 3 : Now , try your pivot table again your error will be now get resolved .

 

Method – 2 : By deleting the empty columns :

 

If the first method does not work then you can try this next method . It takes a little longer time and requires all of your columns to be visible . It seems that the particular spreadsheet had a lot of columns which have header but does not contain any of the value in any of cell . If there are cells which are empty then hide them or delete them after that your problem will get solved .

 

Ques – 2 : How can I display `the text in the data area of a pivot table ?

 

Ans : You are not able to display the text data in the data area of the pivot table . You can display the text fields in the rows and column areas and can show a count of the records in the data area .

 

Ques – 3 : I am trying to group the dress by months and I get an error which says cannot group this selection . What should I do ?

 

Ans : If the fields contain blank spaces , cells or contain cells with text then you might get this error first of all you need to remove or hide such type of cells for clearing the error .

 

Ques – 4 : When I refresh the pivot table , how can I preserve my formatting like column widths ?

 

Ans : To preserve them follow the below mentioned steps :

 

Step – 1 : First of all right click in a cell of the pivot table and choose the option of Pivot table .

 

Step – 2 : Now , you have you ensure that your preserved formatting is turn on and auto format is turned off . After it click on ok .

 

Fir formatting a cell the option of enable should be turned on –

 

Step – 1 : On the Excel ribbon click on the option of analyze tab .

 

Step – 2 : Next , in the action group you have to click on the select .

 

Step – 3 : If this option is not already activated then click on enable to activate it .

 

Ques – 5 : How can I make the two data field to be appear side by side instead of stacked in a separate row ?

 

Ans : After you add the two fields in the pivot table’s data area , you should drag a data field button to the right onto the cell which contains the word ” Total ” .

 

Ques – 6 : I have added the new rows and columns to my pivot table source data , but they are not appearing when I refresh the pivot table data ?

 

Ans : If the new column areas outside the pivot tables data range then they won’t be going to show up in the field list . Similarly new rows that are added to the bottom of the existing data which might not automatically get appear when you refresh the pivot table . For this problem follow the below mentioned steps –

 

1 . Right click on the cell in the pivot table .

2 . Now , choose the pivot table wizard option .

3 . Click the back button and then select the new range .

4 . Now click on the finish option .

Your problem will be solved out . Hope this entire article was useful to you and you got all your answers related to Pivot table. Also, you would be having some doubts over pivot table in excel, fee free to ask us in the comment section below. Thanks