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:
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:
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:
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:
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:
We will click OK, and will have the new field inserted:
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: