Many office users often face a number of problems when trying to create and edit any office documents. This is often due to the fact that companies use several office programs, the principles of which can vary greatly. Especially a lot of difficulties can create an Excel pivot table.
Fortunately, relatively recently appeared MS Office 2010-2013, 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 at the same time, which is simply invaluable in a corporate environment.
Why exactly the new versions?
To understand this issue, you need to imagine all the most significant changes that have occurred with this office suite.
As before, it is Excel that is the second most sought-after program that allows you to not only create simple tables, but even create fairly complex databases. As in other components, the “Office” button was added to it, by clicking on which you can save the document in the format you need, change it or indicate the required measures for its protection. The document security module has been significantly updated.
As for the most significant changes in Excel, it should be noted the correction of many errors in formulas, which is why in the previous versions often quite serious errors occurred 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 can understand, today we will consider the Excel pivot table.
What is it and what is it for?
The most significant change in the new versions of the "Office" is their completely redrawn interface, which was named the creators of Ribbon (tape). In the feeds, all 1,500 teams are conveniently grouped into categories, so you don’t have to look for them for a long time. To fully comply with these parameters, the developers added improved pivot tables to Excel.
Today we will consider Excel "for dummies". 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 shift. In addition, they are used in cases where it is necessary:
- Prepare analytical calculations for writing final reports.
- Calculate each indicator individually.
- Group data by its types.
- Perform filtering and in-depth analysis of the data.
In this article, we will look at simple ways to create them. You will learn how the Excel PivotTable helps you create and analyze time series, create and parse a forecast in more detail.
We create the necessary document
So how to create a pivot table in Excel? First, you need to draw up a simple document in which we enter the data for their subsequent analysis. There should be one parameter per column. Take the simplest example:
- Sale time.
- Sold item.
- The cost of all sales.
Thus, between all the parameters in each particular column, a connection is formed: suppose that the sneakers were sold at 9 a.m., and the profit was n-rubles.
Table formatting
Having prepared all the initial information, place the cursor in the first cell of the first column, open the "Insert" tab, and then click on the "Pivot table" button. A
dialog box will immediately appear in which you can do the following operations:
- If you immediately click on the “OK” button, the Excel pivot table will immediately be displayed on a separate sheet.
- Fully customize the output.
In the latter case, the developers give us the opportunity to determine the range of cells into which the desired information will be displayed. After that, the user must determine where the new table will be created specifically: on the existing one or on the newly created sheet. By clicking “OK”, you will immediately see the finished table in front of you. This completes the creation of pivot tables in Excel.
On the right side of the sheet are the areas that you have to work with. 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 on the left side of the workspace.
Holding down the “Product” field with the left mouse button pressed, we send it to the “Line Name”, and the item “Sum of all sales” is redirected to “Values” in the same way (on the left side of the sheet). This is how you can get the amount of sales for the entire analyzed period.
Creating and grouping time series
To analyze sales by specific time periods, you must insert the relevant items in the table itself. To do this, go to the "Data" sheet and then insert three new columns immediately after the date. Select the column with the name of the product, and then click on the "Insert" button.
It is very important that all newly created columns are inside an existing table with the source data. In this case, creating pivot tables in Excel is not required again. You will simply add new fields with the data you require.
Formulas Used
For example, the newly created columns can be called "Year", "Month", "Months-Years". To get the data we are interested in, we will have to write a separate formula for the calculations in each of them:
- In the "Annual" we insert the formula of the form: "= YEAR" (referring to the date).
- The month must be supplemented with the expression: “= MONTH” (also with reference to the date).
- In the third column, insert the formula of the form: "= CONNECT" (referring to the year and month).
Accordingly, we get three columns with all the source data. Now you need to go to the "Summary" menu, right-click on any free space in the table, and then select the "Update" item in the context menu that opens. Note! Carefully write the formulas in the Excel pivot tables, since any error will lead to an inaccurate forecast.
How to analyze sales by year?
Again, hold down the “Year” item with the left mouse button, then drag it to the “Column Name”, after which the pivot table begins to display the results of the sale of a specific type of product for all years. And if you need to also analyze the monthly sales? In the same way, hold down the “Month” LMC by dragging it under the annual column.
To get the visual dynamics of monthly changes in sales by year, the "Month" field must be dragged directly into the annual column, previously moved to the "Column Name". After completing this operation, you will see a table in the following view:
- The sum of sales of a particular product for the whole year.
- The sales dynamics of each of them by year.
We remove data from the issuance
Suppose that we need to remove data for October from the issue, since we do not have complete statistics on it. In the pivot table, look for the “Report Filter”, and then drag “Year - month” onto it.
A filter appears above the table in which you need to select the checkbox next to the "Select multiple items" item. After that, uncheck the box in October in the list that appears, then click on “OK”.
Thus, you can add and remove elements from the filter, forming really relevant and necessary analyzes. The program will automatically calculate indicators for the months that interest you, visually displaying the dynamics of changes in indicators.
We calculate forecasts
Let's try to predict the results of sales for some time to come. To do this, you should first turn off the totals so that they do not introduce inaccuracies in the generated forecast.
To do this, put the cursor in the "Total" field, and then click on the "Delete total" button. After that, we automatically update the Excel pivot table in the manner described by us above (click on the right mouse button, select "Update").
As a result, this data disappears from our pivot table. To build a visual display of sales forecasts, we place the cursor on the year from which we plan to build on, and then left-click on the button “Graph Forecast Model”.
After that, a beautiful and informative chart appears, on which the forecasted results of buying activity according to the forecast you are interested in are presented in a visual form. This will save your energy and save money that can be spent on the purchase of really necessary goods.
We use other data sources to form a pivot table
It's time to consider more advanced Excel tutorials. A pivot table can be created based on documents that have been edited by other users.
The function "Merge" will help us in this. This program allows you to automatically combine the text of the document and the necessary addresses, names or other necessary information to create personalized forecasts, which is required in our case.
The merge feature in later versions of the Office has not changed much compared to Office 2003. However, database queries to select a data source can be performed using Microsoft Query. To get Excel pivot table data, you must have two objects:
- The main document containing the information you need, as well as fields that tell Excel what data to insert into the document. The main table is a standard table processor document, however, you must specify in the Mail Merge Helper dialog box that it should be used as the main one.
- A data source containing the information that is necessary for making a forecast. You can create a new table in the way that we indicated above.
When the process of creating consolidated documents takes place, the program creates one copy for each of the records that are 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 in”, NEXT and NEXTIF, ELSE. You can also configure the parameters of those forecast points 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 are ready to create a compound document, the Merge dialog box provides the user with the opportunity to identify and eliminate individual errors before they appear. When everything is checked, you can safely click on the "Combine" button. If the merger should be done via e-mail, you will be asked to install the mail program (if you do not have it installed).
In the event that some error occurs during the merge, this indicates an incorrectly specified field in a foreign document, or the data used is presented in an unsupported format. Therefore, this procedure is worth reviewing again.
In general, Excel (the work with pivot tables in which we are considering) allows us not only to conduct relatively complex analyzes of purchasing power, but also to create forecasts based on them.