When people hear "Excel," they usually picture something simple; rows, columns, maybe a few totals. What I didn't expect going into this week was how much of actual data analytics happens before any analysis even starts. Most of the work is just getting the data into a state where it can be trusted. This week was about learning that groundwork: how to move around a spreadsheet efficiently, how to keep data organized as it grows, and how to clean up the small inconsistencies that quietly break formulas and calculations later.
To make this practical instead of theoretical, I worked with a sample sales dataset, 40 order records with customer names, products, quantities, prices, dates, regions, and order status. It wasn't a clean dataset on purpose. Names were inconsistently capitalized, some had extra spaces, some prices were stored as text with currency labels attached, a few cells were empty, and two rows were accidental duplicates. In other words, it looked like real data, because real data is almost never clean the first time you see it.
Excel Basics
Before cleaning anything, I had to get comfortable with how Excel actually organizes information. A spreadsheet isn't just a table, it's a grid of individually addressable cells, where every cell has a coordinate (like B7), and that coordinate is what formulas and functions actually reference. That single idea is the foundation for everything else: sorting, filtering, formulas, formatting, all of it works because Excel knows exactly where each piece of data lives.
- Rows are records, columns are fields. Each row in my dataset was one order; each column was one attribute of that order (Product, Quantity, Price, etc.). Keeping that structure consistent is what makes a spreadsheet usable for analysis instead of just a place to dump numbers.
- Cell references let formulas move with your data. Referencing F2 in a formula, then dragging it down to F40, automatically adjusts to F3, F4, and so on, Excel handles the relative positioning for you.
- Formatting. How a number is formatted (currency, date, plain number) affects how Excel treats it, not just how it looks.
Freezing Panes
With 40 rows of data, it doesn't take long before your headers scroll out of view, and once that happens, it's easy to misread which column is which. Freezing panes solves this by locking the header row and the first column in place while the rest of the sheet scrolls underneath it.
I did this through View → Freeze Panes → Freeze Top Row, and it immediately made the sheet easier to navigate — I could scroll through all 40 orders and still see exactly which column I was looking at.
Sorting and Filtering
Once the headers were locked in place, the next step was organizing them in a way that reveals patterns..
- Sorting rearranges the entire dataset based on one or more columns. I sorted my sales data by Region first, using Data → Sort, which grouped all orders from the same region together instead of leaving them scattered in the order they were entered.
- Filtering, on the other hand, doesn't rearrange anything,it temporarily hides rows that don't match a condition you set, so you can focus on a subset without losing the rest of the data. I applied a filter using Ctrl + Shift + L or HOME -> filter & sort, which added dropdown arrows to each header. From there, I filtered the Status column to show only "Cancelled" orders, which instantly narrowed 40 rows down to a handful, useful if I wanted to investigate cancellations specifically without deleting or reorganizing anything else.
Cleaning Text
Scrolling through the Customer Name column also exposed a different problem that the values weren't consistent. Some names were fully uppercase (ALICE WAMBUI), some had random casing (grace Njeri), and a few had extra spaces hiding inside them.
Two functions fixed this:
TRIM() removes extra spaces, leading, trailing, and repeated spaces between words. Leaving only single spaces between words. =TRIM(B2) cleaned up entries like " Samuel Mwangi".
PROPER() capitalizes the first letter of each word and lowercases the rest, standardizing casing. =PROPER(B2) turned "ALICE wambui" into "Alice Wambui".
I combined both in a single formula
=PROPER(TRIM(B2))
After applying this down the column, I copied the results and used Paste Special → Values to replace the original messy column with the cleaned, static text, otherwise the formulas would keep referencing the original (still-messy) column.
Removing Duplicate Rows
Two orders, 1006 and 1013, appeared twice each, with completely identical values across every column. Duplicate rows like this are especially misleading in a sales dataset because they don't just clutter the sheet, they distort any totals or counts calculated from it. A SUM() of the Total column, or a count of orders per region, would both come out inflated if duplicates aren't caught first.
Excel has a built-in tool for this rather than requiring a manual scan: Data → Remove Duplicates. Selecting the full dataset and running this tool opens a dialog where you choose which columns to check for duplication.
Basic Calculations, Formulas, and AutoSum
The Total column had been left empty on purpose, meant to be calculated as Quantity × Unit Price for each order. Rather than typing that out manually 40 times, I used a formula referencing the two relevant cells:
=E2*F2
I then dragged this formula down the entire column, and Excel automatically adjusted the cell references for each row (E3*F3, E4*F4, and so on), calculating every order's total in seconds.
For a grand total across all orders, I used AutoSum, one of Excel's most basic but genuinely time-saving tools. Selecting the cell directly below the last Total value and pressing Alt + = automatically inserted a SUM() formula covering the entire column, without me needing to type the range manually:
=SUM(G2:G40)
Basic arithmetic operators worked exactly as expected once the data was clean: + for addition, - for subtraction, * for multiplication, / for division.
Top comments (0)