PivotTables in Excel 2013
Learn Basics ABout PivotTables in Excel 2013
PivotTables can help make your worksheets more sensible by summarizing data and permitting you to manipulate it in various ways. When you have a ton of information, it can now and then be hard to dissect the majority of the data in your worksheet.
Using PivotTables to answer questions
Suppose we needed to answer the question: What is the sum sold by each salesperson? for the business information in the case underneath. Noting this inquiry could be tedious and troublesome—every sales representative shows up on numerous columns, and we would need to add up to the greater part of their distinctive requests exclusively. We could utilize the Subtotal command to help locate the aggregate for every sales representative, yet we would, in any case, have a considerable measure of information to work with.
Luckily, a PivotTable can instantly calculate and summarize the information in a way that is both simple to peruse and control. When we’re set, the PivotTable will look something like this:
Once you’ve made a PivotTable, you can utilize it to answer distinctive inquiries by adjusting—or pivoting—the information. For instance, on the off chance that we needed to answer the question: What is the aggregate sum sold in each month? we could alter our PivotTable to resemble this:
To create a PivotTable:
- Select the table or cells (including column headers) containing the data you want to use.
- From the Insert tab, click the PivotTable command.
- The Create PivotTable dialog box will appear. Choose your settings, then click OK. In our example, we’ll use Table1 as our source data and place the PivotTable on a new worksheet.
- A blank PivotTable and Field List will appear on a new worksheet.
- When you make a PivotTable, you’ll have to choose which fields to include. Each field is just a column header from the source information. In the PivotTable Field List, check the case for each field you need to include. In our illustration, we need to know the total amount sold by each salesperson, so we’ll check the Salesperson and Order Amount fields.
- The chose fields will be added to one of the four territories underneath the Field List. In our case, the Salesperson field has been added to the Rows area, while the Order Amount has been added to the Values are. Then again, you can snap, hold, and drag a field to the coveted territory.
- The PivotTable will calculate and summarize the selected fields. In our example, the PivotTable shows the amount sold by each salesperson.
Much the same as with ordinary spreadsheet information, you can sort the information in a PivotTable utilizing the Sort and Filtercommand in the Home tab. You can likewise apply any compose of number formatting you need. For instance, you might need to change the Number Format to Currency. Notwithstanding, know that a few kinds of arranging may vanish when you change the PivotTable.
In the event that you change any of the information in your source worksheet, the PivotTable will not refresh naturally. To physically refresh it, select the PivotTable and after that go to AnalyzeRefresh.
To read more about Office Products visit WWW.OFFICE.COM/SETUP