Menu
homeGames

Chart from an excel pivot table. Pivot charts

Practical work No. 11

Lesson topic: Pivot tables and charts.

Purpose of the lesson: Gain practical skills in creating pivot tables and charts.

Lesson plan :

Creating a pivot table. Customizing the PivotTable Layout. Updating data in a pivot table. Change the format of fields in a pivot table. Create pivot charts. Creating a pivot table.

Create a new workbook.

Rename sheet1 to Sales.

On a sheet Sales Create a table like this:

Calculate the amount of sales. Sales amount = Price*Quantity.

Select any cell within the range of the source table.

Execute Data→PivotTable.

In the first window Pivot Table Wizard Click Further.

In the next window you need to specify the range of the source table. If the program does not do this, specify the range yourself. Click Further.

In the next window, specify to place the table on a new sheet and click Ready.

The PivotTable layout has been placed on a new sheet. Rename this sheet to Analysis.

Customizing the PivotTable Layout.

Go to sheet Analysis. And select one of the PivotTable layout cells.

Once you select a PivotTable layout cell, a toolbar should appear Pivot tables And the window List of pivot table fields.

Using the mouse, drag to the area Rows Field Name, To the columns area field Month, To the region Data elements Drag the field Sales amount.

After completing the above steps, the table should look like this:

Updating data in a pivot table.

When you update data in the source table, the data in the pivot table is not automatically updated.

Go to sheet Sales And change the value of the cell with any price to 55555.

Go to sheet Analysis.

Select a cell inside the PivotTable layout.

On the toolbar Pivot tables Click the update field button

Check if the data has been updated.

Note: To change the PivotTable report format, click the button on the toolbar Pivot tables.

Change the format of fields in a pivot table.

Select one of the PivotTable cells.

Right-click on it and select from the context menu Field parameters.

In the window In the field Operation Select Average.

Click the button Format.

In the window Cell Format Specify the currency format. Click OK.

Back at the window PivotTable Field Calculation click OK.

Exercise: Add a field to the rows area Price And install Operation - Amount;

Format: monetary.

Create pivot charts.

Pivot charts are a graphical display of changes in indicators of fields in pivot tables.

On the toolbar Pivot tables Click the icon Chart Wizard.

Excel automatically creates a new sheet with a ready-made PivotChart.

Go to the sheet with the pivot chart.

On the toolbar Diagrams Click the button Chart type.

In the list of diagrams, indicate Bar chart.

Control questions:

How to create a PivotTable report? How to set auto-format on a PivotTable report structure? How to change field parameters in a pivot table? How to update pivot table fields? How to create a pivot chart? How do I change the PivotChart type?

Conclusion: In this work, we studied ways to create pivot tables and charts. Having completed this work, we can say that the PivotTable report allows you to quickly summarize large amounts of data, swap rows and columns to obtain different summaries of the source data, filter the data by displaying different pages, or display details in the desired areas.


In this article, we'll look at techniques for creating and using a pivot chart.


A Pivot Chart is a chart that automatically combines and summarizes large amounts of data. You can build a pivot chart based on either a simple table or a pivot table.


First, we'll look at how to create a PivotChart from a PivotTable. For practical training, you can use our example by downloading it from the link (simple table.xlsx).


To create a pivot table based on data from a ready-made chart, follow these steps:


1. Select the pivot table you need by clicking on it;

2. On the tab Insert in Group Diagrams select the required chart type.




We chose a simple line graph. As a result, a ready-made graph appeared containing data from the pivot table, as well as a window:




Please note the window PivotTable filter area does not allow you to change the conditions for constructing a chart - that is, you cannot build a chart based on the columns of the main table (for example, using a column Sales volume, pcs.) that are not included in the summary table. And vice versa - including data in a pivot table is simultaneously reflected in the pivot chart:




Window PivotTable filter area designed for convenient management of a pivot table and a chart built on its basis:



By changing the values ​​of filters and axis fields, you can display the data segment of interest for specific coordinate axes values. For example, it is very convenient to analyze sales data in individual stores for the last week:




By default, a PivotChart is created on the same sheet where the PivotTable is located. This is not always convenient, so you can move the pivot chart to a new sheet using the command Move chart from the context menu. Setting up the format of a pivot chart is the same as a regular chart, but using commands from the tab group Working with PivotCharts, which open after clicking on the pivot chart.


As you already understood, a pivot chart built on the basis of an existing pivot table is closely related to it. This is not always convenient, so it often makes sense to immediately build a pivot chart based on the base table. To do this you need:


1. Select the data range we need (or place the cursor on the table we need - then Excel will automatically insert the entire table into the data range);


2. On the tab Insert in Group Tables select section Pivot table, and then point Pivot chart.

Date: March 16, 2017 Category:

Hello, friends. I want to dedicate this short post to Excel PivotCharts. This tool is undeservedly ignored by many users of the program. However, when coupled with PivotTables and using advanced functionality (such as slicers), PivotCharts can create a great user experience. And then, to receive the report in the desired form, the user will need only a couple of minutes, even when the table with the source data contains thousands of records.

Here's an example. I quickly added a pivot chart to the summary table with sales of managers by region. The entire process of creating a table and diagram took no more than a minute:

The chart is dynamic and will change as the pivot table changes.

How to build a pivot chart

To build a pivot chart, you need to perform the following sequence of actions:

  1. We build a pivot table that will be the source of data for the chart
  2. Select any table cell and click on the ribbon: Working with Pivot Tables – Analysis – Tools – Pivot Chart
  3. In the window that opens, select and click OK
  4. If necessary,

By the way, if you have Microsoft Office 2013 or higher, you can skip the first point. Just click on the ribbon Insert - Charts - PivotChart. The creation process will resemble the layout of a pivot table, however, the table will not be displayed. In earlier versions, however, you will have to first build a pivot table.

That's all, we have made a summary chart and now our reports have acquired a finished look, and calculations have become fast and easy!

In the next article, look forward to a very important topic -. If you are not familiar with him yet, read it, you won’t regret it. For example, I use it almost every day.

As always, I look forward to your questions and comments!

Diagrams

Charts are used to present series of numerical data in graphical form. They are designed to make it easier to understand large volumes of data and the relationships between different data series. To build a graph (diagram) based on the available data, you must: a) Select the data that will participate in constructing the diagram (you do not need to select cells that will be the names of the series and labels along the X axis) as shown in Fig. 115; b) on the “Insert” ribbon tab, click on any of the presented types of diagrams in the “Diagrams” group (Fig. 116). After this, a chart based on the selected data will appear on the sheet (Fig. 117).

The elements of the diagram are:

Chart area (1);

Chart area (2);

Data elements in the data series that are used to construct the chart (3);

Horizontal (category axis) and vertical (value axis) axes along which the diagram is constructed (4);

Chart legend (5);

Chart title and axis titles (6);

By default, the chart title and axis labels are not displayed on the chart. To display them, you need to select the diagram, and then on the “Design” ribbon tab, in the “Diagram Layouts” group, select a layout containing certain labels (Fig. 118).

To change the names of series and labels along the horizontal axis (category axis), you need to right-click on the chart area and select “Select data…” in the menu that opens. After this, the “Select Data Source” data selection window will open. On the left side of this window you can change the names of the rows. To do this, select the desired row and click the "Edit" button on the left side of the window. In the “Edit Series” window that opens (Fig. 119), in the Series name field, enter the required name of the series, or select the cell containing the name of the series. In our case, this is cell A2 (the original data is presented in Fig. 115). To change the labels along the horizontal axis (category axis), you need to click the "Change" button on the right side of the "Select data source" window and in the "Axis Labels" window that opens, select the range containing the labels. In our case, these are cells from B1 to F1 (the original data is presented in Fig. 115).

To change the type of chart after it has been built, just right-click on the chart and select “Change chart type...” from the menu that opens. Alternative option: Select the diagram and on the ribbon, on the “Design” tab, click on the “Change diagram type” button.

To move a diagram to a separate sheet, you need to right-click on the diagram and select “Move diagram…” from the menu that opens. In the “Move a diagram” window that opens, select the “On a separate sheet” item, change the name of this sheet if desired, and click the “Ok” button (Fig. 122).

Pivot tables

A PivotTable report allows you to summarize, analyze, explore, and present data from a spreadsheet. A PivotTable report can be especially useful when you need to summarize a large set of numbers, and combined data and subtotals allow you to look at the data from different angles and compare similar measures from different groups of data.

The example below is based on a table containing the costs of different types of office supplies for different departments for the period from 2009 to 2013.

Before you create a PivotTable, you must select the data range that will be used for the PivotTable. A range can be either a range of cells or multiple columns. The differences in choosing a range are as follows: if you select a limited data range, then when you add new data to the rows below the selected range, this data will not be taken into account in the pivot table. If you select columns, then when you add new data to the rows below, this data will be taken into account in the pivot table.

Important: The selected range must not contain empty rows or empty cells in the first row.

In Fig. 123 shows the source data from which the pivot table will be created. To create a pivot table, you need to select a data range (in this case, the range is A1:G10). After this, on the “Insert” ribbon tab, you need to click on the “Pivot Table” button (Fig. 124)

An empty pivot table will be created and the “List of Pivot Table Fields” will be opened (Fig. 126). At the top of this list are all the available PivotTable fields, and at the bottom is the table layout into which you can add these fields. To add a field to a table layout, you need to left-click on the field name in the list of fields and drag it into the appropriate layout container. Second option: right-click on the name of the desired field in the list of fields and in the menu that opens, select the command "Add to report filter", "Add to column names", "Add to row names" or "Add to values". It makes sense to add fields containing text values ​​or dates to the Row Titles or Column Titles containers, and numeric data to the Values ​​container.

Example No. 1. If you need to display the costs of each department for office supplies by year in the pivot table, then you need to add the “Department Number” field to the “Row Names” container, and the “Year” field to the “Column Names” container (or vice versa - it only depends on which will be displayed in the row or column names, respectively). You need to add the “Cost Amount” field to the “Values” container (Fig. 127). The result is a summary table shown in Fig. 128.

Rice. 128

Example No. 2. If you need to display the total costs of each department for each type of product in the pivot table, then you need to add the “Department Number” field to the “Row Names” container, and the “Product Name” field to the “Column Names” container (or vice versa - it only depends on this what will be displayed in the row or column titles respectively). You need to add the “Cost Amount” field to the “Values” container (Fig. 129). The result is a summary table shown in Fig. 130.

The type of operation used in the Values ​​container can be more than just sum. To select the type of operation, you need to left-click on the arrow in the name of the field in the “Values” container and in the menu that opens, select “Value Field Parameters…” (Fig. 131). In the “Value Field Parameters” window that opens, select the desired type of operation and click “Ok” (Fig. 132)

To hide/display the list of fields in a pivot table, right-click on the table, and in the menu that appears, select “Hide list of fields” or “Show list of fields” accordingly. If the source data has changed, the PivotTable can be refreshed to reflect the changes made. To do this, right-click on the table and select “Update” from the menu that appears. You can perform the same operations on the cells of a pivot table as on the cells of regular tables: apply various types of formatting, set filters, perform sorting, etc.

As can be seen from Figure 134, all data was selected from the source data whose department number is “Department 2” (row name in the pivot table) and product name is equal to “Pencils” (column name in the pivot table).

Pivot chart

The process of creating a pivot chart is almost identical to the process of creating a pivot table. First, select the range of data from which the pivot chart will be built, then on the “Insert” ribbon tab, click on the arrow in the corner of the “Pivot Table” button and select “Pivot Chart” (Fig. 135). In the New PivotTable and PivotChart dialog box, select the Select Table or Range option and check that the cell range is correct in the Table or Range box. Also, depending on where you want to place the created table, you need to select “On a new sheet” or “On an existing sheet” and click the “Ok” button (Fig. 136).

An empty PivotTable and an empty PivotChart are created, and the PivotTable Field List opens. The only difference between this list and the similar one when creating a pivot table is the names of the containers in the layout. So, instead of the “Row Titles” container, there is now “Axes (category) Fields”, and instead of the Column Titles container, there is now “Legend Fields (Rows)" (Fig. 137). The rest of the functionality remains the same: field names are added to the layout containers and on Based on them, a summary table and diagram are formed.

Important: when creating a pivot chart, the chart itself and the pivot table created with it are inextricably linked with each other, and when you change one, the other changes accordingly.

With pivot charts, you can perform the same set of actions as with regular ones: change the type, layout, style of the chart, add and change the names of the axes and the chart itself, transfer the chart to a separate sheet.

If you add both fields “Year” and “Department Number” to one container (for example, “Axes (category) fields”), the result will be the following pivot table (Fig. 141) and pivot chart (Fig. 142).

As can be seen from the figures, in this case, in the pivot table, the data is automatically grouped by department number, and within the groups the years appear, the amount of costs is displayed in one column. In the summary chart, the data is also grouped by department name on the category axis. Years are also displayed within the groups, and the data on the graph represents the total amount of costs for each department for a given year.

Creating a chart based on a PivotTable is very easy. Switch to mode Pivot chart(PivotChart View), and a pivot chart will appear on the screen, approximately the same as in Fig. 8.50.

Like a pivot table, it has a filter field - “Country”, which is displayed in the upper left corner of the screen, row and column fields, which are displayed here on the right and bottom. This pivot chart is closely related to the table. If you switch to PivotTable mode and change its structure, this change will be displayed in the PivotChart, and vice versa, if you change the structure of the PivotChart now, then this change will appear in the PivotTable when you switch back to that mode.

Rice. 8.50.

But you can create a diagram directly based on a query or table. In this case, a pivot table will be created at the same time. A PivotTable and a PivotChart are two forms of presenting the same data.

As an example, it is proposed to build a pivot chart for the query “Sales by Employees and Countries”.

  1. Open this query in Design view.
  2. The request has two parameters: [Start date] and [End date], which are used to filter data. For a PivotChart, these parameters are not needed, so first remove the expression from the line Selection condition(Criteria), then open the dialog box Options(Query Parameters) (see section "Queries with parameters" in Chapter 4) and remove both options.
  3. Click the arrow on the button View(View) toolbar and select the item from the menu Pivot chart(PivotChart View). A window will appear, the main part of which is occupied by the diagram display area (Fig. 8.51), limited by coordinate axes and marked with grid lines. In addition, you can see the filter area, which plays the same role and is located in the same way as in the pivot table, the category area and the row area, which correspond to the rows and columns of the pivot table. Fields whose values ​​should be plotted along the X (horizontal) axis are transferred to the categories area, and fields whose values ​​each correspond to one series of points or columns on the chart (depending on the type of chart) are transferred to the series area. These fields correspond to the column fields in a PivotChart. The data area contains fields whose values ​​will be displayed along the Y (vertical) axis of the chart.

Rice. 8.51.

  1. Drag the "Country" field from the list of fields to the filter area, the "Last Name" and "First Name" fields to the categories area, and the "Shipped Date" field to the rows area. By Month). Watch how the area of ​​the diagram changes.[ If you do not see the dialog box with a list of fields, click the button List of fields(Fields List) toolbar. ]
  2. Move the Sale Amount field to the data area and the chart is ready. Click the button Add legend(Show Legend) to display the legend, after which you will get the diagram shown in Fig. 8.52.
  3. You can also enter labels for the chart axes. Click on the inscription Axis name(Axis Title) under the X axis. Display the window Properties(Properties) and expand the tab Format(Format). Enter in the field Heading(Caption): Employees. Similarly, enter the label Sales Volume for the Y axis.