Choose a chart by the business question: trend, comparison, distribution or contribution. Work through reconciled revenue and margin figures to see which display makes each answer clear.
The best Excel charts for data analysis and reporting match the question: lines show trends, bars compare categories, histograms show distributions, and ranked bars show contribution. Start with checked measures rather than a chart menu. A clear display makes the answer easier to assess; it does not establish whether the source figures are correct.
Write a sentence the reader needs answered, such as whether revenue growth is keeping pace with gross profit. Then choose the display that makes the relevant comparison visible.
| Business question | Chart to start with | Inputs to prepare | Check before publishing |
|---|---|---|---|
| How is revenue changing over time? | Line | One revenue total per period | Dates are ordered and periods are comparable |
| Which customers generate the most revenue? | Horizontal bar | Revenue by customer | Categories sum to the included total |
| How widely do customer margins vary? | Histogram | One margin per eligible customer | Bin counts equal the customer count |
| How concentrated is revenue? | Ranked bars and cumulative share | Sorted customer amounts and shares | Final cumulative share reaches the full total |
| Does revenue size vary with margin? | Scatter | Paired revenue and margin for each customer | Both measures refer to the same customer and period |
Microsoft's chart-type reference lists line, clustered bar, histogram and scatter chart types in Excel. Choose among them for the comparison you need, rather than treating the available list as a ranking.
For constructing a chart driven by a summary table, use the existing PivotChart tutorial. This guide focuses on the choice and the figures behind it.
A fictional US distributor wants to review revenue and gross margin for July through September 2026. Its posted sales export includes matched cost of goods sold, customer and month. All amounts are USD, and the customer totals below cover the entire quarter.
| Customer | Revenue (USD) | COGS (USD) | Gross profit (USD) | Gross margin |
|---|---|---|---|---|
| A | 300,000 | 180,000 | 120,000 | 40.0% |
| B | 220,000 | 132,000 | 88,000 | 40.0% |
| C | 140,000 | 84,000 | 56,000 | 40.0% |
| D | 140,000 | 98,000 | 42,000 | 30.0% |
| E | 110,000 | 82,500 | 27,500 | 25.0% |
| F | 80,000 | 68,000 | 12,000 | 15.0% |
| Total | 990,000 | 644,500 | 345,500 | 34.9% |
Place Customer, Revenue, COGS, GrossProfit and Margin in A1:E1, with the six customer records in rows 2 through 7. The units in the displayed table headings are annotations; the workbook headers use those plain names. Calculate D2 and E2, then fill down:
=B2-C2
=IF(B2=0,NA(),D2/B2)
Company margin is =SUM(D2:D7)/SUM(B2:B7): 345,500 / 990,000 = 34.9%, rounded to one decimal place. Retain unrounded values for every calculation. The Excel analysis workflow covers preparing and reconciling the transaction table before creating these summaries.
The same export summarized by month gives:
| Month | Revenue (USD) | COGS (USD) | Gross profit (USD) | Gross margin |
|---|---|---|---|---|
| July 2026 | 300,000 | 190,000 | 110,000 | 36.7% |
| August 2026 | 330,000 | 214,500 | 115,500 | 35.0% |
| September 2026 | 360,000 | 240,000 | 120,000 | 33.3% |
| Total | 990,000 | 644,500 | 345,500 | 34.9% |
A revenue line rises across the quarter. A separate margin line falls. Revenue grew by $60,000, or 20.0%, from July to September, while gross profit grew by $10,000, or 9.1%. Those changes use July as the denominator and are rounded to one decimal place.
That answers the business question: profit grew more slowly than revenue. The tables do not prove whether price, cost or sales mix caused the difference. Keep the month values as dates, and label the dollar and percentage charts separately so their scales remain clear.
A horizontal revenue bar chart shows the customer ranking directly. Keep a common zero baseline so bar lengths represent amounts. Preserve the tie between C and D rather than implying that one contributes more.
For concentration, keep the customer table sorted by revenue and add RevenueShare in F1 and CumulativeShare in G1. Enter these formulas in row 2 and fill down:
=B2/SUM($B$2:$B$7)
=SUM($B$2:B2)/SUM($B$2:$B$7)
Customers A and B contribute 300,000 + 220,000 = $520,000, or 520,000 / 990,000 = 52.5% of revenue. The final cumulative share must equal 100.0%. A ranked bar display with that cumulative measure helps the account owner judge concentration without assuming that a fixed percentage rule applies to every business.
The Pareto analysis guide owns the detailed cumulative-chart construction. Keep this customer view focused on the concentration question and the reconciliation to total revenue.
NIST describes a histogram as a summary of a numeric distribution, commonly formed by dividing the range into equal-sized bins and counting observations in each. Here the observation is one customer margin, not one sales dollar.
Use intervals of ten percentage points with the lower boundary included and the upper boundary excluded:
| Margin interval | Customer count |
|---|---|
| 0% to below 10% | 0 |
| 10% to below 20% | 1 |
| 20% to below 30% | 1 |
| 30% to below 40% | 1 |
| 40% to below 50% | 3 |
| Total | 6 |
If lower and upper bounds are in J2 and K2, entered as percentages, count the interval with:
=COUNTIFS($E$2:$E$7,">="&J2,$E$2:$E$7,"<"&K2)
Verify the bin boundaries in the chart rather than accepting defaults. The counts must total the six eligible customers, and the observations must fit inside the stated range.
The unweighted customer-average margin is 31.7%, from the six percentages in the source table. That differs from the company's 34.9% because larger customers carry more revenue. A distribution chart answers how customers vary; it does not replace the company profitability ratio.
For a different question, plot each customer's revenue against its margin in a scatter chart. NIST explains that scatter plots reveal association between paired variables. Any pattern is a prompt for investigation, not proof that customer size causes profitability.
After selecting the views, use the Excel dashboard build guide for assembly and Excel dashboard examples for layouts. Covirage is not affiliated with Microsoft.
Bring an Excel file with customer, period, revenue and matched cost columns. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own export.
See Excel analysis in Covirage for the supporting workflow.
Use a line chart when the order and spacing of periods matter. Keep the periods consistent, label the currency and state whether revenue is gross or net. Show margin separately when combining its percentage scale with revenue would make the comparison harder to read.
A bar chart compares named categories such as customers or regions. A histogram groups numeric observations into intervals and counts how many fall in each interval. Use a histogram to inspect the spread of customer margins, rather than to rank individual customers.
Sort customers by revenue and use horizontal bars for their amounts. Add cumulative revenue shares when the question is concentration. Calculate each share against the full included customer total, and state how excluded accounts, credits or negative revenue are handled.
An average of customer margins weights every customer equally. Company margin divides total gross profit by total revenue and therefore reflects revenue weights. Both can answer useful questions, but label them separately and use the company ratio when reporting overall profitability.