Sample pivot table in Excel. Why exactly new versions

Hello respected, reader!

Today I want to affect such important and global feature MS Excel is. The consolidated table in Excel is specialized in order to put a huge amount of your data that consist in the table and makes it in the form and the scope that you need. And if you work with large data volumes and do not use a consolidated table, it is equally considered a calculator as a hammer. You are simply not physically able to process for a short time thousands, a dozen, or even a hundred thousand rows in which you store a huge amount of material, but the ability of a consolidated table will allow you to do and not in one form, but in the form you need . With all this, such manipulations neither the droplets appear on the initial data.

The summary table can dynamically change the data, which means when you enter the database (source data table), they also change your consolidated table.

A natural question arises, where the application of the consolidated table will give the greatest effect:

• firstlywhen the analysis of the database for a variety of criteria (city, nomenclature, personnel, time of year, etc.) is done.
• secondlywhen you just work with a huge number or analytical information can not help you at all;
• thirdThis is when the previous 2 options need to constantly recalculate, updating your database.

Although I may not have touched some other use options, but these I consider the basic, and the rest are already hiking from them.

The only big minus in all summary tables is that it cannot be applied if the data in it corresponds to specific conditions, namely:

1. Everyone without exception, the column must have its own heading cap;
2. All lines and columns you must fill, the spaces must be missing.
3. For all data columns, there should be certain cell formats, for those data that should be stored in them (example, for the Date field you need a calendar date format, and for the "Contract" field - text format, etc.)
4. Values \u200b\u200bin these cells should be "sole", which means such which they are not divided (for example, "contract №23 dated 09/03/2016" should be recorded in 3 different columns "Document", "number" and "date", this Allows you to create a flexible and convenient system). It is also possible with
5. If you are conducting an expenditure plate in which except still there is a taking order, then in the database of the initial data, enter the data that is already initially with the "-" sign and then in the twisted form you will receive the result you need;
6. The design itself of your pylon table is obliged to have an optimal view.

If you have already completed all the conditions you will get a wonderful toolkit to work with your information, and not only.

How to create a summary table in Excel

To begin with, we check whether our table is correctly formed: all columns have the correct name, have the appropriate cell format, who is text, who is numerical, etc. ... Check if you want to get an excellent result. And if everything is perfectly proceed:

In the control panel, select the "Insert" tab and get to select 2 options for creating your pylon table:

To begin with, consider the option for beginners, this is when the itself offers you ready options, and you already choose everything, very simple and affordable option To create a summary table in Excel:

To begin with, we just need to specify the cursor to the table and in the "Insert" menu, press the "Recommended Summary" icon and smart Excel will come up and make you a summary table. As you can see, there is a screenshot of choices in the screenshot, you only have to pick up one of the option you suggested that you are most suitable.

Nothing complicated in this variant No, try experimenting, and you will improve your practical cumulative skills.

2. Summary Table (Summary Wizard)

But from this section of the article, the most interesting thing begins. And let's start working from the selection in the "Insert" menu, the "Table" block, the "Summary Table" icon. We do not forget to specify the cursor the base of the source data or the plate with which we will do the consolidated.

In the window that opens, we select several conditions for creating a pivot table - this is the range of the desired source data and where it is necessary to blame the consolidated tablet, clolling on one sheet, well, or create a new sheet and place it. And since the cursor has already stood on the table, Excel quickly and automatically determined the structure of the table and substituted in the range of the range. Click "OK" and get:

As you can see, a new "list3" has been created and called "Creation Wizard Created Tables" well, or for friends, simply "Constructor of Summary Tables", you actually see it in the following screenshot. In the constructor, you specify which columns of the source table, you need to transfer to the consolidated and which calculation will need to be performed above them and all this is commonly dragging into the required area, the title we need the table.

Here we got our first result, but it does not suit us as we do not cage the number of fruits that were sold, and therefore we need from the area of \u200b\u200bthe "line" to drag the headline of the column "weight, kg" and we have a consolidated design Tables we want.

Well, here is the form, of course, but the result is not the one, but the field "Weight, kg" collects according to the criterion - the number of values, and we need, which means we are summing up the mouse cursor to the value of the "value" values \u200b\u200band on the specified field "Number" By field weight, kg, "press the left mouse button calling the context menu. We need to select the last item "parameter of the fields of values."

In the resulting menu, we change the "user name" to a normal view of well, or what we need it, choose the necessary operation on the field, we need a "amount", and not "quantity". With a lot of need, you can change the format of the field cells, but on this moment We are not so necessary for us.

Well, we obtained the necessary tablet of the necessary form, values, well, and format. Everything is done in it as we need and seem to be like everything, and you can finish. But still it is worth a little more quickly, for example, remove unnecessary fields "(empty)", since we spoil the interior created by the consolidated excel tables. So we will continue to work, learn to make a sign beautiful. And for this we call the built-in menu in the table header and remove the checkbox in the list of reflected information, everything, the field is gone.

You see, the science of how consolidated tables are created are not so complicated, but it is necessary to know and understand this issue to each self-respected user Excel. Also, using the consolidated tables in Excel, there is the possibility of your values.

Well, the consolidated table with the sample of fruits is made. But what to do if we need and it is interesting to know, but however, all the same moves through countries. And anyone will be interested in different angles, and since we have already formatted the table and did everything for perfect work. We simply copy our nameplate and in the field of the required area "Rows" change the calculated values \u200b\u200bin places. We specify the first calculated value of the "country", that's all with 1 source data table we received 2 summary tables of data we need.

It is still worth talking that when manipulating with consolidated tables, Excel additionally generates a new menu in the control panel to work with the data of the tables:

And yes, if you are well a very aesthetic user and a standard sample of pivot table decorations in Excel, you do not like the additional styles of the consolidated table. In the "Designer" menu, there is a large selection and I, I think someone will have to do with you. As you can see a simple harness, our tables have become more beautiful and work with them has become more than fun.

But do not forget that the data in our table can not be updated independently, which means that when changing in the source data (for example, I simply removed the data of 2 lines) we have changed the result, but this is aimed at reporting this to our pivot table:

To do this, in the "Work with Consolidated Table" menu, go to the "Analysis" tab and press the "Update" button and all data are recalculated, due to the changes that we have entered our original table:

Well, in principle, how work with consolidated tables in Excel and everything, and the rest will help the practice and

Processing large amounts of information and compose complex multi-level reports are quite difficult without using automation tools. Excel 2010 is just an instrument that allows you to simplify these tasks by creating consolidated (cross) data tables (Pivot Table).

Summary Table in Excel 2010 is used for:

• identify interconnections in a large data set;
• grouping data on various features and tracking trends in groups;
• finding repeating elements, detail, etc.;
• creating consistent reports, which is the most important thing.

Create consolidated tables in two ways. Consider each of them.

Method 1. Creating a summary table using the standard Excel 2010 tool "Summary Table"

Before creating a summary table report, it will be determined that it will be used as a data source. Consider the option with the source in the same document.

1. To begin with, create a simple table with the listed elements that you need to use in the report. Top string Be sure to contain column headers.

2. Open the Insert tab and select the "Summary" tool from the "Table" section.

If you need to create a summary chart together with a consolidated table - click on the arrow in the lower right corner of the Summary Table icon and select "Summary Diagram".

3. In the "Creating a summary table" dialog box, select the table with the data or its range. To do this, highlight the desired area.

You can specify an external source as data for analyzing: set the switch to the appropriate field and select the desired connection from the list available.

5. After confirming the action by pressing the "OK" button, the report layout will be created and open. Consider it.

In the right half of the window, the Basic Control Tools panel is created - "List of a consolidated table fields. All fields (column headers in the source data table) are listed in the "Select fields to add to the report" area. Mark the necessary items and the report of the consolidated table with the selected fields will be created.

The location of the fields can be controlled - to make them the names of the rows or columns, dragging into the corresponding windows, as well as sorted in a convenient order. You can filter individual items by dragging the corresponding field to the "Filter" window. The "Meaning" window is placed the field for which calculations are made and the results are summed up.

Other options for report editing are available from the "Working with Consolidated Table" menu on the "Parameters" and "Designer" tabs. Almost each of the tools of these tabs has a lot of settings and additional functions.

Method 2. Creating a pivot table using the "Summary and Chart Master" tool

To apply this method, you will have to make an accessible tool that is not displayed by default on the ribbon. Open the "File" tab - "Parameters" - "Panel quick access" In the "Select commands from" list, mark the "Ribbon Team" item. And below, from the list of commands, select "Summary Master and Charts". Click the Add button. The wizard icon will appear at the top, on the quick access panel.

The Summary Table Wizard in Excel 2010 is not at all different from a similar tool in Excel 2007. To create a summary tables with it, do the following.

1. Click on the wizard icon in the quick tolerance panel. In the Dialog box, place the switch to the data source list item you need:

• "List or database Microsoft Excel."- the source will be the working sheet database, if any;
• "In the external data source" - if there is a connection to external baseto choose from available;
• "In several consolidation ranges" - if the data combination is required from different sources;
• "Data in another summary table or a summary diagram" - an existing summary table or chart is taken as a source.

2. After that, the view of the created report is selected - "summary table" or "summary diagram (with a table).

• If the source is selected current documentwhere there is already a simple table with the elements of the future report, set the range of coverage - highlight the desired area with the cursor. Next, select the location of the table - on the new or on the current sheet, and click Finish. The summary table will be created.
• If you need to consolidate data from multiple sources, put the switch to the appropriate area and select the type of report. And after it will be necessary to specify how to create the page fields of the future summary table: one field or several fields.

When you select "Create Page Fields" first have to specify the ranges of data sources: highlight the first range, click "Add", then the next, etc.

For convenience, the ranges can be assigned names. To do this, select one of them in the list and specify the number of page fields created for it, then set the name to each field (label). After that, highlight the following range, etc.

• When choosing an external data source, the Microsoft Query application is included in Excel 2010 or if you want to connect to Office data, the data tab options are used.
• If a consolidated table is already present in the document or a summary diagram - you can use them as a source. To do this, it is enough to specify their location and select the desired range of data, after which a new pivot table will be created.

Suppose you need to build a report based on available data. To do this, can be used. standard functions. But when there is a need to add a table or change its structure, the introduction of such changes may require considerable effort.
Application of consolidated tables solves this problem. Thanks to them, you can easily change the structure, calculations, filter the source data.

Creating a pivot table

Before creating the table, make sure that the source data is presented correctly: the columns have headers, the numbers are represented in the numerical format, and not in text, etc. Otherwise, you can get incorrect results.
Click the "Insert" tab, then in the "Table" section, click the "Sent Table" icon or select the appropriate item in the drop-down list.

In the window that appears, you must select a data source that represents:

1. Range (may be in another book);
2. Data table (indicates its name);
3. Data from an external source obtained by SQL query from the database, etc.

Then the place is indicated for accommodation. If you choose a new sheet, the application will create it and places the table, starting from the first cell. If the required sheet already exists, you can independently choose the address of its start.

Managing Table Fields

After pressing the "OK" button in the Create Table Creation window, an empty area is created for its placement, and a window with a list of all fields is displayed.

The names of the fields are the headlines of the columns of the source data, so try to call them as clear and briefly.
The list below shows 4 areas. They are responsible for the actions on the data:

• String names - Displays the field values \u200b\u200bin the form of string name. In fact this area nothing changes if you assign all the existing fields, then we get the source table;
• Column names - field values \u200b\u200bare displayed as column names. The action resembles the usual transposition;
• Values \u200b\u200b- with the values \u200b\u200bof columns placed in this area, calculations are made. In this case, one field can be assigned to it several times;
• The report filter - the fields are placed here, the values \u200b\u200bof which serve to filter the rains of the source data, which the pivot table must ignore.

To assign a particular field area, it is enough to drag the last with the mouse.
For example, a table will be used that displays the dynamics of the dollar rate with respect to the ruble for 9 months:

Month	date	Number of	Course	The change
January	10.01.2013	1	30,4215	0,0488
January	11.01.2013	1	30,3650	-0,0565
January	12.01.2013	1	30,2537	-0,1113
...	...	...	...	...
September	28.09.2013	1	32,3451	0,1715

It is necessary to make a summary table in Excel, which calculates the average course for each month (the empty table has already been built at the beginning of this section).

In the area of \u200b\u200bthe "Line name" by dragging the "Month" field. The "values" area will assign a "course", after which you will change the parameters for the field so that the average arithmetic average is calculated according to it. To do this, click on the desired item in the area, in the open menu, click on the "field parameters of the fields ...". In the window that appears, there is an Operation tab. It must be selected from the list "Average". Ready.

Also in the field parameters, you can change the title and apply additional calculations on the tab of the considered menu.

Calculated Country Table Fields

If the provided operations and calculations are not enough, then the excel allows you to create your own calculated field in a consolidated table. To do this, select the cell from the table area, go to the "Parameters" tab ("Analysis" for Excel 2013) the tape that appears. Next, in the "Service" section, click on the "Formula" icon, from the drop-down menu (in version 2010 and above the path is different: Section "Calculations" -\u003e Fields, Elements and Sets List) Select "Calculated Field ...". A window should appear:

Specify the clear name, and write down the formula using any functions (keep in mind that the calculated fields do not work with the text). As an example, I will multiply the course for 1000 and subtract 13 percent (\u003d Course * 1000 * 0.87). Let's call the "zp" field, add to the range of values \u200b\u200band as an operation will apply maximum. Check out a new report type:

It can be noted that the result on a new field returns a "strange" result. This comes from the fact that the summation is pre-valued by the value of the Course column, and only then the result is used to calculate. If you add a date to the title area, then the calculation will return the desired result:

Summary Table Settings in Excel

To further study the topic, we construct a more complex table (the principle of construction does not differ from the previously considered).
The initial data represent a list of 100 lines, where each entry reflects the employee wages of various industries in certain regions:

From example, it can be seen that the consolidated table represents a tree structure if more than 1 fields are used. The rootes are the values \u200b\u200bof the column, which in the list of the "Line titles" area is first. All subsequent fields are inserted into it and in each other, according to their order in the list, change which can be simple mouse dragging. Each separate branch of such a tree can be folded and disclosed. This property also applies to the column names area.
By default, Excel specifies the pylon table layout in a compressed form. It can be changed through the parameters (click right-click on the table area -\u003e Posted Table Settings -\u003e Conclusion -\u003e Classic Layout) or through the constructor:

The application of the tabular layout allows you to locate each field in a separate column and additionally display intermediate results.
If you fail, the result is not required, then it must be deleted to facilitate the reading of the table. The right click of the mouse on it and in the list, take a tick from the corresponding item. To get rid of all the outcomes, in addition to the main, on the Designer tab, select "Intermediate Results" section -\u003e "Do not show intermediate sums".

Since the summary table represents a tree structure, the line name is displayed only once. In Microsoft Excel, starting with version 2010, you can additionally apply to the repetition of signatures of the elements to the layout.

Now the completed summary table looks like excel sheet:

In addition to the considered properties through the table parameters, you can set:

1. The name of the consolidated table;
2. Association and alignment of signatures;
3. Output values \u200b\u200bfor empty cells;
4. Automatic change in columns width;

Consolidated tables are considered one of the most powerful Excel tools to work with the data. They exist in order to simplify the complex and cumbersome table, and the results of the calculations are simple, understandable and affordable. But! Not far from each table can be made consolidated.

The table must be in the form of a regular list, that is, column headers can only be in the first line. An example of such a table I led in fig. P1.1.

If you have any intermediate headings or intermediate results in the table, you need to remove them. In order not to explain all the benefits of the consolidated table, I will show it on the example. Click the Summary Table button in the Insert Menu Table Table group (Fig. P1.2).

Window opens. It you must first select a table or a range of a table for which a consolidated table is compiled, and indicate where to put it. You can arrange it on a new sheet, and you can - next to the source table, on the same sheet as it will be more convenient (Fig. P1.3).

I inserted a consolidated table on the same sheet. A panel-designer appeared on the right side of the window to create a consolidated table, and on top of a new tab group - work with consolidated tables.

How is the pivot table?

On the right side of the panel, which is called a list of fields of the pivot table, you see a list of column headers from the table shown in Fig. P1.1. From these fields you are now, as from the designer, can comply new table. To do this, you need to drag the field name to the required area. I decided that the names of the months would be in the columns of the pivot table, and the names in the lines, and the value field I fill the values \u200b\u200bfrom the column received. That is, only data on the money obtained will be included in the consolidated table. The result is shown in Fig. P1.4.

The summary table is called this precisely because it reduces all your results into a simple table, summing up common results. In this case, the table summarizes the data (in the graph of the value it is written the amount on the field received). By the way, open the list of the calculation buttons on the Options tab (Fig. P1.5).

I put out the total value, and, as you can see in Fig. P1.5, now the consolidated table considers not the amount of months and surnames, but the average value: average salary by months and the average for the employee. In addition, you can make a summary diagram by the values \u200b\u200bof the pivot table (Fig. P1.6).

At the same time, a group of tabs work with consolidated diagrams, in which there is nothing new for you. We all considered it when we disassemble the work of ordinary charts. By the way, pay attention: I changed the row and columns in places, so the results are now considered by the value of the balance column (see Fig. P1.6). I did it just that you know that the values \u200b\u200bof the columns, rows and the field of values \u200b\u200bcan be labeled as it is convenient for you.

And in the consolidated table you can insert a cut. This is an additional filter that allows you to make the result more visually (Fig. P1.7).

In the Sorting and Filter of the Parameters tab, click the Insert section button and select the parameter for which you want to filter the data. I pointed out a month. Now you can choose a specific month in the cutting window, and only data relating to this month will be displayed in the pivot table (see Fig. P1.6). At your disposal will also appear a whole tab - cutting tools. By the way, you can insert in the table not one slice, but several.

I told you the most simple techniques Works with consolidated tables. If you figure it out, you can understand everything else. Just do not forget that before creating a pivot table, the source table is needed to prepare for this, that is, to make it so that it does not contain any intermediate headlines and results. Well, if something is nevertheless, it is not clear or you want to study the capabilities of consolidated tables in more detail, then I recommend contacting the materials of the Special Site Consolidated Tables Excel 2010, which is entirely devoted to the methods of working with data in the Summary Tables Excel 2010.

29.10.2012

Consolidated tables - This is one of the most powerful Excel tools. They allow you to analyze and sum up the different results for large data volumes with just a few clicks with the mouse. In this article, we will get acquainted with the consolidated tables, let's understand what they represent themselves, learn how to create them and configure them.

When writing this article, Excel 2010 was used. The concept of consolidated tables almost did not change for many years, but the way to create them is slightly different in each new version Excel. If you have excel version Not 2010, then be prepared that screenshots in this article will differ from what you will see on your screen.

A bit of history

At the dawn of development programs for creating spreadsheets of Lotus 1-2-3 rules. His superiority was so complete that Microsoft's efforts to develop their own software (Excel), as Lotus alternatives, seemed a waste of time. And now we move in 2010! Excel dominates the spreadsheets more than Lotus code, either for all its history, and the number of people who still use Lotus, tends to zero. How could this happen? What caused such a dramatic reversal of events?

Analysts allocate two main factor:

• First, Lotus decided that this new-fashioned GUI platform with the name of Windows is just a mumbling passion, which will not last long. They refused to create a version of Lotus 1-2-3 for Windows (however, only a few years), predicting that the DOS version of their software is all that ever need to consumers. Microsoft, naturally, has developed Excel specifically under Windows.
• Secondly, Microsoft has developed such a tool in Excel as a summary table, which was not in Lotus 1-2-3. Summary tables, an exclusive for Excel thing, it turned out to be so stupidly useful that people were inclined to master the new software package Excel, and not continue to work in Lotus 1-2-3, in which they were not.

Summary tables along with the underestimation of the success of Windows as a whole, played the funeral march for Lotus 1-2-3 and put the start of the success of Microsoft Excel.

What are summary tables?

So, how is it better to characterize, what are the summary tables represent?

Speaking simple languageThe summary tables are the results of some data created to facilitate the analysis of this data. In contrast to manually created, Summary Excel tables are interactive. After creating, you can easily change them if they did not give the paintings that you hoped to get. Just a pair of clicking the results of the results can be turned in such a way that column headers become row headers and vice versa. With consolidated tables you can do a lot various actions. Instead of trying to describe all the capabilities of consolidated tables in words, it is easier to demonstrate it in practice ...

Data that you analyze with the help of consolidated tables cannot be any. These must be untreated source data, such as a list. For example, it may be a list of sales performed in the company over the past six months.

Look at the data shown in Figure below:

Please note that these are not raw source data, since they have already summed up. In the B3 cell we see $ 30,000, which is probably the total result that James Cook did in January. Where are the initial data? Where did the figure of $ 30,000 come from? Where is the source list of sales, from which this result was obtained for the month? It is clear that someone made tremendous labor in ordering and sorting all sales data over the past six months and turned them into the outcome table, which we see. How much do you think it took time? Hour? Ten o'clock?

The fact is that the table shown above is not a summary table. It was created manually from the source data stored somewhere else, and their processing took at least a couple of hours. It is such a table of the outcome that can be created using consolidated tables, spending only a few seconds for it. Let's figure out how ...

If you return to the source list of sales, it would look like this:

Perhaps you will be surprised that from this list of trading operations using summary tables and in just a few seconds, we can create a monthly sales report in Excel that we understood above. Yes, we can do it and much more!

How to create a consolidated table?

First, make sure that you have any source data on the Excel sheet. The list of financial transactions is the most typical, which is found. In fact, it can be a list of anything: contact details of employees, a collection of CDs or data on the fuel consumption of your company.

So, run Excel ... and load such a list ...

After we have discovered this list in Excel, we can proceed to creating a consolidated table.

Highlight any cell from this list:

Then on the tab Insert. (Insert) Select the command Pivottable (Summary Table):

A dialog box appears Create Pivottable. (Creating a pivot table) with two questions for you:

• What data to use to create a new pivot table?
• Where to place a pivot table?

Since in the previous step, we have already chosen one of the cells of the list, then the entire list will be highlighted for creating a pivot table. Note that we can choose another range, other table and even some external data source, for example, the base table access data or MS-SQL. In addition, we need to choose where to place a new pivot table: on a new sheet or on one of the existing ones. IN this example We will choose the option - New Worksheet. (On a new sheet):

Excel will create a new sheet and will place an empty consolidated table on it:

As soon as we click on any cell in the pivot table, another dialog box will appear: Pivottable Field List (Fields of the consolidated table).

The list of fields at the top of the dialog box is a list of all headers from the source list. Four empty areas at the bottom of the screen allow you to specify a summary table, how to summarize the data. While these areas are empty, there is nothing in the table. All that is required of us is dragging the headlines from the upper area into the empty areas below. This automatically generates a summary table, in accordance with our instructions. If we made a mistake, you can delete headers from the bottom area or drag other replacement.

Region Values. (Values) is probably the most important out of four. Which header is placed in this area, it determines which data will be supplied (sum, average, maximum, minimum, etc.) is, almost always, numerical values. Excellent candidate for place in this area - data under the heading Amount. (Cost) of our source table. Drag this title to the area Values. (Values):

Please note that the title Amount. Now marked with a check mark, and in the area Values. (Values) appeared recording Sum Of Amount. (Amount field sum indicating that column Amount. summed up.

If we look at the consolidated table itself, we will see the sum of all the values \u200b\u200bfrom the column Amount. source table.

So, our first summary table is created! Convenient, but not particularly impressive. Probably, we want to get more information about our data than it is now.

Turn to the source data and try to define one or more columns that can be used to crush this amount. For example, we can form our consolidated table in such a way that the total sales amount is calculated for each seller separately. Those. In our consolidated table, lines are added with the name of each company seller and its final sales sum. To achieve such a result, it is enough to drag the title Salesperson. Row Labels (Lines):

It becomes more interesting! Our consolidated table begins to get the form ...

See the benefits? For a couple of clicks, we created the table that I would have to create a very long time.

What else can we do? Well, in a certain sense, our consolidated table is ready. We have created a useful summary on source data. Already received important information! In the remainder of the article, we will analyze some ways to create more complex summary tables, and also learn how to configure them.

Setting up a consolidated table

First, we can create a two-dimensional summary table. Do it using the column header Payment Method. (Payment method). Just drag the title Payment Method. in the area Column Labels. (Columns):

We get the result:

Looks very cool!

Now let's do a three-dimensional table. How can such a table look like? Let's see…

Drag the title Package. (Complex) to the region Report Filter. (Filters):

Note where it turned out to be ...

This gives us the opportunity to filter the report on the sign "What a recreation complex was paid." For example, we can see the breakdown by sellers and by payment methods for all complexes or for a couple of clicks to change the view of the pivot table and show the same breakdown for the ordered complex Sunseekers..

So, if you understand this correctly, then our consolidated table can be called three-dimensional. We continue to configure ...

If suddenly it turns out that only payment of check and credit card (that is, cashless calculation), then we can disable the title output Cash (Cash). For this next to Column Labels. Press the down arrow and in the drop-down menu, remove the checkbox from the point Cash:

Let's see what our consolidated table is now similar. As you can see, column Cash disappeared from it.

Formatting Summary Tables in Excel

Obviously, consolidated tables are a very powerful tool, but so far the results look somehow uncomplicated and boring. For example, the numbers that we summarize are not similar to the amounts in dollars are just some numbers. Let's fix it.

Seduced to make the usual action in such a situation and simply highlight the entire table (or the entire sheet) and use the standard formatting buttons of numbers on the toolbar to set the desired format. The problem of this approach is that if you ever ever change the structure of the consolidated table (and this will happen with a probability of 99%), then formatting will be lost. We also need a way to make it (almost) constant.

First, find a record Sum Of Amount. in area Values. (Values) and click on it. In the menu that appears, select item Value Field Settings (Parameters of the fields of values):

A dialog box appears Value Field Settings (Field parameters of values).

Press the button Number Format. (Numeric format), the dialog box opens Format Cells. (Cell format):

From the list Category. (Numeric formats) select Accounting. (Financial) and the number of decimal places set to zero. Now press several times OKTo go back to our pivot table.

As you can see, the numbers turned out to be formatted as amounts in dollars.

Since we have been in formatting, let's configure the format for the entire pivot table. There are several ways to do it. We use the one that is simpler ...

Open tab Pivottable Tools: Design (Work with consolidated tables: Designer):

Choose any suitable style and look at the result in your pivot table:

Other consolidated table settings in Excel

Sometimes you have to filter the data by date. For example, there are many - many dates in our list of trading operations. Excel provides a tool for grouping data by day, months, years, etc. Let's see how it is done.

To start, remove the record Payment Method. From the region Column Labels. (Columns). To do this, drag it back to the list of headers, and move the title in its place Date Booged (Booking date):

As you can see, it temporarily made our consolidated table useless. Excel has created a separate column for each date, which was performed a trading operation. As a result, we got a very wide table!

To fix it, right-click any date and select from context menu paragraph Group. (Group):

The grouping dialog box appears. We choose Monhs. (Months) and click OK:

Voila! From such a table, much more benefits:

By the way, this table is almost identical to the one that was shown at the beginning of the article, where the results of sales were manually prepared.

There is another very important point you need to know! You can create not one, but several levels of line headers (or columns):

... And it will look like this ...

The same can be done with columns headers (or even with filters).

Let's return to the source form of the table and see how to output averages instead of amounts.

To start click on Sum Of Amount. And from the menu that appears, select Value Field Settings (Parameters of the fields of values):

In the list Summarize Value Field By (Operation) in the dialog box Value Field Settings (Field Parameters) Select Average. (Average):

At the same time, while we are here, let's change Custom Name. (User name) with Average of Amount. (Quantity over the Amount field) for something shorter. Enter something like something like AVG.:

Click OK And look what happened. Note, all values \u200b\u200bhave changed with the final sums on the average values, and the title of the table (in the left upper cell) changed to AVG.:

If you want, you can immediately get the amount, average and quantity (sales) placed in one pivot table.

Here step-by-step instructionHow to do this, starting with an empty pivot table:

1. Drag the title Salesperson. (Sales representative) in the region Column Labels. (Columns).
2. Drag the headline three times Amount. (Cost) to the area Values. (Values).
3. For the first field Amount. Change the name on Total (Sum), and the format of numbers in this field on Accounting. (Financial). The number of decimal signs is zero.
4. Second field Amount. name Average, the operation for it install Average. (Average) and the format of numbers in this field also change to Accounting. (Financial) with the number of decimal signs equal to zero.
5. For the third field Amount. Set the name Count and surgery for him - Count (Number)
6. In area Column Labels. (Columns) automatically created field Σ values. (Σ values) - drag it to the area Row Labels (Lines)

That's what we get in the end:

Total amount, average value and sales number - all in one pivot table!

Conclusion

Microsoft Excel consolidated tables contain very, very many features and settings. In such a small article, they are not even close to them. To fully describe all the capabilities of the consolidated tables, it would be necessary to make a small book or a large website. Bold and inquisitive readers can continue the study of summary tables. To do this, it is enough to click on the right mouse button on almost any element of the pivot table and watch what functions and settings open. On the ribbon you will find two tabs: Pivottable Tools: Options (Analysis) and Design. (Constructor). Do not be afraid to allow an error, you can always delete a consolidated table and start moving again. You have the opportunity that there has never been for long-lasting users of DOS and Lotus 1-2-3.