Getting Started with Excel : From Basics to Data Cleaning

Introduction

Data has become one of the main things we interact with in our daily lives. For data to be analyzed and used in making meaningful decisions, it has to be organized and cleaned. One of the key tools which happens to be one of my favorites is Microsoft Excel.

Microsoft Excel is basically an application made up of spreadsheets where one can enter, organize, calculate and analyze data.

This article provides friendly beginner introduction to Excel starting with the basics up to preparing our data for analysis through data cleaning.

1. Excel Environment

Excel has different parts to it. They include;

Ribbon

It is a collection of commands and tools.

Rows

Is a collection of cells that run horizontally in a worksheet. They are numbered 1,2,3, etc.

Columns

Is a collection of cells that run vertically in a worksheet. They are lettered A,B,C etc.

Cells

A cell is an intersection of a row and column.

Formula Bar

An area where the contents of the cell are displayed. It is very useful when working with formulas because it allows one to see the exact formula used.

Quick Access Toolbar

Located at the top left corner of Excel. It provides frequently used commands which are ;
. Save
. Undo
. Redo

Worksheet

It is an individual page in an Excel Workbook where data is entered and organized. A worksheet can be renamed.
Example: If you have a workbook for analyzing employee details you could have
Sheet 1: Employee Details, Sheet 2: Cleaned Data, Sheet 3: Analysis

2.Data Entry

To enter data, you type whatever information you want displayed in the cells as shown below:

To prevent errors in our data entry we can use a feature called Data Validation which controls the type of data one can enter into a cell.
For Example: If the employee's salary is between 30000 - 120000, data validation prevents values less than 30000 and greater than 120000 from being entered.

3. Data Cleaning

Data Cleaning is a vital step when it comes to data analysis. Quality of the intended results depends very much on the quality of the data being analyzed. Data cleaning ensures we get quality data for analysis.

Common problems found in excel datasets include;

• Missing values

• Duplicate records

• Spelling errors

• Incorrect data types

• Inconsistent capitalization

• Extra spaces

Missing Values

A very easy way of identifying missing values in a dataset is by applying the filter feature to a column. If there is a missing value the (Blanks)option will be selected as shown below:

To correct that, one can:

• Enter the correct data needed.

• Deleting the record

• Leaving the cell as it is (blank) where there is no reliable value to enter.

Duplicate records

Duplicate record occur when the same information appears more than once in a dataset.

To remove duplicates, Excel provides a Remove Duplicates feature in the ribbon under the data Tab

Spelling errors

Spelling errors occur when words are entered incorrectly.

To correct that you can use the Find and Replace feature

Incorrect Data Types

Data type is basically the kind of value stored in a cell. This includes text, numbers, dates and percentages.
For example, if a the salary column is supposed to contain numbers, entering an employee's salary a text can pause a problem in data analysis

To correct that, one can select the affected cells and change the data type to the correct one.

Inconsistent Capitalization

Data my contain inconsistent capitalization as shown:

ndegwA
NDEGWA
Ndegwa

To correct the inconsistency, we use the PROPER function. In a cell type:
PROPER(A6) A6 being the cell with the inconsistency

On the same note the following functions also come in very handy;

• LOWER() function which converts text in the chosen cell to Lowercase

• UPPER() function which converts text in the chosen cell to Uppercase

Extra Spaces

Extra spaces can mainly occur when data is copied from another source or entered manually.
For Example:

"Ndegwa"
" Ndegwa'

To correct such, we use the TRIM 'function. In a cell type
TRIM(A6)`A6 being the cell with the issue.

4. Sorting and Filtering

These are very key functions in Excel.
Sorting changes the order in which records are displayed while Filtering displays only the records that meet a certain condition.
Below is raw data

Below is the sorted data

To sort:

1. Select inside the table you want to sort
2. Go to the Data tab
3. Select Sort
4. Select the desired order you want whether it is Smallest to Largest or Largest to smallest
5. Click Sort

Below is Raw data

Below is the Filtered data

To filter:

1. Select the column you want to filter
2. Go to the Data tab
3. Select Filter
4. Click on the dropdown menu and select the value or Condition you want displayed.

Conclusion

In conclusion, Microsoft Excel is such a powerful tool in data analysis. The magic that happens when different functions are entered in a cell and boom we have clean data, is one that needs it's own Magic show. Clean data, quality data for Analysis.