If you want to highlight Unique / Duplicate Rows, follow following steps 1. Select the data range where you want to apply this formatting. 2. Home Tab > Conditional Formatting > New Rule 3. Select Format only unique or duplicate values. In Format all: box, you can select duplicate / unique. 4. Format appropriately and…
Category: Tips and Tricks
Tips & Tricks 35 – Highlight Cells which are different from first Column Cells in a Range (And for Row Cells also)
Let's say you are having data as given below and you want to highlight all the cells in columns after first column where cell values are different. Confused??? B1 is not different from A1 where C1 is different from A1, hence C1 will be highlighted. In row 2 both B2 and C2 will highlighted and…
Tips & Tricks 34 – Get Sheet Name through Formula
Before getting this, make sure that you file has been saved at least once as this formula is dependent upon the file path name which can be pulled out by CELL function only if file has been saved at least once. Use following formula – =REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"") Make sure that A1 is used in the formula….
Tips & Tricks 33 – Generate Week WORK Day Names Mon to Fri or Monday to Friday
1. Put "Mon" without quotes in a Cell. Let's assume this is in cell A1 here. If you want to generate full names of the day, put "Monday" without quotes. Note – The start day can be any WORK day. "Mon" is chosen for illustration purpose only. The start day will be whatever is put…
Tips & Tricks 32 – Generate Week Day Names Sun to Sat or Sunday to Saturday
1. Put "Sun" without quotes in a Cell. Let's assume this is in cell A1 here. If you want to generate full names of the day, put "Sunday" without quotes. Note – The start day can be any day. "Sun" is chosen for illustration purpose only. The start day will be whatever is put in…
Tips & Tricks 31 – Generate Month Names Jan to Dec in Text or January to December
1. Put "Jan" without quotes in a Cell. Let's assume this is in cell A1 here. If you want to generate full names of the month, put "January" without quotes. Note – Start month can be any other month also. "Jan" has been chosen for illustration purpose only. The output will be starting with the…
Tips & Tricks 30 – Generate an Array of a to z
I have already talked about generating a to z in a sequence in below post. Now, I want to generate an array {"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v", "w","x","y","z"} to use in a formula. To generate Small or Capital letters, do it while generating the sequence itself. Let's assume that a to z are generated in A1 to A26. Put…
Tips & Tricks 29 – Generate Alphabets A to Z (or a to z)
For Capital A to Z Put A in first cell and drag down. If you want to do it through formula – In Cell A1, put this formula and drag it till 26th row =CHAR(65+ROW()-1) or CHAR(64+ROW()) CHAR(65) is A. You can verify this by CODE("A") formula which will generate 65. If you are putting…
Tips & Tricks 28 – First Day of the Month for a Given Date
Suppose you have been given a date say 10/22/14 (MM/DD/YY) and you want to calculate the first day of the Current Month. Hence, you want to achieve a result of 10/1/2014 (MM/DD/YY). The formulas to be used – =DATE(YEAR(A1),MONTH(A1),1) =A1-DAY(A1)+1 =EOMONTH(A1,-1)+1
Tips & Tricks 27 – Find the Merged Cells
Sometimes, you would be required to find merged cells in Excel. One of the common cases is when you try to sort a column and all of a sudden of you see the pop-up regarding merged cell. You can do following to find merged cells – 1. Select the column. 2. CTRL+F and click Options….