While you’re at it you might as well add some conditional formatting to make reading, what is usually a drab report, quick and easy.
Watch the Video
Download Template Workbook
Enter your email address below to download the sample workbook.
Excel PivotTable Profit and Loss Step by Step Instructions
My data (shown below) is in a tabular layout with each account classified into an ‘Account Group’. These account groups represent the different sections of a Profit and Loss statement.
Step 1: Insert a PivotTable
Select the data > Insert tab > PivotTable. In the dialog box choose whether you want it in a new sheet or existing sheet.
Step 2: Build the PivotTable
Add the Account Group and Account fields to the Rows and add Actual and Budget to the Values:
Step 3: Rearrange the Account Group order
The Revenue accounts should be listed first, then Cost of Goods Sold, then Expenses. Left click and drag Revenue to the top of the row labels area:
Step 4: Add Calculated Items for Gross Profit and Net Profit
Select one of the Account Group cells in the row labels area of the PivotTable > PivotTable Analyze tab > Fields, Items & Sets > Calculated Item:
We’ll create the Gross Profit item first:
Then one for Net Profit:
They should now appear in the PivotTable row labels.
Step 5: Collapse the Gross Profit and Net Profit Items
This hides the underlying account detail that makes up these values:
Also move Gross Profit up under Cost of Goods Sold using the left click and drag technique covered in step 3.
Step 6: Show Subtotals at Bottom of Group
Step 7: Insert Blank Line after Each Item
Step 8: Format PivotTable
Choose a plain style from the gallery on the Design tab or create your own with no formatting, as I’ve done for this example. Then add cell borders for the sub-total and total rows:
Make sure ‘Preserve cell formatting on update’ is on by right clicking the PivotTable > PivotTable Options > Layout & Format tab:
Rename the Actual and Budget headings to remove the ‘Sum of’ by replacing them with a space. We need to add the space at the front of the labels because we cannot use names that are also in the field list.
Hide the Expand and Collapse buttons and Field Headers:
Remove the Grand Total: right click the Grand Total label > Remove Grand Total:
Step 9: Add a Calculated Field for the Variance (Optional)
In the formula field subtract the budget from the actual:
You can also add one for the % Variance if you want:
Step 10: Conditional Formatting (Optional)
Profit and loss statements make for dry reading, but we can make it quicker for our audience to interpret with the help of some conditional formatting to visually indicate whether the variance is positive or negative using traffic lights.
Positive income variances are good, but the opposite is true for expense variances, so we need two conditional formatting rules.
Set up the income rule first by selecting just the income related Variance % cells: Revenue, Gross Profit and Net Profit > Home tab > Conditional Formatting > Icon Sets:
Repeat for the Cost of Goods Sold and Expenses Variance % cells.
Modify the Conditional Formatting rules: Home tab > Conditional Formatting > Manage Rules…
Select each rule and edit them as per the settings below:
Step 11: Add Slicers (Optional)
If your source data has fields that you’d like to filter your Profit and Loss by, you can add Slicers by selecting a cell in the PivotTable > Insert tab > Slicers
I’ll add them for Region and Financial Year:
And your PivotTable Profit and Loss is done: