Creating Effective MIS Reports in Excel

Hey everyone, welcome to Simply Learn's YouTube channel. In this session, we will be discussing about MIS reports in Excel. Before we begin, make sure that you have subscribed to our YouTube channel and don't forget to hit that bell icon to never miss an update from Simply Learn. So without further ado, let's get started with our one point agenda that is MIS reports in Excel. So first, let's understand what exactly is MIS report in Excel. So MIS stands for Management Information System. MIS reports provide data on different categories for accurate decision making. So MIS in Excel is a procedure to create interactive report using Microsoft Excel. So MIS reports help management to access the performance of organization and allow faster decision making. So if you know to create the MIS reports using various business intelligence tools such as Excel, then you might find a very good job opportunity in a well-established company. Now let us try to learn how to create MIS report using Excel in a practical mode. So let's get back to the Microsoft Excel. If getting your learning started is half the battle, what if you could do that for free? Visit SkillUp by SimplyLearn. Click on the link in the description to know more. So here you can see that I've got some sales data of various car manufacturing companies and I've also got some slicers. So using these slicers, I can get the real-time information from the dashboard. So I'll select an year from the first slicer and I'll go into the second slicer and I'll select a company. Now I'll select BMW and SUV type. So we have the data of all the SUV types. Cars manufactured by BMW Car Manufacturing Company in the year 2018. Now, let's start and create something similar to this one. So, how did I create this MIS report? Let's go through this in a step-by-step way. So, on my screen, you can see that we have created a completely new Excel workbook and we have some sample data with 5 rows and 20 columns. So, this particular data is based on the manufacture year and the car manufacturing company and the type of the car manufactured and number of cars manufactured in that particular year and also the price of that particular car. Now, to create MIS reports, we need to play around pivot tables. So, we also have tutorials on pivot tables and you can go through that in our Simply Learn YouTube channel and also we will try to link those in the description box below. Now, let's select the entire data. and go into the insert ribbon and there you can see the first option that is pivot table. So after selecting the pivot table option, it will show this particular dialog box where it will ask you for the range of cells you need to select and the next one is to create the pivot table in the same sheet or a new sheet. So we'll take a new sheet here. Now select okay and there you go. You have the pivot table here. So first you have an empty pivot table. And on the right side, you can see we have pivot table fields. Now, you can either drag these options into the rows and columns or you can also click it. So, now let's drag and drop these. First, we will select year and car. Followed by that, we will take type, quantity and price to columns. So, there you go. We have our pivot table now. Let's select all the data in the pivot table. And again, let's get back to the insert option. And here, you can see an option called pivot chart. Select that. And you will have various pivot chart options here. Now, you can select any one which you can prefer or which you like or whichever you think can represent your data in the best way possible. So, now let's go with column chart first. Let's select OK. and we have our column chart over here. Now this happens to be our first chart. Now let's create a few more charts. So to create few more charts, we'll go back to the same original data. Select again Insert and Pivot Table. Now again a new worksheet. So here we have another empty pivot table. In the similar way, let's drag the same data into rows and columns. Day. and again we will select all the data and create a pivot chart go to insert option pivot chart and now let's try to select pie chart select ok and we have our pie chart over here now let's try to create one more same procedure select table select pivot table then create new worksheet for the new pivot table and now drag and drop the data now let's go to insert again let's select all the data go to insert option and pivot chart now let's try to select the bar chart select ok And there we have our bar chart. Now, remember the first chart we created that is in sheet 2, this one. So, we will try to move all the charts to this particular table or this particular worksheet. So, let's get back to the sheet number 3. Right click and you can see an option called Pivot Chart Analyze. So, from here, you can move the chart. Select the option of moving the chart. And don't select the first one. Select the Object In option. and select the sheet number to which you want to move this chart. For now, we want to move this chart to sheet number 2. Select that, press OK. Now, we have the chart moved to the sheet number 2. And in the same way, let's go to the sheet number 4. And let's move this particular chart to sheet number 2. There you go, the chart got moved to sheet number 2. Now, you can insert the slices. So, we have selected the third sheet or third chart and inside the pivot chart analyze option, we have an option of inserting the slicer. Now, we will insert the slices for ear, car and type. Now select OK and you will have all the three slices onto your sheet. Now let's rearrange them. So I am just organizing them in a neat way. There you go. And you can also customize your designs for all the charts, so that they look a little more better. Now, if you see, if I make some changes, only one chart will be interacting for this. Right? So if I select Ferrari and year 2020 and option as sports, You can see only one chart is interacting to these particular slices. Why? Because all these slices are connected to just one chart here. So, you need to make sure that all these slices are connected to all the three charts what we created. So, let's erase all the filters and select one slicer at a time. Right click and you will see an option of report connections. Select that and you see that the connections are only connected to the pivot table 3. So, select the remaining pivot tables as well, select OK. And in the same way, select the second slicer, select the report connection option, connect pivot table 1 and 2 and select OK. And in the same way, the third one. So, now all the slices are connected to all the pivot charts. Now, if you make some changes here, they will respond automatically in the same way. Now, let's select the Ford company, the year 2019 and hatchback option and you have all the cars which were made in the year 2019 from Ford company in the segment of hatchback. So, this was the sample data set that we worked on. Now, you can also work on some complex data sets such as this Superstore data set. Now, one thing you need to make sure that when you're working with XLMIS, you need to make sure that all the macros are disabled on your data set. and you convert the entire data into the regular table format of excel. Right now, excel is considering this particular data as a database. So for that, you need to select all the data, press Ctrl T and then, convert the entire data in the form of tabular data and also don't forget to click this icon which reads my table has headers so the first row is the headers now let's select ok and your entire data will be converted into tabular form now let's reset this to the normal type which has the clear color not all the fancy colors now the entire data is converted from the dbms to the tabular form And now, he can start implementing the MIS reports in pivot tables. So, that's how you work on MIS report in Excel. With that, we have come to an end of this tutorial on MIS report in Excel. If you have any queries regarding the topics covered in this session or if you need the data set that we used in this particular tutorial, then please feel free to write us down in the comment section below and our team of experts will be happy to resolve all your queries. Until next time, thank you, stay safe and keep learning. Hi there, if you like this video, subscribe to the Simply Learn YouTube channel and click here to watch similar videos. To nerd up and get certified, click here.

So MIS stands for Management Information System. MIS reports provide data on different categories for accurate decision making. So MIS in Excel is a procedure to create interactive report using Microsoft Excel. So MIS reports help management to access the performance of organization and allow faster decision making.

So if you know to create the MIS reports using various business intelligence tools such as Excel, then you might find a very good job opportunity in a well-established company. Now let us try to learn how to create MIS report using Excel in a practical mode. So let's get back to the Microsoft Excel. If getting your learning started is half the battle, what if you could do that for free? Visit SkillUp by SimplyLearn.

Click on the link in the description to know more. So here you can see that I've got some sales data of various car manufacturing companies and I've also got some slicers. So using these slicers, I can get the real-time information from the dashboard. So I'll select an year from the first slicer and I'll go into the second slicer and I'll select a company. Now I'll select BMW and SUV type.

So we have the data of all the SUV types. Cars manufactured by BMW Car Manufacturing Company in the year 2018. Now, let's start and create something similar to this one. So, how did I create this MIS report?

Let's go through this in a step-by-step way. So, on my screen, you can see that we have created a completely new Excel workbook and we have some sample data with 5 rows and 20 columns. So, this particular data is based on the manufacture year and the car manufacturing company and the type of the car manufactured and number of cars manufactured in that particular year and also the price of that particular car. Now, to create MIS reports, we need to play around pivot tables.

So, we also have tutorials on pivot tables and you can go through that in our Simply Learn YouTube channel and also we will try to link those in the description box below. Now, let's select the entire data. and go into the insert ribbon and there you can see the first option that is pivot table. So after selecting the pivot table option, it will show this particular dialog box where it will ask you for the range of cells you need to select and the next one is to create the pivot table in the same sheet or a new sheet. So we'll take a new sheet here.

Now select okay and there you go. You have the pivot table here. So first you have an empty pivot table.

And on the right side, you can see we have pivot table fields. Now, you can either drag these options into the rows and columns or you can also click it. So, now let's drag and drop these. First, we will select year and car. Followed by that, we will take type, quantity and price to columns.

So, there you go. We have our pivot table now. Let's select all the data in the pivot table. And again, let's get back to the insert option.

And here, you can see an option called pivot chart. Select that. And you will have various pivot chart options here.

Now, you can select any one which you can prefer or which you like or whichever you think can represent your data in the best way possible. So, now let's go with column chart first. Let's select OK. and we have our column chart over here. Now this happens to be our first chart.

Now let's create a few more charts. So to create few more charts, we'll go back to the same original data. Select again Insert and Pivot Table.

Now again a new worksheet. So here we have another empty pivot table. In the similar way, let's drag the same data into rows and columns.

Day. and again we will select all the data and create a pivot chart go to insert option pivot chart and now let's try to select pie chart select ok and we have our pie chart over here now let's try to create one more same procedure select table select pivot table then create new worksheet for the new pivot table and now drag and drop the data now let's go to insert again let's select all the data go to insert option and pivot chart now let's try to select the bar chart select ok And there we have our bar chart. Now, remember the first chart we created that is in sheet 2, this one. So, we will try to move all the charts to this particular table or this particular worksheet.

So, let's get back to the sheet number 3. Right click and you can see an option called Pivot Chart Analyze. So, from here, you can move the chart. Select the option of moving the chart.

And don't select the first one. Select the Object In option. and select the sheet number to which you want to move this chart. For now, we want to move this chart to sheet number 2. Select that, press OK. Now, we have the chart moved to the sheet number 2. And in the same way, let's go to the sheet number 4. And let's move this particular chart to sheet number 2. There you go, the chart got moved to sheet number 2. Now, you can insert the slices.

So, we have selected the third sheet or third chart and inside the pivot chart analyze option, we have an option of inserting the slicer. Now, we will insert the slices for ear, car and type. Now select OK and you will have all the three slices onto your sheet. Now let's rearrange them.

So I am just organizing them in a neat way. There you go. And you can also customize your designs for all the charts, so that they look a little more better.

Now, if you see, if I make some changes, only one chart will be interacting for this. Right? So if I select Ferrari and year 2020 and option as sports, You can see only one chart is interacting to these particular slices. Why? Because all these slices are connected to just one chart here.

So, you need to make sure that all these slices are connected to all the three charts what we created. So, let's erase all the filters and select one slicer at a time. Right click and you will see an option of report connections. Select that and you see that the connections are only connected to the pivot table 3. So, select the remaining pivot tables as well, select OK. And in the same way, select the second slicer, select the report connection option, connect pivot table 1 and 2 and select OK.

And in the same way, the third one. So, now all the slices are connected to all the pivot charts. Now, if you make some changes here, they will respond automatically in the same way.

Now, let's select the Ford company, the year 2019 and hatchback option and you have all the cars which were made in the year 2019 from Ford company in the segment of hatchback. So, this was the sample data set that we worked on. Now, you can also work on some complex data sets such as this Superstore data set. Now, one thing you need to make sure that when you're working with XLMIS, you need to make sure that all the macros are disabled on your data set. and you convert the entire data into the regular table format of excel.

Right now, excel is considering this particular data as a database. So for that, you need to select all the data, press Ctrl T and then, convert the entire data in the form of tabular data and also don't forget to click this icon which reads my table has headers so the first row is the headers now let's select ok and your entire data will be converted into tabular form now let's reset this to the normal type which has the clear color not all the fancy colors now the entire data is converted from the dbms to the tabular form And now, he can start implementing the MIS reports in pivot tables. So, that's how you work on MIS report in Excel. With that, we have come to an end of this tutorial on MIS report in Excel. If you have any queries regarding the topics covered in this session or if you need the data set that we used in this particular tutorial, then please feel free to write us down in the comment section below and our team of experts will be happy to resolve all your queries.

Until next time, thank you, stay safe and keep learning. Hi there, if you like this video, subscribe to the Simply Learn YouTube channel and click here to watch similar videos. To nerd up and get certified, click here.

Transcript for:Creating Effective MIS Reports in Excel

Transcript for:
Creating Effective MIS Reports in Excel