You can use Slicers to have a better clarity on which items the data was filtered.Ĭlick ANALYZE under PIVOTTABLE TOOLS on the Ribbon.Ĭlick Insert Slicer in the Filter group. In such a case, using Slicers is a better option for filtering. However, how many items and / or which items are selected is not known from the report that is displayed. You can see that next to the Filter Region, Multiple Items is displayed, indicating that you have selected more than one item. The data pertaining to South and West Regions only will be summarized as shown in the screen shot given below − Select South and West and deselect the other values and click OK.Check the option Select Multiple Items.A drop-down list with the values of the field region appears. Sum of Order Amount Month-wise appears in the row Grand TotalĬlick the arrow in the box to the right of the filter region.Sum of Order Amount Salesperson-wise appears in the column Grand Total.Summarizing value is Sum of Order Amount.Region Filter appears on the top with default selected as ALL.The filter with the label as Region appears above the PivotTable (in case you do not have empty rows above your PivotTable, PivotTable gets pushed down to make space for the Filter. You can assign a Filter to one of the fields so that you can dynamically change the PivotTable based on the values of that field.ĭrag Region from Rows to Filters in the PivotTable Areas. In case Salesperson represents more than one area and you need to summarize the sales by Salesperson, then the second layout would have been a better option. Note − You can clearly observe that the layout with the nesting order – Region and then Salesperson yields a better and compact report than the one with the nesting order – Salesperson and then Region. The nesting order changes and the resulting PivotTable is as follows − In the PivotTable Areas, in rows, click region and drag it below salesperson such that it looks as follows − salesperson values are nested under region values. Region and salesperson in rows in that order.In the above layout options, you can observe that You can change the order by dragging the fields and observe how nesting changes. If you have more than one field in any of the areas, then nesting happens in the order you place the fields in that area. You can just drag the fields across these areas and observe the PivotTable layout as you do it. You can defer the update and get it updated only when you click on UPDATEĪn instant update helps you to play around with the different Layouts and pick the one that suits your report requirement.You can update it instantly as you drag the fields across areas, or.You can use the PivotTable Areas to accomplish this. You can even change the Layout of your PivotTable instantly. For example, if you want to display the account information instead of order amount information, deselect Order Amount and select Account. You can select / deselect them to instantly change your PivotTable to display only the information you want and in a way that you want. The headers in your data table will appear as the fields in the PivotTable. You can observe the PivotTable fields that was selected in the PivotTable fields list. The selected PivotTable appears on a new worksheet. Click the PivotTable Sum of Order Amount by Salesperson and month.Ĭlick OK.Click each of the PivotTable options to see the preview on the right side.In the recommended PivotTables dialog box, the possible customized PivotTables that suit your data are displayed. The Recommended PivotTables dialog box appears. In case you are new to PivotTables or you do not know which fields to select from the data, you can use the Recommended PivotTables that Excel provides.Ĭlick on Recommended PivotTables in the Tables group. Click New Worksheet to tell Excel where to keep the PivotTable.Ī Blank PivotTable and a PivotTable fields list appear.In the Table / Range Box, type the table name.The PivotTable dialog box appears.Īs you can see in the dialog box, you can use either a Table or Range from the current workbook or use an external data source. To create PivotTables, ensure the first row has headers. The easy way to handle these tasks is to create a PivotTable that you can dynamically modify to summarize the results the way you want. From this data, you might have to summarize total sales region wise, month wise, or salesperson wise. It involves summarizing the data, obtaining the needed values and presenting the results.Įxcel provides PivotTable to enable you summarize thousands of data values easily and quickly so as to obtain the required results.Ĭonsider the following table of sales data. Data analysis on a large set of data is quite often necessary and important.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |