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

Introduction to Excel

Data Cleaning is the streamlining of data that is in disarray and poor in quality to make it more digestible for analysis. Excel has features thatv allow you to create reports for intepreting data e.g. Dashboards, Pivot tables amongst others.
Data does not necessarily have to be in numbers, it can also be in text format.

Running Excel

To open excel, go to the search bar, click on it, search Excel. Once highlighted, click on the open icon.
The landing page is made up of three main areas namely:

1.The Home Area: Is the main landing section which allows one to create a new workbook. It also enlists the recently opened files.

1. The New Area:This is where you get to open a new blank excel file.

1. The Open Area Allows you to browse from your PC and retrieve any file you may need to upload/open in excel. ** **

When you create a blank workbook, it directs you to the homepage.

Labelling of An Excel Worksheet

• The Excel Ribbon: An area that has all the tool bars to be used in excel. It is the section that allows one to access the different tools to be used in excel. For example, the home section of the excel ribbon has different rules for different reasons clustered into: Clipboard;Font;Alignment;Number;Styles;Cells;Editing & Add-Ins.

The Insert section is crucial for reporting especially with the tables and chart icons.

The Draw section presents drawing tools while the Formulas section is a hub of mathematical and text functions.

Under Data section, you can connect your excel to a database and pull data from it. You can also forecast and present case scenarios under What-If Analysis.

Review panel is useful mostly to Informatio Analysts.It has the spelling and grammar checker function that allows for proofreading a dataset. You can also protect sheet to limit the number of viewers/access to the file.

Common Terminologies Associated With Excel

1. Cell - A single entry box.
2. Cell Address - A unique identifier of a particular cell.
3. Name box - This is where a cell address is found. Columns are named alphabetically while Rows are named numerically in excel.
4. A formula bar is where formulas are written and are always preceded by an equal sign.

Introduction to Excel Cleaning

Dates, Numbers and Boolean data entries are typically aligned to the right whereas text are usually aligned to the left.
Quick Access toolbar contains the icons for un-doing,re-doing and saving.
N/B - Excel does not acknowledge dates outside a conventional calender year.

Range

This is a cell or a group of multiple cells highlighted together on a worksheet. A range doesn't always have to be linear.

Formatting

Transforms the data to make it more professional and presentable. To autofit, go to the Home label on the ribbon, point to the format option and from the dropdown menu select autofit column width.

Wrapping a text

To wrap text in Excel so it fits inside a cell, select the cell and click the Wrap Text button on the Home tab.

Data Validation

Simply refers to conditioning excel to only accept certain prescribed instructions. To validate:

• Select the column to validate
• Go to the data field of the ribbon
• Under data tools, click on the tick icon dropdown menu
• Select data validation from the pop-up menu
• Under allow, select list from the dropdown menu
• Under source, list the column options separated by a comma the press okay.

Once a column is validated, no other entry can be made to a cell within within the column other than the listed options.

Validation can be made for numerical values as well including ranges.

Conditional Formatting

A tool that changes the appearance of cells based on the conditions you define. It does not alter the data itself but how it is presented. It can be used to:

• Highlight outliers.
• Visualize trends using data bars.
• To identify missing information.
• To detect patterns.
• To improve data readability.

Functions

Functions are built-in formulas used to calculate results automatically. Operators such as Addition, Subtraction, Multiplication, Division, Exponent can be easily performed by these formulas.
Core Statistical and Mathematical Functions:

=SUM: Totals numeric values within a defined range.

=AVERAGE: Determines the arithmetic mean of a dataset.

=MIN/MAX: Ranks the lowest and highest values in a range, highlighting extreme variables.

=MEDIAN: Determines the middle value useful in mitigating the perceptive skew of outliers.

=MODE: States the most frequently occurring value in an array.

=PRODUCT: Multiplies all numbers as given.

=SQRT:Establishes the positive square root of a specific value.

=POWER:Raises a base number to a specified exponential power.

Date and Time Functions

=TODAY(): Displays the current system date, updating automatically realtime.
=NOW(): Generates the exact current date and precise timestamp.
=YEAR(date)/=MONTH(date)/=DAY(date): Extracts the isolated integer value of the year, month, or day from a target date cell.
=DATE(year, month, day): Synthesizes individual year, month, and day integers back into a structured Excel date format.
=NETWORKDAYS(start_date, end_date, [holidays]): Computes the exact number of working days between two dates, automatically excluding weekends and optionally accounting for a custom list of holidays.

Text functions for Data Cleaning

Can be used to eliminate extra spaces in a cell. The find and replace prompt is the most basic and common one however,there exists a plethora of these including:

TRIM: =TRIM(text) removes all leading spaces, trailing spaces, and redundant consecutive spaces from a text string.
LOWER: =LOWER(text) changes every character in a text string into lowercase.
UPPER: =UPPER(text) converts text entirely into uppercase characters.
PROPER: =PROPER(text) creates proper text by capitalizing the first letter of each word and lowering the remaining letters.
LEN: =LEN(text) counts the total character length of a text in a cell including spaces and punctuation. This is highly useful for validating structural lengths like phone numbers or postal codes.
LEFT/RIGHT: = Extracts a specified number of characters starting from the left or right of a cell.
MID: = Extracts a character segment from the middle of a dataset based on a designated starting position and character count.
CONCATENATE/CONCAT:Brings multiple text data cells together into a single, cohesive text block.

Pivot Tables and Charts

A pivot table is basically a summary table which sums large datasets by grouping,counting,averaging or aggregating based on the selected fields. It does not alter the raw data, it only presents it in a different format.It is key to note that the abscence of headers creates breaks.

Creating a Pivot Table

Under the insert icon on the ribbon,click on Pivot Table.
From the dropdown, click on OK to create a New Worksheet.

Rows is for grouping categories, Values is for what is being calculated. Columns is for creating comparative layers or subcategorizing your dataset further.You can drag as many columns into the filter's section to filter a field.

Instead of filters, it is advisable to use Slicers as they are more interactive and visible.

Conclusion

Mastering Excel is a journey that starts from foundational data cleaning to advanced dashboard reporting. As detailed across these modules, data integrity is the bedrock of all reliable analytics. By using text cleaning functions like TRIM, PROPER, and case adjusters, you transform disorganized datasets into accurate, structured information. Implementing strict Data Validation rules protects your workspace from future entry errors, while Conditional Formatting brings key visual patterns and outliers to light without altering the underlying numbers.The true analytical power of Excel emerges when you summarize this clean data using Pivot Tables and Pivot Charts.

Moving beyond traditional dropdown filters to interactive Slicers allows you to build dynamic, presentation-ready dashboards. These tools do more than just summarize rows and columns; they turn raw data into a responsive, visual narrative. With these core skills—from initial cell formatting to multi-table report connections—you can confidently transform complex data environments into clear, actionable business insights.