Excel Tricks with Tejraj

In this blog post we will learn to apply filter on “ Font Colors ” in excel. For this we have a data of some electronic products sold in East, West, South and North regions from 1st Aug 2019 to 1st Apr 2021 with us and we have applied font colors to this data. We have applied two font colors “Red” and “Blue” to some values under column “No of Items Sold”. We will now see how we can apply filters on Font Colors in above data: 1. Select the entire data on which we wish to apply filter on font colors. 2. Go to “Data” tab and click on the “Filter” command (Data tab and Filter command are highlighted with red in below image). 3. Once we click on the “Filter” command, we can see that drop down button is now applied to all the cells in header row as shown in red highlighted area. 4. Click on the drop down button for cell “No of Items Sold” from header row. This will show us further options to apply the filter for values in this entire column. 5. Now, select the option “Filter by Color” as h...

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...

Pivot Table in excel is a very powerful feature which helps us to to organize and summarize the data in very effective manner, that too without using any complex excel formula or functions. In this blog post we will see how to create Pivot Table in Excel. Below is the dummy sales data for last three years (2018, 2019 & 2020). In this sales data employees A, B, C & D sales various electronic components like Resistor, Capacitor, Inductor, Diodes & LED in all South, East, North & West region of India. Creating Pivot Table: Now we will see how to create Pivot Table in Excel. Pivot Table feature is available in Insert Tab. Pivot Table command is available in "Insert Tab" under "Tables Group" as shown in below image. Once you click on Pivot Table command, “Create Pivot Table” dialog box will appear on screen. This is like as shown in below image. This “Create Pivot Table” dialog box is very important to create Pivot Table. There are two important options a...