Getting Started With Exel For Data Analysis.

Introduction

Microsoft Excel is a spreadsheet application developed by Microsoft. It allows users to store data in rows and columns and perform calculations and analysis using formulas and functions.

Excel is important in data analytics because it provides tools for almost every stage of basic data preparation. An analyst can use Excel to import or manually enter data, clean inconsistencies, calculate metrics, filter records, identify trends, and create reports.

Understanding the Excel interface

The Excel interface consists of several components.

1. The Ribbon - is located at the top of the application and contains different tabs, including Home, Insert, Page Layout, Formulas, Data, Review, and View. Each tab provides tools for a particular category of tasks. For example, the Data tab contains tools for sorting, filtering, removing duplicates, and splitting data using Text to Columns.

2.Worksheets

An Excel workbook can contain multiple worksheets. Each worksheet provides a separate environment for storing data.

3.Cells, rows, and columns

A cell is the basic unit in an Excel worksheet. Each cell has a unique address based on its column and row.

For example:

A1

means column A, row 1.

Rows are identified using numbers, while columns are identified using letters.

A range such as:

A2:A13

represents all cells from A2 through A13.

Understanding Data Types

Correctly identifying data types is essential for analysis.

Text

Text represents characters or categorical information.

Examples include:

• Name
• Email
• Department
• Gender

Numbers

Numbers can be used in mathematical calculations.

Examples include:

• Salary
• Bonus
• Perfomance score
• Experience

Dates

Dates are particularly important in analytics because they allow data to be analyzed over time.

Examples include:

2026-01-05

Data Cleaning

Data cleaning is the process of identifying and correcting inaccurate, inconsistent, incomplete, duplicated, or improperly formatted information.

A clean dataset should be:

• Consistent.
• Accurate.
• Complete where possible.
• Properly structured.
• Free from unnecessary duplicates.
• Suitable for analysis.

Removing duplicates
Excel provides a Remove Duplicates feature under:

Data → Remove Duplicates

Before removing duplicates, the analyst should determine which column or combination of columns uniquely identifies a record.

Sorting Data

Sorting reorganizes records according to a selected column.

For example, the sales dataset can be sorted by:

• Employee name from A-Z.
• Employee name from Z-A.
• Age from smallest to largest.
• Age from largest to smallest.
• Hire date from oldest to newest.
• Hire date from newest to oldest.

Find and Replace

The Find & Replace feature allows analysts to locate specific values and replace them with standardized values.

The shortcut is:

Ctrl + H

For example, suppose the dataset contains:

Nairobi
nairobi
NAIROBI

These technically represent the same region, but inconsistent capitalization can cause problems when grouping or filtering data.

Filtering Data

Filtering temporarily hides records that do not meet specified conditions.

The AutoFilter can be activated from:

Data → Filter

or by using the keyboard shortcut:

Ctrl + Shift + L

Once filtering is enabled, dropdown arrows appear beside the column headings.

For example, an analyst could filter the Region column to display only:
Nairobi

The dataset can also be filtered using conditions such as:

Greater than KSh 10,000.
Less than KSh 5,000.
Contains "Single".
Begins with a particular letter.

Working with Formulas

Excel formulas allow analysts to perform calculations directly inside cells.

A formula begins with:

=

For example:

=E2*F2

could calculate the sales value if A2 contains quantity and A2 contains unit price.

The four arithmetic operators are:

Addition: +
Subtraction: -
Multiplication: *
Division:/

Basic Excel Functions

Excel functions provide predefined methods for performing calculations.

SUM()

=SUM(J2:J13)

calculates the total of a range.

AVERAGE()

=AVERAGE(J2:J13)

calculates the arithmetic mean.

MIN()

=MIN(J2:J13)

returns the smallest value.

MAX()

=MAX(J2:J13)

returns the largest value.

COUNT()

=COUNT(A2:A13)

counts cells containing numerical values.

COUNTA()

=COUNTA(B2:B13)

counts non-empty cells, including cells containing text.

LEFT()

=LEFT(B2,5)

returns the first five characters from a text value.

RIGHT()

=RIGHT(B2,5)

returns the last five characters.

LEN()

=LEN(B2)

returns the number of characters in a cell.

This can be useful for identifying unexpected values.

TRIM()

=TRIM(B2)

removes unnecessary spaces from text.

This is particularly useful for the raw dataset because some customer names contain leading or trailing spaces.

UPPER()

=UPPER(D2)

converts text to uppercase.

LOWER()

=LOWER(D2)

converts text to lowercase.

PROPER()

=PROPER(B2)

converts text into title case.

Date and Time Functions

Dates are extremely important in analytics because organizations frequently analyze trends over time.

TODAY()

=TODAY()

returns the current date.

This is useful for calculations that need to remain current.

NOW()

=NOW()

returns the current date and time.

YEAR()

=YEAR(H2)

extracts the year from a date.

MONTH()

=MONTH(H2)

extracts the month number.

DAY()

=DAY(H2)

extracts the day of the month.

These functions allow analysts to transform a date into useful analytical dimensions such as year, month, and day.

DATEDIF()

DATEDIF() calculates the difference between two dates.

Conclusion

Excel provides a strong foundation for learning data analytics because it combines data entry, organization, cleaning, transformation, calculation, and analysis in one environment.