Getting Started with Excel for Data Analytics

Introduction

Microsoft Excel is one of the most used tool for analyzing data in the world. It works by correcting mistakes found on data, such as duplicates, misspelled words etc.
This article explains Excel concepts covered during Week 1 sessions and demonstrates how they can be applied to a real dataset. The Excel dataset that i will use to demonstrate in this article is Final HR Data Base.
The main objective is to take a raw dataset and transform it into a clean, organized dataset that can be used for analysis.

Understanding the excel workbook.

An Excel file is called a workbook. A workbook can contain one or more worksheets. This excel workbook contains:

• Rows: It is a horizontal line of cells and they are always numbered, e.g., Row 1.
• Column: This is a vertical line of cells, e.g., A1.
• Cell: Intersection of a raw and a column, e.g., A1.
• Range: It is a group of cells, e.g., A1:D4.

The image below shows a blank worksheet.

On the above image we can identify rows, columns, and cells, also ranges.
We also have Excel Interface Components. This includes:

• Ribbon: Toolbar across the top that contains all commands organized into tabs(Home, Insert, Page Layout, Formulas, Data, etc.)
• Quick access Toolbar: Icons for save, undo, and Redo(top left corner)
• Formular Bar: Area above the grid where formula of the selected cell appears.
• Name Box: Displays the address of the selected cell .

Columns, Rows and cells are included as excel interface components.

Entering and Organizing Data.

When organizing Data on excel we should not mix up, every specific information should be in different column or specific row.
For example, Employees ID should be in one column, First name in its own column, etc.
After entering the dataset, it is useful to convert the range into an Excel Table. This can be done by selecting the data and pressing Ctrl + T.

An Excel Table provides useful features such as automatic filtering, structured references, automatic formatting, and easier expansion when new records are added.

We also have data types in excel as follows:

• Text(words): e.g., Kenya.
• Numbers: 1,2,3,7464
• Dates: 08/27/2026.
• Formulas: =Sum(D3:D17)
• Currency: KSh, $, etc. Understanding data types is important because Excel treats different types of information differently.

The image below shows different Data types.

From the above image we can identify:

• Numbers: In column A and H, they are aligned on the right, inside the columns.
• Text: In column B,C,D,E and I, they are aligned on the left inside the column.
• Dates: In column G.
• Currency: In column F, they are in Kenyan Shillings.

Navigating Excel.

Keyboard Navigation.

• Arrow keys: Move one cell at time.
• Ctrl+Arrow key: Jump to the end of the data region.
• Tab: Move one cell right.
• Enter: Move one cell down.
• Ctrl+S: Save the file.

Mouse Navigation.

• Click to select.
• Drag to select multiple cells.
• Scroll to move vertical or horizontal.

Data Formatting in Excel.

Text Formatting.

1. Select the cell.
2. Go to Home tab>Font group.
3. Click Bold(B),Italic(I), or Underline(U).
4. Change Font, Font size, and Font Color.

Numbers Formatting.

1. Select number cells.
2. Go to Home>Number section 3.

Choose:

• Number(with decimals)
• Currency(KES, $,etc.)
• Percentage (%)
• Date formats.

Data Sorting.

Sorting Data in excel is arranging data in a specific order.
For example:

• From smallest to the largest, for the numbers, and text(A to Z, or ascending order)
• We can also sort from the largest to the smallest(Z to A, or descending order)

Types of sorting.

• Text sorting.(A to Z or Z to A)
• Numbers sorting.(Smallest to largest or largest to smallest)
• Date sorting.(Oldest to the newest)

How to Sort Data.

1. Select the Data you would wish to sort.
2. Go to Home>Sort & Filter(or Data>Sort)
3. Choose Sort A to Z or Z to A.
4. For custom sorts, click Sort...., then choose:

• Column.
• Sort order
• Value/cell color/Font color.

5.Click OK.

Filtering.

It allows you to display only the rows that meet certain criteria and hide the rest temporarily.

Steps to apply filter:

1. Click anywhere in your dataset.
2. Go to Home>Sort & Filter (or Data>Filter).
3. Little dropdown arrow appear in header row.
4. Click the dropdown arrow for the column you want to filter,
5. Choose the values to show or use text/number/date filters.

Filter Types:

• Text Filter: Contains, Begins with, Ends with, etc.
• Number Filter: Greater Than, Less Than, Equals, etc.
• Date Filter: Before, After, Between, etc.

Freezing Panes.

This is to keep headers or important columns in view as you scroll.

Steps.

1. Click the row BELOW the header row(e.g., Row 2).
2. Go to View>Freeze Panes.

Basic Excel Formulas.

Formulas are an important part of Excel because they allow calculations to be performed automatically.
Excel formulas Always begins with an equal sign, (=).

Common Examples of Excel Formulas:

• SUM(): =SUM(D3:D7)
• MIN(): =MIN(C4:C8)
• AVERAGE(): =AVERAGE(F2:F20)
• Multiplication: =(D3*D4)

Excel Functions.

TRIM → removes extra spaces.(Example =TRIM(A2))
PROPER → standardizes names. (Example =PROPER(A2))
UPPER → makes text uppercase. (Example =UPPER(A2))
LOWER → makes text lowercase. (Example =LOWER(A2))
LEFT → extracts from the beginning.(Example =LEFT(A2,4))
RIGHT → extracts from the end. (Example =RIGHT(A2,4))
CONCAT → combines text. (Example =CONCAT(A2," ",B2))
LEN → counts characters. (Example =LEN(A2))

Data Validation.

Data Validation restricts the type of data users can input a cell.

Steps to Create a Drop-down List:

1. Select the cells where you want a dropdown (e.g.,A2:A10)
2. Go to Data>Data Validation 3.In the dialog:

• Allow: List.
• Source: Apples, Oranges, Bananas4.

3.Click OK.

Orther Types of Validation.

• Whole number between 1 AND 100.
• Date before or after today.
• Text length limits.

Removing Duplicate Records.

Excel provides a built-in Remove Duplicates feature.

To remove duplicates:

1.Select the dataset.
2.Go to the Data tab.
3.Select Remove Duplicates.
4.Select the columns that should be checked.
5.Click OK.

Data Cleaning.

Practical Data-Cleaning Workflow

I used the employee dataset shown above to demonstrate the following cleaning steps:

Step 1: Inspect the dataset
I checked the column headings, including Employee ID, Name, Email, Department, Salary, Hire Date, Age, and Gender.

Step 2: Check data types
I checked that Salary and Age are numbers and Hire Date is correctly formatted as a date.

Step 3: Check for duplicates
Employee ID 10540 appears several times, so the duplicate records need to be investigated.

Step 4: Check missing values
Some records contain “Unknown”, which needs to be reviewed and corrected where possible.

Step 5: Clean text
I used TRIM to remove extra spaces and PROPER to standardize names such as JAMES to James.

Step 6: Check numerical values
I checked for unusual values. For example, the dataset contains an employee age of 4, which should be investigated.

Step 7: Calculate new fields
A new field such as Annual Salary can be calculated using:

=F2*12

Step 8: Format the data
I formatted Salary as currency, Hire Date as a date, and Age as a number.

Step 9: Filter and sort
I used Excel filters to investigate departments and sort salaries from highest to lowest.

Step 10: Save the cleaned dataset
Finally, I saved the cleaned dataset separately while keeping the original dataset unchanged.

Conditional Formatting.

Conditional Formatting allows Excel to automatically highlight cells that meet specific conditions.

For example, employees who worked more than 40 hours could be highlighted.

Steps for conditional formatting.

1. Go to Home>Conditional Formatting.
2. Choose rule type:

• Highlight cell Rules(Greater than, Less Than)
• Top/Bottom Rules.
• Data Bars, Color scale, Icon Sets.

3.For example select Highlight Cell Rules>Less Than.
4.Enter a value(e.g.,50)
5.Choose a formatting style(e.g.,re fill)
6.Click OK.

Common Formula Errors in Excel.

Error	Meaning	Solution
#DIV/0!	Dividing by zero	Check that the divisor is not 0
#REF!	Invalid cell reference	Check references to deleted or moved cells
#VALUE!	Incorrect data type	Make sure you are using the correct data type, such as numbers for calculations
#NAME?	Incorrect function or name	Check the spelling of the function, e.g., SUM instead of SAM

The image below shows a Raw Data.

The image below shows a Cleaned Data.

Conclusion.

Excel is a useful tool for learning data analytics because it helps users organize, calculate, clean, and analyze data. Week 1 introduced important skills such as formulas, functions, sorting, filtering, and data cleaning.

The employee dataset showed how these skills can be applied to real data by identifying duplicates, missing values, incorrect entries, and inconsistent formatting.

The key lesson is that good data analysis starts with clean, accurate, and well-organized data.

Comments

[Mwenda Harun Mbaabu]

Good work, Kiprono. This is a well structured article and you have covered a wide range of the Excel concepts from Week 1, including workbook basics, navigation, formatting, sorting, filtering, formulas, text functions, data validation, duplicate removal, conditional formatting, common Excel errors, and a practical data-cleaning workflow.

Overall, this demonstrates a good understanding of the fundamentals, keep building on it.