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

Introduction

The pursuit of social, economic or even spiritual endeavours involve a continuous cycle of decision making. We rely on data to make these decisions, however negligible the data may seem.

We are usually presented with raw data, take for example a local government authority collecting population data to help determine appropriate development programmes. They will likely be presented with a general count of people, raw data, which isn't useful unless processed, presented meaningfully and used to advise. This brings us the concept of excel and data analytics.

We will sequentially look at three core concepts.

1. Excel
2. Data Cleaning
3. Data Analytics

Excel

Excel is a spreadsheet application that automates data processing. It's the most basic tool and allows storage and manipulation of data.

Structure

• A grid based interface of columns labelled alphabetically and rows labelled numerically.
• Cells are individual rectangular boxes containing data and are cross-referenced with the rows and columns e.g cell B2 being at the intersection of column B and row 2.
• The ribbon, at the top of an excel window. It contains tabs such as Home, Data and formulas with nested and grouped tools and commands.
• Formula bar, the long horizontal bar above the grid. It displays the currently selected cell and it's contents including formulas. You can directly input formulas in this bar.
• Name box adjacent and left of the formular bar displays your current cell.
• Worksheets which are displayed at the bottom of the grid and are basically pages within an excel document.
• Quick access toolbar at the top left of the window provides shortcuts to save, undo and redo actions.

Core functions.

• Data cleaning
• Formulas and functions
• Pivot tables
• Data visualisation
• What if analysis

Data cleaning

Presented with raw data, users need to ensure the integrity and accuracy of data to make it meaningful.

One important step in cleaning is to ensure your data set has headers. This sets parameters for you when you start inspecting the data set.
In a large data set, you can use freeze panes. Excel freezes columns and panes on top and to the left of your active cell.

It keeps the frozen columns and rows constant regardless of where you're scrolling in the dataset.

Data validation
This ensures only certain type of data is allowed in columns. You could set this to text only or numbers only for example.

Data validation works best when set before data collection, else it only restricts further data types if data exists already.

Where data exists already, you use sort and filter tab to view the data items in each column and clean. In a dataset with gender columns for example, You'd need to ensure you don't have Female and F representing the same thing, or Male and M.

The numbers tab under home tab allows you to format data in each column and standardise. Take a date column for example. In uncleaned data, you'd have dates entered in various formats like DD/MM/YY, YY/MM/DD, DD/MONTH/YEAR etc.

The numbers tab gives you freedom to set columns into text only, standardised date format, specific currency etc.

Duplicates
Duplicates can be removed from the Data tab. You can select columns and ensure only unique data exists.

Exercise with caution to avoid unnecessarily removing valid data. Two items may have a duplicate in one column while everything else varies in other columns making each a unique item.

TRIM
The =TRIM() removes unwanted spaces in a text string, whether leading, trailing or in between text.

Case sensitivity
You can ensure all uppercase, first letter uppercase or all lowercase using =UPPER() =PROPER() and =LOWER() respectively.

=CLEAN()
This removes unwanted characters especially where data was imported. Should also be used cautiously to avoid unintentionally excluding valid data.

Extracting and splitting

• Text to columns found in the data tab and splits text strings in a column into different columns e.g. when you need First and second names separately.

• =LEFT() =RIGHT() =MID() allows you to extract characters in a cell depending on position, for example, =RIGHT(B4, 3) gives us ent as in the example below.

• CONCAT allows us to combine text strings. See example below.

FIND AND REPLACE
Allows quick location of specific items and replacement with a standardized value.

Data analytics

Cleaned data ensures any analysis done gives reliable insights.

Data cleaning is part of the analytics process. It's the first step when presented with a set of raw data.

Data analytics can be described as the elaborate process of inspecting, cleaning, transforming and modelling raw date to express usable insights. You are basically turning raw data into actionable information.

In data analytics, you will do 4 types of analysis.

• Descriptive analysis simply tells us what happened. For example 30 students in a dataset of 100 students scored grade A in stream X in 2026.
• Diagnostic analysis examines why only 30 students scored Grade A, by looking at historic trends maybe, or even variables that could have limited perfomance in that class. -Predictive analysis uses the diagnostic data to predict what could happen in future. -Prescriptive analysis offers insights into what action can be taken to get better performance of the students for example or to limit the inhibiting factors.

Conclusion

Data sets vary vastly and each will require uniquely different tools and approach to clean. The strict rule is to ensure validity, uniformity and highest level of accuracy. Data sets can be processed but integrity of the original data must always be upheld.