Sat 29 Nov 2014

Article 8 - Calculating Percentage Change between New and Old Value

By |Saturday, November 29th, 2014|Categories: Articles|Tags: , , , , , |2 Comments

Many times, I get inspiration to write about articles when I post responses to questions on Microsoft Community. The inspiration to write about this has come from following post - Percentages

You will also have many occasions particularly when you are asked to compute percentage change between two values. This is very much encountered in Finance industry where you have to report percentage growth for many parameters like revenue, cost etc. Economics has demand growth, supply growth and every other industry has their own growth metric. Telecom will have subscriber growth, ARPU growth and so on.

Note - The article is not about growth between percentage values i.e. it is not about growth between 20% and 30% but between absolute values.

(more…)

Sat 08 Nov 2014

Article 7 - Generate a Sequence of Numbers

By |Saturday, November 08th, 2014|Categories: Articles|Tags: , , , , , |1 Comment

Many times, we have need when we want to generate a sequence of numbers for various purpose. When I started Excel, I simply used to put 1 in A1 and =A1+1 in A2 and dragged down to required number of rows. I, sometimes, still do it. Just old habits die hard. Over a period of time, I did learn many other ways which I would like to share here. Here, I will be talking about filling in a column i.e. vertically. Utilizing same line of logic, horizontal sequential numbers can be generated. Also, if your starting number is not 1 but some other number, you will have to utilize same line of logic to do it.

Once again, I would like to tackle this topic in 4 ways.

1. Formula Way
2. Manual Way
3. Hybrid Way (Combining both Formula and Manual ways)
4. VBA

(more…)

Sat 11 Oct 2014

Article 6 - Convert Text Format into Number Format

By |Saturday, October 11th, 2014|Categories: Articles|Tags: , , , , , , |0 Comments

This article was long pending after I wrote Covert Number Format into Text Format.

This post essentially deals with the fact that if a cell has number in text format, how to convert that value into Number format so that it becomes usable for calculations and for other purposes. (Exclusion - If a number has been entered as accounting format 12- i.e. not with leading minus sign, this article doesn't cover this)

Once again, we will look into this problem statement from 4 angles -

1. Formula Way
2. Manual Way
3. Hybrid Way (Both Manual and formula ways combined together)
4. VBA Way

But first, after conversion, how will you know the cell value is converted into number or not. You can use any one method from below -

(more…)

Sat 13 Sep 2014

Article 5 - Difference Between Two Dates (Duration) in Terms of Months / Years

By |Saturday, September 13th, 2014|Categories: Articles|Tags: , , , , |0 Comments

DIFFERENCE IN MONTHS

There will be business scenarios when you will be asked to come up with duration between two dates in terms of months.

-- Note the Excel file for this article can be downloaded from DateDiff --

Let's Say A1 has From Date of 10/5/2013 and B1 has To Date.  You may use following formulas -

=DATEDIF(A1,B1,"m")

Now, this gives us the answer in Completed Months. It will not give fractional answer like 1.6 months.

(more…)

Sat 09 Aug 2014

Article 4 - Dynamic Charting in Excel i.e. Create a Chart which Automatically Updates when Data is Added or Removed

By |Saturday, August 09th, 2014|Categories: Articles|Tags: , , , |0 Comments

When you create a chart, you select a range. Now assume, one more row of data is added. But that will not be reflected in your chart as your chart had a fixed range.

-- The Excel for this article can be downloaded from Dynamic Charting --

A work around is to select more rows in your range beyond the existing range. i.e. if your current range is A1:B5, you can select A1:B10 where A6 to B10 are blanks. Hence, when data is added into A6 to B10, chart will automatically be updated. But there are two problems in this approach.

1. Chart shows placements for A6 to B10 also, hence your chart will have 10 data points in this case whereas actual graph will be for A1 to B5.
2. If your data goes beyond A10 that will not reflect in the chart unless you change the range again for the chart.

There are 3 approaches possible to overcome above limitations and create something called Dynamic Chart -

1. Table Approach
2. OFFSET Approach
3. Pivot Table Approach

(more…)

Sat 12 Jul 2014

Article 3 - Show Date for a Coming Day (e.g. Coming Saturday)

By |Saturday, July 12th, 2014|Categories: Articles|Tags: , , , , , , , , , , |1 Comment

There are many circumstances in life where we want to determine the coming day. We are always eager to wait for Saturday. Hence, we want to show the coming Saturday date. We want to go to a blockbuster movie on Wednesday, hence we want coming Wednesday date. All these are fairly easy by Windows Date / Time Calendar or Calendars on mobile  or through other calendars in day to day life. Excel not needed. But in business scenarios, Excel is the tool which you would most likely use.

Now, consider Excel and business scenarios where you would really need the help of this article. Your shift starts on Monday and you want to display date for coming Monday.

Also, suppose you opened your Excel sheet on Monday itself. Now, you are in a dilemma whether to show today's date itself or next Monday's date. For example, you opened your sheet on 26-May-14 and you need to decide whether to show 26-May-14 as coming Monday's date or 2-Jun-14 as coming Monday's date.

I had responded to a question on Microsoft Excel Community Forum and many persons had posted the response. I particularly found the answer of Ashish Mathur very interesting. That discussion can be read here. There are many ways to achieve the objectives  but this particular formula, I found to be very elegant.

(more…)

Sat 07 Jun 2014

Article 2 - Single Underline / Double Underline in Accounting

By |Saturday, June 07th, 2014|Categories: Articles|Tags: , , , , |18 Comments

Accountants were the first and foremost users of spreadsheet programs and they are still the dominant group using Excel. If you are working for accountants, you need to know this concept as these are critical formats for the accountants. They, sometimes, use Single Underlining and Double Underlining in their statement of accounts. Single Underlining is used to denote Subtotal and Double Underlining is used to denote Grand Total.

The concept, in simple terms (not in statement of account format) is illustrated below.

Q1 and Q2 totals are Subtotals, hence they are single underlined.

Grand Total is final total, hence that is double underlined.

(more…)

Mon 05 May 2014

Article 1 - Convert Number Format into Text Format

By |Monday, May 05th, 2014|Categories: Articles|Tags: , , , , , , , |2 Comments

The very first post which I want to write about is conversion of numbers into text. For example, if have a numeric value of 2.23, it should be converted to text value of 2.23. We may need this in many data manipulation scenarios. I will not go into why we need this but directly jump into writing about this topic.

In my view, there are always 4 ways to achieve anything in Excel

1. Formula
2. Manual
3. VBA
4. Hybrid (Combining minimum 2 of above methods)

Let's touch on all 4 ways. For the purpose of this article, let's assume a numeric value of 2.23 is contained in cell A1.

But first, after conversion, how will you know that you number is converted into text. You can use any one method from below -

1. Numbers are aligned right and Texts are aligned left by default unless you change the alignment. Hence, after conversion, you will notice that right aligned number is changed to left aligned number (which is text now, in fact). {Note - This is not a foolproof method}

2. Use the formula ISTEXT. If converted Number is contained in B1, use formula = ISTEXT(B1). If result is TRUE, it is Text. This is the best and foolproof method as it doesn't depend upon alignment.

(more…)