Getting started with Excel for Data Analytics: From Basics to Data Cleaning

Introduction

Excel is a program developed by Microsoft. Excel is used to organize data in column and rows and allows you to do mathematical functions.

Excel is typically used for :

• Cleaning data
• Data entry
• Visuals and graphs
• Financial modeling
• Data analysis

Overview

This chapter is to give you an overview of Excel. Excel is made up of the Ribbon and the Sheet.

At the picture below, the ribbon is marked with a red rectangle and the sheet is marked with a green rectangle:

Ribbon

The ribbon contains App launcher, Tabs ,Commands as shown in the picture below.

The Sheet Explained

The Sheet is a set of row and columns. It forms the same pattern as we have in math exercise books, the rectangle boxes formed by the pattern are called cells.
Have a look at the picture below. Hello World was typed in cell C4. The reference can be found by clicking on the relevant cell and seeing the reference in the Name Box to the left, which tells you the cell address is C4.

Data Types

• Text: Aligned to the left by default.
• Numbers: Aligned to the right by default used in numeric calculations.
• Currency: Formatted numbers displaying currency symbols or standard calender dates.
• Percentanges: Decimal values displayed as parts of 100.

Data Organization :Sorting, Filtering and Tables

This allows for quick exploration without changing the original values.

Sorting and Filtering

Ranges can be sorted using the*Sort Ascending* and Sort Descending commands.
Sort Ascending: from smallest to largest.
Sort Descending: from largest to smallest.
The sort command work for text too, using A-Z order.
The commands are found in the Ribbon under the Sort & Filter menu.

This also means that numbers can also be sorted according to the formatted data type whether: dates, currency or age.

Filtering

Filter is similar to formatting a table, but it can be applied and deactivated.
The menu is accessed in the default Ribbon view or in the data section in the navigation bar.
Filters are applied by selecting a range and clicking the filter command.
It is important to have a row of headers when applying filters. Having headers is useful to make the data understandable.

Data Cleaning Tools

Important Data Cleaning techniques:

• Handle missing values: Filter to isolate blank cells, or highlight them.
• Removing Duplicates: Select your table go to Data> Remove Duplicates, and choose key columns.
• Trimming Extra Spaces: Using the function =TRIM(cell address)
• Using the Find & Replace command(CTRL+H)to swap out typos or unnecessary additional texts.

###Excel Conditional Formatting
Conditional formatting is used to change the appearance of cells in range based on your specified conditions.
The conditions are rules based on specified numerical values or matching text.

Conditional formatting is used to :

• Highlight duplicate cells.
• Highlight blank cells.
• Highlight values above or below a certain number.
• Can be used to create data bars and set icons.

Data Validation

Feature used to control and restrict the type of data entered into a cell.
Data Validation can be used to:

• Create drop-down lists
• Allow only whole numbers within a specific range.
• Restrict entries to dates or specific texts lengths.

PivotTable

Is a functionality in Excel which helps you organize and analyze data.
It lets you add and remove values, perform calculations, and filter and sort data sets.
To create one ;

1. Click on a data set, and select Insert > PivotTable. PivotTables have components:

Columns

Columns are vertical tabular data.
The column includes the unique header, which is on the top.
The header defines which data you are seeing listed downwards.

Rows

Rows are horizontal tabular data.
Data in the same row are related.

Filters

Filters are used to select what data you see.

Values

Values define how you present the data.
You can define how you Summarize and Show values.

Fields and layout

The TablePivot is displayed how by your settings.
The PivotTable Fields panel is used to change how you see the data.
The settings can be separated in two: Fields and Layout.

Fields

The checkboxes can be selected or unselected to display or change the property of the data.

In this example, the checkbox for Speed is selected.

Speed is now displayed in the table.

![ ](https://dev-to-uploads.s3.us-east-2.amazonaws.com

You can click the downwards arrow to change how the data is presented.

Layout

Drag and drop fields to the boxes to the right to display data in the table.

You can drag them to the four different boxes that we mentioned earlier (four main components):

Filters
Rows
Columns
Values

Excel Functions

AVERAGE Function

The AVERAGE function is a premade function in Excel, which calculates the average (arithmetic mean).

It is typed =AVERAGE()
`
It adds the range and divides it by the number of observations.

Example:

The average of (2, 3, 4) is 3.
3 observations (2, 3 and 4)
The sum of the observations (2 + 3 + 4 = 9)
(9 / 3 = 3)
The average is 3

CONCAT function

CONCAT is a function in Excel and is short for concatenate.

The CONCAT function is used to link multiple cells without adding any delimiters between the combined cell values.

It is typed =CONCAT()
`

COUNT Function

The COUNT function is a premade function in Excel, which counts cells with numbers in a range.

It is typed =COUNT()

Note: The COUNT function only counts cells with numbers, not cells with letters.

COUNTA Function

The COUNTA function is a premade function in Excel, which counts all cells in a range that has values, both numbers and letters.

It is typed =COUNTA()

COUNTBLANK Function

The COUNTBLANK function is a premade function in Excel, which counts blank cells in a range.

It is typed =COUNTBLANK()
`
Note: The COUNTBLANK function is helpful to find empty cells in a range.

COUNTIF Function

The COUNTIF function is a premade function in Excel, which counts cells as specified.

It is typed =COUNTIF()

COUNTIFS

The COUNTIFS function is a premade function in Excel, which counts cells in a range based on one or more true or false condition.

It is typed =COUNTIFS:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
The conditions are referred to as critera1, criteria2, .. and so on, which can check things like:

If a number is greater than another number >
If a number is smaller than another number <
If a number or text is equal to something =
The criteria_range1, criteria_range2, and so on, are the ranges where the function check for the conditions.

SUMIF Function

The SUMIF function is a premade function in Excel, which calculates the sum of values in a range based on a true or false condition.

It is typed =SUMIF:

=SUMIF(range, criteria, [sum_range])
The condition is referred to as criteria, which can check things like:

If a number is greater than another number >
If a number is smaller than another number <
If a number or text is equal to something =
The [sum_range] is the range where the function calculates the sum.

SUMIFS Function

The SUMIFS function is a premade function in Excel, which calculates the sum of a range based on one or more true or false condition.

It is typed =SUMIFS:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2] ...)
The conditions are referred to as criteria1, criteria2, and so on, which can check things like:

If a number is greater than another number >
If a number is smaller than another number <
If a number or text is equal to something =
The criteria_range1, criteria_range2, and so on, are the ranges where the function check for the conditions.

The [sum_range] is the range where the function calculates the sum.

SUM Function

The SUM function is a premade function in Excel, which adds numbers in a range.

It is typed =SUM

Note: The =SUM function adds cells in a range, both negative and positive.

How to use the =SUM function:

Select a cell
Type =SUM
Double click the SUM command
Select a range
Hit enter

MAX Function

The MAX function is a premade function in Excel, which finds the highest number in a range.

It is typed =MAX

MIN Function

The MIN function is a premade function in Excel, which finds the lowest number in a range.

It is typed =MIN

MODE Function

The MODE function is a premade function in Excel, which is used to find the number seen most times.

This function always returns a single number.

It is typed =MODE.SNGL
It returns the most occurring number in a range or array.

MEDIAN Function

The MEDIAN function is a premade function in Excel, which returns the middle value in the data.

It is typed =MEDIAN

LOWER Function

The LOWER function is used to lowercase text in a cell.

Changing the letter case of your cell values can be great when there is a lot of case inconsistency among the cell inputs or when preparing your dataset for case-sensitive usage.

It is typed =LOWER
If you want to use the function on a single cell, write:

=LOWER(cell)

RIGHT Function

The RIGHT function is used to retrieve a chosen amount of characters, counting from the right side of an Excel cell. The chosen number has to be greater than 0 and is set to 1 by default.

It is typed =RIGHT

LEFT Function

The LEFT function is used to retrieve a chosen amount of characters, counting from the left side of an Excel cell. The chosen number has to be greater than 0 and is set to 1 by default.

It is typed =LEFT

CONCLUSION

Excel is a useful tool for data analytics, especially for beginners. It helps users clean, organize, analyze, and visualize data efficiently. By mastering functions and features such as data validation, conditional formatting, and data cleaning, users can turn raw data into meaningful insights.