What Pivot Table can’t do (P&L Statement), PowerPivot Can

A lot of Excel users confuse Pivot Table with PowerPivot Table.

They thought they are referring to the same functions. They are not.

PowerPivot is considered an upgrade version of Pivot Table. When we tried to create a Profit and Loss Statement with Pivot Table, we faced a few problems.

Problem #1 – Pivot Table cannot calculate Gross Profit and Net Profit

Pivot Table cannot calculate Gross Profit and Net Profit. It was not designed to do so. Hence, a lot of accountants ended up abandoning Pivot Table and do back to manual Profit and Loss Statement. But that has changed.

With Power Query, one of the new functions developed for Excel, you can now present Gross Profit and Net Profit in the Pivot Table. This is very helpful because we can now see the full P&L statement and drill down to look at the details when the need arises. Compared to last time, we have to look at the details in another worksheet or did a lot of filters. Now we don’t have to.

Problem #2 – Pivot Table cannot calculate Opex as a percentage of sales

If you have ever attempted to calculate spending as a percentage of sales, you will know what I mean. Pivot Table is not designed to perform calculations. At best, it can only calculate the percentage based on the grand total. With Power Pivot, you can calculate the total sales and use it to divide against each cost item, just like a P&L statement.

I hope this article explains the difference between Pivot Table and PowerPivot. Don’t assume they are the same again.

We have a course on data analytics. In it, we show you how to use both Power Query and PowerPivot. It is a WSQ course and a training grant is available. If you are keen to learn these 2 functions, head over to the business analytics course and take a look. You won’t regret it.