Sat 27 Jun 2015

## Tips & Tricks 97 – Force Text to Columns Wizard to Appear for CSV Files

By |Saturday, June 27th, 2015|Categories: Tips and Tricks||0 Comments

You have a csv file and
1. You double click it, the file will be opened directly in Excel.
2. You opened the file from File > Open but still the file opens directly in Excel.

The solution is to change the extension of file from csv to txt. Hence, if you are having a file name myfile.csv, change is to myfile.txt. Now, you should open the file from File > Open method and Text to Columns Wizard pops up.

Sat 27 Jun 2015

## Challenge 19 - Make Comparative Horizontal Bar Graph

By |Saturday, June 27th, 2015|Categories: Challenges||2 Comments

---- The worksheet for this challenge can be downloaded from Literacy Rates Graph ----

Suppose, you have been given data for two categories as given below. The challenge before you is to make a chart like below.

Note - Solution to this challenge will be published after 1 month i.e. on 26-Jul-15.

Sat 20 Jun 2015

## Tips & Tricks 96 – Abbreviate Given Names

By |Saturday, June 20th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

If you have names given like -

Smith Johnson
Liz lotte
Christy tiara Lewisk
John

And you need to produce abbreviations or acronyms for them like below in all capitals

Smith Johnson - SJ
Liz lotte - LT
Christy tiara Lewisk - CTL
john - J

Then you can use following formula for the same for upto 3 words in the name -

=UPPER(TRIM(LEFT(A1,1)&MID(A1,FIND(" ",A1&" ")+1,1)&MID(A1,FIND("*",SUBSTITUTE(A1&"  "," ","*",2))+1,1)))

Explanation for the formula

1. LEFT(A1,1) - Extracts the first letter from the first name

2. MID(A1,FIND(" ",A1&" ")+1,1) -

FIND(" ",A1&" ") - Find finds the first space in the given name to locate the start of the middle name. " " has been concatenated at the end of A1 so that if there is only first name, FIND will not give error as it will always find the blanks. +1 has been added to start the MID position from where the middle name starts.

3. MID(A1,FIND("*",SUBSTITUTE(A1&"  "," ","*",2))+1,1))

SUBSTITUTE(A1&"  "," ","*",2) will replace the second blank with a *, hence we can find the position of * to locate the start of last name. As in 2 above, a double space "  " has been added in A1 so that FIND always finds the second space. +1 has been added to start the MID position from where the last name starts.

4. TRIM will remove all blanks inserted because of 2 or 3.

5. UPPER will convert the string to all capitals.

Note - If you don't to use the concatenation of single space and double space as in 2 and 3, then IFERROR block can be used. In this case, the formula would become -

=UPPER(TRIM(LEFT(A1,1)&IFERROR(MID(A1,FIND(" ",A1)+1,1),"")&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",2))+1,1),"")))

Note - This technique can be used to extend up to many words. Only change will be in last block where you can replace 2 with 3, 4,5 and so on in IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",2))+1,1),"") for 4th, 5th, 6th words and concatenate them....Hence for upto 6 words, the formula would become

=UPPER(TRIM(LEFT(A1,1)&IFERROR(MID(A1,FIND(" ",A1)+1,1),"")&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",2))+1,1),"")
&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",3))+1,1),"")&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",4))+1,1),"")
&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",5))+1,1),"")))

Fri 19 Jun 2015

## Article 16 – Dependent Data List 02 – Creating Flexible / Dynamic Dependent Data List even First Drop Down is Flexible

By |Friday, June 19th, 2015|Categories: Articles||0 Comments

In Article 15, we saw that our Dependent Data list can grow / reduce vertically but we had fixed the horizontal range. Now, we will remove this restriction and our data list can grow / reduce vertically as well as horizontally.

1. Prepare the formula for first drop down.

In article 15, we saw that it is very simple i.e. \$F\$1:\$K\$1. Let's assume that maximum it can grow till Z1. In this case, we need to prepare a formula which will give us a range of \$F\$1:\$K\$1 within the range of \$F\$1:\$Z\$1 if South America is selected. If another entry is added or deleted, it should adjust appropriately.

The formula in this case would be -

=OFFSET(\$F\$1,0,0,1,COUNTA(\$F\$1:\$Z\$1))

(Though OFFSET is a volatile formula but simplicity of OFFSET is a reason to use)

2. Prepare the formula for second drop down

The formula was already there, only change is that \$K is replaced by \$Z. (As I said earlier 12 is a number till which the list can grow. You can put any number say 100, 900 etc.)

=OFFSET(INDEX(\$F\$1:\$Z\$12,,MATCH(\$A\$2,\$F\$1:\$Z\$1,0)),1,0,
COUNTA(INDEX(\$F\$2:\$Z\$12,,MATCH(\$A\$2,\$F\$1:\$Z\$1,0))))

3. Create the first drop down list and second drop down list utilizing above formulas.

Select A2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put

=OFFSET(\$F\$1,0,0,1,COUNTA(\$F\$1:\$Z\$1))

Select B2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put

=OFFSET(INDEX(\$F\$1:\$Z\$12,,MATCH(\$A\$2,\$F\$1:\$Z\$1,0)),1,0,
COUNTA(INDEX(\$F\$2:\$Z\$12,,MATCH(\$A\$2,\$F\$1:\$Z\$1,0))))

Now, your dynamic drop down list which can grow vertically as well as horizontally is ready.

Sat 13 Jun 2015

## Challenge 18 - Find the Longest Word in a List

By |Saturday, June 13th, 2015|Categories: Challenges|Tags: , , , |1 Comment

--- The worksheet related to this problem can be found at Longest Word ---

Suppose you have a list of words in A1 to A10. The challenge before you is to write a formula (preferably non-array) to find the longest word in the list.

In case of ties, pick up the first longest word.

The response can be posted in the comments section of this post.

Sat 06 Jun 2015

## Excel Quiz 14

By |Saturday, June 06th, 2015|Categories: Quizzes|Tags: , , , |0 Comments

The thirteenth quiz in the series to test your general knowledge in Excel. After you hit Finish Quiz, you can click on View Questions to compare your answers to correct answers.

## Excel Quiz 14

A general quiz on Excel.

Sat 06 Jun 2015

## Article 15 - Dependent Data List 01 - Creating Flexible / Dynamic Dependent Data List

By |Saturday, June 06th, 2015|Categories: Articles||0 Comments

In this article, I want to create a dynamic dependent data list. It means that entries can be added, removed at any point of time without impacting your Dependent Data List. Without making matter complicated, this article assumes that first level is fixed and second level can keep changing. In second article of this series, I am going to remove this dependency also, hence both first and second level of entries can be removed / added in second article. The examples which I am going to show is one level of dependency but same logic can be extended to any level of dependencies.

1. Create the right layout for your dependent data list

Suppose, you have a list of 6 continents and you want to display the countries on the basis of continents selected. The step 1 is to layout your data properly.

Below, is the layout which you need to use. First row will contain the name of continents (i.e. first level drop down values) and below them will be the list of countries (i.e. second level of drop down values)

No blanks are allowed in between the list entries. Blanks can be only at the end.

2. Create the first drop down

Let's assume that cell A1 needs to contain the first drop down.

Select A2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put \$F\$1:\$K\$1

3. Prepare the formula for second drop down

Now, it can be simplified to finding the range which contains first drop down value in  first row. For example, if I select South America, I should be able to get the range \$K\$2:\$K\$12 (I have taken \$K\$12 for illustration purpose only. You can have any range. The trick will be to show only the values from \$K\$2:\$K\$6 in case of South America, \$H\$2:\$H\$5 in case of Australia and so on)

The range can be located by INDEX - MATCH combination as shown below. Notice, there are two commas after \$K\$12 which indicates that all rows should be selected for the column in the range \$F\$2:\$K\$12

=INDEX(\$F\$2:\$K\$12,,MATCH(\$A\$2,\$F\$1:\$K\$1,0))

This formula gives a range of \$K\$2:\$K\$12 for South America.

4. Create the second drop down

Let's assume that we want to create the second drop down in B1.

Select B1 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put

=INDEX(\$F\$2:\$K\$12,,MATCH(\$A\$2,\$F\$1:\$K\$1,0))

Now, select a continent name in A1 and you will have list of countries shown in dependent drop down.

5. Remove the blanks from second dropdown

We notice that there are blanks in second drop down at the end of the list. We need to remove these.

The logic, we will adopt is this - We have got the range in step 3. Now we need to count the non blank cells in that range and use OFFSET to get the required range.

=OFFSET(INDEX(\$F\$1:\$K\$12,,MATCH(\$A\$2,\$F\$1:\$K\$1,0)),1,0,
COUNTA(INDEX(\$F\$2:\$K\$12,,MATCH(\$A\$2,\$F\$1:\$K\$1,0))))

Note - INDEX(\$F\$1:\$K\$12,,MATCH(\$A\$2,\$F\$1:\$K\$1,0)) is first part of OFFSET and part within COUNTA making matter very simple. Hence, the formula is OFFSET("index formula",1,0,COUNTA("index formula")). In first INDEX, in place of \$F\$1:\$K\$12 we can put \$F\$1:\$K\$1 also. But as I said, I want to keep uniformity and make matter simple by having a very simple formula OFFSET("index formula",1,0,counta("index formula")).

INDEX(\$F\$1:\$K\$12,,MATCH(\$A\$2,\$F\$1:\$K\$1,0)) - Gives \$K\$1 in case of South America, \$G\$1 in case of Asia and so on. But, our range needs to start from second row i.e. \$K\$2 in case of South America, \$G\$2 in case of Asia and so on. Notice 1 in bold within OFFSET, it shifts the reference by 1 row. Hence, it makes \$K\$1 to \$K\$2 and so on.

COUNTA(INDEX(\$F\$2:\$K\$12,,MATCH(\$A\$2,\$F\$1:\$K\$1,0))) - Gives number of non blank cells. In case of Asia, it is 6. Hence, our reference becomes \$G\$2:\$G\$6.

6. Put the above formula in Dependent Data List.

Select B2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put

=OFFSET(INDEX(\$F\$1:\$K\$12,,MATCH(\$A\$2,\$F\$1:\$K\$1,0)),1,
0,COUNTA(INDEX(\$F\$2:\$K\$12,,MATCH(\$A\$2,\$F\$1:\$K\$1,0))))

Now, you have perfect looking dependent data list.

Sat 06 Jun 2015

## Tips & Tricks 95 – Send Excel Workbook / Worksheet as Attachment

By |Saturday, June 06th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

You can very easily send an Excel / Workbook as an attachment directly from Excel.

To send Excel Workbook

To use this functionality, your workbook need not be saved. When you use a step from below, it will attach that point of time copy of Excel workbook in the mail.

File > Share > E Mail > Send as Attachment

OR
ALT+FHEA

OR
If you need to e mail very often, you should add the E Mail icon to QAT (Quick Access Toolbar). You just need to press this button whenever you need to send the workbook as an attachment.

File > Options > Quick Access Toolbar > Select All Commands in Choose Command From > Select E Mail in the box below and add it to QAT and OK

To send Excel Worksheet

1. Right click on that Excel worksheet
2. Select Move or Copy
3. Select [new book]
4. Now a new workbook will be created. Now follow the step given in To send Excel Workbook to attach in e mail.

Sat 06 Jun 2015

## Tips & Tricks 94 - Select Big Range Very Easily

By |Saturday, June 06th, 2015|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

You have a need to select A1 to A10. This is a very easy job to do. Suppose, you need to select A1 to A10000. Now, it becomes more cumbersome. Suppose you need to select E23 to AB4325. It is all the more cumbersome job for you.

Approach 1 - Put the range in Name Box and Press Enter

Approach 2

1. Press F5 (or CTRL+G)
2. Specify the range in Reference:
3. OK and you range is selected.

Sat 06 Jun 2015

## Challenge 17 – Sum if Y Appears More than One Time Consecutively Maximum

By |Saturday, June 06th, 2015|Categories: Challenges||2 Comments

The Excel file related to this problem can be downloaded from Challenge - Most Consecutive Appearance

Suppose you have a table like below. The challenge before you is to find the sum of prize money if Y appears consecutively more than once for a person maximum time. You need to input name in F2 and sum of prize money should appear in F3. If name doesn't meet the criterion of consecutive Y maximum times, then Prize money should be blanks or any message can be put here.

Taking the example of Smith, most consecutive times Y appears in rows 7 to 10. For Doe, rows 15 to 16 and for Liz 19 to 21. Note for Smith, Y appears consecutively 3 times, first time in rows 2 and 3, second time in rows 7 to 10 and third time in rows 12 to 13. But since, we are talking about maximum consecutive appearance, hence we need to consider row 7 to 10.

If their are ties, pick up the first maximum consecutive appearance.

Note - Without the use of a helper column, it may be very difficult to solve. Hence, please feel free to use a helper column. Maximum allowed helper column is 0.