We store important information in our Excel worksheet. We also perform necessary operations on our data to analyze different things from time to time. Now, generating a report on a regular time from these Excel data is essential for a company or other institutions. They can understand the improvements or can get proper knowledge on the area that needs to be improved through these reports. In this article, we’ll show you the effective yet simple methods to Generate Reports from Excel Data.
Download Practice Workbook
Download the following workbooks to practice by yourself.
2 Easy Methods to Generate Reports from Excel Data
To illustrate, we’ll use a sample dataset as an example. For instance, the below dataset represents 3 Months (January – March), 2 Products (AC and Heater), and the Net Sales of a company. In this article, we’ll generate reports on the Sum of Net Sales by Month and also by Products.
1. Insert Chart to Generate Reports from Excel Data
1.1 Add Recommended Charts
- First, select the range B4:C10.
- Then, go to Insert ➤ Recommended Charts.
- As a result, the Insert Chart dialog box will pop out.
- There, select your desired chart type from the left pane.
- For this example, select Clustered Column. It’ll return a chart showing the Net Sales of each product in each month in 2 Different Colors. Hence, it’s easy to distinguish.
- After that, press OK.
- Consequently, you’ll get your desired chart in a new worksheet as shown below.
- Moreover, you can click the Month and Product drop-down icons to sort out your required fields.
- Additionally, you can save the chart as a separate picture if you wish.
- For that purpose, select the chart and right-click on the mouse.
- Lastly, choose Save as Picture.
1.2 Create Chart Manually
However, if you wish to create your chart instead of the Excel recommendations, follow the below steps.
- Firstly, choose B4:C10 and select the Insert tab.
- Next, choose your desired chart. In this example, press the 2-D Line graph with Markers.
- Thus, you’ll get a line graph as demonstrated below.
- Here, you can modify your chart by pressing the 3 different icons shown in the red-colored box beside the chart.
- For instance, we change the chart style by clicking the middle icon and choosing the desired style. See the figure below.
- How to Make Daily Activity Report in Excel (5 Easy Examples)
- Make Daily Production Report in Excel (Download Free Template)
- How to Make Daily Sales Report in Excel (with Quick Steps)
- Create a Report That Displays Quarterly Sales in Excel (with Easy Steps)
- How to Make MIS Report in Excel for Sales (with Easy Steps)
2. Apply Excel PivotTable Feature for Generating Reports
PivotTable is a very useful feature in Excel. In this method, we’ll apply this feature to generate our reports. Therefore, learn the following steps for performing the task.
- Select B4:C10 at first.
- Now, click the Insert tab and choose PivotTable ➤ From Table/Range.
- Next, a dialog box will appear. There, press OK.
- As a result, a new worksheet will emerge. On the right side pane, you’ll get to see PivotTable Fields.
- Subsequently, check the Month and Net Sales.
- Place Month in Rows and Net Sales in the Values section.
- Hence, it’ll return the report as shown below where the Sum of Net Sales is on the basis of Months.
- Again, clear the checkmark for the Month and place the Product in the Rows section.
- At last, it’ll return the report based on the products.
- Now, to add Slicer, go to PivotTable Analyze.
- Press Insert Slicer from the Filter section.
- Finally, you’ll get the slicers and make the required changes through the slicers to see your desired results.
How to Print Reports Generated from Excel Data
In the end, we may also need to print the reports instead of just keeping them in the Excel workbook. Hence, learn the process to carry out the operation.
- First of all, go to the Insert tab.
- Press Header & Footer from the Text drop-down.
- Then, type the Header as given below.
- Afterward, hide the sheets that you don’t want on the report.
- For that, select the sheet and right-click on the mouse.
- Choose Hide.
- Next, go to the File tab.
- In the File window, select Print.
- Choose Print Entire Workbook, Landscape Orientation, Fit All Columns on One Page.
- In the end, select Print and it’ll generate a PDF file of the report.
Henceforth, you will be able to Generate Reports from Excel Data following the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.