Dynamically Highlight data points in Excel charts using Form Controls
Charts and graphs and other visual means make information easier to understand and analyze. Excel gives plenty of options in this department as different types of graphs are possible. But in some cases wrong chart types in the situation impair chart’s visual effectiveness. For example in case of line chart with many data points it may become […]
Sum, Average or Count only Positive numbers in Excel - SUMIF + AVERAGEIF + COUNTIF explained
The IFs and BUTs are probably one the most annoying things in real life but they are super cool in Excel when it comes to formulas. Today we are discussing three of IF condition formulas that help us do SUMs, COUNTs and AVERAGEs easily. Note: To better understand their application I will be taking “positive” […]
Preparing Fixed Asset (CapEx) forecast model in Excel - Depreciation & NBV Calculations
In any financial model (forecasts or variance analysis) the idea is to derive expectations where business will end up if particular set of assumptions (scenario) prevails. These are basically calculations that help the decision making process. Excel provides the necessary flexibility in designing such models. As calculations are based on assumptions, it is much easier […]
Budget Vs Actual - Analyzing Profit and Loss Statements in Excel using Pivot tables
This tutorial is effectively a continuation of last tutorial on profit and loss statements using pivot tables in which we learnt how to make a report in Excel using pivot tables feature to make income statement in few steps quickly. Today we will learn how to do budget vs actual variance analysis of profit and […]
Making Profit and Loss Statements in Excel using Pivot tables
Being an accountant myself trust me I hate numbers and all that journals and “T” accounts. But its necessary to get to much meaningful financial statements like Statement of Financial Position (Balance sheet) and Profit and loss Statement (Income statement). Excel’s ability to store data in rows and columns is exceptionally flexible and Pivot tables capitalize […]
Combine or Append data in one table from multiple sources using Power Query in Excel
Suppose you have similar or related data scattered in two different worksheets or workbooks (files) or files of different programs and you want to combine them in ONE SINGLE Table! Is it possible you asked? Yes it is possible. One way is to copy and paste data from all the places in one worksheet or […]
Restructuring (Normalizing) data for Pivot tables using Pivot tables - How To
Last time I discussed the use of Power Query to restructure data for pivot table purposes if it is not arranged in columnar format. Today we will be learning how to normalize the data for pivot table purposes using pivot tables! Yes you read it right. We will use pivot tables to get the data straight […]
Prepare (Normalize) the data for Pivot Tables using Power Query
Pivot tables is a love of life for many Excel heads for its ability to move elements of reports easily is pure awesomeness. Pivot table has greatly improved since improv. But it still has limitations. And one of such is source data needs to be in specific structure to get the most of pivot table muscle. Data structure […]
Calculate the difference between two times in Excel
Its tough to manage times and sometimes it is difficult to manage the time related calculations even in Excel. Suppose you have a situation where you are given Start and End time and you have to find the time difference between two clock times. This is a usual calculation on time sheets to calculate the number of hours a […]
How to show data of hidden rows/columns in Excel Charts
By default if you hide a certain row or column of a data on which a chart is based, Excel will automatically hide that data too. I must confess that I was quite annoyed when I wasn’t able to have a control over this behaviour as sometimes I do need to hide the data but […]