Fixing Rows and Columns in Excel

In the previous lecture, you saw some of the issues in raw data and understood the need for data cleaning. Let's learn how to correct some of these issues at the level of rows and columns.

Let’s summarise what you learnt in the form of a checklist. Make sure that you correctly identify these issues and resolve them before moving on to the next stage of data cleaning.

Checklist for fixing rows

• Delete summary rows: Total, subtotal rows

• Delete incorrect rows: Header rows, footer rows

• Delete extra rows: Column number, indicators, blank rows, page number

Checklist for fixing columns

• Merge columns for creating unique identifiers if needed, e.g., merge state and city details into the full address.
• Split columns for more data. Split the address to get state and city details so that you can analyse each separately.

• Add missing column names.

• Rename columns consistently, with abbreviations and encoded columns.

• Delete unnecessary columns.

• Align misaligned columns; the data set might have shifted columns.

Save this checklist for future reference:

In the next segment, we will talk about another very important concept: missing values.