Introduction
Excel is a spreadsheet program that enables crunching of numbers. The powerful software not allows one to organize data but also facilitates cleaning, formatting and analysis through application of a myriad of formulas and functions. Akin to other Microsoft software packages, Excel’s interface is relatively easy to navigate, even for a beginner. The interface majorly comprises a program frame, menu tabs, a ribbon, a formula bar, a workspace, sheet tabs, and a status bar as shown in the figure below:
The workspace is made up cells, where each cell denotes a small rectangular box formed when a vertical column intersects with a horizontal row. The cells serve as data storage units where typing and editing of numbers, texts, formulas, and dates can be done. Each cell gets a unique name derived from a combination of its column letter and row number. For instance, cell A1 denotes the small box formed where column A meets row 1. Notably, a cell can hold diverse data forms such as numeric values, text labels, Boolean values, or formulas. It is also possible to format the appearance of a cell and its contents by adjusting elements such as size, number formats, fonts, and borders. A single spreadsheet consisting of columns and rows is called a worksheet. Usually, worksheets are located at the bottom at the Excel window. The complete Excel file that one creates, saves, and shares is referred to as a workbook. Markedly, a workbook can hold a single or multiple worksheets.
Data Cleaning
It is common for data entered into an Excel spreadsheet to be inaccurate, duplicated, incomplete, or inconsistent. Such data is generally referred to as dirty data. Dirty data arises from factors such as human error and poor collection processes. To render the data reliable for business analysis, there is a need to find and fix errors, inconsistencies, and duplicates. This process is referred to as data cleaning.
Steps in Data Cleaning
Removal of Duplicates
It is common for duplicates to occur in an Excel worksheet. Such occurrences can lead to erroneous analysis and inaccurate findings. As such, removal of duplicates is a key step in data cleaning. Removing duplicates is simple and entails selecting the entire data range, navigating to the data tab, and clicking remove duplicates as shown in the figure below:
Fixing Typos
Typos can result from misspellings, inclusion or exclusion of unwanted characters, presence of extra spaces in text, and a mix-up of upper- and lower-case characters in texts. Misspellings are corrected using the “Find and Replace” function. Here, one locates misspelt texts in each column and replaces it with the correct one. In a dataset where the word "finance" has been misspelt as "finanace", for example, the error can be fixed by relacing the erroneously spelt word with the correct one as shown in the figure below:
On the other hand, presence of extra spaces in texts is corrected using the TRIM(text) function. The function works by removing all leading and trailing spaces therefore leaving just a single space between words. In the dataset shown in my example below, the text “Paul” had 2 leading spaces that were corrected using the TRIM(text) function.
In an event where upper and lower-case characters are mixed up in texts, the error can be rectified by using UPPER(text), LOWER(text), and PROPER(text) functions accordingly. Notably, the UPPER(text) function converts text to upper case while the LOWER(text) and PROPER(text) functions convert text to lower and proper cases respectively. The example below shows use of the PROPER(text) function to correct "jAMeS" and yield the proper noun "James".
Creating New Columns
When cleaning data, it may be necessary to create new columns crucial for analysing the data. This can be done by either combining texts from different cells or extracting portions of the texts entered in an Excel sheet. Notably, combining texts from different cells applies the CONCAT(text) function. On the other hand, extracting text from the left, right, and middle is done using the LEFT(text), RIGHT(text), or MID(text) functions respectively.
The example below shows how a country code can be extracted from a phone number by applying the LEFT(text) function. Here, the first 4 characters from the left are extracted.
Fixing Data Formats
Erroneous data formats in an Excel sheet can be fixed by highlighting the relevant column, navigating to the “Home” menu and then selecting the “Number” tab in the ribbon. From this section one can then select the correct data format or type that best suits their column. For example, currency figures entered as numbers can be changed to currency by selecting the necessary column, navigating to the “Numbers” tab, and selecting “Currency” from the drop-down menu. The same approach can be used to rectify any other erroneously classed data to the correct data type or format.
Handling Outliers and Missing Values
An outlier is a figure that lies an abnormal distance from other figures in a dataset. Such data points should be removed or adjusted accordingly, for example by replacing them with the median. Similarly, missing values can be handled by either filling blank cells or removing entire incomplete rows. Where filling is the most preferred approach, the missing values are filled using either the mean, mode, or median. Because a dataset may contain outliers which affect the mean significantly but have little to no effect on mode and median, the mode and the median are the most preferred figures for filling blanks.
Conclusion
Excel is an extremely powerful tool for data cleaning. Ordinarily, cleaning entails removing duplicates, fixing typos and data formats, creating new columns, and handling outliers and missing values. Data cleaning improves accuracy of the data and subsequently determines the quality and reliability of analysis.
Top comments (0)