You Need to Have a(N) _____ Before You Can Use a Pivot Table.

Lesson 27: Intro to PivotTables

/en/excel/inspecting-and-protecting-workbooks/content/

Introduction

When you have a lot of information, it can sometimes be difficult to analyze all of the information in your worksheet. PivotTables tin assistance make your worksheets more manageable by summarizing information and allowing you to dispense it in different ways.

Optional: Download our practice workbook.

Picket the video below to learn more well-nigh PivotTables.

Using PivotTables to respond questions

Consider the example below. Let's say nosotros wanted to respond the question What is the amount sold past each salesperson? Answering it could be fourth dimension consuming and difficult; each salesperson appears on multiple rows, and we would need to total all of their different orders individually. We could apply the Subtotal command to help find the total for each salesperson, but we would even so have a lot of data to work with.

Fortunately, a PivotTable can instantly summate and summarize the information in a mode that will brand it much easier to read. When we're done, the PivotTable will look something like this:

Once you've created a PivotTable, you tin can employ it to reply unlike questions past rearranging—or pivoting—the data. For example, allow's say we wanted to respond What is the total amount sold in each month? We could change our PivotTable to wait like this:

To create a PivotTable:

1. Select the tabular array or cells (including column headers) you want to include in your PivotTable.

   

2. From the Insert tab, click the PivotTable control.

   

3. The Create PivotTable dialog box will announced. Choose your settings, then click OK. In our instance, we'll use Table1 equally our source data and place the PivotTable in a new worksheet.

   

4. A blank PivotTable and Field List will announced in a new worksheet.

   

5. One time you create a PivotTable, y'all'll need to decide which fields to add. Each field is simply a column header from the source data. In the PivotTable Fields list, cheque the box for each field y'all want to add together. In our case, nosotros want to know the total amount sold by each salesperson, so nosotros'll check the Salesperson and Order Amount fields.

   

6. The selected fields will be added to one of the iv areas below. In our example, the Salesperson field has been added to the Rows expanse, while Order Amount has been added to Values. You can also drag and drop fields straight into the desired area.

   

7. The PivotTable volition calculate and summarize the selected fields. In our example, the PivotTable shows the amount sold by each salesperson.

   

Just similar with normal spreadsheets, you can sort the data in a PivotTable using the Sort & Filter control on the Abode tab. You can also apply any type of number formatting you want. For example, y'all may want to change the number format to Currency. However, be aware that some types of formatting may disappear when you lot modify the PivotTable.

If y'all modify any of the data in your source worksheet, the PivotTable will not update automatically. To manually update it, select the PivotTable and so go to Clarify > Refresh.

Pivoting information

Ane of the best things about PivotTables is that they can apace pivot—or reorganize—your data, allowing you to examine your worksheet in several ways. Pivoting data tin can aid you reply different questions and even experiment with your information to discover new trends and patterns.

To add together columns:

And so far, our PivotTable has only shown 1 cavalcade of data at a time. To testify multiple columns, you'll need to add a field to the Columns surface area.

1. Drag a field from the Field Listing into the Columns expanse. In our example, we'll employ the Month field.

   

2. The PivotTable will include multiple columns. In our example, there is at present a cavalcade for each person's monthly sales, in addition to the chiliad total.

   

To modify a row or column:

Changing a row or cavalcade can requite yous a completely different perspective on your data. All you have to practise is remove the field in question, then supplant it with another.

1. Elevate the field you want to remove out of its electric current surface area. You can also uncheck the appropriate box in the Field List. In this case, nosotros've removed the Month and Salesperson fields.

   

2. Drag a new field into the desired expanse. In our case, nosotros'll place the Region field nether Rows.

   

3. The PivotTable volition adjust—or pivot—to bear witness the new information. In our example, it now shows the amount sold by each region.

   

To larn more:

Once y'all're comfy with PivotTables, review our Doing More with PivotTables lesson for additional ways to customize and manipulate information.

Challenge!

1. Open up our practice workbook.
2. Create a PivotTable in a separate sheet.
3. We desire to reply the question What is the total amount sold in each region? To practice this, select Region and Order Amount. When yous're finished, your workbook should look like this:

   

4. In the Rows area, remove Region and supercede information technology with Salesperson.
5. Add Month to the Columns surface area.
6. Change the number format of cells B5:E13 to Currency. Note: You might have to brand columns C and D wider to see the values.
7. When you're finished, your workbook should expect similar this:

   

/en/excel/doing-more-with-pivottables/content/

lewisadmily.blogspot.com

Source: https://edu.gcfglobal.org/en/excel/intro-to-pivottables/1/

Share this post