sodcalculator

10 Tech How-To

How to Create Reports in Excel: Step-by-Step

Guide 10-43255 · Updated · Step by step

This guide walks you through building a clear, professional report in Microsoft Excel, from organizing raw data to adding summaries, charts, and print-ready formatting. You’ll learn the essential steps that work in Excel 2016 through Microsoft 365, so you can turn a messy spreadsheet into something your manager or client can actually read.

What You’ll Need

  • Microsoft Excel (2016 or later, including Microsoft 365)
  • A dataset to report on – at least 10–20 rows of data with column headers (e.g., sales transactions, survey responses, project tasks)
  • Basic familiarity with selecting cells and typing in Excel

Step-by-Step

  1. Prepare your raw data. Put your data in a single worksheet with one header row at the top. Each column should have a unique, descriptive name (e.g., “Date”, “Region”, “Sales Amount”). Remove any blank rows or columns within the data range, and make sure there are no merged cells in the header row. This step is critical – Excel’s reporting tools work best on clean, rectangular data.
  2. Convert your data to an Excel Table. Click anywhere inside your data, then press Ctrl+T (or go to Insert > Table). In the dialog, confirm the range includes your headers and check “My table has headers”. Click OK. This gives you a structured table that automatically expands when you add new rows, and it keeps formulas and formatting consistent.
  3. Add total rows for quick summaries. With the table selected, go to the Table Design tab (called Table Tools in older versions) and check the “Total Row” box. A new row appears at the bottom with a dropdown in the last column. Click that dropdown and choose a summary function – for numeric columns, pick Sum, Average, Count, Max, or Min. This gives you an instant summary without writing a formula.
  4. Create a PivotTable for deeper analysis. Select any cell in your table, go to Insert > PivotTable. In the dialog, choose “New Worksheet” and click OK. On the right, the PivotTable Field List appears. Drag fields into the four areas:
    • Rows: the category you want to group by (e.g., Region)
    • Values: the numeric field you want to summarize (e.g., Sales Amount) – Excel will default to Sum, which is usually what you want
    • Columns: optional – use if you want a cross-tab (e.g., Year across the top)
    • Filters: optional – use to filter the whole report by a field (e.g., Product Line)

    The PivotTable updates instantly as you rearrange fields.

  5. Add a chart to visualize key numbers. Click anywhere in your PivotTable. Go to Insert > Charts and choose a chart type. For category comparisons, a column or bar chart works well; for trends over time, use a line chart. Excel will create the chart from the PivotTable data. You can move it to a chart sheet or keep it on the same sheet. To change the chart title, click it and type your own.
  6. Apply professional formatting. Select your PivotTable (or the table) and go to Home > Format as Table (or PivotTable Styles). Choose a style with good contrast. For numbers, right-click the cells and select Format Cells. Under Number, choose Currency or Number with 0–2 decimal places. For dates, pick a format like Mar-14 or 14-Mar-2024. Make sure the header row is bold and the column widths are wide enough to fit the content.
  7. Add a report title and notes. Insert a few blank rows above your table or PivotTable. Type a clear title like “Q3 2025 Sales by Region”. Below the report, add a small section titled “Notes” where you explain any assumptions or data sources. This makes the report self-explanatory for others.
  8. Set up print layout (if needed). Go to Page Layout > Page Setup. Set the orientation to Landscape for wide reports. Under Scale to Fit, set the width to 1 page (so it doesn’t spill over horizontally). Use Print Titles to repeat the header row on every printed page. Click Print Preview to check how it looks before printing.
  9. Save as PDF for sharing. Go to File > Save As and choose PDF from the file type dropdown. This ensures the report looks the same on any device. Alternatively, you can copy the range and paste it into Word or PowerPoint as a picture.

Common Mistakes

  • Leaving blank rows in the data. Excel treats blank rows as the end of your data, which breaks PivotTables and charts. Always remove them.
  • Using merged cells in headers. Merged cells confuse PivotTables and sorting. Keep each header in its own cell.
  • Putting numbers and text in the same column. For example, writing “$1,200” as text instead of a number. This prevents summing. Use the number format instead of typing the dollar sign.
  • Forgetting to refresh the PivotTable. If you add new data to your source table, the PivotTable doesn’t update automatically. Right-click it and choose Refresh.
  • Overcomplicating the chart. Too many series or 3D effects make the report harder to read. Stick to simple bar or line charts.

Tips

  • Use Ctrl+T for tables – it’s the single most useful shortcut for report building.
  • Name your tables (e.g., “SalesData”) in the Table Design tab. This makes formulas like =SUM(SalesData[Amount]) much easier to read.
  • Use slicers (Insert > Slicer) with your PivotTable to create interactive filter buttons. They’re intuitive for viewers.
  • For recurring reports, record a macro (View > Macros > Record Macro) of your formatting steps – then replay it next month.
  • Keep raw data on a separate sheet (e.g., “Data”) and the report on another (e.g., “Report”). This prevents accidental edits.

Troubleshooting

  • PivotTable shows “Blank” or “(blank)”. This usually means there are empty cells in your source data. Go back to the raw data, fill in or delete those rows, then refresh the PivotTable.
  • Numbers are summing to zero. Your values are probably stored as text. Select the column, go to Data > Text to Columns, and click Finish – this converts text numbers to real numbers.
  • Chart doesn’t show the right data. Right-click the chart and choose Select Data. Check that the range includes the correct columns. If you changed the PivotTable, the chart should update automatically, but if not, click Refresh.
  • Table doesn’t expand when I add rows. Make sure you’re typing directly below the last row of the table. If you paste data below, Excel may not include it. Use the resize handle at the bottom-right corner of the table to drag it down.
  • Print cuts off columns. In Page Layout, set Scale to Fit width to 1 page. Also check that your print area is set correctly (Page Layout > Print Area > Set Print Area).

Frequently Asked Questions

  1. What’s the difference between a table and a range in Excel? A table is a range that Excel treats as a single unit – it has built-in filtering, structured references, and auto-expansion. A plain range is just cells with no special properties. For reports, always use a table.
  2. Can I create a report without a PivotTable? Yes. You can use SUMIFS, COUNTIFS, and other formulas to summarize data manually. But PivotTables are faster and more flexible, especially for large datasets.
  3. How do I make my report update automatically when new data is added? If you use an Excel Table as the source, new rows are included automatically when you refresh the PivotTable. To avoid manual refresh, you can use a dynamic array formula (e.g., =SORT(FILTER(...))) in Excel 365, but PivotTables still need a refresh.
  4. Why does my PivotTable show “Count” instead of “Sum”? This happens when the field contains text or blank cells. Ensure the column has only numbers (no text like “N/A”). If it does, convert it to a number and refresh.
  5. Can I add a logo or image to my report? Yes. Go to Insert > Pictures, select your image, and place it near the title. Be careful with print layout – set the image to move and size with cells if you want it to stay in place.
  6. How do I share an Excel report without giving away the formulas? Save as PDF or use File > Info > Protect Workbook > Mark as Final. To truly hide formulas, you can protect the sheet with a password, but note that this isn’t secure for sensitive data.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →