assignment for pivot table

Practice And Learn Excel Online For Free

Pivot table.

• Post published: November 9, 2019

Pivot Table is a great tool for summarizing and analyzing data in Excel.

We can use a Pivot Table to perform calculations on our data based on certain criteria. For example – Sales per Store, Sales per Year, Average Discount per Region, and many more…

Here are some of the advantages of using Pivot Tables:

• There is no need to use formulas in order to perform calculations.
• We can perform complex calculations in a quick and simple way.
• The summarized data is dynamic and can easily be applied to different sets of data

Click to download our absolutely FREE Pivot Table Exercise 

Table of Contents

Creating a Pivot Table

To create a new Pivot Table, we first need to select the data range which we would like to analyze, then click on one of the desired cells in our data range, then click Insert tab, then Pivot Table.

Let’s assume we want to analyze a database of cars sold by a car vendor:

To create a new Pivot Table:

• We will click on one of the cells in the data range.
• We will go to the Insert tab and click on Pivot Table:

• Next, we will confirm that the selected range is indeed the right range.

• Last, we will select “New Worksheet” to create the Pivot Table in a new worksheet, or “Exisiting Worksheet”, to place it in an existing worksheet.

After we decided to create a Pivot Table, we can see all the column headers – these are the fields from our database which we can work with:

To start creating our Pivot Table, we can drag the different fields to the following areas:

• Rows – Here we will choose the field/s which we would like to base our Pivot Table rows upon.
• Columns – Here we will choose the field/s which we would like to base our Pivot Table columns upon.
• Filters – Here we will choose the field/s by which we would like to filter our data in the Pivot Table. i.e.- we would choose “Year” to filter by a specific year.
• Values – Here we will choose the field we want Excel to calculate and our desired calculation.

Creating a basic Pivot Table – Example

One of the most basic examples of using a Pivot Table is summing values of a specific field based on a criteria that appears in a different field.

In order to do so, we will drag the field which we would like to analyze into the “Rows” area or “Columns” if we would like to present the analysis in columns. We will the drag the field we want to sum into the “Values” area:

Changing the way Values are calculated

We will notice that most times, the basic calculation we will get when dragging a field to the “Values” area will be “Sum”.

We can change the calculation by clicking the field after we dragged it into the “Values” area, then “Value Field Settings…”, which will open a menu where we can choose to sum, count, average and many more calculations:

Segmentation to Columns and Rows

We can segment the data using rows and columns simultaneously by dragging fields to the “Rows” and “Columns” areas:

Performing multiple calculations on the same field

We can perform a number of different calculations on the same field by dragging the field several times to the “Values” area and changing the type of calculation in each of the columns:

Segmentation of more than one field

In the Pivot Table, we can segment based on more than one field by dragging several fields into the “Rows” area:

Designing a Pivot Table

Changing the pivot table design to a classic table design.

In order to give the Pivot Table a “classic” look, where each field is presented in a different column, we will click the Pivot table, click on “design” and perform the following steps:

• Click on Report Layout
• Click on “Show in Tabular Form” to show the table in a classic format
• Click on “Repeat All Items Labels” to show all item labels.
• We can click on “Do Not Show Subtotals” to hide the subtotals in the newly created table.

This is the process and final result:

Formatting a Pivot Table field

We can quickly select the way we wish to format a certain value field, by right-clicking the field and then clicking on “Format Cells”, or directly on “Number Format”, if we wish to format the values as number and add 1000 separator (4,524,254 instead of 4524254):

Designing missing values and errors

Filtering a Pivot Table

Filtering existing fields in a pivot table.

We can filter data shown in the Pivot Table rows simply by clicking the corresponding button in the desired field. For example, to filter the “Gear” field, we simply have to click the button next to the field name:

Filtering values in a Pivot Table

What if we wanted to filter the values in our Pivot Table?

To do so, we can start our filtering by clicking the filter button in one of the fields, then click on “Value Filters”, following which we will be able to see the various value filtering options.

Here’s an example of how to filter values greater than 40,000:

Adding an external filter to a Pivot Table

If we want to filter based on a field that is not currently in the Pivot Table, we could drag that field into the “Filters” area:

Please note – we can add more than one field to the “Filters” area.

Sorting values in a Pivot Table

If we want to sort our fields, we just have to right-click on the desired field and click on “Sort”:

Updating and refreshing the Pivot Table data

After updating the source data, we have to refresh the Pivot Table in order for the new data to be reflected in the Pivot Table. We can do that by right-clicking the table and clicking on “Refresh” or by Refresh/Refresh all in the “Data” group

Adding new data at the end of the data range

If we want to add new data to our Pivot Table that will be added at the end of the previously used data range, we need to update the source data’s range by clicking on “Change Data Source” in the “Data” group:

Another way of dealing with this issue is by adding the new data in the middle of the previously used data range and then refreshing.

Automatically update Data Source Range when adding new rows by using Tables

Another way to save time if we are planning to update the data source range often is changing the data source range to a table by clicking in “Table” in the “Insert” tab or by clicking CTRL+T

Now we can create/update the Pivot Table that will use the table as the source data, and when the table will be updated- the Pivot Table’s source data range will be updated as well. Here’s how our Data Source looks like:

Show Values As

Presenting a breakdown of a value in a Pivot Table

Whenever we like, we can present all the items that are calculated in a certain cell in the Pivot Table by double-clicking that cell. This will result in a new sheet opening:

Grouping Data

We can group data presented in the Pivot Table’s rows and columns with “Group” and reverse it with “Ungroup” by right-clicking one of the cells:

Date data will usually be grouped automatically to months/years

We can also group numerical data (i.e 1-100, 101-200, etc.)

Creating Pivot Charts

We can add charts to existing Pivot Tables or create new charts based on a new Pivot Table.

• Existing Pivot Table – We will click on the “Analyze” tab and then on “Pivot Chart” in the “Tools”  group (we have to select a cell in the Pivot Table before doing this)
• Creating a new Pivot Table – “Insert” tab -> “Pivot Chart” in the “Charts” group (we have to select the desired source data before doing this)

When we click on the Pivot Chart, the names of the categories will look like this:

Like any other chart, we can control the axis’ directions and the chart type by clicking on the “Design” tab. We can, for example, replace the X and Y axis by “Switch Row/Column” in the “Design tab”. We can also change the Chart type:

It is important to note that Pivot Charts behave exactly as Pivot Tables, so each functionality that can be used in Pivot Tables, can also be used in Pivot Charts.

Adding Slicers / Timelines to a Pivot Table

Adding slicers to a pivot table.

• We can add Slicers to our Pivot Table / Chart, which will enable visually filtering the field, by clicking on the “Analyze” tab and then on “Insert Slicer”. Here’s how it looks:

• We can have multiple slicers to our Pivot Table, which will work simultaneously:

• We can select several values in the Slicer by using CTRL/ SHIFT.
• To cancel the filtering of a Slicer, we will click on this button at the top of the Slicer:

Adding a Timeline to a Pivot Table

For date fields, we can add a Timeline by clicking on the “Analyze” tab and then on “Insert Timeline”:

Pivot Table Calculated Fields

We can perform calculations within the Pivot Table itself, Instead of creating calculation columns in the source data. For that, we can use a “Calculated Field”.

A Calculated Field is calculated based on the sum of a certain field.

We will add a Calculated field by clicking on: Analyze tab -> Fields, Items & Sets  -> Insert Calculated Fields:

We will name each Calculated Field and write the desired formula for it (you can insert the desired field by double-clicking it).

Here’s an example of calculating the Sales amount after a 2% commission:

Practice Pivot Table

Click here to download our FREE Excel Pivot Table exercise , in which you will be able to practice and learn how to create Pivot Tables, design them, update their data, create Pivot Charts, adding Slicers and many more Pivot Table tips and tricks! 

Having an issue with the formulas' language? check out this post

• Yes, that would be helpful!
• I don't know
• Intermediate

Terms and Conditions - Privacy Policy

• Ablebits blog
• Pivot Table

How to use Pivot Tables in Excel - tutorial for beginners

In this tutorial you will learn what a PivotTable is, find a number of examples showing how to create and use Pivot Tables in all version of Excel 365 through Excel 2007.

If you are working with large data sets in Excel, Pivot Table comes in really handy as a quick way to make an interactive summary from many records. Among other things, it can automatically sort and filter different subsets of data, count totals, calculate average as well as create cross tabulations.

Another benefit of using Pivot Tables is that you can set up and change the structure of your summary table simply by dragging and dropping the source table's columns. This rotation or pivoting gave the feature its name.

What is a Pivot Table in Excel?

An Excel Pivot Table is a tool to explore and summarize large amounts of data, analyze related totals and present summary reports designed to:

• Present large amounts of data in a user-friendly way.
• Summarize data by categories and subcategories.
• Filter, group, sort and conditionally format different subsets of data so that you can focus on the most relevant information.
• Rotate rows to columns or columns to rows (which is called "pivoting") to view different summaries of the source data.
• Subtotal and aggregate numeric data in the spreadsheet.
• Expand or collapse the levels of data and drill down to see the details behind any total.
• Present concise and attractive online of your data or printed reports.

The screenshots above demonstrate just a few of many possible layouts. And the steps below show how you can quickly create your own Pivot Table in all versions of Excel.

Tip. The users of Excel 365 can also group and summarize data using a PIVOTBY formula . It's fully dynamic and updates in real time.

How to make a Pivot Table in Excel

Many people think that creating a Pivot Table is burdensome and time-consuming. But this is not true! Microsoft has been refining the technology for many years, and in the modern versions of Excel, the summary reports are user-friendly are incredibly fast. In fact, you can build your own summary table in just a couple of minutes. And here's how:

1. Organize your source data

Before creating a summary report, organize your data into rows and columns, and then convert your data range in to an Excel Table. To do this, select all of the data, go to the Insert tab and click Table .

Using an Excel Table for the source data gives you a very nice benefit - your data range becomes "dynamic". In this context, a dynamic range means that your table will automatically expand and shrink as you add or remove entries, so won't have to worry that your Pivot Table is missing the latest data.

Useful tips:

• Add unique, meaningful headings to your columns, they will turn into the field names later.
• Make sure your source table contains no blank rows or columns, and no subtotals.
• To make it easier to maintain your table, you can name your source table by switching to the Design tab and typing the name in the Table Name box the upper right corner of your worksheet.

2. Create a Pivot Table

This will open the Create PivotTable window. Make sure the correct table or range of cells is highlighted in the Table/Range field. Then choose the target location for your Excel Pivot Table:

• Selecting New Worksheet will place a table in a new worksheet starting at cell A1.

• In most cases, it makse sense to place a Pivot Table in a separate worksheet , this is especially recommended for beginners.

• It might be useful to create a Pivot Table and Pivot Chart at the same time. To do this, in Excel 2013 and higher, go to the Insert tab > Charts group, click the arrow below the PivotChart button, and then click PivotChart & PivotTable . In Excel 2010 and 2007, click the arrow below PivotTable , and then click PivotChart .

3. Arrange the layout of your Pivot Table report

The area where you work with the fields of your summary report is called PivotTable Field List . It is located in the right-hand part of the worksheet and divided into the header and body sections:

• The Field Section contains the names of the fields that you can add to your table. The filed names correspond to the column names of your source table.
• The Layout Section contains the Report Filter area, Column Labels, Row Labels area, and the Values area. Here you can arrange and re-arrange the fields of your table.

The changes that you make in the PivotTable Field List are immediately reflected to your table.

How to add a field to Pivot Table

By default, Microsoft Excel adds the fields to the Layout section in the following way:

• Non-numeric fields are added to the Row Labels area;
• Numeric fields are added to the Values area;
• Online Analytical Processing (OLAP) date and time hierarchies are added to the Column Labels area.

How to remove a field from a Pivot Table

To delete a certain field, you can either:

• Uncheck the box nest to the field's name in the Field section of the PivotTable pane.
• Right-click on the field in your Pivot Table, and then click " Remove Field_Name ".

How to arrange Pivot Table fields

You can arrange the fields in the Layout section in three ways:

4. Choose summary function for Values (optional)

By default, Microsoft Excel applies the Sum function to numeric value fields placed in the Values area. Conversely, for non-numeric data (such as text, dates, or Boolean values), the Count function is automatically applied.

However, you are free to choose a different summary function according to your preference. For this, right-click on the value field you wish to modify, select Summarize Values By , and then choose the desired function from the options provided.

The functions' names are mostly self-explanatory:

• Sum - calculates the sum of the values.
• Count - counts the number of non-empty values (works as the COUNTA function).
• Average - calculates the average of the values.
• Max - finds the largest value.
• Min - finds the smallest value.
• Product - calculates the product of the values.

5. Show different calculations in value fields (optional)

Excel Pivot Tables provide one more useful feature that enables you to present values in different ways, for example show totals as percentage or rank values from smallest to largest and vice versa. The full list of calculation options is available here .

Tip. The Show Values As feature may prove especially useful if you add the same field more than once and show, for example, total sales and sales as a percent of total at the same time. See an example of such a table.

Working with PivotTable Field List

The Pivot Table pane, which is formally called PivotTable Field List , is the main tool that you use to arrange your summary table exactly the way you want. To make your work with the fields more comfortable, you may want to customize the pane to your liking.

Changing the Field List view

Closing and opening the PivotTable pane

Closing the PivotTableField List is as easy as clicking the Close button (X) in the top right corner of the pane.Making it to show up again is not so obvious :)

Using Recommended PivotTables

As you have just seen, creating a Pivot Table in Excel is easy. However, the modern versions of Excel take even a step further and make it possible to automatically make a report most suited for your source data. All you have to do is 4 mouse clicks:

• Click any cell in your source range of cells or table.
• On the Insert tab, click Recommended PivotTables . Microsoft Excel will immediately display a few layouts, based on your data.
• In the Recommended PivotTables dialog box, click a layout to see its preview.

As you see in the screenshot above, Excel was able to suggest just a couple of basic layouts for my source data, which are far inferior to the Pivot Tables we created manually a moment ago. Of course, this is only my opinion and I am biased, you know : )

How to use Pivot Table in Excel

You can also access options and features that are available for a specific element by right-clicking on it.

How to design and improve Pivot Table

Once you have created a Pivot Table based on your source data, you may want to refine it further to make powerful data analysis.

To improve the table's design, head over to the Design tab where you will find plenty of pre-defined styles. To create your own style, click the More button in the PivotTable Styles gallery, and then click " New PivotTable Style...".

To customize the layout of a certain field, click on that field, then click the Field Settings button on the Analyze tab in Excel 2013 and higher ( Options tab in Excel 2010 and 2007). Alternatively, you can right click the field and choose Field Settings from the context menu.

How to get rid of "Row Labels" and "Column Labels" headings

When you are creating a Pivot Table, Excel applies the Compact layout by default. This layout displays " Row Labels " and " Column Labels " as the table headings. Agree, these aren't very meaningful headings, especially for novices.

How to refresh a Pivot Table in Excel

Although a Pivot Table report is connected to your source data, you might be surprised to know that Excel does not refresh it automatically. You can get any data updates by performing a refresh operation manually, or have it refresh automatically when you open the workbook.

Refresh the Pivot Table data manually

• Click anywhere in your table.

To refresh all Pivot Tables in your workbook, click the Refresh button arrow, and then click Refresh All.

Note. If the format of your Pivot Table gets changed after refreshing, make sure the " Autofit column width on update" and " Preserve cell formatting on update" options are selected. To check this, click the Analyze ( Options ) tab > PivotTable group > Options button. In the PivotTable Options dialog box, switch to the Layout & Format tab and you will find these check boxes there.

Refreshing a Pivot Table automatically when opening the workbook

• On the Analyze / Options tab, in the PivotTable group, click Options > Options .

How to move a Pivot Table to a new location

How to delete an Excel Pivot Table

If you no longer need a certain summary report, you can delete it in a number of ways.

• If your table resides in a separate worksheet , simply delete that sheet.
• If your table is located along with some other data on a sheet, select the entire Pivot Table using the mouse and press the Delete key.
• Click anywhere in the Pivot Table that you want to delete, go to the Analyze tab ( Options tab in Excel 2010 and earlier) > Actions group, click the little arrow below the Select button, choose Entire PivotTable , and then press Delete.

Note. If any PivotTable chart is associated with your table, deleting the Pivot Table will turn it into a standard chart that can no longer be pivoted or updated.

How to prevent pivot table columns from resizing on every change or refresh

By default, Excel pivot tables automatically resize their columns to fit the contents whenever there's a change or refresh. This includes almost every action like adding or removing fields, filtering with drop-down menus, slicers and timelines , or making layout adjustments. While this automatic resizing can be helpful in some situations, it becomes really annoying when the worksheet contains other data outside the pivot table.

Thankfully, there's a simple setting to disable the auto-fit columns feature:

• Right-click any cell inside the pivot table.
• Choose PivotTable Options... from the context menu.
• On the Layout & Format tab, unselect the Autofit column widths on update checkbox.
• Press OK to confirm the change.

Pivot Table examples

The screenshots below demonstrate a few possible Pivot Table layouts for the same source data that might help you to get started on the right path. Feel free to download them get a hands-on experience.

Pivot Table example 1: Two-dimensional table

• Rows: Product, Reseller
• Columns: Months
• Values: Sales

Pivot Table example 2: Three-dimensional table

• Filter: Month
• Rows: Reseller
• Columns: Product

Pivot Table example 3: One field is displayed twice - as total and % of total

• Values: SUM of Sales, % of Sales

Hopefully, this Pivot Table tutorial has been a good starting point for you. If you want to learn advanced features and capabilities of Excel Pivot Tables, check out the links below. And thank you for reading!

Available downloads:

You may also be interested in.

• How to remove Excel table formatting
• How to insert a hyperlink to another worksheet
• Data table in Excel - how to create and use

Table of contents

31 comments

I have many columns. After I create the Pivot Table, I want to apply Show Values As ---> % of Grand Total. But when I select all the columns, and then I apply Show Values As ---> % of Grand Total ... it only applies it to the first column !!! Why? To complete my task, I had to manually do this on each column separately, it took me a long time. How do I apply Show Values As to all columns at once?

Hi! Each column in the Pivot Table needs to be customized individually.

When viewing the data behind a pivot table value as a new worksheet is it possible to select the fields (columns) to be shown as in a power query? In other words, if the original dataset contains 50 columns how can I restrict the new worksheet to show only 5 selected columns of data rather than all 50? This is to avoid having to manually delete 45 columns of unwanted data from the new worksheet.

Hi! If I understand the problem correctly, you can only select the columns you want in PivotTable Field List.

Reviewing the above a few times, it appears this seminar does NOT assist the user as far as INSERTING a row into an existing PivotTable. I created a PivotTable on 01/25/23 and on 2/5/23 needed add a row to the existing table. Did I miss "Insert Row / Columns" section in the above seminar????

Hi! What you can use the pivot table for, is described in detail in the article above. But if you don't need it for your purposes, you don't have to use it. Using the PivotTable Field List, you can add and remove rows from the pivot table.

Spent the entire weekend reviewing your Excel PivotTable seminar(s). Have two questions I can't find answers to. 1) How to add a row to an existing Excel Table (Excel 2007 and 2010 and 2013 and Higher) 2) I have a Excel worksheet with 10 tabs each maintaining detailed schedule. The Summary tab (First tab) contains a Summary Table Hyperlinking total / detail from the other tabs. As I update the low tab, the maintain is updated automatically. Given this fact, why should I create and maintain a Excel PivotTable?? What exactly is the benefit(s) of the PivotTable over the design Hyperlink Master Table?

Basically WHY TAKE TIME TO CREATE A PIVOTTABLE?

In my opinion, the main benefit is that a pivot table allows you to quickly summarize huge amounts of data by categories and subcategories, making it a lot easier to analyze large datasets.

I've looked for a long time for a specific solution to my problem but haven't found one, so here it is:

For those who have been struggling to locate the source of the pivot table in excel when the source is a name, not a cell area.

-> 1) You need to unhide most of the sheets in your file.

2) Go to the drop-down list right above cell A1 in the sheet where your pivot table is located.

3) Click on the name of the source.

4) If it doesn't locate the source right away, continue to unhide sheets. repeat the process listed above.

5) Thank me later.

Pivot table information is very weldon. Clearly we are clarified, So thank you very much your valuable information.

Interested to know more about usage of Pivot Tables in Excel.

Sir in a pivot table can we have present month and cumulative month calculation in one column down by down i.e., A2 has month and A3 cumulative month . Please reply Sir

After delete row from the source data in pivot table, when I refresh in pivot report, I find that source data link is not working, please suggest how I solve this problem.

The below content was very useful for me.

"Another solution is to go to the Analyze (Options) tab, click the Options button, switch to the Display tab and uncheck the "Display Field Captions and Filter Dropdowns" box. However, this will remove all field captions as well as filter dropdowns in your pivot table."

Thankyou very much.

Regards Naresh Prajapati

to expertise in excel

Thank you very much Svetlana Cheusheva and ablebits.com. your posts about excel is very helpful, clean, clear and comprehensive. when I nee to look something about excel I just point my browser to ablebits.com, keep it up. Even Microsoft itself could not give support like yours.

What does it mean by filter, value, column, and row and what do you put in these areas.

I need to put the count of row and sum of person for same data in single pivot chart..... please suggest how can apply this in single pivot for showing two different different count in single pivot. please help me to resolve the same ASAP.

Warm Regards, Yogesh Tandon

Could you help me find a solution for formatting a pivot chart? I did a dash board that contain one chart with primary and secondary axis, and this chart it's attached to a slicer. The problem is: Every time a choose a blank series in the slicer, the chart looses the secondary axies configuration.

Can you Help me? Sorry if it does not sound clear.

To prevent automatic adjustment of the chart's size based on slicer selections, fix the axis bounds (i.e. the starting and ending points of the axes). Please see: How to change the axis scale in Excel chart .

Hi I want to know the short key return pivot table to excel page. If possible please let me know. Thanks.

How to draw pivot tables from 3 different workbooks.Pl. help me.

Great instructions thank you, really clear and easy to understand. Having now got my pivot tables working i would like to use one of the pivot table columns as the source data for a separate drop down menu. Getting the drop down to use the cells as the source is simple but when the pivot table updates and the number of rows changes the drop down does not dynamically update to match so you either end up with blank drop down options or not all options available. Can you help?

I have a list of data having as many 10 rows and two columns. This may be termed as a reference table. Now I have a task by datas used in first table I have to update a very large table containing 25000 and more rows . It took too much time for me to do the task. Please suggest and efficient way to do the same

Perfect exmaple. I have been using this site from quite a long time and i have learned a lot.....

Im trying pivot table but (Range&source) create a problem how to fix this problem please help me

As usual, _great_ instructions. I've been out of the loop a bit with Excel, and they've really added some powerful and cool features to the product.

One minor typo in the instructions - it should be 'Insert' instead of 'Inset' in the line below. To do this, select all of the data, go to the {Inset} tab and click Table.

Anyway, impressive work, thanks very much!!

Thank you, Kupci,

Hi- I am trying to create a pivot for survey responses.... the answers to one of the questions is actual text responses such as "Excellent, Good, ect." Is there an easy way to sort these responses in a pivot? The remaining questions on the survey are numeric responses ranging 1-5, those are working great. It's the text one I am struggling with. Thanks, Deb

Hello, Deb,

To be able to assist you better, we need to see your data. You can send us a small sample table to [email protected] .

Wow. What a wonderful explanation. Thank you very much.

Post a comment

How to Make a Pivot Table in Excel (Easy Step-by-Step)

If you are reading this tutorial, there is a big chance you have heard of (or even used) the Excel Pivot Table. It’s one of the most powerful features in Excel (no kidding).

The best part about using a Pivot Table is that even if you don’t know anything in  Excel, you can still do pretty awesome things with it with a very basic understanding of it.

This Tutorial Covers:

Let’s get started.

Click here to download the sample data and follow along.

What is a Pivot Table and Why Should You Care?

A Pivot Table is a tool in Microsoft Excel that allows you to quickly summarize huge datasets (with a few clicks).

Even if you’re absolutely new to the world of Excel, you can easily use a Pivot Table. It’s as easy as dragging and dropping rows/columns headers to create reports.

Suppose you have a dataset as shown below:

This is sales data that consists of ~1000 rows.

It has the sales data by region, retailer type, and customer.

Now your boss may want to know a few things from this data:

• What were the total sales in the South region in 2016?
• What are the top five retailers by sales?
• How did The Home Depot’s performance compare against other retailers in the South?

You can go ahead and use Excel functions to give you the answers to these questions, but what if suddenly your boss comes up with a list of five more questions.

You’ll have to go back to the data and create new formulas every time there is a change.

This is where Excel Pivot Tables comes in really handy.

Within seconds, a Pivot Table will answer all these questions (as you’ll learn below).

But the real benefit is that it can accommodate your finicky data-driven boss by answering his questions immediately.

It’s so simple, you may as well take a few minutes and show your boss how to do it himself.

Hopefully, now you have an idea of why Pivot Tables are so awesome. Let’s go ahead and create a Pivot Table using the data set (shown above).

Inserting a Pivot Table in Excel

Here are the steps to create a pivot table using the data shown above:

• Click anywhere in the dataset.

• Table/Range: It’s filled in by default based on your data set. If your data has no blank rows/columns, Excel would automatically identify the correct range. You can manually change this if needed.

As soon as you click OK, a new worksheet is created with the Pivot Table in it.

While the Pivot Table has been created, you’d see no data in it. All you’d see is the Pivot Table name and a single line instruction on the left, and Pivot Table Fields on the right.

Now before we jump into analyzing data using this Pivot Table, let’s understand what are the nuts and bolts that make an Excel Pivot Table.

The Nuts & Bolts of an Excel Pivot Table

To use a Pivot Table efficiently, it’s important to know the components that create a pivot table.

In this section, you’ll learn about:

Pivot Cache

Values area, columns area, filters area.

As soon as you create a Pivot Table using the data, something happens in the backend. Excel takes a snapshot of the data and stores it in its memory. This snapshot is called the Pivot Cache.

When you create different views using a Pivot Table, Excel does not go back to the data source, rather it uses the Pivot Cache to quickly analyze the data and give you the summary/results.

The reason a pivot cache gets generated is to optimize the pivot table functioning. Even when you have thousands of rows of data, a pivot table is super fast in summarizing the data. You can drag and drop items in the rows/columns/values/filters boxes and it will instantly update the results.

Note: One downside of pivot cache is that it increases the size of your workbook. Since it’s a replica of the source data, when you create a pivot table, a copy of that data gets stored in the Pivot Cache.

The Values Area is what holds the calculations/values.

Based on the data set shown at the beginning of the tutorial, if you quickly want to calculate total sales by region in each month, you can get a pivot table as shown below (we’ll see how to create this later in the tutorial).

The area highlighted in orange is the Values Area.

In this Pivot Table example, it has the total sales in each month for the four regions.

The headings to the left of the Values area makes the Rows area.

The headings at the top of the Values area makes the Columns area.

In the example below, Columns area contains the months (highlighted in red):

Filters area is an optional filter that you can use to further drill down in the data set.

For example, if you only want to see the sales for Multiline retailers, you can select that option from the drop down (highlighted in the image below), and the Pivot Table would update with the data for Multiline retailers only.

Analyzing Data Using the Pivot Table

Now, let’s try and answer the questions by using the Pivot Table we have created.

To analyze data using a Pivot Table, you need to decide how you want the data summary to look in the final result. For example, you may want all the regions in the left and the total sales right next to it. Once you have this clarity in mind, you can simply drag and drop the relevant fields in the Pivot Table.

In the Pivot Tabe Fields section, you have the fields and the areas (as highlighted below):

The Fields are created based on the backend data used for the Pivot Table. The Areas section is where you place the fields, and according to where a field goes, your data is updated in the Pivot Table.

It’s a simple drag and drop mechanism, where you can simply drag a field and put it in one of the four areas. As soon as you do this, it will appear in the Pivot Table in the worksheet.

Now let’s try and answer the questions your manager had using this Pivot Table.

Q1: What were the total sales in the South region?

Drag the Region field in the Rows area and the Revenue field in the Values area. It would automatically update the Pivot Table in the worksheet.

Note that as soon as you drop the Revenue field in the Values area, it becomes Sum of Revenue. By default, Excel sums all the values for a given region and shows the total. If you want, you can change this to Count, Average, or other statistics metrics. In this case, the sum is what we needed.

The answer to this question would be 21225800.

Q2 What are the top five retailers by sales?

Drag the Customer field in the Row area and Revenue field in the values area. In case, there are any other fields in the area section and you want to remove it, simply select it and drag it out of it.

You’ll get a Pivot Table as shown below:

Note that by default, the items (in this case the customers) are sorted in an alphabetical order.

To get the top five retailers, you can simply sort this list and use the top five customer names. To do this:

This will give you a sorted list based on total sales.

Q3: How did The Home Depot’s performance compare against other retailers in the South?

You can do a lot of analysis for this question, but here let’s just try and compare the sales.

Drag the Region Field in the Rows area. Now drag the Customer field in the Rows area below the Region field. When you do this, Excel would understand that you want to categorize your data first by region and then by customers within the regions. You’ll have something as shown below:

Now drag the Revenue field in the Values area and you’ll have the sales for each customer (as well as the overall region).

You can sort the retailers based on the sales figures by following the below steps:

• Right-click on a cell that has the sales value for any retailer.
• Go to Sort –> Sort Largest to Smallest.

This would instantly sort all the retailers by the sales value.

Now you can quickly scan through the South region and identify that The Home Depot sales were 3004600 and it did better than four retailers in the South region.

Now there are more than one ways to skin the cat. You can also put the Region in the Filter area and then only select the South Region.

Click here to download the sample data.

I hope this tutorial gives you a basic overview of Excel Pivot Tables and helps you in getting started with it.

Here are some more Pivot Table Tutorials you may like:

• Preparing Source Data For Pivot Table .
• How to Apply Conditional Formatting in a Pivot Table in Excel .
• How to Group Dates in Pivot Tables in Excel.
• How to Group Numbers in Pivot Table in Excel .
• How to Filter Data in a Pivot Table in Excel.
• Using Slicers in Excel Pivot Table.
• How to Replace Blank Cells with Zeros in Excel Pivot Tables.
• How to Add and Use an Excel Pivot Table Calculated Fields .
• How to Refresh Pivot Table in Excel .

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

2 thoughts on “How to Make a Pivot Table in Excel (Easy Step-by-Step)”

I am not getting the excel tips free ebook

Just wanna say, thank you for sharing this it will help us, especially me as a beginner in this field.

Leave a Comment Cancel reply

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

FREE EXCEL E-BOOK

Excel Pivot Tables

Pivot tables are the fastest and easiest way to quickly analyze data in Excel. This article is an introduction to Pivot Tables and their benefits, and a step-by-step guide with sample data.

Quick Links

Pivot tables are one of the most powerful and useful features in Excel. With very little effort, you can use a pivot table to build good-looking reports for large data sets. If you need to be convinced that Pivot Tables are worth your time, watch this short video . 

Grab the sample data and give it a try. Learning Pivot Tables is a skill that will pay you back again and again. Pivot tables can dramatically increase your efficiency in Excel.

What is a pivot table?

You can think of a pivot table as a report. However, unlike a static report, a pivot table provides an interactive view of your data . With very little effort (and no formulas) you can look at the same data from many different perspectives . You can group data into categories, break down data into years and months, filter data to include or exclude categories, and even build charts.

The beauty of pivot tables is they allow you to interactively explore your data in different ways.

Sample data

• Insert Pivot table
• Sort by value

Refresh data

Second value field.

• Apply number formatting

Group by date

• Add percent of total
• Benefits summary
• More resources

Step-by-step tutorial

To understand pivot tables, you need to work with them yourself. In this section, we'll build several pivot tables step-by-step from a set of sample data. With experience, the pivot tables below can be built in about 5 minutes.

The sample data  contains 452 records with 5 fields of information: Date, Color, Units, Sales, and Region. This data is perfect for a pivot table.

Data in a proper Excel Table named "Table1". Excel Tables are a great way to build pivot tables , because they automatically adjust as data is added or removed. 

Note: I know this data is very generic. But generic data is good for understanding pivot tables – you don't want to get tripped up on a detail when learning the fun parts.

Insert Pivot Table

1. To start off, select any cell in the data and click Pivot Table on the Insert tab of the ribbon:

Excel will display the Create Pivot Table window. Notice the data range is already filled in. The default location for a new pivot table is New Worksheet.

2. Override the default location and enter H4 to place the pivot table on the current worksheet:

3. Click OK, and Excel builds an empty pivot table starting in cell H4.

Note: there are good reasons to place a pivot table on a different worksheet. However, when learning pivot tables, it's helpful to see both the source data and the pivot table at the same time.

Excel also displays the PivotTable Fields pane, which is empty at this point. Note all five fields are listed, but unused:

To build a pivot table, drag fields into one of the Columns, Rows, or Values area. The Filters area is used to apply global filters to a pivot table.

Note: the pivot table fields pane shows how fields were used to create a pivot table. Learning to "read" the fields pane takes a bit of practice. See below and also here for more examples.

Add fields

1. Drag the Sales field to the Values area.

Excel calculates a grand total of 26356. This is the sum of all sales values in the entire data set:

2. Drag the Color field to the Rows area.

Excel breaks out sales by Color. You can see Blue is the top seller, while Red comes in last:

Notice the Grand Total remains 26356. This makes sense because we are still reporting on the full set of data.

Let's take a look at the fields pane at this point. You can see Color is a Row field, and Sales is a Value field:

Number formatting

Pivot Tables can apply and maintain number formatting automatically to numeric fields. This is a big time-saver when data changes frequently.

1. Right-click any Sales number and choose Number Format:

2. Apply Currency formatting with zero decimal places, then click OK:

In the resulting pivot table, all sales values have Currency format applied:

Currency format will continue to be applied to Sales values, even when the pivot table is reconfigured, or new data is added.

Sorting by value

1. Right-click any Sales value and choose Sort > Largest to Smallest.

Excel now lists top-selling colors first. This sort order will be maintained when data changes, or when the pivot table is reconfigured. 

Pivot table data needs to be "refreshed" in order to bring in updates. To reinforce how this works, we'll make a big change to the source data and watch it flow into the pivot table.

1. Select cell F5 and change $11.00 to $2000.

2. Right-click anywhere in the pivot table and select "Refresh".

Notice "Red" is now the top-selling color, and automatically moves to the top:

3. Change F5 back to $11.00 and refresh the pivot again.

Note: changing F5 to $2000 is not realistic, but it's a good way to force a change you can easily see in the pivot table. Try changing an existing color to something new, like "Gold" or "Black". When you refresh, you'll see the new color appear. You can use undo to go back to the original data and pivot.

You can add more than one field as a Value field.

1. Drag Units to the Value area to see Sales and Units together:

Percent of total

There are different ways to display values. One option is to show values as a percent of the total. If you want to display the same field in different ways, add the field twice.

1. Remove the Units from the Values area

2. Add the Sales field (again) to the Values area.

3. Right-click the second instance and choose "% of grand total":

The result is a breakdown by color along with a percent of the total:

Note: the number format for percentage has also been adjusted to show 1 decimal.

Here is the Fields pane at this point:

Pivot tables have a special feature to group dates into units like years, months, and quarters. This grouping can be customized.

1. Remove the second Sales field (Sales2). 

2. Drag the Date field to the Columns area.

3. Right-click a date in the header area and choose "Group":

4. When the Group window appears, group by Years only (deselect Months and Quarters):

We now have a pivot table that groups sales by color and year:

Notice there are no sales of Silver in 2016 and 2017. We can guess that Silver was introduced as a new color in 2018. Pivot tables often reveal patterns in data that are difficult to see otherwise.

Two-way Pivot

Pivot tables can plot data in various two-dimensional arrangements.

1. Drag the Date field out of the columns area

2. Drag Region into the Columns area.

Excel builds a two-way pivot table that breaks down sales by color and region:

3. Swap Region and Color (i.e. drag Region to the Rows area and Color to the Columns area). 

Excel builds another two-dimensional pivot table:

Again notice that total sales ($26,356) is the same in all pivot tables above . Each table presents a different view of the same data , so they all sum to the same total .

The above example shows how quickly you can build different pivot tables from the same data. You can create many other kinds of pivot tables , using all kinds of data.

Key Pivot Table benefits

Simplicity . Basic pivot tables are very simple to set up and customize. There is no need to learn complicated formulas.

Speed . You can create a good-looking, useful report with a pivot table in minutes. Even if you are very good with formulas, pivot tables are faster to set up and require much less effort .

Flexibility . Unlike formulas, pivot tables don't lock you into a particular view of your data. You can quickly rearrange the pivot table to suit your needs. You can even clone a pivot table and build a separate view.

Accuracy . As long as a pivot table is set up correctly, you can rest assured results are accurate. In fact, a pivot table will often highlight problems in the data faster than any other tool.

Formatting . A Pivot table can apply automatically apply consistent number and style formatting, even as data changes.

Updates . Pivot tables are designed for ongoing updates. If you base a pivot table on an Excel Table, the table resizes as needed with new data. All you need to do is click Refresh , and your pivot table will show you the latest.

Filtering . Pivot tables contain several tools for filtering data. Need to look at North America and Asia, but exclude Europe? A pivot table makes it simple.

Charts . Once you have a pivot table, you can easily  create a pivot chart .

More pivot table resources

• Pivot table examples
• Pivot table tips
• Pivot tables versus formulas
• Pivot table training

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Get Training

Quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

How to Create a Pivot Table in Excel: Step-by-Step (2024)

If you have a huge dataset that’s spread across your entire sheet, and now you want to create a summary out of it – you need a Pivot Table 💪

Pivot Tables make one of the most powerful and resourceful tools of Excel. Using them, you can create a summary out of any kind of data (no matter how voluminous it is).

You can sort your data, calculate sums, totals, and averages and even create summary tables out of it. If you are new to the concept of Pivot Tables, you’d be jaw-dropped by the end of this article.

So you’re ready? Let’s go.

Aa aah! Have you downloaded our free sample workbook for this guide? Get your hands on it right now to practice along with the guide below 🤟

Table of Contents

What is a pivot table?

How to create a pivot table

That’s it – Now what?

Frequently asked questions.

An Excel pivot table is meant to sort and summarize large (very large sets of data).

Once summarized, you can analyze them, make interactive summary reports out of them and even manipulate them 📝

Let’s cut down on the talking and see what a pivot table looks like. Here’s the image of some data in Excel.

The data is about the sales of many products made throughout the year 📆

Yes, it’s super huge and it goes across many columns and rows. But it’s hard to understand the data this way. How about we create a summary of the same?

Wow! That’s what we call a Pivot Table.

It summarizes the sales for each product for each type of customer 💁‍♀️

You can change fields to summarize this data in any way you like. Like summarizing the sales for any particular product, period, type, etc.

Pivot Tables can help you do the following 👇

• Cleanly summarize huge datasets.
• Categorize your data into multiple categories and sub-categories.
• Extract a certain portion of your data (if need be) by selecting the relevant fields only.
• Get any part of your data as a row or as a column (called ‘pivoting’).
• Get totals, and subtotals, or drill down any of them to see their details.

How to create a pivot table in Excel

If the images above made you feel like it would be a science to create a Pivot Table in Excel – that’s just not true.

Pivot Tables are super easy to create. Let me show you how we created the one above 👀

So here’s the data for sales of different products made throughout the year.

Before we go on making a Pivot Table, here are some tips for you to follow to make your Pivot Table better 😎

• Turn your source data into an Excel table before making a Pivot Table out of it. This way, whenever you make any changes to the source data (adding or deleting rows or columns), your Pivot Table will reflect the same.
• Delete any empty rows or columns from the source data.
• Name each column as desired to have the same header as a field title in the Pivot Table.
• Ensure your source data doesn’t have any subtotals or totals.

Let’s concise them into a Pivot Table here.

• Go to the Insert tab > Pivot Tables.

You’ll see the Insert PivotTables dialog box on your screen as follows:

• Create a reference to the cells containing the relevant data.

We will navigate to the sheet ‘Data’ in our workbook and select the cells that contain data.

We have converted our data into an Excel table so Excel automatically recognizes it as Table1. Do not forget to include the headers in the selection.

• Choose the option for New Worksheet or Existing Worksheet.

We will choose New Worksheet to have the Pivot table created on a new sheet.

• Click Okay.

There comes the Pivot Table pane to the right of your sheet 💭

It has two parts. The first part (as above) has all the fields (columns) of your source data listed.

And here’s the second part.

This part includes four boxes where you can specify how each field is to be shown in the Pivot Table. You can choose to have any field organized as a row or as a column, as a filter, or as a value 🎯

• Drag the filed Products from the list of fields to the box for Rows.

Here are the results.

Excel organized all the products as rows.

• Drag the field Amount from the list of fields to the box for Values.

And this is what happens:

Excel adds a column for Values. The column Amount in our source data contained the sales amount of each transaction.

By adding it as values, Excel has summarized the sales of each product and listed them against each of the products 💰

But what if you don’t need the sum of sales of each product, but their count?

• Right-click on any number from the column Sum of Amounts.
• From the context menu, select Summarize Values By.
• Click on any operation that you want to be performed. For example, we want the Count of sales so we are selecting Count 🔢

The results change as follows:

The column Sum of Amounts becomes Count of Amounts . For each product, we now have the Count of sales transactions.

No, it doesn’t stop here.

• Drag the field for Customer Type to the box for columns.

Excel adds columns for each Customer Type . And the sales of each product are now split into customer types 📊

Let’s add another field to see how you can further drill down into details using a Pivot Table.

• Drag and drop the field for Months to the box for Rows.

Excel adds a breakup of months under each product.

So now you can see a summary of sales of each product, for each month and by each customer type. Too convenient and clean ✔

You can make so many more variations to your Pivot Table by pivoting between rows and columns. No matter how vast your data is, Pivot Tables know how to knit it all together.

I am sure you loved the idea of Pivot Tables explained in the Pivot Table tutorial above. Excel Pivot Tables are a blessing for the people who get to deal with huge, messy data now and again.

But that’s just one tool of Excel. And Excel is a whole package of mind-boggling tools, features, and functions. We yet have so much more to explore 🚀

To begin exploring this giant spreadsheet software, I suggest you go with the VLOOKUP, SUMIF, and IF functions of Excel.

Want to learn them already? Enroll in my 30-minute free email course here that will teach you these (and many more) Excel functions in the most fun way.

Other resources

Using pivot tables, you can also create Excel Dashboards. It’s like combining multiple pivot tables in the form of interactive charts and graphs on one page.

Excel dashboards are just amazing – learn how to make them in Excel here.

Also, make sure to check out the 6 best dashboard templates I’ve found on the web!

What is a Pivot Table in Excel used for?

Pivot Tables are used to sort and summarize large datasets in Microsoft Excel. They allow changing pivot table fields so you can readily decide which part of your dataset is to be summarized.

By changing fields, you can create interactive summaries that will bring together massive sets of data in the cleanest manner.

What is the easiest way to add a Pivot Table to your spreadsheet?

To add a Pivot Table to your spreadsheet, go to the sheet (the first cell) where you want the Pivot Table summary inserted.

• Go to the Insert Tab > Pivot Table (Or press the Alt Key > N > V ) to launch the insert Pivot Table dialog box.
• Refer to the cells containing the data.
• Check the option for a ‘New Worksheet’.

How-To Geek

How to create a pivot table in microsoft excel.

Your changes have been saved

Email is sent

Email has already been sent

Please verify your email address.

You’ve reached your account maximum for followed topics.

Midrange CPUs Are All You Need for Gaming

At&t was advertising a service that doesn’t exist, android users should disable 2g connectivity, says google, quick links, what are pivottables in excel, make a basic pivot table in excel, build or edit the pivot table, filter or sort the pivot table.

If you're like many spreadsheet application users, you might feel intimidated by pivot tables. But if you start with the basics and learn how to build a pivot table in Microsoft Excel, it can become your favorite feature.

A pivot table provides an interactive way for you to arrange, group, calculate, and analyze data. You can manipulate the same data several different ways to view exactly what you need. Pivot tables give you a robust way to work with a data set to spot patterns, review summaries, and calculate counts, averages, or totals.

Related: How to Use Pivot Tables to Analyze Excel Data

You would normally create a pivot table if you have an extensive amount of data. This is what makes the pivot table a valuable tool; its ability to make large amounts of data more manageable for analysis .

Microsoft uses "PivotTables" as a single word in its documentation and interfaces surrounding "pivot tables." So, you may see both terms as you use the application, and we'll include both in this tutorial as applicable.

To get started, select your data . You can create a pivot table from a range of cells or an existing table structure. Just make sure that you have a row of headers at the top and no empty columns or rows.

You then have two ways to make the pivot table. You can use one of Excel's recommended PivotTables or create the table yourself.

Use a Recommended PivotTable

Just like inserting a graph in Excel with the recommended chart options, you can do the same with a pivot table. Excel then reviews your data for tables that fit.

Go to the Insert tab and click "Recommended PivotTables" on the left side of the ribbon.

When the window opens, you'll see several pivot tables on the left. Select one to see a preview on the right. If you see one you want to use, choose it and click "OK."

A new sheet will open with the pivot table you picked. You'll also see the PivotTable Fields sidebar on the right which allows you to edit the table, which we explain below.

Make Your Own Pivot Table

If you want to dive right in and create your own pivot table instead, go to the Insert tab and pick "PivotTable" in the ribbon.

You'll see a window appear for PivotTable From Table or Range. At the top, confirm the data set in the Table/Range box. Then, decide if you want the table in a new worksheet or your existing one. For analyzing multiple tables, you can check the box to add it to the Data Model. Click "OK."

You'll then see the pivot table and the PivotTable Fields sidebar, ready for you to build your table or edit the recommended table you inserted.

Using the PivotTable Fields sidebar, start by choosing the fields at the top you want to include by checking the boxes.

You can check and uncheck boxes for the fields you want to use at any time.

Excel then drops those fields into the boxes at the bottom of the sidebar where it believes they belong. This is where you will decide how you want to place them in your table.

Depending on the type of data in your sheet, you'll see things like numbers in the Values box, dates and times in the Columns box, and textual data in the Rows box. These are the defaults for those types of data, but you can move them where you want them.

Related: How to Change Date Formats in Microsoft Excel

As an example, we want to see our Months as columns instead of rows. You simply drag that field from the Rows box to the Columns box and your table will update accordingly. Alternatively, you can use the drop-down arrows next to the fields to move them.

If you have more than one field in a box, the order determines the placement in the pivot table as well. In this example, we have Department first and Location second in the Rows box which is how they're grouped in the table.

But by moving Location above Department, we see each of our locations as the main fields instead, which is what we want. Then, we simply use the minus and plus buttons next to each Location to expand the group and view the Departments.

Because you can move the fields between the boxes with simple drag-and-drop actions, this allows you to easily find the best fit for your data analysis .

Related: How to Use Excel's "Quick Analysis" to Visualize Data

The perks of using a table in Excel include the ability to filter and sort your data as needed. Pivot tables offer these same functions.

You'll see filters built-in for your first column and depending on your data arrangement, maybe more than one column. To apply a filter to the column, click the filter button next to the header and choose how to filter the data as you normally would in an Excel table.

To sort, click the button and select a sort option.

Add a Table Filter

You can also apply a filter to the top level of the table. Using our example, we want to filter the entire table to see each Department, one at a time. Drag the field you want to use as the filter into the Filters box in the PivotTable Fields sidebar.

You'll see your table update to place this filter at the top. Then, click the filter button to apply the one you want at the time.

To remove this higher-level table filter, simply drag the field out of the Filter box in the sidebar.

Well, there you have it! The bare-bones basics you need to create a pivot table in Excel. Hopefully this how-to gets you off to a great start with your own pivot table!

Related: 12 Basic Excel Functions Everybody Should Know

• Microsoft Office
• Microsoft Excel

101 Excel Pivot Tables Examples

Pivot Tables in Excel are one of the most powerful features within Microsoft Excel. An Excel Pivot... read more

#ez_toc_widget_sticky--1 .ez-toc-widget-sticky-container ul.ez-toc-widget-sticky-list li.active{ background-color: #ededed; } Steps To Follow

Get Trainings

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Pivot Tables in Excel are one of the most powerful features within Microsoft Excel. An Excel Pivot Table  allows you to analyze more than 1 million rows of data with just a few mouse clicks, show the results in an easy to read table, “pivot”/change the report layout with the ease of dragging fields around, highlight key information to management and include Charts & Slicers for your monthly presentations.

Pivot Tables are used by Project Managers, Finance Analysts, Auditors, Cost Controllers, Sales Analysts, Financial Controllers, Information Technology, Human Resources, Doctors and Statisticians just to name a few. You would be surprised on how widely used Excel Pivot Tables are!

We have over a hundred tutorials for you to learn and master Excel Pivot Tables ! So read on!

Analysis Calculated Fields & Items Conditional Formatting Customize Excel Pivot Tables Group

Pivot Charts Pivot Table Macros Slicers Sort & Filter Tips and Tricks

Click on any Excel Pivot Table link below and it will take you to the free example tutorial & downloadable Excel workbook for you to practice!

Table of Contents

Want to know learn Excel Pivot Tables in 5 minutes?

*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***

Watch it on YouTube and give it a thumbs-up!

download the youtube excel practice file PivotTable.xlsx

Want to master Excel Pivot Tables from scratch?

Calculated Fields & Items 

Conditional formatting , excel pivot tables , pivot charts , pivot table macros , sort & filter , tips and tricks .

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course .

Related Articles

Month over Month Comparison Charts in Excel Pivot Tables

Excel Harvey Balls Made Easy: Step-by-Step Tutorial

Boost Your Data Analysis with Pivot Tables: Understanding Image and Data Types

Get Video Training

Dramatically Reduce Repetition, Stress, and Overtime! Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job! Learn in as little as 5 minutes a day or on your schedule.

#1 Excel tutorial on the net

• Pivot Tables

Insert a Pivot Table | Drag fields | Sort a Pivot Table | Filter | Change Summary Calculation | Two-dimensional Pivot Table

Pivot tables are one of Excel 's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.

Our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and Country.

Insert a Pivot Table

To insert a pivot table , execute the following steps.

1. Click any single cell inside the data set.

2. On the Insert tab, in the Tables group, click PivotTable.

The following dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet.

3. Click OK.

Drag fields

The PivotTable Fields pane appears. To get the total amount exported of each product, drag the following fields to the different areas.

1. Product field to the Rows area.

2. Amount field to the Values area.

3. Country field to the Filters area.

Below you can find the pivot table. Bananas are our main export product. That's how easy pivot tables can be!

Sort a Pivot Table

To get Banana at the top of the list, sort the pivot table.

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Sort, Sort Largest to Smallest.

Because we added the Country field to the Filters area, we can filter this pivot table by Country. For example, which products do we export the most to France?

1. Click the filter drop-down and select France.

Result. Apples are our main export product to France.

Note: you can use the standard filter (triangle next to Row Labels) to only show the amounts of specific products.

Change Summary Calculation

By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation that you want to use, execute the following steps.

2. Right click and click on Value Field Settings.

3. Choose the type of calculation you want to use. For example, click Count.

4. Click OK.

Result. 16 out of the 28 orders to France were 'Apple' orders.

Two-dimensional Pivot Table

If you drag a field to the Rows area and Columns area, you can create a two-dimensional pivot table. First, insert a pivot table . Next, to get the total amount exported to each country, of each product, drag the following fields to the different areas.

1. Country field to the Rows area.

2. Product field to the Columns area.

3. Amount field to the Values area.

4. Category field to the Filters area.

Below you can find the two-dimensional pivot table.

To easily compare these numbers, create a pivot chart and apply a filter. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful pivot table features Excel has to offer.

1/9 Completed! Learn much more about pivot tables > Go to Next Chapter: Tables

Learn more, it's easy

• Group Pivot Table Items
• Multi-level Pivot Table
• Frequency Distribution
• Pivot Chart
• Update Pivot Table
• Calculated Field/Item
• GetPivotData

Download Excel File

• pivot-tables.xlsx

Next Chapter

Follow excel easy.

Most Popular

• Formulas and Functions
• Conditional Formatting
• Find Duplicates
• Drop-down List
• Index and Match

Become an Excel Pro

• 300 Examples

Pivot Tables • © 2010-2024 Excel is Awesome, we'll show you: Introduction • Basics • Functions • Data Analysis • VBA

How to Use Advanced Pivot Table in Excel (25 Tips & Techniques)

PivotTable – Basic Things

A  PivotTable  is a powerful data analysis tool in  Microsoft Excel . It allows users to quickly summarize, organize, and gain insights from large datasets. By transforming raw data into a more meaningful and compact format, PivotTables enable efficient analysis without the need for complex formulas or manual data manipulation. They are especially useful when dealing with extensive datasets, providing a user-friendly way to extract valuable information and identify trends, patterns, and outliers. If you’ve mastered the basics of PivotTables, exploring advanced techniques can further enhance your data analysis capabilities.

Basic Components of a PivotTable

• Data Source : The data source serves as the foundation for creating a PivotTable. It originates from the original dataset and should be well-organized, complete with column headers. These headers play a crucial role in defining the fields within the PivotTable.
• To access or hide the Field List, navigate to the “PivotTable Analyze” tab and select or deselect the corresponding option.
• Rows and Columns : In a PivotTable, you can arrange fields from the data source into the “Rows” and “Columns” areas. These selections determine how the data is organized and displayed in the final table.
• Values : The “Values” area contains numerical data that you want to summarize or analyze. You can apply various summary functions (such as sum, count, average, minimum, maximum, etc.) to perform calculations on this data.
• Filters : The “Filters” area allows you to add fields that act as filters. By selecting or deselecting filter options, you can dynamically update the results displayed in the PivotTable.

How to Create a Pivot Table in Excel

The below large dataset will be used to create a PivotTable.

• Open your Excel workbook containing the dataset you want to analyze.
• Click on any cell within the dataset to ensure it’s selected.
• Navigate to the  Insert  tab in the Excel ribbon.
• Choose  PivotTable and click on  From Table/Range .

• The PivotTable from table or range  dialog box will appear.
• The  Table/Range  field will automatically be set based on the cell you clicked earlier.
• If you want the PivotTable to appear in a new worksheet , select that option and click  OK .

• You need to select the fields (columns) from your dataset to include in the PivotTable.
• The  Field List  will appear on the right side of your screen.
• Rows : Determines how data is organized vertically.
• Columns : Determines how data is organized horizontally.
• Values : Contains the numerical data you want to summarize (e.g., sum, average, count, etc.).
• Filters : Allows you to add fields that act as filters for your PivotTable.

• Rows : Country and Title
• Values : Gross Revenue and Budget
• Filters : Genre

• By arranging these fields, you’ve successfully created a PivotTable that summarizes and analyzes your data.

• Press  Alt + N + V + T .
• Follow the same steps as described earlier.

Remember, PivotTables are incredibly versatile and can help you gain valuable insights from your data.

Benefits of Using Advanced Techniques in Excel Pivot Table

Using advanced techniques in Excel Pivot Tables can significantly enhance your data analysis capabilities, making your work more efficient and insightful. Let’s explore the advantages:

• Advanced techniques allow you to perform more complex tasks, such as creating calculated fields, calculated items, and custom formulas. These functionalities enable you to extract deeper insights from your data beyond basic summary functions (e.g., sum, average, count).
• With advanced Pivot Table techniques, you can build sophisticated data models. This includes combining multiple data sources, using Power Query for data shaping and transformation, and establishing relationships between tables.
• Advanced features enable you to create dynamic reports that automatically update when the source data changes. This ensures your analysis remains up-to-date without manual adjustments.
• Slicers and timelines are powerful filtering tools within Pivot Tables. They provide an interactive way to filter data, allowing you to explore different aspects of your dataset effortlessly.
• PivotTables can be used to create charts and graphs. Visualizing your data in this way helps present complex information more intuitively to stakeholders.
• Advanced techniques allow you to explore data at different levels. You can drill down into specific details to gain deeper insights, which is valuable for thorough analysis.
• PivotTables can automatically group date and time data into intervals (e.g., months, quarters, years). This simplifies time-based analysis and provides a clearer view of trends over time.
• As you become proficient with advanced Pivot Table techniques, you’ll save time and effort during data analysis. This efficiency allows you to focus on interpreting results and making informed decisions based on data.

25 Tips & Techniques when using Advanced Pivot Tables

1. use slicers for effortless data filtering.

• Scenario : You have a PivotTable, and you want to filter data quickly with a single click.

• Click on any cell within your PivotTable .
• Navigate to the  Insert  tab.
• Select  Slicer .

• In the Insert Slicers dialog box, choose the field (e.g., Country ) by which you want to filter your PivotTable .
• Click  OK .

• A slicer will appear next to your PivotTable . You can now select different countries to filter the data.
• The best part? You can select multiple countries simultaneously as filters.

2. Enhance Data Visualization with Timelines

• Scenario : You’re working with a dataset containing movie release dates spanning from 1920 to 2015 . You want to filter data based on release years .
• Select any cell within your PivotTable.
• Go to the  Insert  tab.
• Choose  Timeline .

• In the Insert Timelines dialog box, you’ll see the available time-related field (e.g., Release Date ).

• A timeline will be added to your PivotTable.
• From the dropdown , select the desired time interval (e.g., YEARS ).

• You can easily filter data by selecting specific years from the timeline.

• Bonus : You can even select multiple years, and the PivotTable values will adjust accordingly.

3. Customize Number Format in a PivotTable

Did you know that you can tailor the number format within a PivotTable? It’s a handy feature! Here’s how you can do it:

• Right-click on any cell in the column for which you want to change the number format .
• From the context menu, select Number Format .

• The Format Cells dialog box will appear.
• Choose an appropriate category (e.g., Accounting ) and set the desired number of decimal places (e.g., 0 ).

You’ll see that the number format has been updated.

4. Sort Items Using the Context Menu

Sorting items in a PivotTable is essential for better analysis. Follow these steps:

• Right-click on any cell in the column you want to sort.
• Select Sort and then choose More Sort Options .

• In the Sort By Value dialog box, specify your sorting preferences (e.g., Smallest to Largest and Top to Bottom ).

Your table will now be sorted based on the sum of the Gross Revenue  column.

5. Custom Sort Items

Sometimes, you may want to sort PivotTable items according to your own order . Here’s how:

• Create a custom sort order by listing the items in a separate column within the same worksheet .

• Click on the File tab.

• Go to Options .

• In the Excel Options dialog box, select Advanced and click on Edit Custom Lists .

• Specify the cell reference of your custom sort list (or manually enter the items).
• Press Import and then click OK .

• Press OK when the Excel Options dialog box appears.

• Refresh the PivotTable by right-clicking on any cell in the column you want to sort.

The items in the Row Labels  column will be custom-sorted according to your preference.

6. Create or Remove a Calculated Field in a PivotTable

Creating a calculated field is an advanced feature in Excel’s PivotTable. It’s a clever technique that allows you to compute various parameters without writing complex formulas. Here’s how you can create or remove a calculated field:

6.1 Create a Calculated Field:

• Click on any cell within the PivotTable.
• Go to the PivotTable Analyze  tab.
• Under Calculations , select Fields, Items, & Cells , and then choose Calculated Field .

• The Insert Calculated Field  dialog box will appear.
• Provide a relevant name for your calculated field (e.g., Gross Profit ).
• From the available fields , select the ones you want to use in your formula and click I nsert Field . For example, you can calculate Gross Profit by subtracting the Budget from the Gross Revenue.
• Review your formula and click OK .

• A new column will be added to your existing PivotTable with the calculated values.

6.2 Remove a Calculated Field

• To remove a calculated field , follow the same steps as when creating one.
• Open the I nsert Calculated Field  dialog box.
• Click the dropdown menu and choose the field you want to delete.
• Press Delete and then click OK .

Now you can manage your calculated fields efficiently!

7. Calculate the Difference Between Two Columns

You can easily compute the difference between two columns in a PivotTable without writing any formulas. Follow these quick steps:

• In your dataset, you have Gross Revenue for the years “2014” and “2015.” Let’s calculate the difference in Gross Revenue between these two years.

• Go to the Design  tab.
• Under Grand Totals , select Off  for both Rows and Columns . We don’t need grand totals for this calculation.

• Add the Gross Revenue  to the Values area a second time. We’ll use this duplicate field to show the difference.

• You’ll see that Gross Revenue  has been added a second time.

• Right-click on any cell in the newly added Sum of Gross Revenue2  column.
• Select Show Value As and then choose Difference From… ”

• The Show Values As dialog box will appear. Set Years (Release Date)  as the Base Field .
• In the Base Item dropdown, select previous  because we want to calculate the difference from the previous column.

• The difference will now be calculated.

• Edit the name of the column to Difference  and hide any unnecessary columns.

8. Show Percentage of Grand Total

Now let’s determine the Total Reviews  as a percentage of the grand total. Follow these steps:

• Right-click on any cell in the column you want to display as a Percentage of the Grand Total .
• Click on Show Values As and then choose % of Grand Total .

You’ll see that the Sum of Total Reviews  is now shown as a percentage of the overall grand total.

9. Disabling the GETPIVOTDATA Formula

The GETPIVOTDATA function retrieves data from a pivot table by referencing specific values within that table. Unlike regular cell references, it directly extracts data from the source data. Suppose you want to reference a cell value from a PivotTable. For example, you want to display the value of cell D7 in cell E7 by simply writing the formula “ =D7 .” However, after doing this, the GETPIVOTDATA formula still appears in cell E7 . The formula looks like this:

Keeping the GETPIVOTDATA formula can be problematic, especially when creating dynamic dashboards. If it remains active, the data won’t update correctly. Here are some difficulties users face when using GETPIVOTDATA :

• When users frequently change data criteria, GETPIVOTDATA becomes cumbersome. Each time the criteria change, the function must be manually updated.
• If you modify the layout or structure of the PivotTable (e.g., changing the layout), GETPIVOTDATA formulas may break, causing errors in the worksheet.
• GETPIVOTDATA may not work well with calculated fields and items in the PivotTable, leading to incorrect results or errors.
• GETPIVOTDATA often uses hard-coded cell references in the formula. This can be problematic when you want to use cell references or other dynamic formulas.

To avoid these problems, you can turn off the GETPIVOTDATA formula:

• Click on any cell in the column for which you want to disable GETPIVOTDATA .
• Under Options , click on Generate GetPivotData .

• The checkmark should disappear next to the Generate GetPivotData  option.

• If you enter the formula “ =D7 ” in cell E7 , it will display only the value of cell D7 . This is because you’ve turned off the GETPIVOTDATA option.

Remember that you can always turn it back on by clicking the Generate GetPivotData  option again.

10. Grouping and Ungrouping Items Under a Field

Grouping items in a PivotTable allows you to organize and summarize data effectively. Here’s how you can group and ungroup items:

• Select the items you want to group together.
• Right-click on the selection.
• From the context menu, choose the Group  option.

• The selected items will now be grouped under a default name (e.g., Group1 ). You can edit this group name according to your preference.

• To ungroup the items, right-click on the group name (e.g., Group1 ).
• Select the Ungroup  option.

• The items will be ungrouped.

11. Grouping a  Date Field

Grouping date fields is useful for analyzing time-based data. Let’s say you have a column called Released Date  represented by quarters, but you want to group it by months. Follow these steps:

• Right-click on any cell within the Quarters  column.
• Select the Group  option from the context menu.

• The Grouping  dialog box will open.
• The starting and ending dates will be set automatically based on your dataset, but you can adjust them if needed.
• In the By box, choose your preferred grouping (e.g., Months ).

• The dates will now be successfully grouped into Months .

12. Creating a Report Filter

A report filter allows you to filter data in your PivotTable based on specific criteria. Here’s how you can create one:

• The PivotTable Fields  pane will appear.
• Drag and drop the field that you want to use as a filter into the Filters  area.

• A filter option will appear just above the table.
• Click on the drop-down arrow.

• Select the option you want to see in the PivotTable (e.g., Black and White ) and press OK .

• Only the values corresponding to your chosen filter will be displayed.

• You can further refine the filter by selecting other options (e.g., Color ).

13. Filter Top/Bottom N Values

Suppose you want to filter the top or bottom items based on the sum of gross revenue in your PivotTable.

Follow these steps:

• Click on the drop-down arrow next to the Row Labels .
• Select Value Filters and then choose the Top 10  option.

• The Top 10 Filter  dialog box will appear.
• From the drop-down, choose Top  to show the top values.

• Select the number of items you want to see. For example, if you want to see the top 12 values, select 12 .
• Under By , choose Sum of Gross Revenue  and press OK .

• As a result, you’ll see the top 12 sum of gross revenue values along with their corresponding genres .

• If you want to show bottom values instead, select Bottom  from the dropdown. For example, you can choose the bottom 5 items by sum of gross revenue.

14. Refresh Data

When you update or add any value, the source data of the PivotTable changes. To reflect these changes, you need to refresh the table. Let’s say the sum of duration for the genre Crime in your PivotTable is currently 29558 . If you make changes to any value under this genre, the PivotTable needs to be updated.

14.1 From the PivotTable Analyze Tab

• Change the value from 110 to 11000  (or any other value) to visualize the change.

• Click on the PivotTable Analyze tab.
• Select the Refresh command and click on the Refresh  option.

• The sum of duration for the Crime  genre will be updated to the new value (e.g., 40448 ).

14.2 From PivotTable Options

• Select any cell within the PivotTable.
• Click on the PivotTable Analyze  tab.
• Choose the PivotTable dropdown and then click on Options .

• In the PivotTable Options dialog box, go to the Data  section.
• Check the Refresh data when opening the file  option.
• The values will automatically refresh every time you open the file.

14.3 Refresh Pivot Table When New Column/Row is Added

In your dataset, you can see that the sum of duration for the Action  movie is 101711 . If you insert new data into the source data of the Action  movie, the PivotTable should be updated accordingly.

• Insert a new row or column of information into the data source of the PivotTable.

• Right-click on any cell within the PivotTable .
• From the context menu, click on Refresh .

• However, you’ll notice that the PivotTable doesn’t update after clicking Refresh . The Action movie still shows a duration of 101711  minutes.

To resolve this issue, follow these additional steps:

• Select the PivotTable Analyze  tab.
• Choose Change Data Source and then select Change Data Source  again.

• The Move PivotTable  dialog box will appear.
• This time, select the entire table, including the newly added row, in the Table/Range  box.

• As a result, you’ll see that the data is now updated in the PivotTable.

15. Hide/Unhide Subtotals

In a PivotTable, subtotals are typically shown. However, there are situations where you might need to hide these subtotals. Follow these steps:

• Click on any cell within the Sum of Duration  column.
• Select the Design  tab.
• Click on Subtotals and choose the option Do not Show Subtotals .

• As a result, the subtotals will be hidden.

• If you want to Unhide them, select the option Show all Subtotals at Top of Group  under Subtotals .

16. Delete Source Data and Restore It with a Double-click

Sometimes, to reduce file size, you may need to delete the source data of a PivotTable. Fortunately, deleting the source data won’t affect the table itself. Here’s how to do it:

• Right-click on the sheet where the source data is stored.
• Select the Delete  option to remove it.

• If you want to restore the source data, right-click on any cell within the PivotTable.
• Choose Show Details .

• The data will be restored in a table form.

• Alternatively, you can double-click on the output of the Grand Total  cell to restore the source data.

17. Drill Down Pivot Table

Drilling down in a PivotTable is a useful feature to show detailed information from a summarized table. Follow these steps:

• Initially, double-click on the item you want to drill down into.

• The Show Detail  dialog box will appear.
• Choose the field that contains the detail you want to see. For example, if you want to see details by country, select Country .

• The items will now have a plus ( + ) sign next to them.
• Double-click on any item to drill down further.

• It will show the names of countries that have released movies in the Animation genre, along with their corresponding values.

18. Create Different Styles in Pivot Table

• Click on the drop-down icon for PivotTable Styles .

• Choose New PivotTable Style…

• The New PivotTable Style  dialog box will appear.
• Give your custom PivotTable style a name.
• Select the element you want to format from the Table Element options. For example, I’ve chosen the Header Row .
• Click on Format .

• Customize the cell formatting according to your preference. In my case, I’ve changed the Fill color.
• Check the Sample and press OK .

• After reviewing the Preview , click OK .

• You’ll now see your created PivotTable style listed in the PivotTable Styles command as Custom .

19. Change Layout of Pivot Table

• Click on Report Layout and choose the layout you want to display. I’ve selected Show in Compact Form .

• The PivotTable will now appear in the Compact Form layout.

• If you choose Show in Tabular Form , it will look different.

• Similarly, selecting Show in Outline Form  will produce a different output.
• Choose a layout that best suits your table.

20. Restrict Column Width Change after Refresh

In a PivotTable, adjusting column widths according to your needs is common. However, after refreshing the table, the column widths automatically adjust to autofit the content. Unfortunately, this can sometimes affect the overall appearance of your table. To prevent this:

• In the following image, I’ve increased the column width to improve readability.

• If I click on the Refresh button, the columns will autofit again.

• To restrict column width changes after refresh, right-click on any cell within the PivotTable.
• Click on PivotTable Options.

• In the PivotTable Options dialog box, select the Layout & Format  option.
• Uncheck the box that says Autofit column widths on update .

21. Display Items with No Data

In a PivotTable, some items may have no data associated with them. By default, the PivotTable hides the field names for these data-less items. However, you can display them using the following steps:

• In your dataset, there are hidden items that lack data.
• Right-click on any cell within the PivotTable.
• Click on Field Settings .

• The Field Settings dialog box will open.
• Select the Layout & Print option and then click on Show items with no data .

• As a result, the items that previously had no data will now be displayed.

22.  Substitute Blank Cells in Pivot Table

In a PivotTable, you can replace any blank cell with a value. If you want to provide additional information about these blank cells, follow this technique:

• Consider the dataset where there are many blank cells. For example, a country didn’t release any movies under the Action  genre.

• Select PivotTable Options  from the context menu.

• In the PivotTable Options dialog box, click on Layout & Format .
• Write the text you want to substitute for the blank values in the For empty cells show box (e.g., No Release ).

• The blank cells will now be substituted with the specified values.

23. Attach Data Bars in Pivot Table

You can enhance your PivotTable by adding data bars. These bars provide a visual representation of data and make the table more attractive and easier to understand. Follow these steps:

• Then, select the Home  tab.
• Go to Conditional Formatting and click on Data Bars , then choose More Rules…

• The New Formatting Rule  dialog box will appear.
• Select All cells showing ‘Sum of Gross Revenue’ values .
• Click on the Show Bar Only  option if you want to display only the bars.
• Choose a color and check the preview.

• As a result, data bars will be added to the selected cells.

24. Create a Pivot Chart

Adding a Pivot Chart to your PivotTable can enhance the readability of your worksheet. Follow these steps to create a Pivot Chart from a Pivot Table:

• Go to the Insert  tab.
• Click on PivotChart  and select the desired chart type.

• The Insert Chart  dialog box will appear.
• Choose the chart type you want (for example, Pie chart ).
• Click OK  to insert the Pivot Chart.

• A Pivot Chart is inserted.

25. Create Multiple Pivot Tables

Suppose you have a dataset with two types of movies: Black and White and Color . You want to create separate PivotTables for each movie type. Here’s how you can do it:

• Click on Show  and then select Field List .

• The PivotTable Fields  pane will open.
• Drag the field (e.g., Color/B&W ) to the Filters  area.

• This will insert a filter option into the existing PivotTable.

• The existing PivotTable is in a sheet named Multiple Pivot Tables .
• Now create two separate PivotTables based on the filter options:

• Go to PivotTable Analyze , select PivotTable and click on Options .
• Click on Show Report Filter Pages…

• Select the filter item from the Show Report Filter Pages  dialog box.

• You can see another two PivotTables have been inserted based on the filter options. The name of the worksheets is based on the filter options.

Apply Keyboard Shortcuts to Enhance Productivity with Pivot Table

Using keyboard shortcuts in Excel is always helpful. Here are some PivotTable-related keyboard shortcuts that can save you time and effort:

Things to Remember

• Select the Appropriate Data Range:  When creating a PivotTable, make sure to choose the relevant data range. Avoid including unnecessary rows or columns.
• Regularly Refresh Your PivotTable:  If your data source changes or updates frequently, remember to refresh your PivotTable to reflect the latest data.
• Use Clear and Descriptive Field Names:  When working with a large dataset, use field names that are easy to understand and describe the data accurately.
• Choose the Right Summary Function: Depending on the type of data you want to analyze (e.g., numeric values, counts, averages), select the appropriate summary function for your PivotTable.

Frequently Asked Questions

1. What’s the differences between a pivot table and a pivot chart?

• A  Pivot Table  is a data analysis tool that summarizes and aggregates data based on specific criteria (rows, columns, values).
• A  Pivot Chart  is a graphical representation of the data within a Pivot Table. It helps visualize trends and patterns by converting data into different types of graphs (e.g., bar charts, line charts, pie charts).

2. Are there any limitations to advanced pivot tables?

While advanced Pivot Tables are powerful, they may face limitations:

• Handling very large datasets could impact performance.
• Complex calculations might slow down processing.
• Customizations may be less flexible compared to specialized data analysis tools. Remember these tips and insights to make the most of your PivotTable experience!

Download Practice Workbook

You can download the practice workbook from here:

What is ExcelDemy?

Mahfuza Anika Era graduated from the Bangladesh University of Engineering and Technology in Civil Engineering. She has been with ExcelDemy for almost a year, where he has written nearly 30 articles and reviewed many. She has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, she is working as a team leader for ExcelDemy. Her role is to guide his team to write reader-friendly content. Her interests are Advanced Excel, Data Analysis, Charts & Dashboards,... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

Overview of PivotTables and PivotCharts

You can use a PivotTable to summarize, analyze, explore, and present summary data. PivotCharts complement PivotTables by adding visualizations to the summary data in a PivotTable, and allow you to easily see comparisons, patterns, and trends. Both PivotTables and PivotCharts enable you to make informed decisions about critical data in your enterprise. You can also connect to external data sources such as SQL Server tables, SQL Server Analysis Services cubes, Azure Marketplace, Office Data Connection (.odc) files, XML files, Access databases, and text files to create PivotTables, or use existing PivotTables to create new tables.

About PivotTables

A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail, and answer unanticipated questions about your data. A PivotTable is especially designed for:

Querying large amounts of data in many user-friendly ways.

Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas.

Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you.

Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the source data.

Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data enabling you to focus on just the information you want.

Presenting concise, attractive, and annotated online or printed reports.

For example, here's a simple list of household expenses on the left, and a PivotTable based on the list to the right:

For more information, see Create a PivotTable to analyze worksheet data .

Ways to work with a PivotTable

After you create a PivotTable by selecting its data source, arranging fields in the PivotTable Field List , and choosing an initial layout, you can perform the following tasks as you work with a PivotTable:

Explore the data     by doing the following:

Expand and collapse data, and show the underlying details that pertain to the values.

Sort, filter, and group fields and items.

Change summary functions, and add custom calculations and formulas.

Change the form layout and field arrangement     by doing the following:

Change the PivotTable form: Compact , Outline , or Tabular .

Add, rearrange, and remove fields.

Change the order of fields or items.

Change the layout of columns, rows, and subtotals     by doing the following:

Turn column and row field headers on or off, or display or hide blank lines.

Display subtotals above or below their rows.

Adjust column widths on refresh.

Move a column field to the row area or a row field to the column area.

Merge or unmerge cells for outer row and column items.

Change the display of blanks and errors     by doing the following:

Change how errors and empty cells are displayed.

Change how items and labels without data are shown.

Display or hide blank rows

Change the format     by doing the following:

Manually and conditionally format cells and ranges.

Change the overall PivotTable format style.

Change the number format for fields.

Include OLAP Server formatting.

For more information, see Design the layout and format of a PivotTable .

About PivotCharts

PivotCharts provide graphical representations of the data in their associated PivotTables. PivotCharts are also interactive. When you create a PivotChart, the PivotChart Filter Pane appears. You can use this filter pane to sort and filter the PivotChart's underlying data. Changes that you make to the layout and data in an associated PivotTable are immediately reflected in the layout and data in the PivotChart and vice versa.

PivotCharts display data series, categories, data markers, and axes just as standard charts do. You can also change the chart type and other options such as the titles, the legend placement, the data labels, the chart location, and so on.

Here's a PivotChart based on the PivotTable example above.

For more information, see Create a PivotChart .

Differences between PivotCharts and standard charts

If you are familiar with standard charts, you will find that most operations are the same in PivotCharts. However, there are some differences:

Row/Column orientation     Unlike a standard chart, you cannot switch the row/column orientation of a PivotChart by using the Select Data Source dialog box. Instead, you can pivot the Row and Column labels of the associated PivotTable to achieve the same effect.

Chart types      You can change a PivotChart to any chart type except an xy (scatter), stock, or bubble chart.

Source data      Standard charts are linked directly to worksheet cells, while PivotCharts are based on their associated PivotTable's data source. Unlike a standard chart, you cannot change the chart data range in a PivotChart's Select Data Source dialog box.

Formatting      Most formatting—including chart elements that you add, layout, and style—is preserved when you refresh a PivotChart. However, trendlines, data labels, error bars, and other changes to data sets are not preserved. Standard charts do not lose this formatting once it is applied.

Although you cannot directly resize the data labels in a PivotChart, you can increase the text font size to effectively resize the labels.

Creating a PivotTable or PivotChart from worksheet data

You can use data from a Excel worksheet as the basis for a PivotTable or PivotChart. The data should be in list format, with column labels in the first row, which Excel will use for Field Names . Each cell in subsequent rows should contain data appropriate to its column heading, and you shouldn't mix data types in the same column. For instance, you shouldn't mix currency values and dates in the same column. Additionally, there shouldn't be any blank rows or columns within the data range.

Excel tables     Excel tables are already in list format and are good candidates for PivotTable source data. When you refresh the PivotTable, new and updated data from the Excel table is automatically included in the refresh operation.

Using a dynamic named range     To make a PivotTable easier to update, you can create a dynamic named range , and use that name as the PivotTable's data source. If the named range expands to include more data, refreshing the PivotTable will include the new data.

Including totals     Excel automatically creates subtotals and grand totals in a PivotTable. If the source data contains automatic subtotals and grand totals that you created by using the Subtotals command in the Outline group on the Data tab, use that same command to remove the subtotals and grand totals before you create the PivotTable.

Using an external data source to create a PivotTable or PivotChart

You can retrieve data from an external data source such as a database, an Online Analytical Processing (OLAP) cube, or a text file. For example, you might maintain a database of sales records you want to summarize and analyze.

Office Data Connection files     If you use an Office Data Connection (ODC) file (.odc) to retrieve external data for a PivotTable, you can input the data directly into a PivotTable. We recommend that you retrieve external data for your reports by using ODC files.

OLAP source data     When you retrieve source data from an OLAP database or a cube file, the data is returned to Excel only as a PivotTable or a PivotTable that has been converted to worksheet functions. For more information, see Convert PivotTable cells to worksheet formulas .

Non-OLAP source data     This is the underlying data for a PivotTable or a PivotChart that comes from a source other than an OLAP database. For example, data from relational databases or text files.

For more information, see Create a PivotTable with an external data source .

Using another PivotTable as a data source

The PivotTable cache     Each time that you create a new PivotTable or PivotChart, Excel stores a copy of the data for the report in memory, and saves this storage area as part of the workbook file - this is called the PivotTable cache . Each new PivotTable requires additional memory and disk space. However, when you use an existing PivotTable as the source for a new one in the same workbook, both share the same cache. Because you reuse the cache, the workbook size is reduced and less data is kept in memory.

Location requirements     To use one PivotTable as the source for another, both must be in the same workbook. If the source PivotTable is in a different workbook, copy the source to the workbook location where you want the new one to appear. PivotTables and PivotCharts in different workbooks are separate, each with its own copy of the data in memory and in the workbooks.

Changes affect both PivotTables     When you refresh the data in the new PivotTable, Excel also updates the data in the source PivotTable, and vice versa. When you group or ungroup items, or create calculated fields or calculated items in one, both are affected. If you need to have a PivotTable that's independent of another one, then you can create a new one based on the original data source, instead of copying the original PivotTable. Just be mindful of the potential memory implications of doing this too often.

PivotCharts      You can base a new PivotTable or PivotChart on another PivotTable, but you cannot base a new PivotChart directly on another PivotChart. Changes to a PivotChart affect the associated PivotTable, and vice versa.

Changing the source data of an existing PivotTable

Changes in the source data can result in different data being available for analysis. For example, you may want to conveniently switch from a test database to a production database. You can update a PivotTable or a PivotChart with new data that is similar to the original data connection information by redefining the source data. If the data is substantially different with many new or additional fields, it may be easier to create a new PivotTable or PivotChart.

Displaying new data brought in by refresh      Refreshing a PivotTable can also change the data that is available for display. For PivotTables based on worksheet data, Excel retrieves new fields within the source range or named range that you specified. For reports based on external data, Excel retrieves new data that meets the criteria for the underlying query or data that becomes available in an OLAP cube. You can view any new fields in the Field List and add the fields to the report.

Changing OLAP cubes that you create      Reports based on OLAP data always have access to all of the data in the cube. If you created an offline cube that contains a subset of the data in a server cube, you can use the Offline OLAP command to modify your cube file so that it contains different data from the server.

Create a PivotTable to analyze worksheet data

Create a PivotChart

PivotTable options

Use PivotTables and other business intelligence tools to analyze your data

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

Table of Contents

Sample data, how to create pivot tables in excel, how to create pivot tables in excel: detailed walkthrough.

Pivot tables are among the most useful and powerful features in Excel . We use them in summarizing the data stored in a table. They organize and rearrange statistics (or "pivot") to draw attention to the valuable facts. You can take an extremely large data set and see the relevant information you need in a clean, concise, manageable way.

Become The Highest-Paid Business Analysis Expert

The sample data that we are going to use contains 448 records with 8 fields of information on the sale of products across different regions between 2013-2015. This data is perfect to understand the pivot table.

1. Insert Pivot Table

To insert a pivot table in your sheet, follow these steps:

• Click on any cell in a data set.
• On the Insert tab, in the Tables group, click PivotTable.

A dialog box will appear. Excel will auto-select your dataset. It will also create a new worksheet for your pivot table.

• Click Ok. Then, it will create a pivot table worksheet.

2. Drag Field

To get the total sales of each salesperson, drag the following fields to the following areas.

• Salesperson field to Rows area.
• Sales field to Values area.

3. Value Field Settings

By default, Excel gives the summation of the values that are put into the Values section. You can change that from the Value Field Settings. 

• Click on the Sum of Sales in the Values field.

• Choose the type of calculation you want to use.

4. Sorting By Value

• Right-click any Sales value and choose Sort > Sort Largest to Smallest.

5. Two-Dimensional Pivot Table

We can create a pivot table in various two-dimensional arrangements. Drag the following fields to the different areas

• Salesperson to Rows area.
• Region to Columns area.
• Sales to Values area.

6. Applying Filters to a Pivot table

Let’s see how we can add a filter to our pivot table. We will continue with the previous example and add the Year field to the Filters area.

You can see that it adds a filter on the top of the worksheet.

7. Grouping Data in a Pivot Table

Excel allows you to group pivot table items. To create the groups, execute the following steps:

• In the pivot table, select the data you want to group.
• Right-click and click on Group.

Now, your data is grouped.

https://www.simplilearn.com/ice9/free_resources_article_thumb/group2.JPG

8. Percentage Contribution in a Pivot Table

There are various ways to display the values in a table. One way is to show the value as a percentage of the total. 

• Add the sales field again to the values section.
• Right-click on the second instance and select % of Grand Total.

In this article, you’ve learned the basics of pivot table creation in Excel. You can see how simple it is to get started creating one and visualizing your data in many different ways. 

Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel course, which includes Power BI training

This Business Analytics course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

Do you have any questions for us? Feel free to ask them in this article’s comments section, and our experts will promptly answer them for you! 

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Recommended Reads

Business Intelligence Career Guide: Your Complete Guide to Becoming a Business Analyst

Business Analyst Interview Questions

How to Become a Business Analyst

Data Analyst Resume Guide

Role of a Business Analyst

Top Business Analyst Skills

Get Affiliated Certifications with Live Class programs

Business analyst.

• Industry-recognized certifications from IBM and Simplilearn
• Masterclasses from IBM experts

Executive Certificate Program in General Management

• The capstone project mentored by IIM Indore faculty
• PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

8 Ways to Insert a Pivot Table in Microsoft Excel

This post is going to show you all the different methods you can insert a pivot table in Microsoft Excel.

Pivot tables are the best way to quickly summarize and analyze large sets of data in Excel.

They are easy to use and you can have a summarized report for your dataset in a few quick clicks.

There are a lot of entry points for pivot tables, and they are all worth knowing.

Get your copy of the example workbook to follow along.

Create an Excel Table for Your Dataset

The first thing you need to know about pivot tables, is they work best when the source data is inside an Excel Table.

Tables are structured objects that contain data. When you have a tabular dataset, you should put it in a Table.

Here is an example dataset of product orders.

Before you put your data inside a table, you will need to make sure it is in the correct tabular format.

• The first row should contain column headings. This should be a short text describing the data contained below.
• There should be no blank column headings. Every column of data should have a name.
• There should be no blank columns or blank rows.
• There should be no subtotals or grand totals within the data.
• One row should represent exactly one record of data. In the example data, 1 row equates to the sale of one product in a customer’s order.
• One column should contain exactly 1 type of data. in the example data, the Item column only contains information on the name of the product.

• Select any cell inside your dataset.
• Go to the Insert tab.
• Click on the Table command.

This will open up the Create Table menu and you should see a green dashed line around your data to indicate what data will be included in the table.

• Make sure Excel has correctly identified the full range of your data. You can adjust this using the selection toggle with the up arrow.
• Check the My table has headers option. This ensures the first row will be placed in the table headings.
• Press the OK button.

Your data is now in an Excel Table. It will be very obvious as tables come with nice formatting that is automatically applied.

You should now give your table a meaningful name instead of the generic Table1 name.

• Select a cell inside your table.
• Go to the Table Design tab. This is a contextual tab and will only appear when you have the active cell inside a table.
• Type a new name into the Table Name input and press Enter .

You will now be able to reference this table name as the source data for your pivot tables.

Insert a Pivot Table with the Insert Tab

The Insert PivotTable command found in the Insert tab is the first method you should know about if you need to use pivot tables.

Follow these steps to insert a pivot table.

• Select a cell inside your data.
• Click on the top half of the PivotTable command. You can click on the bottom half for more advanced options.
• Make sure the Table/Range input has correctly identified your table or range for the data source.
• Choose either a New Worksheet or an Existing Worksheet location for the new pivot table.

You now have a new blank pivot table in Excel!

Insert a Pivot Table with a Keyboard Shortcut

Unfortunately, there is no dedicated keyboard shortcut to insert a pivot table, but you can use the Alt hotkeys.

When you press the Alt key, you will notice various letters appear in the ribbon commands. These allow you to navigate the ribbon commands from your keyboard.

Press Alt , N , V , T to access the From Table/Range command found in the insert PivotTable options.

The insert pivot table options will be the same from this point on.

Insert a Pivot Table with Quick Access Toolbar

If you are using pivot tables a lot, then you are going to want a way to quickly access the options to insert one.

This is exactly what the Quick Access Toolbar is for!

It’s a customizable selection of frequently used commands that are always available in your Excel app.

You can easily add any command found in the ribbon to your Quick Access Toolbar .

Follow these steps to add the From Table/Range command to your Quick Access Toolbar .

• Left-click on the bottom part of the PivotTable command.
• Right-click on the From Table/Range option in the PivotTable submenu.
• Choose the Add to Quick Access Toolbar options from the right-click menu.

This will add the From Table/Range command to your Quick Access Toolbar as shown above.

Anytime you need to create a pivot table you can use this command since it’s always visible!

These commands even come with Alt hotkey shortcuts based on the order they appear in the Quick Access Toolbar .

This example shows the command is placed in the 5th position of the toolbar, so you can press Alt + 5 to use the command.

Insert a Pivot Table with Table Design Tab

Since pivot tables are used so frequently with Excel Tables, they have included a pivot table command inside the Table Design tab.

Select a cell inside your table and the Table Design tab will appear in the ribbon.

Go to the Table Design tab and select Summarize with PivotTable .

Insert a Pivot Table with Quick Analysis

There is a really cool option called Quick Analysis . As the name suggests, it gives you quick access to various tools for data analysis.

Follow these steps to insert a pivot table with the Quick Analysis tools.

• Select your entire dataset. Select any cell in your data and press Ctrl + A to select the entire table.

When you select your entire data set, you will see the Quick Analysis icon appear in the lower right corner.

When you click on this, it will open a menu with access to Formatting , Charts , Totals , Tables , and Sparkline tools.

You can also use the Quick Analysis keyboard shortcut to select the data and open the Quick Analysis tools. Press Ctrl + Q to open the Quick Analysis tools. This can also be achieved if you right-click on the table and choose Quick Analysis from the menu.

• Click on the Quick Analysis tools icon.
• Go to the Tables tab in the pop-up menu.
• Select one of the PivotTable options. These give you a list of suggested prebuilt pivot tables based on your data.

You can hover over the various PivotTable options to see a preview of the pivot table that will result.

When you select any of the PivotTable options, it will insert the pivot table into a new sheet.

Insert a Pivot Table with Recommended PivotTables

Recommended PivotTables are a great way to create a prepopulated pivot table.

This will give you a list of suggested pivot tables you can preview and then insert into your workbook.

Follow these steps to insert a recommended pivot table.

• Select a cell inside your dataset.
• Click on the Recommended PivotTable command.

This will open the Recommended PivotTables menu which will show you a list of suggested pivot tables you might be interested in.

• Select a pivot table from the left-hand side. It will show a larger preview on the right.

This will insert the selected pivot table into a new sheet in your workbook.

Insert a Pivot Table with Analyze Data

Analyze Data is an artificial intelligence feature that will help you spot interesting insights about your data.

It allows you to query your data with natural language questions and also provides a selection of pivot table summaries and visuals to highlight trends and patterns.

Follow these steps to use the Analyze Data feature.

• Select a cell in your data.
• Go to the Home tab.
• Click on the Analyze Data command.

This will open the Analyze Data window pane on the right side.

• You can ask questions about your data using natural language.
• You can select from suggested questions about your data.
• There is a list of pivot tables and visuals in the Discover Insights section.

You’ll see various pivot tables available in the Discover Insights section which you can insert into the workbook.

You can easily spot the pivot tables as they will have a prominent Insert PivotTable button in the lower-left corner.

Click on the Insert PivotTable button and it will be added to a new sheet in your workbook.

Insert a Pivot Table with Table/Range Query

Power Query is a data extraction and transformation tool available in Excel, Power BI, and a few other Microsoft products.

Your data might not come in the exact format you need before you can analyze it inside a pivot table. But you can reshape your data using Power Query and then load it directly into a pivot table.

Follow these steps to load your data into Power Query.

• Select a cell inside your data source.
• Go to the Data tab.
• Click on the From Table/Range command in the Get & Transform Data section.

This will open up the Power Query editor in a new window. You’ll be able to apply any sort of transformation inside the Power Query editor.

When your data is in the required format, you can then load it directly to a pivot table.

• Go to the Home tab in the Power Query editor.
• Click on the lower half of the Close & Load button.
• Select Close & Load To from the options.

This will close the Power Query editor and an Import Data menu will pop up.

• Select PivotTable Report from the loading options.
• Choose where to load the pivot table. You can choose a location in an Existing worksheet or a New worksheet .

Now your transformed data will be available directly inside a pivot table.

This is a great option for adding new calculated columns to your reports!

Insert a Pivot Table with VBA

You might be looking for a way to automate building your pivot table reports.

This is where VBA programming may assist you in automating the procedure.

VBA is the scripting language built into Excel and will allow you to automate your time-consuming reporting activities.

Press Alt + F11 on your keyboard to open the VBA code editor.

Go to the Insert menu in the VBA code editor and choose Module from the drop-down list. This will create a new module where you can add your VBA code.

You can paste the above code into the editor.

It will create a pivot table named myPivotTableReport with the Item column in the Rows area and the Amount column as a sum in the Values area. The Amount column will also get a currency format applied.

Press Alt + F8 to open the Macro dialog box where you can run your code from.

Insert a Pivot Table with Office Scripts

There is also another option for automating your report solutions.

The latest version of Excel for Microsoft 365 includes a new TypeScript based programming language called Office Scripts.

The new Office Script language is now available in Microsoft 365 business plans and may be used from the online web version of Excel.

Open Excel Online and go to the Automate tab, then click on the New Script button to open the Office Script editor.

This will open the script editor on the right side of the Excel window with a blank script.

You can click on the generic name given to the script and rename it with a descriptive name so you can easily find and run it later.

The above code will create a new pivot table from the Orders table and populate it with the Item and Amount field.

Click the Run button to run the script and create a pivot table.

Conclusions

Pivot tables are a fundamental tool for data analysis in Microsoft Excel, and there are many ways to insert pivot tables.

There are options to insert blank pivot tables as well as suggested prebuilt pivot tables.

Options like Recommended PivotTables and Quick Analysis tools can even suggest pivot tables that might interest you based on your dataset.

Analyze Data takes things a step further and can create pivot tables based on natural language questions you ask.

Power Query can import data from external sources, or transform your data to a proper state ready for use in a pivot table. It also allows you to conveniently load the results directly to a pivot table.

You can also automate your pivot table report creation using either VBA or Office Scripts. The possibilities for automating your reporting are endless with these tools.

Which method for inserting pivot tables do you prefer? Do you know any other ways to create pivot tables? Let me know in the comments below!

About the Author

John MacDougall

Subscribe for awesome Microsoft Excel videos 😃

• Pivot Table Tips and Tricks You Need to Know
• Everything You Need to Know About Excel Tables
• The Complete Guide to Power Query
• Introduction To Power Query M Code
• The Complete List of Keyboard Shortcuts in Microsoft Excel
• The Complete List of VBA Keyboard Shortcuts in Microsoft Excel

Related Posts

The Complete Guide To Slicers And Timelines In Microsoft Excel

Aug 12, 2019

Do you want to make impressive looking and functioning Excel workbooks without...

Summarizing Text Data With Pivot Tables

Jan 13, 2019

Learn how to summarize text data in the values area of a pivot table using the data model and DAX formulas.

Create Amazing Key Performance Indicator Data Cards In Excel

Dec 19, 2018

Learn how to create some super cool key performance indicator (KPI) data cards in Excel to show off important metrics in your dashboards.

Get the Latest Microsoft Excel Tips

Follow us to stay up to date with the latest in Microsoft Excel!

SQL Server Database and Server Roles for Security and Permissions

By: Nivritti Suste   |   Updated: 2024-08-13   |   Comments   |   Related: > Security

SQL Server is one of the most used relational database management systems in many organizations. It is mainly used to store, manage, and retrieve data with ease. Apart from this, SQL Server is popular for data security, including encryption, data masking, and role-based access control.

Today, we will discuss role-based access control (RBAC) in SQL Server. Using RBAC, you can assign specific permissions to users according to their roles within the server. There are different types of roles in SQL Server, which can be confusing. Here, we will discuss the distinctions between SQL Server and Database roles, helping us to manage security more effectively.

Let's first understand the roles. There are two types of roles in SQL Server: 1) SQL Server Roles and 2) Database Roles.

What are SQL Server Roles?

SQL Server roles are predefined sets of permissions used to control access to server resources. They are created at the server level and typically assigned to logins or other server roles, which helps administrators manage permissions and security for the entire SQL Server instance. SQL Server roles are like Windows groups, allowing for easy management and assignment of permissions to multiple users.

Types of SQL Server Roles

There are three types of SQL Server roles: fixed server, user-defined server, and application.

Fixed SQL Server Roles -  Fixed server roles are predefined sets of server-level permissions that cannot be modified or deleted. These roles are created during the installation of SQL Server. This includes one of the important ' sysadmin ' roles, which has "God-level control" over the entire SQL Server instance, and other specialized roles like bulkadmin, dbcreator, diskadmin.

User-Defined SQL Server Roles - There are multiple instances when you need custom sets of permissions based on your business needs. Here, user-defined server roles come into the picture; these are not predefined roles. User-defined server roles will allow you to create custom sets of permissions based on your specific needs. These roles granted to logins or only other user-defined server roles provide more control over access to server-wide resources.

SQL Server Application Roles -  The above-mentioned roles are mostly assigned to individual users. This third type of role is like user-defined server roles called Application Roles. These roles are created for applications only and used by applications instead of any individual users. These special roles let applications borrow permissions for a short time to complete the task, keeping regular users and app users separate and safe.

Key Features of SQL Server Roles

• Scope : Server-wide
• Creation : Created at the server level
• Assignment : Assigned to logins or other roles
• Permissions : Control access to server resources (databases, logins, etc.)

Example: SQL Code to Create a SQL Server Role

• Create a SQL Server Role. Replace [role_name] with the desired name for your new server role.
• Assign the User to the Role. Replace [role_name] with the name you chose in Step 1 and [user_name] with the username you want to assign the role to.
• You need to have sufficient permissions (e.g., sysadmin server role) to create server roles and manage user memberships.
• This code snippet only creates the role and assigns the user. You'll need to grant specific permissions to the role itself to control user access within the server.

Example: Granting Permissions to the Role

You can use the GRANT statement.

This grants the "Connect to Server" permission to the newly created role. You can explore other permission options based on your needs.

How to Check Server Roles Using SSMS

• Open SSMS and connect to your SQL Server.
• In the Object Explorer , navigate to Security > Server Roles .
• Expand the Server Roles. You will see all the predefined and user-defined roles listed.

Alternatively, you can use SQL Query:

What are SQL Server Database Roles?

The Database Roles, as the name suggests, are specific to control databases and database objects. Unlike server roles, these roles are created and managed at the database level and can be assigned to database users and other roles within the same database they are created. These roles are a more controlled approach to managing permissions in a SQL Server instance as different users may have different levels of permissions.

Types of SQL Server Database Roles

There are also three types of database roles: fixed database, user-defined database, and application.

Fixed SQL Server Database Roles - Fixed database roles are like fixed server roles in that they cannot be modified or deleted. However, they are limited to the specific database in which they were created. The default fixed database role is ' db_owner' , which has full control over the entire database and other roles like db_accessadmin, db_backupoperator, and db_datareader.

User-Defined SQL Server Database Roles - User-defined database roles allow for the creation of custom sets of permissions within a specific database. These roles can be assigned to users or other user-defined database roles, allowing for more granular control over access to objects within that database.

SQL Server Application Roles - Like SQL Server roles, application roles at the database level are intended for use by applications rather than normal users. They enable applications to temporarily assume permissions and perform actions on behalf of the role, providing an added layer of security.

Key Features

• Scope : Database-specific
• Creation : Created at the database level
• Assignment : Assigned to database users or other roles
• Permissions : Control access to specific database objects (tables, views, etc.)

Example: SQL Code to Create a Database Role

• Create a Database Role
• [role_name]: The desired name for your new database role.
• [user_name]: The username who will own (own as in "be authorized by") the role. This user doesn't necessarily need to be the one assigned to the role.

This statement combines the CREATE ROLE and AUTHORIZATION clauses in a single line. The AUTHORIZATION clause specifies the user who will "own" the database role. This doesn't necessarily restrict who can be assigned to the role, but it determines who can manage the role's permissions later (e.g., adding/removing members and granting/revoking permissions to the role).

• Assigning a User to the Database Role
• [role_name]: The name of the database role you created.
• [user_name]: The username you want to assign to the database role.

This will grant the user the permissions associated with the database role.

• You need to have the db_owner role or equivalent permissions on the database to create database roles and manage user memberships.
• Remember to grant specific permissions to the database role itself to control user access within the database. You can use the GRANT statement for this purpose.

How to Check Database Roles Using SSMS

• In SSMS , navigate to the specific database you want to check.
• Right-click on " Security " and select " Roles ".
• This will show you a list of all the roles defined within that database.

Another way to check database roles with a system view:

• Open a new query window in SSMS.
• Use the below query to check all 'Database_Role.'

Roles Key Differences Brief

When to Use Which Role

• SQL Server Roles: To manage overall user access to the SQL Server instance and its resources.
• Database Roles: To grant granular permissions within specific databases based on user needs.

Best Practices for Using SQL Server and Database Roles

Follow these tips to keep things safe and organized when setting up who can access what in SQL Server:

• Limit Sharing: Only give roles what they need. Don't give extra access.
• Keep Checking: As things change, update roles so access stays right.
• Give Just Enough: Roles and users should only have what they need to do their job.
• Make Your Own Roles: Don't use predefined roles. Create ones that fit your needs.
• Roles for Jobs: Use roles for different jobs to keep things organized.
• Write it Down: Keep track of all the roles, so you don't get confused.
• Double Check: Look at the roles regularly to make sure everything is safe.

Understanding the difference between SQL Server roles and database roles is important to keep your SQL Server secure. SQL Server roles provide server-wide control, while database roles offer more controlled permissions within specific databases. By leveraging these roles appropriately, database administrators and SQL developers can enhance security, streamline permission management, and ensure users have the necessary access without compromising security.

• Check out these MSSQLTips.com Security tips .

About the author

Comments For This Article

Related Content

Understanding SQL Server fixed database roles

SQL Server Database Users to Roles Mapping Report

The Power of the SQL Server Database Owner

Nesting Database Roles in SQL Server

Implicit Permissions Due to SQL Server Database Roles

Retrieving SQL Server Fixed Database Roles for Disaster Recovery

List SQL Server Login and User Permissions with fn_my_permissions

Free Learning Guides

Learn Power BI

What is SQL Server?

Download Links

Become a DBA

What is SSIS?

Related Categories

Auditing and Compliance

SQL Injection

Surface Area Configuration Manager

Development

Date Functions

System Functions

JOIN Tables

SQL Server Management Studio

Database Administration

Performance

Performance Tuning

Locking and Blocking

Data Analytics \ ETL

Microsoft Fabric

Azure Data Factory

Integration Services

Popular Articles

Date and Time Conversions Using SQL Server

Format SQL Server Dates with FORMAT Function

SQL Server CROSS APPLY and OUTER APPLY

SQL Server Cursor Example

SQL CASE Statement in Where Clause to Filter Based on a Condition or Expression

DROP TABLE IF EXISTS Examples for SQL Server

SQL NOT IN Operator

SQL Convert Date to YYYYMMDD

Rolling up multiple rows into a single row and column for SQL Server data

Format numbers in SQL Server

Script to retrieve SQL Server database backup history and no backups

Resolving could not open a connection to SQL Server errors

How to install SQL Server 2022 step by step

SQL Server PIVOT and UNPIVOT Examples

How to monitor backup and restore progress in SQL Server

An Introduction to SQL Triggers

SQL Server Management Studio Dark Mode

Using MERGE in SQL Server to insert, update and delete at the same time

SQL Server Loop through Table Rows without Cursor