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

What is Data Analytics?

Data Analytics is the concept of examining data to find trends, patters or even information that will aid in decision making. Several tools can be used for data analysis. However, today, we will focus on excel. Excel is widely used in organizations to organize, calculate, analyze and store data. Different users can use it for different use cases. In this article, we will start from basic excel capabilities and gradually build up to advanced data cleaning.

The Excel Ribbon

Before starting any data analysis, let's understand the tabs in excel. We will interact with different tabs including File, Home, Insert, Data, Review, View. The below list describes the functions of each of the tabs.

• Home — basic formatting, alignment, number formats, and editing tools.
• Insert — used to insert tables, PivotTables, charts, and other visual elements.
• Page Layout — controls how worksheets are arranged and displayed when printed.
• Formulas — provides access to Excel functions and formula-related tools.
• Data — used for data analytics. It contains tools for sorting, filtering, removing duplicates, data validation, and other data management activities.
• Review — includes tools for reviewing, commenting, and protecting worksheets.
• View — controls how the workbook and worksheets are displayed.

Rows, Columns, Cells

👍Rows- runs horizontally across the worksheet and are usually represented by numbers.
💯 Columns - runs vertically across the worksheet and are represented by letters.
👎Cell - Formed when a row and a column meet e.g D7
⭐ Name Box (Cell Address) - displays the reference of the currently selected cell.
👀Formula Bar - displays the value or formula contained in the displayed cell.

Data Types In Excel

A data type is basically the different information contained in a cell. It can take different forms e.g Text, Numbers, Currency, Percentage. To view the different data types click, Home - Number. In excel, data may appear as number but its actually stored as text.

Formatting and Organizing Data in Excel

Formatting is used to make data easier to read, analyze and understand. Excel has different features that enhance data depending on the use-case.

• Font – Changes how text is displayed, think of different handwritings.
• Font Size – Increases or decreases the size of the text.
• Bold – Makes text darker and is commonly used to emphasize headings or important values.
• Italic – Slants text and can be used to emphasize specific information.
• Underline – Places a line underneath text to make it stand out.
• Font Color – Changes the color of the text.
• Fill Color – Changes the background color of a cell.

Creating Tables

Tables consist of several rows and columns. Tables can have headers which define the titles of the different columns. To create a table, Click insert - Table

Sorting and Filtering Data

Sorting and filtering data aid in exploring and organizing data to get more insights about it. We can sort text from A-Z, Z-A. We can sort numbers from Largest-Smallest or Smallest-LargestWe can even sort by color. Another advanced sorting mechanism is adding levels during sorting.

Filtering data allows us to display the conditions we need and hide the undesired conditions. We can do a custom filter to display the conditions required e.g Only filter quantity equals to 7

Data Cleaning in Excel

Data cleaning is the process of identifying, removing incorrect, incomplete, duplicate or unwanted data values before performing data analysis.

Data Cleaning Techniques

1. Missing Values Identification - for this, we can utilize filter, then select blanks. Another way can be through COUNTBLANK

1. Removing duplicates - to remove duplicates, highlight the selection, Data- Remove duplicates.

1. Removing spaces from a word - you can use the TRIM function for this.
2. Text standardization - this can be formatting text into different cases. UPPER, LOWER, PROPER
3. Find & Find and Replace - to look for certain values or data from your excel, use FIND(CTRL+ F). We can also choose to replace what we find with desired data using FIND & REPLACE CTRL+H

All these techniques can be used to clean data before jumping into analysis.

Working with Formulas and Functions in Excel

Formulas and functions come in handy in excel. Imagine you're trying to find the sum of company owned assets in a company with thousands of employees. All formulas in excel must start with the =sign.

Functions in excel are designed to perform a particular operation. Instead of manually writing the calculation, we can specify the cell range and use a function e.g =SUM(G2:G20).
Other basics functions we can work with are SUM, AVERAGE, COUNT, AVERAGE, MIN, MAX, MEDIAN, MODE, COUNT, COUNTA, COUNTBLANK

AVERAGE - Calculates the average of numerical values
COUNT - Counts cells containing numbers
COUNTA - Counts cells that are not empty
COUNTBLANK- Counts cells which are blank
MIN - Returns the smallest value
MAX - Returns the largest value
MEDIAN - Returns the middle value
MODE - Returns the most frequently occurring value

Check the return of the functions for the below values:

Conditional Functions in Excel

Conditional functions or conditional aggregations perform calculations based on specific conditions and criteria. We can count, sum, average, when defined conditions are met.

• COUNTIF- Counts the number of cells that meet one specified condition. =COUNTIF(range, criteria)
• COUNTIFS - Counts the number of records that meet multiple conditions.=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
• SUMIF - Adds values that meet one specified condition.=SUMIF(range, criteria, [sum_range])
• SUMIFS - Adds values that meet multiple conditions.=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
• AVERAGEIF - Calculates the average of values that meet one specified condition.=AVERAGEIF(range, criteria, [average_range])
• AVERAGEIFS - Calculates the average of values that meet multiple conditions. =AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

So, you can tell that IF is for one condition, and IFS specifies multiple conditions.

Date and Time Functions in Excel

Date and Time functions in excel help us extract parts from a date, like the number of days, months, years, difference between dates and the working days e.tc.

TODAY() Returns the current date.
NOW() Returns the current date and current time.
DAY() Extracts the day from a date.
MONTH() Extracts the month number from a date.
YEAR() Extracts the year from a date.
DATE() creates a date using a specified year, month, and day.
DATEDIF() - Calculates the difference between two dates in days, months, or years. You can specify with (D) for days, (M) for months, (Y) for years
NETWORKDAYS() - Calculates the number of working days between two dates, excluding weekends.

DATEDIF() example

PivotTables in Excel

PivotTables come in handy when you need to summarize and analyze a large amount of data. Think of a telco engineer who wants to analyze the KPIs of all sites across the country. It might take time analyzing one by one.

TO create a pivotable in excel, follow the following steps:

1. Select all or the section of data you need to work with.
2. Click insert - pivotable. Excel might open a new worksheet for the pivotables.

1. You can choose different fields to use as filters, columns, rows and values, by dragging and dropping them. With the data below, we can analyze the sum of sales for the different products.

1. If you need a graphical analysis, you can proceed to insert pivot charts. Click on Insert - Pivot chart - choose the chart type and you will have a graphical representation of your analysis.

Conclusion

Excel is a great tool for working with data. We have explored excel from basics, understanding the different tabs, formatting, creating tables, using different functions and even creating pivot tables and charts. Excel is capable of much more and is a great foundation for data analytics.