Your manager forwards a file with 8,000 rows of orders and asks which region is dragging down the quarter. You could scroll for an hour and guess. Or you could get the answer in about thirty seconds, correct to the dollar, without deleting a single row. That gap, an hour of scrolling against thirty seconds of certainty, is the whole reason spreadsheets still run most of the working world.

Part 3 was about asking a sharp question. This part is where you answer one. A spreadsheet is not a place to store numbers, it is a small calculation engine, and two features do most of the real work: formulas that compute an answer from many cells at once, and pivot tables that fold thousands of rows into a summary you can read at a glance. Learn those two well and you will out-produce people who have clicked around spreadsheets for years.

What a spreadsheet is actually doing

Every cell has an address, a column letter and a row number, so the cell three columns over and one row down from the top left is C2. A formula is any cell entry that starts with an equals sign. Instead of storing a value, it stores an instruction: go read these other cells, do this arithmetic, and show the result here. Type =A2+A3 and the cell shows the sum. Change A2 and the result updates by itself, instantly, because the spreadsheet recalculates every formula that depends on the cell you touched. That live recalculation is the whole trick, and it is what separates a spreadsheet from a table printed on paper.

One idea trips up almost every beginner: relative versus absolute references. When you copy a formula down a column, its cell references shift to follow it, so =A2+B2 in row 2 becomes =A3+B3 in row 3. That is a relative reference, and usually it is exactly what you want. Sometimes you need a reference to stay locked on one cell, say a tax rate sitting in F1. Put a dollar sign in front of the parts you want frozen, so $F$1 never moves no matter where you copy it. Get this one distinction straight early and half of the mysterious wrong answers beginners hit simply disappear.

flowchart LR
  A[8000 order rows] --> B[Choose a row field and a value field]
  B --> C[Group rows by region]
  C --> D[Sum sales inside each group]
  D --> E[A four line summary you can read]

The formulas that carry most analyses

If you learn only one family of formulas, make it conditional aggregation. These add up, count, or average numbers but only for the rows that meet a condition you set. The workhorse is SUMIFS, which adds every value in one column where matching rows in other columns pass your tests. COUNTIFS counts those rows instead of summing, and AVERAGEIFS averages them. With just these three you can answer most of the questions a small business will ever ask a spreadsheet, without a pivot table in sight.

=SUMIFS(D2:D8001, B2:B8001, "West")                total West sales
=COUNTIFS(B2:B8001, "West")                        number of West orders
=AVERAGEIFS(D2:D8001, B2:B8001, "West")            average West order value
=SUMIFS(D2:D8001, B2:B8001, "West", C2:C8001, "June")   West sales in June only

D holds sales amounts, B holds the region, C holds the month. Column D is the sum range, and each pair after it is a criteria range followed by the value to match.

The pattern is always the same. SUMIFS starts with the column you want to add, then takes pairs: a column to test, and the value it must match. Add as many pairs as you like and every one must be true for a row to count, so the last line above sums only rows that are both West and June. You can match on more than plain text too. Use a comparison like ">500" to sum large orders, or a wildcard like "North*" where the asterisk stands for any characters, which catches North and Northeast in one shot. The diagram below names each piece so the structure sticks.

Look up a value from another table

Real data lives in more than one table. Your orders list has a region code, and a separate reference sheet says which manager owns each region. Copying managers over by hand is slow and goes stale the moment someone changes jobs. A lookup formula does it for you: give it a value to find and the two columns to search and return, and it fetches the match. The modern tool for this is XLOOKUP, and it reads in the order you think: what to find, where to look, what to bring back.

=XLOOKUP("West", Regions, Managers)                 returns the West manager
=XLOOKUP(A2, Regions, Managers, "not found")        looks up the region in A2
                                                   and shows a message if missing

You will still meet VLOOKUP, the older lookup that most existing spreadsheets use. It works, but it counts columns by position, so inserting a new column between the lookup and the answer silently breaks it. XLOOKUP points straight at the return column, so it survives edits that would wreck a VLOOKUP. One caveat before you commit: XLOOKUP exists in Excel for Microsoft 365, Excel 2021, and Excel 2024, and in Google Sheets, but not in Excel 2016 or 2019. If you must support those older versions, keep a VLOOKUP handy as the fallback.

Build your first pivot table

A pivot table answers grouping questions with no formulas at all. It takes a long list of rows and folds it into a compact summary, grouping by whatever field you choose and totalling whatever number you point it at. The mechanics are the same idea in both tools. Click any cell inside your data, then in Excel choose Insert and then PivotTable, or in Google Sheets choose Insert and then Pivot table. Both then ask whether to drop the result on a new sheet, which is the safe choice, and open an editor on the side.

The editor has three drop zones that matter for now. Drag the field you want to group by into Rows, so dropping Region there stacks one row per region down the left. Drag the number you want measured into Values, so Sales there totals the sales for each region. If you want a second breakdown across the top, say by month, drag that field into Columns. That is the entire skill. No equals sign, no cell ranges, just deciding what goes down the side and what number fills the grid. Microsoft and Google both note that text fields usually belong in Rows and numeric fields in Values, which is a good default to start from.

Read the pivot like an analyst

Here is the summary a pivot on the sample orders produces in a few seconds, with Region in Rows and both a count of orders and a sum of sales in Values. The average column is the sum divided by the count, which the pivot can show directly if you set the value to summarize by average.

Illustrative figures for one quarter. Totals sum to 448,900 across 1,605 orders, a blended average near 280 per order.

That last point is what lifts a pivot from a counting toy to an analysis tool. A single ranking tells you who is behind. Splitting the measure into orders and average order value tells you the shape of the gap, and the shape decides the action. Fewer orders is a demand or marketing problem. A smaller average basket is a pricing or product mix problem. Same pivot, two numbers, two very different conversations with the business, and you got there without writing a formula.

Mistakes that quietly corrupt a spreadsheet

The dangerous spreadsheet errors are the silent ones, the kind that give you a clean looking number that happens to be wrong. The most common is numbers stored as text. A column imported from another system often arrives as text that looks like a number, and SUMIFS quietly skips it, so your total is too low and nothing warns you. The tell is values hugging the left edge of the cell, since real numbers sit on the right. Select the column and convert it to a number format, or multiply by one in a helper column, before you trust any total built on it.

Two more habits save you real pain. Never merge cells inside data you plan to analyze, because merged cells break both sorting and pivot tables in ways that are maddening to debug. And keep your raw data on its own sheet, untouched, with every formula, pivot, and chart living on separate sheets that read from it. When you overwrite raw numbers with a hand typed correction, you lose the ability to redo the work later or catch your own mistake. Treat the raw import as read only and your future self will thank you.

Learn SUMIFS and pivot tables before anything else

My recommendation for this week is narrow on purpose. Get fluent in two things and ignore the rest for now: writing a SUMIFS that returns the right number every time, and building a pivot table you can group and refresh without thinking. Those two skills answer more everyday questions than any other pair in a spreadsheet, and fluency in them is what makes the difference between staring at data and actually reading it. Everything flashier can wait until these are reflexes.

Open a real file this week, one with at least a few hundred rows, and force yourself to answer three questions with a pivot and three more with SUMIFS. Keep the Data Analyst guide open as your map, and glance back at Part 3 to sharpen each question before you touch the file. Next, Part 5 steps behind the spreadsheet to show how data is really stored, in tables, types, CSV files and databases, which is the bridge to writing your first SQL.

References

• SUMIFS function, Microsoft Support
• XLOOKUP function, Microsoft Support
• Create a PivotTable to analyze worksheet data, Microsoft Support
• Create and use pivot tables, Google Docs Editors Help

About The Author

Dr. Pranay Jha

Dr. Pranay Jha is a Cloud and AI Consultant with 18+ years of experience in hybrid cloud, virtualization, and enterprise infrastructure transformation. He specializes in VMware technologies, multi-cloud strategy, and Generative AI solutions. He holds a PhD in Computer Applications with research focused on Cloud and AI, has published multiple research papers, and has been a VMware vExpert since 2016 and a VMUG Community Leader.

See author's posts