What is an Excel PivotChart and how to get the most out of it

If you often work with data in Excel, you've probably felt overwhelmed at some point by thousands of rows and not quite knowing where to begin. In those cases, a dynamic chart or PivotChart It can be your best ally for seeing the whole picture at a glance, without getting lost in the numbers. This type of chart is powered by a pivot table and allows you to analyze, filter, and present information quickly and interactively.

Before we delve into dynamic graphics, it's important to be very clear about what they are. dynamic tablesWe'll explain what PivotCharts are and how to configure them, because a PivotChart is essentially the visual component of all that analysis. From there, we'll see how to create a dynamic chart from scratch, how to generate one from an existing pivot table, how to summarize and display the values, and also what limitations these charts have when creating professional presentations, along with some practical solutions.

What is a PivotChart in Excel?

A dynamic Excel chart, known as PivotChartA chart is a graphical representation of data from a pivot table. In other words, it's a chart "linked" to a pivot table that updates and changes based on the filters, fields, and layouts you apply to that table.

These charts are fully interactiveWhen you move fields between rows, columns, filters, or values ​​in the pivot table, the chart automatically adjusts. It also features dedicated field and filter buttons so you can change the view directly from the chart, without touching the table.

Unlike regular Excel charts, a dynamic chart is designed to explore and analyze datanot just for decoration. That's why it's so useful when you work with large volumes of data and need to see patterns, trends, or comparisons without writing complex formulas.

Relationship between pivot table and pivot chart

The foundation of every PivotChart is always a dynamic tableThis table is based on a range of data (or several related tables) and allows you to group, summarize, and reorganize information without modifying the source data.

In a pivot table, the fields are distributed into four main zones: Filters, Columns, Rows, and ValuesBy dragging fields to each of these areas, different summaries are built on the same dataset, without needing to write any formulas. The dynamic chart "inherits" this entire structure.

When you create a dynamic chart from that table, the series, categories and legends They are based directly on the fields the table has in Rows, Columns, and Values. If you change the table, the chart changes. If you filter or group dates in the table (by months, quarters, years, etc.), the chart updates to show exactly that view.

What is a pivot table and why is it so important for PivotCharts?

With a pivot table you can switch rows and columns on the fly that appear on screen, apply filters, group values, and recalculate totals in a matter of seconds. All this without having to build long or complicated formulas, something especially useful for users who often spend a lot of time creating repetitive reports.

Imagine, for example, that you have a sales list with thousands of records: date, country, region, city, product, salesperson, and amount. With a pivot table, you can retrieve this information in seconds. sales by countryThen you can view sales by region within each country, or sales by city and product, simply by dragging fields to different areas. From these views, you can create dynamic charts that visually display the same analysis.

Main uses of pivot tables in data analysis

Pivot tables are primarily used to create interactive reports and dashboardswhere the user can change filters and fields to answer different business questions. It's not just about adding data, but about being able to view it from different angles quickly.

A typical example is that of periodic sales reports: with a single source data table and a well-designed pivot table, you can View sales by country, product, salesperson, or period Without duplicating sheets or creating formulas for each report. Simply move fields and change filters.

These types of reports are the natural next step towards creating dynamic graphics that allow for the visual analysis of trends, comparisons between categories, temporal evolution, etc. Thus, what is a set of numbers in the table becomes a graph that can be read at a glance.

How to create a pivot table step by step (based on PivotChart)

1. Prepare or select the data source

The first step is to have data organized in the form of rows and columnswith no empty rows in between and with clear headers for each column. It can be an Excel table, a table-formatted range, or even data imported from a database.

For example, you could have a table with sales data from a computer store, with fields like invoice number, commercial name, date, model, brand and amountThat will be the basis on which the pivot table will generate the summaries.

2. Create the pivot table

With any cell selected within the data range, go to the tab Insert and click on “PivotTable”. Excel will automatically detect the entire range if the data is well-structured.

In the dialog box that appears, you can choose whether you want to place the pivot table report in a new spreadsheet or on an existing sheet. If you have the add-in. PowerPivotYou can also send that data to the data model to combine it with other related tables.

Once you accept, Excel will create an empty pivot table and display the panel on the right. List of fields, where you will see all the fields from the source table ready to be dragged to the different areas.

3. Configure the pivot table fields

In the pivot table fields panel you have four main areas where to place the fields:

• Filters: to apply a general filter to the entire report (for example, filter by year or by country).
• Columns: for fields you want to see as column headers without repetitions (for example, product brands).
• Rows: for the fields you want to list as rows (for example, businesses, products, or cities).
• Values: for the numeric fields to which they will be applied aggregation functions (sum, count, average, etc.).

By dragging and dropping fields into these areas, you shape the report. Excel, by default, applies the SUM function to the numeric fields you place in Values. If the field contains text or a mix of data types, it usually uses the function TELLThat's why it's important to keep the data types clean in your source table.

Depending on the combination of fields you choose, you can obtain completely different summaries on the same data source. For example, you could place the "salesperson name" field in Rows, "brand" in Columns, and "total amount" in Values ​​to see how much each salesperson has sold per brand. If you later swap salesperson and brand, you'll get a report structured in reverse, but with the same data.

Configure “Summarize values ​​by” in a pivot table

In the Values ​​area, you can change how the data is calculated. By clicking on the arrow to the right of the value field name By selecting “Value field settings”, a dialog box with several options opens.

In the tab “Summarize values ​​byYou can select the aggregation function you want to use: Sum, Count, Average, Max, Min, Product, Count of Numbers, StDev, Var, etc. Excel will automatically add the function name to the field name in the report, for example, "Sum of Amount," although you can change it in the "Custom Name" box.

In this same box you can access “Number format"To set the format for all values ​​in that field (currency, percentage, decimal number, etc.). This ensures that both the pivot table and the associated pivot chart display the data in a consistent and readable format."

Configure “Display values ​​as” for advanced calculations

In addition to summarizing, pivot tables allow you to transform the results using the “Show values ​​asFrom the same Value Field Settings window, on the corresponding tab, you can specify that the values ​​be displayed, for example, as:

• % of the overall total
• % of row or column total
• % of a selected element
• Difference from a base value
• % change from a base value

This is especially useful when you want to see, for example, what percentage each category represents of total sales instead of just the sum in euros. In the classic case of household expenses, you could transform the amounts for each category into percentages of the total to see what portion is allocated to housing, transportation, food, etc.

If you need to see both the absolute value and the percentage at the same time, you can drag the same numeric field twice In the Values ​​area: in one, leave it as Sum, and in the other, apply Show values ​​as “% of grand total”. This way, for each item, you will have the amount and the corresponding percentage in the same pivot table, and both can be used in the pivot chart.

Creating dynamic charts (PivotCharts) in Excel

Create a dynamic chart from a data table

If you don't already have a pivot table, you can create one. dynamic chart directly from the dataExcel will actually generate the pivot table behind the scenes and associate it with the chart. The basic steps are:

1. Select a cell within the table or data range.
2. Go to the tab Insert and choose the Dynamic Chart option (or the type of dynamic chart you want, depending on the version).
3. Specify where you want to place the dynamic chart (new sheet or existing sheet).
4. Click on Accept.
5. Select or drag the fields you want to appear in the dynamic chart fields panel.

In parallel, Excel will have created a associated pivot tableThis is what actually feeds the chart. When you modify that table (fields, filters, design), the chart will update automatically without you having to touch anything.

Create a dynamic chart from an existing pivot table

If you already have a well-configured pivot table, the process is even simpler. You only have to:

1. Select any cell within the pivot table.
2. Go to the tab Insert and click on “Dynamic Chart”.
3. Choose the type of chart (column, bar, line, pie, etc.).
4. Confirm with Accept.

The graph will remain automatically linked to that specific pivot table. Every design change, filter, or slicer you apply to the table will be instantly reflected in the chart. Similarly, the filter and field buttons displayed in the chart allow you to interact with the data without having to go to the table.

Pivot tables with multiple source tables and relationships

Excel allows you to build pivot tables from several related tablesThis is very useful when your data is in different tables of a relational database (for example, SQL Server, Oracle, or Access).

The basic steps To work with multiple tables in a pivot table, you need to:

Step 1: Import related tables
You import the different tables from the database, and you can do this all at once if you select several. These tables are added to the data model and can be related to each other.

Step 2: Add fields to the pivot table
In the list of fields you will now see all tables that you have imported. You can expand each one to see its fields and drag them to the Rows, Columns, Values, or Filters areas, even if they come from different tables, as long as they are correctly related.

Step 3: Create relationships if necessary
If Excel detects that a relationship between two tables is needed to complete the analysis, it will display a message indicating that you must create a relationshipIn that case, you define the relationship between key fields (for example, CustomerId in a sales table and CustomerId in a customer table) and, from there, the pivot table will be able to use fields from both tables without problems.

When these pivot tables are connected to dynamic graphicsYou can visually represent data from multiple tables, such as sales by geographic area by combining customer and sales data, or summaries by product using information from different sources.

Dynamic charts and segmentations for interactive analysis

Dynamic graphics work especially well when combined with segmentations (Slicers) and time scalesSegmentation allows you to visually filter information by fields such as country, product category, sales representative, etc., using buttons. Time scales are used to filter date periods (years, quarters, months, days) in a very intuitive way.

When applying a slicer to the pivot table, the dynamic chart updates instantly to show only the filtered data. This greatly simplifies exploring information in meetings, internal presentations, or ad hoc analyses, without having to modify formulas or recreate charts each time.

Limitations of Excel dynamic charts for presentations

Although PivotCharts are great as a tool for exploration and analysisThey have several limitations when you want to use them in formal presentations (for example in PowerPoint) or when you need a very polished and flexible graphic design.

The main limitations of dynamic graphics include:

• The graph is strongly linked to the pivot tableIf you want to remove a series only from the chart, you also have to remove it from the table, which may not be desirable for analysis.
• The layout of the graph follows the pivot table row and column structureIf you want to swap series and categories, you must change the rows and columns of the table, which can damage the sheet layout or even mess up other formulas.
• La grouping of dates What is useful in the table (e.g., months or quarters) may not result in a visually legible or aesthetically pleasing chart.
• Los number formats They don't always synchronize perfectly between the pivot table and the chart, generating small discrepancies in labels and values.
• The graph shows field and filter buttons which are very useful for analysis, but annoying when you want a clean chart for a slide.

Added to all this are their own format limitations of standard Excel and PowerPoint charts (inflexible axis labels, difficulty in highlighting certain points, etc.), which are even more noticeable when the data comes from a complex pivot table structure.

Reference tables as a solution to the limitations of PivotCharts

One very practical way to deal with these limitations is unlink the chart from the pivot table using an intermediate reference table. The idea is that you use the pivot table only to analyze and prepare the data, and then copy or link the result to another "flat" table that will serve as the source for a normal chart.

There are three main forms to create these reference tables:

• Copy and paste values ​​from the pivot table: You copy the results from the pivot table and paste them as values ​​in another area. This captures the current state of the analysis, ideal when the data is finalized and you don't expect any changes. You can continue to work on the pivot table without affecting the reference table.
• Use formulas that reference pivot table cells: This way, the reference table updates when the data in the pivot table changes (for example, when the data source is refreshed). The risk is that if you change the pivot table structure, the cell references will shift and become misaligned.
• Use the GETPIVOTDATA function: This function allows you to reference the data in the pivot table. by field name and elementSo, if the structure changes, the formulas usually continue to work. It's more powerful and robust, but also somewhat more complex to configure.

Once the reference table is ready, you can create a normal Excel chart Based on that table, with complete formatting freedom, no filter buttons, and without the rigidity of a pivot table structure. It's recommended to have the pivot table well-defined and the analysis finalized before creating the reference table to avoid having to recreate it multiple times.

Don't forget that pivot tables They do not update automatically When the data source changes, you must refresh the pivot table so that the reference tables and charts based on them are also updated.

Link pivot table and chart data to PowerPoint

When you have a chart based on a reference table or directly on a pivot table and you want to take it to Power pointYou can use different options of Special glue:

The main options are:

• Microsoft Excel chart object: Insert a chart with an embedded datasheet into PowerPoint. You can edit both the chart and the data directly from the presentation.
• Graph object of Microsoft Excel bound: The chart maintains a link to the original Excel file. When you update the data in Excel, the chart in PowerPoint is also updated.
• Image (JPG, PNG, etc.): Create a static snapshot of the chart in its current format, perfect if the data is no longer going to change.

Each option has its drawbacks: unlinked charts can become outdated if the data in Excel changes; linked charts can lose their connection if the file is moved or renamed; and images don't update if there are subsequent changes. If you use specialized visualization tools such as think cellYou can take advantage of more robust links between Excel and PowerPoint, both for charts and text, always remembering that the pivot table must be well refreshed.

Excel's dynamic charts are one of the key tools for transforming large data tables into clear, flexible and interactive reportsUnderstanding how pivot tables work, how to summarize and display values, their limitations in presentations, and the solutions using reference tables allows you to get much more out of Excel, whether for analyzing day-to-day information or for preparing periodic reports that are maintained with very little effort.