How to Display Multiple Records into a Single Record in Excel Formula

Sometimes, you may have a data table that contains multiple records for the same entity, such as an employee, a product, or a customer. For example, you may have a table that shows the sales of different products in different regions, and you want to display the total sales of each product in one row. Or, you may have a table that shows the wages and taxes of different employees in different states, and you want to display the wage and tax information of each employee in one row.

In this article, we will show you how to display multiple records into a single record in Excel formula, using some of the built-in functions and features of Excel. We will also explain the basic theory behind each method, and provide a detailed example with real numbers to illustrate the steps and results.

Method 1: Using the CONCATENATE or CONCAT Function

One of the simplest ways to display multiple records into a single record in Excel formula is to use the CONCATENATE or CONCAT function. These functions allow you to join text strings from multiple cells or ranges into one cell. The syntax of these functions is:

=CONCATENATE (text1, text2, text3, …) =CONCAT (text1, text2, text3, …)

where text1, text2, text3, … are the text strings or references to the cells or ranges that you want to join. You can use up to 255 arguments for the CONCATENATE function, and up to 253 arguments for the CONCAT function. The CONCAT function is available in Excel 2019 and later versions, and it automatically ignores empty cells and ranges.

For example, suppose you have a table that shows the wages and taxes of different employees in different states, as shown below:

If you want to display the wage and tax information of each employee in one row, you can use the following formula in cell F2:

=CONCATENATE (B2, ” “, C2, ” “, D2, ” “, B3, ” “, C3, ” “, D3)

This will return the following result in cell F2:

CA 1900 10 AZ 2345 20

You can then copy and paste the formula to the other cells in column F, and adjust the references accordingly. Alternatively, you can use the CONCAT function instead of the CONCATENATE function, which will give you the same result.

The advantage of this method is that it is simple and easy to use. However, the disadvantage is that it does not separate the values by commas or other delimiters, which may make the result hard to read or analyze. Also, if you have more than two records for the same entity, you will need to add more arguments to the function, which may make the formula long and complex.

Method 2: Using the TEXTJOIN Function

Another way to display multiple records into a single record in Excel formula is to use the TEXTJOIN function. This function allows you to join text strings from multiple cells or ranges into one cell, and separate them by a delimiter of your choice. The syntax of this function is:

=TEXTJOIN (delimiter, ignore_empty, text1, text2, text3, …)

where delimiter is the text string that you want to use to separate the values, ignore_empty is a logical value that specifies whether to ignore empty cells or ranges, and text1, text2, text3, … are the text strings or references to the cells or ranges that you want to join. You can use up to 252 arguments for the TEXTJOIN function. The TEXTJOIN function is available in Excel 2019 and later versions, and in Excel Online.

For example, suppose you have the same table as before, and you want to display the wage and tax information of each employee in one row, separated by commas. You can use the following formula in cell F2:

=TEXTJOIN (“,”, TRUE, B2:D3)

This will return the following result in cell F2:

CA, 1900, 10, AZ, 2345, 20

You can then copy and paste the formula to the other cells in column F, and adjust the references accordingly.

The advantage of this method is that it allows you to use a delimiter of your choice, which may make the result easier to read or analyze. Also, it automatically ignores empty cells or ranges, which may save you some time and effort. However, the disadvantage is that it requires Excel 2019 or later versions, or Excel Online, which may not be available for some users.

Method 3: Using the Power Query Feature

Another way to display multiple records into a single record in Excel formula is to use the Power Query feature. This feature allows you to connect to, transform, and analyze data from various sources, such as Excel tables, text files, web pages, databases, and more. You can use the Power Query feature to transpose your data table, which means to switch the rows and columns of your table. The Power Query feature is available in Excel 2016 and later versions, and in Excel 2010 and 2013 as an add-in.

For example, suppose you have the same table as before, and you want to display the wage and tax information of each employee in one row. You can use the following steps to transpose your table using the Power Query feature:

1. Select any cell in your table, and go to the Data tab on the ribbon. Click on the From Table/Range button in the Get & Transform Data group. This will open the Power Query Editor window, where you can see your table as a query.
2. In the Power Query Editor window, go to the Transform tab on the ribbon. Click on the Transpose button in the Any Column group. This will switch the rows and columns of your table, as shown below:

3. Click on the Close & Load button on the Home tab on the ribbon. This will close the Power Query Editor window, and load the transposed table to a new worksheet in your workbook, as shown below:

You can then copy and paste the values from the transposed table to your original worksheet, and delete the extra columns and rows.

The advantage of this method is that it allows you to transpose your table without using any formulas, which may make the process faster and easier. Also, it gives you more flexibility and control over your data transformation, as you can use various options and commands in the Power Query Editor window. However, the disadvantage is that it requires Excel 2016 or later versions, or Excel 2010 and 2013 with an add-in, which may not be available for some users. Also, it may create an extra worksheet in your workbook, which may take up some space and memory.