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
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