How to Merge Cells with Same Value in Excel (2024 Update)
In this article, we will show you how to merge cells with the same value in Excel. Simply follow the steps below.
Merge Cells with Same Value in Excel
To merge cells with the same value, we have outlined three approaches. Choose the one that best suits your needs.
Merge Cells with Same Value in Excel Using “Merge & Center” Feature
Follow the steps below to merge cells with the same value using the “Merge & Center” feature.
1. Select Rows to Merge and Choose Merge Options
Start by selecting the rows containing the cells with the same value you want to merge. Navigate to the “Home” tab in the Excel ribbon. Choose the “Merge & Center” option from the dropdown menu. Alternatively, you can select other merge options depending on your preference.
2. Confirm the Dialogue Box Upon Appearing
A dialogue box will appear, confirming that the upper-left value will be retained. Click “OK” to proceed with the merge.
3. View Merged Rows
Once you click “OK,” the selected rows with the same value will be merged, and the value will be centered within the merged cell.
Merge Cells with Same Value in Excel Using the “IF” Function
You can also merge cells with the same value in Excel using the ‘IF’ function. Here’s how it works:
1. Select Cell Range and Sort Data
Begin by selecting the cell range containing the data you want to merge based on identical values. Then, navigate to the “Data” tab and click on “Sort & Filter” to sort the data, ensuring rows with the same values are adjacent.
2. Create a New Column for Merged Values
Insert a new column next to your data and you can label it as “Merged Rows with Same Value.”
3. Input the “IF” Formula
In the first cell of the newly created column, enter the following formula: =IF(B5=B4, E4&”, “&C5, C5)
This formula checks if the value in cell B5 is the same as the value in the cell above (B4). If it is, it combines the values from the previous row (E4) and the current row (C5) separated by a comma and space. If not, it simply displays the value from the current row (C5).
4. Copy the Formula and View Merged Values
Double-click the fill handle (the small square at the bottom right corner of the cell with the formula) to copy the formula down the column. This will apply the formula to all rows in the column, merging cells with the same value accordingly.
Once the formula is applied, the cells in the new column will display the merged values of the same rows, making it easy to identify and manage duplicated data.
Merge Cells with Same Value in Excel Using “Consolidate” Command
Another method to merge cells with the same value is to use the ‘Consolidate’ feature. Follow the steps below.
1. Access the Consolidate Command
Navigate to the “Data” tab and locate the “Consolidate” command under the “Data Tools” category.
2. Choose Function and Reference
In the Consolidate dialog box, select the desired function for consolidation, such as SUM. Then, select the range of cells containing the data you want to merge.
3. Add References and Specify Options
Add the selected cell range to the “All references” field. Optionally, check the box next to “Top row” or “Left column” depending on your data layout and consolidation requirements.
4. Confirm and Merge Cells with the Same Value
Click “OK” to apply the consolidation. Excel will merge cells with the same value and perform the specified function, such as summing the values.
We hope you now better understand how to merge cells with the same value in Excel. If you enjoyed this article, you might also like our article on How to Find a Merged Cell in Excel or our article on How to Merge Cells in Excel and Keep All Data.
