Excel Tricks with Tejraj

Previously we have learned to create “ Drop Down List ” in excel. In this blog post, we will go little advanced and learn to create “Dependent Drop Down” in Excel. It means, we have to create 02 Drop Down Lists in excel and out of these two, the second Drop Down list will change based on the selection made in the first Drop Down List . For this, we have listed few countries from three different continents. These three continents are “Asia”, “Africa” and “Europe”. So our first drop down list will be for selecting Continents and the second drop down list will be for selecting Counties from respective continent. 1st Drop Down List : It is for selecting Continent 2nd Drop Down List : It is for selecting Countries from respective Continent. We have to use “Define Name” functionality in excel for this entire process. This “Define Name” command we can find in “Formulas” tab under “Defined Names” group. Select all the countries listed under continent ‘Asia’ and click on command “Define Nam...

In our last blog post, we have learned to remove duplicates entries in excel . In this blog post, we will go little advanced to remove the duplicate entries from multiple columns. Removing Duplicates Entries from 02 Columns: We will learn to remove duplicate entries from the data as shown in below image. The entries which are duplicate in both the “Fruits” column and “Price/Kg” column are highlighted with different colors. To remove duplicate entries in these 02 columns, follow the steps provided below: 1. Select the data from which we want to remove the duplicates. 2. Go to “Data” tab and under “Data Tools” we can find command named as “Remove Duplicates” as highlighted in below image. 3. Once we click on “Remove Duplicate” command, one dialog box with same name “Remove Duplicate” will appear on our excel screen. 4. If we check the checkbox for “My Data has headers” then the header row from our selection will be excluded automatically and under “Columns” section we will see the name o...

Many times while working in excel we encounter the data which contains many duplicate entries. It is very frustrating to deal with such duplicate entries. To tackle this situation we will learn to how to remove duplicate entries in excel. To explain this we have taken very simple example. We have listed the names of some fruits as shown in below image. As we can see this data contains many duplicate entries. To remove these duplicates entries, follow the steps provided below: 1. Select the data from which we want to remove the duplicate entries. 2. Go to “Data” tab and under “Data Tools” we can find command named as “Remove Duplicates” as highlighted in below image. 3. Once we click on “Remove Duplicate” command, one dialog box with same name “Remove Duplicates” will appear on our excel screen . As we have selected data from only single column, we can see only one column named as “Fruits” under “Columns” field. After this Click on “OK” button. 4. Once we click on “OK” button, we can se...

In this blog post we will see how to delete Custom List in Excel. We can delete the Custom List in Excel by following easy steps. 1. Go to File Menu. 2. Click on “Options”. 3. Once we click on the “Options” following window will appear on the screen. 4. Click on the “Advanced” Option as shown in following image. 5. Scroll down and Go to “General” option & click on the “Edit Customs Lists…”. 6. Once we click on the “Edit Custom Lists…” following window will appear on the screen named as “Custom List”. 7. But Custom Lists shown in above image are “Built in Lists” so we cannot delete this lists. And when we try to delete this “Built in Lists” ‘Delete Option’ will be disabled as shown in below image. 8. So, Here we have entered Team Member 1, Team Member 2, Team Member 3, Team Member 4 and Team Member 5 in List entries as shown in above image. ( Click here to learn about Creating Custom List in Excel ). 9. Now, to delete custom list highlighted in above image we have to select that sa...