Sat 24 Jun 2017

## Excel Quiz 53

By |Saturday, June 24th, 2017|Categories: Quizzes||0 Comments

Sat 04 Mar 2017

## Solution - Challenge 59 - Clean the Problem Workbook Data

By |Saturday, March 04th, 2017|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution to the problem Challenge 59 - Clean the Problem Workbook Data

Formula to convert would be which you need to drag down would be

=--SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(8237),""),UNICHAR(8236),"")

Data has UNICHAR(8237) and UNICHAR(8236) prefixed and suffixed which need to be replaced by above formula.

Sat 04 Feb 2017

## Challenge 59 - Clean the Problem Workbook Data

By |Saturday, February 04th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Download the the workbook from the below link. The Challenge before you is to write a formula to clean the data. If you perform any mathematical operation on the data, it will return #VALUE error. You need to clean the data through a formula.

Challenge 59 - Problem Workbook

The solution to the above problem will be posted after a month i.e. on 4-Mar-17.

Sun 10 Jan 2016

## Tips & Tricks 126 - Press CTRL+A Three Times to Select Entire Worksheet not Two Times

By |Sunday, January 10th, 2016|Categories: Tips and Tricks||0 Comments

The safest bet to select entire worksheet is through pressing CTRL+A three times not two times or one time, if you are using shortcut (The safest bet is to press the triangle between 1 and A as marked in Red in the given picture. The tip is for CTRL+A shortcut.)

This peculiarity of CTRL+A shortcut comes into picture when your worksheet contains tables also. Suppose, you have a worksheet like below which has at least one table.

1. Select a cell outside any data range, press CTRL+A and it will select entire sheet.

2. Select a cell inside A1:D2, press CTRL+A once and it will select A1:D2 range only. Pressing CTRL+A again will select entire sheet.

3. Select a cell inside header row in the table , press CTRL+A once and it will select entire table. Pressing CTRL+A again will select entire sheet.

4. Select a cell inside the table data range i.e. A6:D7, press CTRL+A once and it will select table data range i.e. A6:D7 only. Press CTRL+A again, now it will select entire table i.e. A5:D7 only not entire worksheet.. Now, press CTRL+A again i.e. 3rd time and now, it will select entire  worksheet.

Sat 28 Nov 2015

## Tips & Tricks 120 - Filter by Color Drop Down Menu Greyed Out Problem

By |Saturday, November 28th, 2015|Categories: Tips and Tricks|Tags: , , , , , |3 Comments

Sometimes, you find that though your are having colored cells but Filter by Color is greyed out i.e. it can not be selected.

Following are the possible causes -

1. Your sheets may be grouped. If they are grouped, you will notice word "Group" in the title bar of your Excel workbook.

Another way to check whether the sheets are grouped or not is to right click on any tab name and option "Ungroup Sheets" will appear:

2. There may be blank lines before a color is encountered in your range. Filter doesn't like blank cells. Hence, be watchful.

3. The workbook may be shared. If this is a shared workbooks, the word "Shared" will appear in the title bar of your workbook.

To remove sharing > Review tab > Share Workbook > Uncheck the box in Red zone

4. Also if first colored cell appears after approximately 10000 rows, Excel may not detect the color. To overcome this behavior, put a color in the first cell and now it will enable the Filter by Color. Only thing is that this colored cell will be extra in your filtered list.

Sat 05 Sep 2015

## Excel Quiz 20 - Excel Interface Quiz - Part I

By |Saturday, September 05th, 2015|Categories: Quizzes||0 Comments

Mon 10 Aug 2015

## Article 22 - Detect corruption of MS Excel worksheet and Recover it

By |Monday, August 10th, 2015|Categories: Articles|Tags: , , , , |7 Comments

--- This post is contributed by Priyanka Chauhan of Stellar Info. This is not an endorsement of the product on behalf of eforexcel.com ---

Microsoft Excel is used worldwide majorly for business purposes. It is the simplest tool which helps in maintenance of records, timelines and tasks in a table based format. Most of us have used or currently use the row-column cell based approach of MS Excel in our day to day working. However, an Excel workbook can get as complicated as a mysterious maze if one gets lost. And that is primarily contributed to the high level of sophistication this simple looking software offers in terms of formula computations, macros and VBA code. This article will be focusing on a very important aspect of working with Excel sheets – their corruption and recovery from it.

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 29 Nov 2014

## Tips & Tricks 67 - Show the Complete Data in Chart even though Data is Filtered

By |Saturday, November 29th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

By default, charts in excel show filtered data only.

To show complete data i.e. to ignore filtering -

1. Right click on Chart and click on Select Data.

2. Click on Hidden and Empty Cells at the bottom

3. Select Show data in hidden rows and columns. Now, you will have the result which you want.

Sat 28 Jun 2014

## Tips & Tricks 26 - Fill in the data from non blank cells to blank cells down

By |Saturday, June 28th, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Suppose you have data like below and you want to fill in blank rows with data with non blank row up. Here, in range A3:A5, you want value A to appear, in range B7:B9, you want value B to appear and so on.

You need this kind of situation, mostly, when you are making pivot which demands that all rows should have the data.

You need to follow following steps -

1. ALT+EGSK and Enter. This will select all your blank cells.

2. Type "=" (without double quotes) and press up Arrow.

3. CTRL+Enter

Now you will have cells filled as you wanted. You will notice that those blank cells are carrying the formula = A2, =A3 etc.

4. To convert them to static values, select you column A, Copy and Paste Special Values.