Consulting | Excel Examples
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.
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.