How to Create a Report in Excel

Using charts, graphs, and pivot tables makes it easy

What to Know

  • Create a report using charts: Select Insert > Recommended Charts, then choose the one you want to add to the report sheet.
  • Create a report with pivot tables: Select Insert > PivotTable. Select the data range you want to analyze in the Table/Range field.
  • Print: Go to File > Print, change the orientation to Landscape, scaling to Fit All Columns on One Page, and select Print Entire Workbook.

This article explains how to create a report in Microsoft Excel using key skills like creating basic charts and tables, creating pivot tables, and printing the report. The information in this article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac.

Creating Basic Charts and Tables for an Excel Report

Creating reports usually means collecting information and presenting it all in a single sheet that serves as the report sheet for all of the information. These report sheets should be formatted in a way that's easy to print as well.

One of the most common tools people use in Excel to create reports is the chart and table tools. To create a chart in an Excel report sheet:

  1. Select Insert from the menu, and in the charts group, select the type of chart you want to add to the report sheet.

    selecting a chart in Excel
  2. In the Chart Design menu, in the Data group, select Select Data.

    select data in Excel
  3. Select the sheet with the data and select all cells containing the data you want to chart (include headers).

    selecting data in Excel
  4. The chart will update in your report sheet with the data. The headers will be used to populate the labels in the two axis.

    Inserting charts into a report
  5. Repeat the above steps to create new charts and graphs that appropriately represent the data you want to show in your report. When you need to create a new report, you can just paste the new data into the data sheets, and the charts and graphs update automatically.

    Screenshot of inserting charts into report

    There are different ways to lay out a report using Excel. You can include graphs and charts on the same page as tabular (numeric) data, or you can create multiple sheets so visual reporting is on one sheet, tabular data is on another sheet, and so on.

Using PivotTables to Generate a Report From an Excel Spreadsheet

Pivot tables are another powerful tool for creating reports in Excel. Pivot tables help with digging more deeply into data.

  1. Select the sheet with the data you want to analyze. Select Insert > PivotTable.

    selecting Pivot Table in Excel
  2. In the Create PivotTable dialogue, in the Table/Range field, select the range of data you want to analyze. In the Location field, select the first cell of the worksheet where you want the analysis to go. Select OK to finish.

    Create PivotTable dialogue
  3. This will launch the pivot table creation process in the new sheet. In the PivotTable Fields area, the first field you select will be the reference field.

    selecting data to analyze in pivot table

    In this example, this pivot table will show website traffic information by month. So, first, you'd select Month.

  4. Next, drag the data fields you want to show data for into the values area of the PivotTable fields pane. You'll see the data imported from the source sheet into your pivot table.

    Screenshot of a PivotTable analysis in Excel
  5. The pivot table collates all of the data for multiple items by adding them (by default). In this example, you can see which months had the most page views. If you want a different analysis, just select the drop-down arrow next to the item in the Values pane, then select Value Field Settings.

    Value Field Settings in a pivot table.
  6. In the Value Field Settings dialog box, change the calculation type to whichever you prefer.

    changing field calculation type
  7. This will update the data in the pivot table accordingly. Using this approach, you can perform any analysis you like on source data, and create pivot charts that display the information in your report in the way you need.

How to Print Your Excel Report

You can generate a printed report from all the sheets you created, but first you need to add page headers.

  1. Select Insert > Text > Header & Footer.

    inserting a header in Excel
  2. Type the title for the report page, then format it to use larger than normal text. Repeat this process for each report sheet you plan to print.

    creating a header for report pages.
  3. Next, hide the sheets you don't want included in the report. To do this, right-click the sheet tab and select Hide.

    hiding sheet tabs in Excel
  4. To print your report, select File > Print. Change orientation to Landscape, and scaling to Fit All Columns on One Page.

    printing a report using Excel
  5. Select Print Entire Workbook. Now when you print your report, only the report sheets you created will print as individual pages.

    You can either print your report out on paper, or print it as a PDF and send it out as an email attachment.

  • How do I create an expense report in Excel?

    Open an Excel spreadsheet, turn off gridlines, and enter your basic expense report information, such as a title, time period, and employee name. Add data columns for Date and Description, and then add columns for expense specifics, such as Hotel, Meals, and Phone. Enter your information and create an Excel table.

  • How do I create a scenario summary report in Excel?

    To use Excel's scenario manager function, select the cells with the information you're exploring, and then go to the ribbon and select Data. Select What-If Analysis > Scenario Manager. In the Scenario Manager dialog box, select Add. Name the scenario and change your data to see various outcomes.

  • How do I export a Salesforce report to Excel?

    In Salesforce, go to Reports and find the report you want to export. Select Export and choose an export view (Formatted Report or Details Only). Formatted Report will export in .xlsx format, while Details Only gives you other choices. Select Export when ready.

Was this page helpful?