This is part 2 of 6 on Profit & Loss Reporting using Excel, 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
In this post we will learn how to setup the basic pivot table from that data.
First step is to create Pivot Table. Here is a video tutorial on making pivot tables.
My favorite for this type of Pivot it Classic Pivot Table layout. This is standard layout available in Excel 2003. You can change pivot table layout using following steps,
- Right click within PivotTable created in Excel 2007
- Click on PivotTable Options
- Select Display Tab
- Click Classic PivotTable layout (enables dragging of fields in the grid)
Here is a screen-cast showing how to switch pivot table layout.
Once you have got classic pivot layout, start adding data fields to it. Once you start dropping data field in pivot table it will start showing as different columns. However we need them in the rows rather than in columns. Check out screen cast on changing data from column labels to row labels
Here is a screen-cast showing how to change column label to row labels.
Data added by you will keep showing “Sum of” in addition to data field name. Like when you add sales field it will show as “Sum of Sales”.
You can change the “sum of x” to “x” by,
- Select all the row labels
- Press Ctrl+H – This will show Replace Dialogbox
- Type “Sum of” in find box without quotes
- Click on Replace all
PS: You cannot change “Sum of Sales” to “Sales”, you have to leave one space before, so we are changing it to ” Sales”.
Here is a screen-cast showing how to clear sum of from field labels.
Now we have a report which has major data available for preparing P&L Report. We need to add some calculated fields to it make it complete P&L report. We will do that in next post.
Download the Profit & Loss Pivot Table Excel File
Click here to download the file on todays example. Play with it. [here is a mirror of the file]
What Next?
In the next part of this series, learn how to add calculated fields to complete this P&L report.
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 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/
30 Responses to “Great News: Chandoo becomes MVP”
Congratulations! It's well deserved. 🙂
This is amazing. Hearty congratulations and a rocking new year ahead!!!
Congrats! I have learnt alot from your site... and the most important is learning how to achieve the most with the simplest concepts.
Thanks for the excel calendar. Is it possible to get a simple big fonted calendar printable on an A4 size paper without any distracive notes or visuals? BTW, I have already signed up for your newsletter. With warm regards and
Gratefully yours
50+ year old CHarish.
Hey Chandoo,
Great to hear that. Congratulations! The best new year gift, I would say. Keep it up, u've been doing extraordinary work for the excel users community.
Regards,
Pankaj Verma
Congrats dude... fantastic news!
congratulations! your site is great, this is well deserved
Rich
I recently found your site, I visit many. The tips that you provide are in the top 1% of all the sites I visit. Keep on Excelling.
Arnold
South Africa
Congratulations, Chandoo! That's a great way to start the year and make the PHD even better.
Congratulations, Chandoo.
Your site is one of most useful on the net. Happy new year and lot of ideas you will present for us.
Congrats.
Just read your name in an email from Abhishek. Well deserved.
Congratulations, and Happy New Year.
Greetings from Rio de Janeiro my friend! You trully deserve it!
Nive way to start 2009! Keep up the good work!
FC
That was quite forseeable , so you have now really got your PhD in excel.
Anyways Chandoo you have made excel a real Fun doo
I will like you to write some more on INDEX and MATCH function in near future.
@Hey Chandoo ! Congrates....
Ab to treat mangta hai !
Well deserved Chandoo!!
Congrat's!! Very well deserved 🙂
i always browsed mr.excel and used to see MVP writtne below names of people who used to solve queries in excel forum there......i just used to admire as to what they have special in them that they are MVP......
but now i got my answer...............u deserve it man..........
@All: thanks everyone 🙂
Congratulations Chandoo, nice job!
Chandoo,
A well deserved recognition and a good start to the New Year. Continue your good work.
Subbu
Many Congratulations.
You deserve a Ph.D. 🙂
congrats.....
Congrats dude. Rock on!
[...] charting community in 2007 and has been growing strongly ever since. In year 2009, I have received the MVP award from Microsoft. Just few days back I have become a dad [...]
Respected sir,
I am impressed!.... Good job done.. Keep it up...
Sir, How to be a MVP certified person. What level of knowledge is required for it? send me links if possible.
Please reply...
Regards,
Dipak Khalasi.
Dipak -
The first thing you need to cultivate is the ability to search the web effectively. You could start by Googling "Microsoft MVP".
[...] boy and girl which has been made hectic and incredibly fun ever since to their life.He has been awarded MVP status in 2009 by Microsoft(and renewed in 2010,2011 & 2012).His MVP profile is here.If you want to contact him direct then [...]
Congrats Chandoo!!
[…] Chandoo becomes MVP […]