Excel: The Foundation of Every Data Scientist's Journey

Introduction

In todays times, raw data is as precious if not more precious than oil, but it is rarely useful alone. it is a foundational tool in the data science field serving as as a primary interface for data organization, cleaning and manipulation.

excel is not just a simple tool as it appears on the surface, it is a unique tool that can be used for various uses when it comes into contact with raw data. It allows users to organize datasets, identify errors, remove duplicates, standardize values, perform calculations and clean unorganized data(dirty data). Once mastered excel becomes a skill on its own hence these skill are the foundation of professional data analytics.

With Raw data being a gold mine across every department in each institution a tool is needed to help facilitate the organization and sorting of the data hence, this is where excel comes in. So we ask ourselves:

What is Excel?

Excel is a tool created by Microsoft that help in simplifying data storage, cleaning and manipulation. Excel is an accumulation of one or more related worksheet. The worksheet is made up of different parts that comprise of it and some of them are:

• Quick Access Toolbar: A small, customizable group of buttons for frequent actions like save, undo and redo
• Ribbon: The main toolbar containing organized command tabs like Home, Insert, and Page Layout.
• Name box: Located on the left below the ribbon, it shows the address of your active cell.
• Formular Bar: The long input line next to the Name Box used to view, type or edit cell formulas and data.
• Active cell: The current, individual box where data, text or formulas are being entered.
• Column: Vertical blocks of cells identified by letters running across the top (A,B,C...).
• Row: Horizontal blocks of cell identified by numbers running down the left side(1,2,3...).
• Worksheet Tab: Located at the bottom left, letting you switch between or add new worksheets.
• Title Bar: The very top bar displaying the current file name and software controls.

Below is an image depicting an excel worksheet and some of its components:-

Common Uses for Excel

• Data Entry and Management: Store lists, contact information, inventory, or raw numbers in an organized grid.
• Calculations and Formulas: Use basic math(addition, subtraction,...) or built-in functions(like SUM or AVERAGE) to process numbers automatically.
• Budgeting and Finance: Track monthly expenses, build financial forecast, and manage business or personal accounts.
• Data Analysis: Sort, filter and summarize large data sets using pivot tables and conditional formatting.
• Project Management: Track timelines, task statuses, team resources, and project costs.

Features of Excel

Excel as a versatile tool is composed of different features that help it execute various commands.
Some features are:-

1. Conditional Formatting: Used to alter, filter and highlight important information.
2. Charts and Graphs: Used as a visual aid in data representation.
3. Worksheets: Used in data organization.
4. Formulas and functions: Used in mathematical calculations. Some example of formulas and functions are;

• =SUM- Use for addition
• =AVERAGE- Used to find the average number in a data set
• =MAX- Used to find highest number
• =MIN- Used to find lowest number
• =COUNT- Used to count number of entries.

The following is an example of formulas and functions as applied in excel;

1. Formula =K3+I9 =B7+F8
2. Function =SUM(I6+F4) =AVERAGE(A4:F4)

Data Manipulation

This is the changing, organizing, structuring, and cleaning or raw data to make it easier to analyze.
Given a small dataset here is how data manipulation is done:
figure 1: An excel worksheet containing raw data with common data issues.

some of the problems it has are extra spaces, different letter cases, missing values, duplicate rows and mixed data formats. A data analyst's first responsibility is to correct these issues.

Data Cleaning

This is the process of finding and fixing errors, duplicates, and missing values in a dataset to make it accurate and ready for analysis.

Text Cleaning with Excel Functions

Excel provides several powerful functions that aid in text cleaning.

TRIM()- Used to remove unnecessary spaces. Formula:
=TRIM(B2)

PROPER()- Converts names into proper capitalization. Formula: =PROPER(B2)

UPPER() and LOWER()- Useful for standardizing codes or emails

=UPPER(D2)
=LOWER(F2)

Handling Missing values

Missing information is detrimental to data analysis and results yielded. There are several ways to fill missing values:

1. Manual correction using Known information.
2. Fill Down for repeated values
3. using find and replace
4. IF formulas for conditional replacements.

=IF(D2="", "Unknown", D2)

replaces blank cells with unknown.

Removing Duplicate Records

Duplicate are repeating records on the same worksheet. They may lead to incorrect or misleading reports.
go to the ribbon go to the Data Tab then Remove duplicates. After removal every entry appears only once.

Formatting Dates

Dates are important for reports, dashboards and trend analysis. Excel stores dates as serial numbers internally, allowing calculations using functions such as:

- TODAY(): Returns the current date, which updates automatically every day.

- NOW(): Returns both the current date and time.

- DATE(year, month, day): Creates a valid Excel date by combining individual year, month, and day values.

- YEAR, MONTH, DAY: Extracts the specific year, month number (1–12), or day number (1–31) from a given date.

Sorting and Filtering Data

Sorting helps organize information, while filtering isolates specific records. Examples;

• Highest to smallest
• Smallest to Highest
• Newest to Oldest
• Oldest to Newest

Creating Calculated Columns

A calculated column generated new information from existing fields.

Final Cleaned Dataset

After applying all cleaning techniques, the dataset becomes reliable and analysis ready.

The cleaned version can now be used for creating PivotTables, dashboards, charts and further statistical analysis.

Data Cleaning in Analytics

Data cleaning takes the most part of an analyst's time because poor-quality data leads to major issues(inaccurate forecast, wrong reports, misleading visualization,...).

Excel fundamentals acts as skills that can be directly transferred to other professional tools such as Power BI, SQL, Python,.... The logic and basics remain the same: identify errors, standardize data, validate quality, and prepare datasets for meaningful analysis.

Key Excel functions learned

Function	Purpose
TRIM()	Remove extra spaces
PROPER()	Proper capitalization
UPPER()	Convert to uppercase
LOWER()	Convert to lowercase
IF()	Replace missing values conditionally
TODAY()	Calculate date differences
*	Calculate total revenue

Excel is an excellent starting point for anyone entering data science and analytics. Although advanced tools like Python and SQL offer greater automation, Excel remains one of the most widely used applications for data preparation in businesses around the world.

These are not merely spreadsheet skills—they are essential analytical practices that ensure data is accurate, consistent, and ready for decision-making.

Career Benefits

Excel accelerates career growth by boosting daily productivity, increasing earning potential and making the user a more competitive candidate across multiple industries.

Excel being a universal accepted application, it creates a competitive edge among users and it also improves efficiency and automation. Hence:

• Save Time: Formula and Functions update data automatically, removing the need for manual recalculations.

• Automate tasks: Macros handle repetitive formatting and report generation, reducing human error.

• Better Organization: Large and small datasets stay clean, structured, and easy to filter in a single workbook.

Access to Specialized Roles

• Finance and Accounting: Mastery enables precise financial modeling, budgeting and forecasting.

• Project Management: Spreadsheets help in tracking timelines, allocate resources and document progress.

• Data Analysis: Power Query and Dashboards turn raw data into strategic business insight.

• Education: Excel help in storing students, teachers, staffs information to ease data storage, manipulation, retrieval and representation.

Conclusion

As a beginner, mastering these Week 1 concepts provides a strong foundation for more advanced topics such as PivotTables, dashboards, Power BI, SQL, and machine learning. The quality of every analysis begins with the quality of the data, and Excel is where that journey starts.

Mastering Excel itself is a skill that can be used to gain different opportunities in different areas and can also be monetized as a skill on its own.

"The goal is to turn data into information, and information into insight.”
— Carly Fiorina, former CEO of Hewlett-Packard.