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




608 views

Related Posts

See All

Commentaires


bottom of page