Excel Tricks with Tejraj

In this blog post we will learn to about OFFSET function in excel. OFFSET function will give us a reference of a cell , or a cell range based on provided number of rows and number of columns. Basically, OFFSET function will shift the original reference to up, down, left, or right based on provided number of rows and columns and gives us new reference. Where to find OFFSET function on Excel Screen: OFFSET function can be found under “Lookup & Reference” function category under “Formulas” tab and under “Function Library” group as shown in below image: Once we click on “Lookup & Reference” function category, we can see list of various Lookup & Reference functions available in excel. OFFSET function is highlighted in blue in below image. Once we click on OFFSET option as highlighted above, we will get the function argument dialog box as shown in below image: Syntax of OFFSET Function: The Syntax of OFFSET function is as below: =OFFSET(reference, rows, cols, [height], [width]) ...

In this blog post we will learn to about COLUMNS function in excel. COLUMNS function will return number of columns from selected cell range. Where to find COLUMNS function on Excel Screen: COLUMNS function can be found under “Lookup & Reference” function category under “Formulas” tab and under “Function Library” group as shown in below image: Once we click on “Lookup & Reference” function category, we can see list of various Lookup & Reference functions available in excel. COLUMNS function is highlighted in blue in below image. Once we click on COLUMNS option as highlighted above, we will get the function argument dialog box as shown in below image: Syntax of COLUMNS Function: The Syntax of COLUMNS function is as below: =COLUMNS(array) Arguments of COLUMNS Function: COLUMNS function takes only one argument. array : In this argument, we have to provide range of cells from which we want to find out number of columns. If we do not provide any reference here, then excel will au...

In this blog post we will learn to about COLUMN function in excel. COLUMN function will return column number of a selected cell. Where to find COLUMN function on Excel Screen: COLUMN function can be found under “Lookup & Reference” function category under “Formulas” tab and under “Function Library” group as shown in below image: Once we click on “Lookup & Reference” function category, we can see list of various Lookup & Reference functions available in excel. COLUMN function is highlighted in blue in below image. Once we click on COLUMN option as highlighted above, we will get the function argument dialog box as shown in below image: Syntax of COLUMN Function: The Syntax of COLUMN function is as below: =COLUMN() Arguments of COLUMN Function: COLUMN function takes only one argument. reference : In this argument, we have to provide cell reference of a cell for which we want to find out COLUMN number. If we do not provide any reference here, then excel will automatically provi...

In this blog post we will learn about CHAR function in excel. With the help of CHAR function we can convert numbers from 1 to 255 into their respective character codes. Where to find CHAR function on Excel Screen: CHAR 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. CHAR function is highlighted in red in below image. Once we click on CHAR option as highlighted above, we will get the function argument dialog box as shown in below image: Syntax of CHAR Function: The Syntax of CHAR function is as below: =CHAR(number) Arguments of CHAR Function: To use the CHAR function we have to provide only one arguments: number : This is the only one argument which have to provide. In this we have to provide number between 1 to 255. Example of CHAR Function: Let’s learn about using this CHAR function with the help of simple example. We entered random numbers ...

In this blog post we will learn to Freeze & Unfreeze Panes in excel. Whenever we are working with large volume of data we may need to scroll our screen top-down or left-right to refer the data which is located at top of our sheet or on the left side of our sheet. Scrolling the excel screen will consume lot of time. To tackle this situation excel has provided one excellent feature in which we can freeze the desired rows or columns. This feature is called as “Freeze Panes”. Freeze Pane command we can find in “View” tab under “Window” group as shown in below image. Once we click on “Freeze Panes” command, we can see 03 more options as below: 1. Freeze Panes 2. Freeze Top Row 3. Freeze First Column We will see all these 03 options one by one. Freeze Panes: This option will freeze the rows which are on top and which are on the left side of the selected cell. All the other cells can be scrolled top-down or left-right. This will help us to lock all the rows above and all the columns on th...

In this blog post we will see to create “Dynamic Progress Chart in Excel" from scratch.  This chart will help you to present your data in a very dynamic way.  For this we will take a simple example. Let’s say we want to show how many males and females (in percentage) are present in our country. We have used dummy data as shown in below image to create “Dynamic Progress Chart in Excel”. Let us say we have 47% of Males and remaining 53% are Females. First we will write 47% in front of Male. To calculate the % of females we will use formula (1-Cell address of % of Male). First we will see the steps to create “Dynamic Progress Chart in Excel” for Male and then by using these steps you have to create “Dynamic Progress Chart in Excel” for Female by your own. So let us start… 1. First we have to create helper column just beside the cell where we have inserted % for male. Use formula (1- Cell address of % of Male). 2. Go to Insert Tab. Select “Insert Pie or Doughnut Chart” command an...