How to Remove Consolidation in Excel (2 Handy Methods)

Method 1 – Applying the FILTERXML and SUBSTITUTE Functions in Excel

We have a dataset where there is a list of Countries and their respective cities consolidated like the screenshot below. We want to remove the consolidation.

how to remove consolidation in excel dataset

Steps:

  • 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.
  • Insert the following formula into cell C8 or your selected cell.
=FILTERXML("<t><s>"&SUBSTITUTE(C5,",","</s><s>")&"</s></t>","//s")

Cell C5 is the cell for Cities of the country, USA. We used the FILTERXML function which has two arguments xml and xpath, respectively. We used the SUBSTITUTE function which has arguments text,old_text, new_text, and instance_num, respectively.

Applying FILTERXML and SUBSTITUTE

  • Drag the Fill Handle to the rest of the cells of the respective row.

Applying FILTERXML and SUBSTITUTE

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


Method 2 – Using the Text to Column Wizard to Remove Consolidation into Multiple Columns

We have a dataset of Countries with their respective Cities consolidated with the Delimiter comma. We want to remove the consolidation and keep the data in multiple columns.dataset

Steps:

  • Select the range you want to remove consolidation from. In this case, it is range C5:C7.
  • Go to the Data tab.
  • Select Text to Column from Data Tools.

Using Text to Column Wizard to Remove Consolidation

  • A box will appear as shown in the below screenshot.
  • Select Delimited and hit Next.

Using Text to Column Wizard to Remove Consolidation

  • Select Comma from the Delimiters options.
  • Press Next.

Using Text to Column Wizard

  • In Destination, insert your desired destination. In this case, it is $D$5.
  • Click on Finish.

Using Text to Column Wizard to Remove Consolidation

  • You will get your desired output like the screenshot below.

Using Text to Column Wizard


How to Remove Consolidation References Using VBA Code

We have a worksheet where we have performed consolidation using the Consolidate tool.

Remove Consolidation References Using VBA Code

Steps:

  • Press Alt + F11 to open the VBA window.
  • Select Sheet 5 or the sheet you are working on, and right-click on it.
  • Select Insert and choose Module.

Remove Consolidation References Using VBA Code

  • Copy the following code and paste it into 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

Remove Consolidation References Using VBA Code

We used an If Not command to determine if the Consolidation Sources were empty or not. If not, we created a MsgBox to determine if we want to remove the consolidation references or not. We deleted all the consolidation sources. And, if the Consolidation Sources are empty, then the code will return ‘No consolidation references found’.

  • Run the code.
  • Select Yes and all your consolidation references will be deleted.

Remove Consolidation References Using VBA Code


Download the Practice Workbook


<< Go Back To Consolidation in Excel | Merge Sheets in Excel | Merge in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sowmik Chowdhury
Sowmik Chowdhury

Sowmik Chowdhuri, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a crucial Excel & VBA Content Developer at ExcelDemy. His profound passion for research and innovation seamlessly aligns with his unwavering dedication to Excel. In this role, Sowmik not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, highlighting his steadfast commitment to consistently deliver content of exceptional quality and value. Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo