This is part 4 of 6 on Profit & Loss Reporting using Excel series, written by Yogesh
![Exploring Pivot Table Profit Loss Reports [Part 4 of 6]](https://chandoo.org/img/ea/profit-loss-report-pivot-options-4.png)
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
This is continuation of our earlier post Adding Calculated Fields to Pivot Table P&L.
Now that we have P&L report as PivotTable, in this post we will explore excel features to make various types of reports.
Making Reports by Geography / State /City or Month:
You can make Geography-wise , State-wise, City-wise or Monthly P&L with just few clicks. You just need to drop the required field to the column area of your PivotTable.
Want more? – You can prepare Geography wise P&L for the month of Dec.2009
Keep exploring the power of Filters and use them in various combinations to make report on required parameters.
Making Report of Top 5 Stores based on a Parameter
Okay I have got something more for you, How about finding top five stores based on Gross Margin % or Bottom 5 Stores based on Operating Profit %
Add “Store” Filed to Column area of Pivot Table. Now right click on store filed > Click on Filters > Choose Top 10
Choose your options From Drop Down Menu
Above shows you to calculate Top 5 based on Gross Margin%, go ahead and try to find out Bottom 5 based on Operating Profits%
Define KPIs and Make Reports Based on them
By adding some more calculated fields you can calculate various KPIs from the same set of Data.
City wise KPI is just example , you can calculate them on any other parameter as we have done with PivotTable P&Ls above. Use these formulas to calculate these KPIs
Sales per SFT = Sales / ‘Store Area’
Rent per SFT = Rent / ‘Store Area’
Utilities per SFT = Utilities / ‘Store Area’
Consumable % = Consumables / Sales
Then you can prepare a KPI report like this:
Download Excel File with these Pivot Tables
Click here to download Explore PivotTable P&L.xls with these PivotTables [mirror].
What Next?
In the next part of this series, learn how to prepare quarterly and half-yearly profit loss reports in excel.
Meanwhile, make sure you have read the first 3 parts of this series – Data sheet structure, Preparing P&L Pivot Table, Adding Calculated Fields.
Also check out the Excel Pivot Tables – Tutorial, Pivot Table Tricks, Grouping Dates in Pivot Reports articles to get more ideas.
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/
11 Responses to “Fix Incorrect Percentages with this Paste-Special Trick”
I've just taught yesterday to a colleague of mine how to convert amounts in local currency into another by pasting special the ROE.
great thing to know !!!
Chandoo - this is such a great trick and helps save time. If you don't use this shortcut, you have to take can create a formula where =(ref cell /100), copy that all the way down, covert it to a percentage and then copy/paste values to the original column. This does it all much faster. Nice job!
I was just asking peers yesterday if anyone know if an easy way to do this, I've been editing each cell and adding a % manually vs setting the cell to Percentage for months and just finally reached my wits end. What perfect timing! Thanks, great tip!
If it's just appearance you care about, another alternative is to use this custom number format:
0"%"
By adding the percent sign in quotes, it gets treated as text and won't do what you warned about here: "You can not just format the cells to % format either, excel shows 23 as 2300% then."
Dear Jon S. You are the reason I love the internet. 3 year old comments making my life easier.
Thank you.
Here is a quicker protocol.
Enter 10000% into the extra cell, copy this cell, select the range you need to convert to percentages, and use paste special > divide. Since the Paste > All option is selected, it not only divides by 10000% (i.e. 100), it also applies the % format to the cells being pasted on.
@Martin: That is another very good use of Divide / Multiply operations.
@Tony, @Jody: Thank you 🙂
@Jon S: Good one...
@Jon... now why didnt I think of that.. Excellent
Thank You so much. it is really helped me.
Big help...Thanks
Thanks. That really saved me a lot of time!
Is Show Formulas is turned on in the Formula Ribbon, it will stay in decimal form until that is turned off. Drove me batty for an hour until I just figured it out.