In today's world, decisions are not based on intuition or guesses. They are not based on what one thinks is the right thing. If you own a business, you would like to make a decision based on certain trends. If you are a teacher, you may want to see the progress of a student over a certain period. This and more requires one key ingredient; data. This data is collected using various methods, over a long period of time, and you are able to analyze it to get meaningful information from it. As the name says, the final output, informs you on a decision you should make, based on the trends that your data has shown.
The data, however, may not be clean. The data can contain duplicates, extra spaces on certain values, blank rows and columns, wrong data types on values, inconsistent cases, and many more. Getting insights from such data may not give you accurate insights. This is where data cleaning comes in handy.
You would get provided with a piece of data either in csv or xlsx format, and use Microsoft Excel to start the cleaning process. Let's dig deep into this.
Opening An Excel File
First things first, we would want to open our data in Microsoft Excel. We also need to make sure that we have a copy of our raw data somewhere safe so that we can come to it for any references. To safely get the data from the excel file open Microsoft Excel and click Blank workbook. This will open a new workbook with no information in it. If you had already opened it, then simply navigate to File and click Blank workbook to create a new workbook.
Getting Data From our Raw File
We now have our blank workbook. We now need to get our data into the workbook so that we can get started with the cleaning process. To do this, we will navigate to the Data tab on our top bar menu. We shall then click the Get Data button that has a cylinder+table icon. Once clicked, we get a few options. Microsoft provided a ton of options which you can choose to get your data from, depending on your source. You can get data from files e.g Excel Workbook, Text/CSV, XML, JSON, PDF, Folder, or SharePoint Folder. You can even get data from Database, Azure etc.
Once you identify you source e.g. CSV, simply select it. You will then get a window to navigate to where your raw data is located. Navigate to that location and select your data. Click the Open button or double click your data source. This will then open a preview of the data inside the selected file, showing the encoding format of the file, the delimiter, and the data inside the file. Click the Load button at the bottom of your preview window.
This will now open your raw data inside a table. Note that this is now single worksheet that you have of your raw data. In our worksheet, we may want to duplicate our data so that we can separate it from the raw worksheet, and now what we shall clean. If you did the above steps properly, you would notice a tab at the right part of your window saying Queries & Connections. What happened is that the Get Data created a live connection to the external data source using an underlying query. When we click on this query, we are taken to the Power Query Editor where we can now make certain changes. These changes are structured as steps which are recorded and can be undone. The changes do not touch your worksheet until you Close & Load.
Why Should One Use Power Query Over Normal Excel?
The Power Query Editor is preferred over traditional Excel the moment that your data cleaning would need automation, has grown in terms of volume, or needs to have steps reproducible. This enhances visibility of steps done on your data, which can also be repeated over and over again.
Identifying Data Types in Excel
An excel file can have as low as one column and as many columns as possible. Each column holds information, which has been stored in a certain data type. It is important that you ensure that each column has consistent data type depending on what the column is.
A data type is a classification that specifies which type of value a field or variable can hold. Excel recognizes the following data types in cells: Text, Number, Dates, Times, Logical (Boolean) values, and Errors.
When cleaning your data, it is therefore important to ensure that each column has a consistent data type across its cells. Cleaning up data is very simple. Simply select the column you would like to specify its data type. In the Home tab, in the Number menu, specify the data type. Repeat the steps for all the other columns.
Similar Values in a Column, Duplicates or Not?
When cleaning our data, we mentioned that we may have values that repeat themselves. At times, this might be erroneous, while some may have repetitive values, but values in the subsequent columns be different, which might change how we handle the duplicate values.
For example, if we were checking for duplicates on a column such as a product name, and the product had batch numbers, it would not be a surprise when we have repeating product names, but different batch numbers. This would suggest that we have variations of our product, due to the batch number. In such a case, we would not remove the duplicate product. However, if the batch number was the same across the same product, this would highly suggest that some of the records are duplicates, which would necessitate us to remove one record, and remain with a single non-duplicate record.
Formatting Data
We had already started formatting our data but had not gone through other methods of formatting our data. In the Power Query Editor, one can format columns as they would wish to. Some options provided are:
- lowercase -> Normalizes the selected column to have all its values as lowercase.
- UPPERCASE -> Normalizes the selected column to have its values as upper case.
- Capitalize Each Word -> Capitalizes the first letter of each word in each cell in the selected column.
- Trim -> Removes leading and trailing whitespaces in each cell of the selected column.
- Clean -> This will remove all non-printable characters in each cell of the selected column.
- Add Prefix -> This will add a text that you will specify and will be appended before each value in the cells of the column you have selected.
- Add Suffix -> This will add a text that you will specify and will be appended after each value in the cells of the column you have selected.
These are some of the ways you can manipulate your text values in Excel to have consistency.
Sorting and Filtering
Sorting
When cleaning your data, you may want it to appear in a certain order. You may want the first column to be sorted in ascending order. You may also want to combine sorting so that your data is arranged based on more than one column. This is where sorting comes in. Sorting does not add or remove any data. It just changes how they order themselves to allow one to see patterns, trends or even outliers. Some of the methods of sorting include:
- Alphabetical (A to Z or Z to A -> This sorting method is used for text columns e.g. product names, or employee names.
- Numerical (Smallest to Largest or Largest to Smallest) -> This is used for financial or quantity data and can be used for cases such as getting the lowest prices, finding the product with the highest quantity etc.
- Chronological (Oldest to Newest or Newest to Oldest -> This can used to track dates e.g. oldest student in a class, the nearest deadline etc.
- Multi-Level Sorting -> This kind of sorting allows one to sort data by more than one column at a time.
Filtering
You may want to see data based on specific criteria. This is made possible by Microsoft Excel using the magic of Filters. Filters allows you to focus on what you need, while temporarily hiding the rows that don't meet the criteria specified. You can always get the full data back by clearing your filter. Some of the filter methods include:
- Checkbox Filtering -> The filter allows you to just check or uncheck boxes. Rows with the unchecked values will be filtered out, remaining with what has been checked.
- Text Filtering -> The text filters allow one to search for values based on criteria such as Equals, Does Not Equal, Begins With, Ends With, Contains, and Does Not Contain. This will also provide you with a custom filter to create your own.
- Number Filtering -> This allows you to filter numbers according to criteria such as Equals, Does Not Equal, Greater Than, Greater Than Or Equal To, Less Than, Less Than Or Equal To, Between, Top 10, Above Average, and Below Average. You can also create custom filters just like with the text filters.
Functions
We have cleaned our data, we have sorted it, and we have also filtered. We would want to have a bit of insights now. To do this, we need to use the functions provided by Microsoft Excel.
Instead of rearranging, we will now use functions to analyze our data. In this article, we shall talk about statistical functions and date functions offered in excel to help in analyzing data.
Statistical Functions
Statistical functions allow you to get a single value (metric) from a range of data points. Examples include:
- MIN and MAX -> The two functions help you get the lowest and maximum values in a range respectively. You can get, for example, the highest salary given to your employees, or the lowest age among your staff.
- AVERAGE -> This function will add up all selected values and divide by the total count.
- MEDIAN -> Median is used to identify the exact middle number in a sorted list.
- MODE -> The Mode function allows you to find the value that appears the most in your dataset. ### Date Functions Date functions are important when used in a dataset, as they can help you extract and analyze your data by time periods. For example:
- YEAR -> The YEAR function extracts the four-digit year from a full date.
- MONTH -> The MONTH function pulls the month from a full date, returning a value from 1 (January) to 12 (December).
- DAY -> The DAY function pulls the day from a full date, returning a value ranging from 0 to 31.
Closing
This is just a scratch to the powerful world of Excel, and yet we can do much with the little we have covered. It is important that one understands all skills, as they build up as one continues exploring more and more functionalities of
Microsoft Excel.
Top comments (0)