Adding Calculated Fields to Pivot Table P&L [part 3 of 6]

Posted on February 25th, 2010 in Learn Excel , Pivot Tables & Charts - 14 comments

This is part 3 of 6 on Profit & Loss Reporting using Excel series, written by Yogesh

Data sheet structure for Preparing P&L using Pivot Tables
Preparing Pivot Table P&L using Data sheet
Adding Calculated Fields to Pivot Table P&L
Exploring Pivot Table P&L Reports
Quarterly and Half yearly Profit Loss Reports in Excel
Budget V/s Actual Profit Loss Report using Pivot Tables

Adding Calculated Fields to Profit & Loss (P&L) Pivot ReportThis is continuation of our earlier post Preparing Pivot Table P&L using Data. We have learned to prepare Pivot Table P&L. The report prepared in last post has all the major data to prepare a P&L but it is not a complete P&L report. Now we will add calculated fields to make it a complete P&L. We will also format data points to make it a complete P&L report.

We need the following extra values in our P&L

  • Gross Margin = Sales – Cost of Goods Sold
  • Gross Margin % = Gross Margin / Sales
  • Operating Expenses = Rent + Personnel Cost + Utilities + Consumables + Misc Exp
  • Operating Profit = Gross Margin – Operating Expenses
  • Operating Profit % = Operating Profit / Sales

Making these extra fields in Pivot Table using Calculated Fields Features:

Click on PivotTable Tools > Calculated Items to define a new calculated field. [tutorial: how to add calculated fields to pivot tables]

Check out below screencast. Just replace the Field Names and Formulas to add the rest of the calculated fields.

Adding Calculated Fields in Pivot Tables - Ex. Gross Margin Calculation in P&L

Once you have added all the calculated fields to Pivot Table, these will start showing at the end of PivotTable. You will need to drag them to their respective position on P&L

Drag Fields inside Pivot Table

Now you are almost ready with your P&L report, only few steps more to format data are required. You may have noticed that % Fields are showing as zero as of now. This is because they are formatted as numbers instead of percentages.

Do not use standard cell formatting to format them, instead use Value Field Setting Option to format pivot table fields. This one is useful as it will show data always as per the format set for particular field. Use Percentage format for % fields and Accounting Format for other value fields.

Number Format - Pivot Table Fields

Few More steps like formatting certain fields as bold and italics and your PivotTable P&L is ready, you can play with is as any other pivot table and start presenting on various dimensions with few clicks

Make sure that you have correctly setup “Preserve Cell Formatting on update” option under pivot table options. This will help you retain the same format while you play with your PivotTable P&L.

Enable Preserve Cell Formatting Setting in Pivot Report

The Final Profit & Loss Pivot Report

Once you finish all the formatting and settings, this is how the final report should look like:

profit-loss-report-with-calculated-fields

Download the profit and loss report excel file

Download the excel file and play with it to understand the techniques discussed in this post.

What Next?

In the next part of this series, we explore this pivot table further, Continue reading.

Added by PHD:

  • Please share your feedback and ideas for this series using comments. Yogesh and I will reply to your questions. Also, say thanks if you like the idea and want to learn more.
  • Sign-up for PHD E-mail newsletter because you will get updates as new posts are live.
Yogesh Gupta - CA, Excel BloggerYogesh is an accountant with 13 years of experience in India and abroad. His specialties are budgeting and costing, supplier accounting, negotiation of contracts, cost benefit analysis, MIS reporting, employees accounting. He writes about excel at http://www.yogeshguptaonline.com/
Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

14 Responses to “Adding Calculated Fields to Pivot Table P&L [part 3 of 6]”

  1. Jim says:

    This works nicely when you have a separate field for each profit & loss account – Sales, Cost of sales, etc. What do you do if you have one field, titled “Account”, and another titled “Amount”. Potential records within account would be Sales, Cost of sales, etc. I can’t calculate “Account” minus “Account”, or can I??

  2. m-b says:

    @ Jim: In that case I would use a pivot table as an intermediate step and then use GETPIVOTDATA in another tab to build the P&L. That way you have much more flexibility.

  3. Ewoud Vogels says:

    Those calculated fields are great, can’t wait for next lesson!

  4. Jeff says:

    When I try to add the calculated fields, only the first 3 show up in the pivot table. But when I use the ‘List Formulas’ button, it shows that there are 5 calculated fields. Any idea why only the first 3 only show in table?

  5. [...] structure for Preparing P&L using Pivot Tables Preparing Pivot Table P&L using Data sheet Adding Calculated Fields to Pivot Table P&L Exploring Pivot Table P&L Reports Quarterly and Half yearly Profit Loss Reports in Excel Budget [...]

  6. [...] structure for Preparing P&L using Pivot Tables Preparing Pivot Table P&L using Data sheet Adding Calculated Fields to Pivot Table P&L Exploring Pivot Table P&L Reports Quarterly and Half yearly Profit Loss Reports in Excel Budget [...]

  7. rolo says:

    I would like to know which animated gif software is used here at chandoo.

    Thanks !

  8. Josh says:

    Thanks, this was a massive help for me. Found the article via Google. Saved me oodles of time and complication.

  9. dhooni says:

    hi,
    Thank you for this tutorial section.!!!! great job.
    But i have a question. When I enter the formulas in pivot table via pivot table>tools>formula then i did the same as its done here. The first three formula of gross margin and its % came  but other two operating profit and its % didnot came in the table, though it is showing in the list formulas .

    Could you help me with this. may b i am making some mistakes

    dhooni 

  10. James says:

    This is great work,i am  new at excel but i have found this posting of great help, in coming up with profit and loss. Fantastic work on pivot tables 

  11. Moiz says:

    I am facing the same problem what dhooni is facing, if you can help with the problem.

  12. Casey says:

    Hi all, I am working on a P&L constructed in a form of pivot table with a # of calculated fields and calculated items as the article highlights. When dragging the calculated items around in the pivot – usually 2 unique ones — it takes about 3-5 min to complete refreshing (because it seems to calculate everything, not just the fields I am filtered on). Adding a 3rd field can take 20 + mins. How can I speed this process up? Any more an excel crashes. Turning off option DEFER LAYOUT UPDATE in table options doesn’t work here….when I make the update with it off it still takes the same time. The source data has about 10,000 rows only, and if I remove the calculated items the speed problem goes away, so I am quite certain that is the root cause of the issue…..does anyone know of a way to speed this up? As it stands, the P&L is too slow to use regularly……..

    Thanks in advance!

  13. veniz pradz says:

    When I’m doing these things in excel it’s not working what was the problem can you explain about this briefly…….?

Leave a Reply