To make an analysis report in Excel, organize clean records in a table, summarize them with a PivotTable or formulas, add charts and clear explanations, then check and refresh the results. Excel does not have one single “analysis report” command: the best setup depends on what you need to compare and who will use the report.
What an Excel analysis report includes
An analysis report turns underlying data into a concise account of what happened and what deserves attention. It is more than a worksheet full of numbers or a chart without context. A useful report usually gives readers a question, a measure, a comparison, and a takeaway. For example, a sales report might answer how much was sold this quarter, which regions contributed most, and whether performance changed from the previous quarter.
Excel is well suited to reports built from structured data such as sales transactions, survey responses, inventory movements, or monthly expenses. The workbook can contain both detailed source records and summarized views, but readers should be able to tell which sheet is the source and which is the report. A report may be a dedicated worksheet in the workbook or a carefully prepared range that is printed or exported to PDF.
Before building anything, decide who will read the report and what decision or question it needs to support. That choice influences the measures, level of detail, chart type, and amount of explanation. A manager checking monthly trends needs a different view from someone reconciling individual transactions.
Prepare and check the source data
Summaries are only as reliable as their source. Arrange records in a rectangular list with one header row, one record per row, and one field per column. Avoid blank rows within the list, merged cells in the data area, and multiple unrelated tables in the same range. Use consistent column names, dates, units, and category labels.
For instance, a sales table could contain Date, Region, Product, and Sales columns. Each row would represent one sale or one clearly defined sales record. If one row contains a monthly total while another contains a single transaction, the report may compare unlike things. Establish what a row represents before summing or averaging it.
Check common data-quality issues before creating a summary:
- Look for dates stored as text, numbers stored as text, missing values, and duplicate records.
- Standardize labels such as “North” and “North ” so the same region is not split into two categories.
- Confirm that amounts use one currency and one consistent unit, and that percentages are stored consistently.
- Check whether blank cells mean zero, unknown, or not applicable; those meanings are not interchangeable.
In Excel for Microsoft 365 on Windows, select a cell in the data and choose Insert > Table. Confirm that the table has headers, then choose OK. A table expands as rows are added and helps formulas and PivotTables refer to a growing dataset. Give the table a descriptive name on the Table Design tab if you will use it in formulas or documentation.
Choose a report structure
A good report starts with the central measure and a useful comparison. Total sales alone may be hard to interpret; sales by region, by month, or against a target supplies context. Pick a small number of measures that answer the question rather than adding every available field.
A practical worksheet layout separates the information into layers:
- Headline: State the period covered and the principal finding, such as “Sales were $5,300 across five sample transactions.”
- Key measures: Show a few important totals, averages, counts, or rates with labels and units.
- Breakdown: Use a PivotTable or compact summary to show how the result varies by category or time.
- Visual and explanation: Add a chart where it clarifies a comparison, then explain a notable pattern and any limitations.
Include the reporting period and, when relevant, the date the source was last updated. If the figures omit canceled orders, exclude tax, or cover only selected departments, say so. A brief note about scope can prevent a reader from treating a limited dataset as a complete picture.
Summarize records with a PivotTable
A PivotTable is a flexible way to group and aggregate records without writing a separate formula for each category. To create one in Excel for Microsoft 365 on Windows, select a cell in the source table and choose Insert > PivotTable. Check the proposed table or range, choose where to place the PivotTable, and select OK. In the PivotTable Fields pane, drag fields into Rows, Columns, Values, or Filters.
For a regional sales summary, drag Region to Rows and Sales to Values. Excel will usually summarize numeric fields by sum, but check the value field settings rather than assuming. If the data contains text or missing numbers, the default or resulting summary may not be the measure you expect. Use the PivotTable to answer a focused question, then format totals and number formats so readers can interpret them.
As a small example, suppose a table has these five records: North sales of 1,200 and 800; South sales of 900 and 1,300; and West sales of 1,100. A PivotTable grouped by Region should show North at 2,000, South at 2,200, and West at 1,100, for a grand total of 5,300. These figures are illustrative; the main point is to confirm the grouped totals against the underlying records.
If source data changes, refresh the PivotTable. Select it and use PivotTable Analyze > Refresh, or right-click within the PivotTable and choose Refresh. If you add records to a properly defined Excel table, the table can expand to include them; refresh the PivotTable afterward so its summary reflects the new rows. On Mac, PivotTables are also available, though the ribbon arrangement and some labels can differ by Excel version.
Use formulas for direct summaries
Formulas are useful when you need a fixed summary, a small set of key metrics, or calculations that sit alongside report text. With the five example sales values in C2:C6, the total can be calculated using =SUM(C2:C6). The result is 5,300 because 1,200 + 900 + 800 + 1,100 + 1,300 = 5,300.
With the same sales values in C2:C6, the average is calculated with =AVERAGE(C2:C6). The result is 1,060, because 5,300 divided by five records equals 1,060. An average should be labeled clearly: it is the mean value per record here, not necessarily average sales per customer or per day.
To calculate a total by category, assume region labels are in A2:A6, sales are in C2:C6, and D2 contains the category North. The formula =SUMIFS($C$2:$C$6,$A$2:$A$6,D2) returns 2,000. SUMIFS adds values from the sales range only when the corresponding region matches the value in D2. For a report with many categories or frequent changes, a PivotTable may be easier to maintain than a long set of category-specific formulas.
Keep formulas readable and verify them using a second method when the result matters. For example, compare a formula total with a PivotTable grand total. Use cell references for inputs that may change, and label the cells so another person can understand what each calculation represents. Avoid typing a result manually over a formula cell; that removes the calculation and can make later updates misleading.
Select charts that fit the comparison
Choose a chart based on the relationship you want readers to see. A column or bar chart works well for comparing a modest number of categories. A line chart is generally suitable for a value measured at regular time intervals, such as monthly sales. A pie chart can show parts of a whole when there are only a few categories and the values form a meaningful total, but it becomes difficult to compare when many slices are similar.
To create a chart, select the summary cells and choose Insert > Recommended Charts, or choose an appropriate chart from the Insert tab. Use the chart title to identify the measure and period, and check that the axis labels and number formats are clear. Do not chart every row of transaction data if the purpose is to compare categories; summarize the data first.
For the sample regional totals, a bar or column chart would make the three values easy to compare. A line chart would not be a good choice because the regions are categories, not points in a sequence of time. Avoid decorative effects that make values harder to judge, and do not truncate an axis in a way that exaggerates small differences without making the scale clear.
Format, explain, and share the report
Use formatting to improve reading, not to make uncertain results look authoritative. Apply consistent number formats, readable headings, and restrained colors. Show currency symbols when the measure is monetary, include units where needed, and use the same decimal precision for comparable values. Conditional formatting can draw attention to values that meet a defined rule, but a color highlight should not replace a written explanation of what the rule means.
Put the most important information near the top and keep detail available below or on a separate source-data sheet. Use descriptive sheet names and remove temporary calculations or unrelated content from the presentation area. If readers need to filter the report, keep the controls obvious and explain whether filtering changes displayed totals.
For a fixed copy, use File > Export to create a PDF, or use the print options to check page orientation, scaling, margins, and print area before distributing it. A PDF preserves a snapshot, not an interactive analysis. If you share the workbook instead, state which sheet is the report and which is the source, and consider whether recipients should be able to edit the underlying data. Excel for the web can open and edit many workbooks, but some features or layout details may behave differently than in the desktop app.
Common problems and how to avoid them
A PivotTable showing “Count of Sales” instead of a sum often indicates that Excel is treating some values as text or that the field contains nonnumeric entries. Inspect the source column, correct inconsistent values, and refresh the PivotTable. A total that seems too high may come from duplicate rows, mixed units, or a source range that includes a subtotal as if it were a transaction.
Missing or unexpectedly split categories often result from inconsistent labels, blank fields, or extra spaces. Clean the source labels and rebuild or refresh the summary. If the report stops including new rows, check whether the source is an Excel table and whether the PivotTable source includes the expanded data.
Another frequent mistake is treating correlation or a change in a chart as proof of cause. A report can show that two measures moved together, but that alone does not establish why. State what the data directly shows, note relevant limits, and avoid claiming that a result applies beyond the records and time period examined.
Frequently Asked Questions
What is an analysis report in Excel?
It is a structured presentation of data summaries, comparisons, and findings created with Excel features such as tables, formulas, PivotTables, and charts. It is not a special file type or a single built-in report command.
Is a PivotTable or a formula better for an Excel report?
Use a PivotTable when you need to group and rearrange many records by fields such as month, product, or region. Use formulas for a small, fixed set of calculations or when results need to appear in a custom layout. They can also be used together.
How do I keep an Excel report up to date?
Keep source records in a consistent Excel table, add new records to that table, and refresh any PivotTables. Check that formulas include new data and verify key totals after updates. If the workbook uses external data connections, follow the relevant refresh process and confirm the connection succeeded.
Can I make an analysis report without charts?
Yes. A clearly labeled table of measures and comparisons may be more effective than a chart when there are only a few values or precise numbers matter most. Add a chart only when it makes a pattern or comparison easier to understand.
Can I share an Excel analysis report as a PDF?
Yes. Export or save a PDF when readers need a stable, noninteractive copy. Check page breaks, scaling, and print areas first, and keep the workbook available separately if readers need to filter, inspect formulas, or update the analysis.
Related
- Excel Scenario Manager: How to Create and Compare Cases
- Scenario Manager and Data Tables in Excel
- Scatter Plot in Excel With 3 Variables: How to Make One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Workbook File Type: Formats and Extensions ExplainedThe standard file type for a modern Excel workbook is .xlsx, used by Microsoft Excel for Microsoft 365…
- How to Open an AIS JSON File in ExcelTo open an AIS JSON file in Excel, import it with Power Query: in Excel for Microsoft 365…
- Excel File Could Be Corrupted or Unsafe? What to DoYes, an Excel file can be corrupted, unsafe, both, or neither: the warning alone does not prove which.…
- How to Update a Data Validation List in Excel Step by StepTo update an Excel drop-down list, select a cell that uses it, go to Data > Data Validation,…
- Google Sheets Dropdown Based on Another Cell: How It WorksYes. In Google Sheets, you can make one dropdown list depend on the value in another cell by…
- Flip Paste in Excel: Turn Rows Into Columns EasilyTo flip-paste a range in Excel for Microsoft 365 on Windows, select and copy the cells, click a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.