Divide One Column by Another in Pivot Table Calculated Field

Excel is perhaps the best overall product for preparing and presenting our data. Pivot Tables are excellent tools in Excel that allow us to group the data based on different information.

In the example below, we will show how can you manipulate columns of Pivot Tables, more precisely, how to divide one by another.

Create a Pivot Table

For our example, we will use the table with sales figures from various years presented in months, and buyers that company had to achieve those sales:

Table

Description automatically generated with medium confidence

To insert a Pivot Table, we will select our range, then go to Insert Tab >> Tables >> Pivot Table >> From Table/Range:

On the window that appears, we will simply click OK, and our Pivot Table will be created:

Graphical user interface, text, application, email

Description automatically generated

Divide one Column By Another in Pivot Table Calculated Field

Now that we have the table created, we will add the Month field in Rows, and the Sum of Revenues and Sum of Buyers in Values fields:

Graphical user interface, application

Description automatically generated

Our Pivot Table looks like this:

Next, we want to see how much revenue the company generates per buyer. To add this field, we will click on our table, and then go to PivotTable Analyze >> Calculations >> Fields, Items, & Sets >> Calculated Field:

Graphical user interface, application

Description automatically generated

On the pop-up window that appears, we will give the name to our field- “RevenuePerBuyer” and we will choose the revenues and buyers from our Pivot Table, and finally divide these fields:

Graphical user interface, application

Description automatically generated

We will click OK, and will have the new field inserted:

Table

Description automatically generated

We will format these numbers as dollars. We can see that how the Calculated Field works is that the name of the new column always begins with “Sum of…” and then our name is added, as Sum is the custom option in the Values field. This is what our final table looks like:

Table

Description automatically generated

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.