Starting with Excel PivotTables, functions are available to create custom calculations by the user. The spreadsheet allows you to make a pre-established summary of all the value fields, combining the data that originates from other statistics.
But these functions or formulas may not work, so anyone can choose to create their own custom calculations. In this sense, if Excel does not have a way to calculate a data that you want to add to the table, it is possible to provide it manually.
The mathematical processing system included in the program will do the rest of the work, identifying the elements and performing the necessary calculations. It can be taken as an example that it is decided to place a formula of commissions for sales made by each worker.
Adding said algorithm, Excel will proceed to calculate and it will be reflected automatically after entering the determined value (value of the product sold).
Create Excel Pivot Tables
You may have reached the point where you can insert or create functions for custom calculations in Excel PivotTables. But it is likely that the table you are going to work on does not yet have that name and you need to create one.
To do this, you must create a common structure from the Excel table creation tool and fill it with the relevant data. It should be noted that you can write several lines in the same Excel cell.
In this way, with all the information captured, we fully select said table with the mouse pointer (shade it). Then, we go to the tab at the top called “Insert” and select the option located on the left side that says “Pivot table”.
It will show us a pop-up window with a series of settings of which we only have to make sure that the one that says “Create new spreadsheet” is checked. It will take us to a section where, on the right side, the table data will be shown in the form of a list, and below, four categories.
In them, the sections of the data must be dragged depending on the user’s considerations, these are: filters, values, rows and columns. Thus, once each one is assigned, the table will be reflected in the spreadsheet, but this time with dynamic characteristics.
It is possible that the currency format must be assigned to the values that are of this nature, such as values in euros, dollars, among others. This can be achieved from the same section where the table sections were dragged. This should be assigned to “Values”.
Create custom calculations
When Excel pivot tables are created, a separate page is made in our spreadsheet, where it shows us the original structure in one, and the dynamic one in another. In this sense, the latter allows us to perform different operations or functions without having to manipulate the original table.
So, to create custom calculations, we go to the page where the table in question is located and select it. Then, we will go to the tab of the program called “Analyze” and select the option “Fields, elements and sets”.
A menu will slide, and you will click on the first choice of it, which is called: “Calculated field”. A pop-up window will appear, and we will customize the calculated field by filling in the section that says “Name” with the one that suits us.
Below, there will be another section where you can place the formula that will be needed depending on the case or calculation that you want to perform. This window allows you to use the sections of the table values to add them to the formula automatically without having to be assigned manually.
Next, click on “Accept” and a new column will be created, filled with the calculations requested by the user based on the formula entered.
