Users create PivotTables to analyze, summarize, and present large amounts of data. Such an Excel tool allows you to filter and group information, display it in various sections (prepare a report).
The source material is a table with several tens and hundreds of lines, several tables in one book, several files. Recall the order of creation: "Insert" - "Tables" - "Pivot Table".
And in this article we will look at how to work with pivot tables in Excel.
How to make a pivot table from multiple files
The first step is to upload the information to Excel program and bring it into line with Excel spreadsheets. If our data is in Worde, we transfer it to Excel and make a table according to all the rules of Excel (we give headings to columns, remove empty lines, etc.).
Further work on creating a pivot table from several files will depend on the type of data. If the information is of the same type (there are several tables, but the headings are the same), then the PivotTable Wizard is to help.
We simply create a summary report based on data in multiple consolidation ranges.
Much harder to do pivot table based on source tables of different structure. For example, these:
The first table is the arrival of goods. The second is the number of units sold in different stores. We need to combine these two tables into one report to illustrate balances, store sales, revenue, and so on.
The PivotTable Wizard will generate an error with such initial parameters. Since one of the main conditions for consolidation is violated - the same column names.
But the two headings in these tables are identical. Therefore, we can combine the data and then create a summary report.
A draft Summary report opens with a List of fields that can be displayed.
Let's show, for example, the quantity of goods sold.
You can display different parameters for analysis, move fields. But the work with pivot tables in Excel does not end there: the possibilities of the tool are diverse.
Detailing Information in PivotTables
From the report (see above) we see that ONLY 30 video cards have been sold. To find out what data was used to obtain this value, double-click on the number "30". We get a detailed report:
How to refresh data in an Excel PivotTable?
If we change any parameter in the original table or add new record, this information will not be displayed in the summary report. This state of affairs does not suit us.
Data update:
The cursor should be in any cell of the summary report.
Or:
Right mouse button - refresh.
To customize automatic update pivot table when changing data, we do according to the instructions:
Changing the structure of a report
Add new fields to the pivot table:
After changing the range, the "Sales" field appeared in the summary.
How to add a calculated field to a pivot table?
Sometimes the user does not have enough data contained in the PivotTable. There is no point in changing the original information. In such situations, it is better to add a calculated (custom) field.
This is a virtual column created as a result of calculations. It can display averages, percentages, discrepancies. That is, the results various formulas. Calculated field data interacts with PivotTable data.
Instructions for adding a custom field:
Grouping Data in a Pivot Report
For example, consider the cost of goods in different years. How much money was spent in 2012, 2013, 2014 and 2015. Grouping by date in the Excel pivot table is done as follows. For example, let's make a simple summary by delivery date and amount.
Clicking right click mice for any date. Select the "Group" command.
In the dialog that opens, set the grouping parameters. The start and end date of the range are displayed automatically. Select the step - "Years".
We get the amount of orders by year.
In the same way, you can group data in a pivot table by other parameters.
Pivot tables are necessary for summarizing, analyzing and presenting data that are in "large" source tables, in various cuts. Consider the process of creating simple Pivot Tables.
Pivot tables (Insert/ Tables/ Pivot Table) can be useful if the following conditions are simultaneously met:
- there is an initial table with many rows (records), we are talking about several tens and hundreds of rows;
- it is necessary to carry out data analysis, which requires data sampling (filtering), their grouping (summing up, counting) and presenting data in various sections (preparation of reports);
- this analysis is difficult to carry out on the basis of the original table using other tools: ( CTRL+SHIFT+L), ;
- the source table satisfies certain requirements (see below).
Users often avoid using Pivot tables, because sure they are too complicated. Indeed, learning any new tool or method takes time and effort. But, as a result, the effect of mastering the new should exceed the invested efforts. In this article, we will understand how to create and apply Pivot tables.
Preparing the initial table
Let's start with the requirements for the original table.
- each column must have a heading;
- each column should only contain values in one format (for example, the Delivery Date column should contain all values in the format date; column "Supplier" - company names only in text format or you can enter the Supplier Code in numerical format);
- the table should not contain completely blank rows and columns;
- “atomic” values must be entered into the cells, i.e. only those that cannot be split into different columns. For example, you cannot enter an address in one cell in the format: "City, Street name, building number". You need to create 3 columns of the same name, otherwise pivot table will work inefficiently (if you need information, for example, in the context of the city);
- avoid tables with "wrong" structure (see figure below).
Instead of producing repeating columns ( region 1, region 2, …) that will have empty cells galore, rethink the table structure as shown in the figure above (All sales volume values should be in one column, not spread across multiple columns. In order to implement this, you may need to maintain more than detailed records(see figure above), rather than listing total sales for each region).
More detailed tips on building tables are set out in the article of the same name.
Makes the build process a little easier. pivot table, the fact that if the original ( Insert/ Tables/ Table). To do this, first bring the source table in accordance with the above requirements, then select any cell in the table and call the menu window Insert/ Tables/ Table. All fields in the window will be automatically filled in, click OK.
Now check the box next to the Sales field in the Field List.
Because If the cells in the Sales column are in numeric format, they will automatically be placed in the Values Field List section.
With a few mouse clicks (six to be exact) we created a Sales report for each Product. The same result could be achieved using formulas (see article).
If it is required, for example, to determine sales volumes for each Supplier, then to do this, uncheck the box next to the Goods field in the List of fields and check the box next to the Supplier field.
Drilling down on PivotTable data
If you have questions about what data from the original table was used to calculate certain values pivot table, then it is enough to double-click on a specific value in pivot table to create a separate sheet with rows selected from the original table. For example, let's see which records were used to summarize the sales of the Product "Oranges". To do this, double-click on the value 646720. A separate sheet will be created with only the rows of the original table related to the “Oranges” Product.
Update a PivotTable
If after creating pivot table new records (rows) were added to the original table, these data will not be automatically taken into account in pivot table. To update pivot table select any of its cells and select the menu item: menu Working with PivotTables/ Options/ Data/ Refresh. The same result can be achieved through the context menu: select any cell pivot table Refresh.
Deleting a PivotTable
Delete pivot table possible in several ways. The first is to simply remove the sheet from pivot table(unless it has other useful data, such as the original table). The second way is to delete only itself pivot table: select any cell pivot table, click CTRL+ A(all will be selected) pivot table), press the key Delete.
Changing the Totals Function
While creating pivot table grouped values are summed by default. Indeed, when solving the problem of finding sales volumes for each Product, we did not care about the totals function - all Sales related to one Product were summed up.
If it is required, for example, to count the number of sold batches of each Product, then the totals function must be changed. For this in pivot table Totals per/ Quantity.
Changing the sort order
Now let's modify our Consolidated report. First, let's change the sort order of the names of the Goods: sort them in reverse order from Z to A. To do this, through the drop-down list at the head of the column containing the names of the Goods, enter the menu and select Sort from Z to A.
Now let's say that the Baranka Item is the most important item, so it should be displayed on the first line. To do this, select the cell with the Baganka value and place the cursor on the border of the cell (the cursor should take the form of a cross with arrows).
Then, pressing the left mouse button, drag the cell to the topmost position in the list, right below the column heading.
After the mouse button is released, the Bagel value will be moved to the topmost position in the list.
Change the format of numeric values
Now let's add a digit group separator to numeric values (the Sales field). To do this, select any value in the Sales field, call the context menu with the right mouse button and select the menu item Numeric format…
In the window that appears, select the number format and check the box Thousand separator.
Adding new fields
Suppose you want to prepare a report on the sales of Goods, but broken down by Regions of sale. To do this, add the Sales region field by checking the appropriate checkbox in the Fields list. The Sales Region field will be added to the Row Names area of the Field List (to the Product field). By changing in the area Row names In the list of fields, the order of the fields Product and Region of sale, we get the following result.
By highlighting any Product name and clicking the menu item PivotTable Tools/ Options/ Active Field/ Collapse All Field, can be rolled pivot table to display only sales by Region.
Adding columns
Adding the Sales region field to the rows area resulted in pivot table expanded to 144 lines. This is not always convenient. Because sales were made only in 6 regions, it makes sense to place the Sales region field in the column area.
pivot table will take the following form.
Swapping columns
To change the order of columns, you need to grab the column heading in pivot table drag it to the right place.
Removing fields
Any field can be removed from the PivotTable. To do this, move the mouse cursor over it in the Field List (in the Report Filter, Report Names, Column Names, Values areas), press the left mouse button and drag the field to be deleted outside the Field List.
Another way is to uncheck the box next to the field to be deleted at the top of the Field List. But, in this case, the field will be deleted from all areas of the Fields List at once (if it was used in several areas).
Adding a filter
Suppose that it is necessary to prepare a report on the sales of Groups of Goods, and it must be done in 2 versions: one for batches of Goods that brought profit, the other for unprofitable ones. For this:
- PivotTable, click menu item ;
- Put a tick in the List of fields near the fields Group, Sales and Profit;
- Move the Profit field from the Row Names area of the Field List to the Report Filter area;
Type of resulting pivot table should be like this:
Now taking advantage Drop-down (drop-down) list in a cell B1 (Profit field) you can, for example, build a report on the sales of Groups of Goods that brought profit.
After pressing the OK button, the Sales values of only profitable Lots will be displayed.
Please note that in the Field List pivot table a filter icon appeared opposite the Profit field. You can remove the filter by unchecking the checkbox in the List of fields.
You can clear the filter through the menu PivotTable Tools/ Options/ Actions/ Clear/ Clear Filters.
Also the data is available through the dropdown list in the headers of the rows and columns Pivot table.
Multiple totals for one field
- Clear the previously created report: select any value pivot table, click the menu item PivotTable Tools/ Options/ Actions/ Clear/ Clear All;
- Check the boxes next to the Product and Sales fields at the top of the Field List. The Sales field will automatically be placed in the Values area;
- Drag and drop another copy of the Sales field into the same Values area. IN pivot table there will be 2 columns counting the amount of sales;
- V pivot table select any value in the Sales field, right-click the context menu and select Totals per/ Quantity. Problem solved.
Disable total rows
The totals bar can be disabled via the menu: Working with PivotTables/ Constructor/ Layout/ Grand totals. Don't forget to pre-select any cell pivot table.
Grouping Numbers and Dates
Let's assume that you want to prepare a time-of-sale report. As a result, you need to obtain the following information: how many consignments of the Goods were sold in the period from 1 to 10 days, in the period of 11-20 days, etc. For this:
- Clear the previously created report: select any value pivot table, click the menu item PivotTable Tools/ Options/ Actions/ Clear/ Clear All;
- Check the box next to the Sales field (the date of the actual sale of the Goods) at the top of the Field List. The Sales field will automatically be placed in the Values area;
- select the only value of the Sales field in pivot table, call the context menu with the right mouse button and select the item Totals per/ Quantity.
- Drag another copy of the Sales field with the mouse to the Row Names area;
Now pivot table shows how many consignments of the Goods were sold in 5, 6, 7, ... days. There are 66 lines in total. Let's group the values with a step of 10. To do this:
- Highlight one value pivot table in the Row Names column;
- Select an item from the menu Group by field;
- Fill in the window that appears, as shown in the figure below;
- Click OK.
Now pivot table shows how many consignments of the Goods were sold in the period from 1 to 10 days, in the period of 11-20 days, etc.
To ungroup values, select Ungroup on the menu Working with PivotTables/ Options/ Group.
A similar grouping can be carried out by the Delivery date field. In this case, the window Group by field will look like this:
Now pivot table shows how many consignments of the Goods were delivered each month.
Conditional Formatting PivotTable Cells
To cells pivot table you can apply the rules as you would to cells in a regular range.
Let's single out, for example, the cells with the 10 largest sales volumes. For this:
- Select all cells containing sales values;
- Select a menu item Home/ Styles/ Conditional Formatting/ Rules for selecting the first and last values/ The first 10 elements;
- Click OK.
What are Excel Pivot Tables? Probably, an uninitiated person will assume the most obvious - these are tables in which data from several other disparate sources are collected (summarized) that meet some query. In fact, this conclusion is not entirely correct.
Excel PivotTables is a tool that collects, according to a given algorithm, information most often from one database table! (The possibility of using multiple consolidation ranges as a source exists, but will not be discussed in this article.)
With the help of pivot tables from a chaotic set of database rows, you can in seconds extract the necessary ones, cut off unnecessary information in them, add the missing information and present it to the user in a beautiful and easy-to-read form.
If you constantly work in MS Excel with large amounts of information and do not know anything about pivot tables, then consider that you are “hammering nails” with a calculator without knowing its true purpose!
When should pivot tables be used?
Firstly, when you work with a large amount of statistical data, which is very laborious to analyze with the help of sorting and filters.
Secondly, when it is necessary to repeat (recalculate) reports at certain intervals due to changes in the source - the database table.
Thirdly, when analyzing changes in database data in the context of various time periods or other criteria.
These are the main situations in which the real magic power of the Excel PivotTable tool is revealed.
Creating pivot tables and working with them does not affect the content of the source - the database! Pivot tables excelallow you to look at this data from different angles without changing anything in the database!!!
This is the final and most important article of the review series on storing information and managing large tabular data volumes.
In the previous articles of the cycle, we created, learned, apply and, enter.
Create a pivot table template.
We will create a pivot table in the same workbookexcel, where the source of information is located - a database table, but on a different sheet.
We continue to work with the educational mini-base DB2 "Production of metal structures by section No. 2", which we worked with in all articles of this cycle.
1. Open the file in MS Excel.
2. We activate ("click with the mouse") any cell inside the base table.
3. We execute the command of the main menu of the program "Data" - "Pivot table ...". This command launches the PivotTable Wizard mini-service.
4. Without thinking for a long time about the options for choosing the positions of the switches in the drop-down windows of the "Wizard ...", we configure them (more precisely, do not touch them) as shown in the screenshots below, moving between the windows using the "Next" buttons.
At the second step, "Wizard ..." will choose the range itself if you have correctly prepared the database and completed step 2 of this section of the article.
5.
At the third step of the "Wizard ...", click the "Finish" button. The pivot table template is generated and placed on a new sheet of the same file where the database is located!
Except for now empty template on the newly created sheet Excel file you see the "Pivot Tables" panel with its tools and the "List of Pivot Table Fields" window that appears when any cell inside the pivot table is activated. They appear automatically, I just placed them next to the template and adjusted the sizes. If you do not see the "Pivot Tables" panel, then call it through the main menu with the command "View" - "Toolbars" - "Pivot Table"
In MS Excel 2007 and newer versions, "Wizard ..." is abolished because 99% of users never change the proposed switch settings and go through these three steps, simply agreeing with the proposed options (we did this too).
In MS Excel 2007, using the "Pivot Table" command, the "Create Pivot Table" dialog box appears, in which it is enough to specify the data source for analysis and the location of the pivot table to be created.
Creation of working Excel pivot tables.
There are four zones in the created template, in which to create a pivot table you should place the names of the fields-columns of the database table by dragging them with the left mouse button from the "List of Pivot Table Fields" window. I remind you that in the database a field is a column with a name.
Attention!!!
The elements of the "List of PivotTable Fields" window are the titles of the database fields!
1. If you place one of the elements of the "Pivot Table Field List" in the most upper zone template where it will become "Page field", then we get a convenient filter the records of this field.
2. If you drag one element (or two, less often three or more) of the PivotTable Field List to the left zone of the template, where it will become "Field of strings" row headers pivot table.
3. If you drag one element (or two, less often - three or more) of the "List ..." to the upper zone of the template, where it will become "Column field", then we will get from the records of this field of the database table column headings pivot table.
4. If you drag one element (or two, less often - three or more) of the "List ..." into the central area of the template, where it will become "data item", then we will get from the records of this database field values pivot table.
Values - data elements - will be located in strict accordance with the rules of two-dimensional tables, that is, at the intersection of the corresponding row headers and column headers!
Not very clear? Let's move on to practical examples - everything will become clear!
Task number 9:
How many tons of metal structures were produced for each order?
Let's do two things to answer this question! The scheme of these actions is shown in the previous figure with blue arrows.
1. Drag the "Order" element from the "List of Pivot Table Fields" window to the "Row Fields" area of the empty template.
2.
Drag the element “Total mass, t” from the window “List of fields of the pivot table” to the zone “Data elements” of the template. The result is in the screenshot on the left. I think no explanation is needed.
The heading of the "Data Elements" "Sum over the field Total mass, t" is located higher heading "Row fields" and to the left of the place where the heading "Column fields" can be located. Pay attention to this!
Task number 10:
How many tons of steel structures were produced for each order by dates?
We continue to work.
3. To answer the question of task No. 10, it is enough to add the “Date” element to our pivot table from the “List of Pivot Table Fields” window to the “Column Fields” zone of the template.
4.
Records can be grouped, for example, by month. To do this, on the "Pivot Tables" panel, click the "Pivot Table" tab and select "Group and Structure" - "Group ...". In the Grouping window, make the settings in accordance with the screenshot on the left.
Since all records in our database were made in April, after grouping, we see only two columns - "Apr" and "Grand Total". When records dated May and June appear in the source database, the corresponding two columns are added to the pivot table. 
Task number 11:
When and how many tons and pieces of stamps were produced in total by order No. 3?
5. To get started, drag the "Order" field from the "List of Pivot Table Fields" window or directly from the Pivot Table itself to the very top of the sheet to the "Page Fields" area of the template.
7. Add the "Quantity, pcs" field to the "Data elements" area.
The configuration of the Excel pivot table necessary to answer the question of the task has been formed.
8. Click on the down arrow button in cell B1 and in the drop-down list, instead of the entry “(All)”, select “3”, corresponding to order No. 3 of interest to us and close the list by clicking the “OK” button.
The answer to the task question is in the screenshot below this text.
Pivot table formatting.
If the pivot table needs to be printed and shown to someone, or just to please yourself a little, then it needs to be formatted - given a decent appearance and reformulated some "inadequate" headings.
The headings “Sum for the field Total weight, t” and “Sum for the field Quantity, pcs” sound, sort of, and understandably, but somehow not in Russian. Let's rename them to more euphonious "Total mass of products, t" and "Number of products, pcs."
1. Let's continue with the created table. Activate cell B5 with the mouse.
2. On the PivotTable toolbar, click the Field Options button (highlighted on the right in the image below).
3. In the drop-down window "Calculation of the field of the pivot table" change the name and click the "OK" button.
4.
According to a similar algorithm, we rename the second field header, after first “inserting” the mouse on cell B6.
5. On the "Pivot Table" panel, click the "Report Format" button (highlighted on the left in the picture above - above item 3).
6. In the "AutoFormat" window that appears, select from the proposed formatting options, for example, "Report 6" and click "OK".
Appearance our pivot table has changed significantly.
Conclusion.
I draw your attention to several very important points!
When changing the source (for example, adding another record to the database), changes will not automatically occur in the pivot table itself !!!
In order for changes in the database to be reflected in the pivot table, it is necessary to “manually” press the “Update data” button each time (the icon of the button is “ ! ) in the PivotTables panel!!!
Place in the "Page Margins" area fields containing text information, which may become heading for the generated pivot table.
In the Row Fields and Column Fields area, place fields containing text information, which may become headings for rows and columns created pivot table.
In the "Data Elements" zone, place mainly numerical information.
Proceed by placing the fields the way you would do it on a sheet of paper! Don't be afraid to make mistakes. Everything is easily fixed.
Pivot tables created with two or three mouse movements in one of the previous sections of this article answered very difficult questions very quickly! If orders are made within several months, and the number of brand names exceeds several thousand, then how long will it take you to solve the problems discussed above? Hour? Day? Excel PivotTables do it instantly, repeatedly and without errors!!!
Many good thick and thin books have been written about Excel PivotTables. First of all, these are books by Bill Jelen (“Mr. Excel”) and Michael Alexander. For those who are interested in this topic, I recommend reading them.
Of course, it is very difficult, almost impossible, to talk in detail about this wonderful tool in a short article. On the one hand, I wanted to touch on more practically important aspects, but on the other hand, in the introductory article I didn’t want to “get into the jungle”, scaring off users with the volume and complexity of information. If this “magic tool” of MS Excel arouses the interest of blog readers, then in the future this topic will be continued with the release of a small brochure with important and valuable practical recommendations.
Be sure to use Excel PivotTables! Your value as a specialist will increase many times over!
On this series of six articles, which is a general overview of the topic processing large amounts of information in Excelcompleted.
I beg respecting author's work subscribe for article announcements in the window located at the end of each article or in the window at the top of the page!
Dear readers, leave questions, feedback, and comments in the comments at the bottom of the page.
Excel PivotTables are one of the tools that help you quickly and easily analyze large amounts of information. We will show with examples how to build pivot tables in Excel.
In this article you will learn:
What is a PivotTable in Excel?
When working with data in Excel, periodically there is a need to analyze them. This is important, for example, in sales - who, in what period, how many made sales, when there was a recession, and when there was a surge, which goods are in the greatest demand, which are the least. The more data, the more difficult it is to analyze them. The higher the probability of error due to the human factor. Therefore, it is necessary to apply the tools that are provided by Excel itself.
Pivot tables MS Excel is a tool for analytics and data presentation in a convenient and easily understandable form. They are used in the following cases:
- Large amount of initial data. A small amount of data can be calculated and analyzed manually. However, if there are hundreds and thousands of records at your disposal, errors are inevitable and there will be many of them. The time required for processing will also increase significantly.
- It is necessary to identify trends and dynamics in the data. In pivot tables, you can easily transform the output of information, you do not need to sort it manually.
- To create a chart or graph based on formatted data.
- It is necessary to make an intermediate "cut" of the results, which often change, for their subsequent comparison. In this case, it is enough to update the data, Excel will recalculate everything automatically.
- For analysis and search for extreme values - data minima and maxima.
- To bring together disparate data from different sources. If necessary, you can add the required row or column to the source data and update the final display of the report.
Also, pivot tables in Excel are more convenient to use when viewing data. .
What does a pivot table look like
Before we talk about how to build a pivot table in Excel, let's show how it looks. The data for the table is taken from the user-specified shared table. Functionally, the table is divided into four segments:
- values;
- lines;
- columns;
- report filter.
The PivotTable of the .xlsx file has the following limitations:
- the maximum number of row fields is 1,048,576 or limited by the size of the RAM;
- the maximum number of column fields is 16,384;
- the maximum number of page fields is 16,384;
- the maximum number of data fields is 16,384.
Let's take the following data as an example.
The pivot table for this data will look like this:
In the right part of the pivot table - last names, at the top - dates, in the center - the results of the work. Obviously, this presentation of information is more convenient and understandable. .
In this image, the value segment is highlighted in red. This is the segment in which the calculations are carried out. Here you can see the cost of concluded deals by day and by last name.
This is a string segment. There may be one or more values here. In our case, these are the names of managers. Opposite the names in the corresponding lines - a summary of the work.
This is the column segment. Usually it is a time scale, its use facilitates the analysis and perception of data.
In a special pivot table wizard, you can change the placement of data. In our case, last names can be placed in the column segment, in dates - in the row segment. Then the pivot table will take on a new look.
The PivotTable Builder is intuitive. In it, you can drag values into fields, you can select values from a drop-down list. Further in the article, a tutorial on creating a pivot table in Excel with examples. .
How to Create a PivotTable in Excel
We will tell and show with examples how to create a pivot table in Excel. As initial data, let's take our table with information on the work of managers for May. To create a pivot table, select "Pivot Table" from the top menu in the "Insert" tab. They are highlighted in the picture.
A new window will appear:
It will need to specify the source whose data will be used to build the pivot table. To do this, you need to select the desired area or the entire table and Excel will substitute this data in a row.
The color shows that the entire array is selected, data appeared in the line. You then need to specify where Excel should build the pivot table. You can select the current sheet or select a new sheet.
If you select a new sheet, then in this case it will be created, the pivot table designer will appear on it. We recommend doing so. After all the information is specified - the source data and the place for the report, you must click the "OK" button and a prompt will appear prompting you to select the fields for building a new report.
To the right of it, a section will open in which you can specify the fields to add them to the pivot table.
Under it there will be another work field where you can drag fields to different areas.
You need to check the boxes for the required fields. The specified positions will automatically appear in the bottom section. They can be customized to your liking.
When rebuilding the source data, the display will be automatically rebuilt. If there are many values, then this can interfere normal operation. Therefore, you can check the "Defer layout update" checkbox. Automatic rebuilding will be disabled. To refresh the pivot table, just click the Refresh button.
Thus, we will create a pivot table and it will take the following form.
It can be seen that the order of the lines matters. The first line in the constructor comes first. The second line in the constructor becomes a subparagraph of the first. In our case, the results of the work of each manager are shown with a breakdown by commodity items - articles with reference to dates.
You can change the order of the lines - to do this, just drag the desired line up or down with the mouse. If you swap the surname and article, then the pivot table will take the following form.
For ease of perception of information, sub-items can be collapsed. By default, they are displayed expanded. To collapse unnecessary positions, you must click the minus sign next to the article. Then the sub-items will be hidden. To expand them, click the plus sign. They are highlighted in the picture.
The central value segment will display all the information summarized and calculated by the program. The total will also be shown - by dates, by articles, and if the article is not collapsed - then by the names of managers.
If you make changes to the original table, the pivot table must be updated. To do this, right-click anywhere in its place and select in the appeared context menu"Update". The data will be rebuilt.
If new rows or columns have been added and the old report display does not cover all the required data set, you must include them in the pivot table. For example, new data has been added to the main array of information (highlighted in color) and the pivot table does not take them into account yet.
To add them to the report, you need to click on the pivot table, go to the "Parameters" tab and select "Change data source" - this option is highlighted in the picture.
In the window that appears, specify the data source along with the updated rows and click OK.
The pivot table will automatically rebuild. On the image, the data for 05/29/2018, which were not available earlier, are highlighted in color.
VIDEO: How to work with pivot tables
My friend, an economist at the enterprise, owns Excel, sometimes looks at the forums, but for 5 years she could not approach the most, in my opinion, useful Excel tool - pivot tables. What is it? Pivot tables are the result of a selection of well-formed data. The tool has many advantages, I won’t describe it, I’ll just say that without it, many would have to master not only Excel, but also Microsoft Access.
My opinion is that this tool should be studied by yourself, and in this article I will tell you the nuances that beginners who want to master this tool stumble over.
ST requires well-formed data. Ideal view - one operation per line corresponds to different fields of the record properties.
I made a simple table that appears in any warehouse. The warehouse ships building materials to 4 stores during the year, and each operation of the shipment of goods has its own record.
As you can see, there is no grouping or summing of data here, I call it a sheet, because although there may be many columns, there are many more rows. There are no gaps here, which is also important.
Excel will suggest a range, so just click OK.
There are 4 options here: Rows, Columns, Values and Report Filter.
Let's stop in more detail. Strings And columns basically the same categories. You can put here those fields that are indicative - for example, name, month, category. You can put two in rows and none in a column, in which case your table will have a grouping in rows.
Values- here we put only numeric values.
Report Filter. As you understand, you build the frame yourself and you can ignore some fields, put some horizontally, some vertically. Both rows and columns will have their own filters, but the report filter allows you to more conveniently select the data that you need to show in this table.
Now on the example of my data.
What kind of table can take:
As you can see, there are many variations.
1) Although the pivot table pulls out very clearly all the slices of the available information, it is not always easy to work in it, it cannot be edited, only read-only . If you have a lot of sections of information, then it is easier to make a table and fill it in using the GET.DATA.PIVOTAL.TABLE function and the rules for working with relative links.
The format of the function is simple ("Values"; "PivotTable"; "Field1"; "Item1"; "Field2"; "Item2", etc.)
That is, you can manually replace the values of the Elements with the relative links of your table.
2) In order to constantly update the data and not touch the layout, add data at the bottom of the sheet. Then nothing will crawl for you, and the update will happen with one button to update on the ST itself. Plus, in the data source for ST, specify either all the columns of the sheet (lazy option) or the name of the range, the dimensions of which will be changed arbitrarily without going into the ST settings.
"The eyes are afraid, but the hands are doing"
Loading...