Create a Pivot Desk in Excel: A Step-by-Step Tutorial

The pivot desk is one in all Microsoft Excel’s strongest — and intimidating — capabilities. Pivot tables might help you summarize and make sense of huge information units.

Nevertheless, additionally they have a popularity for being sophisticated.

The excellent news is that studying find out how to create a pivot desk in Excel is far simpler than you could consider (belief me!).

I’m going to stroll you thru the method of making a pivot desk and present you simply how easy it’s. First, although, let’s take a step again and ensure you perceive precisely what a pivot desk is and why you may want to make use of one.

Desk of Contents

By immersing your self, you possibly can turn into proficient in creating pivot tables in Excel very quickly. Pair it with our package of Excel templates to get began on the appropriate foot.

Bear in mind, pivot tables aren’t the one instruments you should utilize in Excel. To be taught extra, check out our information to mastering Excel.

The aim of pivot tables is to supply user-friendly methods to shortly summarize massive quantities of information. They can be utilized to raised perceive, show, and analyze numerical information intimately.

With this info, you possibly can assist determine and reply unanticipated questions surrounding the info.

Listed here are 5 hypothetical situations the place a pivot desk could possibly be useful.

1. Evaluating Gross sales Totals of Completely different Merchandise

Let’s say you’ve gotten a worksheet that accommodates month-to-month gross sales information for 3 totally different merchandise — product 1, product 2, and product 3. You need to determine which of the three has been producing probably the most income.

A technique could be to look by way of the worksheet and manually add the corresponding gross sales determine to a working complete each time product 1 seems.

The identical course of can then be achieved for product 2 and product 3 till you’ve gotten totals for all of them. Piece of cake, proper?

Think about, now, that your month-to-month gross sales worksheet has 1000’s upon 1000’s of rows. Manually sorting by way of every essential piece of information may actually take a lifetime.

With pivot tables, you possibly can routinely combination all the gross sales figures for product 1, product 2, and product 3 — and calculate their respective sums — in lower than a minute.

Picture Supply

2. Displaying Product Gross sales as Percentages of Whole Gross sales

Pivot tables inherently present the totals of every row or column when created. That’s not the one determine you possibly can routinely produce, nonetheless.

Let’s say you entered quarterly gross sales numbers for 3 separate merchandise into an Excel sheet and turned this information right into a pivot desk.

The pivot desk routinely offers you three totals on the backside of every column — having added up every product’s quarterly gross sales.

However what in case you wished to seek out the share these product gross sales contributed to all firm gross sales, quite than simply these merchandise’ gross sales totals?

With a pivot desk, as a substitute of simply the column complete, you possibly can configure every column to provide the column’s share of all three column totals.

Let’s say three merchandise totaled $200,000 in gross sales, and the primary product made $45,000. You may edit a pivot desk to say this product contributed 22.5% of all firm gross sales.

To point out product gross sales as percentages of complete gross sales in a pivot desk, merely right-click the cell carrying a gross sales complete and choose Present Values As > % of Grand Whole.

Picture Supply

3. Combining Duplicate Knowledge

On this situation, you’ve simply accomplished a weblog redesign and needed to replace many URLs. Sadly, your weblog reporting software program didn’t deal with the change nicely and cut up the “view” metrics for single posts between two totally different URLs.

In your spreadsheet, you now have two separate situations of every particular person weblog put up. To get correct information, it is advisable mix the view totals for every of those duplicates.

Picture Supply

As an alternative of getting to manually seek for and mix all of the metrics from the duplicates, you possibly can summarize your information (by way of pivot desk) by weblog put up title.

Voilà, the view metrics from these duplicate posts will probably be aggregated routinely.

Picture Supply

4. Getting an Worker Headcount for Separate Departments

Pivot tables are useful for routinely calculating issues which you could’t simply discover in a fundamental Excel desk. A type of issues is counting rows that each one have one thing in frequent.

For example, let’s say you’ve gotten a listing of staff in an Excel sheet. Subsequent to the staff’ names are the respective departments they belong to.

You may create a pivot desk from this information that reveals you every division’s title and the variety of staff that belong to these departments.

The pivot desk’s automated capabilities successfully get rid of your process of sorting the Excel sheet by division title and counting every row manually.

5. Including Default Values to Empty Cells

Not each dataset you enter into Excel will populate each cell. If you happen to’re ready for brand new information to come back in, you might need numerous empty cells that look complicated or want additional clarification.

That’s the place pivot tables are available in.

Picture Supply

You may simply customise a pivot desk to fill empty cells with a default worth, akin to $0 or TBD (for “to be decided”).

For giant information tables, with the ability to tag these cells shortly is a helpful characteristic when many individuals are reviewing the identical sheet.

To routinely format the empty cells of your pivot desk, right-click your desk and click on PivotTable Choices.

Within the window that seems, test the field labeled “For Empty Cells Present” and enter what you’d like displayed when a cell has no different worth.

Picture Supply

On making a pivot desk, Toyin Odobo, a Knowledge Analyst, stated:

“Curiously, MS Excel additionally offers customers with a ‘Beneficial Pivot Desk Perform.’ After analyzing your information, Excel will suggest a number of pivot desk layouts that will be useful to your evaluation, which you’ll choose from and make different modifications if essential.”

They proceed, “Nevertheless, this has its limitations in that it could not at all times suggest the perfect association in your information. As a knowledge skilled, my recommendation is that you just maintain this in thoughts and discover the choice of studying find out how to create a pivot desk by yourself from scratch.”

With this nice recommendation in thoughts, listed here are the steps you should utilize to create your very personal pivot desk. However in case you’re searching for different methods to visualise your information, use Excel graphs and charts.

Step 1. Enter your information into a spread of rows and columns.

Each pivot desk in Excel begins with a fundamental Excel desk, the place all of your information is housed. To create this desk, I first merely enter the values right into a set of rows and columns, like the instance beneath.

Right here, I’ve a listing of individuals, their training stage, and their marital standing. With a pivot desk, I may discover out a number of items of knowledge. I may learn how many individuals with grasp’s levels are married, for example.

At this level, you’ll need to have a objective in your pivot desk. What sort of info are you making an attempt to glean by manipulating this information? What would you wish to be taught? This can assist you design your pivot desk within the subsequent few steps.

Step 2. Insert your pivot desk.

Inserting your pivot desk is definitely the best half. You’ll need to:

• Spotlight your information.
• Go to Insert within the high menu.
• Click on Pivot desk.

Notice: If you happen to’re utilizing an earlier model of Excel, “PivotTables” could also be underneath Tables or Knowledge alongside the highest navigation, quite than “Insert.”

A dialog field will come up, confirming the chosen information set and supplying you with the choice to import information from an exterior supply (ignore this for now).

It’s going to additionally ask you the place you need to place your pivot desk. I like to recommend utilizing a brand new worksheet.

You sometimes received’t must edit the choices except you need to change your chosen desk and alter the placement of your pivot desk.

When you’ve double-checked every little thing, click on OK.

You’ll then get an empty end result like this:

That is the place it will get somewhat complicated and the place I used to cease as a newbie as a result of I used to be so thrown off. We’ll be modifying the pivot desk fields subsequent so {that a} desk is rendered.

Step 3. Edit your pivot desk fields.

You now have the “skeleton” of your pivot desk, and it’s time to flesh it out. After you click on OK, you will notice a pane so that you can edit your pivot desk fields.

This is usually a bit complicated to have a look at if that is your first time.

On this pane, you possibly can take any of your current desk fields (for my instance, it will be First Title, Final Title, Schooling, and Marital Standing) and switch them into one in all 4 fields:

Filter

This turns your chosen area right into a filter on the high, by which you’ll phase information. For example, beneath, I’ve chosen to filter my pivot desk by Schooling. It really works similar to a traditional filter or information splicer.

Column

This turns your chosen area into vertical columns in your pivot desk. For example, within the instance beneath, I’ve made the columns Marital Standing.

Take into account that the sector’s values themselves are was columns and never the unique area title. Right here, the columns are “Married” and “Single.” Fairly nifty, proper?

Row

This turns your chosen area into horizontal rows in your pivot desk. For example, right here’s what it appears like when the Schooling area is about to be the rows.

Worth

This turns your chosen area into the values that populate the desk, supplying you with information to summarize or analyze.

Values could be averaged, summed, counted, and extra. For example, within the beneath instance, the values are a rely of the sector First Title, telling me which individuals throughout which instructional ranges are both married or single.

Step 4: Analyze your pivot desk.

Upon getting your pivot desk, it’s time to reply the query you posed for your self originally. What info had been you making an attempt to be taught by manipulating the info?

With the above instance, I wished to understand how many individuals are married or single throughout instructional ranges.

I subsequently made the columns Marital Standing, the rows Schooling, and the values First Title (I additionally may’ve used Final Title).

Values could be summed, averaged, or in any other case calculated in the event that they’re numbers, however the First Title area is textual content. The desk routinely set it to Depend, which meant it counted the variety of first names matching every class. It resulted within the beneath desk:

Right here, I’ve realized that throughout doctoral, decrease secondary, grasp, major, and higher secondary instructional ranges, these variety of individuals are married or single:

• Doctoral: 2 single
• Decrease secondary: 1 married
• Grasp: 2 married, 1 single
• Major: 1 married
• Higher secondary: 3 single

Now, let’s take a look at an instance of those similar rules however for locating the common variety of impressions per weblog put up on the HubSpot weblog.

Picture Supply

If you happen to’re in HR, working a enterprise, or main a small crew, managing staff’ holidays is important. This pivot desk means that you can seamlessly monitor this information.

All it is advisable do is import your staff’ identification information together with the next information:

• Sick time
• Hours of PTO
• Firm holidays
• Time beyond regulation hours
• Worker’s common variety of hours

From there, you possibly can kind your pivot desk by any of those classes.

2. Constructing a Finances

Picture Supply

Whether or not you’re working a venture or simply managing your individual cash, pivot tables are a superb instrument for monitoring spend.

The best funds simply requires the next classes:

• Date of transaction
• Withdrawal/bills
• Deposit/revenue
• Description
• Any overarching classes (like paid adverts or contractor charges)

With this info, I can see my largest bills and brainstorm methods to avoid wasting.

3. Monitoring Your Marketing campaign Efficiency

Picture Supply

Pivot tables might help your crew assess the efficiency of your advertising campaigns.

On this instance, marketing campaign efficiency is cut up by area. You may simply see which nation had the very best conversions throughout totally different campaigns.

This might help you determine techniques that carry out nicely in every area and the place commercials must be modified.

Picture Supply

Equally, drag and drop the fields you need to use for calculations or aggregations to the “Values” space.

• Customise the pivot desk. You may additional customise your pivot desk by adjusting the format, making use of filters, sorting, and formatting the info as wanted.

For extra visible directions, watch this video:

Copy a Pivot Desk

To repeat a pivot desk in Excel, comply with these steps:

• Choose all the pivot desk. Click on anyplace inside the pivot desk. It is best to see choice handles across the desk.

• Copy the pivot desk. Proper-click and choose “Copy” from the context menu, or use the shortcut Ctrl+C in your keyboard.

• Select the vacation spot. Go to the worksheet the place you need to paste the copied pivot desk.

• Paste the pivot desk. Proper-click on the cell the place you need to paste the pivot desk and choose “Paste” from the context menu, or use the shortcut Ctrl+V in your keyboard.

• Alter the pivot desk vary (if wanted). If the copied pivot desk overlaps with current information, you could want to regulate the vary to keep away from overwriting the present information. Merely click on and drag the nook handles of the pasted pivot desk to resize it accordingly.

By following these steps, you possibly can simply copy and paste a pivot desk from one location to a different inside the similar workbook and even throughout totally different workbooks.

This lets you duplicate or transfer pivot tables to totally different worksheets or areas inside your Excel file.

For extra visible directions, watch this video:

Kind a Pivot Desk

To kind a pivot desk, you possibly can comply with these steps:

• Choose the column or row you need to kind.

• If you wish to kind a column, click on on any cell inside that column within the pivot desk.
• If you wish to kind a row, click on on any cell inside that row within the pivot desk.

• Kind in ascending or descending order.

• Proper-click on the chosen column or row and select “Kind” from the context menu.
• Within the “Kind” submenu, choose both “Kind A to Z” (ascending order) or “Kind Z to A” (descending order).

Alternatively, you should utilize the type buttons on the Excel ribbon:

• Go to the PivotTable tab. With the pivot desk chosen, go to the “PivotTable Analyze” or “PivotTable Instruments” tab on the Excel ribbon (relying in your Excel model).

• Kind the pivot desk. Within the “Kind” group, click on on the “Kind Ascending” button (A to Z) or the “Kind Descending” button (Z to A).

Picture Supply

These directions will will let you kind the info inside a column or row in your pivot desk. Please keep in mind that sorting a pivot desk rearranges the info inside that particular area and doesn’t have an effect on the general construction of the pivot desk.

You may as well watch the video beneath for additional directions.

Delete a Pivot Desk

To delete a pivot desk in Excel, you possibly can comply with these steps:

• Choose the pivot desk you need to delete. Click on anyplace inside the pivot desk that you just need to take away.

• Press the “Delete” or “Backspace” key in your keyboard.
• Proper-click on the pivot desk and choose “Delete” from the context menu.
• Go to the “PivotTable Analyze” or “PivotTable Instruments” tab on the Excel ribbon (relying in your Excel model), click on on the “Choices” or “Design” button, after which select “Delete” from the dropdown menu.

Picture Supply

• Affirm the deletion. Excel could immediate you to verify the deletion of the pivot desk. Evaluate the message and choose “OK” or “Sure” to proceed with the deletion.

When you full these steps, the pivot desk and its information will probably be faraway from the worksheet. It’s vital to notice that deleting a pivot desk doesn’t delete the unique information supply or another information within the workbook.

It merely removes the pivot desk visualization from the worksheet.

Group Dates in Pivot Tables

To group dates in a pivot desk in Excel, comply with these steps:

• Be certain that your date column is within the correct date format. If not, format the column as a date.

• Choose any cell inside the date column within the pivot desk.

• Proper-click and select “Group” from the context menu.

Picture Supply

• The Grouping dialog field will seem. Select the grouping choice that fits your wants, akin to days, months, quarters, or years. You may choose a number of choices by holding down the Ctrl key whereas making picks.

Picture Supply

• Alter the beginning and ending dates if wanted.

• Click on “OK” to use the grouping.

Excel will now group the dates in your pivot desk primarily based on the chosen grouping choice. The pivot desk will show the summarized information primarily based on the grouped dates.

Notice: The steps could barely fluctuate relying in your Excel model.

If you happen to don’t see the “Group” choice within the context menu, you may as well entry the Grouping dialog field by going to the “PivotTable Analyze” or “PivotTable Instruments” tab on the Excel ribbon, choosing the “Group Discipline” button, and following the next steps.

By grouping dates in your pivot desk, you possibly can simply analyze information by particular time durations, akin to months, which might help you get a clearer understanding of tendencies and patterns in your information.

Add a Calculated Discipline in a Pivot Desk

If you happen to’re making an attempt so as to add a calculated area in a pivot desk in Excel, you possibly can comply with these steps:

• Choose any cell inside the pivot desk.
• Go to the “PivotTable Analyze” or “PivotTable Instruments” tab on the Excel ribbon (relying in your Excel model).
• Go to the “Calculations” group. Within the “Calculations” group, click on on the “Fields, Objects & Units” button and choose “Calculated Discipline” from the dropdown menu.
• The “Insert Calculated Discipline” dialog field will seem. Enter a reputation in your calculated area within the “Title” area.
• Enter the formulation in your calculated area within the “Method” area. You should utilize mathematical operators (+, -, *, /), capabilities, and references to different fields within the pivot desk.
• Click on “OK” so as to add the calculated area to the pivot desk.

The pivot desk will now show the calculated area as a brand new column or row, relying on the format of your pivot desk.

The calculated area you created will use the formulation you specified to calculate values primarily based on the present information within the pivot desk. Fairly cool, proper?

Notice: The steps could barely fluctuate relying in your Excel model. If you happen to don’t see the “Fields, Objects & Units” button, you possibly can right-click on the pivot desk and choose “Present Discipline Record.” They each do the identical factor.

Including a calculated area to your pivot desk helps you carry out distinctive calculations and get new insights from the info in your pivot desk.

It means that you can broaden your evaluation and carry out calculations particular to your wants. You may as well watch the video beneath for some visible directions.

Take away Grand Whole From a Pivot Desk

To take away the grand complete from a pivot desk in Excel, comply with these steps:

• Choose any cell inside the pivot desk.
• Go to the “PivotTable Analyze” or “PivotTable Instruments” tab on the Excel ribbon (relying in your Excel model).
• Click on on the “Discipline Settings” or “Choices” button within the “PivotTable Choices” group.
• The “PivotTable Discipline Settings” or “PivotTable Choices” dialog field will seem.
• Relying in your Excel model, comply with one of many following strategies:

• For Excel 2013 and earlier variations: Within the “Subtotals & Filters” tab, uncheck the field subsequent to “Grand Whole.”
• For Excel 2016 and later variations: Within the “Totals & Filters” tab, uncheck the field subsequent to “Present grand totals for rows/columns.”

• Click on “OK” to use the adjustments.

The grand complete row or column will probably be eliminated out of your pivot desk, and solely the subtotals for particular person rows or columns will probably be displayed.

Notice: The steps could barely fluctuate relying in your Excel model and the format of your pivot desk. If you happen to don’t see the “Discipline Settings” or “Choices” button within the ribbon, you possibly can right-click on the pivot desk, choose “PivotTable Choices,” and comply with the next steps.

By eradicating the grand complete, you possibly can give attention to the particular subtotals inside your pivot desk and exclude the general abstract of all the info. This may be helpful if you need to analyze and current the info in a extra detailed method.

For a extra visible clarification, watch the video beneath.