Faysal Easy Excel

A Bank Employee in many case needs to calculate the Closing Balance after each transaction in PivotTable like a Bank Statement . But with the help of Calculated Field of a PivotTable , you can only calculate Field with Field . It's not possible to use a formula in Calculated Field where you can mention a single cell with Relative Reference Cell . Because a PivotTable acts like a Table format where you can work with Field or Column Name . A Helping Column (A regular column, that helps to get a partial result where formula applied) can be a good idea. But there is an option in PivotTable by which you can Calculate Cumulative . In this article I will show you, how you can do it. Calculating with Formula: Assume that, you have an Excel file with Debit and Credit transaction of an Account . Image 1: Sample Bank Statement In a Bank Statement , Month wise Cumulative Balance is important. In the Excel sheet, as above, it is very easy to use a formula and calc...

We know that, PivotTable helps you to find the unique values from a wide data range. But the problem is to find the frequency of each unique value in that range How to count the frequency of Unique values in PivotTable? Assume that, You have Student and Marks column.  Image 1: Students Table Now first step is to find the unique Student's name. To do this, drag the Student field in ROWS box in PivotTable.  Image 2: Students Table in ROWS Box This will show the unique students list. Now to find the frequency of each student, drag again the same Student field in COLUMNS box in PivotTable. This will count the students frequency: Image 3: Count of Students Finally the result is as below: Image 4: Frequency of Unique Values

Sometimes, PivotTable shows "(blank)" as value if there is nothing in the cell. The problem is if it shows then, calculation is not dependable. That is why need to remove it or replace with a 0 (zero). Image 1: (blank) in PivotTable Settings: To remove the (blank) value from PivotTable simply Click on DropDown arrow of the Row Labels and deselect the Blank. Image 2: Analyze | Options

Step 1: Select the PivotTable or Click anywhere inside the PivotTable report area. Step 2: Click on Analyze menu and then go to Filter group. Then Click on Insert Slicer. or You can Click on Insert menu and Click on Slicer in Filter Group. Image 1: Insert Slicer Step 3: This will allow you to choose the field you wish to add as a Slicer as below image. The field list will depends on your PivotTable source data table. In my PivotTable source data table there are only 3 Fields. That is why the Insert Slicers shows 3 field name to choose. Image 2: Choose field name for Slicer Step 4: Click to mark one or more fields and then Click on Ok to add the marked fields as Slicer. I have selected salesChannel. Image 3: Field selection Step 5: Now click on Slicer button to filter your PivotTable report: Image 4: Slicer

Step 1: Select the PivotTable or Click anywhere inside the PivotTable report area. Image 1: PivotTable Step 2: Click on Analyze menu and then go to Actions group. Then click on Entire PivotTable. Or you can simply Select all cells of your PivotTable by draging mouse. Image 2: Entire PivotTable option Image 3: Entire PivotTable Selected Image 4: PivotTable deleted Step 3: From Analyze menu, click on Clear list and then click on Clear All if you wish to re use the PivotTable Fields Settings from the beginning. Or if you really want to delete it and convert the cell to a normal cell, then just press Delete from keyboard. Delete a PivotTable in 3 Seconds! 00:00:01: Click anywhere in the PivotTable reporting area. 00:00:02: Press Ctrl+A to select all. 00:00:03: Press Delete key.

If you have historical data, then it is very much disturbing to select a specific item from your slicer menu from all visible items. To do a better look, you need to hide inactive items while there is no data. How to do it? It is very simple. First select the Slicer items box by clicking on the title. Then go to the Option menu and select Slicer Setting. Image 1: Options | Slicer Settings Choose hide items with no data and click on OK. Image 2: Slicer Settings Slicer with no data has hidden after new settings: Image 3: Slicer with no data has hidden