Report in Excel (Using Pivot Table and Charts)

In this Excel tutorial, you will learn how to generate a report in Excel.  We will also see how we can organize raw data with PivotTable, create charts to visualize data and print them in a suitable format.

Here, we prepared the dataset using the Microsoft 365 version of Excel.  However, all the functions and features used in this article can be found in other versions of Excel, starting from Excel 2007.

In Excel, we need to generate reports on various subjects such as creating income and expense reports, summary reports, daily and monthly activity reports, sales and expenses reports, inventory aging reports, MIS reports, report cards, etc.

Download Practice Workbook

What Are the Steps to Create a Report in Excel?

We can create a report in just five easy steps. They are:

• Managing Data
• Inserting Pivot Table to Organize Data
• Creating a Chart to Visualize Data
• Summarizing Report
• Printing Report with Proper Header and Footer

Let’s see those steps one by one. As a demo, I will prepare a Sales Report for a company.

Step-1: Managing Data

• The first step of creating reports is to gather and manage relevant data on which we want to create a report.
• As I will create a sales report, I have gathered some sample sales data containing columns for Salesperson, Region, Product, Category, Date, Units Sold, and Revenue.
• This dataset has 100 rows but I am only showing the first 23 rows.

Click the image for a detailed view.

• As we can see, this dataset is not well organized but rather very scattered.
• To generate a report, we need to organize it in a way from which we can extract meaningful and presentable information.
• To organize this kind of data, the Pivot Table feature in Excel is one of the best options.

Read More: How to Generate PDF Reports from Excel Data

Step-2: Inserting Pivot Table to Organize Data

In Excel, we can organize a large volume of unorganized, scattered data within a second using the Pivot Table feature. Follow the steps below to insert a pivot table from the dataset.

• First, select any cell from the dataset, and then go to the Insert tab and click on the PivotTable option.

• As a result, a new dialog box named PivotTable from table or range will pop up.
• Here, you need to select the source range, the range of the dataset, and the destination where the pivot table will be placed if you want to place it inside the existing worksheet.
• Finally, click OK.

Click the image for a detailed view.

• As a result, you will see a PivotTable layout around the destination cell and a PivotTable Fields list in the right corner.

Click the image for a detailed view.

• Now, according to your choice of how you want to organize and summarize the date, you need to drag fields into different areas.
• In the picture below, you can see how I chose the fields in different areas.

• As you start dragging the fields in different areas, the PivotTable will be simultaneously generated. Then, you may need to change some by default generated Labels.

Read More: Create a Report in Excel as a Table

Step-3: Creating a Chart to Visualize Data

Now that we have successfully organized and summarized the dataset, we can generate some useful charts to visualize the data. To do that, follow the steps below.

• Click on any cell inside the PivotTable and then go to the PivotTable Analyze.
• From there, click on the PivotChart.

Click the image for a detailed view.

• Consequently, the Insert Chart window will be opened. From there, toggle between different chart types and styles and choose the one that best suits your purpose. Finally, click OK.

Click the image for a detailed view.

• As a result, a chart will be generated on the worksheet.

Click the image for a detailed view.

• You may want to add some elements to the chart such as Axis Title, Chart Title, etc, and enhance the beauty of the chart.

Click the image for a detailed view.

Step-4: Summarizing Report

Now that we have generated some graphics for the visual interpretation of our dataset, we can compile the chart and pivot table in one location for printing.

Read More: Create Report That Displays Quarterly Sales by Territory in Excel

Step-5: Printing Report with Proper Header and Footer

After summarizing the report, we may need to print it so that we can present it when required. To print the summary report, follow the steps below.

• Before printing, we need to decide whether we need to print the entire workbook, any specific worksheet, or a specific portion of a worksheet.
• In my case, I just want to print the summary table with the chart. So I will first select that portion.

• Now, go to the File tab.

• From the menu that appears, click on Print.
• Consequently, you will see many options related to printing, such as Printer and different settings related to printing.
• As I wanted to print it as a PDF file, I chose the Microsoft Print to PDF option in the Print section.
• Under the Print Selection menu, I chose only print the current selection. You can also choose the page size, margins, and orientation.

• If you want to add a header/footer, click on the Page Setup below to open the Page Setup dialog box.
• Then, in the menu, go to the Header/Footer.
• From here, you can choose from predefined headers and footer lists or make a custom one.

• You can also customize the margin of the borders by going to the Margins tab.

• After setting up all those parameters, you can see the preview on the right side of the File tab.

• Finally, you can click on the big Print option on the top to print the report.

Read More: How to Make MIS Report in Excel for Sales

How to Customize Reports in Excel

After creating a report, we can make some customizations such as changing the colors of the pivot table, changing the chart style and color, etc. It is very important to know how to customize an existing report in Excel because we may need to change some aspects of the report after presenting it to others. Follow the steps below to change the chart type and color of the report.

Changing Chart Type in Report

• Suppose, we want to change the type of the chart in the report. To do that, first, click on the chart.
• Consequently, you will see the Design tab on the ribbon. Go to that tab.
• Now, click on the Change Chart Type option.

Click on the image for a detailed view.

• As a result, the Change Chart Type window will open up. Now, from here, we can select any chart type. For illustration, I am selecting a Bar Chart.

Click on the image for a detailed view.

• Consequently, the bar chart will be shown in the report.

Read More: How to Prepare MIS Report in Excel

Change the Color of the Chart

• From the Design tab, you can change the color of the chart as well by going to the Change Color command and then selecting your desired color palette.

Click on the image for a detailed view.

• Consequently, we will get the desired color of the chart.

Read More: How to Generate Report in PDF Format Using Excel VBA

Which Things to Remember While Creating a Report in Excel?

• While generating PivotTable, place the fields in the areas that best suit your purpose. You can always try different orientations to check which orientation is best for you.
• Always try to include the necessary details in the report and exclude any kind of unwanted data.
• Before printing, try different page setups to see which one best fits your report.

Frequently Asked Questions

1. How do I add data to my report in Excel?

Answer: To add data to your Excel report, either manually enter it into the cells or copy and paste it from another source. If your data is large or frequently updated, consider using formulas or functions to retrieve data from other worksheets, workbooks, or external sources. Excel also allows you to import data from databases, CSV files, and other file formats.

2. Can I include charts and graphs in my Excel report?

Answer: Absolutely! You can add a variety of chart types to your report, including column charts, line charts, pie charts, and more. Simply choose the appropriate data range and the chart type that best displays your data.

3. Can I export reports in PDF format from Excel?

Answer: Yes! You can export reports using the Microsoft Print to PDF option.

4. What kinds of reports are usually generated using Excel?

Answer: You can generate a whole bunch of reports such as income and expense reports, summary reports, daily and monthly activity reports, sales and expenses reports, inventory aging reports, MIS reports, report cards, etc.

Read More: How to Generate Reports from Excel Data

Conclusion

Thank you for staying with us till the end. Here, I showed all the steps of creating a report in Excel. I hope that by going through the article, you learned how to manage raw data, organize it using PivotTable, generate reports with charts, and print it in a suitable format.

However, one disadvantage of PivotTable is that it does not automatically refresh source data. So each time, the source data is changed, you need to refresh the PivotTable manually, and only then the chart will get updated. Also, the PivotTable does not change the source range when new data is added. We can overcome this issue by converting the source range as an Excel Table and then using the name of the table as the source of the PivotTable.

If you find this article helpful, you can share it with your friends and colleagues. Also, don’t hesitate to leave your questions and thoughts in the comment section of this post. Goodbye!

Report in Excel: Knowledge Hub

<< Go Back to Learn Excel