Filtering Data in Excel

In the previous segment, you learnt how to fix invalid values. Now, let’s talk about how to filter data.

After you have fixed the missing values, standardised the existing values, and fixed the invalid values, you get to the last stage of data cleaning. Though you have a largely accurate data set by now, you might not need the entire data set for your analysis. It is important to understand what you need to infer from the data and then choose the relevant parts of the data set for your analysis. Thus, you need to filter the data to get what you need for your analysis.

Let’s now watch Anand as he takes us through the various steps of data filtering.

Let’s summarise what you learnt about filtering data. You could use this as a checklist for future data cleaning exercises.

• Deduplicate data: Remove identical rows, and remove rows where some columns are identical.
• Filter rows: Filter by segment and date period to get only the rows relevant to the analysis.
• Filter columns: Pick the columns that are relevant to the analysis.
• Aggregate data: Group the data by the required keys and aggregate the rest.