top of page

PivotTable Profit & Loss

Creating a Profit Loss PivotTable has many benefits

The Benefits of Creating a PivotTable Profit & Loss

1️⃣ Easily extend dates using timeline slicers

When you create a PivotTable, you can add what’s called a timeline slicer

That makes it really easy for you to control the dates that you’re showing with just the click of a button

2️⃣ Expand and collapse fields

PivotTables make is really simple to expand details of any fields that you want to expand

Here I segmented by operating expenses by cost type…

allowing me to see a quick glance of what I’m spending across each category

and then I can expand to see the accounts that make up that cost category / section with the click of a button

3️⃣ Double click to drill into details

With PivotTables, you can easily generate a report showing the details behind any totals by just double clicking on the value

After you double click, a new tab will be created instantly showcasing all of the details behind that value

4️⃣ Set slicers to filter for different sections

Looking to only show a segment of your Profit & Loss?

Perhaps you only want to see results for a specific department

Or perhaps you only want to see your Revenue & COGS

When you create a slicer, updating the report for those values is as simple as a click of a button

5️⃣ PivotCharts are a game changer

PivotCharts put your charts on steroids

It allows you to control the outputs from your chart just as much ease as you would control a PivotTable

So when you update a slicer, or a timeline

The chart automatically updates as well (Learn more about All The Excel charts here)

There are many other benefits of using PivotTables…these are just a few

Now let’s discuss how to create a PivotTable Profit & loss

Creating a PivotTable Profit & Loss

1️⃣ Export your Profit & loss

Start by exporting your Profit & Loss from your accounting software on a monthly basis

If you don’t have an accounting software, you can go ahead and create your own profit & loss

2️⃣ Add 2 columns for Grouping 1 and Grouping 2

These 2 columns will allow you to aggregate your data in your PivotTable so you can have the right mix of detail vs summary as you expand and collapse

3️⃣ Convert your Profit & Loss to a table

Highlight your data, and hit Control + T. There are so many benefits to working in a table format…one of the biggest being your ability to load it into the Power Query Editor

4️⃣ Load your Data into Power Query

Power Query may be my favorite excel tool - there’s so much you can do with it.

Think of it like a way to write macros on your data with a simple click of a button

Anytime you refresh your data, those “macros” get applied

To load into Power Query, navigate to “Data” and then under “Get & Transform” hit “From Table / Range”

5️⃣ Unpivot your data

Now that you’re in the Power Query editor, we’re going to unpivot our dates so that they show in a column instead of across multiple columns going vertically

Simply click the columns you don’t want unpivoted, and click “unpivot other columns”

This is my favorite Power Query function!

6️⃣ Set your PivotTable

Now that we have our data ready to use in our PivotTable, let’s add fields to our rows in this order

Grouping 1 > Grouping 2 > Account

Then for the values we’ll add our value field

And for the columns, we’ll add our date

7️⃣ Set a custom list

This is looking good, but we have one last important step - creating a custom list so we can sort for the correct order of our P&L

To do that, navigate to File > Options > Advanced > Custom List

Then write out the custom list for Grouping 1

Navigate back to your PivotTable and hit Sort

8️⃣ Add a timeline slicer & make it all pretty

Now let’s make a bit pretty by changing the styling, adding a timeline slicer, and giving a nice title

There you go! You just created a PivotTable Profit & Loss


Related Posts

See All


bottom of page