GETTING STARTED WITH EXCEL FOR DATA ANALYTICS: FROM BASICS TO DATA CLEANING.

INTRODUCTION.

Many times, I thought Excel was just a simple tool that could only work on simple tasks. After interacting with it for the past one week, I have come to realize how powerful it is.
So, what exactly is Excel? Excel is a spreadsheet program developed by Microsoft that helps you organize, manage and analyze data.

Excel Use Cases.

-Used in data entry
-Data cleaning
-Analyzing data
-Data visualization

Overview.

There are many tools and commands displayed on Excel's interface.
Ribbon- It's a toolbar that contains all the commands used in excel.
For example, Home, Formulas, insert, Review, View, Page layout, Draw.

Terminologies used in Excel.

-Cell- A basic unit of a worksheet where you can enter or display data.
Each cell has an address that identifies each cell.

-Formula bar- Area above the worksheet where you can view, enter and display formulas.

-Rows-This are horizotal lines of cells that run from left to right.

-Columns- This are vertical lines of cells that run from top to bottom in a worksheet.

-Name box- It contains the address the selected cell.

Data Types.

-Numbers- Values that can be used in calculations ie, 2,5,7,54
-Texts-refers to letters, words, sentences or combination of characters

used to describe
-Date- this are values that reprent a specific day, month or year ie. 30/04/2026.
To access data types, highlight column, right click then format cells.

Data Validation

This is typically restricting what a user can enter into a data cell. We use data validation to prevent errors when entering data, to maintain consistency across data set, ensuring correct data type is entered and to improve data quality before analysis
Follow this steps to validate your data:

-select the column or cells you want to validate
-On the data ribbon, select Data
-click on Data Validation the choose the validation criteria.

Data Filtering and Sorting.

Filteringis displaying only the data that meets a specific condition while temporarily hiding the rest.
We use Sort & Filter command to filter.
Types of Filtering:
-Text filtering
-Date filtering
-Number filtering

Sorting is arranging data in a particular order ie
-Number sorting- its arranging numbers in a particular order that is ascending or descending.
-Text sorting- its arranging text data in a specific order can be either ascending or descending.

Data Preparation and Cleaning.

Data cleaning is one of the most essential steps in data analysis.
Whenever we deal with data, we are bound to encounter data inconsistencies such as duplicate values, missing values or even spelling mistakes.

Conditional Formatting

It's an excel feature used to automatically change the appearance of cells based on specific rules or conditions.
Used to identify outliers, trends, patterns,duplicates and missing information

Key Functions used in data Cleaning

1. Text Functions.

• Upper() Function- converts texts to uppercase.

• Lower() Function- converts texts to lowercase.

• Proper() Function- capitalizes the first letter in a word.

• Trim() Function- It removes extra spaces before and after the text.
  We use LEN()to count how many characters are in a text

• Mid()- used to extract characters in the middle of a text ie.
  MID(F2,2,3)

-F2- Representing text
-2- representing the starting position
-3-representing the number of characters to be removed.

Aggregate Functions.

• Product()- Multiplies numbers in a range

• Max()- used t find the highest value in a range

• Median()- Returns the value in the middle for the sorted data.

• Mode()- used to find most frequent value in a range

Conditional Aggregations.

• COUNTIF- used for condiotional counting ie.

=COUNTIF(E2, E870, "Sales")

• COUNTIFS()- Used when we have more than one condition ie
  finding male employees who have masters
  =COUNTIFS(I2:I870,"M",N2:N870,"Masters")

• SUMIF- used to add values that meet a specific criteria
  we start with criteria range then sum range.
  ie Total employee salary in HR department
  SUMIF(E2:E870,"Human Resource", F2:F877)

• SUMIFS()- Used in more than two conditions
  we start with criteria range, criteria, then the sum range
  ie . Salary for females in Human resource department.
  SUMIFS(F2:F877,E2:E870,"Human Resource,I2:I870,"F")

Date/Time Functions

• Today()- it returns today's date
  ie
  =TODAY() click enter.

• Now()- Gets Todays date and time

-YEAR()- Used to extract year from a date
ie =YEAR()

-MONTH()- Used to extract a month from date
-DAY()- Extracts a day from a date ie.
=Day()
-DATEDIF()- Calculates the difference between two dates