Beginner Excel Basics and Data Cleaning

Introduction to Excel.

Excel is the most basic tool used for comparison, analysing, cleaning and reporting.
It is one of the most important tools used widely especially during data cleaning. Understanding the basic features it has makes data cleaning and transformation easy. We have different features in excel that makes our work easier when aligning, sorting, filtering and deleting what's not needed.
We also do have formulas that makes calculations or comparisons between columns, rows and even worksheets easier.
Basically excel easens work that could take longer.

Features in Excel and its importance.

These are the basic Tabs that are visible when first opening your excel and each have their own importance since they complemnt each other.

• File Tab- This is basically for Saving, Exporting, Sharing your data and also for checking your Excel account.
• Home Tab- This is a tab that contains mostly used features. They contain: Text features, Alignment,condition formatting, cell styles, sort and filtering, delete and insert feature.
• Insert Tab- This is for inserting tables,forms and charts.
• Page Layout Tab- This is for designing the page layout for your excel.
• Formulas Tab- This is a tab that contains different formulas.
• Data Tab- This is a Tab that contains feature that is able to assist with updating, sorting and filtering data.
• View Tab- This Tab assist with the view of the excel sheet data, Most importantly Freeze panes(This helps when scrolling down there is no confusion of the column or row and what it represents)

Each cell in a column and row has a unique identifier(A1,A2,B1,B2..)

Data Cleaning.

N/B:

• With Data cleaning , first identify the inconsistencies in your data and change them so that they all align as expected for each rows and column.

• In a large dataset we have both texts and numerical values and with both we have ways of cleaning and transforming them inorder to align and be able to come up with good, realiable insights from the dashboard created after the cleaning and transforming.

Cleaning Texts Values.

Names

To identify the inconsistencies in a specific column:

1. Highlight the column

2. On home tab, look for filter button.

3. After filtering , you will be able to see the incosistencies. In our above example: We are able to identify there are different cases for the names.

4. After Indentifying the issue, it is easier to insert another column, and use a function to be able to align all the cases to PROPER

=PROPER(B2)
-Proper is the function.
-B2 is the scpecific cell being referenced.
-After all the cases are aligned to PROPER then you can copy that specific column and paste special then choose paste values .

• Do that for the Names colunm that requires it.(First and last name)

• For Other Texts columns we proceed with the same , Highlighting the column and filtering to be able to see what inconsistencies that column has.

To align the texts in the column use:
CTRL+H to identify and replace the text values with the expected values in that column to avoid many descriptions representing a specific group/dept.

Cleaning Numerical Values.

DATES.

• After identifying the mixup in your dates :
• Highlight the column
• Right click and choose format cells. This will help you choose the format of the cell you expect.

CURRENCIES
-For some numerical values that appears as text e.g: Currencies, could be because the currency was input manually as a text.
To resolve this:
-After highlighting the column, CTRL+H, Then identify the values and replace with the expected value.
-In our case we are removing the currency input manually*KES*
We identify it then replace we leave it as blank.

• Another way to work around it when you want the currency symbol to be visible is by higlighting the column, right click, choose format cell and define the curreny needed.

Using Conditional formatting Feature.

• We use conditional formatting to identify alot in a data set:
• Highlight cell rules: Duplicates, data less than, between, Greater than, Less than..
• Top/ Bottom Rules
• Colour cells.

Identifying Duplicates

The color code will identify he duplicates :
-To deal with duplicates mainly depends with the whole data , e.g: If its a data that can be deleted then proceed to delete, if its a specific cell and instead of deleting the whole row, you can delete the specific cell and remain with the rest of the data.

Generating Automatic emails .

We use =LOWER(CONCAT(B2,B10,"@luxdevhq.com")) .
This formula will generate automatic email in lowercase given that the first and last name for the specific people are defined.

Aggregate Functions.

-These are the functions that are used for calculations either to get the total, mean, mode..

• We use these formulas:

=SUM()- This will calculate the total of a specific column. In the bracket define the cell range you want it to calculate the total.

=PRODUCT()- This will help to calculate the multiplication of the cells defined in the cell range.

=MEDIAN()/ =AVERAGE()- This helps in calculating the average of the cells defined.

=MODE()- This helps to calculate the most visible value in the cell range.

=MIN()- This identifies the least value in the cell range.

=MAX()- This identifies the largest value in the cell range.

Statistical Functions.

=COUNT()-This counts the numerical values in a cell range.
=COUNTA()-This counts non numerical values in a cell range.
=COUNTBLANK()- This counts blank cells in the cell range.

# Conditional Calculations.

=COUNTIF(E2:E870,"Females")- This formula counts cells that only meet one condition. In our case our condition is females. So from the whole cell range it will count the female only.
=COUNTIFS(E2:E870,"Females",F2:F870,"30"- This formula counts cells that meets more than one condition. In our example here, the first condition is gender (females)and the 2nd condition is age(30).
=SUMIF(E2:E870,"Females",F2:F870- This Adds values that meet one condition. In Our case we are adding the salary of Females
SUMIFS(E2:E870,I2:I870,"Females,"30"- This Adds values that meet multiple conditions. In our Case we want the sum of (Females) who's age is (30). Here we are meeting more than one condition.