Microsoft Excel is one of the most important computer applications because of the key role it plays in many sectors. It is used in data entry, record keeping, project management, business intelligence and data analysis.
The data stored in Excel can sometimes be messy due to duplicate values, missing values, inconsistent text, incorrect data types for example having a text value in a column with numeric values etc, which can affect the accuracy of analysis. In data analytics, before the data can be analyzed, it needs to be cleaned and well structured.
In this article we will explore the basics of Excel and also practical data-cleaning techniques. We will use the Cafe Sales Dataset to demonstrate how to work with data in Excel and cleaning the data to prepare it for analysis.
Download the dataset from the link above.
Understanding The Dataset
Let us explore the dataset first before diving into cleaning. Exploring helps us understand what exactly we are working with.
The dataset has 8 columns in total and 10,000 rows of synthetic data representing sales transactions in a cafe.
| Column Name | Data Type |
|---|---|
| Transaction ID | Text |
| Item | Text |
| Quantity | Numeric |
| Price Per Unit | Numeric |
| Total Spent | Numeric |
| Payment Method | Text |
| Location | Text |
| Transaction Date | Date |
Below is a screenshot of the dataset when opened in Excel:
Working With Excel
Before we dive into cleaning let's look into some Excel fundamentals that will help us work with this dataset.
Adjusting the column width
You will note that when you open the dataset not all cell values are fully visible. Some values, for example values in column Transaction Date, are represented by # because the values are longer than the available column width. Adjusting the column width makes all values visible. Start by selecting all values in our dataset with CTRL + A. Then in the Home Tab, cell group, select Format and choose AutoFit Column Width.Excel automatically adjusts the width of each column based on the contents of the cells, making the values fully visible.
Formatting the Header
We can make our header row different from the other rows by making it bold, centering the text, and adding a background color. The header row is the row containing the column names. Select the first row with column names, in the Home tab under Font group click B. Alternatively you can use CTRL + B to apply the bold.
While still selecting the header row, in the Home tab under Alignment select Center.
We can also add a background color using the Fill Color option in the Font group. This is how the header row looks after formatting:
Adding Freeze Panes
As you scroll down the dataset you will note that you lose sight of the header row. This makes it hard to remember which column is which. That is where freeze pane comes in. What Freeze Panes does is that it ensures the header row is always at the top of the dataset.
To freeze our header row first select the whole dataset, then under Window group in View tab select Freeze Panes and choose Freeze Top Row. Now as you scroll down the dataset, the header row always remains at the top.
Also note that Freeze Panes can also be applied on the first column.
Sorting Data
Sorting means arranging data in a specific order. In Excel, text data can be sorted alphabetically, from A to Z or from Z to A and numeric data can be sorted from largest to smallest or from smallest to largest. Date data can also be sorted chronologically, from the earliest date to the latest date or vice versa.
For example in our dataset we can sort Price Per Unit column. To do this, select any cell in the Price Per Unit column, go to the Data tab, and select Sort Smallest to Largest icon in the Sort & Filter group. This will arrange the values from the lowest price to the highest price while keeping the other values in each row together.
Sorting makes it easier to examine the data and identify values that may require further investigation during the cleaning process.
Filtering
Filtering allows us to display only the records that meet specific conditions while temporarily hiding the other records.
For example, in our dataset, we can filter the Payment Method column to display only transactions where customers paid using Cash. To do this, select any cell in the dataset, go to the Data tab, and select Filter from the Sort & Filter group. A dropdown arrow will appear in each column header. Click the dropdown arrow in the Payment Method column, deselect Select All, select Cash, and click OK.
Excel will now display only the transactions where the payment method is Cash and the other records will be hidden.
Basic Formulas and Functions
Excel formulas and functions allow us to perform calculations and extract useful information from a dataset.
For example, we can calculate the total quantity of items sold in our dataset using the SUM function. Since the Quantity column contains the number of items purchased in each transaction, we can use the following formula:
=SUM(C2:C10001)
The AVERAGE function can be used to calculate the average quantity of items purchased per transaction:
=AVERAGE(C2:C10001)
Other useful functions when exploring a dataset include COUNT, MIN, and MAX. For example, COUNT can be used to determine the number of numeric values in a column, while MIN and MAX can help identify the smallest and largest values.
Data Cleaning
Having gone through some of Excel fundamentals, we can now move on to cleaning the dataset. Data cleaning is the process of identifying and correcting inaccurate, incomplete, inconsistent, or irrelevant data to improve the quality of a dataset.
Removing Duplicate Records
The first step in cleaning our dataset is to remove any duplicate records. In our dataset, a duplicate record would be a cafe sale that has the same value in all fields.
We can do this by selecting a cell in the dataset, then in the Data tab under the Data Tools group click Remove Duplicates. A dialog box appears showing all the columns in the dataset. Since we are looking for records that are identical across all fields, ensure all the columns are selected and click OK.
Our dataset does not have any duplicate rows.
Missing Values
To identify missing values, we will make use of Filter. Select any cell in the dataset, in the Home tab, under the Editing group, select the Sort&Filter dropdown and click Filter. You will see a filter dropdown appear in each column header. When clicked, this filter dropdown shows the unique values contained in that column. To identify missing values, we can look for the (Blanks) option in the list.
For example, we can check the Item column for missing values. Click the filter dropdown in the Item column. We can now see all the unique values in the column, (Blanks) included. This shows that there are missing values in the Item column.
To handle such missing values, we first check if we can determine the missing value from the other values in the row. Since we cannot do that, we will use Find & Replace to change them to Unknown. Select the Item column then press CTRL + H. A dialog appears. Leave the Find field empty and write Unknown in the Replace field then click Replace All.
A total of 333 missing values in the Item column were changed to Unknown
Correcting Data Types
Another important part of data cleaning is ensuring that each column contains the appropriate type of data. The data type determines how Excel interprets and works with the values.
For example in our dataset, Quantity, Price Per Unit, and Total Spent should contain numeric values because they represent quantities and monetary amounts. Transaction Date should contain date values, while columns such as Item, Payment Method, and Location contain text.
A good example of a column with data-type problems is in the Price Per Unit column. It should contain numeric values but we have identified it has values UNKNOWN and ERROR.
Perfect way of cleaning this is by using the Find & Replace we used earlier on. Find cells with UNKNOWN then replace with blanks. Do the same for ERROR.
The Quantity column also contains the text values UNKNOWN and ERROR, which can be replaced with blanks using the same approach.
Conclusion
In this article, we explored some of the Excel fundamentals that are useful when working with datasets, including adjusting column widths, formatting headers, freezing panes, sorting, filtering, and using basic formulas and functions.
We then applied these concepts to clean a cafe sales dataset containing 10,000 records. We removed duplicate records, identified missing values using filters, and handled missing and inconsistent values such as UNKNOWN and ERROR.
Data cleaning is an important step before performing analysis because the quality of the data directly affects the quality of the insights gotten.
