Sat 26 Jul 2014

Tips & Tricks 35 - Highlight Cells which are different from first Column Cells in a Range (And for Row Cells also)

By |Saturday, July 26th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

Let's say you are having data as given below and you want to highlight all the cells in columns after first column where cell values are different.

Confused???

B1 is not different from A1 where C1 is different from A1, hence C1 will be highlighted.
In row 2 both B2 and C2 will highlighted and so on.

1

Approach

1. Select the required range. In above case, select A1:C8.

2. ALT+EGSW and Enter (ALT+EGS opens Go to Special and W selects "Row Difference")

And you will have your results.

1

ALT+EGS to disply

Note - For ROW CELLS, select Column Difference i.e. ALT+EGSM and you get following results (I have changed data set to illustrate the concept. Here comparison is done with first row for following rows -

1

Sat 26 Jul 2014

Tips & Tricks 34 - Get Sheet Name through Formula

By |Saturday, July 26th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

Before getting this, make sure that you file has been saved at least once as this formula is dependent upon the file path name which can be pulled out by CELL function only if file has been saved at least once.

Use following formula -

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

Make sure that A1 is used in the formula. If it is not used, it will extract sheet name for the last active sheet which may not be one which we want.

If you want the sheet name for last active sheet only, then formula would become

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

Sat 19 Jul 2014

Tips & Tricks 33 - Generate Week WORK Day Names Mon to Fri or Monday to Friday

By |Saturday, July 19th, 2014|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

1. Put "Mon" without quotes in a Cell. Let's assume this is in cell A1 here. If you want to generate full names of the day, put "Monday" without quotes.
Note - The start day can be any WORK day. "Mon" is chosen for illustration purpose only. The start day will be whatever is put in cell A1.

2. Drag the bottom left corner till row 5 (or to any number of rows) by pressing right click.

3. Leave Right Click.

4. From the menu which pops up, select Fill Weekdays. It will populate all cells with workdays of the week except Sat and Sun i.e. with only Workdays.

1

Sat 12 Jul 2014

Tips & Tricks 32 - Generate Week Day Names Sun to Sat or Sunday to Saturday

By |Saturday, July 12th, 2014|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

1. Put "Sun" without quotes in a Cell. Let's assume this is in cell A1 here. If you want to generate full names of the day, put "Sunday" without quotes.
Note - The start day can be any day. "Sun" is chosen for illustration purpose only. The start day will be whatever is put in cell A1.

2. Drag the bottom left corner till row 7 (or to any number of rows) by pressing left click. It will automatically generate the names of the week days.

Or

3. Drag the bottom left corner till row 7 (or to any number of rows) by pressing right click.

4. Leave Right Click.

5. From the menu which pops up, select Fill Days .

1

Sat 12 Jul 2014

Tips & Tricks 31 - Generate Month Names Jan to Dec in Text or January to December

By |Saturday, July 12th, 2014|Categories: Tips and Tricks|Tags: , , , , , , , , , , , , , |0 Comments

1. Put "Jan" without quotes in a Cell. Let's assume this is in cell A1 here. If you want to generate full names of the month, put "January" without quotes.
Note - Start month can be any other month also. "Jan" has been chosen for illustration purpose only. The output will be starting with the month whatever has been put in cell A1.

2. Drag the bottom left corner till row 12 by pressing left click. It will automatically generate the names of the month.

OR

3. Drag the bottom left corner till row 12 by pressing right click.

4. Leave Right Click.

5. From the menu which pops up, select Fill Months.

1

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 05 Jul 2014

Tips & Tricks 30 - Generate an Array of a to z

By |Saturday, July 05th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

I have already talked about generating a to z in a sequence in below post.

Now, I want to generate an array {"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v",
"w","x","y","z"} to use in a formula.

To generate Small or Capital letters, do it while generating the sequence itself.

Let's assume that a to z are generated in A1 to A26. Put a in A1 and drag down till A26. If you want to generate a to z in capital letter, put A in A1 and drag down.

In a cell, put following formula and press F9

= TRANSPOSE(A1:A26)

In the formula bar, you will have array generated which you can copy and paste and use in the formula.

1

Sat 05 Jul 2014

Tips & Tricks 29 - Generate Alphabets A to Z (or a to z)

By |Saturday, July 05th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

For Capital A to Z

Put A in first cell and drag down.

If you want to do it through formula -

In Cell A1, put this formula and drag it till 26th row

=CHAR(65+ROW()-1) or CHAR(64+ROW())

CHAR(65) is A. You can verify this by CODE("A") formula which will generate 65.

If you are putting this formula is some other row apart from 1, adjust the mathematical expression within CHAR in such a manner that first entry is 65.

For example, if you are starting in row 10, the formula would become

= CHAR(65+ROW()-10) or CHAR(64+ROW()-9)

For Small a to z

Put a in first cell and drag down.

If you want to do it through formula -

Replace 65 with 97 or 64 with 96 in above formulas.

Sat 05 Jul 2014

Tips & Tricks 28 - First Day of the Month for a Given Date

By |Saturday, July 05th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

Suppose you have been given a date say 10/22/14 (MM/DD/YY) and you want to calculate the first day of the Current Month. Hence, you want to achieve a result of 10/1/2014 (MM/DD/YY).

The formulas to be used -

=DATE(YEAR(A1),MONTH(A1),1)

=A1-DAY(A1)+1

=EOMONTH(A1,-1)+1

Sat 05 Jul 2014

Tips & Tricks 27 - Find the Merged Cells

By |Saturday, July 05th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

Sometimes, you would be required to find merged cells in Excel. One of the common cases is when you try to sort a column and all of a sudden of you see the pop-up regarding merged cell. You can do following to find merged cells -

1. Select the column.
2. CTRL+F and click Options.
3. Click on Format
4. Alignment tab > Remove ticks from Wrap Text and Shrink to Fit. Tick Merge Cells.
5. Now click Find Next to find if Column has merged cell or not.

1