This is part 5 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
This is continuation of our earlier post Exploring Profit Loss Pivot Reports.
We have learned how to change our P&L report on various data elements. We have seen how the P&L report can be changed with just few clicks.
In this post we will be learning some grouping tricks in PivotTables. We will cover grouping of dates, text fields and numeric fields. You will need to start with Monthly P&L report prepared in previous post.
Grouping Profit Loss Report based on Dates
- Right click on date field > Choose Group > Choose Quarters from dates grouping dialog box > click OK.
- Tutorial: Grouping Dates in Pivot Tables.
Once done your quarterly P&L is ready, you can still filter it on any other filed. Checkout screen cast for quarterly P&L prepared and filtered on City filed to make Quarterly P&L for Amritsar City.
Not only this, you can also drag grouped data to page area and Geography field to column area of PivotTable. Now you can filter it on Qtr1 to make Geography wise P&L for Qtr1.
[Click here to see how to do this]
This one is simple as it groups January to March period as Qtr1, April to June as Qtr2 and so on.
Grouping Dates based on Apr-Mar Financial Year
Most of the Indian companies follow April to March as Financial Year. They start with April to June as Qtr1 and finish with January to March as Qtr4. You will need different steps if you want make April to June as Qtr1 and January -March as Qtr4.
In our Data we have January 2009 to March 2009 period as Qtr 4 of 08-09 Financial Year. Grouping steps are as under
- Select January to March month columns > right click on selected columns > Choose Group.
- Rename Group1 as Q4.08-09
- Follow similar steps to Group April 2009 to June 2009 as Q1.09-10 and so on.
- [Click here to see how to do this]
- Drag the grouped field out of PivotTable in case you want to remove grouping.
The final report looks like this:
Many companies follow different Financial Year. In case your company follows May to April as Financial Year, you can select May June and July months and group them as Qtr1. You have the flexibility to select particular periods and group them. You can input name of the group as you want.
In similar way you can group 6 months to make half yearly Profit Loss Report in Excel.
Grouping Profit Loss Report Based on Text Fields
You can group data on text fields. In Geography wise P&L you can group South and East column to make P&L for South East.
You can select non consecutive columns :- Click on East column > Press Ctrl > Click on South column while you keep Ctrl key pressed. You will see that you have both the columns selected.
Select East and South Column > Right Click > Choose Group > Rename Group1 as South East.
Drag the grouped field out of PivotTable in case you want to remove grouping.
[Click here to see how this is done]
Grouping Profit Loss Report based on Numbers
You can also group data on numeric fields. We will make a PivotTable P&L grouping stores based on their size. Prepare P&L on store sizes by dropping store area field into column area of PivotTable.
Right Click on Store Area filed > Choose Group > Enter grouping parameters in Group dialog box > Click OK
You have the P&L for stores grouped based on their size.
Putting it all together – Creating a Custom Profit Loss Report Layout in Excel:
You can explore your PivotTable P&L in any combinations discussed in this post and our previous post.
So how about making South East Geography P&L for Q1 only of stores sized 4000 – 4999, like this?
It is just few clicks and your P&L will be ready , watch out screencast.
[this is a heavy screencast, so click thru if you want to watch it].
Dealing with “Cannot group that selection” error:
While grouping fields in PivotTable you may get a message saying “Cannot group that selection”. This happens due to blank date / number field or text in date / number field. You may have some blank rows in the data causing this problem. Some time you may have second copy of date field shown as Month2 or date2. This is duplicate field created in PivotTable which is already grouped. You will need to un-group this before grouping date filed again differently if required.
Download Excel file with all these example Pivot Reports:
Download Profit Loss Report Excel file and practice all these tricks on your own. [mirror download location]
What Next?
In the next part of this series, learn how to prepare budget vs. actual profit-loss reports.
Meanwhile, make sure you have read the first 4 parts of this series – Data sheet structure, Preparing P&L Pivot Table, Adding Calculated Fields and Exploring Profit Loss Report Pivot
Also check out the Excel Pivot Tables – Tutorial, Pivot Table Tricks, Grouping Dates in Pivot Reports articles to get more ideas.
Added by PHD:
- Say thanks if you like the idea and want to learn more.
- Please share your feedback and ideas for this series using comments. Yogesh and I will reply to your questions.
- Sign-up for PHD E-mail newsletter because you will get updates as new posts go online.
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/
27 Responses to “9 Box grid for talent mapping – HR for Excel – Template & Explanation”
Great stuff! I can understand how to add a slicer to the pivot table, but how do you implement the departmental selector on the 'Filter' formula scheme?
Just saw this on your Youtube channel, and it’s areat idea...!
An easy way to overcome the "ugliness" of pivot tables and get it to look nice (in the format of the Output sheet), would be to simply build a sheet with the nice map at the top, a pivot underneath it and a slicer next to formatted map and then reference each of the 9 cells in the formatted map to the “related” cell in the Pivot.
Keep up the good work!
/Claus
Thanks Claus. That is a great idea 🙂
Hi Chandoo,
This is great! Curious how to make additional columns operate the same as the Department column (ex. have a "manager column") that would allow you to sort a 9 box by manager, area, or team in addition to department?
Feel free to email me if needed! mfry01@minnetronixmedical.com
Happy New Year
Madison Fry
I am curious about the smae thing. I would like to populate the 9 box with other views as well by adding additional columns. IE., I would like to add location, region, etc. Thank you.
This is great, thank you!
How can i see the whole data set of all the teams in the output table. Need a formula that will pick up all the employees
Hello,
Love the template. Thank you. Question - the drop down to pick a department on the Output tab does not seem to work on the downloadable template. Am I doing something incorrectly?
Thank you!
Hi Heather... Thank you. I am using Excel 365 to make the calculations. If you are using an older version of Excel, then the drop-down filter won't work.
Hi
I was able to follow your 9 box grid and modified based my needs. However, you tutorial did not show how to you create the filter for the "Pick a department. Can you kindly share how to create that filter that updated the grid. Thank you.
I am working on this project but I am struggling with the data validation for the department. I copy the worksheets data entry and output as the managers want to see different tabs for each managers.
I updated the source reference for each tab but It does not update the grid based on the new source. The list was updated but it does not populate the grid based on the performance and potential listed.
In addition the hyperlink Update Data and View Talent Map no longer works. Can you please help me.
I keep getting this error message in the pivot table:
This formula is invalid or incomplete: 'The expression is not valid or appears to be incomplete. Please review and correct the expression.
The following syntax error occurred during parsing: Invalid token, Line 1, Offset 14, ‘.
Hi, I used your 9-box excel template with excel 365. First off, thank you so very much. It is incredibly helpful!! My only question is that the boxes aren't big enough for all of the employees (specifically the middle which we call 'Core Employee'). Is there a way to make the boxes larger? Even though it is in excel, I am not able to increase row height (like I normally do in a speadsheet). Any ideas? Thanks again, Jody
Hi Chandoo,
Thanks for the great content. Re. 9 box grid, pls advise how do I increase the size of the box to accommodate more names?
Hi Chandoo,
I figured it out. Excel 365 has the format row height on the ribbon. Thank you
Merci Chandoo pour le modèle proposé,
j'ai une question et un souhait est il possible de développer davantage ce modèle en insérant la photo de chaque employé.
Hi Chandoo!
Great tutorial and tool, thank you! Your tutorial didn't include how to create additional filters on the "Output" tab. Could you please share how you did it?
Can this be done exactly in google sheets?
Hi Chandoo,
Thanks for the video it was really helpful. Is there any way to multi select the dropdown to display multiple or all departments rather than just one at once?
Hello Prish
I have Microsoft 365 and I am struggling to make the boxes larger/unable to increase row height; any idea how you made this work? Specifically in the Output tab where the map is?
Many thanks
Hello Jody, I have Microsoft 365 and I am struggling to make the boxes larger/unable to increase row height; any idea how you made this work? Specifically in the Output tab where the map is? Many thanks
Is there a way to change the 9 box wording descriptions, i.e. Work Horses, to our own internal langauge?
You can edit the file. The descriptions are textboxes.
Hi Chandoo, this is awesome and has worked perfectly. Due to a big organisation the 9 box grid on the output file is too small. I tried adjusting using the row/width ribbon under the format ribbon however it doesn't seem to work. Is there an easier way to adjust this?
Thanks!
When I drag the formula, it doesn't work, and the order I use with the data changes. In the beginning, the order is it is " candidates," " potential," and " performance," but when it goes to another column, it is " Potential," Performance," and "Candidates."Can you help me? Thank you very much, sending love from vietnam
Hi- I am working on the 9 grid project and I am trying to expand the box since I have over 100 names on a few of the columns. How do I do that?
Hi, Thank you this is great stuff and really useful.
As well as department as demonstrated on your clip, how can I display all candidates on the grid at once?
Many thanks in advance