how do i create a pivot table

We will learn what is an Excel Pivot Table, why it is useful, and how to create a Pivot Table in Excel with the help of a step-by-step example:

Microsoft Excel has several great features that allow us to organize and analyze the data. A pivot table is yet another excellent feature that helps us manage a large set of records.

In this tutorial, you will get to know the principles of Pivot Table along with important topics like PivotTable Fields, Sorting and Value field settings, etc. You will also understand how to analyze a large set of data and learn how to refresh, update, and delete an already existing table.

What You Will Learn:

• Excel Pivot Table
  • Why Is Pivot Table Useful
  • Preparing Data For Pivot Table
  • Creating A Pivot Table
  • PivotTable Field List
  • Sorting
    • Sorting By Fields
    • Sorting The Grand Total
    • More Sort Options
    • More Option Under Columns
    • Manual Sort
    • Sort By Value Using Data Tab
  • Value Field Settings
    • How To Access Value Field Settings
    • Value Field Settings Options
  • Refresh A Pivot Table
    • Manual Refresh
    • Refresh Table When Opening Worksheet
  • Update A PivotTable
  • Delete A Pivot Table
  • FAQs
• Conclusion
  • Recommended Reading

Excel Pivot Table

Pivot Table is a data analysis tool that is particularly useful while dealing with extremely large data. It allows us to reorganize the data in a way that you wish in just a matter of clicks.

In short, this table gives you the ability to summarize your records easily and more effectively.

List of Tutorials in this Excel Series:

Tutorial #1: What Is An Excel Pivot Table And How To Create One [This Tutorial]
Tutorial #2: Pivot Table Grouping, Ungrouping And Conditional Formatting
Tutorial #3: Excel Slicer And Timeline – Tutorial With Examples
Tutorial #4: What Is A Pivot Chart In Excel And How To Make It
Tutorial #5: VLOOKUP Tutorial – How To Use VLOOKUP Function In Excel

---

Why Is Pivot Table Useful

These tables are proven to be very helpful during business presentations that deal with huge data as they don't demand you to build complex formulas. It has many advantages as listed below.

1. It lets you summarize your data and explore the trends and figures based on the criteria provided by you.
2. It's a very flexible table that allows us to transform rows into columns and vice versa.
3. It lets us sort, reorganize, and perform basic arithmetic operations like count, add, average on the data in the table.
4. Grouping of data in various ways to help us achieve the desired table.

Preparing Data For Pivot Table

Before we move into creating a pivot table, it is very important to have the Excel data in a proper format. There are certain principles to be followed to design a pivot table accurately to avoid any sort of errors.

1. All the columns must have a unique header name.
2. There should be no blank rows and blank columns in the excel data. Make sure to remove any blank rows and columns before creating an Excel pivot table.
3. It is recommended to remove any duplicate entries and also to remove any filters applied for the excel data.

Given below is the sample datasheet.

Creating A Pivot Table

Follow these easy steps to create the table.

#1) Click anywhere on the data table you just created.

#2) Go to Insert -> PivotTable

#3) Create PivotTable dialog will appear as shown below.

The first section will give you the details about the table that y ou want to analyze.

• Select a table or range: By default, it will select the table which has selected and hit on the PivotTable option.
• Use an external data source: If you have the data that is stored outside Excel-like SQL Server DB then you can use this option and select the appropriate connection category that contains the required data.

The second section provides us an ability to select where the Pivot table has to be created.

• New Worksheet: Generates the table in a new worksheet created before the existing worksheet at cell A1.
• Existing Worksheet: Generates the table in the active worksheet, at the location mentioned in the Location text box.

#4) Click Ok to create a PivotTable at the desired location.

PivotTable Field List

Once you create this table, a Field List will be displayed to the right. You can decide the design by selecting and arranging its fields as you wish.

The Field List appears as you click anywhere inside the Table. If it doesn't open, then click on the PivotTable, click Analyze -> Field List on the ribbon.

There are 2 sections in the Field List:

1. Field section: This shows the list of all the column headers and you can pick the fields you wish to show in the PivotTable.
2. Areas section: This allows us to arrange the selected fields in the way you want to present them in the Pivot Table by dragging them between the four areas listed below.

• • Filters: Fields under this give us the ability to add report filters shown at the top of the table.
  • Columns: Fields under this are shown as Column headers at the top of the Pivot Table.
  • Rows: Fields under Rows are the Row Labels displayed on the left side of the Pivot Table.
  • Values: Mainly used to show the summarized numeric values.

You can even add multiple fields in the same area like Region and City under Rows and you can check the resulting Pivot Table as shown in the diagram above if you want to remove a field from the table, just drag the field outside that areas section.

If you notice the above picture, each region has a expand/collapse button, if you want to remove that button, go to Analyze -> +/- Button.

Similarly, if you don't want Row Labels and Column Labels displayed on the table, click on Analyze -> Field Header

The resulting table will be as shown below.

Note: Usually non-numeric fields are added to the Rows, and numeric fields are added under the Values.

Click the link to download the Sample Data File Sample Data – What Is Pivot Table.

Sorting

Sorting is necessary when you are designing or examining a table to bring the value first or just to make a sense of order. You can sort the text entries alphabetically (A to Z and Z to A) Numbers (Ascending or descending order) and Date (Oldest to Newest or Vice versa)

Consider the below data source for designing the examples of the Sort feature.

I have created a PivotTable using Region and City as Rows and Order date as the column.

The resulting table is shown below.

Sorting By Fields

In the PivotTable you can see a small arrow next to Row & Column, if you have multiple fields under Rows or Columns, then all the fields will be shown under the sort option.

You can select the appropriate field and click on Sort A to Z or Sort Z to A. Let's select the City field and Sort Z to A.

The resulting table is shown below.

As we choose City and Sort Z to A, you can see that only the cities are sorting from Z to A and not the Region.

Similarly, you can sort the column headers too. As we only have one field under the column there won't be any section to choose the field. We are sorting the dates, thus the sorting order will be Oldest to Newest and vice versa.

Let's select Newest to Oldest and it should result in showing Sep to March.

Sorting The Grand Total

As you can see, there is no arrow provided to sort the grand total column. But you can still sort the total by following the below order.

#1) Right-click on the Total value cell for any of the Region and the context menu will be displayed.

#2) As we are sorting number values, the sorting options are displayed as smallest to largest and vice versa. Navigate to sort and choose from the options of ascending or descending.

Suppose you only want to sort the values of the city and not the region i.e. the Region sorting should be largest to smallest but the values for the city should be sorted in ascending order. To achieve this, click on the total value cell for any of the cities, right-click, and sort as shown above.

The result below shows that the regions are sorted in descending order but cities are sorted in ascending order.

More Sort Options

There is an additional option to include both row and column during sorting. Suppose you want to include just one column value during sorting. For example, if we want to sort the Regions in descending order of Total Sum in March.

#1) Click on the arrow next to Row Label and select More Sort Options.

#2) In the Sort(Region) dialog you can see many different options. For this example, we need to sort the Region based on the sum of the Total Price in descending order. So let's go ahead and select Sum of Total Price under descending (Z to A) and click on More Options.

#3) As we want to sort for March, let's select the "Values in selected column" and enter the cell number of the Total sum of March i.e. H3.

#4) Click Ok and you can see that the Region is sorted in descending order only based on the column values in March in descending order.

More Option Under Columns

Let's understand More Sort Options for Column.

#1) Click on the small arrow in the Column Label and select More sort options.

#2) Select Ascending (A to Z) by drop-down and select Sum of Total Price and click on More Options.

#3) Deselect AutoSort and under the First key sort order dropdown select Jan, Feb, Mar, and Sort By Grand Total.

#4) Click Ok

The resulting table will be as shown below.

All the columns are sorted in ascending order based on the grand total.

Manual Sort

You can manually sort the table by dragging the items. Hover on the item's border until you see the four-pointed arrow and drag. You will see a green line for the row/column you want to move.

Now you can drop it to any position you wish, notice in the result below that we have placed the Jun column at the beginning.

Sort By Value Using Data Tab

Alternatively, you can also go to the Data Tab on the Toolbar, select any field you wish and click on Sort.

As an example , let's sort Grand Total for Region as Largest to Smallest value and city as smallest to largest.

1. Click on the grand total value for any of the cities. For example, New York
2. Go to Data Tab and select smallest to largest under Sort.
3. Click on the grand total value for any of the regions. For example, North.
4. Go to Data Tab and select Largest to Smallest under Sort.

Value Field Settings

We have learned that the Pivot table summarises a large set of data. But sometimes the default values created by the table is not really the results that we are looking for.

For example, by default, it gives us the sum of values but if we need average, maximum, minimum values, then the Pivot table provides us with value field setting options to get the desired results.

Let's take an example with a simple table as shown below.

Let's create a simple Pivot table with Region and Months under Area Rows and Total Price under Values.

How To Access Value Field Settings

There are different ways to open the Value Field settings as shown below.

#1) From Pivot Table Cell

Right-click on any of the cells with value in the pivot table. Select Value Field Setting in the context menu.

#2) From Pivot Table Field

Another way to access the value field is from the Pivot Table field, click on the small arrow in Values under the areas.

#3) From Analyze Tab

Click on any value field and go to Analyze -> Field Settings.

Value Field Settings Options

Once you click on Value Field Settings, you will get a dialog as shown below.

You can choose any type of calculation you wish like average, count, max, etc and that will be presented on the Pivot table. You can also change the header displayed under the Custom Name.

For this example , we will change the header name to Average Value and select Average in the list box, and click ok.

The result is shown below.

Suppose you wish to get Sum, count, Average values in a single Pivot table, then you can drag multiple Total prices under the Values area and change their Value Field Settings.

Now notice that the average value is showing a long decimal point.

If you want to truncate the decimal point to just 2 digits then follow the below steps.

#1) Open the Value Field Settings for Average Value.

#2) Click on Number Format.

#3) Select Number

#4) Select 2 under Decimal places and if you don't wish a decimal number, then make it 0.

#5) Click ok

Now if you want to add any currency symbol to Total Sum then:

1. Open Value Field Settings for Total Sum.
2. Click on Number Format in the dialog opened.
3. Select Currency
4. Select the currency Symbol you wish. For example, ($ or Rs)
5. Click ok

Refresh A Pivot Table

These tables do not refresh automatically. When you change the content on the data source like update values, the Pivot Table doesn't reflect the latest information automatically. Hence you need to refresh it manually or you can choose to refresh it whenever the workbook is opened.

Manual Refresh

Whenever you change the values in the data source, click anywhere on the pivot table and Right-click, and select Refresh. You can also use the Alt+F5 shortcut key.

If you have multiple PivotTable and you want to refresh all of them at once, then click anywhere in the Pivot Table to show the PivotTable Tools on the ribbon. Click Analyze -> Refresh All.

Refresh Table When Opening Worksheet

You can make the Pivot Table refresh every time you open the Excel sheet. Right-Click on the table and select PivotTable Options and a Dialog will appear.

Click on the Data tab on the dialog and select Refresh Data while opening the file.

Note:

1. If refresh is taking a long time then, you can check the status by clicking on Analyze -> Refresh arrow -> Refresh Status.
2. You can also stop refreshing by clicking on Cancel Refresh present under Analyze -> Refresh arrow.

Update A PivotTable

After we have created a Pivot Table, the range of the data source may change, and we might even add more rows into the data source. Now if you go ahead and refresh the PivotTable, those values do not reflect in the table.

The reason is while creating the PivotTable you have specified a data range and it will always look for the changes made only in those rows and columns.

Follow the below steps to include new rows into the table.

#1) Click anywhere on the PivotTable.

#2) Go to Analyze -> Change Data Source.

#3) Change PivotTable Data Source dialog will appear and there you can enter the new data range.

#4) Click ok

The Pivot Table will update to reflect the new rows that are added to the data source.

Note: If you have an external data source then choose to use an external data source from the Change PivotTable Data Source dialog and select your connection server.

Delete A Pivot Table

When you no longer need the PivotTable, you can easily delete it. There are different ways of deleting the same as shown below.

#1) Delete the Entire Table:

Follow the below steps, to delete the entire table:

• Click anywhere on the PivotTable
• Go to Analyze -> Select -> Entire Table

• Hit Delete on the keyboard.

#2) Delete the values in the PivotTable:

To achieve this follow the below-mentioned steps:

• Click anywhere on the PivotTable.
• Go to Analyze ->Select ->Entire Table.
• Click on Clear All and this will keep the table but delete all the values.

#3) Delete a Sheet:

Suppose you have the PivotTable on a separate sheet and the sheet has no other data and you would like to save then, deleting the entire sheet is the fastest way to remove the PivotTable.

FAQs

Q #1) How can I create a Pivot table in Excel?

Answer: Select any cell in the data source from which you want to create a PivotTable. Insert -> PivotTable

Select a range and click ok. You can build the table by selecting the appropriate fields.

Q #2) What is the recommended Pivot Table?

Answer: Excel willgenerate Pivot Tables automatically. Recommended Pivot Tables give you a template table based on the data source you selected. You can select the appropriate table that matches your requirement.

Q #3) Why do we use a Pivot Table?

Answer: Pivot table can quickly and automatically aggregate, calculate sums, and find the maximum or minimum, etc. for a huge business-critical data. It also allows you to sort, and group the data. It is a very flexible table that allows us to transform rows into columns and vice versa.

Q #4) What is the Pivot Table and how do you use it?

Answer: A pivot table gives the summarised statistics of an organized large set of data. It is a data analysis tool with many user-friendly features.

Excel allows you to use the data source present in the excel or any external files and build the Pivot table from the Insert -> PivotTable option. You can then build your desired table using fields, sort, group, settings, etc. feature available in the PivotTable Analyse ribbon.

Conclusion

In this tutorial, we explored the Excel Pivot Table along with its advantages and learned how to use it. We learned important concepts like PivotTable fields, Sorting, Value Field Settings.

All the related concepts like Refreshing, Changing data source, and deleting the Pivot Tables, etc. were also discussed in the tutorial.

how do i create a pivot table

Source: https://www.softwaretestinghelp.com/excel-pivot-table-tutorial/

Posted by: johnsonwhowerromed56.blogspot.com

Share this post