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

Introduction

Excel is mainly known as a tool for creating tables,keeping records and performing calculations. However, its features also make it useful for preparing and analyzing data. A well organized spreadsheet makes it easier to identify patterns, correct errors, calculate useful measures, and prepare information for further analysis.
This article demonstrates the use of Excel with a small sales dataset. The dataset contains customer information, regions, product categories, order dates, quantities, prices and order status. The main focus is on moving from basic spreadsheet operations to practical data cleaning. The process includes entering and formatting data, sorting and filtering records, correcting inconsistent text, removing duplicates, creating formulas, and checking the quality of the final dataset.

The Excel workspace

Before working with a dataset, it is important to understand the basic Excel workspace. The Ribbon provides access to the main commands and features, while the Formula Bar displays the contents of the selected cell. The Name Box identifies the cell address, such as A1 or B5.
The worksheet is organized into rows and columns. Each intersection forms a cell, and a collection of cells is known as a range. This structure is particularly useful for data analytics because each column can contain a specific field while each row represents an individual record.

Formating dataset

Formatting improves the readability of a worksheet and helps distinguish different types of information. Column headings can be made bold, column widths can be adjusted, and borders can be added to make the table easier to follow.
Number formatting is also useful. Prices and calculated sales values can be displayed with thousands separators or currency formatting, while dates can be displayed using a consistent format. Formatting does not change the underlying value; it changes how that value appears on the worksheet.
A clean and simple layout is usually more useful than excessive formatting. The main purpose of formatting in a data worksheet is to make the information easy to read and understand

Sorting and Filtering Data

Sorting is a quick way to arrange records in a meaningful order. A customer column can be sorted alphabetically, dates can be arranged from oldest to newest, and prices can be arranged from lowest to highest.It's particularly useful during data checking because unusual values can become easier to notice. For example, sorting Unit Price from largest to smallest makes the highest priced orders immediately visible.
Filtering works differently because it temporarily hides records that do not meet selected conditions. The dataset can be filtered to show only orders from Nairobi, only completed orders or only sales above a particular value. The hidden records are not deleted and can be displayed again by removing the filter.

Freezing Headings

Large datasets can contain hundreds or thousands of rows. Scrolling through such a dataset can make it difficult to remember which column contains which information.
Freeze Panes solves this problem.
Freezing the top row keeps the headings visible while scrolling down the worksheet. This is a simple feature but it becomes very useful when working with larger datasets.
Raw data before cleaning

Cleaning Customer Names

The first cleaning step involves standardizing customer names. Extra spaces can cause problems when comparing values, while inconsistent capitalization makes the dataset look untidy and can interfere with grouping.
The TRIM() function removes unnecessary spaces from text. PROPER() changes text so that the first letter of each word is capitalized. These functions can also be combined.
For example:
=PROPER(TRIM(B2))
This can convert an entry such as “ john doe ” into “John Doe”.
The result is more consistent and easier to work with.
Other text functions such as UPPER(), LOWER(),LEFT(), RIGHT(), MID(), LEN(), FIND()and SUBSTITUTE() can also be useful for different cleaning tasks.

Standardizing Categories

The Category column also needs consistent values. For example, “electronics”, “Electronics”, and “electronics ” may all refer to the same category even though the entries are written differently.
Standardizing the categories ensures that records belonging to the same group remain together during analysis. Without this step, a summary of Electronics sales could incorrectly treat different spellings or capitalization as separate categories.
This demonstrates why data cleaning is an analytical step rather than simply a formatting exercise. Small inconsistencies can affect the results of later calculations.

Removing Duplicate Records

A duplicate transaction was also identified in the dataset. The same Order ID and transaction details appeared more than once.
Before removing a duplicate, the records should be checked carefully. A repeated customer name does not automatically mean a duplicate because one customer may place several legitimate orders. In this case, the repeated record represents the same transaction and it can be removed.
Excel provides the Remove Duplicates feature under the Data tab. Removing confirmed duplicates prevents the same transaction from being counted twice and helps keep totals accurate.

Working with Dates

Dates are important in a sales dataset because they allow transactions to be sorted and filtered by time.All dates should be stored consistently and recognized by Excel as actual date values.
The Order Date column was standardized so that all dates use the same display format. Once the dates are consistent, the records can be arranged chronologically and filtered by date more reliably.
This becomes useful when analyzing monthly or yearly sales trends.

Creating a Sales Calculation

After cleaning the basic information, a calculated column can be added to the dataset.
The new column is called Sales (KES). It shows the total value of each order.
The calculation is
=F2*G2
If Quantity is stored in F2 and Unit Price is stored in G2, Excel multiplies the two values to calculate the sales amount.
The formula can then be copied down the column. This is much faster than calculating every row manually.
It also means that if the quantity or price changes, the sales value can update automatically.

Using Basic Excel Functions

Excel provides many functions for summarizing data.

• The SUM() function adds values together. For example: =SUM(I2:I16) calculates the total sales.
• The AVERAGE()function calculates the average value. =AVERAGE(I2:I16)
• The MIN() function identifies the smallest value, while MAX() identifies the largest.
• The COUNT() function counts cells containing numerical values.
• The COUNTA() function counts all non blank cells.
• The COUNTBLANK function counts all blanks.

How to Use a Function:

1. Click on an empty cell (e.g., B6)
2. Type =SUM(B1:B5)
3. Press Enter
4. Excel returns the total sum of B1 to B5 Examples:
5. =SUM(A2:A4) → Adds numbers in A2, A3, A4 2.=AVERAGE(B1:B3) → Returns the average
6. =MAX(C1:C5) → Finds the largest number

These functions provide a quick way to understand a dataset without manually calculating each result.

Relative and Absolute Cell References

Cell references determine which cells a formula uses.
A reference such as A1 is a relative reference. When the formula is copied to another cell, the reference normally changes.
An absolute reference uses dollar signs, such as $A$1. This reference remains fixed when the formula is copied.
Absolute references are useful when one value needs to be used repeatedly. For example, if a tax rate is stored in one cell, $A$1 can be used in several formulas without the reference changing.
Understanding cell references is important because incorrect references can produce incorrect calculations.

Conditional Formatting

Conditional Formatting provides a visual way to identify important values.
For example, sales values above a certain amount can be highlighted. Lower values can also be identified using a different rule.
Conditional Formatting is useful when checking a dataset because unusual values can be spotted quickly.
It does not change the actual values. Instead, it changes how the cells are displayed based on a selected condition.

Data Validation

Data Validation helps control what can be entered into a cell.
For example, the Status column can contain a dropdown list with three options

• Completed
• Pending
• Cancelled This prevents users from entering different versions such as complete _or _done. Data Validation is useful because preventing inconsistent data is easier than cleaning it later. It can also be used to restrict numbers, dates or the length of text entered into a cell.

From Raw Data to Clean Data

The difference between the raw and cleaned datasets may appear small, but it is important.
The raw dataset contains inconsistent customer names, category formats, unnecessary spaces, and a duplicate record.
The cleaned dataset has standardized names and categories, consistent dates, the duplicate removed, and a new Sales column containing calculated values.
The cleaned version is therefore easier to filter, summarize and analyze.
This process shows that data preparation should happen before conclusions are drawn from a dataset.

Cleaned data

Importance of Data Cleaning

Data cleaning is important because errors in the original dataset can affect every stage that follows.
A duplicate transaction can increase total sales. An inconsistent category can cause sales to be split between two groups. Extra spaces can prevent values from matching correctly.
Cleaning therefore improves the reliability of the analysis.
It also makes the dataset easier for another person to understand. A clean spreadsheet has consistent headings, values, formats, and structures.

Conclusion

Excel provides a practical starting point for data analytics because it combines data organization, cleaning, calculations and basic analysis in one tool.
The sales dataset demonstrates how a raw spreadsheet can be improved through a series of simple steps. Data is first entered and organized, followed by formatting, sorting and filtering. Problems such as unnecessary spaces, inconsistent capitalization, duplicate records and inconsistent categories are then corrected.
Functions such as TRIM(), PROPER(), SUM(), AVERAGE(), MIN() and MAX() make it possible to clean and summarize the information efficiently. Features such as Conditional Formatting, Data Validation, Freeze Panes and Remove Duplicates provide additional support.

The main lesson is that good analysis depends on good data. Cleaning may seem like a basic step but it has a direct effect on the accuracy of the final results. With these Excel skills raw information can be transformed into a structured and reliable dataset that is ready for further analysis.