Essential Excel Concepts: Pivot Tables

Pivot tables are used frequently in the business world for many reasons. However, in this blog, we are going to highlight its aggregation value for data rollups.

Let us pause before we dive in. What does aggregation and rollups mean? Aggregation and rollups refer to summing or group data to help summarize your results in meaningful ways. We will discuss this more later. However, I will use these terms often, so I wanted to define them upfront.

Here I already have my data in a table. Let us perform some analysis on the data first and then rollup our results in a pivot table.

We are going to add new columns and add logical functions for discounts and a new Total Cost column.

Let us start by adding a Total Cost column by adding a column to the right of the Labor Cost column now by right-clicking in the cell beside Labor Cost to add a new column titled Total Cost.

Now we are going to create a function that sums up the Item Cost and Labor Cost values to equal the Total Cost with the SUM () function (we could also just type = Cell 1 + Cell 2 for summing).

The result will be as follows.

We will now use a logical function to test if the SKU is in season or not with using an IF function. If the SKU is not in season, we will add a discount cost to the item.

We will use the following function where we test if the cell value is No within the In Season column. If this value is TRUE (No is the cell value), we will multiply the Total Cost by .25 (25%).

Notice in the function, 0 is a criterion at the end. If the value in the cell is “Yes,” 0 will be added in the Discount column since there are no discounts applied if an item is in season.

Here is the resulting table after the logical function is applied.

Now we will add another column for the final discounted price and deduct the discount (if any) to the Total Cost with a new function. Here I am using = Total Cost – Discount rather than using the sum function. However, the sum function can be applied to get the same results.