• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel PivotTable Profit and Loss

You are here: Home / Excel PivotTables / Excel PivotTable Profit and Loss
excel pivottable p&L
July 30, 2020 by Mynda Treacy

Creating an Excel PivotTable Profit and Loss statement is surprisingly easy. And because it’s a PivotTable you can team it with Slicers to make it interactive. 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.

excel pivottable profit and loss animation

Watch the Video

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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.

excel pivottable profit and loss data

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.

insert excel pivottable

Step 2: Build the PivotTable

Add the Account Group and Account fields to the Rows and add Actual and Budget to the Values:

build an excel pivottable

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:

rearrange pivottable row order

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:

excel pivottable calculated item

We’ll create the Gross Profit item first:

excel pivottable calculated item for gross profit

Then one for Net Profit:

excel pivottable calculated item 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:

excel pivottable collapse rows

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

excel pivottable subtotal position

Step 7: Insert Blank Line after Each Item

excel pivottable blank rows

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:

excel pivottable format style

Make sure ‘Preserve cell formatting on update’ is on by right clicking the PivotTable > PivotTable Options > Layout & Format tab:

excel pivottable options

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:

excel pivottable buttons

Remove the Grand Total: right click the Grand Total label > Remove Grand Total:

excel pivottable remove grand total

Step 9: Add a Calculated Field for the Variance (Optional)

excel pivottable calculated field

In the formula field subtract the budget from the actual:

excel pivottable profit and loss variance calculated field

You can also add one for the % Variance if you want:

excel pivottable profit and loss percent variance calculated field

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:

excel pivottable profit and loss variance conditional formatting

Repeat for the Cost of Goods Sold and Expenses Variance % cells.

Modify the Conditional Formatting rules: Home tab > Conditional Formatting > Manage Rules…

modify conditional formatting

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

add slicers for pivottable profit and loss

I’ll add them for Region and Financial Year:

add slicers for region and financial year

And your PivotTable Profit and Loss is done:

excel PivotTable Profit and Loss completed

excel pivottable p&L

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.

Excel PivotTable Percentage Change

Excel PivotTable Percentage Change calculation is dead easy with Show Values As. Add conditional formatting, and Slicers for interactivity.


Category: Excel PivotTables
Previous Post:getting started with api's in power queryGetting Started with API’s in Power Query
Next Post:Connecting to an OAuth API Like PayPal With Power QueryConnecting to an OAuth API Like PayPal With Power Query

Reader Interactions

Comments

  1. John Densham

    March 9, 2023 at 6:55 pm

    Hi Myanda,

    Earlier this week, I was asked by a Manager to draw up a P&L for a Camp ( Resort ) and after searching “hi&lo” for a “how to create a P&L in a pivot table” tutorial, I stumbled on this from 2020 ..
    .. it was exactly what I needed !!
    I had to tweak the “calculated items” formulas for Gross & Net Profit as our Company shows revenue as ( – ), but aside from that, and a couple of revenue variance ( % ) that seem odd, I’m very happy with the result.

    Reply
    • Mynda Treacy

      March 10, 2023 at 8:03 am

      So pleased to hear that, John!

      Reply
  2. F.M Rathod

    October 16, 2022 at 5:10 am

    Dear Teacher

    I am 68 and never learned so fast in my life, I must say you are the best teacher and I salute you for it.

    Be Blessed

    Rathod

    Reply
    • Mynda Treacy

      October 17, 2022 at 4:27 pm

      Wow, that’s amazing to hear, Rathod! Thank you for your kind words. Never stop learning 🙂

      Reply
  3. Anyim

    April 3, 2022 at 7:58 pm

    Very helpful, indeed you are a teacher.
    Have you covered the ones involving trial balance and balance sheet? Do you also have as any tutorial on macros and VBA?

    Reply
    • Mynda Treacy

      April 3, 2022 at 8:29 pm

      Hi Anyim,

      No, I haven’t done any on trial balance and balance sheet. A trial balance should be straight forward, and the Balance Sheet will use the same techniques used here. You can find all our VBA tutorials here: https://www.myonlinetraininghub.com/category/excel-vba

      Mynda

      Reply
  4. Giovanni

    September 23, 2021 at 3:47 am

    Do you have any tutorial how to create P/L using power Pivot, I have my normalized tables in SQL Server, wondering how to do using DAX . Thank you.

    Reply
    • Mynda Treacy

      September 23, 2021 at 8:47 am

      Hi Giovanni,

      No, I don’t have any Power Pivot P&L tutorials. It’s notoriously difficult to create them with Power Pivot.

      Mynda

      Reply
  5. Christina

    May 20, 2021 at 2:20 pm

    Hi Tracy,

    When I insert the Calculated Items to get the Revenue mins Expense, the Excel show “This PivotTable report field is grouped. You cannot add a calculated item to a grouped field.”

    Can you please advise what went wrong? How can I fix this?

    Thanks a lot.

    Christina

    Reply
    • Mynda Treacy

      May 20, 2021 at 8:57 pm

      Hi Christina,

      Sounds like a field in your Pivot Table or cache is grouped. Most likely the date field. Right-click on the fields > ungroup.

      Mynda

      Reply
  6. Celerina Grady

    April 25, 2021 at 11:15 pm

    Hi Mynda,

    I watched your webinar training how to create interactive dashboard and it is excellent. I was able to create my own Sales Order dashboard with your excellent tools and input from the webinar using one big data information. My boss really loves it and he wanted me to add more information using the same dashboard. Unfortunately, the additional information is a totally separate set of data. Am I able to combine two or three sets of data in one dashboard?

    Reply
    • Mynda Treacy

      April 26, 2021 at 8:46 am

      Great to hear my tutorial was helpful. If you want to work with multiple datasets you can either consolidate them all into one table using Power Query, or use Power Pivot to create a model and related tables that can then be summarised in the same PivotTable.

      Reply
  7. Steven Alker

    April 14, 2021 at 11:49 pm

    Dear Mynda

    Simply wonderful!

    By the end of the week, I hope to have completed your free tutorials and webinars, to bring 25 years of Excel use up to a new level. However, I am a pianist and I have approached this a bit like learning a new Chopin Polonaize, which I have done whilst watching and noting your techniques.

    The piano piece is now near to performance standard, but it will not be fit for listening to until my music teacher has a chance to show me how to do it properly and to eliminate mistakes.

    So, I need to ask you which of your courses I should subscribe to so that I can achieve my goal to become a consultant working on a contract basis, using Excel at the highest level.

    I recognise that I have a lot of practising to do, both in Excel and on the piano before I can perform before an audience, so using your courses seems to be the best way I have identified out of about 12 vendors of Excel courses on the web.

    Where should I start Mynda?

    Sincerely

    Steve

    Reply
    • Mynda Treacy

      April 15, 2021 at 9:43 am

      Hi Steve,

      It’s great to hear you found our tutorials helpful and are keen to expand your Excel skills. To work competently as a consultant you need broad Excel skills, as I’m sure you expect. I therefore recommend the following courses:

      • Excel Expert Course – Advanced Excel. This is going to cover everything from the basics (you can skip what you know) right through to the new dynamic array functions, PivotTables, Data Validation, Conditional Formatting and more.
      • Power Query Course – Power Query can automate a lot of the data gathering and cleaning tasks we used automate with VBA, but it’s much quicker and easier to learn.
      • Power Pivot Course – Power Pivot allows Excel to work with big data spread across multiple tables like a relational database and it has its own function language called DAX, that extends the capabilities of regular PivotTables.
      • Excel Dashboards Course – Reporting skills will be key as a consultant. This course will equip you to build dashboard reports for any organization.

      There is detailed information at the above links, but if you have any questions, please get in touch via email (website at MyOnineTrainingHub.com) where we can help you further.

      Mynda

      Reply
      • Steven Alker

        April 16, 2021 at 1:19 am

        Thanks, Mynda

        I have signed up for the first two, and will then proceed to the next ones.

        I do hope that you will forgive the piano analogies, but as you amply demonstrate, one needs to be both knowledgeable and fluent to be credible in front of a client (Or an audience!)

        So, the courses will become the start of a lot of practice on the keyboard and with the mouse to memorise all of the details so that when I present to a client, the presentation flows. I have no intention of showing them exactly how to do what I offer, most CEOs and FDs are too busy to become involved at that level, and in any case, from the messes they make of their sales plans and dashboards, they wouldn’t have a clue what was going on.

        I bet that the Chopin reaches performance standard before my Excel gets there!

        From the world of CRM and relational databases, I was often assigned a junior manager to guide me through their “Requirements” Read, “Learn what he does and then be able to do it so we don’t need to hire him again, no matter how good he is”

        It never worked!!

        Sincerely

        Steve

        PS I seem to have been signed up as a member of the Excel Expert course, but not been offered the same option on Power Queries. Do I need to do anything?

        Thanks again.

        Reply
        • Mynda Treacy

          April 16, 2021 at 9:09 am

          Hi Steve,

          Great to have you join our courses. You should see them both available when you login next.

          I hope you enjoy them. We’re here if you have any questions along the way, but please use email or the forum going forward.

          Mynda

          Reply
  8. rami

    February 25, 2021 at 4:55 am

    excellent

    Reply
    • Mynda Treacy

      February 25, 2021 at 9:03 am

      Glad you think so, Rami!

      Reply
  9. Simona

    February 13, 2021 at 4:12 am

    I appreciate very much these lesson.
    They broaden my excel knowledge and they are very close to my working style.
    Simona

    Reply
    • Mynda Treacy

      February 13, 2021 at 1:58 pm

      So pleased to hear that, Simona!

      Reply
  10. Marc Bosman

    November 9, 2020 at 5:44 am

    Hello Mynda,

    I have used this presentation and the result looks very good, thank you for this.

    Now I look for a way to add a column showing actual and budget numbers as percentage of revenue. Example: $93,427 in the new column would show as 14.90% of Revenue Total $626,954.
    What is the best way to calculate this new column, can this be done without DAX or measures, because I am not yet familiar with DAX.

    Many thanks for an answer.

    Reply
    • Mynda Treacy

      November 9, 2020 at 9:21 am

      Hi Marc, you need Power Pivot and DAX to add a column that calculates a percentage based on the total of another column.

      Reply
      • Marc Bosman

        November 10, 2020 at 8:29 am

        Hello Mynda,
        thank you for answering.
        As I thought, I need to study some more.
        I’d better get cracking.

        Reply
        • Mynda Treacy

          November 10, 2020 at 9:23 am

          If you want to get up to speed with Power Pivot, please consider my Power Pivot course which will teach you how to write these DAX measures and more.

          Reply
  11. Marlon Halliday

    September 19, 2020 at 12:39 pm

    Hi Mynda,

    Good presentation. Have you done something similar in power bi or power pivot?

    Thanks
    Marlon

    Reply
    • Mynda Treacy

      September 19, 2020 at 6:11 pm

      Thanks, Marlon. No, I haven’t done this in Power BI or Power Pivot yet, sorry.

      Reply
  12. MANOJ SARANGI

    September 9, 2020 at 8:08 pm

    Hi, Your presentation is excellent, very clear and understandable. Thanks

    Reply
    • Mynda Treacy

      September 9, 2020 at 8:33 pm

      Thanks for your kind words, Manoj!

      Reply
  13. Eduardo Rodriguez

    August 6, 2020 at 3:34 am

    I Love Your explanation. I would like to take more of your courses!

    Reply
    • Mynda Treacy

      August 6, 2020 at 8:44 am

      Glad it was helpful, Eduardo!

      Reply
  14. Nidia

    July 31, 2020 at 12:02 pm

    Hi Mynda,
    I absolutely loved the pivot tables for profit and loss. My only issue is the data we get from our accounting system is not split into two columns for actual and budget figures. Its tabular form but it comes trough a separate line telling me which is Actual and which is Budget. Thus, I can make it into a pivot but I cant do the calculated fields for the variances. Do you know if power pivot can help with this. I have not explored that area but trying to figure out better ways to speed up our process at work. Thanks!

    Reply
    • Mynda Treacy

      July 31, 2020 at 2:42 pm

      Hi Nidia,

      You can use Power Query to Pivot the actual and budget figures into separate columns. If you’re not sure how to do this, post your question and sample Excel file in our forum and we’ll show you how.

      Mynda

      Reply
  15. Pieter Etzebeth

    July 30, 2020 at 8:39 pm

    I need some help with this one. The Calculated field and Calculated Item button is greyed out in my pivot table. My data is in the Data Model. Is that the reason for the greyed out options and how can I fix the problem.

    Reply
    • Mynda Treacy

      July 30, 2020 at 8:43 pm

      Correct. When you put your data in the data model aka. Power Pivot, you can’t use calculated fields and items. In Power Pivot you must write DAX measures. To fix it, create a regular PivotTable without adding the data to the data model.

      Reply
      • Rob

        July 9, 2021 at 10:24 am

        In case it’s necessary to use data model (eg to get benefits of multi-table pivot), can DAX measures be used to replicate the ‘gross profit’ calculation?

        Reply
        • Mynda Treacy

          July 9, 2021 at 1:10 pm

          You can build P&Ls with Power Pivot, but it’s not easy!

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.