MS Excel data analysis: a practical business workflow

MS Excel data analysis works best as a sequence: define the business question, clean a consistent table, summarize the measures, and prove that the totals reconcile. Start with revenue and cost amounts before interpreting a margin or a customer segment. A result is ready to use when another reader can follow it back to the retained export.

Start with one decision and one row definition

Suppose a US distributor asks why September gross profit declined even though revenue held steady. The sales operations analyst needs posted invoice and credit lines with their matched cost of goods sold, month, and customer segment. The finance owner confirms which postings belong in the comparison.

Write down the scope before changing the workbook: August and September 2026, USD, posted sales, and costs attached to those sales. Decide whether a row is an invoice line or a whole invoice. Do not combine both in the same amount column.

This is a descriptive business comparison. Installing statistical tools is a separate job covered in the Excel Data Analysis ToolPak guide. If you need a practice table before using company records, use the synthetic Excel analysis dataset.

Prepare a table from the export

Retain the export unchanged on a source sheet. On a working sheet, keep a single header row, remove presentation subtotals, and give each column one meaning. Convert the range to an Excel table named Sales; Microsoft's table instructions describe selecting the range and identifying its headers.

Here are all the records in the example. Name the workbook's amount columns Revenue and COGS; the USD annotations below identify their units. Month contains real dates representing the first day of the posting month. Format those dates for display as shown; do not replace them with month names stored as text.

R04 is a valid credit against September sales with the associated cost reversal. The example assumes finance has approved both amounts; a negative cost is not automatically an error.

Add a GrossProfit column:

=[@Revenue]-[@COGS]

Add a uniqueness check in another column. Every result must be TRUE for this table's entry identifier:

=COUNTIF(Sales[EntryID],[@EntryID])=1

Also check the required labels and numeric revenue fields:

=COUNTBLANK(Sales[Segment])
=COUNT(Sales[Revenue])=ROWS(Sales[Revenue])

The blank count must be zero. Repeat the numeric check for COGS and confirm that dates fall inside the chosen period. Do not replace a missing amount with zero unless the export owner confirms that meaning.

Summarize each month and segment

On a summary sheet, use columns A through F for Month, Segment, Revenue, COGS, GrossProfit, and Margin. Enter the month and segment combinations in A2:B5. Microsoft's SUMIFS documentation confirms that the function sums amounts meeting multiple criteria.

Enter these formulas in C2:F2 and fill down:

=SUMIFS(Sales[Revenue],Sales[Month],$A2,Sales[Segment],$B2)
=SUMIFS(Sales[COGS],Sales[Month],$A2,Sales[Segment],$B2)
=C2-D2
=IF(C2=0,NA(),E2/C2)

Format the final column as a percentage. NA() keeps an undefined margin visible when revenue is zero. The gross profit margin guide explains the measure and its cost boundary in more detail.

Percentages are rounded to two decimal places for display. Keep the underlying calculations unrounded.

Prove the summaries before interpreting them

The combined summaries must equal the source totals. Outside the summary range, these differences must both be zero:

=SUM(C2:C5)-SUM(Sales[Revenue])
=SUM(D2:D5)-SUM(Sales[COGS])

The complete export has eight records, revenue of $200,000, and COGS of $140,500. Gross profit is therefore $59,500. Check that every entry belongs to exactly one month and one segment before accepting the tie.

The monthly reconciliation is:

Each margin divides that row's gross profit by its revenue. Do not average segment percentages to produce the monthly result.

Turn the movement into a business finding

Retail revenue fell by $15,000 while wholesale revenue rose by $15,000. Those changes offset completely. Retail gross profit fell by $4,250; wholesale gross profit increased by $3,750. Their net movement is -$500, matching $29,500 minus $30,000.

The company margin fell by 0.50 percentage points even though retail margin improved and wholesale margin held steady. More revenue came from the segment with the lower margin. That supports investigating the sales mix; it does not prove that discounts or customer behavior caused the change.

Record the question, scope, reconciled totals, interpretation, and next owner in the finished report. The Excel analysis report example shows how to present that evidence without repeating the workbook.

Where it goes wrong

• Subtotal rows survive cleaning. Including both transaction lines and their subtotal doubles the same activity.
• Text and numeric values mix. A number formatted as text can disappear from a sum while remaining visible on the sheet.
• Segment definitions move. A customer reassigned between months can create a movement unrelated to sales performance. Choose a consistent classification policy.
• Dates and costs use different boundaries. Invoice-date revenue and unrelated payment-date costs do not establish a gross margin comparison.
• Percentages replace amounts. Explain the dollar movement first, then calculate ratios from the appropriate totals.

Analyze your own workbook in Covirage

Bring an Excel file containing consistent revenue, cost, period, and customer segment columns. Covirage maps the columns, and its tools compute and check the comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.

See Excel analysis in Covirage for the supporting workflow. Covirage is not affiliated with Microsoft.