How to Make a MIS Report in Excel for Accounts (With Quick Steps)

What Is a MIS Report?

MIS stands for Management Information System. It is a summarized report that shows the filtered data of a vast dataset. This type of report is widely used in our regular professional lives to provide data to higher authorities.

Classifications of MIS Report

According to a person’s requirement, an MIS report can be classified into several types:

Sales Report
MIS Report in Accounts
Budget Report
Production Report
Cash Flow Statement Report
Funds Statement Report
Profit Report
Income Statement Report
Abnormal Losses Report
Costing Report
HR Report
Inventory Report
Statistical Publication Report
Orders in Hand Report
Report on Ideal Time
Machine Utilisation Report
Summary Report
Trend Report
Exception Report

Advantages:

MIS reports provide us with considerable advantages in our daily professional lives.

MIS report helps in data management
To define the data trend
Set up the organization’s goal
Identifying the problems
Increasing and improving the efficiency of a company
Reduced production costs & errors

The dataset below is a stationery shop’s account report for a month. In our dataset, we take a list of 10 stationery products in column B. Their quantity is in column C, the month’s sales amount is in column D, and the remaining inventory is in column E. The cost of the shop owner, income, and profit are in columns F, G, and H. Our dataset is in the range of cells B5:H14.

Step 1: Import Your Dataset

Here, we are going to input our dataset into Microsoft Excel. You can also import it from other types of data files. Use the SUM function to sum the Cost, Income, and Profit columns.

To sum the Profit, enter the following formula in cell H15:

=SUM(H5:H14)

Follow the same process for F15 and G15 to get the sum of Cost and Income.

Step 2: Create a Pivot Table

Select the entire range of cells B4:E14.

In the Insert tab, select PivotTable from the Tables group.

A small dialog box called PivotTable from table or range will appear.
Make sure to check the New Worksheet option to preserve your original dataset.
Click OK.

You will see a new sheet entitled Sheet in the Sheet Name Bar.
You will see a side window titled Pivot Table Field. You will also see the columns’ names here.

Drag the field name into the 4 boxes below the title Drag fields between areas below according to your needs. We keep the Item in the Rows and the other three columns in the Values.
You will see that when you start to input the field name into the four boxes, they will be inserted into the pivot table one by one.
Click the Close button on the right corner of that side window to close.

Step 3: Insert Charts for Pivot Fields

Select the entire range of cells A3:D14.

Select the Column or Bar Chart drop-down arrow from the Charts group.
Select the Clustered Column option from the 2-D Column section.

The chart will appear.
Click on the Chart Element icon and check the elements you want to keep. In this case, we checked only the Axes and Data Table elements.

You can also modify your chart style and texts from the Design and Format tabs.
We choose Style 8 for our chart.
- To do so, select the Style 8 option from the Chart Styles group.
- Use the Resize icon at the edge of the chart for better visualization.

Select the whole pivot table and from the Charts group, insert the 100% Stacked Column from the drop-down arrow of Column or Bar Chart.

Click the Chart Element icon and keep the Axes and Legend in this chart. Keep the same chart style as the previous chart.

For the Bar chart, follow the same process, and in the drop-down arrow of Columns or Bar Chart, select the Clustered Bar option from the 2-D Bar section.

Modify the number of elements and the chart style according to your desire. We choose Style 3 and only the Axes element in this chart.

Fr the Pie chart, select the entire range of cells A3:D14 and select the drop-down arrow of the Insert Pie or Doughnut Chart option.
Select the 3-D Pie option.

Adjust the chart style. We chose Style 8 for our chart and eliminated all the chart elements.

Step 4: Insert a Slicer

Go to the Pivot Chart Analyze tab.
Select the Inset Slicer option from the Filter group.

A small dialog box entitled Insert Slicer will appear.
Check the field you want in the Slicer. For our dataset, we only choose the Item field.
Click OK to close the dialog box.

You will get the Slicer titled Item, which contains all the values of the Item column.
Use the Resize icon at the edge of the Slicer to show all the values.

Step 5: Generate a MIS Report

Create a new sheet and set the name of that sheet. We set the sheet name as Report.
Select a chart, and from the PivotChart Analyze tab, select the Move Chart option from the Action group.

The Move Chart dialog box will appear.
Check the Object in option, and from the drop-down arrow of that box, choose the Report sheet.
Click OK to see the chart moved into that sheet.

Select a chart and press ‘Ctrl+C’ to copy the chart.
Go to the Report sheet and press ‘Ctrl+V’ to paste. It will make a copy of your chart.
Follow the same process for the rest of the elements.
Rearrange them in the sheet to get a perfect visualization of all the charts and the Item Slicer.