In this session, you learnt about custom sort and Advanced filters. You also explored the various text, statistical, and logical functions that Excel offers. After applying these functions to the dataset, we retained most of the results and arrived at a cleaned dataset at the end.
Let’s revise what you have learnt in this session.
In this session, we have learnt the following:
Data Cleaning
When you import data from an external source, such as a database, text file, or web page, the format and type of data may not be ideal. Usually, the data needs cleaning before analysis. Excel provides various functions to format your data precisely, and some tasks can even be handled automatically.
Data Dictionary
A data dictionary describes the columns of a table using common properties like name, definition, and data type. It helps administrators and analysts understand complex datasets without checking every column individually. Essentially, it is a summary of metadata that communicates the structure of your database.
Advanced Filters
Advanced filters are useful for extracting data that meets multiple complex criteria. You can find exact matches, differences between columns, filter rows that match another list, and even consider case sensitivity (uppercase vs lowercase) in your search.
Sorting
Sorting is essential for organising data meaningfully. Excel allows you to sort data alphabetically, numerically, or by date in ascending or descending order. Multi-level sorting enables sorting by one criterion and then by another, making it easier to spot patterns, inconsistencies, and key insights.
Text Functions
Excel offers functions to transform and clean up text efficiently. The TEXT function, for example, changes the appearance of numbers using format codes, allowing for better readability and the combination of numbers with text or symbols.
Statistical Functions
Statistical functions summarise your data’s properties, including central tendency, variability, distribution traits, and the ability to detect outliers.
Conditional Formatting
Conditional formatting visually highlights patterns, trends, and exceptions in large datasets. You can apply formatting such as colours, icons, or data bars based on cell values to make data easier to interpret.
The demonstration file used in this session is shared below.