FREE DOWNLOADS SmartVBA | SmartVB6 | CodeVBA | CodeVB6 | Code Generator Pro | TraderXL Pro Package | Catalog | MORE..
Pivot Tables: Excel Pivot Table Examples, Downloads & Tutorials.
Introduction To Pivot Tables: Many Excel users are not familiar with, or are intimidated by Pivot Tables, one of the most powerful features in Excel.
What is a Pivot Table? As you'll shortly see, Pivot Tables can become quite complex. So we'll take it easy and just design a simple one.
Using a Pivot Table as Source for Chart: If you use a range of data as input to a pivot table -- and name that range " Database", the Data Form will add new records and include them in the named range. They will be included in the pivot table (after it is refreshed). You can link a chart to a pivot table so that changes in the pivot table automatically change the chart.
Pivot Table Multiple Consolidation Example: Use the multiple consolidation range feature of pivot tables to summarize accounting data arranged in columns by month. The pivot table data can be summarized by quarter using the built-in pivot table grouping feature. Each page of data included in the pivot table can be assigned a unique identifier in a new page field that is created when you define the pivot table.
Data Input and Analysis: You can link charts, PivotTables and Ranges of data to produce a basic information system. With a command button, you can use the built-in Data Form to add data to the range. If you name the range "Database", you can link the PivotTable to the named range. When you add new data, the range will expand and the new data will be included in the PivotTable. You can link a chart to the data range of the PivotTable and the chart will automatically change to show new values in the PivotTable.
PivotTable to Calculate Budget Variances: When you create a Pivot Table field to show Budget Variance with the "% of" calclulation option, the PivotTable will show Actual as a percent of Budget and Budget as a percent of Budget. You can create a Visual Basic for Applications procedure that will hide the Budget as a percent of Budget columns.
PivotTables With Accounting Data: The powerful grouping capability of pivot tables can be used to bring flexibility to the manipulation of financial totals by month, quarter, etc. If the source data is arrayed as columns of monthly accounting data, you can use the PivotTable Multiple Consolidation Range option to create a date field you can group by month, quarter or year.
Apply Styles to PivotTables: When you change a pivot table it will lose its formatting. You can create a Visual Basic for Applications procedure that will apply styles to each portion of a pivot table. If you use the Format, Styles option to create special styles, the Visual Basic for Applications procedure can apply those named styles to the areas of the PivotTable.You do not need to use a special procedure for PivotTables with standard AutoFormat styles.
PivotTable Tutorial: You can link named ranges and PivotTables. If you name the range "Database", you can add new records to the range with the built in Data Form and the named range will be extended to include the new records. If you use named ranges, Excel lists, PivotTables and Vlookup formulas, you can create a simple but powerful database system in an Excel workbook.
Creating an Automatic Copy of a Pivot Table: You can copy a PivotTable to another worksheet and manipulate the copy in ways not permitted in the PivotTable. With a few lines of VBA code, you can automatically update the PivotTable and update the copy. The PivotTable copy can be used to create special subtotals or formatting not allowed in the PivotTable.
Excel Pivot Tables Tips: List of tips
Microsoft®, Microsoft Excel®
are registered trademarks of Microsoft Corporation. OzGrid is in no way
associated with Microsoft or Google.
©2001-2007 ozgrid. All right reserved