How to Remove Consolidation in Excel (2 Handy Methods)

Consolidation, merging or combining data in a cell from different rows and columns is a common phenomenon. But, the hard part is to remove this consolidation in Excel and separate the data into multiple columns and rows. This article demonstrates how to remove consolidation in Excel with a few handy methods.

Download Practice Workbook

You can download the practice workbook from the link below.

2 Methods to Remove Consolidation in Excel

In many cases, you may need to remove the consolidated data and put the data into different cells of a column or row. Also, the data may be consolidated with the help of a Delimiter. At this point, I will show you two methods to remove consolidation where the data is consolidated by a comma, ‘,’.

1. Applying FILTERXML and SUBSTITUTE Functions in Excel

Let’s assume you have a dataset where there is a list of Countries and their respective cities consolidated like the screenshot below. At this point, you want to remove the consolidation. In this case, I will show you some quick steps of how to do so with the help of FILTERXML and SUBSTITUTE functions.

Steps:

• First, select the cell where you want to keep the split data from the existing consolidated data. In this case, it is cell C8 which is the first cell of the new column USA.
• Next, insert the following formula into cell C8 or your selected cell. Eventually, this will result in a Vertical Array like the screenshot below.

In this case, cell C5 is the cell for Cities of the country USA.

Also, we used the FILTERXML function which has two arguments xml and xpath respectively. Moreover, we used the SUBSTITUTE function which has arguments text,old_text, new_text, and instance_num respectively.

• Finally, drag the Fill Handle to the rest of the cells of the respective row.

Note: The FILTERXML function is only available in Microsoft 365 for now.

Read More: How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)

Similar Readings

• Data Validation and Consolidation in Excel (2 Examples)
• How to Consolidate Data in Excel from Multiple Workbooks (2 Methods)
• Automate Consolidation in Excel (with Easy Steps)
• How to Consolidate Data from Multiple Ranges in Excel (2 Easy Ways)

2. Using Text to Column Wizard to Remove Consolidation into Multiple Columns

Now, suppose that you have a dataset of Countries with their respective Cities consolidated with the Delimiter comma. At this point, you want to remove the consolidation and keep the data in multiple columns. In this case, you can follow the steps below to do so.

Steps:

• First, select the range you want to remove consolidation from. In this case, it is range C5:C7.
• Then, go to the Data tab.
• After that, select Text to Column from Data Tools.

• At this point, a box will appear as shown in the below screenshot.
• Consequently, from this box, select Delimited > Next.

•  Next, select Comma from the Delimiters options.
•  Again, press Next.

• Now, in Destination insert your desired destination. In this case, it is $D$5.
• Finally, click on Finish.

• Lastly, you will get your desired output like the screenshot below.

Read More: How to Consolidate Data from Multiple Columns in Excel (7 Easy Ways)

How to Remove Consolidation References Using VBA Code

Now, let’s assume you have a worksheet where you have performed consolidation using the Data Tool Consolidate. So, you have a lot of consolidated references in the Consolidate tool box like the screenshot below. If you want to remove the references using VBA code, you can follow the steps below.

Steps:

• First, press ALT + F11 to open the VBA window.
• Now, select Sheet 5 or the sheet you are working in and Right-Click on it.
• Next, sequentially select Insert > Module.

• At this point, copy the following code and paste it in the blank box.

'This Code will Remove Consolidation References
Sub RemoveConsolidationRef()
    Dim ACS As Worksheet
    Dim J As Long
    Dim P As String
    Dim ces As Variant
    Set ACS = ActiveSheet
    With ACS
    If Not IsEmpty(.ConsolidationSources) Then
    ces = .ConsolidationSources
    For J = 1 To UBound(ces)
    P = P & "#" & J & ":" & ces(J) & vbCr
    Next J
    Select Case MsgBox("Consolidation Reference found" _
    & vbCr & vbCr & P & vbCr & "Do You Want to Delete?", _
    vbYesNo Or vbQuestion, "Worksheet " & .Name)
    Case vbYes
    On Error Resume Next
    .Cells.Consolidate Sources:=Array(vbNullString), _
    Function:=xlProduct, TopRow:=False, LeftColumn:=False, CreateLinks:=False
    End Select
    Else
    MsgBox "Nothing Found", vbOKOnly Or vbInformation, "Worksheet " & .Name
    End If
    End With
End Sub

Here, in this code, we declared some variables first. Then, we used an If Not command to determine if the Consolidation Sources is empty or not. If not, we created a MsgBox to ensure if we want to remove the consolidation references or not. Finally, we deleted all the consolidation sources. And, if the Consolidation Sources are empty, then the code will return ‘No consolidation references found’.

• Lastly, select Yes and all your consolidation references will be deleted.

Read More: [Fixed]: Consolidation Reference Is Not Valid in Excel (with Quick Fix)

Conclusion

Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.

Related Articles

• How to Consolidate Two Sheets into One in Excel (3 Useful Methods)
• Consolidate Data by Category in Excel (2 Suitable Examples)
• How to Consolidate Multiple Worksheets into One PivotTable (2 Methods)
• Consolidate Information in Excel (2 Simple Ways)
• How to Consolidate Data from Multiple Workbooks in a Single Worksheet
• Create a Linked Consolidation in Excel (2 Useful Methods)
• How to Build a Static Consolidation in Excel (2 Suitable Examples)