Introduction

3rd week into Data Science and Analytics with Lux Dev Academy. This week we started a two-week learning module on the use of Microsoft Excel to clean and analyze data.

In the modern data-driven landscape, organizational decision-making hinges on the ability to transform raw, unstructured inputs into clear and actionable insights.

"Garbage in, garbage out" (GIGO) remains the defining axiom of data engineering. Statistical models, pivot tables, and visualization dashboards are only as reliable as the underlying data clean-up routines. Data cleaning is not a preliminary administrative chore; it is the core analytical step where data validity and structural integrity are established.

Excel Use Cases

Some of the common area/scopes where Excel is used are:

1. Data Entry
2. Data Analysis
3. Data Cleaning
4. Reporting (using dashboards & Pivots).

Navigating the Excel Interface & Understanding Data Types

Navigating the Excel Interface

We started by learning most if not all the components and features of Microsoft Excel. Below images show most important sections of the Microsoft excel, indicated on an open blank sheet.

The core interface components are:

• Ribbon: this is the top navigation toolbar organize into tabs which include Home, Insert, Data, Formulas, Reviews and Help.
• Formula bar: Displays and also can be used to edit the cell value or the underlying formula active in a selected cell.
• Name Box: Displays the name or the defined name of the currently selected cell or range.
• Worksheet Grid: This is the primary worksheet made up of rows(numbered 1 - 10000...) and columns(lettered A-X...)
• ** Cells**: this is the intersection of a row and a column where data, text, or formulas are entered.
• Sheet Tabs: this a located at the bottom and enables one to switch between different worksheets within a single excel workbook file.
• Status Bar: this is bar at the bottom of the worksheet that shows the real-time information or calculations like count of values in selected cells, sum of values in a selected range. Highlighted in yellow in the below image.

Other important sections of excel can be found once you click the file tab at the top left corner. This sections include Home(button to return to worksheet), New(for creating new workbook), Open (to open an existing excel file from the laptop/host machine),info, print, export ( can be used to convert the file into other formats like pdf), close, recent, Favorites and more. This appears as shown in the image below.

Data Types

Excel categorizes data entering a cell into distinct underlying visuals and mathematical data types. Understanding how Excel stores and processes these data types is important since mismatched data types result in execution errors during analysis.
Some the Excel data types include the following:

• Numeric(Values); these are right-aligned and are floating-point numbers.
• Text(Strings); these are left-aligned and are alphanumeric sequences(cannot be evaluated mathematically)
• Date & Times; also Right-aligned and are sequential serial numbers where Day 1 is 1/1/1900. Time is represented by fractional values.
• Boolean(Logical); they are centered on a cell and restricted to uppercase binary values: TRUE or FALSE.
• Formulas & Errors; expressions starting with =. They yield dynamic data or error states.

Conditional Formatting

Learned about conditional formatting which is a feature of Microsoft Excel that automatically changes how a cell looks based on the data inside it. Instead of manually applying colors or styles to individual cells, you set rules (conditions) and the software formats the cells automatically when those conditions are met.
Conditional formatting can be applied on both text and numbers. When working with conditional format there as many options and functions provided in Microsoft Excel. Some of this options are highlighted in the below image.

Sample of data with conditional formatting applied is as shown below.

How it works: You select the cells you want to monitor, define the rule, and the forma updates automatically.

One important function during conditional formatting of data in Excel is the Find and Replace tool. You can access it instantly using keyboard shortcuts:
- Ctrl + F opens the Find tab directly.
- Ctrl + H opens the Replace tab directly.

Below is how it appears once open in an Excel sheet.

Formulas, Functions, and References: The Calculation Engine.

Formulas represent the foundational syntax of Excel analytics. Every formula begins with an equals sign =, signaling to the calculation engine to evaluate the subsequent expression rather than treating it as a literal string.

Mathematical Operators and Order of Operations.

Excel adheres to the standard algebraic order of operations PEMDAS: Parentheses, Exponents, Multiplication/Division, Addition/Subtraction. While using Excel we face situations where we use one or all of these operations and there its very crucial to understand how to apply and use them properly. When building analytical sheets, one is required to structure complex algebraic expressions logically. The symbols used for the different operations are as shown below.

• Addition & Subtraction: + and -
• Multiplication & Division: * and /
• Exponentiation:^ (e.g., =10^2 yields 100)
• Comparison Operators: =, <> (Not equal to), >, <, >=, <=

Mastering Cell References: Relative vs. Absolute vs. Mixed

Understanding cell referencing is very important for one to become proficient data analyst. When formulas are copied from one row or columns to another, cell references adapt based on the presence of dollar sign $ anchors.

• Relative References: Cell references are relative by default. For a formula containing =A1+10 in cell B1 is dragged down to B2, it automatically shifts to =A2+10. The formula stores the relative offset rather than a fixed address.
  • Absolute References: Adding a dollar sign $ before both the column letter and row numbers locks the reference completely. For example if =$A$4*.001 is copied from cell L1 down to L10, every single cell will evaluate against cell A4. This is very essential for central parameters like discount factors, tax rates or KPI targets.
  • Mixed References: This lock either the row or the column, leaving the other free to adjust dynamically. eg $A10 and B$10.

Core Statistic Functions

The foundation of initial exploratory data analysis relies on fundamental aggregation functions:

• =SUM(range): used to calculate the total arithmetic sum of a numeric range.
• =AVERAGE(range): used to compute the arithmetic mean.
• =COUNT(range): used to count cells containing numeric values only.
• =COUNTA(range): used to count non-empty cells (includes text, numbers, logical values TRUE/FALSE and errors).
• =COUNTBLANK(range): Counts empty cells within a specified range.
• =MIN(range) or =MAX(range): used get the minimum or maximum value in a range of values. Other statistical functions we covered included: =COUNTIF()& =COUNTIFS(), =SUMIF() & =SUMIFS(), =AVERAGEIF() and =AVERAGEIFS().

Structured Data: Working with Excel Tables.

The single most impactful practice for modern analytics in Excel is converting raw data blocks into official Excel Tables(Keyboard Shortcut: Ctrl + T).
Some of the advantages of using Structured data are:

• Dynamic Auto-Expansion: When new rows or columns are added directly adjacent to a Table, the table automatically expands to encompass the new data, automatically extending formulas, formatting, and data validation rules.
• Structured references.
• Calculated Columns: Entering a formula in a single cell of a table column automatically fills down the entire column instantly.
• Integrated Filter Controls: Automatic addition of sorting and filtering drop-downs on the header row

Data Preparation & Data Cleaning Techniques.

Data cleaning is the process of detecting, correcting or removing corrupt, inaccurate, incomplete, improperly formatted or duplicate records from a dataset. Below are some of the methodologies we covered for execution with Excel.

Text Cleaning & Manipulation Functions

Uncleaned text files often contain leading or trailing whitespaces, non-breaking spaces, merged text strings or inconsistent casing.
Some of the functions for cleaning text we covered are:

• =TRIM(text): removes all leading & trailing spaces and reduces multiple internal spaces to a single space.
• =CLEAN(text): removes all non-printable ASCII characters.
• =UPPER(text) and =LOWER(text): standardizes text strings into uniform uppercase or lowercase for case-sensitive comparisons.
• =PROPER(text): Capitalizes the first letter of each word in a text string. It is useful for names and addresses.
• =LEN(text): Used to count the total characters in a text string. One of the use cases would be when validating Fixed-length IDs. These functions can be used together when need arises. e.g =LOWER(CONCAT(text1,text2,text3,..))

Substring Extraction: LEFT, RIGHT, MID, and FIND.

Data fields frequently combine multiple pieces of data into single alphanumeric codes, for example Transaction ID is TXN-88492-2026. To extract individual variables, you combine string positional functions:

• =LEFT(text, [number of characters]): used to extract characters from the start of a string.
• =RIGHT(text, [number of characters]): used to extract characters from the end of a string.
• =MID(text, start number, number of characters): used to extracts characters from any starting position within the string.
• =FIND(find_text, within_text): Returns the 1-based index position of a specific character sequence and it is case-sensitive.

Conclusion

Mastering Microsoft Excel for data analytics is not about memorizing isolated functions but it is about building robust, repeatable data processing pipelines. We established the baseline mechanism required to operate within the spreadsheet efficiently. understanding cell layout, controlling relative and absolute reference framing, using official Excel Tables, and applying string extraction and text-cleaning functions to sanitize messy real-world inputs.

_ Never replace blank cells with guesses but you can replace them with a keyword like "Unknown"._