How to Make MIS Report in Excel for Sales (With Easy Steps)

In this article, we’re going to show you the quick steps of how to make an MIS report in Excel for sales. To demonstrate our method, we’ve picked a dataset with 5 columns: “Product Name”, “Type”, “Month”, “Region”, and “Sales”.

What Is MIS Report?

MIS stands for “Management Information System”. With this, top management can make effective decisions. This also provides the opportunity of communicating information to all stakeholders. Moreover, a company’s marketing and sales departments can analyze sales data using this report. Consequently, this will help the company to maximize profits. Moreover, there are 5 components of an MIS report – People, Data, Business Procedures, Hardware, and Software. We’ll use these to create our MIS sales report.

How to Make MIS Sales Report in Excel (With Easy Steps)

Step-1: Utilizing Excel PivotTable Feature to Make MIS Sales Report

Firstly, we’re going to use Excel PivotTable to organize our dataset. Secondly, we’ll add Charts. Finally, we’ll move those Charts into a new Sheet and insert Slicers to modify our output. Without further ado, let’s jump into the task.

Steps:

• Firstly, select the cell range B4:F12.
• Secondly, from the Insert tab >>> select PivotTable.

Then, a dialog box will appear.

• Thirdly, select Existing Worksheet and select cell B2 in our “Pivot” Sheet as the output location.

After that, we’ll see the PivotTable Fields dialog box.

• Then, select all the Fields. This will put everything except the Sales fields into Rows.
• After that, drag the “Region” field to Columns.

We should get an output similar to this.

Read More: How to Make Monthly Report in Excel

Step-2: Inserting Charts in Excel

We’ll insert Charts using the PivotTable Tools in this section. Then, we’ll add PivotCharts. This will help us visualize the dataset. To do this –

• Firstly, select anywhere on the PivotTable output data.
• Secondly, from the PivotTable Analyze >>> select PivotChart.

Then the Insert Chart dialog box will appear.

• Thirdly, from Column >>> select Clustered Column.
• Then, press OK.

We’ll see a column Chart on our Sheet.

Then, we’re going to add a Pie Chart.

• Firstly, from the PivotTable Analyze >>> select PivotChart.
• Secondly, from Pie >>> select Doughnut and press OK.

A Doughnut type Pie Chart will be shown.

Finally, we’re going to add Line Chart.

• Firstly, from the PivotTable Analyze >>> select PivotChart.
• Secondly, from Line >>> select “Line with Markers” and press OK.

A “Line Graph” will appear.

Read More: How to Make Sales Report in Excel

Step-3: Moving Charts to Another Sheet

In this section, we’ll move the Charts to a new Sheet named “Graphs”. This will help us to display our data in a clean Sheet.

• Select, any of the three charts.
• Then, from PivotChart Analyze >>> select Move Chart.

The Move Chart dialog box will appear.

• From “Object in” select “Graphs”.
• Then, press OK.

Similarly, move all the Charts to the new Sheet.

loying Slicers in Excel to Make MIS Sales Report

In this step, we’ll add Slicers. This will allow us to apply Filters to our Charts.

• Firstly, select any of the Graphs.
• Secondly, from the PivotChart Analyze tab >>> select “Insert Slicer”.

A dialog box will appear.

• After that, select the first 4 fields then click on OK.

The Slicers will look like this.

We can click on any of the fields to change our datasets.

• Select “March” under the Month Slicer.

This will show only the values from the month of March.

Moreover, our Slicer will modify all the existing Charts. Thus, we’ve created an MIS sales report in Excel.

Read More: How to Make Monthly Sales Report in Excel

Practice Section

We’ve added a practice dataset in the Excel file.

Download Practice Workbook

Conclusion

We’ve shown you quick steps on how to make an MIS report in Excel for sales. If you face any problems, feel free to comment below. Thanks for reading, keep excelling!

Related Articles

• How to Prepare MIS Report in Excel
• How to Make MIS Report in Excel for Accounts
• Create a Report that Displays the Quarterly Sales by Territory
• How to Make Daily Sales Report in Excel
• Create a Report That Displays Quarterly Sales in Excel

<< Go Back to Report in Excel | Learn Excel