Every week, somewhere in the world, a data scientist opens a terminal, fires up Python, and writes elegant code to analyze a dataset.
And every week, in even far greater numbers, someone opens Excel — and gets the same job done before lunch!
I did not anticipate to write that sentence when I started my data science training at LuxDevHQ.
I came in ready to learn Python, obsessed with the idea of writing code that crunched millions of rows in milliseconds.
Excel felt like the thing you used before you knew better. The tool you graduated from.
Then my instructor dropped a 7,000-row sales dataset on my screen and said: "Analyze this. You have two hours."
I reached for Python. I spent forty minutes setting up the environment, importing libraries, and debugging a pandas error I had never seen before.
My classmate next to me who had quietly opened Excel was already building her second PivotTable!
She finished in ninety minutes. Her output was clean, visual, and ready to present.
Mine was still a stack of error messages.
That day, reset my entire perspective. . .
Excel is not the tool you use before you know better.
It is the tool professionals reach for when they need answers fast, when data lives in a spreadsheet and needs to stay there, and when the person waiting for your findings does not know what a DataFrame is!
In this article, I walk you through what Excel actually is, how it shows up in real-world data work. Delving thru features and formulas I have been learning at LuxDevHQ in the same logical order you would actually use them when working with real data.
Explaining them the way I wish it had been explained to me when I started.
What Is Excel, Really?
Microsoft Excel is a spreadsheet application developed by Microsoft and part of the Microsoft Office and its 365 suite.
At its most basic level, it organizes data into a grid of rows and columns, forming what are called cells.
Each cell can hold a value. Could be a number, text, a date, or a formula.
But calling Excel just a "spreadsheet tool" would be like calling a Swiss Army knife just a "small blade."
Excel is a full-featured environment for:
• Storing and organizing data
• Cleaning and transforming raw data
• Performing statistical and mathematical analysis
• Visualizing data through charts and dashboards
• Automating repetitive tasks with formulas and macros
It is available on Windows, Mac, and as a web application, making it one of the most accessible data tools in existence.
More importantly, it is used across virtually every industry; finance, healthcare, retail, logistics, human resources, marketing, and beyond.
How Does Excel Still Matters in a World of Python and SQL?
That is a fair question. With tools like Python, R, SQL, and Power BI becoming standard in data science, why are we still talking about Excel?
Here is the honest answer: most of the world's business data still lives in spreadsheets.
When a sales manager hand you a .xlsx file and needs answers by end of day, you are not spinning up a Jupyter notebook.
You open Excel, and you deliver.
Beyond accessibility, Excel has some unique advantages:
• Low entry barrier — almost every computer in a professional setting has it
• No setup or environment required — you open it and start working
• Great for quick Exploratory Data Analysis (EDA)
• Excellent for communicating findings to non-technical stakeholders who are comfortable with spreadsheets
• Bridges the gap between raw data and business decisions
At LuxDevHQ, we were taught that a good data scientist is a versatile one.
Excel is not a competitor to Python — it is a complement.
Real-World Use Cases of Excel in Data Analysis
Before diving into the features, let me paint a picture of where you will actually encounter Excel in the field.
1. Sales and Revenue Analysis
Sales teams use Excel to track monthly revenue, compare performance across regions, calculate growth rates, and forecast future sales. Analysts build dashboards that update dynamically as new data is entered.
2. HR and Workforce Analytics
HR departments use Excel to manage employee records, calculate attrition rates, analyze compensation data, and track recruitment pipelines. Conditional formatting helps flag outliers — like employees’ overdue for a performance review.
3. Financial Modelling
In finance, Excel is the industry standard. Analysts build complex models involving projections, scenario analysis, net present value calculations, and cash flow statements — all inside a spreadsheet.
4. Healthcare Data Tracking
Hospitals and clinics use Excel to monitor patient data, track inventory of medical supplies, analyze readmission rates, and manage appointment scheduling data.
5. Marketing Campaign Analysis
Marketers use Excel to analyze A/B test results, calculate conversion rates, track cost-per-acquisition, and measure return on investment (ROI) across campaigns.
In all of these scenarios, the workflow is roughly the same: get the data → clean it → analyze it → visualize it → communicate findings.
That is the pipeline I want to walk you through now, using the features I have learned so far.
Step 1: Getting Familiar with the Data
When you receive a dataset, the first thing you do is understand its structure.
• How many rows?
• What columns exist?
• Are there blanks?
• What data types are present?
In Excel, a few keyboard shortcuts become your immediately best c jumps to the last used cell, telling you the size of your dataset
• Ctrl + Shift + L — toggles filters on your headers so you can quickly sort and explore
• Freeze Panes (View → Freeze Top Row) — keeps your headers visible as you scroll through thousands of rows
These seem small, but they immediately tell you what you are working with.
Step 2: Cleaning the Data
Raw data is almost never analysis-ready.
It has duplicates, inconsistent text, extra spaces, missing values, and wrong formats. Hence data cleaning being where analysts spend most of their time.
And you guessed right, Excel has excellent tools for this.
TRIM() — Removing Extra Spaces
A common issue is hidden spaces in text fields that cause lookups and filters to fail silently.
=TRIM(A2)
This removes all leading, trailing, and excess internal spaces from the cell A2. Simple, but critical.
PROPER(), UPPER(), LOWER() — Standardizing Text Case
If one row says "lagos" and another says "LAGOS" and a third says "Lagos," Excel treats them as three different values.
These functions standardize text:
=PROPER(A2) → "Lagos"
=UPPER(A2) → "LAGOS"
=LOWER(A2) → "lagos"
IFERROR() — Handling Errors Gracefully
When a formula returns an error (like dividing by zero), it can break the downstream calculations. IFERROR wraps any formula and returns a fallback value instead of an error:
=IFERROR(B2/C2, 0)
This means: divide value in B2 by value in C2, but if that throws an error, return 0 instead.
Remove Duplicates
Under the Data tab, "Remove Duplicates" is a one-click tool that scans selected columns and deletes duplicate rows.
Always done after first creating a backup of your raw data.
Step 3: Exploring and Summarizing Data
Once the data is clean, analysis begins. The goal here is to extract meaningful summaries.
SUM(), AVERAGE(), COUNT(), MIN(), MAX()
These are the foundational aggregate functions every analyst uses daily:
=SUM(D2:D100) → Total revenue
=AVERAGE(D2:D100) → Average revenue per transaction
=COUNT(D2:D100) → Number of transactions
=MIN(D2:D100) → Lowest single transaction
=MAX(D2:D100) → Highest single transaction
COUNTIF() and SUMIF() — Conditional Aggregation
Here is where things get interesting.
In the real world, you rarely want to sum everything.
You want to sum revenue for a specific region, or count how many sales came from a given product.
COUNTIF counts cells that meet a condition:
=COUNTIF(C2:C100, "North")
→ "How many transactions came from the North region?"
SUMIF sums cells where a related column meets a condition:
=SUMIF(C2:C100, "North", D2:D100)
→ "What is the total revenue from the North region?"
These functions are workhorses in sales and marketing analysis.
SUMIFS() and COUNTIFS() — Multiple Conditions
When you need to filter by more than one criterion:
=SUMIFS(D2:D100, C2:C100, "North", E2:E100, "Q1")
→ "Total revenue from the North region in Q1"
Step 4: Looking Up and Combining Data
Real-world data rarely lives in one sheet. You might have a sales table and a separate customer details table, and you need to bring them together.
This is where lookup functions shine.
VLOOKUP() — Vertical Lookup
VLOOKUP searches for a value in the first column of a range and returns a corresponding value from another column in the same row.
=VLOOKUP(A2, CustomerTable, 3, FALSE)
Breaking this down:
• A2 — the value to look for (e.g., a Customer ID)
• CustomerTable — the range or table to search in
• 3 — return the value from the 3rd column of that range
•FALSE — find an exact match
Real-world example: You have a list of transactions with Customer IDs, and a separate customer table with names and email addresses.
VLOOKUP pulls the customer’s name into your transactions sheet automatically.
XLOOKUP () — The Modern Upgrade
XLOOKUP is the newer, more flexible replacement for VLOOKUP.
It is cleaner to write, works in both directions (not just left-to-right), and handles missing values natively:
=XLOOKUP(A2, CustomerTable[CustomerID], CustomerTable[CustomerName], "Not Found")
Step 5: Analyzing Patterns with PivotTables
If I had to name the single most powerful Excel feature for data analysis, it would be PivotTables.
A PivotTable lets you instantly summarize, group, filter, and cross-tabulate data — without writing a single formula.
Here is the scenario: you have 10,000 rows of sales transactions. You want to know total revenue by region, broken down by product category, for each month.
Writing formulas for that would take a long time. And I mean, a very long time.
A PivotTable does it in under 60 seconds.
How to create one:
- Click anywhere inside your clean dataset
- Go to Insert → PivotTable
- Choose where to place it (new sheet recommended)
- In the PivotTable Fields panel: o Drag Region to Rows o Drag Product Category to Columns o Drag Revenue to Values (set to Sum) o Drag Month to Filters
PivotTables are also dynamic. If the underlying data changes, you right-click and hit Refresh — and all your summaries instantly update.
Step 6: Visualizing Data
Numbers alone do not communicate.
Charts make patterns visible and findings convincing. Excel's charting tools are robust enough for most business reporting needs.
Choosing the Right Chart Type
Goal Chart Type
Compare categories Bar or Column Chart
Show trends over time Line Chart
Show proportions Pie or Donut Chart
Show distribution Histogram
Show relationships Scatter Plot
Building a Chart from a PivotTable
Once your PivotTable is ready, you can insert a PivotChart directly from it.
This creates a chart that stays linked to the PivotTable — when you change the filter, the chart updates too.
Conditional Formatting — Visual Analysis Without Charts
For quick pattern recognition within a table, Conditional Formatting applies color scales, data bars, or icon sets to cells based on their values.
Example: Apply a green-to-red color scale on the Revenue column — highest values glow green, lowest glow red. In seconds, you can visually identify your best- and worst-performing segments.
Step 7: Making Data Dynamic with Named Ranges and Data Validation
Named Ranges
Instead of referring to Sheet1!$D$2:$D$100 in every formula, you can name that range "Revenue" and use it like this:
=SUM(Revenue)
=AVERAGE(Revenue)
This makes formulas readable and easy to maintain — especially important when handing work to a colleague.
Data Validation
Data validation controls what can be entered into a cell. Common uses:
• Allowing only numbers within a specific range
• Creating a dropdown list to ensure consistent entries (e.g., only "North", "South", "East", "West" can be entered in the Region column)
This is crucial for maintaining data integrity — bad inputs are the root cause of most analysis errors.
Putting It All Together: A Mini Case Study
Let me close with a real scenario from a practice exercise at LuxDevHQ.
The Problem: A retail company gave us 12 months of transaction data — over 8,000 rows covering sales reps, regions, product lines, and revenue figures.
The question: Which region and product combination drove the most revenue in H2, and how does that compare to H1?
The Workflow:
Opened the raw .xlsx file and used Ctrl + End to confirm the dataset size
Cleaned the data — used TRIM () to fix text fields, removed duplicates, standardized region names with PROPER ()
Created a helper column using SUMIFS to calculate H1 and H2 revenue per row
Built a PivotTable with Region on rows, Product on columns, and Revenue (Sum) as values — filtered by half-year period
Added Conditional Formatting to highlight top-performing cells
Created a PivotChart (clustered bar chart) to visualize the comparison
Wrote a 5-line summary of findings directly below the chart, ready to be shared with the team
Total time: under 2 hours. And the output was clear, professional, and actionable.
Final Thoughts
Excel is not a beginner tool that you grow out of. It is a professional tool that grows with you.
The further you go in data analytics and data science, the more you appreciate having a solid foundation in Excel — because it trains you to think about data: how it is structured, how it should be cleaned, how summaries are built, and how stories are told through numbers.
As a student at LuxDevHQ, learning Excel alongside Python and SQL has shown me that the best analysts are not defined by which tool they use, but by how clearly, they can move from raw data to insight — whatever the tool in hand.
The Tool Does Not Make the Analyst — But It Reveals Them
The formulas and features in this article are just the beginning. But master these, and you will already be thinking like a data analyst.
The thing nobody tells you when you start learning data science: the tools are not the point.
The thinking is.
Excel does not make you a great analyst. Neither does Python. Neither does SQL.
What makes you a great analyst is the ability to look at a messy pile of numbers and ask the right questions — then pursue the answers with whatever is in your hands.
And that is what Excel does do, and better than almost anything else at this stage: it keeps you honest.
Every step is visible. Every formula is traceable. Every error is right there, staring at you from a cell, refusing to be hidden behind a stack trace or a silent script failure.
Excel forces you to understand your data before you summarize it, and understand your summary before you visualize it.
That discipline — that sequential, deliberate way of working — is not a limitation of the tool.
It is a lesson the tool is teaching you.
My classmate who finished her PivotTable in ninety minutes while I was drowning in pandas errors was not better at Excel than I was at Python.
She was better at thinking through data.
Excel just made that thinking visible.
I am still learning. We all are.
But every SUMIFS formula I write, every VLOOKUP I debug, every PivotTable I build from a chaotic spreadsheet — I am not just producing an output.
I am training a muscle.
*The muscle that asks: *
• What does this data actually say?
• What is it hiding?
• And what decision should it impact?
That muscle does not care what tool you use.
However, if you are just starting out, Excel is one of the best places to build it.
Personal Reflection
Learning Excel has genuinely changed my perception of data.
Earlier, I used to look at numbers as isolated values—figures on a screen without much meaning attached to them. But now, I naturally start asking questions.
- What does this number represent?
- Why does it change?
- What story is it telling?
Working with Excel trained me to slow down and pay attention to structure—how data is organized, how small inconsistencies can affect results, and how a simple formula can reveal something deeper.
Even something as basic as cleaning a dataset made me realize that good analysis starts long before any insights appear.
What stands out the most is how Excel shifted my mindset.
I no longer rush to conclusions.
I Instead explore, test, and verify. I’ve become more curious and more intentional with data.
You could say, learning Excel didn’t just teach me a tool—it changed how I think.
So, the next time someone hands you a .xlsx file and expects answers — do not reach for something fancier.
Open Excel.
Trust the grid. And deliver.
Top comments (0)