What Data Is Good For Pivot Table

Pivot tables are one of the virtually powerful and useful features in Excel. With very fiddling effort, you tin utilise a pivot tabular array to build practiced-looking reports for large data sets. This article is an introduction to Pivot Tables and their benefits, and a step-by-step tutorial with sample data.

Grab the sample data and give information technology a attempt. Learning Pivot Tables is a skill that will pay you lot back again and once again. Pivot tables tin can dramatically increase your efficiency in Excel.

What is a pivot table?

Yous can remember of a pivot table as a report. Notwithstanding, unlike a static study, a pivot table provides an interactive view of your information. With very trivial try (and no formulas) you tin look at the aforementioned data from many dissimilar perspectives. You can group data into categories, interruption downward data into years and months, filter information to include or exclude categories, and even build charts.

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

Sample information
Insert Pin table
Add fields
Sort by value
Refresh data
2nd value field
Apply number formatting
Group by date
Add percent of total
Benefits summary
More resources

Pace past step tutorial

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

Sample data

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

Sample sales data already in an Excel Table

Data in a proper Excel Table named "Table1". Excel Tables are a neat way to build pivot tables, because they automatically accommodate every bit information is added or removed.

Notation: I know this data is very generic. Only generic data is practiced for understanding pivot tables – yous don't want to get tripped upwards on on a detail when learning the fun parts.

Insert Pivot Table

1. To get-go off, select any jail cell in the information and click Pivot Table on the Insert tab of the ribbon:

Click the button at Insert > Pivot Table

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

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

Create Pivot Table window

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

New empty pivot table staring at cell H4

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

Excel besides displays the PivotTable Fields pane, which is empty at this signal. Notation all five fields are listed, but unused:

Fields pane for new empty pivot table

To build a pivot tabular array, drag fields into one the Columns, Rows, or Values area. The Filters area is used to apply global filters to a pin 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 chip of practice. See below and as well here for more than examples.

Add fields

1. Drag the Sales field to the Values area.

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

Grand total of all data in data set

2. Drag the Color field to the Rows area.

Excel breaks out sales by Colour. You tin can see Blue is the elevation seller, while Ruby comes in last:

Breakdown by color

Discover the Grand Full 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 bespeak. You can see Color is a Row field, and Sales is a Value field:

Pivot table fields pane - sales by color

Number formatting

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

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

Right-click and select Number Format

ii. Utilize Currency formatting with zero decimal places, the click OK:

Currency number format with zero decimal places

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

Pivot table with Currency format applied

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

Sorting past value

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

Right-click and select Sort > Largest to smallest

Excel at present lists tiptop-selling colors first. This sort lodge will be maintained when data changes, or when the pin table is reconfigured.

Breakdown by color, top selling colors first

Refresh information

Pivot tabular array data needs to be "refreshed" in club to bring in updates. To reinforce how this works, we'll make a large change to the source information and lookout man it flow into the pivot table.

ane. Select cell F5 and modify $11.00 to $2000.

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

To update data, right-click and choose "Refresh"

Notice "Ruby-red" is now the top selling color, and automatically moves to the height:

Pivot table after data refresh

3. Modify F5 back to $eleven.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 lot can easily run into in the pivot tabular array. Try irresolute an existing color to something new, like "Gold" or "Black". When yous refresh, y'all'll see the new color appear. You lot can utilise undo to become dorsum to original data and pivot.

Second value field

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

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

Breakdown by color with Sales and Units

Percentage of full

There are unlike ways to display values. One selection is to show values as a percent of total. If you desire to display the aforementioned field in different means, add the field twice.

i. Remove the Units from the Values expanse

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

3. Right-click the 2d instance and cull "% of grand total":

Right-click select Show values as > percent of total

The result is a breakup past colour along with a percent of total:

Pivot table - breakdown by color with percentage

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

Here is the Fields pane at this point:

Pivot table fields pane - sales by color with percentage

Group by appointment

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

1. Remove the 2d Sales field (Sales2).

two. Drag the Date field to the Columns area.

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

Right click a date and select group

four. When the Group window appears, grouping by Years merely (deselect Months and Quarters):

Date grouping settings - group by Years only

We at present take a pivot table that groups sales by color and twelvemonth:

Two-way pivot table - sales by color and year

Notice at that place are no sales of Silver in 2016 and 2017. Nosotros can judge that Silver was introduced as a new colour in 2018. Pivot tables frequently reveal patterns in data that are difficult to see otherwise.

Here is the Fields pane at this point:

Pivot table fields pane - sales by color and by year

Two-way Pivot

Pivot tables can plot information in diverse two-dimensional arrangements.

1. Drag the Date field out of the columns area

ii. Drag Region into the Columns surface area.

Excel builds a two-mode pin table that breaks down sales by color and region:

Two-way pivot table - sales by color and region

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

Excel builds some other two-dimensional pin table:

Two-way pivot table - sales by region and color

Once more observe total sales ($26,356) is the same in all pivot tables above. Each tabular array presents a different view of the same data, and so they all sum to the aforementioned full.

The above case shows how quickly y'all tin can build unlike pivot tables from the same data. Y'all can create many other kinds of pivot tables, using all kinds of data.

Key Pin Tabular array benefits

Simplicity. Basic pivot tables are very simple to gear up upward and customize. In that location is no need to larn complicated formulas.

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

Flexibility. Dissimilar formulas, pin tables don't lock you lot into a detail view of your information. Y'all can quickly rearrage the pivot table to suit your needs. You can even clone a pivot table and build a split up view.

Accuracy. As long every bit a pin table is set upwards correctly, you tin can rest bodacious results are accurate. In fact, a pivot tabular array volition oft highlight bug in the data faster than any other tool.

Formatting. A Pin tabular array can apply automatically employ consistent number and style formatting, even as data changes.

Updates. Pivot tables are designed for on-going updates. If yous base of operations a pivot table on an Excel Table, the tabular array resize as needed with new data. All you need to do is click Refresh, and your pivot tabular array volition show you the latest.

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

Charts. One time yous accept a pivot tabular array, you can easily create a pin chart.

More than pivot table resources

Pin table examples
Pin tabular array tips
Pivot tables versus formulas
Pivot table grooming