Excel Tricks with Tejraj

Separate Date & Time in Excel

2023-10-01T19:49:00.001+05:30

Many times we are working with date and time values while working in excel. Many time we get data in which date and time is mentioned in a single cell (as shown in below image).

But what if we want to separate the date as well time from this value?

Well, we will learn to separate the date and time in this blog post whenever we will face similar situation.

Basic Information:

Before moving to this separation, first of all we need to understand some basic concepts related to date and time values which are available in excel.

Dates are nothing but the integer values stored in excel. Whereas Times are the decimal values stored in excel.

For example:

If we enter integer value 1 and change the cell format to dates, then it will become 01 January 1900.

If we enter the decimal value 0.59 and convert the cell format to time, then it will become 02:09 PM.

Now, we will use these basic concepts to separate date and time from combined value. For this we will use INT function in excel. We have already learned about this INT function in our previous blog post. Click here to learn more about INT function in Excel.

Steps to Separate Date & Time

1. Select the cell in which we want to extract the date. In this case we have selected cell B2.

2. We have to use INT function in selected cell (here in B2) as shown in below image. Also choose the cell formatting for this cell as any date format.

3. Once we press “Enter” button on keyboard then we can see the date is separated from combined value.

4. Similarly, select the cell in which we want to extract time. In this case we have selected cell C2. Also choose the cell formatting for this cell as any time format.

5. Now, in this cell we have to just subtract the value of B2 from A2 as shown in below image.

6. Once we press “Enter” button on keyboard then we can see the time is separated from combined value.

In this way, we can separate date and time from the combined values. Hope this information is useful for you and you can implement this in your day to day working.

Inserting Image into Shapes in Excel

2023-06-25T21:30:00.005+05:30

We have already learned about Inserting Shapes as well as Inserting Images in excel. Now, in this blog post we will learn about inserting images in shapes in excel.

We will learn to insert an image (which is logo of our website “Excel Tricks with Tejraj”) into various shapes. First we will insert the shape in excel and then we will insert the logo into inserted shape.

Insert Image in Oval Shape:

First, click on “Insert” tab and then click on “Shapes” command.

Once we click on “Shapes” command, we can see various shapes available in excel.

Out of these, you can select any shape as per your choice. In this case, we want to select the “Oval” shape. If you are not aware about the names of these shapes, then you can just place the mouse cursor on any shape which will automatically show the name of that shape.

Once we click on the “Oval” shape, the shape of mouse cursor will change from doctor symbol to plus symbol.

As soon as the mouse cursor symbol changes to plus symbol, we can draw the “Oval” shape on excel which will look like as shown in below image.

Now we want to insert the image (in this case we will insert logo) in this “Oval” Shape. For this click on the inserted “Oval” shape. This will enable “Format” tab in the Ribbon.

Click on “Shape Fill” command which will show us few more options. Out of these, we have to click on “Picture” option as highlighted in blue in below image.

Once we click on “Picture” option, we can see that one dialog box named as “Insert Picture” will appear on the screen. This dialog box will give us various options to select the image or picture. We can select the image or picture “from a file”, “Bing Image Search” or “OneDrive – Personal”.

In our case, we have stored the image of logo in our local drive. So we will select option as “From a file”. This will enable another dialog box in which we can select the exact location of the image and then we can click on image which we want to insert in shape. Once we click on the image, we have to click on “Insert” button.

Once we click on “Insert” button, we can see that the selected image is now inserted in “Oval” shape as shown in below image.

In this way, we can select any image into desired shapes. You can try inserting various images into other shapes available in excel.

SUBTOTAL Function in Excel

2023-06-11T16:39:00.000+05:30

In this blog post we will learn to about SUBTOTAL function in excel. SUBTOTAL function will return subtotal from the list or database.

Where to find SUBTOTAL function on Excel Screen:

SUBTOTAL function can be found under “Math & Trig” function category under “Formulas” tab and under “Function Library” group as shown in below image:

Once we click on “Math & Trig” function category, we can see list of various Math & Trig functions available in excel. SUBTOTAL function is highlighted in blue in below image.

Once we click on SUBTOTAL option as highlighted above, we will get the function argument dialog box as shown in below image.

Syntax of SUBTOTAL Function:

The Syntax of SUBTOTAL function is as below:

=SUBTOTAL(function_num,ref1, ref2, …)

Arguments of SUBTOTAL Function:

SUBTOTAL function takes arguments as listed below:

function_num: In this argument we have to enter 1 to 11. Each number represents different function as

listed below:

1: AVERAGE

2: COUNT

3: COUNTA

4: MAX

5: MIN

6: PRODUCT

7: STDEV.S

8: STDEV.P

9: SUM

10: VAR.S

11: VAR.P

ref1: This is reference or cell range for which you want to calculate SUBTOTAL.

We can add 1 to 254 ranges or references under SUBTOTAL.

Example of SUBTOTAL Function:

Let’s take an example to learn how we can use SUBTOTAL function. We have created dummy data:

Brown Highlighted Cells: In these cells we have entered few random numbers. This numbers are divided into 02 reference lists. Reference 1 and Reference 2.

Blue Highlighted Cells: In these cells we will calculate the output of SUBTOTAL function based on reference 1 and reference 2.

We will calculate the values for all the 11 functions using SUBTOTAL in these blue highlighted cells:

For this select a cell where we want to apply SUBTOTAL function. In this case we have selected cell E2.

Now apply SUBTOTAL function in cell “E2” to calculate the “Average” for numbers listed in reference 1 and reference 2 as shown in below image.

Once we press enter button on our keyboard then we can see the output of SUBTOTAL function.

Now apply SUBTOTAL function in cell “E3” to calculate the “count” of numbers listed in reference 1 and reference 2 as shown in below image.

Once we press enter button on our keyboard then we can see the output of SUBTOTAL function.

In this way we can use all the remaining “function numbers” and calculate the output of SUBTOTAL function.

SUMIFS Function in Excel

2023-05-28T12:40:00.002+05:30

In this blog post we will learn to about SUMIFS function in excel.

In our previous blog post we learned about SUMIF function in which we learned to calculate the SUM based on certain criteria or condition. In this blog post we will learn to calculate the sum based on set of criteria’s or conditions, with the help of SUMIFS function.

Where to find SUMIFS function on Excel Screen:

SUMIFS function can be found under “Math & Trig” function category under “Formulas” tab and under “Function Library” group as shown in below image:

Once we click on “Math & Trig” function category, we can see list of various Math & Trig functions available in excel. SUMIFS function is highlighted in blue in below image.

Once we click on SUMIFS option as highlighted above, we will get the function argument dialog box as shown in below image.

Syntax of SUMIFS Function:

The Syntax of SUMIFS function is as below:

=SUMIFS(sum_range, cirteria_range1, cirteria1, …)

Arguments of SUMIFS Function:

SUMIFS function takes three arguments as listed below:

sum_range: This is cell range from which we want to calculate sum.

criteria_range1: This is cell range which you want to evaluate based on provided condition or criteria.

criteria1: This is criteria or condition in the form of number, expression, or text which that will define which cells to be added.

Example of SUMIFS Function:

Let’s take an example to learn how we can use SUMIFS function to calculate SUM based on certain criteria or condition. We have created dummy data:

Brown Highlighted Cells: In these cells we have listed names of few employees and sales figures of these employees in year 2021 & 2022 based on regions: East, West, North & South.

Blue Highlighted Cells: In these cells we will calculate the SUM based on selected region & selected year with the help of SUMIFS function.

Under the Column Region, we have created drop down list to select the desired region.

Under Year region, we have created drop down list to select the desired year

For this select a cell where we want to apply SUMIFS function. In this case we have selected cell H3.

Select the region from available drop down list (in this case we have selected “South”).

Now, select year from available drop down list (in this case we have selected year “2021”).

Now apply SUMIFS function in cell “H3” so that we can calculate the total sales done in “South” region and in year “2021” as shown in below image.

Once we press enter button on our keyboard then we can see the output of SUMIFS function.

In this way we can use SUMIFS function in excel.

SUMIF Function in Excel

2023-05-13T16:09:00.000+05:30

In this blog post we will learn to about SUMIF function in excel.

Whenever we want to calculate the SUM based on certain criteria or condition, we can use the SUMIF function.

Where to find SUMIF function on Excel Screen:

SUMIF function can be found under “Math & Trig” function category under “Formulas” tab and under “Function Library” group as shown in below image:

Once we click on “Math & Trig” function category, we can see list of various Math & Trig functions available in excel. SUMIF function is highlighted with blue color in below image.

Once we click on SUMIF option as highlighted above, we will get the function argument dialog box as shown in below image.

Syntax of SUMIF Function:

The Syntax of SUMIF function is as below:

=SUMIF(range, criteria, [sum_range])

Arguments of SUMIF Function:

SUMIF function takes three arguments as listed below:

range: It is cell range which you want to evaluate or in other words, it is cell range from which we want to apply condition or certain criteria to calculate sum.

criteria: This is criteria or condition based on which we want to calculate the sum. This can be in the form of cell reference, expression or text.

sum_range: This is actual cell range from which we want to calculate the sum.

Example of SUMIF Function:

Let’s take an example to learn how we can use SUMIF function to calculate SUM based on certain criteria or condition. We have created dummy data:

Blue Highlighted Cells: In these cells we have listed names of few employees and sales figures of these employees based on regions: East, West, North & South.

Brown Highlighted Cells: In these cells we will calculate the sum based on each region with the help of SUMIF function.

For this select a cell where we want to apply SUMIF function. In this case we have selected cell F3.

Apply SUMIF function in selected cell F3 as shown in below image.

Once we press enter button on our keyboard then we can see the output of SUMIF function.

To find the output in remaining cells just copy and paste the SUMIF function in remaining cells. We see the output as shown in below image.

In this way we can use SUMIF function in excel.

PI Function in Excel

2023-04-14T21:12:00.000+05:30

In this blog post we will learn to about PI function in excel.

PI function will return value of Pi which is mathematical constant which has value of 3.14.

The PI function in excel return the value of Pi accurate to 15 digits.

Importance of PI Function?

Pi can be used to solve many mathematical problems. So if you are thinking to solve some mathematical problems for example to “calculate area of circle” or to “calculate the circumference of circle” (these are just few examples) then you can use the PI function effectively.

Where to find PI function on Excel Screen:

PI function can be found under “Math & Trig” function category under “Formulas” tab and under “Function Library” group as shown in below image:

Once we click on “Math & Trig” function category, we can see list of various Math & Trig functions available in excel. PI function is highlighted in blue in below image.

Once we click on PI option as highlighted above, we will get the function argument dialog box as shown in below image.

Syntax of PI Function:

The Syntax of PI function is as below:

=PI()

Arguments of PI Function:

PI function does not take any argument.

Example of PI Function:

Let’s take an example to calculate circumference of circle. In mathematics formula to calculate circumference of circle is 2πr, where r is radius of circle and π is a constant. We will now calculate the circumference of circle in excel with the help of PI function.

For this, we have prepared raw data in which we have entered radius of circles in centimeters in column A. In column B, we will calculate the circumference of a circle with the help of radius mentioned in column A and PI function available in excel.

Now, in column B enter the formula as shown in below image.

Now hit enter button on keyboard which will provide us final output which will be circumference of a circle with radius mentioned in column A.

To calculate the circumference of circle in remaining cells, we have to just copy the formula from cell B2 and paste it in the remaining cells. The output will be as per below screenshot.

In this way, we can use PI function in excel.

PRODUCT Function in Excel

2023-04-06T21:19:00.001+05:30

In this blog post we will learn about PRODUCT function in excel.

PRODUCT function can be used when we want to multiply multiple values provided in the arguments.

Importance of PRODUCT Function?

In mathematics, PRODUCT of a number is calculated by multiplying all the input numbers. So in case we want to multiply the numbers then we can use the PRODUCT function. Usage of this function is more effective whenever we want to multiply huge numbers together.

Where to find PRODUCT function on Excel Screen:

PRODUCT function can be found under “Math & Trig” function category under “Formulas” tab and under “Function Library” group as shown in below image:

Once we click on “Math & Trig” function category, we can see list of various Math & Trig functions available in excel. PRODUCT function is highlighted in blue in below image.

Once we click on PRODUCT option as highlighted above, we will get the function argument dialog box as shown in below image.

Syntax of PRODUCT Function:

The Syntax of PRODUCT function is as below:

=PRODUCT(number)

Arguments of PRODUCT Function:

PRODUCT function can take maximum 256 numbers.

number 1: In this argument, we must enter any number, logical value, or text representation of numbers that you want to multiply.

[number 2]….. [number 255]: These are optional arguments. we must enter any number, logical value, or text representation of numbers that you want to multiply in these arguments.

Example of PRODUCT Function:

We have listed few random numbers in column A. We will multiply these numbers and find out the result with the help of PRODUCT function.

For this select a cell where we want to apply PRODUCT function. In this case we have selected cell C4.

Apply PRODUCT function in selected cell C4 as shown in below image.

Once we press enter button on our keyboard then we can see the output of PRODUCT function.

In this way we can use the PRODUCT Function in excel.

RANDBETWEEN Function in Excel

2023-03-04T09:00:00.008+05:30

In this blog post we will learn to about RANDBETWEEN function in excel.

RANDBETWEEN Function will return random numbers between the two numbers provided by the user.

Where to find RANDBETWEEN function on Excel Screen:

RANDBETWEEN function can be found under “Math & Trig” function category under “Formulas” tab and under “Function Library” group as shown in below image:

Once we click on “Math & Trig” function category, we can see list of various Math & Trig functions available in excel. RANDBETWEEN function is highlighted in blue in below image.

Once we click on RANDBETWEEN option as highlighted above, we will get the function argument dialog box as shown in below image.

Syntax of RANDBETWEEN Function:

The Syntax of RANDBETWEEN function is as below:

=RANDBETWEEN(bottom, top)

Arguments of RANDBETWEEN Function:

RANDBETWEEN function takes two arguments. One is smallest integer value and the other is largest integer value. RANDBETWEEN function will return the random value between these two values.

bottom: It is the smallest integer value

top: It is the largest integer value

Where to Use RANDBETWEEN Function:

RANDBETWEEN function can be used to create a dummy data where we want to use dummy numbers

Let’s say we want to two-digit random numbers in highlighted cells.

Select any cell from highlighted cells, in this case we have selected cell A2.

Enter RANDBETWEEN function along with required arguments in selected cell A2. For this we must provide the two arguments

Bottom: 10 (as 10 is the smallest two-digit number)

Top: 99 (as 99 is the largest two-digit number)

Once we press enter button, we can see the output of RANDBETWEEN function as shown in in below screenshot.

In order to enter the random numbers in remaining cells, we have to just copy the formula entered in cell A2 and paste in remaining cells. The output we will get is as shown in below image.

Important Points about RANDBETWEEN Function:

1. Output of RANDBETWEEN function changes once excel sheet gets recalculated (either by automatic or manual recalculation).

2. Output of RANDBETWEEN function will change while we open or close the worksheet.

3. To change the output of RANDBETWEEN function we just need to press “F9” button on our Keyboard.

RAND Function in Excel

2023-02-25T09:00:00.006+05:30

In this blog post we will learn to about RAND function in excel.

RAND Function will return random numbers which are greater than or equal to zero and less than one.

Where to find RAND function on Excel Screen:

RAND function can be found under “Math & Trig” function category under “Formulas” tab and under “Function Library” group as shown in below image:

Once we click on “Math & Trig” function category, we can see list of various Math & Trig functions available in excel. RAND function is highlighted in blue in below image.

Once we click on RAND option as highlighted above, we will get the function argument dialog box as shown in below image.

Syntax of RAND Function:

The Syntax of RAND function is as below:

=RAND()

Arguments of RAND Function:

RAND function does not take any argument.

Important Points about RAND Function:

1. RAND function does not take any argument

2. Output of RAND function changes once excel sheet gets recalculated (either by automatic or manual recalculation)

3. Output of RAND function will change while we open or close the worksheet.

4. To change the output of RAND function we just need to press “F9” button on our Keyboard.

Where to Use RAND Function:

1. RAND function can be used to create a dummy data where we want to use numbers between 0 to 1.

2. RAND function can also be used to create random time just by converting them into TIME format.

INT Function in Excel

2023-02-18T09:00:00.003+05:30

In this blog post we will learn about INT function in excel.

INT Function will round a number down to nearest integer.

Where to find INT function on Excel Screen:

INT function can be found under “Math & Trig” function category under “Formulas” tab and under “Function Library” group as shown in below image:

Once we click on “Math & Trig” function category, we can see list of various Math & Trig functions available in excel. INT function is highlighted in blue in below image.

Once we click on INT option as highlighted above, we will get the function argument dialog box as shown in below image.

Syntax of INT Function:

The Syntax of INT function is as below:

=INT(number)

Arguments of INT Function:

INT function takes only one argument which is mandatory.

number: This is the real number which you want to round down to an integer

Example of INT Function:

We have listed few random numbers in column A. With the help of INT function, we will round down these number to an integer.

For this select a cell where we want to apply INT function. In this case we have selected cell B2.

Apply INT function in selected cell B2 as shown in below image.

Once we press enter button on our keyboard then we can see the output of INT function.

To find the output in remaining cells just copy and paste the INT function in remaining cells. We see the output as shown in below image.

In this way we can use INT function in excel.

Important Note:

If we apply INT function to negative numbers, the output we will get is higher negative number. This is reverse case if we compare with the positive numbers. The output of INT function to positive number will return the lower positive number.

POWER Function in Excel

2023-02-11T09:00:00.001+05:30

In this blog post we will learn to about POWER function in excel.

Whenever we want to return value of a number raised to its power then we can use POWER function which is readily available in excel.

What is POWER of a number?

In mathematics, POWER of a number means how many times a number we want to use in multiplication.

For example, 5^2 (5 to the power of 2) means 5 is used twice in multiplication (5x5 = 25).

2^3 (2 to the power of 3) means 2 is used three times in multiplication (2x2x2 = 8).

Where to find POWER function on Excel Screen:

POWER function can be found under “Math & Trig” function category under “Formulas” tab and under “Function Library” group as shown in below image:

Once we click on “Math & Trig” function category, we can see list of various Math & Trig functions available in excel. POWER function is highlighted in blue in below image.

Once we click on POWER option as highlighted above, we will get the function argument dialog box as shown in below image.

Syntax of POWER Function:

The Syntax of POWER function is as below:

=POWER(number, power)

Arguments of POWER Function:

POWER function takes two arguments, and both are mandatory.

number: This is base number, and it can be any real number. (i.e., which number we want to use in multiplication).

power: This is exponent to which base number is raised (i.e., how many times we want to use above number in multiplication).

Example of POWER Function:

We have listed few random numbers in column A and column B. The numbers from column A are base numbers and numbers from column B are exponents. With the help of these two columns, we will learn to use POWER function.

For this select a cell where we want to apply POWER function. In this case we have selected cell C2.

Apply POWER function in selected cell C2 as shown in below image.

Once we press enter button on our keyboard then we can see the output of POWER function.

To find the output in remaining cells just copy and paste the POWER function in remaining cells. We see the output as shown in below image.

In this way we can use POWER function in excel.

FACT Function in Excel

2023-02-04T09:00:00.003+05:30

In this blog post we will learn to about FACT function in excel.

Whenever we want to return factorial of a number, we can use FACT function which is readily available in excel.

What is Factorial of number?

In mathematics, factorial of a number is calculated by multiplying all the numbers which are less than the provided number. For example, if we want to calculate factorial of 5 then it will be 5x4x3x2x1 = 120. i.e., factorial of 5 is 120.

Where to find FACT function on Excel Screen:

FACT function can be found under “Math & Trig” function category under “Formulas” tab and under “Function Library” group as shown in below image:

Once we click on “Math & Trig” function category, we can see list of various Math & Trig functions available in excel. FACT function is highlighted in blue in below image.

Once we click on FACT option as highlighted above, we will get the function argument dialog box as shown in below image.

Syntax of FACT Function:

The Syntax of FACT function is as below:

=FACT(number)

Arguments of FACT Function:

FACT function takes only one argument.

number: In this argument, we must enter “non-negative” number for which we want to find out factorial.

Example of FACT Function:

We have listed few random numbers in column A. We will find out factorial for respective numbers in column B with the help of FACT function.

For this select a cell where we want to apply FACT function. In this case we have selected cell B2.

Apply FACT function in selected cell B2 as shown in below image.

Once we press enter button on our keyboard then we can see the output of FACT function.

To find the output in remaining cells just copy and paste the FACT function in remaining cells. We see the output as shown in below image.

In this way we can use FACT function in excel.

Important Points.

1. Factorial of 0 will be 1.

2. If we try to find the factorial of non-negative number, then excel will give us #NUM error.

3. Maximum value for which we can calculate factorial in excel is 170. If we try to calculate factorial of 171 then excel will give us #NUM error.

Applying HLOOKUP in Excel

2023-01-28T09:00:00.003+05:30

In this blog post we will see how we can apply HLOOKUP in excel.

For this we have prepared a dummy data as shown in the below image. This data is divided into two groups.

Blue Highlighted Data: This data contains Student Names and marks obtained by each student in the subject Maths, English and Science. In the last row we have calculated total marks obtained by each student.

Red Highlighted Data: This data contains similar headers as that of Blue Highlighted data. We will apply HLOOKUP in these cells.

Methods to find HLOOKUP Function on Excel Screen:

We can find HLOOKUP function using two different methods.

In this first method, HLOOKUP function can be found in “Lookup & Reference” which we can find in “Function Library” group under “Formulas” tab (Below screenshot for your reference).

Once we click on this “HLOOKUP”, one dialog box named as “Function Arguments” will appear on excel screen. There are total 04 arguments available for this HLOOKUP function. Once we enter all the 04 arguments we can get the desired output.

In this second method, we have to press “=” button and then type “HLOOKUP” word and after that we have to type open bracket “(“. With this, all the 04 arguments will pop up on the screen. Once we enter all the 04 arguments we can get the desired output.

Arguments of HLOOKUP Function:

There are total 04 argument for HLOOKUP function as listed below:

lookup Value: This is the value which we want to look. Our HLOOKUP output will be based on this value. This is mandatory argument.

table Array: This is the table in which we want to look the “Lookup Value”. This is mandatory argument.

row index num: In this argument we have to enter number of row from which we want the output. This is mandatory argument.

[range lookup]: This argument is optional. This argument again contains two sub-arguments TRUE or FALSE.

a. TRUE: It will find the “approximate” match of Lookup value.

b. FALSE: It will find the “exact” match of Lookup value.

Now we will see how we can apply HLOOKUP to find the marks obtained by selected student in subjects Maths, English and Science as well as to find the Total marks. We have applied Drop Down List in cell “J2” where we can select the Students Name by using this Drop Down List.

In this example, we have selected Student Name “C” with the help of Drop Down List and we will apply the HLOOKUP based on this value.

To Find Marks in Maths using HLOOKUP Function:

1. Select the cell “J3”.

2. Apply the HLOOKUP function and enter all the 04 arguments as listed below:

a. Lookup Value: We want to look Student Name, hence in this argument we will select cell “J2” where we have already selected the Student Name “C”.

b. Table Array: Select all the cells from Blue Highlighted Data as we want to find the marks obtained by Student “C” from this table.

c. Row Index Number: Enter number “2” in this argument as we are calculating marks obtained in Maths and they are listed in 2nd row from the Lookup value in our table array.

d. Range Lookup: Enter FALSE in this argument as it will find the “exact” match of our Lookup value.

Below image is for your reference.

3. Once we enter all the 04 arguments, hit enter button on keyboard and we can see the HLOOKUP output.

To Find Marks in English using HLOOKUP Function:

1. Select the cell “J4”.

2. Apply the HLOOKUP function and enter all the 04 arguments as listed below:

a. Lookup Value: We want to look Student Name, hence in this argument we will select cell “J2” where we have already selected the Student Name “C”.

b. Table Array: Select all the cells from Blue Highlighted Data as we want to find the marks obtained by Student “C” from this table.

c. Row Index Number: Enter number “3” in this argument as we are calculating marks obtained in English and they are listed in 3rd row from the Lookup value in our table array.

d. Range Lookup: Enter FALSE in this argument as it will find the “exact” match of our Lookup value.

Below image is for your reference.

3. Once we enter all the 04 arguments, hit enter button on keyboard and we can see the HLOOKUP output.

To Find Marks in Science using HLOOKUP Function:

1. Select the cell “J5”.

2. Apply the HLOOKUP function and enter all the 04 arguments as listed below:

a. Lookup Value: We want to look Student Name, hence in this argument we will select cell “J2” where we have already selected the Student Name “C”.

b. Table Array: Select all the cells from Blue Highlighted Data as we want to find the marks obtained by Student “C” from this table.

c. Row Index Number: Enter number “4” in this argument as we are calculating marks obtained in Science and they are listed in 4th row from the Lookup value in our table array.

d. Range Lookup: Enter FALSE in this argument as it will find the “exact” match of our Lookup value.

Below image is for your reference.

3. Once we enter all the 04 arguments, hit enter button on keyboard and we can see the HLOOKUP output.

To Find Total Marks using HLOOKUP Function:

We can also calculate the Total marks obtained by Student “C” with the help of SUM function but this blog post is specifically for learning HLOOKUP, we will use HLOOKUP function to calculate total marks.

1. Select the cell “J6”.

2. Apply the HLOOKUP function and enter all the 04 arguments as listed below:

a. Lookup Value: We want to look Student Name, hence in this argument we will select cell “J2” where we have already selected the Student Name “C”.

b. Table Array: Select all the cells from Blue Highlighted Data as we want to find the marks obtained by Student “C” from this table.

c. Row Index Number: Enter number “5” in this argument as we are calculating total marks and they are listed in 5th row from the Lookup value in our table array.

d. Range Lookup: Enter FALSE in this argument as it will find the “exact” match of our Lookup value.

Below image is for your reference.

3. Once we enter all the 04 arguments, hit enter button on keyboard and we can see the HLOOKUP output.

With the above listed steps, you can easily apply HLOOKUP function in excel.

OFFSET Function in Excel

2023-01-21T09:00:00.072+05:30

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])

Arguments of OFFSET Function:

OFFSET function takes below arguments.

reference: We need to provide reference of a single cell or a cell range in this argument.

rows: We need to provide positive of negative numbers in this argument. Positive number in this argument indicates that the original reference will shift towards upside and negative number in this argument indicates that the original reference will shift towards downside.

cols: We need to provide positive of negative numbers in this argument. Positive number in this argument indicates that the original reference will shift towards right side and negative number in this argument indicates that the original reference will shift towards left side.

[height]: This is optional argument. We need to provide positive number in this argument.

[width]: This is optional argument. We need to provide positive number in this argument.

Example of OFFSET Function:

Let’s learn to use OFFSET function with the below example. We have listed names of few employees and their sales figures from last 05 years.

Now, we will consider cell “A2” as an initial reference. This cell is highlighted with yellow color in below image.

Now we want to return the value which is 2 columns towards right and 2 rows below the initial reference (i.e., from yellow highlighted cell). Then we will use OFFSET function as below.

Once we press enter button on our keyboard then we will get the output as below. In this case, cell reference of yellow highlighted cell is shifted 2 rows down and 2 columns right. The blue arrows in below screenshot will help you to understand the movement of original reference and how we get the output value.

While using OFFSET function above, we have provided row number and column numbers directly in the function. But we can also provide reference to these tow arguments as well.

Refer below table, where we have already listed row numbers and column number and with the help of this we will apply OFFSET function.

Now select a cell where we want to apply OFFSET function in this table.

Apply OFFSET function in selected cell as shown in below image.

Once we press enter button on our keyboard then we can see the output of OFFSET function as below.

To get the OFFSET output in remaining cell, we need to just copy and paste this function in remaining cells.

In this way we can use OFFSET function in excel.

INDEX Function in Excel

2023-01-14T09:00:00.012+05:30

In this blog post we will learn to about INDEX function in excel.

We can use INDEX function to find value which is present at the intersection of provided row number and column number in g given cell range.

For example, we want to find our which value is present at the intersection point of row 3 and column 4 in a given table then applying INDEX function is a perfect solution.

Where to find INDEX function on Excel Screen:

INDEX 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. INDEX function is highlighted in blue in below image.

Once we click on INDEX option as highlighted above, we will get the select argument dialog box as shown in below image. As this function has multiple argument lists, we must select one of the two argument lists appearing in this dialog box. In this case, we have selected 1st list.

Once we click on OK button in above dialog box, we will get function arguments dialog box as shown in below image.

Syntax of INDEX Function:

The Syntax of INDEX function is as below:

=INDEX(array, row_num, [column_num])

Arguments of INDEX Function:

INDEX function takes below arguments.

array: We have to select cell range in this argument.

row_num: We need to provide row number from selected cell range in this argument.

[column_num]: We need to provide column number from selected cell range in this argument.

Important Note:

The row number and column number we are talking above is not a row and column number of excel sheet. We are talking about the row number and column number of a selected cell range or array from 1st argument.

Below image will help you to understand this.

Example of INDEX Function:

Let’s learn to use INDEX function with the below example. We have listed names of few students and marks obtained by them in various subjects as shown in below image.

From this data, if we want to find out the value which is at the intersection of provided row number and provided column number then INDEX function is the best solution for this. We will find the values from various rows and columns as per the combination provided in below table with the help of INDEX function.

Now we will apply INDEX function to find out our desired result. For this select a cell where we want to apply INDEX function.

Apply INDEX function in selected cell as shown in below image.

Once we press enter button on our keyboard then we can see the output of INDEX function.

To find the output in remaining cells just copy and paste the INDEX function in remaining cells. We see the output as shown in below image.

In this way we can use INDEX function in excel.

MATCH Function in Excel

2023-01-07T09:00:00.001+05:30

In this blog post we will learn to about MATCH function in excel.

We can use MATCH function to find relative position of a provided item in a cell range. For example, we have listed name of few countries in one column, and we want to find out the relative position of specific country in that cell range then we can do so by using MATCH function.

Where to find MATCH function on Excel Screen:

MATCH 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. MATCH function is highlighted in blue in below image.

Once we click on MATCH option as highlighted above, we will get the function argument dialog box as shown in below image:

Syntax of MATCH Function:

The Syntax of MATCH function is as below:

=MATCH(lookup_value, lookup_array, [match_type])

Arguments of MATCH Function:

MATCH function takes below argument.

lookup_value: In this argument we have to provide a value for which we want to find our relative position in a cell range.

lookup_array: In this argument we have to provide a cell range which contains possible lookup values

[match_type]: There are 03 match types available in this argument 1, 0 and -1. We can select any one based on our requirement.

1 (Less Than): This option is to be selected if and only if the values in lookup_array are arranged in ascending order. It will find the largest value which is less than or equal to provided lookup value.

0 (Exact Match): This option is to be selected if and only if the values in lookup_array are arranged in any manner. It will find the first value from a cell range which is exactly matching with the lookup_value.

-1 (Greater Than): This option is to be selected if and only if the values in lookup_array are arranged in descending order. It will find smallest value which is greater than or equal to provided lookup value.

Example of MATCH Function:

Let’s learn to use MATCH function with the below example. We have listed names of few countries in a single column. If we have to find out relative position of a any specific country in that column, then we can use MATCH function.

Now, we will enter the name of country in cell “E3”. We can either type the name of specific country manually or we can also create drop down list for this activity. Here, in our case we have created drop down list to select country instead of typing their name each time. This will also help us to avoid typo errors.

Select any value from the drop-down list. We have selected India as shown in below image.

Now select a cell where we want to apply MATCH function.

Apply MATCH function in selected cell as shown in below image.

Once we press enter button on our keyboard then we can see the relative position of selected country in array. As India is listed at 1st place, the output is showing as 1.

If we change the name of country in cell “E3”, the output in cell “E5” will change accordingly.

In this way we can use MATCH function in excel.

TRANSPOSE Function in Excel

2022-12-31T09:00:00.016+05:30

In this blog post we will learn to about TRANSPOSE function in excel. We can use TRANSPOSE function to convert vertical cell range into horizontal cell range and horizontal cell range into vertical cell range.

Where to find TRANSPOSE function on Excel Screen:

TRANSPOSE 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. TRANSPOSE function is highlighted in blue in below image.

Once we click on TRANSPOSE option as highlighted above, we will get the function argument dialog box as shown in below image:

Syntax of TRANSPOSE Function:

The Syntax of TRANSPOSE function is as below:

=TRANSPOSE(array)

Arguments of TRANSPOSE Function:

TRANSPOSE function takes only one argument.

array: In this argument we have to select cell range which we want to convert from horizontal to vertical or from vertical to horizontal.

Example of TRANSPOSE Function:

Let’s learn to use TRANSPOSE function with the below example. We have prepared a dummy data as shown in below screenshot in which we have listed marks obtained by 05 students (A, B, C, D and E) in subjects Maths, English and Science. In the last column, we have calculated total marks obtained by each students using SUM function.

Now, we want to transpose this data i.e., we want to convert cell range from vertical to horizontal and from horizontal to vertical. To do so, we will use TRANSPOSE function. For this, select the cell where we want to convert cell range from vertical to horizontal and from horizontal to vertical. In this case we have selected cell “J2” as shown in below image.

In selected cell “J2”, we will apply TRANSPOSE function as shown in below image.

Press enter button on keyboard which will show us the result of TRANSPOSE Function. You will get same result as shown in below image.

In this way, we can use TRANSPOSE function to convert vertical cell range into horizontal cell range and horizontal cell range into vertical cell range.

CHOOSE Function in Excel

2022-12-26T16:39:00.001+05:30

In this blog post we will learn to about CHOOSE function in excel. CHOOSE function will choose a value or an action to perform based on the provided index number.

Where to find CHOOSE function on Excel Screen:

CHOOSE 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. CHOOSE function is highlighted in blue in below image.

Once we click on CHOOSE option as highlighted above, we will get the function argument dialog box as shown in below image:

Syntax of CHOOSE Function:

The Syntax of CHOOSE function is as below:

=CHOOSE(index_num, value1, [value2], …)

Arguments of CHOOSE Function:

CHOOSE function have below arguments.

index_num: This argument decides which value argument is to be selected. We must provide number between 1 to 254. If we are providing any formula or a reference to a number, then it should be between 1 to 254.

Value1: We can provide any value or formula or function or any cell reference which will get selected based on the index number as defined above.

We can provide maximum 255 value arguments from value1 to value254.

Example of CHOOSE Function:

Let’s learn to use CHOOSE function with the below example. We have prepared a dummy data as shown in below screenshot. In this data we have mentioned employee ratings and their performance based on these rating.

We will learn to use CHOOSE function with this data to check the performance of employee based on the provided rating.

For this, we must Select the cell in which we want to apply CHOOSE function. In this case we have selected “C10” where we will apply CHOOSE Function.

In selected cell “C10”, we will apply CHOOSE function as shown in below image.

Press enter button but with this we will an error in our output. This is because we have not yet provided any index number in cell B10.

To get the correct output value, we need to provide index number in cell B10. We can also type the index number from 1 to 5 manually but in this case we will create drop down list in cell B10 which will make us easier to select the index number from drop down list.

Now we will select the index number from drop down list and accordingly we can see the output of CHOOSE function in cell C10.

Similarly, we can select other index numbers from drop down list and the output of CHOOSE function will change accordingly.

In this way, we can use CHOOSE function in excel.

COLUMNS Function in Excel

2022-10-30T21:30:00.002+05:30

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 automatically provide column number of a cell where we are currently typing this function.

Example of COLUMNS Function:

Let’s learn to use COLUMNS function with the below example. We have prepared a dummy data as shown in below screenshot. In this we have highlighted cell range with brown colors. Now, we will find out the numbers of COLUMNS from this brown highlighted cell range.

For this, we have to Select the cell in which we want to find out the COLUMNS number of highlighted cell. In this case we have selected “B1” which is highlighted with light blue color.

In selected cell “B1”, we will apply COLUMNS function as shown in below image where we have provided reference of a brown highlighted cell.

Once we hit enter button, we can see the output out COLUMNS function in below image.

In this way we can use COLUMNS function in excel.

ROWS Function in Excel

2022-10-23T21:30:00.003+05:30

In this blog post we will learn to about ROWS function in excel. ROWS function will return number of rows from selected cell range.

Where to find ROWS function on Excel Screen:

ROWS 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. ROWS function is highlighted in blue in below image.

Once we click on ROWS option as highlighted above, we will get the function argument dialog box as shown in below image:

Syntax of ROWS Function:

The Syntax of ROWS function is as below:

=ROWS(array)

Arguments of ROWS Function:

ROWS function takes only one argument.

array: In this argument, we have to provide range of cells from which we want to find out numbers of rows.

If we do not provide any reference here, then excel will automatically provide the number of a cell where we are currently typing this function.

Example of ROWS Function:

Let’s learn to use ROWS function with the below example. We have prepared a dummy data as shown in below screenshot. In this we have highlighted cell range with yellow colors. Now, we will find out the numbers of rows from this brown highlighted cell range.

For this, we have to Select the cell in which we want to find out the ROWS number of highlighted cell. In this case we have selected “A2” which is highlighted with light blue color.

In selected cell “A2”, we will apply ROWS function as shown in below image where we have provided reference of a brown highlighted cell.

Once we hit enter button, we can see the output out ROWS function in below image.

In this way we can use ROWS function in excel.

COLUMN Function in Excel

2022-10-16T21:30:00.002+05:30

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 provide the COLUMN number of a cell where we are currently typing this function.

Example of COLUMN Function:

Let’s learn to use COLUMN function with the below example. We have prepared a dummy data as shown in below screenshot. In this we have highlighted few cells with different colors. Now, we will find out the COLUMN numbers of these colored cells with the help of COLUMN function.

For this, we have to Select the cell in which we want to find out the COLUMN number of highlighted cell. In this case we have selected “B2”.

In selected cell “B2”, we will apply COLUMN function as shown in below image where we have provided reference of a yellow highlighted cell.

Once we hit enter button, we can see the output out COLUMN function in below image.

To apply COLUMN function in all the remaining cells in row 2, we have to just copy the COLUMN function which we applied in cell B2 and paste it in remaining cells. We can see the result in all the remaining cells as shown in below image.

In this way we can use COLUMN function in excel.

ROW Function in Excel

2022-10-09T17:39:00.000+05:30

In this blog post we will learn to about ROW function in excel. ROW function will return a row number of a selected cell.

Where to find ROW function on Excel Screen:

ROW 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. ROW function is highlighted in blue in below image.

Once we click on ROW option as highlighted above, we will get the function argument dialog box as shown in below image:

Syntax of ROW Function:

The Syntax of ROW function is as below:

=ROW()

Arguments of ROW Function:

ROW function takes only one argument.

reference: In this argument, we have to provide cell reference of a cell for which we want to find our row number.

If we do not provide any reference here, then excel will automatically provide the row number of a cell where we are currently typing this function.

Example of ROW Function:

Let’s learn to use ROW function with the below example. We have prepared a dummy data as shown in below screenshot. In this we have highlighted few cells with different colors. Now, we will find out the row numbers of these colored cells with the help of ROW function.

For this, we have to Select the cell in which we want to find out the row number of highlighted cell. In this case we have selected “B2”.

In selected cell “B2”, we will apply ROW function as shown in below image where we have provided reference of a yellow highlighted cell.

Once we hit enter button, we can see the output out ROW function in below image.

To apply ROW function in all the remaining cells in column B, we have to just copy the ROW function which we applied in cell B2 and paste it in remaining cells. We can see the result in all the remaining cells as shown in below image.

In this way we can use ROW function in excel.

Merge data from Duplicate Rows into a Single Cell in Excel.

2022-09-24T21:30:00.001+05:30

In this blogpost, we will learn to merge data from duplicate rows into a single cell in excel. Many times, we may face a situation where we have duplicate entries in rows, and we want to merge the data from these duplicate entries into single cell. This post is perfect solution for similar problems. Let us understand this solution with simple example.

Let us assume that we have a supplier database in which we have multiple contacts listed for same supplier in multiple rows (as shown in below image).

In this database, Supplier 1 with unique supplier code has 03 different Contact IDs listed in 03 different rows and same scenario is with Supplier 2, Supplier 3, Supplier 4 and Supplier 5.

Now we want to merge these multiple Contacts IDs for respective supplier in a single cell separated by semicolon (;).

To merge data from multiple rows into a single cell in excel, follow below simple steps:

Before following the below steps make sure “Supplier Code” column is sorted (Sort Type is A to Z) if this column is not already sorted. In this case this column is already sorted from A to Z.

1. Add one helper column in above data where we will merge multiple contact IDs. In this case we have added helper column in “Column D” and named this column as “Output of Merged Data”.

2. Select cell D2 where we will enter one formula with the help of which we can merge the contact IDs for duplicate supplier names or duplicate supplier codes.

The formula we will enter is: =IF(B2=B1,CONCATENATE(D1,";",C2),C2)

3. Once we hit “Enter” button, we can see the result of this formula. But keep in mind that this is not our final result.

4. Copy this formula and paste it in the remaining cells in column D. With this we can see the result of this formula in all the remaining cells as well.

5. Now we have to remove duplicate entries and keep only one entry in which multiple Contact IDs got merged. For this we will add another helper column in “Column E” to count how many times each supplier is duplicated.

6. Select cell E2 in which we will enter a formula which will count how many times each supplier is duplicated based on Supplier Code.

The formula we will enter is: =COUNTIF($B2:$B$16,B2)

7. Once we press “Enter” button on our keyboard then we will see the result in cell E2 as shown in below image.

8. Now we will copy above formula and paste it in all the remaining cells in column E. The result will be displayed as shown in below image.

9. Now we have to apply filter in column E, and we will filter only values “1” from this column as shown in below image.

10. Once we press “OK” button highlighted in yellow in above image, we will see the result as shown in below image.

11. Now we have to just hide “Column C” by selecting any cell from column C and press “Ctrl” and “0” keys together on our keyboard. (To learn more about hiding columns in excel click here).

12. Similarly hide “Column E” as well and the appeared result will be our final result.

In this way, we can merge data from duplicate rows in single cell.

NOW Function in Excel

2022-09-17T21:30:00.003+05:30

In our previous blog post, we have seen how to insert Today’s date in excel. In this blog post we will learn to insert today’s date along with current time in excel. To display today’s date along with current time we can use NOW function which is readily available in excel.

Where to find NOW function on Excel Screen:

NOW 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. NOW function is highlighted in blue in below image.

Once we click on NOW option as highlighted above, we will get the function argument dialog box as shown in below image:

Syntax of NOW Function:

The Syntax of NOW function is as below:

=NOW()

Arguments of NOW Function:

NOW function does not take any argument.

Once we click on the “OK” button on dialog box which appears once we click NOW function as explained above, NOW’s date will immediately appear in selected cell.

Another way to use NOW’s Function:

Instead of entering NOW function through Function Arguments dialog box, we can use alternative way which is simpler.

1. Select the cell in which we want to enter today's date along with current time. In this case we have selected cell “A2”.

2. Enter Function “NOW()” in cell “A2” as shown in below image.

3. Once we hit “Enter” button on our keyboard, today's date along with current time will appear in cell “A2”.

Keyboard Shortcuts:

We have seen above couple of methods to insert today’s date along with current time in excel. Now we will see keyboard shortcut with the help of which we can insert today’s date along with current time in excel within a moment.

To do this,

1. Press Ctrl and Semicolon keys together to display today's date.

2. Press spacebar button on keyboard.

3. Press Ctrl, Shift and Semicolon keys together to display current time.

In this way we can use NOW function in excel.

TODAY Function in Excel

2022-09-10T21:30:00.013+05:30

In this blog post we will learn about TODAY function in excel. Whenever we want to display today’s date in excel, we can use TODAY function.

Where to find TODAY function on Excel Screen:

TODAY 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. TODAY function is highlighted in blue in below image.

Once we click on TODAY option as highlighted above, we will get the function argument dialog box as shown in below image:

Syntax of TODAY Function:

The Syntax of TODAY function is as below:

=TODAY()

Arguments of TODAY Function:

This function does not take any argument.

Once we click on the “OK” button on dialog box which appears once we click TODAY function as explained above, today’s date will immediately appear in selected cell.

Another way to insert today’s date:

Instead of entering today’s date through Function Arguments dialog box, we can use alternative way which is simpler.

1. Select the cell in which we want to enter today’s date. In this case we have selected cell “B1”.

2. Enter Function “TODAY()” in cell “B1” as shown in below image.

3. Once we hit “Enter” button on our keyboard, today’s date will appear in cell “B1”.

Keyboard Shortcuts:

We have seen above couple of methods to insert today’s date in excel. Now we will see keyboard shortcut with the help of which we can insert today’s date within seconds. To do this just Press Ctrl and Semicolon keys together and today's date will display in selected cell.

In this way we can use TODAY function in excel.