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

Microsoft Excel

Microsoft Excel is a spreadsheet software developed by Microsoft that allows you to collect,organize, analyze, calculate, and visualize data efficiently using tables, rows, and columns. Excel's features, which include charts, functions, formulas, and formatting tools, facilitate information organization and decision-making. It is a vital resource for professionals in a variety of industries and companies

The Excel Interface

The Microsoft Excel interface consists of a collection of customizable tools, menus, and grid-based workspaces designed for data management and analysis.

When you open Excel, you are presented with a user interface made up of many tools ie

Ribbon-Toolbar across the top that contains all commands organized into tabs
Quick Access Toolbar- Icons for Save, Undo, and Redo (top left corner)
Formula Bar- Area above the grid where the content or formula of the selected cell appears
Name Box- Displays the address of the selected cell (e.g., A1)
Columns- Labeled A, B, C... across the top vertically
Rows- Labeled 1, 2, 3... down the left side horizontally
Cell- A single box where a row and column intersect (e.g., B2)
Worksheet Tabs- Tabs at the bottom (Sheet1, Sheet2) that switch between spreadsheets within a workbook

The Excel’s Structure: Rows, Columns, Cells

Row- A horizontal line of cells (e.g., Row 1)
Column- A vertical line of cells (e.g., Column A)
Cell- Intersection of a row and column (e.g., A1)
Range- A group of cells (e.g., A1:C3)

Supported data types in include; Text (words): e.g., Brian, Numbers: 100, 60.1, Dates: 01/06/2025, Formulas: COUNT. =COUNT(A2:A10)

Data Formatting in Excel

Data formatting in Excel is the process of changing how data looks and is categorized in a spreadsheet without changing the actual value inside the cell.

Main Types of Formatting include;

1. Number Formatting Controls how numbers appear, such as changing raw digits into Currency ($1,250.00), Percentages (75%), Dates (MM/DD/YYYY), or decimals

Common Number Formats:

• Currency (e.g., $1,000.00, KES 1000)
• Percentage (e.g., 75%)
• Date and Time (e.g., 03-Jun-2025, 2:30 PM)

Real life application in Number Formatting can be showing Salary of employees as currency

Currency formatting steps

1. Select the cells with numbers (Salary Column)
2. Go to Home tab > Number group
3. Click the dropdown arrow on the number format box
4. Select Currency

5. To change currency symbol, click the small dialog launcher → Choose symbol

6. Text Formatting Changes text appearance using font styles, sizes, bold, italics, underlining, and text colors via the Home tab.

Real life application in text formatting can be showing Employee records ie Clearly displaying employee names, departments, salary, and other information.

1. Cell Alignment To align cells in Excel, select your cells, go to the Home tab, and use the buttons in the Alignment group. You can change horizontal alignment (left, center, right), vertical alignment (top, middle, bottom), or use Wrap Text to fit long content

Real life application in Cell Alignment can be aligning employee IDs to the right and aligning names to the left appropriately.

1. Cell Styling & Borders Adds visual boundaries, background fill colors, and structure to tables. In Home > Font, click: Borders → Choose “All Borders” Fill Color → Choose a light background for headings

Real life application in cell styling & Borders can be Styling cells to distinguish Employee's First name, Last name, deparment etc.

1. Conditional Formatting highlights cells automatically based on rules or criteria, helping spot trends or outliers

Real life application in conditional formatting can be highlighting salary of employees below certain figure. ie our case <ksh 75,000

Steps:

1. Go to Home > Conditional Formatting
2. Choose a rule type:
   • Highlight Cell Rules (Less than, Greater than)
3. Select Highlight Cell Rules > Less Than
4. Enter a value (e.g., ksh75,000)
5. Choose a formatting style (e.g., red fill)
6. Click OK

Data Sorting

Sorting means arranging data in a specific order — either ascending (A to Z or smallest to largest) or descending (Z to A or largest to smallest).

Types of Sorting:

1. Text Sorting (A to Z or Z to A)
2. Number Sorting (Smallest to Largest or Largest to Smallest)
3. Date Sorting (Oldest to Newest or vice versa)

Real life application of data sorting can be Sorting employee names alphabetically

Steps to Sort Data:

1. Select the column or data range to sort
2. Go to Home > Sort & Filter (or Data > Sort)
3. Choose Sort A to Z or Sort Z to A (our case A to Z)
4. For custom sorts, click Sort..., then choose:
5. Column
6. Sort order
7. Value/Cell Color/Font Color
8. Click OK

Filtering

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

Filter Types:

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

Real life application of data filtering can be showing hire date of employees between certain periods ie our case between 01-01-2021 and 01-01-2022

Steps to Apply a Filter:

1. Click anywhere in your dataset
2. Go to Home > Sort & Filter > Filter (or Data > Filter)
3. Little dropdown arrows 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

Data Validation

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

Steps

1. Select the cells where you want a dropdown (e.g., A2:A10)
2. Go to Data > Data Validation
3. In the dialog:
4. Allow: List
5. Source: Department, Age, Gender
6. Click OK

Removing Duplicates

Duplicate data causes errors in analysis and inflates numbers.

Functions in Excel

Text Functions are built-in formulas used to clean, extract, change, and combine text strings in your spreadsheets.

Key Functions include;

• UPPER() Converts text to uppercase
• LOWER() Converts text to lowercase
• PROPER() Capitalizes first letter of each word
• TRIM() Removes extra spaces from text
• LEFT() Extracts leftmost characters
• RIGHT() Extracts rightmost characters
• MID() Extracts characters from the middle
• LEN() Returns length of text
• FIND() Finds position of a substring (case-sensitive)
• SUBSTITUTE() Replaces text within a string

An example can be Correcting names to lower case, Upper case and proper case

Number Function help you perform math, find totals, count items, and change how numbers look in your spreadsheet.

Arithmetic operators are symbols used to perform basic mathematical calculations like addition, subtraction, multiplication, and division

Addition + =A1+B1
Subtraction - =A1-B1
Multiplication * =A1*B1
Division / =A1/B1
Exponents ^ =A1^2

An example can be performing a Simple Addition Calculation

Functions A function is a predefined formula in Excel that performs a specific task.

SUM() Adds a range of numbers
AVERAGE() Calculates the mean of values
MIN() Finds the smallest number
MAX() Finds the largest number
COUNT() Counts how many numbers are in a range
POWER() to raise a base number to a specified exponent or power
SQRT() to calculate the square root of a positive number
PRODUCT() to multiply all the numbers given as arguments and return the final result
MODE() to find the most frequently occurring number in a datase
MEDIAN() to find the middle number in a set of given values

An example can be performing Addition for cells ranging from A12 to D13

1. Click on an empty cell (e.g., D15)
2. Type =SUM(A12:D13)
3. Press Enter
4. Excel returns the total sum of A12 to D13