Analyzing and Summarizing Data

A table becomes useful when you know what you want to find. Analysis organizes, transforms, and summarizes cleaned data to answer a question. Its result becomes the input for a chart, dashboard, or recommendation.

Turn the question into four decisions

For “Which region had the highest revenue in March 2026?”, make the decisions explicit:

A metric is the number you calculate. A dimension supplies the groups. A filter sets the records included, and the comparison tells you how to judge the results. Define the metric precisely: revenue before discounts differs from revenue after discounts. Record how returns, cancellations, and tax are treated so a second analyst can calculate the same number.

Check what one row represents

The grain is what one row stands for. One order per row lets a row count measure orders. One order line per row does not: an order containing three products occupies three rows. Counting those rows would report three lines as three orders.

Here are ten March orders, one row per order. Prices are in dollars. In a spreadsheet, the six input columns are A through F, data occupy rows 2 through 11, and the revenue calculation goes in G.

Plan the operations before running them

First select March dates. Calculate each order's revenue from its quantity and unit price, group by region, sum the calculated field, and sort the regional totals in descending order. The row revenues are 30, 40, 120, 15, 60, 50, 25, 80, 40, and 75. Their total is 535. The grouped answer is North 250, South 180, and East 105.

In a spreadsheet, this path uses a filter, a formula column, and either conditional functions or a pivot table. In SQL, it uses WHERE, a multiplication expression, GROUP BY, and ORDER BY. A spreadsheet makes small-table exploration easy to inspect; SQL works directly with database tables and saves repeatable queries.

Keep source records unchanged by working in a separate sheet, copy, or query. Save the question, definitions, and operations alongside the result. Producing a convenient total before deciding what it measures is a common way to answer a different question from the one asked.

Sorting puts records in an order that makes rankings and extremes easier to see. Ascending runs from smallest to largest, A to Z, or oldest to newest; descending reverses that order. The values in a record stay together.

Sort complete records

In Excel, use Data > Sort; in Google Sheets, use Data > Sort range. Select the whole range, including every column that belongs to each record, and identify the header row. An Excel table keeps its row fields together during a sort.

If you sort only the revenue column, an amount can move beside the wrong order id. The total can remain 535 even though the records are now incorrect. Check a few complete rows after sorting, including an id, a date, and its amount. A row count alone cannot catch this mistake.

Use a second field to resolve ties

Suppose four records have region and revenue values East 15, North 30, East 50, and North 120. Sort by region ascending, then revenue descending. The result is East 50, East 15, North 120, North 30. The first field forms the main blocks; the second orders records inside each block.

SQL expresses the same rule as:

SELECT order_id, region,
       quantity * unit_price AS revenue
FROM orders
ORDER BY region ASC, revenue DESC;

ASC is the default. The alias revenue names the calculated result and can be used in ORDER BY. This query returns a sorted result without rearranging the stored table. Without ORDER BY, SQL gives no guaranteed row order, even when repeated runs happen to look the same.

Find the top records

For the March orders, revenue descending begins with order 1003 at 120, order 1008 at 80, and order 1010 at 75. To return three rows, PostgreSQL, MySQL, and SQLite support an ending such as ORDER BY revenue DESC LIMIT 3; SQL Server uses SELECT TOP 3 with the ordering clause.

A cutoff may fall inside a tie. Add a second field, such as order_id ASC, when you need a predictable choice among equal revenues. Returning three rows and returning every record tied for third place are different requests; a simple row limit addresses the first.

Check the values behind the display

Numbers stored as text can sort as “10”, “100”, “9”. Real numbers sort as 9, 10, 100. Text dates written as “03/12/2026” may follow character order instead of time order. Convert the column to the proper type during cleaning; changing the visible number format alone does not reliably convert text.

The spreadsheet SORT function returns a sorted copy in another location. Excel uses 1 for ascending and −1 for descending in its sort-order argument. Google Sheets uses TRUE and FALSE. The copy updates when the referenced data change.

Before treating the display as a ranking of the full data set, check active filters and the selected range. A correctly sorted visible subset can still omit the record with the highest value.

A filter decides which records belong in an analysis. Spreadsheet filters hide nonmatching rows; SQL WHERE returns matching rows. Neither operation deletes the source records.

Build conditions that match the question

SQL comparisons include =, <>, >, <, >=, and <=. Use IN for a list, LIKE for a text pattern, and IS NULL for missing values. Text literals use straight single quotes, as in WHERE region = 'North'. Spreadsheet filter menus offer text, number, and date conditions.

AND requires every condition to hold. OR requires at least one. Among the March orders, North and Books gives two orders, 1001 and 1010. North or Books gives six: the four North orders plus the two Books orders elsewhere. An order meeting both parts of an OR belongs in the result once.

SQL evaluates AND before OR. Compare these conditions:

WHERE category = 'Books'
   OR category = 'Toys' AND region = 'East'

This includes every Books order plus East Toys orders: five rows. To include East records only, group the categories first:

WHERE (category = 'Books' OR category = 'Toys')
  AND region = 'East'

That keeps two rows. category IN ('Books', 'Toys') can replace the parenthesized OR.

Include the whole date period

BETWEEN includes both endpoints. With timestamps, an upper endpoint written as March 31 may mean midnight at the start of that day, leaving out its later records. Use an inclusive start and an exclusive next-period start:

WHERE order_date >= '2026-03-01'
  AND order_date < '2026-04-01'

Date literal syntax varies by database. The boundary logic keeps every moment in March without guessing the last possible time on March 31.

Handle missing and inconsistent values

discount = NULL is not a true comparison. Use discount IS NULL. A missing discount also satisfies neither discount < 5 nor discount >= 5, so those two conditions do not cover all records when missing values exist. Give missing values their own group or exclude them deliberately.

Inspect distinct text values before filtering. Capitalization and trailing spaces can affect matches differently across tools and database settings. Standardize the keys and labels during cleaning so “North” and a variant with an extra space do not depend on matching behavior.

Total the records you can see

In Excel and Google Sheets, =SUM(G2:G11) still includes rows hidden by a filter. =SUBTOTAL(9, G2:G11) sums the rows the filter leaves visible. Filtering the March table to North leaves four orders: SUM remains 535, while SUBTOTAL gives 250.

Alternatively, =FILTER(A2:G11, C2:C11="North") returns a matching copy in a new location; a sum over that copy covers only those returned records. Record the before-and-after counts and state the filter beside the result. An old active filter can otherwise make a later analysis silently cover the wrong population.

A calculated field adds a value to each record using the fields already there. Revenue from quantity and unit price is a row-level calculation. It still leaves one row per order; a regional total comes later.

Build and fill a formula

With quantity in E and unit price in F, enter =E2*F2 in G2. Filling down changes the relative references to =E3*F3, then =E4*F4. Check the first and last filled cells to make sure the formula covers the data and refers to each row's inputs.

A rate stored in one fixed cell needs an absolute reference, such as $J$1. The dollar signs lock both its column and row when the formula is copied. In an Excel table, =[@Quantity]*[@UnitPrice] identifies fields by name and fills the calculated column automatically. Keep inputs in their own columns so a revised quantity can update the result.

In SQL, the expression and its label belong in SELECT:

SELECT order_id, quantity * unit_price AS revenue
FROM orders
WHERE quantity * unit_price > 50;

The alias names an output column; it does not add a stored column. The same query's WHERE is processed before that output alias exists, so repeat the expression there.

Keep the denominator attached to the metric

An order contains three games, each priced at 40 with a unit cost of 28. Revenue is 120, total cost is 84, and profit is 36. Two useful ratios describe different comparisons:

Both use the same profit. Switching the denominator changes what the result means; label the calculated field with the ratio you actually used.

Create grouping labels from dates and text

Spreadsheets use YEAR(B2) and MONTH(B2) for date parts, or TEXT(B2, "yyyy-mm") for a label such as “2026-03”. WEEKDAY(B2) provides a day-of-week number. In PostgreSQL and MySQL, EXTRACT(MONTH FROM order_date) returns the month number; date functions differ across databases. Grouping by month number alone mixes March in different years, so keep the year too.

Text formulas can build a combined key. Joining store “S04” and month “2026-03” with a hyphen produces “S04-2026-03”. Spreadsheets can use & or CONCATENATE; many SQL dialects offer CONCAT, and several support ||. Use the same construction in both tables you will match.

Preserve decimals until the final display

PostgreSQL, SQL Server, and SQLite can discard the fractional part when both division inputs are integers. 45 / 60 can therefore give 0. Use 45 * 1.0 / 60, or convert to a decimal type, to obtain 0.75. Spreadsheet =45/60 keeps the fraction.

Round only reported results. Three exact thirds sum to 1; rounding each to 0.33 first produces 0.99. Number formatting can shorten the display while retaining the underlying precision. A formula such as ROUND(value, 2) rounds to two decimal places; use it on a final output when the rounded value itself is needed. Check one calculated row by hand before using the column in a summary.

Rules can turn numeric fields into groups you can count or compare. A conditional calculated field assigns a label by testing each row. A flag records whether a row meets one condition, often as 1 or 0.

Make boundaries complete

Classify revenue as Large at 100 or more, Medium at 50 or more but below 100, and Small below 50. In a spreadsheet, with revenue in G:

=IF(G2>=100, "Large", IF(G2>=50, "Medium", "Small"))

IFS offers another form:

=IFS(G2>=100, "Large", G2>=50, "Medium", TRUE, "Small")

The SQL expression follows the same order:

CASE
  WHEN quantity * unit_price >= 100 THEN 'Large'
  WHEN quantity * unit_price >= 50 THEN 'Medium'
  ELSE 'Small'
END AS order_size

These tests use the first true condition. If the test for 50 comes first, revenue of 120 becomes Medium and never reaches the Large branch. Test the most restrictive threshold first, or write nonoverlapping ranges.

For the March revenues, 120 is Large; 60, 50, 80, and 75 are Medium; the other five are Small. The counts are 1, 4, and 5, totaling ten. A boundary test matters: revenue of exactly 50 is Medium. Rules using “below 50” and “above 50” would leave that value out.

Keep unknown values distinct

An unknown revenue needs an Unknown label, not a size inferred from missing information. In SQL, a comparison with NULL is not true, so a final ELSE 'Small' would otherwise collect missing revenue too. Add a first branch that checks missing inputs, such as WHEN quantity IS NULL OR unit_price IS NULL THEN 'Unknown'.

Include a fallback deliberately. A SQL CASE without ELSE returns NULL when nothing matches. Omitting the false result of IF produces FALSE in Excel and a blank default in Google Sheets. An explicit final result makes the classification easier to inspect.

Use flags to count a condition

For each order, CASE WHEN status = 'Returned' THEN 1 ELSE 0 END creates a return flag. Five flags of 1, 0, 0, 1, and 0 sum to 2 returned orders. Their mean is 0.4, or 40%. This works because every included row contributes either one event or no event; define any treatment of unknown status before assigning the values.

Protect a ratio at its denominator

Orders divided by visits is undefined on a day with zero visits. With orders in B and visits in C, =IF(C2=0, "", B2/C2) leaves the result blank for that case. In SQL, use orders * 1.0 / NULLIF(visits, 0). NULLIF turns a zero denominator into NULL, giving a missing result instead of a divide-by-zero error.

A blank or NULL here means an unavailable ratio, not a measured rate of zero. Broad IFERROR handling can also hide a misspelled formula or a broken reference. Test the expected problem directly, then inspect boundary rows, unknowns, and the category counts.

Related tables need a reliable way to identify the same entity. A customer name is useful for display but can belong to two people or appear in different forms. An identifier such as customer_id makes the match explicit.

Identify the one side and the many side

A primary key uniquely identifies rows in its own table. A foreign key in another table refers to that identifier. In a customers table, each customer id appears once. In an orders table, the same customer id can appear on many orders.

Repeated customer ids are expected in orders; they are a problem in a reference table meant to contain one row per customer. Check uniqueness where the relationship requires it:

SELECT COUNT(*) AS rows,
       COUNT(DISTINCT customer_id) AS different_ids
FROM customers;

If the table has 1,200 rows but 1,195 different ids, investigate duplicate or missing identifiers. Equality of these counts is useful only alongside a missing-key check, because distinct counts skip NULL. In a spreadsheet with filled keys, compare COUNTA(A2:A1201) with COUNTA(UNIQUE(A2:A1201)).

Match the full key

A targets table with one record per store per month needs both store and month. This composite key distinguishes “S04” in March from “S04” in April. Match both fields, or construct “S04-2026-03” in both tables using the same rules. A store id alone would leave several possible targets.

Standardize keys before combining. In spreadsheet lookups, the number 1001 and text “1001” can fail to match; “C-001”, “C001”, and “C001 ” are also different representations. SQL may convert types or ignore some spaces depending on its settings. Clean formats, spaces, and types so your result does not rely on those differences.

Choose whether to add columns or rows

A lookup or join adds related fields by matching keys. Adding each customer's name to an orders table with a unique customer reference keeps one row per order. Starting from customers and attaching their orders can repeat customer details and change the grain.

Appending stacks records with the same structure, such as January sales below February sales. SQL UNION ALL retains every row; UNION also removes duplicate output rows. The selected columns need the same order and compatible types. If two legitimate sales records happen to have identical selected values, removing one changes the total.

Before combining, write what one row represents in each input and what you expect it to represent in the output. Then choose keys and a relationship that produce that result. This expectation gives you a concrete row-count check after the operation.

A lookup brings a value from a reference table into a main table. For each sales row, a product code can retrieve the product's name or price. The match must return the value for that exact product.

Read the lookup inputs

Suppose the Products sheet has codes in A, names in B, and prices in C, with records in rows 2 through 50. The main sheet's product code is in B2:

=XLOOKUP(B2, Products!$A$2:$A$50, Products!$C$2:$C$50, "Not found")

The inputs are the value to find, the key range to search, the range to return from, and a result for a missing match. XLOOKUP uses exact matching by default in current Excel and Google Sheets. The two ranges must line up row by row. Absolute references keep them fixed when the formula fills down; B2 moves to B3 for the next sale.

The reference table contains P01 Notebook at 3.50, P02 Pen at 1.20, and P03 Folder at 2.80. For a sale of four P03 items, the lookup returns 2.80 and the calculated revenue is 11.20. P07 is absent, so the formula returns “Not found”.

Use exact VLOOKUP for identifiers

The equivalent older function is:

=VLOOKUP(B2, Products!$A$2:$C$50, 3, FALSE)

VLOOKUP searches the first column of the selected range. The 3 means its third column, and FALSE requests an exact match. Omitting the last input allows approximate matching, which can silently return the wrong product on an unsorted id column.

Inserting a column inside a VLOOKUP range can change what its fixed column number returns. Check the formula after structural edits.

Investigate missing and duplicate matches

#N/A usually signals no match. Check whether the reference really lacks the code, or whether spaces, text-versus-number types, or code formats differ. IFNA can replace this particular error with a visible label; IFERROR catches other errors too and can hide unrelated faults.

Count missing labels, for example =COUNTIF(H2:H500, "Not found"), and report what they affect. Do not replace unknown prices with zero simply to make a total calculate.

The default search returns the first exact match. Two P03 reference rows with different prices make the result ambiguous, even if the lookup shows no error. Verify key uniqueness before trusting the returned values.

Use approximate matching for ordered bands

A threshold table of 0 Small, 50 Medium, and 100 Large classifies 75 as Medium and 120 as Large. Here the intended rule is the largest threshold not above the input. XLOOKUP match mode −1 implements exact-or-next-smaller matching. VLOOKUP with TRUE can do the same when its threshold column is sorted ascending. This is useful for numeric bands, while product identifiers need exact matching.

A join combines table records whose keys match. Unlike a spreadsheet lookup's usual first-match result, a join returns every match. Predicting those matches is essential before counting rows or summing amounts.

Choose the records to retain

Consider customers C1 Ana, C2 Ben, C3 Chen, and C4 Dara. Orders are O1 for C1, O2 for C1, O3 for C2, and O4 for C5.

An inner join keeps matched records only. A left join keeps every left-table row, attaching the right-table fields when they exist and NULL when they do not. Choose the left table from the population your question must retain. To list every order, including orders with unknown customers, start with orders and left join customers.

A basic statement is:

SELECT o.order_id, c.name
FROM orders AS o
LEFT JOIN customers AS c
  ON o.customer_id = c.customer_id;

The aliases o and c show where each field comes from; ON identifies the matching columns. The result is a new view of the data, not a replacement for either input table.

Recognize repeated amounts

Suppose order 501 has an order total of 90 and three lines; order 502 has a total of 40 and one line. Attaching the lines produces four rows. Order 501's total appears on each of its three rows, so summing that field gives 310 instead of the original 130.

This repetition is fan-out. The join can be correct for studying lines while the sum is wrong for studying orders. Sum order totals in the orders table, or first summarize lines to one row per order before joining. A distinct sum is not a general repair: two different orders can have the same total and both need counting.

Count actual matches

After customers left join orders, a customer with no order still has a result row. COUNT(*) counts that row as one. COUNT(o.order_id) counts the non-NULL order ids and gives zero for the customer without orders.

To find unmatched customers, test order_id IS NULL in the joined result. Test the right table's key, not a field such as telephone number that can be missing even in a matched record. In the example, C3 and C4 have no orders; O4's C5 is instead an unmatched customer reference when starting from orders.

Check the relationship against the output

A plain left join produces at least as many rows as its left input. If every right key should be unique, the row count should stay the same. Extra rows show repeated right-side keys; fewer rows suggest an additional filter or a different operation. Compare key counts, unmatched rows, and a source total before and after. Equal totals alone cannot establish that each individual record was matched correctly.

An aggregate function reduces many input values to one result. The right function depends on whether you need a total, a count, an average, or an extreme, and on what a row represents.

Choose the quantity to summarize

For the ten March revenues, the count is 10, sum 535, mean 53.5, minimum 15, and maximum 120. With revenue in G2:G11, spreadsheet functions are SUM, COUNT, AVERAGE, MIN, and MAX over that range. A SQL summary can calculate them together:

SELECT COUNT(*) AS orders,
       SUM(quantity * unit_price) AS revenue,
       AVG(quantity * unit_price * 1.0) AS mean_revenue,
       MIN(quantity * unit_price) AS smallest_revenue,
       MAX(quantity * unit_price) AS largest_revenue
FROM orders;

The decimal factor protects the average in databases such as SQL Server, where AVG of integer input returns an integer. Rounding a truncated result afterwards cannot restore the missing fraction.

Count rows, filled fields, and different values

Ten orders can come from five customers. Counting rows answers “How many orders?” at order grain; counting distinct customer ids answers “How many customers ordered?” In Google Sheets, COUNTUNIQUE is also available. Check blanks separately before counting a UNIQUE result, because a missing key can appear as a distinct entry.

Understand what missing values do

For SQL ratings 4, 5, NULL, 3, NULL, COUNT(*) is 5, COUNT(rating) is 3, the sum is 12, and AVG(rating) is 4. The average uses the three known values. Replacing unknown ratings with zero changes the denominator and produces 2.4, a different claim about the data.

Spreadsheet AVERAGE also skips blank cells and text in a referenced range, while including numeric zero. A text entry “10” beside numeric 20 can therefore produce an average of 20 instead of 15. Compare COUNT with COUNTA when a numeric column unexpectedly contains fewer counted values than filled cells. Then inspect its data types.

Respect the grain and the field's meaning

In an order-lines table, use distinct order ids for an order count. Average order value is total order revenue divided by the number of orders, not the number of lines. A running balance is already a total at a point in time; adding every running balance does not give the period's sales.

A summary can look reasonable while its denominator counts the wrong thing. Put the counted entity into the column label, such as “Orders”, “Order lines”, or “Known ratings”, so the result states what was included.

Conditional functions build a summary without moving the original records. Each row contributes only when its values meet the criteria. A result table can then list regions, categories, or other groups in a layout you control.

Put the ranges in the right order

COUNTIFS takes pairs of criterion ranges and criteria. SUMIFS starts with the range to add, followed by those pairs. AVERAGEIFS follows the same pattern with an average range:

=COUNTIFS(C2:C11, "North", G2:G11, ">50")
=SUMIFS(G2:G11, C2:C11, "North", D2:D11, "Books")
=AVERAGEIFS(G2:G11, C2:C11, "North")

For the March table, these return 2 orders, 105 revenue, and mean revenue of 62.5. The first function counts the North revenues of 120 and 75. The second adds North Books revenues of 30 and 75. The third divides North's total 250 by its four orders.

All criterion pairs inside one COUNTIFS, SUMIFS, or AVERAGEIFS use AND: every condition must hold on the same row. Make each range begin and end on matching rows. Comparing one row's region with another row's revenue would not describe a record.

Write criteria precisely

Text and comparison criteria use straight double quotes: "North", ">50", or "<>Returned". A cell can supply the criterion directly, such as I2. To combine an operator with a cell's value, use ">"&H1. The wildcard "*book*" matches text containing “book”; these spreadsheet text criteria ignore capitalization.

The singular functions can use a different argument order. In SUMIF(criteria_range, criterion, sum_range), the sum range comes last. In SUMIFS, it comes first. Read the function name before editing or copying a formula.

Fill a group summary safely

Put North, South, and East in I2:I4. In J2 enter:

=SUMIFS($G$2:$G$11, $C$2:$C$11, I2)

Fill down. The data ranges stay fixed, while I2 becomes I3 and I4. The results are 250, 180, and 105. Compare their sum with the direct source total of 535. If new records extend beyond row 11, expand the ranges or use a growing table reference.

Avoid counting an OR overlap twice

North or Books includes six March orders. Separate counts give four North orders plus four Books orders, but two orders belong to both. Subtract the overlap once: four plus four minus two gives six. The overlap is the result of COUNTIFS with North and Books together. Simply adding the separate counts would report eight.

Express a conditional sum in SQL

A condition can sit inside an aggregate:

SUM(CASE WHEN region = 'North'
         THEN quantity * unit_price ELSE 0 END)

Each North record contributes revenue and every other record contributes zero. For a conditional count, contribute 1 for a match and 0 otherwise. This lets one query return several named conditional totals side by side. Check the labels actually present in the source, especially spaces, before treating a missing group total as zero activity.

GROUP BY creates one result row for each observed combination of group values. Aggregate functions then summarize the records in each group. The output has a new grain, such as one row per region.

Build a grouped query

SELECT region, COUNT(*) AS orders,
       SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY region
ORDER BY revenue DESC;

For the March orders, the result is North with 4 orders and 250 revenue, South with 3 and 180, and East with 3 and 105. The group counts total ten and revenues total 535.

Selected fields must describe the group or summarize its records. A category column beside a regional total is ambiguous when a region contains several categories. Include category in GROUP BY if you want one row per region-category combination. The March table has eight observed combinations; South has no Toys record, so grouping does not create that combination.

Filter records before groups, or groups after totals

WHERE category = 'Games' leaves North revenue 120 from one order, South 120 from two, and East 40 from one. A different query with no category filter and HAVING COUNT(*) >= 4 keeps North only.

To total each region's Games orders and keep regional Games revenue above 100:

SELECT region, SUM(quantity * unit_price) AS revenue
FROM orders
WHERE category = 'Games'
GROUP BY region
HAVING SUM(quantity * unit_price) > 100
ORDER BY revenue DESC;

North and South remain, tied at 120. Moving the revenue condition into WHERE would test individual orders, changing the question.

Follow the logical processing order

The useful order for reasoning is FROM and joins, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, then the row limit. The database may execute a more efficient physical plan, but this logical order explains why a SELECT alias is available for sorting and generally unavailable in the same query's WHERE.

For a mean, use AVG(quantity * unit_price * 1.0) to retain decimal results where integer inputs would otherwise truncate them. Treat NULL group labels deliberately: they form a group of their own. Show it as Unknown or exclude it with an explicit record count.

Summarize at two levels when needed

Customer A places orders of 60 and 40, B one of 50, and C three of 10. Total revenue is 180 across six orders, giving 30 per order. To find the average per customer, first sum by customer: A 100, B 50, C 30. Average those three totals to get 60. A saved intermediate table or a WITH clause can hold the first summary.

Inspect distinct group values before running the query. Text variants can split or combine groups differently across database settings; clean them and compare the observed groups with the groups your question expects.

A pivot table builds a grouped spreadsheet summary by placing fields in a few areas. It is useful when you want to rearrange comparisons and inspect totals without writing a separate formula for each group.

Start with a complete source

Use one header row, one record per row, and a clear rectangular range. Remove source subtotal and total rows: a pivot treats them as records and counts their amounts again. Avoid merged cells and blank separators inside the source.

Select the range, then choose Insert > PivotTable in Excel or Insert > Pivot table in Google Sheets. Place fields according to the question:

With region in Rows and Sum of revenue in Values, the March result is North 250, South 180, East 105, and grand total 535. Sorting the summarized values shows the ranking.

Check the summary function

A numeric field can summarize by Sum, Average, Min, or Max. Do not assume the selected function matches your question. Excel may choose Count when the field has text or blanks, so an apparent revenue summary can actually show how many filled cells it contains.

To count orders, use a fully filled order-id field. Excel's Count counts nonempty cells; Google Sheets offers COUNTA for nonempty cells and COUNT for numeric cells. Counting a price field with blanks would miss records whose prices are unknown.

Read a two-dimension summary

Add category to Columns. North has Books 105, Games 120, and Toys 25; South has Books 60 and Games 120; East has Books 15, Games 40, and Toys 50. Column totals are Books 180, Games 280, and Toys 75.

The empty South–Toys cell means no source records occupy that combination. A recorded order with revenue zero would be a different case, even if a display setting makes both cells look empty or zero.

Understand an average grand total

Choose Average of revenue instead. North is 62.5 from four orders, South 60 from three, and East 35 from three. The grand total is 53.5 because the pivot averages all ten source records. Averaging the three displayed means gives 52.5 and incorrectly gives the four-order group the same weight as each three-order group.

Check updates and source coverage

After source changes, verify the pivot is current. In Excel, use Refresh when automatic refresh is unavailable or off; supported versions also offer Auto Refresh for local workbook data. Google Sheets updates pivot results when cells in the source range change. In either tool, records added outside a fixed source range can still be omitted. An Excel table source grows with added rows, while a fixed range needs inspection and expansion.

Compare the pivot grand total and order count with direct source calculations. A refresh updates the selected source; it cannot fix a range that excludes new records.

A numeric column needs more than one average to describe it well. Center tells you where values sit; spread tells you how different they are. The count shows how much data the summary represents.

Calculate a small set of measures

Eight bike trips lasted 6, 8, 9, 10, 12, 12, 15, and 58 minutes. Their total is 130 minutes.

For the median, the fourth and fifth values are 10 and 12, giving 11. Spreadsheet functions include AVERAGE, MEDIAN, MODE or Excel's MODE.SNGL, MIN, and MAX. In SQL, AVG, COUNT, MIN, and MAX are common; median and standard-deviation function names and syntax vary by database.

Choose a useful center

The 58-minute trip is an outlier, far from the others. It pulls the mean upward: seven of the eight trips are shorter than the mean. The median better describes the middle of these trips.

Without 58, the total is 72 across seven trips, so the mean is about 10.29 minutes and the median is 10. This shows the effect of the long trip; it does not justify deleting it. Check whether the ride was real, recorded incorrectly, or affected by a return problem. Keep, correct, or exclude it according to that evidence and record the decision.

The mean is useful for roughly symmetric data and questions connected to totals, because multiplying the mean by its count recovers the sum. The median is useful for skewed durations, incomes, or prices. A large mean–median gap is a reason to inspect the distribution, not a complete description of its shape.

A mode can describe categories, such as the most frequent payment method. More than one category can tie: Books and Games each occur four times in the March orders. Numeric data with few repeats may have no useful mode.

Describe variation

The sets 48, 50, 52 and 30, 50, 70 both have mean 50. Their sample standard deviations are 2 and 20. Standard deviation describes spread around the mean in the original units; a larger value means more spread. It is based on squared distances, so it is not the ordinary average of absolute distances.

Use STDEV.S when treating the records as a sample and STDEV.P for the entire population of interest. Their calculations divide squared deviations by the count minus one and the count, respectively, before taking the square root. Here the purpose is to interpret the measure, not calculate it by hand.

Quartiles divide sorted data into quarters. The median is the second quartile; the interquartile range is the third quartile minus the first and measures the middle half's spread. A percentile locates a chosen percentage in the distribution. Tools can use different interpolation methods, including QUARTILE.INC and QUARTILE.EXC, so use a consistent method when comparing results.

Report counts alongside the center and spread. One unusual trip affects a three-trip mean much more than a 300-trip mean.

Percentages and rates make comparisons readable only when their denominators are clear. A share, a change, and a rate answer different questions. Combining them across groups requires the size of each group's base.

Separate shares, changes, and percentage points

A share is a part divided by the relevant total. North's share of March revenue is 250 divided by 535, about 46.7%. South is about 33.6% and East 19.6%. The rounded values total 99.9%; the unrounded shares total 100%.

Percent change compares a new value with its old value:

Revenue rising from 80 to 100 increases by 25%. Falling from 100 to 80 decreases by 20%. The absolute difference is 20 in either direction, but the old value changes. A zero old value makes this percent-change formula undefined.

A percentage-point change subtracts percentages directly. A conversion rate moving from 4% to 5% rises by 1 percentage point. Its relative increase is 25%, because the increase of 1 is a quarter of the original 4. Name the kind of change in the report.

Compare events against the same base

Store A has 18 complaints from 12,000 orders; Store B has 9 from 4,500. Per 1,000 orders, the rates are 1.5 and 2.0. B has fewer complaints but a higher rate. A fair comparison needs the same event definition, period, denominator, and scale.

Do not let a large raw count stand in for a high rate. A busier store can have more complaints simply because it handles more orders. Report the counts too, so readers can see the bases behind the rates.

Weight averages by the records they represent

Region X has 200 orders averaging 50; Y has 50 orders averaging 90. Recover each region's total: 10,000 and 4,500. Divide the combined 14,500 by 250 orders to get 58. The simple average of the two means, 70, incorrectly gives the two regions equal weight.

The weights must count the observations used in those means. If a group average excludes missing ratings, use its known-rating count, not every record in that group.

Pool rates from totals

Page A converts 100 of 5,000 visits; B converts 10 of 100. The rates are 2% and 10%, but the overall rate is 110 conversions divided by 5,100 visits, about 2.2%. Averaging the two percentages gives 6% and overweights the smaller page.

A whole-table ratio follows the same logic. Orders with profit 50 on revenue 100 and profit 100 on revenue 1,000 have an overall margin of 150 divided by 1,100, about 13.6%. Their individual margins average to 30%, which answers a different question. In SQL, use SUM(profit) * 1.0 / NULLIF(SUM(revenue), 0); in a spreadsheet, divide the profit total by the revenue total after checking the denominator.

Label the denominator, such as “share of March revenue” or “complaints per 1,000 orders”. A percentage without that base can describe a different population from the one a reader assumes.

Build checks into the analysis before sharing its results. A formula can return a believable number even when its range misses rows or a join repeats amounts. Each operation needs an expectation you can compare with its output.

Reconcile totals and counts

For the March orders, regional revenues 250, 180, and 105 sum to 535. Their order counts 4, 3, and 3 sum to ten. Compare both with the source filtered to the same period and definition.

A mismatch can reveal lost records, repeated records, or groups that overlap. A match is useful evidence but is not enough on its own: sorting only one column can corrupt row relationships while leaving every total unchanged. Also, a positive and a negative error can cancel.

Write an expected count before filtering or joining. Four North orders should remain after the March table is filtered to North. A left join from ten orders to a reference with one customer row per id should return ten rows. Twelve rows indicate repeated right-side keys. A left join from customers to orders can legitimately increase the count because one customer has many orders.

Inspect individual records

Choose a few records and recalculate them directly. Order 1003 has three units at 40, producing revenue 120. North's four revenues are 30, 120, 25, and 75, giving 250. Check a lookup against the reference table by eye, including one missing code and one boundary case for a numeric band.

A spot-check should test the operation that could fail. For a filled formula, inspect early and late rows; for a classification, inspect each boundary; for a join, inspect repeated and unmatched keys. Checking only convenient ordinary rows can leave the fault untouched.

Compare independent routes

Calculate the regional summary with SUMIFS, a pivot table, or a SQL query and compare the results. Use the same metric and filter, but independently select the ranges or records. Two outputs built from the same incomplete intermediate table can agree while both omit the same orders.

If they disagree, compare period boundaries, source ranges, data types, missing values, and grain before changing the answer. Save the calculation or query that resolves the difference.

Account for missing values and plausibility

Count blank or NULL inputs, “Not found” lookup results, and joined records whose right-side key is NULL. Explain how these records affect the reported metric. A matched row can have another missing right-side field, so a blank name alone is not a reliable unmatched-row test.

Check the magnitude and units of the result. A share of a nonnegative whole is between 0% and 100%; a percent change can exceed 100% or be negative. From 50 to 150 is an increase of 200%. Investigate unexpected negative quantities and dates outside the analysis period, using the field's definition to decide what is valid.

Keep the question, metric definition, filters, formulas or queries, source period, and decisions together. This record lets someone repeat the work and explains such choices as excluding an invalid trip or keeping an unknown-region group.

A report should answer the question in numbers that a reader can place in context. State the metric, period, group, and comparison directly, then add the information that changes how the result should be interpreted.

State the answer and comparison

“In March 2026, North had the highest revenue: 250 from four orders, or about 46.7% of the total 535. South followed at 180 and East at 105.” This statement gives the ranking and the scale of the result.

North's revenue exceeds South's by 70. Relative to South, the gap is about 39%. The comparison base matters: a percentage using North as the denominator would answer how far South falls below North, a different question. Use consistent decimal places and round final figures only; 46.7% is more useful here than many displayed decimal places.

Describe associations without inventing causes

Suppose discount users spend more per order. They may spend more because of the discount, or people planning large purchases may seek discounts. A summary of these records establishes the observed difference, not which explanation caused it. Write “average order value was higher among discount users” and identify what further evidence would be needed to explain why.

Inspect the mix behind an overall rate

An overall comparison can reverse the comparison within subgroups. Consider two teams receiving different mixes of warm and cold leads:

A has the higher conversion rate for both lead types. B has the higher overall rate because three quarters of its leads are warm, compared with half of A's. Warm leads convert much more often for both teams. This reversal is called Simpson's paradox. The aggregate describes each team's actual mix; it does not isolate performance for a shared lead mix.

Break results down by a relevant subgroup before crediting a team or policy for the overall difference. Calculate each rate from its own numerator and denominator, then inspect how much each subgroup contributes to the total.

Show the information behind the summary

Give the group sizes with averages and rates. An unusual order can move a three-order mean much more than a 300-order mean. A small observed difference is not enough to establish a stable difference in the wider population.

State limitations that affect the answer: the time period, exclusions, missing or unmatched rows, and the metric's definition. For example, “Excludes 12 orders without a region, representing 2% of revenue.” Report that consequence, not merely that some data were missing.

Choose a small sorted table or a key figure when it carries the answer clearly. It can become the input for a later chart or dashboard, with the same definitions and checks preserved.