How to Analyze Data Using Excel PivotTables - spencernoblat1959
When you compile data in a number, you often penury to answer questions such as "How some revenue did the Westside Coast office sire last month?" or "What was the average count of customers served at each office in each quarter last year?"
Excel's PivotTables (Microsoft runs the words unitedly, although you?ll happen pivot tables in other spreadsheet programs) fanny provide those types of answers.
Do you deprivation to group data by category? Use a PivotTable. Interested in comparing results by person, by quarter, or by category? Use a PivotTable. Need to answer questions that start with "How many?" or "How much?" PivotTables can arrange that, besides. I?ll render you how to accomplish those tasks and more.
If you'd like to work through the examples I'll present in this article, you can download my Stand out spreadsheet.
What Is a PivotTable?
A PivotTable is an Excel tool around for summarizing a list into a simple format. You create PivotTables from lists, American Samoa you delineate which William Claude Dukenfield should be placed in columns, which W. C. Fields should become rows, and what information you wish to summarize. You preceptor't have to use all of the data in a spreadsheet?fair-and-square the data and the Fields you need to answer your questions. In one case you've created the table, you can then see the answer to your question. You can late reuse the PivotTable to answer unusual questions by rearranging it.
Create a PivotTable
Before creating a PivotTable, consider the questions you want to answer, Beaver State which information you wish to extract from your data. This step will determine how you should make the set back.
Lashkar-e-Taiba's consider the data in this worksheet, and the question "How such did we earn from Development for each quarter in 2011?"
To create the PivotTable, click somewhere in the name of data, choose the Insert tab, and click PivotTable. Excel wish automatically select the area containing the data, including the headings. If it does non select the country correctly, drag over the area to select it manually. Placing the PivotTable happening a new sheet is high-grade, so click New Worksheet for the location and then sink in Okeh.
You might become confused at this point, because if you've never created a PivotTable, nothing you construe with on this screen leave look familiar. In reality, it?s simpler than it looks. The PivotTable Branch of knowledg List control panel, as its name suggests, contains the fields from your list; all you need to do is to arrange them in the boxes at the foot of the board. Once you?ve done that, the diagram happening the left becomes your PivotTable.
In the PivotTable Discipline Listing on the right side of the screen, dawn the Quarter, Category, and Feescheckboxes; this is the data you need to answer the motion "How much did we earn from Development for each quarter in 2011?" When you do this, Excel creates a PivotTable for you. The data might not be arranged on the dot to your liking, but that's an easy fix.
If you want to see the categories as columns and the quarters as rows, for illustrate, drag Draw and quarter to the Row Labels boxful and drag Category to the Column Labels corner. You can now read the answer to your question in the PivotTable, because column B now contains the results for fees earned from Development for the four quarters individually, as well as the grand add for the twelvemonth.
Next Paginate: How to Format a PivotTable
Format a PivotTable
Although the PivotTable answers the question, information technology could be formatted more neatly. To format the numerical data, right-click a value and choice Number Initialize.
Over in the Category list, prefer Number, and and then band up your desired keep down format; in my example, I added a thousand centrifuge and set decimal places to zero. Get through OK, and Excel formats the data?all of the numbers game in the shelve?accordingly. To relieve oneself the PivotTable itself Sir Thomas More attractive, click inside it, take PivotTable Tools > Design, and get through a style in the PivotTable Styles Gallery.
Answer Multiple Questions With One PivotTable
Having created a PivotTable, you can now employ that synoptic tabular array to answer otherwise questions. For example, to respond the question "How much did Davidson earn for Growing for each quarter?" click in the table to redisplay the PivotTable Field List, and click Consultant to sum up IT to the Wrangle Labels box. If the PivotTable Theater List disappears from the screen, right-click inside the PivotTable and click Show Field Tilt.
Even though you can read the answer already in the PivotTable, you can also filter the PivotTable to hide unwanted data. Select one of the consultants' names, then click the Row Labels drop-down arrow in the PivotTable. Deselect the checkboxes for completely merely Davidson. If you see the quarters listed in the drop-down box seat, perish back and choose a consultant before clicking the drop-down pointer.
Now opt one of the categories (Development or Support), click the Column Labels overleap-thrown pointer, and deselect near Development. This action filters the PivotTable then that it shows only the data that answers the question. In this elbow room, you bum use your created PivotTable complete and terminated again to do all the questions you have about your information.
Perform Smarter Filtering With Slicers
If you have Stand out 2010, you can use the newborn Slicers instrument to make your PivotTable even easier to work with. Slicers let you select the data to show in the PivotTable in a more sensory system way. To add a Slicer, click at heart the PivotTable, and pick out PivotTable Tools > Options > Insert Slicer. Click the checkbox for to each one Slicer to add to the worksheet, and click O.k.. In my case, I added them for Draw, Family, and Advisor.
To filter the PivotTable, click an particular in the Slicer to see that information in the PivotTable. To view multiplex items at in one case, Ctrl-get across to each one particular you want to see. The items you're showing will be colored in the Slicer. When the image in the top right of a Slicer is involved, IT indicates that the Slicer is filtering the data in the PivotTable; click the ikon to take off that filter out.
You buttocks format Slicers aside selecting a Slicer, choosing Slicer Tools > Options, and then clicking a Slicer Panach to use for each of the Slicers in bend.
Update a PivotTable When Information Changes
If you make whatever changes to the information in the original list from which you created the PivotTable, you need to update the PivotTable to reflect these changes. To do so, just right-click the PivotTable and sink in Refresh.
Next Page: Create a PivotChart Based on Your PivotTable
Create a PivotChart Based on Your PivotTable
You can easily chart the data from your PivotTable; doing so creates a PivotChart that Excel mechanically links to the PivotTable. Any changes you make in the chart's organization will personify echolike in the PivotTable, and contrariwise.
To make up a PivotChart, click anywhere in the PivotTable, choose Cut-in, and select a Column graph (a simple chart will turn best). The PivotChart leave appear in the sheet aboard the PivotTable, and it will presentation the data from the PivotTable.
When you later update the data in the PivotTable by exploitation Slicers, or by changing the board layout, the chart testament automatically modification to match.
Practice Down to View the Data
PivotTables have a shapely-in Mandrillus leucophaeus-falling sport. If you want to explore a particular value in your PivotTable, much equally a total, double-get across it. When you do, you'll see a inexperienced sheet containing all the information that contributed to the value you double-clicked. This sheet allows you to dissect the data and understand IT better.
Each time you three-fold-click a value in a PivotTable, a new sheet appears in your workbook. It can get crowded with these extra sheets if you don't bump off them when you are up with them. To do so, right-chatter a sheet's tab and click Cancel.
Use Other Calculations happening Your PivotTable Data
By default, Excel sums the value fields in a PivotTable, but you can modify this setting indeed that information technology makes other calculations, such as the maximum, minimum, and average. For instance, if you wish to see the average of the values in the PivotTable, right-click an item in the information area, choose Summarize Values By, and click Average. The heading in the top left of the table will change to indicate that you are viewing the average values, and Excel will recalculate the PivotTable values to usher averages. To untie the change and see the information totaled, moral-click a value, opt Sum up Values By, and click Sum.
In that discussion I've but scraped the surface of what you bum reach with PivotTables and PivotCharts in Excel. With knowledge of these alkalic tools, however, you can easily create a PivotTable to summarize and analyze your possess data.
If you liked this article, you might also be interested in these stories:
How to Create Advanced Microsoft Surpass Spreadsheets
Work Faster in Microsoft Stand out: 10 Secret Tricks
Make Data Unveiling Easier in Microsoft Excel: 10 Tricks
10 Secrets for Creating Awe-inspiring Excel Tables
Note: When you purchase something after clicking links in our articles, we may earn a tiny commission. Read our affiliate relate policy for more details.
Source: https://www.pcworld.com/article/460664/how_to_analyze_data_using_excel_pivottables.html
Posted by: spencernoblat1959.blogspot.com
0 Response to "How to Analyze Data Using Excel PivotTables - spencernoblat1959"
Post a Comment