Excel for slrfc.org 365 Excel for the internet Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More...Less

You deserve to usage a PivotTable to summarize, analyze, check out, and existing summary data. PivotCharts enhance PivotTables by including visualizations to the summary information in a PivotTable, and enable you to quickly watch comparisons, trends, and also patterns. Both PivotTables and PivotCharts enable you to make increated decisions around crucial data in your enterpincrease. You have the right to additionally connect to external information resources such as SQL Server tables, SQL Server Analysis Services cubes, Azure Marketlocation, Office Data Connection (.odc) records, XML documents, Access databases, and text papers to produce PivotTables, or use existing PivotTables to develop new tables.

You are watching: Which of the following best describes a pivot table?


Note: The screen shots in this post were taken in Excel 2016. If you have a different variation your check out could be slightly various, however unmuch less otherwise listed, the usability is the exact same.


About PivotTables

A PivotTable is an interactive means to conveniently summarize big quantities of data. You have the right to use a PivotTable to analyze numerical data in information, and answer unanticipated questions around your information. A PivotTable is especially designed for:

Querying big quantities of data in many user-friendly methods.

Subtotaling and aggregating numeric data, summarizing data by categories and also subcategories, and also creating custom calculations and formulas.

Expanding and collapsing levels of information to focus your outcomes, and drilling dvery own to details from the summary information for areas of interest to you.

Moving rows to columns or columns to rows (or "pivoting") to see different recaps of the resource information.

Filtering, sorting, grouping, and conditionally formatting the a lot of helpful and exciting subcollection of information permitting you to focus on simply the indevelopment you want.

Presenting concise, attractive, and annotated virtual or published reports.

For example, here"s an easy list of household expenses on the left, and also a PivotTable based upon the list to the right:

Househost price data

Corresponding PivotTable

*

*

For even more information, check out Create a PivotTable to analyze worksheet data.


Ways to work-related via a PivotTable

After you develop a PivotTable by choosing its data resource, arvarying fields in the PivotTable Field List, and also selecting an initial layout, you have the right to perform the following work as you job-related with a PivotTable:

Explore the data by doing the following:

Expand and collapse data, and display the underlying details that pertain to the worths.

Sort, filter, and also group areas and also items.

Change summary functions, and add custom calculations and formulas.

Change the form layout and also area plan by doing the following:

Change the PivotTable form: Compact, Outline, or Tabular.

Add, rearselection, and also remove fields.

Change the order of areas or items.

Change the layout of columns, rows, and also subtotals by doing the following:

Turn column and row field headers on or off, or display screen or hide empty lines.

Display subtotals over or listed below their rows.

Adjust column widths on refresh.

Move a column area to the row area or a row area to the column area.

Merge or unmerge cells for outer row and column items.

Change the display screen of blanks and also errors by doing the following:

Change exactly how errors and empty cells are presented.

Change how items and labels without information are presented.

Display or hide blank rows

Change the format by doing the following:

Manually and conditionally format cells and varieties.

Change the all at once PivotTable format style.

Change the number format for fields.

Include OLAP Server formatting.

For even more information, check out Design the layout and format of a PivotTable.


PivotCharts carry out graphical representations of the data in their connected PivotTables. PivotCharts are additionally interactive. When you produce a PivotChart, the PivotChart Filter Pane shows up. You deserve to use this filter pane to type and also filter the PivotChart"s underlying information. Changes that you make to the layout and data in an linked PivotTable are instantly reflected in the layout and information in the PivotChart and vice versa.

PivotCharts screen data series, categories, data markers, and also axes simply as conventional charts do. You can additionally adjust the chart form and other options such as the titles, the legend placement, the information labels, the chart area, and also so on.

Here"s a PivotChart based on the PivotTable instance over.

*

For more indevelopment, view Create a PivotChart.


If you are acquainted with standard charts, you will find that the majority of operations are the very same in PivotCharts. However, tbelow are some differences:

Row/Shaft orientation Unchoose a conventional chart, you cannot switch the row/column orientation of a PivotChart by making use of the Select File Source dialog box. Instead, you have the right to pivot the Row and Pillar labels of the linked PivotTable to attain the exact same result.

Chart types You can adjust a PivotChart to any type of chart kind except an xy (scatter), stock, or bubble chart.

Source data Standard charts are attached straight to worksheet cells, while PivotCharts are based on their linked PivotTable"s data source. Unfavor a traditional chart, you cannot readjust the chart data selection in a PivotChart"s Select File Source dialog box.

Formatting Most formatting—including chart facets that you add, layout, and style—is kept as soon as you refresh a PivotChart. However, trendlines, data labels, error bars, and various other transforms to data sets are not maintained. Standard charts execute not lose this formatting as soon as it is used.

Although you cannot directly resize the information labels in a PivotChart, you ca rise the message font size to successfully resize the labels.


You can usage data from a Excel worksheet as the basis for a PivotTable or PivotChart. The information should be in list format, through column labels in the first row, which Excel will certainly usage for Field Names. Each cell in succeeding rows have to contain information correct to its column heading, and you shouldn"t mix data species in the same column. For circumstances, you shouldn"t mix currency values and dates in the same column. Furthermore, tbelow shouldn"t be any blank rows or columns within the data selection.

Excel tables Excel tables are already in list format and are great candidates for PivotTable source information. When you refresh the PivotTable, new and updated data from the Excel table is immediately had in the refresh procedure.

Using a dynamic called range To make a PivotTable less complicated to upday, you can produce a dynamic called variety, and also usage that name as the PivotTable"s data source. If the called selection increases to incorporate even more information, refreshing the PivotTable will encompass the new information.

Including totals Excel automatically creates subtotals and grand totals in a PivotTable. If the resource data consists of automatic subtotals and grand also totals that you developed by utilizing the Subtotals command also in the Outline group on the Data tab, use that very same command also to rerelocate the subtotals and grand also totals before you produce the PivotTable.


You can retrieve data from an outside data source such as a database, an Online Analytical Processing (OLAP) cube, or a text file. For instance, you can preserve a database of sales records you want to summarize and analyze.

Office File Connection files If you usage an Office Data Connection (ODC) file (.odc) to retrieve external information for a PivotTable, you deserve to input the data straight right into a PivotTable. We recommfinish that you retrieve external data for your reports by making use of ODC files.

OLAP resource data When you retrieve resource information from an OLAP database or a cube file, the data is went back to Excel only as a PivotTable or a PivotTable that has been converted to worksheet functions. For more information, check out Convert PivotTable cells to worksheet formulas.

Non-OLAP source data This is the underlying information for a PivotTable or a PivotChart that comes from a source various other than an OLAP database. For instance, information from relational databases or text papers.

For more indevelopment, see Create a PivotTable through an external data resource.


The PivotTable cache Each time that you create a new PivotTable or PivotChart, Excel stores a copy of the information for the report in memory, and saves this storage location as component of the workbook file - this is called the PivotTable cache. Each new PivotTable calls for added memory and disk room. However, when you usage an existing PivotTable as the resource for a new one in the same workbook, both share the same cache. Because you reusage the cache, the workbook size is diminished and much less information is kept in memory.

Location requirements To usage one PivotTable as the resource for an additional, both should be in the same workbook. If the source PivotTable is in a various workbook, copy the resource to the workbook location where you want the brand-new one to show up. PivotTables and also PivotCharts in different workbooks are separate, each with its very own copy of the information in memory and in the workpublications.

Changes influence both PivotTables When you refresh the information in the new PivotTable, Excel likewise updates the data in the source PivotTable, and also vice versa. When you team or ungroup items, or produce calculated fields or calculated items in one, both are impacted. If you have to have a PivotTable that"s independent of one more one, then you deserve to produce a new one based upon the original data source, instead of copying the original PivotTable. Just be mindful of the potential memory implications of doing this also often.

PivotCharts You have the right to base a brand-new PivotTable or PivotChart on another PivotTable, however you cannot base a brand-new PivotChart directly on one more PivotChart. Changes to a PivotChart affect the associated PivotTable, and vice versa.


Changes in the resource information can lead to various information being available for analysis. For instance, you may desire to conveniently switch from a test database to a manufacturing database. You can upday a PivotTable or a PivotChart with new data that is equivalent to the original information link information by respecifying the resource data. If the data is dramatically various with many kind of new or extra fields, it might be much easier to produce a brand-new PivotTable or PivotChart.

Displaying new information lugged in by refresh Refreshing a PivotTable deserve to likewise adjust the data that is easily accessible for display. For PivotTables based upon worksheet data, Excel retrieves new areas within the resource variety or called variety that you specified. For reports based on exterior information, Excel retrieves brand-new information that meets the criteria for the underlying query or information that becomes obtainable in an OLAP cube. You can view any brand-new fields in the Field List and include the areas to the report.

Changing OLAP cubes that you create Reports based upon OLAP data always have actually accessibility to all of the information in the cube. If you produced an offline cube that contains a subset of the information in a server cube, you can usage the Offline OLAP command to modify your cube file so that it consists of various data from the server.

See more: Thinking About Financial Leverage Impacts The Performance Of The Firm By:


Need even more help?

You have the right to constantly ask an skilled in the Excel Tech Community or obtain assistance in the Answers neighborhood.