Skip to main content

Posts

Showing posts from September, 2018

New Article

How to SUM by matching partial text in Excel

Cumulative Closing Balance like a Bank Statement in PivotTable

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...

How to create a Battery Chart in Excel

Battery Chart is a great way to view the percentage information. It can easily represent many reports with " Still Remaining " information. Such as, The remaining achievement value, the remaining achievement percentage, the remaining days to go in this month etc. This chart can attract audience in seconds. So, using of this chart could be a plus point of your professional career.   Image 1: Battery Chart in Excel Understanding A Battery Chart: To create a Battery Chart, first of all I divide a battery into 4 parts. Bottom Portion , Achievement Value , Remaining Value and Top Portion . To design a Battery Chart I also need to divide a single information into 4 parts.  Data Require to Create a Battery Chart:   If I have a single information like, Achievement Percentage is 63% , then I can find another information like, 100% - 63% = 37% . So I can call it, Achievement Value = 63% and Remaining Value = 37% . Manually I have selected The Botto...