Excel has various functions, including functions to calculate inverse cosine of a given value, to multiply 2 matrices, to estimate the internal rate of return. But, most of us(well, just me then..) use just about 5-6 formulas to do our jobs. And IF formulas are a majority of these, so it doesn’t harm to learn a few interesting things you can do with just the excel IF functions.
1. Sum alternative rows / columns:
There comes a time when you are slapped with a sheet of data and need to sum every other row in it (dont ask me why, it happens, for eg. when you copy paste your credit card statement in excel), Ofcourse, we can always type the sum function with all those arguments, but we would rather chomp on that donut while excel does the dirty work for us. Thats why it helps to know that you can sum alternative rows / columns of data using sumif() formula [syntax and examples]. Sumif function sums a range of data that meets a specific criteria. In our case the criteria will be, “if the data element is in odd number rows”.
All you need to do is add an additional column at the end of the table and fill it with 1s and 0s. (just enter 1 and 0 in 2 rows, select both of them and drag till the end of the table). Now we can use this column to test our condition by writing the sumif function as =sumif(condition range, 1, sum range) [learn how you can highlight alternative rows / columns in an excel table]
2. Count how many times each item on list A is in List B vice versa
Often when you are working on data spread across multiple sheets, it helps to know how many times each item on one list is repeated in another list(s). This can be done easily using a good old countif function. Excel countif formula counts data that meets a specific criteria. [syntax and examples]
In the above example, I have used countif function to findout how many customers are there in each city (where customer data is in List B and city data is in List A). The formula looks like =countif(condition rage, condition), eg. countif($g$32:$g$47,"chicago") would tell us how many customers are in Chicago.
3. Quickly Summarize Data with countif / sumif:
Now that we have figured out how to use sumif and countif, you can use these two functions to create quick summary of your data.
For example, we can findout, average sales per customer per city dividing total sales data per each city (obtained using a sumif) with total customers in that city (obtained using a countif) as shown below. This can be a quick way to do pivot analysis of data without actually using excel pivot tables (very useful if you are allergic to excel pivot tables or not very happy using and constantly updating them)
4. Lookup second, third … nth occurrence of an item in a list in excel:
Often we work with data that has lot of duplicates, for eg. customer phone number data that has grown over a period of time with new numbers added at the bottom of the list. Getting second, third, fourth or nth occurrence from the list can be tricky, by using a combination of countif and vlookup we can lookup nth occurrence from a list. [VLOOKUP tutorial and examples]
First in our data list we will insert another column and place the formula =current-item&countif(range till that point, item), this will append the number of smiths till that point to the end of smith, thus first smith would become smith1, second smith would become smith2, so on…
Next, when looking up smith instead of using the initial column of customer data we will use our modified customer data to fire the lookup, for eg. vlookup("smith3", lookup range, 2, false) would tell us the 3rd phone number of smith. Note the last argument to the vlookup as “false”, since our list may not be alphabetically sorted, we have to use “false” to force excel to keep looking till it finds the 3rd smith row.
5. Reduce your nested if()s to one function
Do you know that you cannot nest if functions beyond 7 levels (in earlier versions of Excel)? Thankfully, most of us never go beyond 3 or 4 levels. But why write even that many levels when you can use choose() function, which is like a switch structure in programming. A typical switch function would look like, =switch(condition, outcome1, outcome2, outcome3...), for eg, =switch(3,"good","average","poor") would return “poor” when used. The only limitation to the switch() excel function is that it accepts numbers for testing the condition. But you can overcome this with some creativity, like I have shown in the below student letter grading example:
How did I convert the letter grade to a numeric in the choose(), well thats for you to figure out 🙂
What is your favorite IF formula trick?
Well, that is for you to tell me. What is your favorite Excel IF function tip / usage? Share it in the comments, let everyone know
Download IF Formula Examples and Play with them
I have prepared an excel sheet with all these if function examples, Click here to download it and experiment.
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