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

Introduction

Microsoft Excel is a spreadsheet program used to organize, calculate, analyze, and visualize data in rows and columns. It is organized in worksheets that are contained in the workbook - the file.
Data analysis is the process of cleaning, organizing and examining raw data to find useful patterns the support business decisions.
Data is often received in it rawest form which is disorganized and in order to make business sense of it, the data must be cleaned and organized. So how do we clean raw data and make business sense out of it.

1. Filtering

Filtering is temporarily conceals rows that do not fit your rules. It displays a smaller, relevant subset of data while leaving the original row order untouched.

Using filtering to clean data

• Auto fit the column width to see column data properly. Home tab Cell.
• Select the row with all the headers and select filter. This will enable us to remove unwanted data fields our columns.
• When cleaning data for non-unique input values we need columns to have only one instance of a text field. We do this by deleting all other text fields until we remain with unique ones. An example would be having HR and human resource both as input fields instead of one instance.

In this column we have only one instance of every department. In such columns we often use data validation to ensure that inputs are strictly of a specific type. Example Male or female, or a specific department.

Using filtering for analysis
In excel we can filter by certain types of data namely text, numbers and date. This means we can conceal all other data and get only the data that fits our criteria

Above is text filter. We are filtering last names column for names beginning with 'mbo'

We have various options for number filters. Example we can filter for salaries that are greater than 50,000 using the option provided.

This is date filter. We use it to get specific time lines and time ranges.

2. Sorting

Sorting changes the physical position and order of rows (such as A to Z, Z to A, smallest to largest, or by cell color). All data remains visible on the sheet unlike filtering that hides data giving only what was filtered. It is found in home -> editing

Multilevel Sorting

Here we are sorting our data by the columns department, gender and age. This rearranges the entire worksheet to fit this parameters.

3. Conditional Formatting.

Conditional formatting in Excel is a tool that changes the look of cells automatically based on their content. It lets you use **colors, icons, or data bars to spot trends, high values, or errors and duplicates **without looking through rows by hand

In this instance we are using conditional formatting on the employeeID column to highlight duplicates using a specific color, since the ID values are supposed to be unique any duplicate should be deleted
Conditional formatting can also be used on dates and time, getting values that are greater or less than, to highlight text that contains a specific text, getting top 10%. Explore the conditional formatting options

4. Data Validation

Data validation is a feature that restricts what users can type into a cell or a range of cells to keep the data clean and accurate. Found in the data tab --> data tools group

In the above image we have validate the department column to only accept a list of departments available in the organization

In this example we see that to input values into the department column we have a drop down list to select from

• The data validation criteria can also cater for dates, time whole numbers, lists and text length. This ensures that the data being input in the cell already meets the criteria for that column or cell. The process ensures incoming data is clean.

5. Functions

Aggregation Function
Aggregate function calculate a single summary value such as a sum, average, or count from a list of numbers
Count - The function is used to count non-numeric cells.
CountA - counts non-blank cells in the column
CountBlank - Counts blank cells in a column

Conditional Aggregations
A conditional aggregate function in Excel summarizes a set of data (like adding numbers or counting rows) only for the cells that meet a specific rule or condition.
Examples are COUNTIFS, SUMIFS, COUNTIF.

In the above image we use SUMIFS to calculate the sum of salary(range) if the gender is female(range and text) and the department is Finance (range and text). Using the syntax provided.

Statistical Functions
Statistical Functions to analyze, summarize, and interpret data. They include mean, median and mode.
Note that the median and the average should not have a big difference if the data is consistent and has no outliers

Here we find the median for out salary column

Date and time functions
Excel date and time functions work like a hidden digital calendar and clock that let you track, display, and calculate days and hours with ease.
=YEAR(A1) =MONTH(A1) =DAY(A1) This are some of the date and time functions

Text Functions
This are functions that format text for a certain expected outcome. Example: CONCAT(adding two strings together), TRIM, PROPER(first letter capitalization)

In this example we use the text functions CONCAT and LOWER to join the first and last name and form an email field from that text

Conclusion.
This article explains how you can clean data and analyze data using excel using tools like data validation, functions, filters and sorts. It helps capture the power of excel in your data analysis journey.