Hello friends, as we know very well MS Excel is a huge ocean in which you will learn day to day new more tips and tricks. Today we discuss Count values in MIS report PivotTable. After reading this article you will be able to know how to count values in the matched range of cells using the PivotTable in MS Excel. This question is mostly asked by the HR Manager when you are facing the interview session for preparing MIS reports in MS Excel.
In this situation most of the candidate’s make hurry make hurry to solve the problem. But as per my view there is no need to hurry first you must listen the question properly then you must think which command or function is used to solve the problem in MS Excel. Assume you have a slab of range in which number of values exists. Now, someone ask you how to count number of values which exists in the same range. After hearing the question you will be confused which function or command used in MS Excel. How to count number of values which exists in the matched range of cells. To solve this type of problem you have to take following easy steps in MS Excel:
Step 1: First you have to create the given range of cells in any worksheet of MS Excel.
Step 2: Now, if you want to count how much values are exists in the range 0-100 and others values are exists in matched range of cells then you have to use “Pivot Table” option from the “Insert” tab in MS Excel, to overcome this type of problem.
If you have a huge database or table and you want to arrange and summarize you complicated data as per your need or required in the form of table or chart then you have to use “PivotTable” or “PivotChart” option in MS Excel. Both these commands are available in the Insert tab.
Must Read: Microsoft Excel Important Tips and Tricks
Step 3: Select the range of cells A1:B11 > Click on the “Insert” tab > Choose PivotTable option. MS Excel displays Create Pivot Table dialogue box in which you can select the range of cell which you want to use to preparing summary information of your required data. You can also choose the location where you want to place the Pivot Table report either in New Worksheet or set the cell address in the Existing Worksheet then finally click on the OK button.
Step 4: Choose field name which you want to add in your PivotTable Field List box for e.g. turn on both “Value” and “Range” field names.
Step 5: Now, take mouse pointer in the bottom part in which you will get Sum of Value in the “Values” box and Range displays in the “Row Labels” box in the Pivot Table Field List box > Click on the drop down arrow on the Sum of Values option and choose “Value Field Settings..” option > In the Value Field Settings.. option choose Count option at the place of Sum then click on the OK button.
Note: After choosing Count option Sum of Value will be change with Count of Value in the “Values” part of PivotTable Field List dialogue box and you will get your required result as per given below.
Must Read: MS Excel DMIN Database Function
I hope after reading this article Count values in MIS report PivotTable you will be able to easily solve your problem. Read carefully all the steps of count values in MIS report PivotTable in MS Excel. Do more practise on these given steps to solve your problem and share your knowledge with others to help them. Thanks to all.