Trending February 2024 # Lookup In Excel (Formula, Examples) # Suggested March 2024 # Top 2 Popular

You are reading the article Lookup In Excel (Formula, Examples) updated in February 2024 on the website Kientrucdochoi.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Lookup In Excel (Formula, Examples)

Introduction to LOOKUP Function in Excel

For example, if you want to search for the number 5 in a table, you could use the Lookup function to return the other information in the same row or column as 5. The LOOKUP function is a built-in worksheet function in Microsoft Excel. It is available in Lookup & References under the Formula tab.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Syntax of Excel LOOKUP Function, The LOOKUP function in Excel is of two types: Vector and Array.

1. Vector form LOOKUP in Excel

The vector form of the LOOKUP function is useful for searching for a specific value in one row or column and returning a value from the same location in another row or column. For instance, you have a list of students with their names in one column and grades in another, and you want to find a grade for a particular student. The vector form of the LOOKUP function in Excel will search the student’s name and then display their grade from the same row.

If you know the row or column range where the search value is present, you can use this form of the LOOKUP function to get the value from that row or column.

Syntax: =LOOKUP(lookup_value, lookup_vector, [result_vector])

Lookup Value: (Required) It is the value that we want to search in one row or one column. It can be a text, number, cell reference, value, or name.

Lookup Vector: (Required) The range of one row or column where the lookup_value will be first searched. The value can be numbers, text, or reference values.

Result Vector: (Optional) It is the range of a single row or column from where we want to fetch the required value. The size of the result_vector must be the same as that of the lookup vector.

2. Array form LOOKUP in Excel

An array is a combination of values in rows and columns in the form of a table. The array form of the LOOKUP function searches for a specific value in the first row or column and returns a value from the same region in the last row or column of the array. Use this array form only when the values you want to search are in the array’s first row or column.

Note: Vlookup & Hlookup is used instead of an array form because it has limited options

Syntax =LOOKUP(lookup_value, array)

Lookup _value: (Required) lookup_value in array form is the value the LOOKUP function searches for in an array.

Array: (Required)It is the range of cells of multiple rows and columns, like a two-dimensional data (table), where you want to search the lookup_value.

Let’s Learn the Use of the LOOKUP Function in Excel.

In the following section, you will learn how to use the Lookup function with the help of various examples. You will also learn to create a LOOKUP formula to find a value for specific criteria.

You can download this LOOKUP Function Excel Template here – LOOKUP Function Excel Template

Example #1

The result, “Clothes,” is displaced. Thus, Kim sold Clothes.

This LOOKUP function searches the lookup_value, Cell B8 (Kim), in the lookup_vector, A2:A6, and returns the value in the same row from the result_vector, B2:B6. In simple terms, the value of Cell B8 is present in A4, and the function returns the value from the same position in another column, B4. The final output is Clothes.

Example #2

Let’s take another example to understand the vector form of the LOOKUP function.

Output 70 is displayed as shown below. Hence, the price of ID A04 is 70.

Explanation of the formula: “=LOOKUP(B8,A2:A6,C2:C6)”

The lookup_value, A04, is first searched in the range of lookup_vector (A2 to A6). Here the lookup_value is A04; the lookup_vector is A2:A6, and the result_vector is C2:C6. The look_up value is in A5; now, the function returns the value in the same row from the result_vector range (B2:B6), i.e., B5. Thus, the output of the given formula is 70.

Example #3

Output 20 is displaced in Cell B7. Thus, the quantity of apples is 20.

Example #4

The result of the formula is 60. Thus, the price of the product sold by Doug is 60.

Explanation of the formula: “=LOOKUP(B8, A1:C6)”

Here, the lookup_value is B8, and the array range is A1: C6. The formula will search for the value of Cell B8 from Cell A1 to Cell C6 and returns the value corresponding to Cell B8.

Example #5

The syntax for retrieving the last value of a specific column is:

Things to Remember 1. While Using the Vector Form of the LOOKUP Function

The LOOKUP function with a result_vector provided will check for the lookup_value in the lookup_vector range and return the corresponding value from the result_vector.

Without a result_vector, it will return the result value found from the lookup_vector.

If the lookup_value cannot be found, the function returns the next smallest value or equal to the lookup_value.

If the lookup_value is less than the lowest value in the lookup_vector, the function will display the #N/A error.

The function will return the last value in the range of the lookup value that exceeds each vector value.

If it cannot find the exact lookup_value, it searches for the next highest value that is lower than or equal to the lookup_value.

Let us learn about the above conditions through an example. The below data contains details of employee codes and their name.

Note: The data should be in ascending order.

Formula

Description

Result

=LOOKUP(1150,A3:A7,B3:B7) The Lookup function finds the exact match of the lookup_value in column A. Elizabeth

=LOOKUP(1156, A3:A7, B3:B7) The function looks for the nearest value for 1156 and returns the value of 1157 John

=LOOKUP(1151, A3:A7, B3:B7) The function looks for the nearest value for 1151  and returns the value of 1150 Elizabeth

=LOOKUP(1124,A3:A7,B3:B7) lookup_value is smaller than all values present in A3:A7. #N/A

=LOOKUP(0, A3:A7, B3:B7) The function looks for 0 and returns an error because 0 is less than the smallest value, 1150. #N/A

=LOOKUP(1162, A3:A7, B3:B7) The function looks up for value 1162 and returns the last value from column A. Mary

2. While Using the Array Form of the LOOKUP Function

If the array range has multiple columns than rows (broader than taller), then the LOOKUP function will look for the lookup_value in the first row of the array (like HLOOKUP).

If the array range has multiple rows than columns (taller than broader) or an equal number of rows and columns, then the LOOKUP function will look for the lookup_value in the first column (like VLOOKUP).

In both cases, the LOOKUP function will return the very last value in the row or column of the array.

Essential Notes

Sort the values in the lookup vector and result vector in ascending order, e., from A to Z if the values are in text and smallest to biggest if they are in numeric form.

The LOOKUP function in Excel may provide incorrect results or errors if the data is unsorted.

The LOOKUP function is not case-sensitive. It does not distinguish between text written in uppercase and lowercase.

The lookup_vector and result_vector should be the same size when using the vector form of the LOOKUP function.

Frequently Asked Questions(FAQs)

Answer: There are two types of lookup formulas in Excel.

Vector form: The formula for the vector form of the lookup function is “=LOOKUP(lookup_value, lookup_vector, [result_vector]).”

Array form: The formula for the array form of the lookup function is “=LOOKUP(lookup_value, array).”

Recommended Articles

This article is a tutorial for the LOOKUP Function in Excel. We have covered how to use the LOOKUP function for different conditions in Excel, practical examples, and a downloadable Excel template. You might also read our other recommended articles:

You're reading Lookup In Excel (Formula, Examples)

Xirr In Excel (Formula, Examples)

XIRR Function in Excel

XIRR function is also one of the financial functions in Excel, which is used to calculate the Internal Rate of Return (IRR) in any series of intervals. XIRR function works when we assign the dates of obtained intervals, whether regular or irregular and calculates the internal rate of return on that array of cash flow. The good thing about XIRR Function is it considers only irregular series of cash flow or installment intervals. In contrast, most of the functions in Excel give fine results when a regular interval series is selected.

Returns:

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

XIRR Formula in Excel

Below is the XIRR Formula.

How to Open XIRR Function?

We Get A New Function Windows Showing In Below Mention Picture.

Then we have to enter the details of cash value and dates.

The shortcut of using the XIRR Formula

Values (Required Argument) –The values representing the series of cash flows. Instead of an array, it can reference a range of cells containing values.

Dates (Required Argument) – A series of dates corresponding to the given values. Subsequent dates should be later than the first date as the first date is the start date, and subsequent dates are future dates of outgoing payments or income.

Guess (Optional Argument) –It is an initial guess of the IRR. If omitted, Excel takes the default value, which is 10%.

The format of the result

It is the internal interest rate, so we consider it in the percent (%) number format.

How to use the XIRR Function in Excel?

This XIRR function is very simple and easy to use. Let us now see how to use XIRR in Excel with the help of some examples.

You can download this XIRR function Excel Template here – XIRR function Excel Template

Example #1

Calculating XIRR value using XIRR Formula  =XIRR(H3: H9, G3: G9)

The answer will be 4.89%

In the above table, the interest inflows are irregular. Hence, you can use the XIRR function in Excel to compute

The internal rate of interest on these cash flows, the investment amount is showing a minus sign. When you get the result, then change the format with %.

Example #2

Calculating XIRR value using XIRR Formula  =XIRR(A3: A15, B3: B15)

The answer will be 16.60%

In an Excel sheet, first, enter the original amount invested. A ‘minus sign should represent the amount invested. In the following cells, enter the returns received during each period. “Remember to include the ‘minus’ sign whenever you invest the money.

Now, find XIRR using the values that refer to a series of cash flows corresponding to a schedule of payments in dates. The series of values must contain at least one positive and one negative value. The first payment refers to the investment made at the beginning of the investment period and must be a negative value. All succeeding payments are discounted based on a 365-day-year.

The date is when the first investment and the returns were received. Each date should correspond to its respective investment made or income received, as shown in the above table. Dates should be entered in a ‘DD-MM-YY (date-month-year)’ format, as errors can occur if the format is not followed. If any number in the dates is invalid, or the format of dates is inconsistent, XIRR will reflect the “#value!” Error.

If we discuss error problems, this is also an important part. If our data is not correct, we will face such a type of issue as below mention.

#Num! Occurs if either:

The supplied values and date arrays have different lengths.

The supplied values array does not contain at least one negative and at least one positive value;

Any of the supplied dates proceeds to the first supplied date.

The calculation needs to converge after 100 iterations.

#value! : – occurs if any supplied dates need to be recognized as valid Excel dates.

If you attempt to input dates in text format, there is a risk that Excel may misinterpret them, depending on the date system or data interpretation settings on your computer.

Recommended Articles

This has been a guide to XIRR Function. Here we discuss the XIRR Formula and how to use XIRR Function along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –

Histogram In Excel (Types, Examples)

Histogram in Excel

Histogram Chart in Excel is a data analysis tool used to show the periodic rise and drop in the data with the help of vertical columns. We can find the Histogram chart option if we use Excel 2024. Still, for the older version of MS Excel, such as 2013 and 2010, we need to find this option in the Data Analysis option, which is available under the Data Analysis option.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Uses of Histogram Chart in Excel

A histogram is a graphical representation of the distribution of numerical data. A histogram is a column chart that shows the frequency of data in a certain range in a simpler way. It provides the visualization of numerical data by using the number of data points that fall within a specified range of values (also called “bins”).

A histogram chart in Excel is classified or made up of 5 parts:

Title: The title describes the information about the histogram.

X-axis: The X-axis is the grouped interval that shows the scale of values in which the measurements lie.

Y-axis: The Y-axis is the scale showing the times the values occurred within the intervals corresponding to the X-axis.

The bars: This parameter has a height and width. The height of the bar shows the number of times the values occurred within the interval. The width of the bars shows the interval or distance, or covered area.

Legend: This provides additional information about measurements.

Why is the histogram chart important in Excel?

There are many benefits to using a Histogram chart in Excel.

The histogram chart shows the visual representation of data distribution.

A histogram chart displays a large amount of data and the occurrence of data values.

Easy to determine the median and data distribution.

Types/Shapes of Histogram Chart

It depends on the distribution of data; the histogram can be of the following type:

Normal Distribution

A Bimodal Distribution

A Right Skewed Distribution

A Left Skewed Distribution

A Random Distribution

Now we will explain the shapes of the Histogram chart in Excel.

Normal Distribution:

A Bimodal Distribution:

There are two peaks. This is also called Double peaked distribution in this dist. Under this distribution in one data set, the results of two processes with different distributions are combined. The data is separated and analyzed like a normal distribution. This looks like the below image:

A Right Skewed Distribution:

In this distribution, a large number of data values occur on the left side and a fewer number of data values on the right side. This is also called a positively skewed distribution. This distribution occurs when the data has a range boundary on the left-hand side of the histogram. For example, a boundary of 0. This dist. It looks like the below image:

A Left Skewed Distribution:

This is also called a negatively skewed distribution. In this distribution, a large number of data values occur on the right side and a fewer number of data values on the left side. This distribution occurs when the data has a range boundary on the right-hand side of the histogram. For example, a boundary such as 100. This dist. It looks like the below image:

A Random Distribution:

This is also called a multimodal distribution. In this dist., several processes with normal distributions are combined. This has several peaks; thus, the data should be separated and analyzed separately. This dist. It looks like the below image:

Where is the Histogram Chart found in Excel?

The histogram chart option is found under Analysis ToolPak. The Analysis ToolPak is a Microsoft Excel data analysis add-in. This add-in is not loaded automatically on Excel. Before using this, we need to load it first.

Steps to load the Analysis ToolPak add-in:

This will open the below Add-Ins dialog box.

This will open an Add-Ins dialog box.

The Analysis ToolPak is loaded in excel now and will be available under the DATA tab with the name Data Analysis.

How to Create a Histogram Chart in Excel?

Before creating a histogram chart in Excel, we must create the bins in a separate column.

Bins represent the intervals into which we want to group the data set. These intervals should be consecutive, non-overlapping, and of equal size.

There are two ways to create a histogram chart in Excel:

If you are working on Excel 2024, there is a built-in histogram chart option.

If you are working on Excel 2013, 2010, or earlier versions, you can create a histogram using Data Analysis ToolPak.

Creating a Histogram chart in Excel 2024:

Excel 2024 adds a histogram chart option as an inbuilt chart under the chart section.

Select the entire dataset.

The histogram chart would appear based on your dataset.

Creating a Histogram chart in Excel 2013, 2010, or earlier version:

Download the Data Analysis ToolPak as shown in the above steps. You also can activate this ToolPak in Excel 2024 version too.

Examples of Histogram Chart in Excel

You can download this Histogram Chart Excel Template here – Histogram Chart Excel Template

Example #1

Let’s create a dataset of scores (out of 100) of 30 students as shown below:

For creating a histogram, we need to create the intervals at which we want to find the frequency. These intervals are called bins. 

Below are the bins or score intervals for the above data set.

Please follow the below steps to create the Histogram chart in Excel: 

A Histogram dialog box will open.

In the Histogram dialog box, we will enter the following details:

Select the Input Range (as per our example – with the scores column B)

Select the Bin Range ( Intervals column D)

This would create a Frequency Distribution table and the Histogram chart in the specified cell address.

There are below points that need to keep in mind while creating Bin’s or Intervals:

The First bin includes all the values below it. For bin 30, frequency 5 includes all the scores below 30.

The last bin is 90. This bin includes any data values which are higher than the last bin. In our example, 3 values are higher than the last bin, 90. If the values are higher than the last bin, Excel automatically creates another bin – More.

This chart is called a static histogram chart. If you want to change the source data, you must create the histogram again.

You can do the formatting of this chart like other charts.

Example #2

Let’s take another example with 15 data points which are the salary of a company.

Now we will create the bins for the above dataset.

For creating the histogram chart in Excel, we will follow the same steps as in example 1.

It will open a histogram dialog box.

Select the Input Range with the Salary data points.

Select the Bin Range with the bins column.

The below chart will appear as follows:

Explanation:

The First bin, $25,000, will include all data points less than this value.

The last bin, More, will include all the data points over $65,000. It’s created automatically by Excel.

Pros

The histogram is very useful while working with a large amount of data.

A histogram chart shows the data in a graphical form that is easy to compare and understand.

Cons

Histogram chart is very difficult to extract the data from the input field in the histogram. It means difficult to point out the exact number.

While working with histograms, it creates a problem with multiple categories.

Things to Remember About Histogram Charts in Excel

A histogram chart uses for continuous data and determines the range of data with the bin.

The bins are usually determined as consecutive and non-overlapping intervals.

The bins must be adjacent and are of equal size (but are not required to be).

If you are working with Excel 2013, 2010, or earlier versions, you must activate the Excel Add-Ins for Data Analysis ToolPak.

Recommended Articles

This has been a guide to histograms in Excel. Here we discuss its types and how to create a Histogram chart in Excel, along with Excel examples and a downloadable Excel template. You may also look at these useful charts in Excel –

How To Implement & Apply Lookup Function In Excel Vba?

Excel VBA Lookup Function

In excel, we have used VLookup many times. It is there in Insert function which is used to fetch or map any kind of values we want. Similarly, in VBA we have Lookup application, which works as same as Excel Vlookup. VBA Lookup has a flexible data structure as it can be used to map any kind of value from any kind of table array. This means if we apply Excel Vlookup, then we won’t be able to map the right column data with left column data in one syntax. Whereas in VBA Lookup, there is no proper structure of mapping. We just need to follow the syntax of VBA Lookup. If the syntax is satisfied and properly framed then we can fetch the values from the right or left side of the table.

Syntax of VBA Lookup Function:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Where,

Arg1 = Value we want to lookup.

Arg2 = Range of the columns or table from where we want to lookup.

Arg3 = Result as Boolean or Vector.

How to Use the Lookup Function in Excel VBA?

We will learn how to use the Lookup function in Excel by using the VBA Code.

You can download this VBA Lookup Excel Template here – VBA Lookup Excel Template

VBA Lookup – Example #1

There are many ways to write the VBA Lookup code. In this example, we will see a simple way to write the VBA Lookup. For this, we have a data set as shown below. This table has the number of races and the average speed of the racers. Now we will be using this data to apply VBA Lookup in the below table with the Blue headers in A8 to B8. For this, follow the below steps:

Step 1: Open a Module from the Insert menu tab as shown below.

Code:

Sub

VBA_Lookup1()

End Sub

Step 3: Select the output cell where we need to see the lookup value. Here that cell is B9 where we will be lookup with the value concerning the name “Aniket” which is our lookup value.

Code:

Sub

VBA_Lookup1() Range("B9").Value

End Sub

Step 4: Now we will use Worksheet Function and select Lookup function inbuilt in the list as shown below.

Code:

Sub

VBA_Lookup1 () Range("B9").Value = WorksheetFunction.Lookup

End Sub

Step 5: Once we select the Lookup function, we will see the Arg1, Arg2, and Arg3 in its syntax. For that, we will first put our Lookup value range which is cell A9 in place of Arg1.

Code:

Sub

VBA_Lookup1() Range("B9").Value = WorksheetFunction.Lookup(Range("A9").Value,

End Sub

Step 6: Now for Arg2, select the lookup range which is from cell A2:A5.

Code:

Sub

VBA_Lookup1 () Range("B9").Value = WorksheetFunction.Lookup(Range("A9").Value, Range("A2:A5"),

End Sub

Step 7: At last, select lookup values ranges which is from B2 to B5 in place of Arg3.

Code:

Sub

VBA_Lookup1() Range("B9").Value = WorksheetFunction.Lookup(Range("A9").Value, Range("A2:A5"), Range("B2:B5"))

End Sub

VBA Lookup – Example #2

There is another way to apply a Lookup function in Excel VBA. For this, we will be using the same data that we have seen in example-1. For this, follow the below steps:

Step 1: Write the subprocedure for VBA Lookup, as shown below.

Code:

Sub

VBA_Lookup2()

End Sub

Step 2: Define a variable as String which will be used to map the Name column.

Code:

Sub

VBA_Lookup2()

Dim

Name

As String

End Sub

Step 3: In the defined variable Name, we will apply the Vlookup application as shown below.

Sub

VBA_Lookup2()

Dim

Name

As String

Name = Application.VLookup(

End Sub

Step 4: Let’s say our lookup value is named “Ashwani” from the table.

Code:

Sub

VBA_Lookup2()

Dim

Name

As String

Name = Application.VLookup("Ashwani",

End Sub

Step 5: And the range is from A1 to C6 from Sheet1.

Code:

Sub

VBA_Lookup2()

Dim

Name

As String

Name = Application.VLookup("Ashwani", Sheet1.Range("A1:C6"),

End Sub

Step 6: Now if we want to see the average speed of rider “Ashwani” here, we need to map the cell in the Lookup syntax which is at 3rd place.

Code:

Sub

VBA_Lookup2()

Dim

Name

As String

Name = Application.VLookup("Ashwani", Sheet1.Range("A1:C6"), 3)

End Sub

Step 7: Now to see the Average speed of rider “Ashwani”, we can use MsgBox and Debug Print both. But using Debug Print is way better than MsgBox. So Assign Debug Print with defined variable Name.

Code:

Sub

VBA_Lookup2()

Dim

Name

As String

Name = Application.VLookup("Ashwani", Sheet1.Range("A1:C6"), 3)

Debug.Print

Name

End Sub

Step 8: Now open Immediate Window which is there in View menu tab to see the output.

Step 9: Compile the code and run it. We will see, Lookup has mapped with speed of Ashwani and fetched that into Immediate window as 86.

Code:

Sub

VBA_Lookup2()

Dim

Name

As String

Name = Application.VLookup("Ashwani", Sheet1.Range("A1:C6"), 3)

Debug.Print

Name

End Sub

VBA Lookup – Example #3

For using the lookup function in excel VBA, follow the below steps:

Code:

Sub

VBA_Lookup3()

End Sub

Step 2: Declare a variable for Name as String as shown below.

Code:

Sub

VBA_Lookup3()

Dim

Name

As String

End Sub

Step 3: Now assign the name which wants to lookup to defined variable Name as shown below.

Code:

Sub

VBA_Lookup3()

Dim

Name

As String

Name = "Deepinder"

End Sub

Step 4: Now use any word to define and use Lookup lets say LUp. And in that use Worksheet Function with Vlookup as shown below.

Code:

Sub

VBA_Lookup3()

Dim

Name

As String

Name = "Deepinder" LUp = Application.WorksheetFunction.VLookup( MsgBox "Average Speed is : " & LUp

End Sub

Step 5: Now use the same Vlookup syntax as we use in Excel. In Arg1 put the Name variable, then select the range matrix and look for up value which we want to get. Here that column is 3 as shown below.

Code:

Sub

VBA_Lookup3()

Dim

Name

As String

Name = "Deepinder" LUp = Application.WorksheetFunction.VLookup(Name, Sheet1.Range("A2:C6"), 3,

False

) MsgBox "Average Speed is : " & LUp

End Sub

Step 6: Now use MsgBox to see the output.

Code:

Sub

VBA_Lookup3()

Dim

Name

As String

Name = "Deepinder" LUp = Application.WorksheetFunction.VLookup(Name, Sheet1.Range("A2:C6"), 3,

False

) MsgBox "Average Speed is : " & LUp

End Sub

Step 7: Compile and run the code. We will see, for the Name “Deepinder” the Average Speed is coming as 88. Whereas the same value is given for the name Deepinder in the table.

Code:

Sub

VBA_Lookup3()

Dim

Name

As String

Name = "Deepinder" LUp = Application.WorksheetFunction.VLookup(Name, Sheet1.Range("A2:C6"), 3,

False

) MsgBox "Average Speed is : " & LUp

End Sub

Pros of Excel VBA Lookup Function

It is as easy to use and implement as applying regular excel Vlookup formulas in Excel.

We can use any kind of range and matrix in VBA Lookup.

There are very few or no constraints while applying VBA Lookup.

Things to Remember

We can use Lookup in place of Vlookup in any situation.

Range for Lookup vector and result vector should be the same.

Once done the application, save the file as Macro Enable format to retain the code.

There is no mandatory requirement to put the result column every time to be at the right of the lookup value.

Recommended Articles

This is a guide to the VBA Lookup function. Here we discuss how to use the Lookup function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

Rows To Columns In Excel (Examples)

Excel Rows to Columns (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Rows to Columns in Excel

In Microsoft Excel, we can change the rows to columns and columns to rows and vice versa, using TRANSPOSE. We can either use the Transpose or Transpose function to get the output.

The transpose function normally returns a transposed range of cells which is used to switch the rows to columns and columns to rows and vice versa, i.e., we can convert a vertical range of cells to a horizontal range of cells or a horizontal range of cells to a vertical range of cells in Excel.

For example, a horizontal range of cells is returned if a vertical range is entered, or a vertical range of cells is returned if a horizontal range of cells is entered.

How to Convert Rows to Columns in Excel using Transpose?

It is very simple and easy. Let’s understand how to convert rows to columns in Excel using examples.

You can download this Convert Rows to Columns Excel?Template here – Convert Rows to Columns Excel?Template

Steps to use transpose:

Start the cell by selecting and copying an entire range of data.

Choose to paste special, and we will find the transpose button.

We will get the result converted to rows to columns.

Example #1

Consider the below example where we have a revenue figure for sales month-wise. We can see that month data are row-wise and Part number data are column-wise.

If we want to convert the rows to columns in Excel, we can use the transpose function and apply it by following the below steps.

First, select the entire cells from A To G with data information.

Copy the entire data by pressing the Ctrl+ C Key.

Now select the new cells where exactly you need to have the data.

Choose the option paste special.

Select the fourth option in paste special, transpose, as shown in the below screenshot, highlighted in yellow.

In the above screenshot, we can see that rows have been changed to the column, and the column has been changed to rows in Excel. This way, we can easily convert the given data from row to column and column to row in Excel. For the end-user, if there is a huge amount of data, this transpose will be very useful, and it saves a lot of time instead of typing it, and we can avoid duplication.

Example #2

In this example, we will convert rows to columns in Excel and see how to transpose the employee salary data by following the steps below.

Consider the above screenshot, which has the id number, emp name, HRA, Allowance, and Special Allowance. Suppose we need to convert the data column to rows; in these cases, the TRANSPOSE function will be very useful to convert it, which saves time instead of entering the data. We will see how to convert the column to a row with the below steps.

Once you copy the data, choose the new cell location.

We will get the paste special dialogue box.

Choose the Special Paste option.

Select the Transpose option in that, as shown below.

Once you have chosen the transpose option, the Excel row data will be converted to the column, as shown in the below result.

In the above screenshot, we can see the difference that the row has been converted to columns; in this way, we can easily use the transpose to convert horizontal to vertical and vertical to horizontal in Excel.

Transpose Function

In Excel, a built-in function called Transpose Function converts rows to columns and vice versa; this function works like transpose. i.e., we can convert rows to columns or columns into rows and vice versa.

Syntax of Transpose Function:

Array: The range of cells to transpose.

When a set of an array is transposed, the first row is used as the first column of an array, and in the same way, the second row is used as the second column of a new one, and the third row is used as the third column of the array.

If we use the transpose function formula as an array, we have CTRL+SHIFT +ENTER to apply it.

Example #3

In this example, we will see how to use the transpose function with an array with the example below.

Consider the below example, which shows weekly sales data, where we will convert the data to columns to row and row to column and vice versa using the transpose function.

Select the row you want to transpose.

Here we are going to convert the MONTH PLAN to the column.

We can see that there are 11 rows, so to use the transpose function, the rows and columns should be in equal cells; if we have 11 rows, then the transpose function needs the same 11 columns to convert it.

Choose exactly 11 columns, use the Transpose Formula, and select an array from C1 to C11, as shown in the below screenshot.

Now use CTRL+SHIFT +ENTER to apply as an array formula.

Once we use the CTRL+SHIFT +ENTER, we can see the open and close parenthesis in the formulation.

We will get the output where a row has been changed to a column, shown below.

Use the formula for the entire cells to get the exact result.

So the Final Output will be as below.

Things to Remember about Convert Rows to Columns in Excel

The transpose function in Excel is one of the most useful functions, as it allows us to rotate the data without altering its information.

If any blank or empty cells exist, transpose will not work, giving the result zero.

While using the array formula in the transpose function, we cannot delete or edit the cells because all the data are connected with links, and Excel will throw an error message that “YOU CAN NOT CHANGE PART OF AN ARRAY.”

Recommended Articles

This has been a guide to Rows to Columns in Excel. We discuss converting rows to columns in Excel using transpose, practical examples, and a downloadable Excel template. Transpose can help everyone to convert multiple rows to a column in Excel easily and quickly. You can also go through our other suggested articles –

Top Examples With Excel Template

Definition of Quick Ratio

The term “Quick Ratio” refers to the liquidity ratio that assesses the ability of a company to cover its short-term liabilities by utilizing all those assets that can be easily converted into cash. The name “quick ratio” comes from the underlying idea that the ratio considers only those assets that can be quickly liquidated. The ratio is also known as the name acid test ratio.

Start Your Free Investment Banking Course

Download Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others

If a company’s quick ratio is greater than 1, it means that it has more than enough liquid assets that can be used to repay the current liabilities immediately. On the other hand, if it is less than 1, it indicates that the company’s liquidity is inadequate to pay off its current liabilities in case it is required to pay immediately.

Formula:

The formula for Quick Ratio can be derived by dividing the sum of cash, marketable securities, accounts receivables, and other current assets (other than inventories and prepaid expenses) by the total current liabilities. Mathematically, it is represented as,

Quick Ratio = (Cash + Marketable Securities + Accounts Receivable + Other Current Assets) / Total Current Liabilities

One can also derive the formula for Quick Ratio by subtracting inventories and prepaid expenses from the total current assets and then dividing the resulting figure by the total current liabilities. Mathematically, this is represented as:

Quick Ratio = (Total Current Assets – Inventories – Prepaid Expenses) / Total Current Liabilities.

Examples of Quick Ratio (With Excel Template)

Let’s take an example to understand the calculation of the Quick Ratio formula in a better manner.

You can download this Quick Ratio Excel Template here – Quick Ratio Excel Template

Example – #1

Let us take the example of a company that has applied for a bank loan in order to remodel its storefront. The company has provided the following balance sheet information to the bank:

Based on the given information, Calculate the quick ratio of the company.

Solution:

Of the above-mentioned current assets, only cash, marketable securities, and accounts receivable can be considered quick assets.

Calculate Quick Ratio using the formula given below:

QR = ($15,000 + $20,000 + $10,000) / $35,000

QR = 1.29

Therefore, the quick ratio for the company stood at 1.29, which indicates a fairly comfortable liquidity position.

Example – #2

Let us take the latest annual report of Apple Inc. to explain the quick ratio calculation. As per the annual report for the year ended on Sep 29, 2023, the following information is available:

Based on the given information, Calculate the quick ratio for Apple Inc. for the year ending Sep 29, 2023.

Solution:

Out of the above-mentioned current assets, only cash and cash equivalents, marketable securities, net accounts receivable, vendor non-trade receivables, and other current assets can be considered quick assets.

Quick Ratio = (Cash and Cash Equivalents + Marketable Securities + Accounts Receivable + Net Accounts Receivable + Vendor Non-Trade Receivables + Other Current Assets) / Total Current Liabilities

QR = ($25,913 Mn + $40,388 Mn + $23,186 Mn + $25,809 Mn + $12,087 Mn) / $116,866 Mn

QR = 1.09

Therefore, the QR for Apple Inc. for the year ending Sep 29, 2023, stood at 1.09, indicating a moderate liquidity position.

Source:d18rn0p25nwr6d.cloudfront.net

Advantages

It assesses the ability to repay current liabilities based on the assets a company can quickly convert to cash. As such, it is a relatively more conservative liquidity ratio.

Its calculation omits inventories because converting inventories into cash could take too long. Elimination of inventories from its calculation helps management and other stakeholders to have a precise idea about the liquidity position of the concerned company.

This ratio is one of the easiest ratios to understand, and as such, it can be very helpful for people who do not have a deep understanding of accounting and finance.

It doesn’t provide any information about the timing of cash flows which can be a defining factor in the assessment of the liquidity position of a company.

Some of the assumptions of the QR are not realistic. For instance, it assumes that accounts receivable is readily available for collection, which is not always true.

It does not consider the situation that may arise in times of crisis. During the crisis, even the most easily saleable securities may find it difficult to trade in the market.

Conclusion

So, it can be seen that the quick ratio is a moderate conservative liquidity measure which is more conservative than the current ratio but less conservative than the cash ratio. This ratio helps the creditors in the assessment of the liquidity position of a company more accurately.

Recommended Articles

Update the detailed information about Lookup In Excel (Formula, Examples) on the Kientrucdochoi.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!