Over the last month we have seen some 52, Excel Tips, Tricks, Cheats & Hacks presented by some of the best Excel practitioners on the net:
Excel Tips, Tricks, Cheats & Hacks – Microsoft MVP Edition
Excel Tips, Tricks, Cheats & Hacks – Chandoo.org Excel Ninja Edition
Excel Tips, Tricks, Cheats & Hacks – Notable Excel Sites Edition
Excel Tips, Tricks, Cheats & Hacks – Readers Prequil
In this final post I am presenting a compilation of Readers Contributions.
These have been compiled from comments on the above 4 posts and submissions sent directly to me.
I hope you enjoy the following Excel Tips, Tricks, Cheats & Hacks – Readers Edition
001. Toggle the Absolute/Relative $ Sign in Formulas using F4 – Desk Lamp
Instead of typing $AA$12 simply type AA12 then press F4.
Press F4 to Toggles through the sequence: AA12 -> $AA$12 -> AA$12 -> $AA12 -> AA12
You can read about Absolute vs Relative Cell References here
002. Current Region – Peter Carr
My favorite tip is the CurrentRegion of a range.
CurrentRegion is the contiguous range of cells starting from a cell, and moving out in all directions until an entire blank row or column is reached.
The current Region of the Yellow Cell above is the Red Outlined Area
Keyboard
From the keyboard you can do this by pressing Ctrl+* or Ctrl+A, which is a shortcut for GoTo, Special, Current Region.
VBA
In VBA you can use the Range.CurrentRegion property
If there is a block of data in B4:F10 with blank rows & columns around it
Dim myRange as Range
myRange = Range(“C8”).CurrentRegion
will set myRange to $B$4:$F$10
To identify the number of rows in a contiguous region.
e.g. intNumberOfRows = Range(“FirstCell”).CurrentRegion.Rows.Count
003. Select the Current Region using the QAT – Christine
In addition to the techniques described by Peter above, you can select the Current Region by adding an Icon to the Quick Access Toolbar.
Click on any cell and then click on the icon or use Alt+4
004. Find the Alt-Shortcut Key Number for the QAT – Hui
In the “Select the Current Region using the QAT” post above, Christine showed us how to use the Current Region Icon 
to quickly select the current Region. But how do we know it is the 4th Icon?
The Alt Number is Position dependent, in the example above the Current Region Icon is the 4th Icon from the Left in the QAT and so it is accessed by Alt+4
But by simply pressing the Alt key, Excel will show you the shortcut numbers for the QAT and all other Tabs
So we can see that the Select the Current Region icons is yes, No 4 and so Alt+4 is required to activate it
We can also see that the Record a Macro icon is number 08. To use that You use Alt+08 (Using the Number keys, not the numeric keypad)
Using Alt also shows you all the Tab shortcuts as well
005. Stay on the Current Cell after you press Enter – MF
Typically when entering data as you press the Enter key, Excel advances the current cell to the next cell as defined in the File, Options, Advanced, Editing Options menu
To stay on the current cell Simply press Ctrl+Enter instead of Enter
You can set your default move direction or disable Move Selection permanently by changing the option in the File, Options, Advanced, Editing Options menu:
006. Close a File Shortcut – Johnathan Cooper
Simply pressing Ctrl+W closes the current file
If the file has changed since the last save you are given the option to Save the file before it closes
007. Keyboard Shortcuts – Chirayu
Hide columns – CTRL + 0
Apply Filter (alternative) – SHIFT + CTRL + L
Clear Filter – ALT + D + F + S
Drag Down – CTRL + D
Drag Right – CTRL + R
Drag Up – ALT + E + I + U
Drag Left – ALT + E + I + L
Value Paste – ALT + E + S + V
Format Paste – ALT + E + S + T
You can find a comprehensive list of Keyboard Shortcuts at: Chandoo.org Keyboard Shortcuts
008. Use AutoCorrect to write formula – Wynn Hopkins
My favorite trick is using AutoCorrect to help write INDEX MATCH formulas..
Copy the following line into AutoCorrect and then use iii as the text to replace
=INDEX( DblClk_to_Select_Column_to_return, MATCH( DblClk_Single_Lookup_Cell, DblClk_Lookup_Column, 0),0)
This way whenever you need INDEX MATCH you just type iii and AutoCorrect kicks in and you are 3 double clicks away from a robust formula.
AutoCorrect is found in the File, Options, Proofing Menu
Contributor: Wyn Hopkins
009. Fill Blanks in a Data Table before use in a Pivot Table – RobD
When building pivot tables, it helps to have a full column of like values, so if you have a set up such as:
Where the data area has blank cell
Use this handy VBA
‘Change the MyCol value to match your value
Becomes…
Note: The code copies the text above the blank cell, and so the user must be careful that this is a valid assumption
010. Fill Blanks in a Data Table before use in a Pivot Table II – Jomili
Extending the technique shown above, Jomili supplied some VBA code that does the same as 007 above, except that it handles Multiple Columns at once as well as allowing Formulas to be converted to Values in the final result
So
becomes
by using this code:
011. QAT Copy/Paste Shortcut – Ian Watkins
By assigning the Copy, Paste Values and Paste Formulas Icons to positions 1, 2 & 3 of the Quick Access Toolbar
Instead of doing a big move of my hand from Ctrl+C to Alt+2, I can just move a finger from Alt+1 t copy
Click on the new cell and press Alt+2 or Alt+3 without moving my hand
Speeds things up quite a bit!
012. Customize Markers in a Chart – Chandeep
Customizing markers in a chart – http://www.goodly.co.in/customize-markers-in-a-chart/
013. Charting Hacks to work faster – Chandeep
Charting Hacks to work faster – http://www.goodly.co.in/5-charting-hacks-to-help-you-work-faster/
014. Seven Date formulas to make life easy – Chandeep
7 Date formulas to make life easy – http://www.goodly.co.in/date-formulas-in-excel/
015. Customised scrollbar using VBA – Chandeep
Customised scrollbar using VBA – http://www.goodly.co.in/customized-scroll-bar-in-excel/
016. Adding Direct Legends – Chandeep
Adding Direct Legends – http://www.goodly.co.in/how-to-add-direct-legends-to-the-chart/
017. Excel Ninja Menu – Krishna Khemraj
Select a cell or range then move till the 4-way cross appears.
Then Right-Click and drag the selection to another place in the worksheet then, like a ninja, a menu full of skills and throwing stars pops up allowing me to do all kinds of awesomeness.
When you click the fill box on a Date and Right Click and Drag it down, a lot of amazing Date options pop up.
018. Copy & Paste Filtered Data Only – Patricia
If you try to copy subtotaled data (and in earlier Excel versions filtered data), when you paste it all the data displays instead of just the summarized data.
To get around this, select your summarized data, click on Find and Select tab and then select Go to Special.
Click Visible cells Only and click Ok.
Now paste and you will see that only the summarized data has been copied.
You can also go CTRL+G and then click the Special icon at the bottom of the dialog box.
019. Clear Filters for the Current Column – Graham
With a table that is filtered, ensure the active cell is in the header of a filtered column and hit ALT + Down Arrow + C to clear the filter for the current column
020. Names Formula Tips – Pedro Paulo
You can bring up the Name Manager in Excel by pressing Ctrl+F3.
This lists the names used in your current workbook, and you can also define new names, edit existing ones or delete names from the Name Manager.
You can define several named ranges using data that’s arranged in neat tables. Excel creates named ranges from your selection and uses your data headings as the new names.
Make sure your data has headings (top row, left column, bottom row or right column) as these will turn into the names of your named ranges
Select the data including headings, press Ctrl+Shift+F3, in the dialog box select where your headings are (top row, left column, bottom row or right column) and click Ok.
Field Names which include spaces will be replaced with underscores
eg: Account Code will become the Account_Code named formula
021. Avoid Division by Zero – Ian Wilson
If a formula returns a number value, the Iferror() function can be used to isolate a returned value of zero.
You just need to utilize reciprocals:
1/(1/x) = x, however, if x = 0, then the function is an error.
My most common use of this feature is to return a blank instead of a 0.
=iferror(1/(1/sum(range)),””)
This could also be used to avoid division by 0 or replace 0s with a string.
022. Text to Columns Shortcut – Vishal Onkar
When working with lots of Text or CSV Files you invariably end up using the Text to Columns function repeatedly
This can be accessed by the ALT+D+E keyboard shortcut
023. Convert a Month in Words to a Month Number – Denys calvin
To convert a month in words (i.e., “August”) to its number (i.e., “8”), use, at least, the first three letters of the word in the following formula: =MONTH(“mmm”&1)
=Month(“March”&1) returns 3
=Month(“Mar”&1) returns 3
024. Trace Precedent/Dependents – Prashant99
Trace precedent cells Ctrl+[
Trace dependent cells F5+Enter or Ctrl+]
025. Resize Columns – Target
I routinely get sheets with data all smashed up which I hate and I’ve never been able to find a shortcut to do this.
To get around this I use the following VBA and assign a shortcut key (CTRL+Q)
It can be a nuisance if I’ve intentionally hidden columns, but the convenience far outweighs the inconvenience
026. Format Table Header Row – Ronnie
I use Tables many times a day and have a simple macro to give me a consistent Table format
The VBA Code:
027. Quickly Jump to Range – Efand
Type the range address directly in the Name Box and then press Enter to select it.
e.g: type A3:A6 will select its ranges without using any clicking and dragging
If you select a Range say B3:B6, then type a Name in the Name Box “From_Date“, Excel sets up a Named Formula referring to that range
If the Named Formula already exists, eg: From_Date, Typing From_Date into the box will take you to it.
You can also use the Drop Down next to the Name Box to select existing Named Ranges
028. Easily delete all Non-Formula cells – Martin
To easily delete all none-formula entries in a worksheet in one go:
Goto Home, Find & Select, Constants
This selects all cells that do not contain a formula.
Then just hit the delete button and you are done!
029. Reset all Cell Comments to the Same Style – Hui
To Reset all Cell Comments to the Same Style simply copy this code into a code module in your workbook
Edit the style parameters to suit your need
Run the code with F5
Closing
Many many thanks to the Microsoft Excel MVPs, Chandoo.org Ninja’s & My Favorite Excel Websites Authors for the 52 and You for the 29 Excel Tips, Tricks, Cheats & Hacks that have been showcased over these past 5 posts.
I hope you get to to revue all the tips and pass comments and appreciation back to the authors as appropriate.
I will re-run this series in May 2017 so keep a list of your new Excel Tips, Tricks or Hacks handy.
If you have any Excel Tips, Tricks or Hacks, Don’t be afraid to share them below in the comments:
15 Responses to “Highlight Employees by Performance Rating – Conditional Formatting Challenge”
While this might solve the question Shelly asked, there is another option that might be more useful - a pivot table could make a list of people who fall into the various categories, so, if you needed to simply see who got in the top bracket to give them a bonus, you would have that list
Simply sorting by the rankings would work too, but you would knock them out of alphabetical order.
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
The solution I chose makes use of the percentile formula.
The percentile formula returns the value representing the K-th percentile of a range of values. The range of values is the first criteria, and K is the second criteria in the formula.
I applied Conditional Formatting according to the formulas in the order below:
5% =$C6>=PERCENTILE($C$6:$C$33,0.95) Dark Blue
15% =$C6>=PERCENTILE($C$6:$C$33,0.85) Light Blue
65% =$C6>=PERCENTILE($C$6:$C$33,0.1) Green
10% =$C6>=PERCENTILE($C$6:$C$33,0.05) Light Red
5% =$C6<PERCENTILE($C$6:$C$33,0.05) Dark Red
The issue I noted with this approach is that Zambi was not highlighted in my solution as it is in the solution provided. Unless I am mistaken, and I very well may be, the 10th percentile for this data set is at 2.21, so Zambi would fall above the 10th percentile with a PR of 2.3.
The first step to this was figuring out the 'buckets'; what scores should fall into each range. In attempting to match the formatting of the spreadsheet, I determined the buckets below.
5% = 95% to 100%
10% = 90% up to but not including 95%
65% = 10% up to but not including 90%
10% = 5% up to but not including 10%
5% = under 5%
After that, it is a relatively simple matter to plug the necessary values into the conditional formatting formulas as shown above.
One final consideration is that while the buckets above match the color banding on the spreadsheet, I believe that the original request suggests a different color banding with 6 buckets shown below.
Top 5% = 95 to 100% Dark blue
Top 10% = 85 up to but not including 95% Light blue
Top 65% = 35 up to but not including 85% Green
Bottom 10% = 10% down to but not including 5% Light Red
Bottom 5% = 5% or under Dark Red
This leaves one final bucket of 10 to 35% (exclusive of both values) that is not highlighted and so would remain white.
Thank you Chandoo and Shelly for an interesting and useful exercise. This is certainly a valuable technique to have in my reporting bag of tricks.
Use of PERCENTILE is a smarter way of doing it. Below is my solution.
First 5 % = Apply conditional formatting (Dark Blue) as highlight ">=" =PERCENTILE(C:C,0.95)
Next 15% = Apply conditional formatting (Lighter Blue) as highlight between =PERCENTILE(C:C,0.95)-0.01 and =PERCENTILE(C:C,0.8)
Next 65% = Apply conditional formatting as highlight (Olive Green) between =PERCENTILE(C:C,0.8)-0.01 and =PERCENTILE(C:C,0.15)
Next 10% = Apply conditional formatting as highlight (Lighter Red) between =PERCENTILE(C:C,0.15)-0.01 and =PERCENTILE(C:C,0.05)
Bottom 5% = Apply conditional formatting (Red) as less than =PERCENTILE(C:C,0.05)
I agree, this is a challenge faced by HR managers every year and use of percentile formulae is the most popular solution which permits further processing like making bell curve, applying increments based on segmentation etc.
Hi Chandoo,
I came at the same solution as yours (not looking at yours first) but I have hard coded the conditions in the conditional formatting. For example:
=AND($C6>=$D$10,$C6<$D$9)
I have done the same thing 5 times for each condition. This makes the formatting independent of the order of specification. I think it will work better across versions of excel.
To copy the same thing in all sheets, Shelly can copy these formatted cells with format painter and apply it to the relevant cells in next sheet and so on! I know 700 sheets will be difficult but I dont know of any other way to apply conditional formating rules to the whole sheet.
First i have used percentile formula in the next column of "percentile Threshold" where E5, E6.. is input to colour code.
The idea behind doing this is to replicate the formula for any range and any threshold
=PERCENTILE($C$3:$C$30,1-E5)
=PERCENTILE($C$3:$C$30,1-E6)
=PERCENTILE($C$3:$C$30,1-E7)
=PERCENTILE($C$3:$C$30,1-E8)
=PERCENTILE($C$3:$C$30,1-E9)
Now i have given logic to different employee by applying "if Formula"
=+IF(J3>=$G$5,1,IF(J3>=$G$6,2,IF(J3>=$G$7,3,IF(J3>=$G$8,4,5))))
where 'J" referes to PR and "G" refers to percentile derived from above mentioned formula.
once again it is replicable (just change reference points)
Now comes the major part of Conditional Formatting, i have used "use a formula to determine which cells to be formatted"
Formula =$j=5, format "required colour" Applies to "$I$3:$J$30"
plus put tick on stop if true
This solves the query, important point that this is repeatable and can be done for n number of departments
Thanks !
I had done some reading on it and in Excel 2010 a new function has been introduced, percentile.exc. Attaching a video which also talks why the old percentile function shouldn't be used as it acts erroneous at times. Might be worth a watch Chandoo,
http://www.itechtalk.com/thread10579.html
@Deepa
Quit correct.
Where ever you use statistical spreadsheet functions and are using excel 2010 you should use the new versions of the functions as MS did a lot of work to speed up and fix errors in the old functions.
Warning: If you use the new Excel 2010 statistical functions in Named Formulas most of them will crash excel so do keep that in mind.
Hello Chandoo,
When i first read the challenge file, i thought, the color that need to be applied for a given rule, also need to be picked dynamically as given in rule set. But in the solution file, i found that color is hard Coded. So in case, someone has same data, but wants different colors, he/she needs to goto manage rules and change colors.
Let me know if my understanding is correct, and if yes, can we also make the color to be applied dynamic?
Thanks
Kishore
HI I ALSO USED THE PERCENTILE FUNCTION. HOWEVER, I WENT A STEP FURTHER AND USING THE SMALL() FUNCTION I SORTED THE DATA BY PERCENTILE SO THE COLOSCHEME WOULD BE GROUPED BASED ON THE VALUE. THIS WAY IT IS BETTER AND EASIER TO VIEW.
[...] recently posted a challenge to help a reader with a [...]
Hi, i have got doubt regarding to the percentages that has been put in chandoo's spreadsheet, i cant understadn how he put directly. can some one please explain how chandoo put the percetages straight way that i stated below..
5%
15%
60%
10%
5%
I have stumbled on this post as the solution has been already given so I have taken the liberty to record a video where I show the implementation of it as well as adding a filtering feature which I hope can prove to be useful.
Thank you
http://www.xlninja.com/2012/06/28/how-to-use-excel-to-highlight-employee-performance-rating/
[...] scriu nici macar un cuvant din urmatorul articol. Astazi mi-am citit mailul si hopa challenge de la Chandoo. Cum puteam sa refuz asa ceva si m-am apucat de citit, iar dupa 5 min i-am spus sotului ca pe asta [...]
Question for Chandoo:
I came to your site late but am totally loving these challenges 🙂
I guess it all boils down to how the bins are set up.
I agree with the PERCENTILE.INC function.
pls help me understand where I am wrong.
I have determined following the bins:
bottom 5% <=2.00 (F6:F33 <=PERCENTILE(range,.05))
lower 15% (5+10) <= 2.40 (F6:F33 <=PERCENTILE(range,.15))
lower 80% (5+10+65) <=3.46 (F6:F33 <=PERCENTILE(range,.80))
lower 95% (5+10+65+15) <=4.00 (F6:F33 =PERCENTILE(range,.95))
top 5% <=4.20 (F6:F33 <=PERCENTILE(range,1.00))
I find that only Tom is highest scorer and unique top 5% achiever.
I notice that Chandoo has included Christy and Daniel in top 5% achievers. How can there be 3 people in top 5% out of a population of 28 (5% of 28 = 1.4, i.e. only one person can achieve that status)?
I tried different ways but cannot get to that distribution.
Rest of the work is simply organizing the conditional formatting rules with Stop If True box checked.
Thanks for your insights