Home > Excel Help Portal > Excel PivotTables

<<  (Page 2)  Previous              Next  (Page 2)  >>

Excel PivotTables

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses the use of PivotTables in
Microsoft Excel.

1. How to Create PivotTables from Multiple Consolidation Ranges In Microsoft Excel, you can create a PivotTable from multiple consolidation ranges. These ranges can be on the same worksheet, on separate worksheets, or even in separate workbooks. This article provides two examples of PivotTables created... KB#142589. Keywords: Charts, Plot, PivotTables

 
2. How to create PivotTables from Multiple Consolidation Ranges in Excel 2000 In Excel 2000, you can create a PivotTable from multiple consolidation ranges. These ranges can be on the same worksheet, on separate worksheets, or even in separate workbooks. This article provides two sample PivotTables that are created... KB#213947. Keywords: Charts, Plot, PivotChart, PivotTables

 
3. How to use a Web Data Source for a PivotTable in Excel 2000 This step-by-step article shows you how to refer to data that is on a Hypertext Transfer Protocol (HTTP) Web server when you create a PivotTable report. Microsoft Excel 2000 contains many Internet-related features, including Web queries,... KB#211908. Keywords: Query, SQL, Defined Name, PivotChart, PivotTables, Setup, File Name, Files, Text Files, Web, ROW

 
4. How to Analyze Questionnaire or Survey with a PivotTable PivotTables are analysis tools that you may find helpful in summarizing data from a questionnaire or survey. If you create a table of survey results in Microsoft Excel, a PivotTable is useful when the answers are Yes, No, and Undecided. This is... KB#151551. Keywords: Data Table, Format, PivotTables, ROW, COLUMN

 
5. How to analyze a Questionnaire or Survey with a PivotTable in Excel 2000 This step-by-step article shows you how to analyze a questionnaire or survey with a PivotTable. PivotTables are analysis tools that you may find helpful in summarizing data from a questionnaire or survey. If you create a table of survey results... KB#213920. Keywords: Data Table, Format, PivotTables, PivotChart, ROW, COLUMN

 
6. How to custom Sort or Prevent Auto Sort in PivotTable in Excel 2000 This step-by-step article describes how to custom sort a PivotTable or keep a PivotTable from being automatically sorted. In Microsoft Excel, data in a PivotTable is automatically sorted. There are no options available to turn this automatic... KB#235537. Keywords: Sorting, Format, Arrays, Formulas, PivotTables, PivotChart, VBA, Macro, Web

 
7. How to Use STDDEV or STDDEVP with a PivotTable The PivotTable STDDEV or STDDEVP functions provide standard deviation statistics against data. STDDEV is best used when data is a sampling of the overall data. STDDEVP, on the other hand, is for use when the information included is all the... KB#152984. Keywords: Data Table, Years, Forecasting, Sales, Statistics, PivotTables, ROW, COLUMN

 
8. How to Use the PivotTable AutoShow Feature In Microsoft Excel 97, you can use a new PivotTable feature called AutoShow to quickly analyze a large set of data to find specific results. For example, you can use the AutoShow feature to find the top ten salespeople per region based on... KB#162386. Keywords: Sorting, Sales, PivotTables

 
9. How to optimize PivotTable Performance in Excel 2000 This step-by-step article describes the options in steps 1 and 3 of the PivotTable Wizard, and explains how they affect memory usage, speed, and file size. When you create a PivotTable, Microsoft Excel creates a hidden copy (cache) of the source... KB#273583. Keywords: Query, PivotChart, PivotTables

 
10. How to index Data in a PivotTable in Excel 2000 The Index function in a PivotTable calculates a weighted average by using the following formula: ((value in cell) x (Grand Total)) / ((Grand Row Total) x (Grand Column Total)) When you use PivotTables to compare the relative importance of your... KB#213922. Keywords: Sales, Format, PivotChart, PivotTables

 
11. Excel -- Pivot Tables -- Show/Hide Items Show and Hide Items Show All Items -- Excel 2002 Each field button has an arrow that opens a dropdown list of items. Hide Items -- Excel 2002 You can hide all except one of the items in a field. Show All Items -- Excel 97/Excel 2000 In previous versions of Excel, where there is no Show All... Keywords: Charts, Download, Errors, PivotTables, PivotChart, Class, VBA

 
12. How to Use Grouping for Fields in a PivotTable In Microsoft Excel, you can group a field that is a date, a time, or a number. With date and time pivot fields, data in the table can be grouped into specific periods. With numeric pivot fields, data in the table can be grouped into evenly... KB#142153. Keywords: Days, Sales, PivotTables

 
13. How to Index Data in a PivotTable The INDEX function in a PivotTable calculates a weighted average by using the following formula: (value in cell) x (Grand Total)) / ((Grand Row Total) x (Grand Column Total)) When you use PivotTables to compare the relative importance of your row... KB#151515. Keywords: Query, Sales, Format, PivotTables, Toolbar, INDEX, ROW

 
14. How to use STDEV or STDEVP with a PivotTable in Excel 2000 This step-by-step article describes how to use the STDEV function and the STDEVP function with a PivotTable in Excel 2000. The PivotTable STDEV and STDEVP functions in Microsoft Excel calculate standard deviation based upon a set of data. You... KB#213914. Keywords: Years, Forecasting, Sales, PivotChart, PivotTables, ROW, COLUMN

 
15. "Unable to Read File" Error Message When You Open Excel File That Contains a PivotTable Report When you open an Excel workbook that contains a PivotTable report, you may receive the following error message: Unable to read file. When you click OK , you receive the following error message: Errors were detected in ' filename .xls', but... KB#819853. Keywords: Dates, Errors, Password, PivotTables, AutoSave, Corrupted, Error Message, File Name, Files, Web

 
16. Pivot Table Fields Do Not Function After Excel Repairs and Recovers File When you open an Excel workbook that contains a large pivot table, Excel may repair and recover the file. After the repair and recovery operation is completed, you receive the following message: Errors were detected in Filename.xls , but... KB#322238. Keywords: Dates, PivotTables, File Name, Files, Web

 
17. Data Fields Are Not Included in PivotFields Collection In Microsoft Excel, when you run a Microsoft Visual Basic for Applications macro that uses the PivotFields collection to return the fields in a PivotTable, the fields in the data area (DataFields object) are not... KB#213455. Keywords: Sales, PivotTables, PivotChart, VBA, Macro, Object, Web

 
18. Using Server Page Fields in PivotTables This article describes how to use server page fields to reduce the amount of data returned from an external data source to a PivotTable. This is useful when creating PivotTables from very large databases. NOTE : This article does not apply to... KB#211515. Keywords: Query, Third-Party, PivotChart, PivotTables

 
19. Limits of PivotTables in Microsoft Excel 2000 This article discusses some of the limitations of PivotTables in Microsoft... KB#211517. Keywords: Errors, PivotTables

 
20. How to Use Grouping for Fields in a PivotTable In Microsoft Excel, you can group a field that is a date, a time, or a number. With date and time pivot fields, data in the table can be grouped into specific periods. With numeric pivot fields, data in the table can be grouped into evenly... KB#214259. Keywords: Dates, Days, Sales, PivotChart, PivotTables, COLUMN, ROW

 
21. Limits of PivotTables in Microsoft Excel 2002 This article discusses some of the limitations of PivotTables in Microsoft... KB#291061. Keywords: Errors, PivotTables, Error Message

 
22. How to use the GetPivotData Worksheet Function in Excel 2000 This step-by-step article shows you how to use the GETPIVOTDATA function to retrieve data from a PivotTable. Microsoft Excel 2000 has a new worksheet function, GETPIVOTDATA, that allows you to work with PivotTable data outside of a... KB#211949. Keywords: Sales, PivotChart, PivotTables, GETPIVOTDATA, ROW, COLUMN

 
23. How to Create a Report Template In Microsoft Excel 97 there is a new feature called a report template. A report template is a Microsoft Excel template that includes one or more queries or PivotTables based on external data. When you save a report template, the external data is... KB#156449. Keywords: PivotTables, File Name, Templates

 
24. How to Display Data as Percentage of Total in Pivot Table In Microsoft Excel, you can display data in a PivotTable as a percentage of the data's total using the % Of Row and % Of Column calculation... KB#106359. Keywords: Sales, PivotTables

 
25. How to display Data as Percentage of the Total in a PivotTable in Excel 2000 This step-by-step article shows you how to display data as percentage of the total in a PivotTable in Microsoft Excel 2000. In Excel 2000, you can use the using the % of row and % of column calculation types to display data in a PivotTable as... KB#214057. Keywords: Sales, PivotChart, PivotTables

 
26. Time Calculation in Pivot Table May Not Be Accurate When you create a PivotTable in a new Excel 2002 workbook, the time calculation that is contained in the PivotTable is not calculated... KB#816997. Keywords: Dates, PivotTables, File Name, Files, Web

 
27. How to Use the GetPivotData Worksheet Function Microsoft Excel 97 has a new worksheet function, GetPivotData, that allows you to work with PivotTable data outside of a PivotTable. This article contains an example that uses the GetPivotTable function to retrieve data from a... KB#161538. Keywords: Sales, PivotTables, GETPIVOTDATA, ROW, COLUMN

 
28. How to change connection information for a query in Excel After you change the folder location or server name of a database that a query or PivotTable is using, you cannot manually change the connection information to the new folder or server. This article offers a programmatic... KB#269619. Keywords: Query, Arrays, Errors, PivotTables, VBA, Web

 
29. How to Change Connection Information for a Query After you change the folder location or server name of a database that a query or PivotTable is using in Microsoft Excel, you cannot manually change the connection information to the new folder or server. This article offers a programmatic... KB#816562. Keywords: Query, Arrays, PivotTables, VBA, Macro, Web

 
30. How to Use the PivotTable AutoSort Feature In Microsoft Excel 97, there is a new PivotTable feature called AutoSort. This feature allows you to automatically sort a PivotTable when you create it, or when you refresh a PivotTable after you change data in the... KB#156314. Keywords: Sorting, PivotTables, Toolbar

 
31. Pivot table pre-process macro Fr. Mark from Kansas sent in this week's question: Pivot tables work best when the data is broken down into the most records possible, but this is not always the most intuitive way to load the data. For example, it is intuitive to load data like figure 1, but the best way to analyze the data is... Keywords: Format, PivotTables, VBA, Macro, Web

 
32. Edits to PivotTables Do Not Bring Back New Columns If you create a PivotTable that contains grouped data, you may not be able to edit the data. You can go into query by means of the PivotTable Wizard and add or delete fields of data to be returned to the PivotTable. However, when you return the... KB#325207. Keywords: Query, Dates, PivotTables, File Name, Files, Web

 
33. How to create a Normal Chart from a PivotTable in Excel 2000 This step-by-step article describes how to create a normal chart (not a PivotChart) from PivotTable data. Ordinarily, if you use a PivotTable as the source data for your chart, Microsoft Excel always creates a PivotChart. To create a normal chart... KB#231421. Keywords: Charts, PivotChart, PivotTables, Object

 
34. Contents of PivotTable Disappear When Data Is Updated in a Different Language Version of Excel In Microsoft Excel, if you create a PivotTable that is based on multiple consolidation ranges, open the file in a different language version of Excel, and then update the data, the contents of the PivotTable... KB#316479. Keywords: PivotTables, File Name, Registry

 
35. PivotTable Updated Slowly with OLAP Data Source When you pivot or refresh a PivotTable, it takes a long time for Excel to update the PivotTable, and Excel may appear to stop... KB#237469. Keywords: SQL, PivotChart, PivotTables, Parse

 
36. Excel -- Pivot Tables -- GetPivotData GetPivotData Generate GetPivotData In Excel 2002, and later versions, when you type an equal sign, then click on a cell in a pivot table, you may see a GETPIVOTDATA formula, instead of a cell reference. If you prefer to use a cell reference, you can: or To add a Generate GetPivotData button: Click... Keywords: Download, Errors, Formulas, PivotTables, Toolbar, GETPIVOTDATA

 
37. Creating a database table from a summary table Many users are familiar with Excel's pivot table feature, which creates a summary table from a database table. But what if you want to perform the opposite operation? This document describes how to create a database table from a simple two-variable summary table.The worksheet below demonstrates.... Keywords: Charts, Sorting, Format, Formulas, PivotTables, PivotChart, Printing, Macro, VBA, Menu Command

 
38. Excel Stops Responding When You Drag an Item in the PivotTable Layout Wizard When you drag an item in the PivotTable and PivotChart Wizard - Layout dialog box, Microsoft Excel stops... KB#307809. Keywords: Dates, PivotChart, PivotTables, File Name, Files

 
39. Excel -- Pivot Tables -- Clear Items Clear Old Items Old Items Remain in Pivot Field Dropdowns The data in the pivot table source may change, and items that were previously in the source are no longer included. For example, some sales reps may leave the company, and the names of their replacements appear in the source table. Even... Keywords: Download, Sales, Errors, PivotTables, VBA, Macro

 
40. Excel -- Pivot Tables -- Field Settings Field Settings Manually Hide or Show Subtotals To manually hide subtotals for a field: To manually show subtotals for a field: Programmatically Hide Subtotals You can use a macro to hide subtotals in a PivotTable. This example uses the PivotField property, to hide all the subtotals. To hide only... Keywords: Download, Colors, PivotTables, VBA, Macro

 
41. "Error 2147417848" Occurs When Data Fields Are Added to a PivotTable from VBA A Microsoft Excel spreadsheet contains Microsoft Visual Basic for Applications (VBA) script to create and format a series of PivotTables. While the VBA script tries to add data fields to a PivotTable, the following error is received: Run-time... KB#317782. Keywords: SERIES, Dates, Format, Errors, PivotTables, Object, VBA, File Name, Files

 
42. Macro to print each page of a pivot table Raj asks: I have a pivot table set up with 150 different values in the page field. How can I run a macro to print each individual page? I tried recording the macro, but it hard codes the name of each page, and some of those pages may not be there from week to week. Visual Basic makes this a snap.... Keywords: PivotTables, Printing, VBA, Macro, Web, Window

 
43. Summarizing data with a pivot table MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as... Keywords: PivotTables, VBA, Web, ROW

 
44. File That Contains PivotTable Grows Each Time It Is Saved When you save, close, and reopen an Excel file that contains a PivotTable, the file may increase in size even if there are no changes to the data in the file. This problem occurs whether or not you save the pivot data with the file, and whether or... KB#316446. Keywords: PivotTables, File Name

 
45. Excel -- Pivot Tables -- Data Fields Data Field Layout 1. Arrange Multiple Data Fields 2. Rename Data Fields Arrange Multiple Data Fields If you place two fields in the Data area of a Pivot Table, they appear vertically arranged. To make the data easier to read, you can rearrange the table. Rename Data Fields When you add fields to... Keywords: Download, PivotTables

 
46. Data Input and Analysis Learn how to link named ranges, the Data Form, PivotTables and charts to produce an information system. Use a button to add data to the worksheet and a Vlookup formula to link Excel lists. You can link charts, PivotTables and Ranges of data to produce a basic information system. With a command... Keywords: Charts, Download, Format, Formulas, PivotTables, Links

 
47. PivotTable to Calculate Budget Variances A Visual Basic for Applications procedure useful in displaying Budget variances. The procedure hides columns in a PivotTable based on the values in the PivotTable data fields. When you create a Pivot Table field to show Budget Variance with the "% of" calclulation option, the PivotTable will show... Keywords: Download, Format, PivotTables, VBA

 
48. Pivot Table Multiple Consolidation Example Use the multiple consolidation range feature of PivotTables to summarize accounting data arranged in columns by month. Summarize the PivotTable by quarter using the built-in PivotTable grouping feature. Use the multiple consolidation range feature of pivot tables to summarize accounting data... Keywords: Query, Download, Format, PivotTables

 
49. PivotTables With Accounting Data This workbook demonstrates three pivot tables built using the multiple consolidation range option with source data in standard accounting format. The Pivot tables take advantage of Excel's ability to group dates in PivotTables. The powerful grouping capability of pivot tables can be used to bring... Keywords: Data Table, Dates, Download, Format, PivotTables

 
50. PivotTable Tutorial PivotTable, VLookup and Excel List Tutorial based on "Building Profits with Excel-lent Tools" by John F. Lacher, Maximize Magazine Special #1 November 1995 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... Keywords: Download, Format, PivotTables, Links

 

<< (Page 2)  Previous              Next  (Page 2) >>