Excel Tricks with Tejraj

In this blog post we will learn about DATE function in excel. When we have Year, Month and Day as a three separate values then with the help of DATE function we can convert them into single value which will represent a particular date. Where to find DATE function on Excel Screen: DATE function can be found under “Date & Time Function” category under “Formulas” tab and under “Function Library” group as shown in below image: Once we click on “Date & Time” category, we can see list of various Date & Time Functions available in excel. DATE function is highlighted in blue in below image. Once we click on DATE option as highlighted above, we will get the function argument dialog box as shown in below image: Syntax of DATE Function: The Syntax of DATE function is as below: =DATE(year, month, day) Arguments of DATE Function: To use the DATE function, we have to provide three arguments: year : In this argument we have to enter a number from 1900 or 1904 (depending upon workbook’s da...

In this blog post we will learn about CODE function in excel. With the help of CODE function we can get numeric code for the character. In case of text string, it will return numeric code for 1st character in text string. Where to find CODE function on Excel Screen: CODE function can be found under “Text Function” category under “Formula Tab” as shown in below image: Once we click on “Text” category, we can see list of various Text Functions available in excel. CODE function is highlighted in red in below image. Once we click on CODE option as highlighted above, we will get the function argument dialog box as shown in below image: Syntax of CODE Function: The Syntax of CODE function is as below: =CODE(text) Arguments of CODE Function: To use the CODE function we have to provide only one arguments: text : This is the only one argument which have to provide. In this we have to provide a text for which you want code. Example of CODE Function: Let’s learn about using this CODE function wit...

Many times we receive data in excel which is not properly formatted. It may contain some unwanted spaces which will waste our extra time to remove them. In this blog post we will learn to remove these unwanted spaces from text string in excel. For this we will use TRIM function which is readily available in excel. Where to find TRIM function on Excel Screen: TRIM function can be found under “Text Function” category under “Formula Tab” as shown in below image: Once we click on “Text” category, we can see list of various Text Functions available in excel. TRIM function is highlighted in red in below image. Once we click on TRIM option as highlighted above, we will get the function argument dialog box as shown in below image: Syntax of TRIM Function: The Syntax of TRIM function is as below: =TRIM(text) Arguments of TRIM Function: To use the TRIM function we have to use only one arguments: text : This is the only one and mandatory argument in which we can provide text string from which we ...

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