Excel Tricks with Tejraj

Whenever we want to limit the values which we are going to enter in cells, we can use drop down list. With this list, we can enter cell values which are present in the drop down list only. Excel will not accept any other values which are not included in the drop down list.  Following steps will give you cleat idea about creating your own drop down list: 1. Select a cell where we want to create a drop down list (in this example we have selected cell “E1”). 2. List down the values to be included in drop down list anywhere in the excel sheet (in this example we have listed these values in cell range “A1:A5”) 3. Select Data validation command which is available in Data Tab under Data Tools group. 4. Data Validation window will open as shown in below image. Now we have to select validation criteria in Settings tab. Under “Allow” Select “List” option. 5. Under “Source”, select the values which are to be included in drop down list (in this case ...

Many times we apply colors to cells in excel sheet which will help us to identify important cells just by looking at the data. But what if we use multiple colors to highlight data and later on want to select all the cells which are highlighted with specific color only???  YouTube Video: For example, we have listed some values in excel sheet and highlighted few cells with red color and few cells with yellow color. Now, we want to select all cells which are highlighted with only yellow color at a time. But how????  Don't worry... We will provide you a fantastic solution for this!! Follow below steps:  1. Select entire data. 2. Press Ctrl + F. "Find and Replace" window will appear. 3. Click on Options button. 4. "Find and Replace" window will expand with some additional options. Click on Format button. 5. "Find Format" window will appear. Click on Fill tab and select yellow color. You will s...

Conditional Formatting is an excellent functionality we have in excel which will help us to format our data based on conditions. In this post we will see how to highlight duplicate values with the help of Conditional Formatting.  YouTube Video: Conditional Formatting command is available in Home Tab under Style group. To highlight duplicate values follow below steps: 1. Select the data from which we want to highlight duplicate values. 2. Click on conditional formatting icon. 3. Select option “Highlight Cells Rules” from available drop down list. 4. Select “Duplicate values…” from available drop down list. 5. Now, one message box will appear which will show 02 drop-down options to select. 6. Select “Duplicate” in 1st drop-down list. 7. Select the formatting style of your own choice from the 2nd drop-down list. 8. Click “Ok” and you will see the duplicate values (in this case 3) are highlighted...