Sun 19 Jul 2015

Article 18 - LTRIM and RTRIM through Excel Formulas

By |Sunday, July 19th, 2015|Categories: Articles|Tags: , , , , , , , , |0 Comments

So, we have TRIM function in Excel. Almost all programming languages provide LTRIM and RTRIM functions also but Excel doesn't provide LTRIM and RTRIM. The same is provided in VBA but most of the Excel users are not using VBA. They are simple folks who want to accomplish their day to day job through Excel functions only.

Before I delve into LTRIM and RTRIM, let's recap TRIM.

TRIM, basically, removes all spaces from your string and if there are more than one space between your words, it will convert them into only one space. So, it removes all leading spaces, all trailing spaces and all spaces between words except one. If there is only one space between words, it will leave that space untouched.

(more…)

Sat 04 Jul 2015

Article 17 - 3D Formulas - Hidden Wonder of Excel

By |Saturday, July 04th, 2015|Categories: Articles|Tags: , , , , , |0 Comments

3D formulas are one of the hidden wonders of Excel and not many of us know about the secret of this. The purpose of this article is to unravel the mystery of 3D formulas in Excel.

Suppose you have 4 worksheets called Quarter1, Quarter2, Quarter3, Quarter4 as below. And you want to find the total of Quarter1 to Quarter4 revenue. This is quite simple a task, just put the formula =Quarter1!B6+Quarter!B6+Quarter3!B6+Quarter4!B6 and you are through. But what happens when number of sheets are large say 12, 50 or 100. This becomes quite cumbersome to enter. And that is where the magic of 3D formulas comes into picture.

(more…)

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|Tags: , , , , , |0 Comments

--- The Excel file related to this article can be downloaded from Dependent Drop Down List 02 ---

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 06 Jun 2015

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

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

---- Excel file related to this article can be downloaded from Dependent Drop Down List ----

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.

Dependent Drop Down Layout

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

First Dropdown

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.

Second Dropdown with Blanks

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.

Second Dropdown without Blanks

Sat 09 May 2015

Article 14 - Dependent Data List 00 - Create Dependent Data List

By |Saturday, May 09th, 2015|Categories: Articles|Tags: , , , , , , |0 Comments

--- The Excel files related to this article can be downloaded from Dependent Drop Down List_Single Words & Dependent Drop Down List_Multiple Words  ---

Creation of dependent data list is a topic which I wanted to write and I have got some time to talk about this. In this article, I will be covering the dependency for one level but same logic can be extended to more levels.

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.

Dependent Drop Down Layout_00

(Please note that Row1 has single words like North America is written as NorthAmerica i.e. no blanks. Also, it doesn't have any special character like @, % etc. The reason is that Naming Range doesn't support blanks / special characters in between the words. For matters of simplicity, let's keep it as one word without any special characters. We will remove this limitation later on)

2. Create the first drop down

Let's assume that cell A2 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

First Dropdown

3. Prepare for second drop down - Create Name Ranges

We can see column F has values from F1 till F7.

3.1 Select the range F1:F7
3.2 Formulas tab > Create from Selection > The Create from Selection box will have Top Row automatically selected and all others deselected > Click OK
3.3 Now, you have created a named range Africa.
3.4 Do, it for all other ranges also i.e. select G1:G7 for named range Asia and repeat steps 3.1 to 3.3. For Australia H1:H5 and repeat steps 3.1 to 3.3. Do it for all the continents.

Dependent Drop Down Layout 00

4. Create your second drop down

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

=INDIRECT($A$2)

Dependent Drop Down  00

Now, you have the drop down created.

Dependent Drop Down Layout 00_1

Removing Limitation of Spaces (blanks) in First Row (First Dropdown)

We talked about the limitation that First Row (First Dropdown) can not contains spaces between the words as Name Manager doesn't support space in the Name Box. Let's now remove this.

Let's assume you have a list like below where first row has blanks - Notice Australia and NZ, North America & South America.

Dependent Drop Down Layout 00_2

Your remember in Step 3, you created Named Ranges. Excel automatically replaces blanks with underscore and creates named ranges. Hence, you will have Australia_and_NZ, North_America & South_America if there are blanks.

The only change you need to make is for step 4. The formula in this case would be

=INDIRECT(SUBSTITUTE($A$2," ","_"))

Now, you have the drop down created even with blanks in first row.

Dependent Drop Down  00 with blanks

Removing Limitation of Illegal Characters in First Row (First Dropdown)

Many characters are not allowed in the name and when you follow step 1, Excel automatically replaces them. Hence, if you put Australia & NZ, the name range created will be Australia___NZ (2 underscores for spaces and 1 for &). Excel treats & as illegal character as & is a concatenate operator.

Similarily, if I put C1, Excel will treat it as illegal character while creating name as C1 is also a cell address. Hence, Excel creates a named range like C1_  .

For this case, don't use "Create from Selection" method to create Named Ranges

Create the named ranges by selecting the range and choosing Define Name rather from Create From Selection. As you will have to use an INDIRECT formula as given in Removing Limitation of Spaces (blanks) in First Row (First Dropdown). You can use appropriate SUBSTITUTE, REPLACE or LOOKUPS to workout replacing the characters.

Sat 11 Apr 2015

Article 13 - Generate a Unique / Distinct List out of a List when no blanks in the list / range

By |Saturday, April 11th, 2015|Categories: Articles|Tags: , , , |0 Comments

Suppose you have a list as given below and now task before you is to generate a list of unique / distinct list from column A. We will also be looking into generating Unique values from Columns A, B and C together which can be generalized to any number of columns.

Note - This article is for the list which has no blanks in the range select / list. The case of having blanks in data range will be investigated into another article.

1

We will revert to tackle this problem in 6 ways

A. Manual Way
B. Hybrid Way
C. Formula Way
D. MS Query Way
E. Power Pivot Way
F. VBA Way

(more…)

Sat 14 Mar 2015

Article 12 - Overcoming Wildcard Limitation in Sumproduct

By |Saturday, March 14th, 2015|Categories: Articles|Tags: , , , |1 Comment

In my view, SUMPRODUCT is the king of all Excel Functions and till Excel 2003, it was rightly so. But starting Excel 2007, SUMIF(S) and COUNTIF(S) conspired to dethrone the king named SUMPRODUCT.

But once a king, always a king. There are many scenarios where SUMPRODUCT still comes handy and this is still one of the most widely used Excel Functions.

But SUMPRODUCT has a limitation. The limitation is that you can't use SUMPRODUCT with wildcards. SUMPRODUCT doesn't like wildcards whereas SUMIF(S) and COUNTIF(S) support wildcards.

(more…)

Sat 14 Feb 2015

Article 11 - How to Encrypt Excel Workbook

By |Saturday, February 14th, 2015|Categories: Articles|Tags: , , , , , , , , |0 Comments

Note - The article is for Excel 2010 / 2013 versions and doesn't cover any version prior to Excel 2010.

Excel 2010 / 2013 uses default 128 bit AES encryption which is considered highly secured. If you are interested in knowing all things about Excel security, I will recommend following MS article -

http://technet.microsoft.com/en-us/library/dn194021%28v=office.15%29.aspx

---- Update on 27-Jun-15 - Following is a very good article which contains some useful info on Excel security -

http://www.thespreadsheetguru.com/blog/2014/8/20/understanding-excels-password-security-methodology ----

Anyway, coming back to topic, there are 4 ways to encrypt Excel Workbook (Not counting VBA to do this which is not a safe way to encrypt).

1. Way 1 - Encrypt Document Button - This is recommended if you encrypt your workbook very often. (more…)

Sat 31 Jan 2015

Article 10 - Intersection Operator in Excel

By |Saturday, January 31st, 2015|Categories: Articles|Tags: , , , , |0 Comments

Today, I am going to talk about a nifty but not so well known feature of Excel. This is about Intersection Operator in Excel. Before, I go deep in Intersection Operator, I would like to talk about Reference Operators in Excel. There are 3 Reference Operators -

1. Range Operator (represented by Colon) - It specifies a range. Hence, A1:B10 where a colon (:) has been used, specifies all cells contained between A1 to B10. Similarly, D:D specifies entire column D. 3:3 specifies entire row 3.

2. Union Operator (represented by Comma) - It specifies the union of ranges. Union means inclusive of all cells in the ranges specified. Hence, if you specify A1:A5,D2:D4 it will include all cells lying between A1 to A5 and D2 to D4. Hence, if I specify =SUM(A1:A5,D2:D4)
The answer would be 221.
If I specify =SUM(A1:A5,B2,D2:D4,C4,C1:C2), it will pick up all cells from A1 to A5, B2, D2 to D4, C4 and C1 to C2.
The answer would be 326.

(more…)

Sat 10 Jan 2015

Article 9 - Overcome WildCard VLOOKUP / MATCH Problem when Target String is more than 255 Characters

By |Saturday, January 10th, 2015|Categories: Articles|Tags: , , , , |4 Comments

Once again, I have got idea to write this article after I responded to this post Vlookup to find URL using wildcard in Excel Microsoft Community.

Before I delve into problem statement, I want to start with some basic information. Maximum column width can be of 255 characters, this means that if I select a column, take right click and select Column Width, I can give a maximum value of 255. If I try to give more than 255, it will immediately give an error message.

Length of cell contents (text) - 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

(more…)