Sat 31 May 2014

Tips & Tricks 6 - Assign a Shortcut Key to a Symbol

By |Saturday, May 31st, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

In many scenarios, you may be using a symbol very often. For example, new Indian Currency Symbol ₹. Every time, you need to insert symbol which is many steps process and introduces inefficiency. You can use following method to assign a short-cut key to a symbol. (Below example is actually not a short cut key but a word which you type)

Let's take example of ₹.

1. Insert this symbol in an empty cell and copy this symbol. You need to copy only the symbol by selecting and CTRL+C. Don't copy the entire cell.

1

2. Go to Excel Options > Proofing and Click Autocorrect Options (For Excel Options, you need to press Office Button and Excel Options is right bottom.

3. Put a word for the symbol in Replace box and paste (CTRL+V) and paste symbol in With box.  (It is suggested that you enter lower case letters)

1

4. Now whenever you enter the word IRC in a cell, the symbol will be inserted.

Sat 31 May 2014

Tips & Tricks 5 - Allow Entry of Dates if they are not Weekends

By |Saturday, May 31st, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

1. Select the Cell > Data Tab > Data Validation > Data Validation

2. Under Settings Tab, in Allow, select Custom

3. Put following formula

=WEEKDAY(A1,16)>2

1

4. Customize your Input and Error Message as per your requirements.

Sat 24 May 2014

Tips & Tricks 4 - Allow Entry of Date for a Particular Day only

By |Saturday, May 24th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

1. Select the Cell > Data Tab > Data Validation > Data Validation

2. Under Settings Tab, in Allow, select Custom

3. Let's assume that we want users to enter only those dates which are Wednesdays. Put following formula in

=WEEKDAY(A1)=4

1

4 is for Wednesdayday. 1 is for Sunday and 7 is for Saturday other values lying in between.

4. Customize your Input and Error Message as per your requirements.

Sat 24 May 2014

Tips & Tricks 3 - Add Year to or Subtract Year from a Given Date

By |Saturday, May 24th, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

In many business problems, you might encounter situations where you will need to add or subtract years from a given date.

Let's say A1 contains Date and B1 contains numbers of years.

If you want to add Years to a given date, formulas would be -

=EDATE(A1,12*B1)

=DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))

If you want to subtract Years from a given date, formulas would be -

=EDATE(A1,-12*B1)

=DATE(YEAR(A1)-B1,MONTH(A1),DAY(A1))

Sat 17 May 2014

Tips & Tricks 2 - Add Month to or Subtract Month from a Given Date

By |Saturday, May 17th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

Very often, you will have business problems where you have to add or subtract month from a given date. One scenario is calculation for EMI Date.

Say, you have a date of 10/22/14 (MM/DD/YY) in A1 and you want to add number of months which is contained in Cell B1.

The formula in this case would be

=EDATE(A1,B1)

[Secondary formula =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)) ]

Now, you want to subtract month which is contained in Cell B1.

=EDATE(A1,-B1)

[Secondary formula =DATE(YEAR(A1),MONTH(A1)-B1,DAY(A1)) ]

Sat 17 May 2014

Tips & Tricks 1 - A List is Unique or Not (Whether it has duplicates)

By |Saturday, May 17th, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

Assuming, your list is in A1 to A1000. Use following formula to know if list is unique.

=MAX(FREQUENCY(A1:A1000,A1:A1000))

=MAX(INDEX(COUNTIF(A1:A1000,A1:A1000),,))

If answer is 1, then it is Unique. If answer is more than 1, it is not unique.

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…)