Excel and Google Sheets for Data Analysis

Spreadsheets are still the most common analysis tool in U.S. offices, and for good reason: for a few thousand rows they're fast, visual and everyone can open the file. In this lesson you'll learn the formulas and features analysts actually use, applied to the course dataset: how many orders came from the West, how much wholesale contributed, what each order earned after product cost, and which orders deserve a closer look. Every formula works in both Excel and Google Sheets unless noted.

1. Set up the table
2. Filter and sort
3. Summarize with formulas
4. Look up related data
5. Highlight what matters

Step 1: Set up the data before you analyze it

Three minutes of setup prevents most spreadsheet errors:

• Format the range as a table. In Excel, click any cell in the data and press Ctrl+T (Cmd+T on a Mac). Tables expand automatically when you add rows, so formulas and pivots never miss new data. In Google Sheets, use Format → Convert to table, or simply keep the data in one contiguous block with a single header row.
• Freeze the header row (View → Freeze) so column names stay visible while you scroll through 200 rows.
• Turn on filters (Data → Filter in Excel, or Data → Create a filter in Sheets).
• Check the types: dates should be real dates, numbers right-aligned (see Lesson 3).

With filters on, you can already answer simple questions: filter channel to Wholesale and the status bar at the bottom of Excel shows the count and sum of the selected revenue cells. Filters are great for exploring. For answers you'll reuse or share, use formulas, because a formula documents exactly what you calculated.

Step 2: Conditional formulas, the analyst's workhorses

The "IFS" family lets you count, add or average only the rows that meet one or more conditions. The pattern is always: the range to calculate, then pairs of (criteria range, criteria).

Two details trip up beginners. First, comparison operators go inside quotes: ">=1000". Second, to compare with a date or a cell, join the operator to it with &. December 2025 revenue looks like this:

=SUMIFS(J2:J201, B2:B201, ">="&DATE(2025,12,1), B2:B201, "<="&DATE(2025,12,31))

Result: $18,319. That matches the December figure you saw in Lesson 2, which is exactly the kind of cross-check you should do whenever two methods should agree.

Step 3: XLOOKUP to bring in data from another table

The sales table has prices but not product costs. Those live in the Products sheet. XLOOKUP finds a value in one column and returns the matching value from another. Add a column K called unit_cost to Sales Clean and type in K2:

=XLOOKUP(E2, Products!$A$2:$A$12, Products!$D$2:$D$12, "not found")

Read it as: "look for this row's product (E2) in the product list, and return the unit cost from the same row." For order SO-10001 (Office Chair) the result is $141. The fourth argument is what to show if there's no match, which is much clearer than an error.

Now add column L, gross_profit, with what the order earned after product cost:

=H2*(I2-K2)

For SO-10001 that's 2 × ($249 − $141) = $216. Fill both formulas down to row 201 and sum column L: the 200 orders generated $42,514 of gross profit on $89,735 of revenue, a gross margin of 47.4%. You've just answered a question the original table couldn't.

Step 4: IF and text functions to create new columns

New columns often come from simple rules. To label each order as business or consumer:

=IF(G2="Wholesale","B2B","Consumer")

A few text and date functions are worth memorizing:

You'll use TRIM and PROPER heavily in the next lesson, when you clean the raw sheet.

Step 5: Dynamic arrays: UNIQUE, FILTER and SORT

In Microsoft 365, Excel 2021+ and Google Sheets, some functions return a whole list that "spills" into the cells below:

• =UNIQUE(C2:C201) lists each region once (4 values). Next to it, a SUMIFS pointing at that list gives a live summary table.
• =FILTER(A2:J201, J2:J201>=3000) returns every order of $3,000 or more. There are 5, all wholesale, and four of them are in the West.
• =SORT(FILTER(A2:J201, J2:J201>=3000), 10, -1) sorts those orders by the 10th column (revenue), largest first. The top one is SO-10186: 21 office chairs to Arizona for $5,229.

Google Sheets also has QUERY, which accepts a SQL-like statement: =QUERY(A1:J201,"select C, sum(J) group by C") returns revenue by region in one formula. It's a nice bridge to the SQL lessons.

Step 6: Conditional formatting to make patterns visible

Select the revenue column, then Home → Conditional Formatting (Excel) or Format → Conditional formatting (Sheets):

• Highlight cells greater than 999 to spot the 27 orders of $1,000 or more instantly.
• Data bars draw a mini bar chart inside each cell, which is great for the regional summary.
• Color scales on a month-by-region table reveal seasonality at a glance.

Use it sparingly. One or two rules guide the eye; ten rules make a rainbow nobody can read.

Here's what a simple SUMIFS summary by category looks like once you chart it:

In dollars: Electronics $44,068, Home & Kitchen $17,875, Office $15,507 and Fitness $12,285. Almost half the business is one category.

Excel or Google Sheets?

The formulas in this lesson are nearly identical in both. Learn the concepts once and you'll switch between them easily. If you work with millions of rows, both start to struggle; that's where SQL (Lesson 8) and Python (Lesson 14) take over.

Summary

• Set up first: one header row, table format, frozen headers, filters and correct data types.
• COUNTIFS, SUMIFS and AVERAGEIFS answer most "how many / how much / how big" questions with conditions.
• XLOOKUP joins data from another sheet; with product costs you can calculate gross profit ($42,514, a 47.4% margin).
• Use absolute references ($) for ranges you copy, and keep criteria in cells.
• UNIQUE, FILTER and SORT build live lists; conditional formatting makes patterns pop.
• Formulas can't fix messy text: "TX" and "Texas " slip past a COUNTIF for "Texas".

Next lesson: Data Cleaning: Duplicates, Errors and Missing Values. You'll find and fix every deliberate error in the raw sheet and prove your data is clean.