Paramix Learning: Dealing With Horrible Data In Excel

Dealing with Horrible Data in Excel

By: Eddie Baki, 1.9.2015, 06:40

Very often data in Excel, especially data imported from other applications is endowed with misalignment and huge number of empty rows and columns. How can you fix that and render that data aligned and connected, without any gaps.

The biggest problem with horrible data is misalignment and those empty rows and columns. As long as these gaps exist, each cell is treated as a separate entity, unconnected to other related data.

Fixing the alignment problem is relatively easy. Select the data you wish to move and then drag it either up or down.

Getting rid of those empty columns and rows is a bit more intricate.

First go to the first cell (A1) in the next sheet and connect this cell to the A1 cell of the sheet with all the data.

Repeat process for every column of data, ignoring empty columns in the original data sheet. So in the second sheet you should have as many columns full of data as in the original sheet, albeit without the empty columns.

All the gaps in the original sheet are represented as zeros in the second sheet.

Now using Excel's columns filters you can filter out those zero rows and delete them.

End Result: Clean data.

Please use the feedback link below for comments and suggestions.

Contact   Feedback   Just Content   Top

Copyright 2016 Paramix e. K.    Contact   Legal Details