Getting Started with Excel for Data Analytics: From Basics to Data Cleaning

Introduction

• Microsoft Excel is a spreadsheet program developed by Microsoft used to collect, organize, analyze and visualize data. It organizes data into rows and columns, allowing for complex computations and visual representations.
• It is a powerful and versatile tool for data analysis, while effective data cleaning ensures accuracy providing a strong foundation for meaningful insights and informed decision-making.

The Excel Interface

• An excel file is known as a workbook.
• A workbook is made up of worksheets that can be renamed as desired for easier identification.
• An excel worksheet is made up of rows and columns. The rows are horizontal lines of cells numbered 1,2,3... while the columns are vertical lines of cells labeled A,B,C...
• A cell is an intersection of a row and a column where you enter data such as A1
• A ribbon is the toolbar across the top that contains all commands organized into tabs that is: Home, Insert, Draw, Formulas, Page Layout and Data.
• Once you open an excel workbook containing data, it is advisable to open a new worksheet and copy paste the existing data which you will use in data cleaning so that you do not alter the original data.
• Below is an example of an excel workbook:

Data Types

• Data types are the different forms of data that can be entered in an excel sheet. They comprise of:
  • Text -words e.g Nairobi
  • Numbers - used for counting and computations e.g 17, 40, 56
  • Date - e.g 20/08/2004
  • Currency - money amounts e.g $500
  • Formula - a command that performs calculations e.g =A1+B1

Data Formatting

• It changes how cell values appear without modifying the underlying raw data, it makes data look more clear and well organized.

Basic Types of Formatting

• Font- it changes the color, type and size of a text.
• Bold(B)/Italics(I)/Underline(U)- changes the style of a text. Example John➡️John, John➡️John and John➡️John.
• Borders- adds lines on a cell.
• Horizontal Alignment- positions data to the left, center or right
• Vertical Alignment- positions data to the top, middle or bottom
• Wrap text- makes long text to appear on multiple lines in the same cell

Number Formatting

• It controls how numbers look in a cell depending on the format applied. -Go to the Home tab, in the Number group, choose a format from the drop-down list. Examples:
  • Number- 1234.567➡️1,234.56
  • Currency- 1234.57➡️$1,234.56, Ksh1,234.56
  • Percentage- 0.25➡️25%
  • Fraction- 0.25➡️1/4
  • Date- 2021-12-01

Data Cleaning Using Text Functions

• It refers to removing incorrect, unwanted, or messy data so that it is accurate and easy to use by use of various text functions

1. Upper()- converts text to upper case
2. Lower()- converts text to lower case
3. Proper()- capitalizes first letter of each word
4. Left()- extracts leftmost letters in a word
5. Right()- extracts rightmost letters in a word
6. Trim()- removes extra spaces in a word
7. Len()- shows the number of letters in a text
8. Mid()- displays the characters from the middle in a word

Case Example

• Below is an excel worksheet containing data that needs to be cleaned:

Cleaning the Patient ID column

• Some data consists of numbers while others consist of numbers and texts. The proper data type should be text since patient ID is an identifier and although it is made up of values, there is no computation that can be done.
• Select the column, press ctrl+h for the find and replace tool, replace "PM-" with " ". This will omit the unwanted text as shown.

Cleaning the first and last name column

• Use the Proper() function discussed earlier to ensure that text in the first and last name column are written in proper case. ⬇️

Cleaning the gender column

• The gender column consists of texts that have been misspelled, the goal is to ensure that all the cells are either "Male" or "Female".
• Use ctrl+h to find all misspelled texts and replace them. ⬇️

Cleaning the bill amount column

• The column consists of values in both text and number data types as well as values in different currencies.
• The words formatted on the left have text data type while those formatted on the right have number data type.
• First delete letters appearing before the numbers then use the currency format and select "Ksh" to ensure the currency is in Kenyan Shilling.

Creating an email column

• Assuming you want to add a new column containing the patients email addresses, you can use Concat() function to join the first and last names, then, use lower() and concat() functions together to create an email address as shown below:

Sorting and Filtering

• Sorting arranges data in a specific order. This include:
  • Text Sorting (A to Z or Z to A)
  • Number Sorting (Smallest to Largest or vice versa)
  • Date Sorting (Oldest to Newest or vice versa)
• Filtering shows only the records that meet specific conditions while the other records are temporarily hidden. Common types of filter are:
  • Text Filter(Contains, Does Not Contain, Begins With)
  • Number Filter(Greater Than, Less Than, Between)
  • Date Filter(Before, After, This Month, Last Year)
  • Color Filter(Filters by cell or font color)

Functions in Excel

• An excel function is a preset formula that performs specific calculations.
• Sum()- adds numeric values in a range.
• Average()- calculates the mean of values.
• Max()- finds largest value.
• Min()- finds lowest value.
• Count()- counts how many numbers are in a range of selected cells
• CountA()- counts the number of cells that are not empty both numbers and texts.
• If()- checks whether a condition is true or false and returns a specified result.
• CountIf()- counts the number of cells that meet a specific condition.
• SumIf()- adds values that meet a specific condition.

Conclusion

• This article provides an introduction to the basics of excel, but there is so much more this powerful tool can do. Mastering excel is a continuous journey that requires practice, consistency, and a willingness to learn. Start your journey with excel today to turn data into insights, and insights into impact.