Excel summary table: how to create and work? Working with Excel PivotTables

Many office users often face a number of problems when trying to create and edit any office documents. Often this is due to the fact that companies use several office programs, the principles of work in which can vary greatly. Especially many difficulties can create an Excel spreadsheet.

Fortunately, MS Office 2010-2013 appeared recently, which not only includes a number of updated programs for processing text files, tables, databases and presentations, but also allows several employees to work with them simultaneously, which is priceless in a corporate environment.

Why new versions?

To understand this issue, you need to imagine all the most significant changes that have occurred with this office suite.

As before, Excel is the second most popular program, which allows you to not only create simple tables, but even create fairly complex databases. As with other components, the "Office" button was added to it, clicking on which you can save the document in the format required by you, change it, or specify the required measures to protect it. The document security module has been significantly updated.

As for the most significant changes in Excel, we should note the correction of many errors in the formulas, which is why in the past versions quite often there were quite gross errors in the calculations.

I must say that the new version of the package is not just a set of office programs, but also a powerful tool, suitable for solving even very complex tasks. As you could understand, today we will consider an Excel spreadsheet.

What is it and what is it for?

The most significant change in the new versions of the "Office" is their completely redesigned interface, which was called the creators of Ribbon (tape). In the tapes, all 1500 teams are conveniently grouped into categories, so you do not have to search for them for long. In order to fully comply with these parameters, the developers added to Excel also improved summary tables.

Today we will consider Excel "for teapots". Pivot tables are a special tool in which the results of a process are visually grouped. Simply put, with their help you can see how many products each seller sold for his work shift. In addition, they are used in cases where it is necessary:

• Prepare analytical calculations for writing final reports.
• Calculate each of their indicators separately.
• Make a grouping of data by their types.
• Carry out a filtration and in-depth analysis of the obtained data.

In this article, we'll look at simple ways to create them. You will learn how an Excel spreadsheet helps create and analyze time series, create and disassemble a more detailed forecast.

Create the desired document

So how to create a pivot table in Excel? First, we need to compile a simple document into which we enter data for their subsequent analysis. One column must have one parameter. Let's take the simplest example:

• Time of sale.
• Sold goods.
• The cost of all sales.

Thus, between all the parameters in each particular column, a bond is formed: suppose that the sneakers were sold at 9 am, with the profit being n-rubles.

Formatting a spreadsheet

Having prepared all the initial information, put the cursor in the first cell of the first column, open the "Insert" tab, and then click on the "Summary table" button. A dialog box appears immediately , in which you can do the following:

• If you immediately click on the "OK" button, the Excel spreadsheet will immediately be displayed on a separate sheet.
• Fully customize the inference.

In the latter case, the developers give us the ability to determine the range of cells into which the desired information will be displayed. After that, the user must determine exactly where the new table will be created: on an existing one or on a newly created sheet. Clicking "OK", you immediately see a ready-made table in front of you. At this, the creation of pivot tables in Excel is almost complete.

On the right side of the sheet are those areas with which you will work. Fields can be dragged into separate areas, after which the data from them will be displayed in the table. Accordingly, the pivot table itself will be located in the left part of the workspace.

Holding down the "Goods" field with the left mouse button pressed, we send it to "Line Name", and the item "Sum of all sales" in the same way is forwarded to "Values" (on the left side of the sheet). That's how you can get the amount of sales for the entire analyzed period.

Creation and grouping of time series

To analyze sales for specific time intervals, you need to insert the relevant items in the table itself. To do this, go to the "Data" sheet and then insert three new bars immediately after the date. Select the column with the name of the product, and then click on the "Paste" button.

It is very important that all the newly created columns are inside an already existing table with the original data. In this case, you do not need to create the pivot tables in Excel again. You simply add new fields with the required data.

The formulas used

For example, the newly created columns can be called "Year", "Month", "Months-Years". To get the data we are interested in, each of them will have to write a separate formula for calculations:

• In the "Annual" we insert a formula of the form: "= YEAR" (referring to the date).
• The month should be supplemented with the expression: "= MONTH" (also with reference to the date).
• In the third column, we insert a formula of the form: "= CLICK" (referring to this for a year and a month).

Accordingly, we obtain three columns with all the initial data. Now you need to go to the "Summary" menu, click on any free space in the table with the right mouse button, and then in the opened context menu select "Update". Note! Carefully write the formulas in the Excel spreadsheets, since any error will lead to inaccurate forecast.

How to analyze sales by year?

Again, we hold down the "Year" item with the left mouse button, then drag it into the "Column name", after which the summary table begins to display the results of selling a particular type of product for all years. And if you also need to analyze the monthly sales? Similarly, we squeeze the LMC "Month", dragging it under the annual column.

To get visual dynamics of monthly changes in sales by years, the "Month" field should be dragged directly into the annual column that was previously moved to "Column Name". After completing this operation, you will see the table in the following view:

• The sum of sales of a specific product for the whole year.
• Dynamics of sales of each of them by years.

We remove data from the issue

Suppose that from the issuance we need to remove the data for October, since we do not have complete statistics on it. In the summary table we are looking for the "Report Filter", then drag the "Year - Month" to it.

Above the table appears a filter in which you want to check the box next to "Select multiple items". After that, in the list that appears, uncheck the box from October, then click on "OK".

Thus, you can add and remove elements from the filter, generating really relevant and necessary analyzes. The program automatically calculates the indicators for the months you are interested in, clearly showing the dynamics of changes in indicators.

We calculate the forecasts

Let's try to predict the results of sales for some time to come. To do this, you must first disable the totals so that they do not introduce inaccuracies into the generated forecast.

To do this, put the cursor in the "Total total" field, then click on the button "Delete the grand total". After that, we perform an automatic update of the Excel spreadsheet in the manner described above (click on the right mouse button, select "Update").

As a result, this data disappears from our summary table. To build a visual representation of the sales forecasts, we put the cursor on the year from which we plan to repulse, then click the "Graph Forecast Model" button with the left mouse button.

After that, a beautiful and informative schedule appears, in which the predicted results of the buying activity are presented in visual form on the forecast you are interested in. This will save your energy and save money, which can be used to purchase goods that are really needed.

We use other data sources to create a summary table

It's time to consider more complex Excel lessons. A summary table can be created based on documents that have been edited by other users.

The "Merge" function will help us in this. This program allows you to automatically merge the text of the document and the necessary addresses, names or other necessary information to create personalized forecasts, which in our case is required.

The merge function in the later versions of the "Office" remained virtually unchanged compared to Office 2003. However, queries to the databases for selecting the data source can be performed using Microsoft Query. To get data from an Excel PivotTable, you need two objects:

• The main document containing the information you need, as well as the fields that indicate the Excel program, what data should be inserted into the document. The main table is a standard table processor document, but you must specify in the Mail Merge Helper dialog box that it should be used as the primary table.
• A data source containing the information that is needed to compile the forecast. You can create a new table in the way that we mentioned above.

When the process of creating summary documents occurs, the program creates one copy for each of the records contained in the data source file. These files can be displayed as separate sections of a new document or used as sections of your pivot table.

Just click on the field you need with the left mouse button, and you can see the source data instead of the field names that appear in the main document. Fields can be inserted anywhere in the document.

Categories used

Also here you can use the ASK category, which means "request", and FILLIN, which means "fill", NEXT and NEXTIF, ELSE. You can also configure the parameters of those forecast items that will be displayed in the finished table. You can find the necessary record using a special filter or by simply sorting the data, as we mentioned above.

When you're ready to create a master document, the Merge dialog box provides the user with the ability to identify and fix individual errors before they occur. When everything is checked, you can safely press the "Merge" button. If the merge is to be done via e-mail, you will be prompted to install the mail program (if you do not have it installed).

In the event that an error occurs during the merge process, this indicates an incorrectly specified field in the foreign document, or the data used is presented in an unsupported format. Therefore, this procedure should be reviewed again.

In general, Excel (working with pivot tables in which we are considering) allows not only to conduct comparatively complex analyzes of purchasing power, but also to create forecasts based on them.