Download Using Power Pivot Tables in Excel: A Replacement for Formulas and more Study notes Design in PDF only on Docsity! Using Power Pivot Tables in Excel to Replace Formulas Benefits of using pivot tables: Pivot Tables are worksheet tables that let you summarize and analyze your Excel data. Benefits include: Ability to recap using any data element and then drill down to review the details. Formulas may restrict sorting capabilities or may be corrupted when adding and deleting rows or columns. Ability to get a summary or recap of the data rather than scrolling down to find each subtotal. Data does not have to be sorted by a particular element in order to get a subtotal for that data element. Creating a simple pivot table: 1. Format data into a table. To do this, simply select the data in your table. The headers should be in the first row. Select Format as Table. 2. The dropdown arrows in the header row will allow you to sort the data and/or filter only the data you want to see. 3. Select Summarize with PivotTable Button under Table Tools (Design) 4. The create PivotTable Box should display. Make sure the table you want to summarize is in the table or range box. Choose where the pivot table should display. Using Power Pivot Tables in Excel to Replace Formulas 5. A dialogue box will appear with your table headers listed as fields. Drag the fields from the Field List to one of the bottom panels to create the PivotTable that captures the data you need. Using Power Pivot Tables in Excel to Replace Formulas 12. A box with my cost centers will now be visible. If I select any one of the cost centers, only that information will appear in the recap. In this example, I selected CN-Food Rebates. Even though this information is included with other data in my pivot recap as blank, I can still separate it using the slicer. 13. Multiple pivot tables can be inserted into one worksheet, to create a dashboard or overview of the data in multiple formats. See the picture below. I have a recap of my salary cost by general ledger code and I also have a count of the number of employees for each certification by cost center. I can view the entire list as shown under the AA or just the totals. Using Power Pivot Tables in Excel to Replace Formulas 14. Excel will also create recommended pivot tables. You may be able to use these with very little additional manipulation.
