Sat 27 Sep 2014

Tips & Tricks 55 - Quickly Fill in Cells with Dates from M to N

By |Saturday, September 27th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

1. Put the start date in the cell where you want your first date to be. Let's put 6/1/14 as an example.

2. Select that Cell and in Home Tab, go to Fill and Select Series

1

3. Select Series in Columns if you want Column to be populated (Most likely case). Put End Date in in stop value as we want to generate dates till that end date and OK to generate list of numbers. Step value consists of increments by which dates have to be incremented. (If you want to space out dates with one week interval, put 7 in step value)

1

Sat 27 Sep 2014

Tips & Tricks 54 - Quickly Apply Thousand Separator in Cell with Two Decimal Places

By |Saturday, September 27th, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

You have a number 123456789 and you quickly want to apply Thousand Separator Custom Format with Two Decimal Places. Hence, you are seeking an answer of 123,456,789.00.

Excel has already provided a short cut to do the same -

CTRL+SHIFT+!

Sat 20 Sep 2014

Tips & Tricks 53 - Print Comments in the Sheet

By |Saturday, September 20th, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

1. You need to show the comments which you want to print or you can show all comments in the sheet. To show the comments

 Show Comments in the Sheet

2. First step is mandatory for printing the comments. Only those comments will be printed which are shown in the sheet.

3. ALT+PSP

1

4. After setting up page, you can fire the print.

Sat 20 Sep 2014

Tips & Tricks 52 - Show Comments in the Sheet

By |Saturday, September 20th, 2014|Categories: Tips and Tricks|Tags: , , |2 Comments

1. If you want to show all comments in the sheet -

1.1 Go to Review tab and click on Show All Comments.

1

2. If you want to show selected few comments (not all) in the sheet -

2.1 Click a cell where the comment is.

2.2 Go to Review tab and click on Show / Hide Comments. If step 1 is not followed, Show / Hide Comments will be greyed out. Another option is to right click and you will have Show/Hide Comments in the menu.

Note - This has to be done for all cells individually where you want to show the comments. Selecting all cells and then clicking on Show / Hide Comments will be showing comments only in last selected cell.

1

Sat 13 Sep 2014

Tips & Tricks 51 - Not able to see few rows / columns what to do

By |Saturday, September 13th, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

There are two possibilities -

1. Those rows (columns) are hidden.
2. Rows (columns) width is very small.

To uncover them -

1. Press CTRL+A to select entire sheet.

2. Double click on white line joining two numbers for row (two column alphabets in case of columns) and all these rows (columns) will be uncovered.

1

Question - I want to uncover only selected rows (columns)

Answer - Just select rows (columns) in such a way that one row (column) before those rows (columns) and one after are selected. Double click and only selected rows (columns) will be uncovered.

Sat 13 Sep 2014

Tips & Tricks 50 - My Developer Tab is Missing, how to show

By |Saturday, September 13th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

1. File > Options > Customize Ribbon
2. Check Developer

1

 

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 06 Sep 2014

Tips & Tricks 49 - My Column Numbers are 1,2,3....rathar than A, B, C.....

By |Saturday, September 06th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

It means your R1C1 reference setting has become enabled. If you haven't done it manually, then it means that you have opened a sheet which was R1C1 enabled and any sheet opened after that will be opened in R1C1 mode only.

To go back to A,B,C column notations which is called A1 style referencing.

1. Office Button > Excel Options > Formulas

1

2. Uncheck the R1C1 reference style

1

Sat 06 Sep 2014

Tips & Tricks 48 - Multi Column VLOOKUP

By |Saturday, September 06th, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

You know VLOOKUP, one of the most loved function of Excel. The syntax is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Here look_value can be a single value not multiple values.

Now, you are having a situation where you want to do vlookup with more than 1 values. For the purpose of illustrating the concept, let's say we have 2 values to be looked up.

Below is your lookup table and you want to look up for Emp - H and Gender - F for Age.

1

=INDEX(C2:C12,MATCH(1,INDEX(--((A2:A12=F2)*(B2:B12=G2)*(ROW(A2:A12)-ROW(A2)+1)<>0),,),0))

Concatenation Approach

=INDEX(C2:C10,MATCH(F2&"@@@"&G2,INDEX(A2:A10&"@@@"&B2:B10,,),0))

@@@ can be replaced by any characters which should not be part of those columns.

By concatenation, you can have as many columns as possible.

CAUTION - Result of entire concatenation should not be having length more than 255. Hence, F2&"@@@"&G2 should not have more than 255 characters.

Another alternative is to use below Array formula -

=INDEX(C2:C12,MATCH(1,--NOT(ISLOGICAL(IF(A2:A12=F2,IF(B2:B12=G2,C2:C12)))),0))

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.