A Pivottable Report ________ Large Amounts of Data So You Can Analyze Related Totals.

Learning Outcomes

• Pivot tables

In Excel, the PivotTable tool creates ways to reorganize data in a spreadsheet. Reorganizing data in this way, brings about additional information and insights that foster meliorate understanding of your data. PivotTables allow y'all to forgo creating many summary calculations by hand, considering a PivotTable does the work for you.

The example below includes a multicolumn table of sales data containing but over a twelvemonth's worth of data. Looking at the raw information in the tabular array you may be able to pull out a few salient points, just with a pin table, you tin quickly answer questions like:

• What was the largest order by sales or by product quantity?
• What was the yard total sold of furniture for 2022 per region?
• Which sales region had the everyman sales figures?
• What sales representative had the highest total sales?

Practice Question

Now that you sympathise that PivotTables can be a powerful assistant with your Excel data, let'south await at a few examples and the ii main ways to create a PivotTable; the Recommended PivotTable push button, or by creating a PivotTable from scratch.

Create by Recommend PivotTables Push

If you are unfamiliar with pivot tables, this process is the recommended pick to use until you become more familiar with them. The dialog box displays diverse options for a given information set, giving you lot a range of choices so you tin select the one best suited for your information analysis.

Follow these steps to apply this tool:

1. Open an Excel spreadsheet with existing data, click on any prison cell inside the data table and click the Insert tab.
2. Click the Recommended PivotTables button in the Tables group. The entire table has been selected, indicated by the dotted line around the edge of the data table.
   1. Culling short-cut for data selection if the data set is large: While holding down the Shift + Ctrl keys, tap the right arrow key on your keyboard. And then while still holding down the Shift + Ctrl keys, click the downwardly arrow cardinal on the keyboard. All the data should now be selected in the entire data tabular array.
3. In the Recommended Pin Tables window, a diversity of Pivot Tables are available to be selected. Scroll through the options, select one and click OK. In this example, the Sum of Unit Sale by Region is selected.

Note

If you make up one's mind at this bespeak a bare PivotTable is preferable, select the Blank PivotTable button on the bottom left.

4. A new tab is opened containing the information of the selected PivotTable. Notice that the PivotTable will calculate the sum of unit sales per region in the table that was selected even without any of the total cost cavalcade filled out in the original data table.

5. This new PivotTable, summing upward the sales, is the first pace in rearranging the original data to be able to take a more in-depth analysis of the information. You can stop here or repeat the process and create other recommended tables.
6. If an additional PivotTable is desired, such as sales per sales rep, or sum of unit sales per particular and region, return to tab containing the original data, follow the same steps using the Recommended PivotTables push button, scroll until you meet the desired table, and select it. Here are two other examples of PivotTables created from the aforementioned data set.

Create by PivotTables Button

If yous are more familiar with pivot tables, or but wish to create one from the ground upwards, this button allows you select and reorganize the data even so you want to see the data interpreted. Follow these steps to create a PivotTable from scratch.

1. Open an Excel worksheet containing data for the PivotTable tool and select a cell anywhere in the information gear up.
2. Click the Insert tab, and the PivotTable button on the ribbon. Excel will automatically select the data it identifies as the information for this table.
   
   1. If the selected expanse missed data, starting time once more by clicking on the beginning data table cell, drag the cursor over all the desired data to select. Once the area is selected, click the PivotTable button under the Insert tab, Tables Group.
   2. Some other option to select the correct table data: Click on the PivotTable button and open the Create PivotTable dialog box. In the box under the "Choose the data you want to clarify" surface area, type in the table/range area for the table; for case 'Sales Orders'!$A$ane:$K$iv, or drag the cursor over the information area for the table and the range will be added to the Table/Range field.
3. After making sure the information selected is correct, select New Worksheet choice, and click the OK.
4. A new worksheet is created. On the right side of the worksheet, a PivotTable Fields chore pane is open. In it are four areas (Filters, Columns, Rows, and Values) where various field names can exist placed to create a PivotTable. The task pane also includes a checklist area of the fields from which to choose from the data.
   
5. Elevate one field name into different areas to create a PivotTable. Alternatively, you lot can check the boxes for fields to be added to the tabular array. Each of the areas operate in the post-obit fashion in a PivotTable:
   1. Columns: The filed used to measure out and compare information.
   2. Rows: The field for data you want to clarify.
   3. Values: The field containing the values a tabular array uses for comparisons.
   4. Filter (optional): A field used to sort table data. It is displayed in the upper left corner of a table and is an optional field for tables.
      
6. The PivotTable in the screenshot in a higher place is created based on the sales information of these fields added to these areas:
   1. Columns: Region
   2. Rows: Particular
   3. Values: Sum of Unit of measurement Sales
7. Rearrange fields in a variety of means past dragging them into a new surface area or clicking the option in the list of fields above the areas. Each action will affect the PivotTable. Move fields around into new areas until you have created a table giving you the best insight into your information. Congratulations! You accept created a PivotTable from scratch.

Note

Yous may run into scenarios where a information field is dragged into an expanse that does not work well. Just drag the field into another surface area and quickly see whether that field in the new expanse works better. Excel fabricated the PivotTable tool flexible so you tin can easily alter the structure of fields and areas if y'all make a misstep along the mode.

Exercise Question

Contribute!

Did yous have an idea for improving this content? We'd love your input.

Better this pageLearn More

sheridanlockonamind.blogspot.com

Source: https://courses.lumenlearning.com/wm-computerapplicationsmgrs-2/chapter/pivot-tables/

Share this post