Show Formulas Used In a Sheet

When you enter formula in a cell, Excel calculates and return the result to be shown in that cell. You will not be able to see the formula any more unless you select that cell. Sometimes you might need to display all the formulas appearing in a sheet instead of results. You can quickly do this in Excel by using a short-cut Ctrl+` (grave accent). See that key left side of number 1 key, that's the one to be pressed along with control key.

In Excel 2007 or later, you can also do this by clicking on Formula tab > Formula Auditing group > Show Formulas. In Excel 2003 or earlier versions, Tools > Formula Auditing > Formula Auditing Mode

Doing this will not change the way calculations are made in Excel. Only appearance of Excel window will be changed. All columns size will become broader and instead of results in the cell, all formulas will be displayed. Any range references used in the formulas will be highlighted with different colour borders.

You can read through all the formulas and edit / delete any formula just the way you do in regular mode. You can as well print the sheet with formulas appearing all over.

Pressing the short-cut again or clicking in sequence above make the Excel appear normal. Enjoy!!

3-D Reference Style

We have seen relative references and absolute references in one of our previous posts. We also talked about R1C1 style of referencing. I said we will see more referencing style in a future post. This post is about that. What I am talking about is 3D referencing style. Sounds interesting? Well, it is. Let’s see about it.

What is 3D reference?

Simply put, a cell reference that is spread over multiple sheets is called 3D reference. This is particularly useful to use in formulas when you have to retrieve contents of cell(s) from across similar sheets. To understand better, let’s go by a simple example.

Assume a situation where you maintain your daywise sales total in a sheet. You name the sheet by the month name i.e. Jan, Feb, Mar etc; this sheet has all dates in column A and column B contains sales total for that day. In the next month, you roll over this sheet with same format. Now, for your trend analysis, you wanted to create a summary sheet that gives you day wise sales of all the months together. That means sales made on 1^st day across 12 months in one cell and so on. What will you do? Think of it. To show a visual representation of what I mean, I copied the pictures here. (I took only up to 8^th day for want of space; notice that cell references containing the sales are consistent across sheets.

Jan Tab

Feb Tab

March tab

Summary Tab

Here is what I used to do. I basically enter a formula in B2. Below are the steps I follow.

1. Type “=” (equal to) in cell B2 to start a formula
2. Click on January related sheet i.e. tab named Jan and select related cell in column B that contains sales total
3. Type a “+” (plus)
4. Click on February related sheet i.e. tab name Feb and select related cell in column B that contains sales total
5. Repeat steps 2 to 4 till the end of all the months
6. Press Enter and I will get required total in B2. Now the formula looks like “=Jan!B2+Feb!B2+Mar!B2+……+Dec!B2”
7. Drag this formula until the end of all days.

We have to perform around 30 steps in order to get data from 12 months. You can imagine the practical situations of 1 sheet for each day or multiple sheets to be considered. Pretty time consuming, huh?

You got the idea. 3D reference might help? Yes boss, that’s why I gave such a long backdrop. Let’s see what you can do to simplify the above task.

1.  Type “=SUM(Jan:Dec!B2)” in cell of summary sheet
2. Drag the formula till the end of dates.

That’s it. What you did is, you created a 3D reference of cell B1 spanning all the 12 sheets. Look at the below picture. Cell B2 contains old method and D2 contains 3D reference. Result is same in both the formulas. (I only calculated until 3^rd sheet which is Mar, you can extend the same way to Dec)

Understood the power of 3D references? Instead of typing the sheet names in the formula, you can also type equal to sign and click on first tab you want to start referencing then hold down Shift key and click on last tab you want to reference. Now click on cell(s) you want to include and press Enter.

What’s more, if you add a new sheet in between the given 3D reference sheets, formula will automatically consider that sheet too. You can also use 3D references in Named Ranges. Just follow the steps in above paragraph in the Refers To: field of named range.

How 3D Reference Adjust when Sheets are Added, Deleted or Moved

Continuing from our example above, we have the formula =SUM(Jan:Mar!B1). Let’s see how this reference moves when the sheets are rearranged.

Insert New Sheet: If you insert a new sheet anywhere between Jan and Mar sheets, 3D reference automatically includes new sheet in calculation. No change in formula if you insert new sheet before or after Jan-Mar sheets.

Delete Sheet: If you delete an existing sheet between Jan-Mar, 3D reference will no more consider that sheet values in calculation. Obviously, no change if you delete a sheet outside the referenced sheets.

Move Sheet: If you move a sheet which is in between Jan-Mar to a place outside Jan-Mar sheets, 3D reference will no longer consider those values in calculation. No change if you move sheets those are outside of referenced sheets.

Delete Start Reference or End Reference Sheet: In this case, 3D reference will be adjusted removing that deleted point. In our example, if you delete sheet named Jan, formula automatically becomes =SUM(Feb:Mar!B2)

Move Start Reference or End Reference Sheet: If you move end points to a new place, 3D reference adjusts to consider the new sheets that are between the new placing of end points. In our example, if you move Jan sheet to the right of Feb sheet, formula now only considers only Jan & Mar.

There is one limitation of 3D references. You can only use it with functions SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA.

You can download the workbook I used to present this example by clicking here.

I use 3D reference extensively while preparing summary reports of different tabs. But I will be careful to make sure that the same cell(s) across the sheets contain the data I need. Hope you find this feature useful in your office too. Happy Learning!!

R1C1 Referencing Style

Hello people!! In one of our earlier post, we saw that there is a reference style in Excel named R1C1 style. In that post, I mentioned that we will see about it in one of the future posts. Well, this post is the one. Read on to know more about it.

What is R1C1 Reference Style?

In simple words, R1C1 refers to row 1 column 1. Remember how we refer first cell in a sheet? A1, right? This is what is called A1 reference style. Each cell is named by the column name with row number next to it. Likewise, a cell at the intersection of column C and 2^nd row is named as C2.

In R1C1 style, we refer each row as R and column as C. Row number and column number is mentioned to refer a particular cell. So A1 is referred as R1C1 and C2 is referred as R2C3, so on and so forth.

How to enable R1C1 Style?

Excel by default uses A1 reference style. To turn R1C1 reference style on or off, you will have to do the following.

In Excel 2010 or later

Click File tab > Options. Click on Formulas tab. On the right side, under Working with formulas section, select or clear the R1C1 reference style check box.

In Excel 2007

Click Office button  > Excel Options. Click on Formulas tab. On the right side, under Working with formulas section, select or clear the R1C1 reference style check box.

In Excel 2003 or earlier

Click Options on the Tools menu, and then click the General tab. Under Settings, select or clear the R1C1 reference style check box.

How is it Different from A1 Style?

When you enable this style, column names as you see normally (like A, B, C …..) will be changed to numbers (1, 2, 3 ……..). Also the name box will start showing the reference of the active cell in R1C1 style rather than A1 style. (See top left in the pictures below)

A1 Style

R1C1 Style

Above difference is obvious and trivial. But the real difference will be noticed when you use this style in formulas. We will now compare A1 style and R1C1 style in an example to know what I mean. Let’s do a simple calculation in both the styles. I am adding values in column A and column B in Column C in below screenshots.

A1 Style

R1C1 Style

R1C1 style formulas are looking weird, isn’t it? Don’t fret. I too felt the same at the start. Let’s understand what it means and you will be clear. Before seeing what it is, let’s see the differences in both the screenshots.

Our familiar A1 style has different formulas in each cell of column C. But R1C1 style has the same formula. That’s the cool thing about R1C1 style, wherever you write this formula in column C to add column A and B values, the formula will be same. Let’s breaks down the formula in R1C1 style.

We already know R refers to row and C refers to column. Numbers in brackets are the relative distance between the cell where formula is entered to the cell we are referring. 

In RC[-2]+RC[-1], R without any number next to it means we are referring the same row. C[-2] means 2 columns to the left of the cell. Since our formula is in third column, two columns to the left is the first column (column A in A1 style), in the same row means the second row (where we have first formula), combining both the statements we get cell A2 in our understanding. Similarly RC[-1] means one column to the left in the same row, which is A3.

You might already make a wild guess here. Minus symbol (-) next to C means column to the left so plus symbol (+) means to the right? Yes. Absolutely! Similarly if minus and plus are next to R instead of C, you have to move rows instead of columns. But basic maths logic, if a number is positive, you need not enter plus in front of it. So there is no need to input + before R or C.

Let’s summarise the basics here.

To get familiar with this, let’s see couple of more examples. Assuming you are entering the formula in third row, second column (R3C2 or B3 in A1 style), below are couple of examples.

Ok, if you noticed, all that we discussed above is relative references. What about absolute references?

Your question is timely. Answer to this question also brings up another important advantage of this style. If you want absolute references in A1 style, you will have to enter a cell reference and press F4 or place $ symbols manually, correct? In R1C1, there is no need of $ symbols. R1C1 always refers to first row and first column intersection i.e. equal to $A$1.

In one line, no brackets mean absolute reference. On the same lines, mixed references are also easier to enter.

Why Should We Use R1C1 Style?

We all by default learnt using A1 style in Excel and quite comfortable with it. So, why in the world we should know R1C1 style?

Well, apart from quoting ‘knowledge is wealth’, we have few advantages using R1C1 reference over A1 style. Getting used to this style will be bit confusing at the start but you will love it as you learn it more.

Apart from ease of referring absolute references & uniform formulas when referring relative references, much of the use of R1C1 style is when you write in macro (VBA) code. When you record a macro, Excel records some commands by using the R1C1 reference style. For example, if you record a command such as clicking the AutoSum button to insert a formula that adds a range of cells, Excel records the formula by using R1C1 style, not A1 style, references.

History of R1C1 style

We are so used to A1 style so we might think why and how R1C1 style came into existence. Actually, Microsoft started with using R1C1 style by default in its very first spreadsheet program Multiplan. (Read more about this version here). Over the years to take over its main rival Lotus 1-2-3, Microsoft had to adapt Lotus’s referencing style which is A1 style. Eventually this style became default and R1C1 went behind curtains.

That’s all I have to say about R1C1 style. Though A1 style will be what we use mostly and life feels happy with it, try to meddle with R1C1 style once. Just a thought, you may like it.

In one of the future post, we will also see a different style of referencing. More than a style of referencing, that one works much in formulas where cell references spanning across sheets. Stay tuned and Happy Excelling!!

Named Ranges - An Easy Way to Refer Cells

Hello all!! Welcome back to another session on learning Excel. I hope your Excel learning is so far fruitful. We have been using named ranges extensively in our posts. So I thought it’s about time we see in and out of named ranges, though its bit late, better late than never.

What Is Named Range

A name is meaningful shorthand that makes it easier to understand the purpose of a cell reference. As the name suggests, you can name a range so that instead of using the range addresses, you can use the name in the formulas. Suppose you have your 12 months sales figures in cells A1:A12, you can name these cells together as MySales. Apart from ease of reading, you can use these names in the formulas quite seamlessly. =SUM(MySales) is more legible than =SUM(A1:A12), isn’t it?

Having said that, you can not only name a cell or block of cells using this feature but also a standalone value can be named, so do a custom formula. These names make navigation and selection easier too. Since a named range doesn't change when a formula is copied to other cells, it provides an alternative to using absolute cell references in functions and formulas.

Apart from these defined names, Excel also allows naming tables. This feature deserves separate post for itself so we will cover it in one of our future posts.

How to Name a Range

Typing in Name Box

You can name a range in several values. Quickest way is typing the name in Name Box. This is the box that displays the range address; it is visible on the left hand side of formula bar. Select the cell or block of cells you want to name, click in Name Box and type the name you want. Press Enter once you are done with typing name. Clicking anywhere else apart from pressing Enter will not save the name.

From Define Name Command

In Excel 2007 or later versions, names can be created by clicking on Formulas ribbon > Define Name. This action brings up a dialogue box named New Name which contains the fields to define a name, scope, comment and a field to mention what this name refers to. This box appears as shown in below picture. Refers to: value is auto picked which is the address of currently active cell(s).

In Excel 2003 or earlier versions, names can be created by clicking on Insert menu > Name > Define. This action opens up Define Name dialogue box which contains fields to define a name, what this name refers to. This box appears as shown in below picture.

From Create Name Command

If you want to create a whole set of names in one go rather than painstakingly enter each one, you can make use of Excel’s Formulas ribbon > Create from Selection command (in Excel 2007 or later) or Insert menu > Name > Create (in Excel 2003 or earlier).

Before clicking this option, you need to arrange your data. Once you click above options, a dialogue box opens up where in you can select if you want pick names from Top row, Left column, Bottom row or Right column of your selected data. This dialogue box pictures are as below.

Excel 2007 or later

Excel 2003

Note: If cell labels contain spaces, the spaces will be replaced by underscore characters and if they contain text that starts with a number they will be preceded by an underscore.

Naming Conventions

1. Name can contain up to 255 characters
2. Range names cannot start with anything other than a letter, underscore (_)or a backslash (\).
3. Name should not include spaces
4. Although they can include numbers, they mustn’t start with a number
5. A range name cannot be a cell reference, for example: you cannot name a range as A1. This might sound obvious, but because of the dramatic increase in the number of columns in Excel 2007/10 it’s quite easy to accidentally try and enter a cell reference – for example in Excel 2007 SAL1 (shortcut for salary) or PUR1 (shortcut for purchase) are both cell references, though they would be perfectly valid range names in Excel 2003.
6. Excel reserves some names for its regular operations and quite obviously you cannot use the reserved names (for example: You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.)
7. Excel names are not distinguished by letter case (means names are not case sensitive). You cannot give SALES and sales in one workbook. Both SALES and sales are same in Excel’s view in this context.

Scope of the Name

One of the options in New Name dialogue box is Scope. This is a drop-down with options viz. workbook and one option for each sheet. If you have defined a name, such as MyList, and its scope is Sheet1, that name is recognized only in Sheet1, but cannot be recognized in other sheets without mentioning entire path that leads to the name. For example, if in Sheet2, you want to use name MyList whose scope is Sheet1, you have to mention Sheet1!MyList.

Likewise, if you have defined a name, such as MyList, and its scope is limited to the workbook, that name is recognized for all worksheets in that workbook, but not for any other workbook.

A name must always be unique within its scope. Excel prevents you from defining a name that is not unique within its scope. However, you can use the same name in different scopes. For example, you can define a name, such as GrossProfit, that is scoped to Sheet1, Sheet2, and Sheet3 in the same. You need to mention the entire path in that case to make Excel understand which GrossProfit we are trying to use in the formula.

Refers To

Although names are most commonly used to refer to Excel cells or blocks of cells, a name can also refer directly to a value or an Excel formula. To achieve this, you will need to use the Define Name dialogue and enter the formula in the “Refers To” box. For example: define a Name “InterestRate” with Refers To as 10%. You can use this name in formulas like =A1*InterestRate. This method eliminates the need of maintaining a cell specifically to keep interest rate.

If you entering a big formula in Refers to, it will be a good idea to make sure the formula works correctly in a cell before copying it to the Refers to: box.

How to Use Names in Formulas

• You can use the defined names just by typing those names in the formulas.
• If you are using Excel 2007 or earlier, when you start first letter of the name, Excel lists down all the available names in drop-down and you can select the one you want.
• You can also insert a defined names from Formulas ribbon > Defined Names > Use In Formula
• You can press F3 while on a sheet or while you are entering a formula. A list of available names is shown in a pop up box i.e. Paste Name from where you can insert the names into your function.

Apply Name

Excel facilitates you to apply names to existing formulas. Think of a situation; you have a workbook with complex formulas using actual cell references. Later, you got to know about named ranges and created names for all the required ranges. You are now very eager to use them in your existing formulas to simplify them. Do you have to edit all the formulas manually to remove cells references and add names? Fortunately answer is no. You need not do all the formula editing manually, just make use of Excel’s Apply Names command (command is Apply if you are using Excel 2003).

Below are the screenshot of the location of this command.

Excel 2007

Apply Names will be enabled (clickable) only if there is a name created already. Clicking this command opens up a dialogue box as below. (I also clicked on Options button to show all the available options)

Available names are listed in the white space below Apply names: field. I named a range A1:A10 as "abc". Checking Ignore Relative/Absolute field treats A1:A10 (relative reference) and $A$1:$A$10 (absolute reference) same way. Any formula containing these 2 types of reference will be changed to names. For example: =SUM(A1:A10) and =SUM($A$1:$A$10) will be changed to =SUM(abc). Keeping this option unchecked will replace only similar reference what you have given in Refers To: under that name.

Note: It is easy applying names to existing reference but other way around is not possible with existing Excel functionality. So be careful before applying names as you cannot reverse later.

The Name Manager

Alright, you defined a name but now want to change the ‘name’ or ‘refers to’ field value. You can do that by accessing Name Manager.

To review or edit an existing name in Excel 2003 or before you need to follow the Insert > Name > Define. Select the name to be edited from the list. You can then delete it, or change what it refers to in the “Refers To:” box. In Excel 2007/10 Formulas > Defined Names > Name Manager presents a list of names in use together with information about their Scope. Again a name can be selected and then the “Refers To:” changed or the Edit button used or name can be deleted.

Name Manager also provides an option to filter available names. You can see various options available to filter in the screenshot below.

Navigate to the Named Ranges

You can go to a range quickly by clicking on the down arrow in the Name Box at the upper left corner of your Excel screen (which displays a list of all range names in the worksheet) and clicking on the name of the range to which you want to go. The entire range will be highlighted and the upper left cell of the range will be active.

For practical examples of named ranges, please refer to our previous posts by following the links below.

• Data Validation from other sheets/workbooks
• Data Validation using IF function
• Data Validation using INDIRECT function
• Data Validation - Growing List / Dynamic List
• Data Validation - Cascading Lists (Using CHOOSE function)
• Data Validation - Cascading Lists (Using INDEX & OFFSET function)

We have been using these named ranges extensively so far and will continue using them in future. I hope this post makes it clear on various concepts on this subject. Let me know if anything is unclear regarding this. Happy Learning!!

Functions: INDIRECT

INDIRECT function returns the reference to a cell based on its string representation. It returns the reference specified by a text string. References are immediately evaluated to display their contents. Function syntax is as below.

INDIRECT(ref_text, [A1])

ref_text is a textual representation of a cell reference.

[A1] is optional. It is either a TRUE or FALSE value. TRUE (or if this argument is omitted) indicates that ref_text will be interpreted as an A1-style reference. FALSE indicates that ref_text will be interpreted as an R1C1-style reference. (We will see more on this referencing style in one of the future posts)

Example 1

Let’s see the actual usage of this function. INDIRECT function allows you to input the address of one cell in another and get data from the first cell by referencing the second.  Let’s assume this: cell A1 has a value C3. Cell C3 has a value 10. Now, entering =INDIRECT(A1) in any cell fetches 10. So, cell A1 value which is C3 is evaluated to get its value.

Example 2

Taking it further, assume this situation. I have a file where I compile 12 months data, each month in a different sheet. Each sheet is named by the month; January, February etc. I have a summary sheet that displays key calculations for a particular month. I have a drop-down list in the summary sheet, when I select a month from the drop-down list, few key numbers from that month needs to be populated. For example, one of key number to be populated includes a formula to give sum of respective month's sheet cells F1:F10 i.e. like =SUM(January!F1:F10) for January month.  Is there any way that I can do this without manually writing so many formulas?

As usual, Excel says yes. Excel's INDIRECT function was designed specifically for this sort of thing. This function accepts a text string as an argument, and then evaluates the text string to arrive at a cell or range reference. In your case, assume that cell B1 on your summary worksheet holds the month name. The following formula utilizes the INDIRECT function to create the range reference used by the SUM function:

=SUM(INDIRECT(B1&"!F1:F10"))

Whatever month is selected in B1, this function returns that particular month related data by summing cells F1 to F10 of that month named sheet.

Example 3

You need to sum a series of cells in column A but starting row and ending row numbers are dynamic (changes frequently). If you write a SUM formula, you will have to update it each time your start and end row number changes. You can solve this problem using INDIRECT function. Suppose you entered your starting row in cell B1 and ending row in cell C1.  Then, you can use the formula;

=SUM(INDIRECT("A"&B1&":A"&C1))

If B1 contains 5 and C1 contains 10, this evaluates to the string "A5:A10". INDIRECT function converts this string to an actual range reference, which is passed to the SUM function.

Example 4

In our post relating to relative & absolute references, we saw this example. (Refer to section: Point to remember)

When you create a formula that refers to a cell, the reference to the cell will be updated if: (1) the cell is moved by using the Cut command to delete the cell or (2) the cell is moved because rows or columns are inserted or deleted. If you always want the formula to refer to the same cell regardless of whether the row above the cell is deleted or the cell is moved, use the INDIRECT worksheet function. For example, if you always want to refer to cell A10, use the following syntax:

=INDIRECT("A10")

Example 5

We used this function to get cascading lists in one of our earlier post.

Hope we are clear on using INDIRECT function. Do let me know otherwise. Happy Learning!!

Functions: ROW & COLUMN

Hi, let’s start new month with a post on Excel Lookup & Reference functions. In this post, we will be discussing about Excel built-in functions ROW, COLUMN. I do not use these functions as much as I use other functions in Excel lookup & reference category. Nevertheless, knowing these functions doesn't harm. You never know, sometimes these are just the functions you wish you knew. Okay, let's see about ROW function.

ROW

ROW function returns the row number of a cell reference. Let’s see the function syntax.

ROW( [reference] )

reference is optional. It is a reference to a cell or range of cells. If the reference parameter is omitted, then the ROW function assumes that the reference is the cell address in which the ROW function has been entered in.

ROW function entered in cell A1 without any reference given returns value 1 as cell A1 is in row 1.

Let’s see few examples:

Formula in cell A2 omits reference. Since cell A2 is in second row, formula returns 2 as result.  Formula in cell A3 is referring to cell A5 which is 5^th row hence the result is 5.

ROW function is most useful as an argument in other functions rather than by itself. You can see a more practical example of this function in our previous post where we were trying to get cascading lists with only unused items.

COLUMN

On very similar lines with ROW function, COLUMN function returns the column number of a reference. Function syntax is as below.

COLUMN( [reference] )

reference is optional. It is a reference to a cell or range of cells. If the reference parameter is omitted, then the COLUMN function assumes that the reference is the cell address in which the function has been entered in.

COLUMN function entered in cell B1 without any reference given returns value 2 as cell B1 is in second column.

Few examples:

Formula in cell A2 omits reference. Since cell A2 is in first, formula returns 1 as result.  Formula in cell A3 is referring to cell D5 which is 4^th column from start hence the result is 4.

COLUMN function is most useful as an argument in other functions rather than by itself.

Points to remember:

1. ROW & COLUMN functions returns the row & column number respectively starting from 1^st row and 1^st column of any sheet. That means cell B5 will always have row number 5 and column number 2. These functions won’t work if you are looking for row and column numbers relative to a range. I mean to say, you selected a range say B3:D10 and you want the row number of cell B5 relative to the range selected (i.e. taking B3 as first row, so B5 is 3^rd row in the range).
2. Instead of actual references, you can also provide named ranges as arguments. For example: If you name range A1:A5 as MyList, you can enter ROW(MyList) instead of ROW(A1:A5)
3. If you provide a range that refers to more than one cell, Excel returns ROW / COLUMN number of the first cell in that range.

In the next post, we will see about 2 related functions in the same category. Happy Excelling!!

Functions: ROWS & COLUMNS

We saw about ROW & COLUMN functions in the previous post. In this post, we will see about 2 more functions in Excel lookup & reference category viz. ROWS & COLUMNS functions. This would be very small post.

ROWS

This function returns the number of rows in a reference provided as an argument. Function syntax is as below.

ROWS(array)

array: Unlike ROW function, this argument is mandatory here. This argument can refer to a cell, range of cells, array or named range.

Few examples:

As the function returns the number of rows in the given reference, formula in cell A2 results 1 (cell A1 contains only 1 row) and cell A6 formula result is 9 (there are 9 rows in range D3 to D11).

COLUMNS

On the same lines with ROWS function, COLUMNS returns the number of columns in a reference provided as an argument. Function syntax is as below.

COLUMNS(array)

array: mandatory argument. This argument can refer to a cell, range of cells, array or named range.

Few examples:

As the function returns the number of columns in the given reference, formula in cell A2 results 1 (cell A1 refers to only 1 column) and cell A3 formula result is 2 (there are 2 columns in range B3 to C3).

Functions: LOOKUP

Let's start the discussion about 'Lookup & Reference' functions with LOOKUP function. Simply put LOOKUP function looks for a value either from one row range or one column range or from an array (array is set of rows and columns) and gives a corresponding value from same position in a second row or column. Continue to read below to see this function at length.

Excel LOOKUP function has two syntaxes.

LOOKUP(lookup_value,lookup_vector,[result_vector])

LOOKUP(lookup_value,array) 

First syntax is called 'vector' form and the latter one is 'array' form. 

Vector form

A vector is a one row or one column range. This form of LOOKUP searches for a value in the given row or column and returns corresponding value in another one row or one column range. Function syntax is below.

LOOKUP(lookup_value,lookup_vector,[result_vector])

lookup_value:  It is the value you want this function to search for. This can be a text, number, logical value, a cell address or a name.

lookup_vector: This is the place where this function should search for lookup_value. This range must be spread over one column OR one row only.

result_vector: This is the place where values you wish to retrieve are in. This range must be spread over one column OR one row only. Also should be of the same shape & size as lookup_vector. Its optional. If this is omitted, first column of data is considered.

Note 1: If LOOKUP cannot find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

Note 2: If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.

Let's see this with an example: I have a table with set of numbers & names along with a colour assigned to each value as in below picture.

I tried LOOKUP with various formulas and see the results below.

Array form

Array form of this function is used when your data is in a tabular format and lookup_value is in the left most part of the table. This form searches lookup_value in the first column of given array and returns  corresponding value from the right most column in the same row. Function syntax is below.

LOOKUP(lookup_value,array) 

lookup_value: Same as vector form. What you are looking for.

array: Table form. If rows are more than columns, lookup_value is searched in first column. If more columns in the array then lookup_value will be searched in first row.

Important points to note:

1. Values in lookup_vector & array must be sorted in ascending order. If that's not done, LOOKUP may give incorrect result. If you cannot sort the data, consider using functions like VLOOKUP, HLOOKUP or MATCH.
2. For lookup_value, upper case and lower case text is equivalent. If you are searching for 'a' and lookup_vector contains 'A', result will be corresponding value of 'A'.
3. Result_Vector and lookup_vector must be identical in terms of shape & size.

Advantages of LOOKUP:

1. You can use LOOKUP function as an alternative to multiple IF functions.
2. You can return a value which is left side of lookup_value using vector form. Functions like VLOOKUP does not support this unless you tweak the standard function a bit.
3. This function supports looking values in rows and columns as well. In other functions either row wise or column wise search is possible (VLOOKUP for column search, HLOOKUP for row search)
4. Arguments need not be cell references. You can enter your own set of data in the formula itself. For example: Below function takes lookup_value from cell C2 and lookup in data in first set of curved brackets (i.e. A to I) and returns corresponding value from second set of curved brackets (1 to 9)

=LOOKUP(C2,{"A","B","C","D","E","F","G","H","I"}, {"1","2","3","4","5","6","7","8","9"})

Disadvantages of LOOKUP:

1. Since your data must be sorted first, this function is not flexible.
2. If lookup_value is not available in the list, LOOKUP considers next least number. This is not friendly if you are looking for exact match.

That's it about lookup. Post a comment if you have any doubts relating to this function or have anything to say. Happy learning!!

Functions: RANK

Hi there!! Departing from the regular series on functions (Lookup & Reference) that we are seeing in last few posts, today I am going to introduce RANK function in this post. RANK can be found under 'Statistical' category in Excel built in functions. I thought of writing about this function because I recently used it and found myself researching about tweaking this so that I can adapt it to more practical solutions.

RANK function returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. That means, if you sort the list, the rank of the number would be its position from top. For example, you have a list of marks secured by a set of 7 students and you want to give them ranks from 1 to 7 based on their performance in which 1 being the top and 7 being the lowest marks. You can use RANK in order to get the desired result. Let's further analyse this function syntax and usage.

RANK Function Syntax

RANK(number,ref,[order])

number: Is the number you are looking to find a RANK for.

ref: Is the list of numbers in which you want to find the rank of a number

order: This is an optional argument. If you omit or enter 0 or False, RANK returns the rank from high to low. In our example above, person who secured the top marks will get a rank of 1. Any other value for this argument returns the rank from low to high. Same example above returns rank of 1 to lowest mark.

Example:

Following our example above, I have a list of marks for 7 students and want to find the ranks for each of them. I want to give them ranks from 1 to 7 based on their performance in which 1 being the top and 7 being the lowest marks. See below on how RANK can help us.

That's it. We get the ranks for each item. Other way around, if I want to rank them in reverse order, a formula like below would help.

Points to remember

• If you are applying RANK to multiple numbers by dragging it to subsequent cells, make sure you use absolute reference in ref.
• Of course, number you are looking to give a rank should be available in the list of numbers. Otherwise RANK returns an error (#N/A).
• Make sure that list for second argument contains numbers only. Non-numbers or numbers formatted as text will result into #N/A error. RANK will not give rank to this item and continues ranking other numbers.
• Zero is also a number for RANK function hence it will be given a rank. But because of any reason, what if you want to exclude zero while giving ranks? Actually this is the situation I faced couple of days back. I was analysing returns given by certain mutual funds in FY13. There is a big list of numbers varying from -40% to +120%. A return of 0% means that the mutual fund did not participate in that category or data is not available. Hence it should not be ranked. I end up tweaking RANK function to suit this requirement. Refer to my solution in 'Avoid zeros' section below.
• RANK gives same rank to duplicate numbers but effects the sequence of ranks. For example, if you have a list of numbers 10, 20, 20, 30, 40, 50 and applied RANK function for all the numbers, ranks of second AND third number will be 2 and 2 but the fourth number will have a rank 4. Meaning there is no rank 3 given to any number in the list.

Avoid Zeros

In some practical cases, we need not give ranks to zeros. If your data contains zeros, RANK assigns a rank to them. See the standard RANK performance in below picture. See the cells highlighted in blue. Zeros got the rank 5.

So what if I do not want to rank zeros in the above example because zero means return figure is not available hence cannot be ranked. Let's see how we can use IF and RANK combination to do this.

Same data. But see the highlighted cells. There is no ranking to zeros and other numbers got ranks ignoring zeros. (Notice the difference of 2 in all ranks compared to earlier ranks including zeros). Mission accomplished.. Let's analyse what our formula is doing here.

We used a basic IF formula to check if number we are checking is zero. If true then return a null value (""), if false then further use rank function. For cell B2, Excel evaluated the function like below.

1. Check if A2 = 0
2. If above result is True then show "" as a result. No further analysis.
3. If result is False then result will be result of 

RANK(A2,$A$2:$A$11)-IF(A2<0,COUNTIF($A$2:$A$11,0),0)

So our first requirement, showing null (blank in the cell) for zeros is achieved by above simple steps. Showing correct ranking for cells not containing zeros is the work of 3rd step above. First part of the above function calculates the rank including zeros using standard RANK syntax.

RANK(A2,$A$2:$A$11)

Result for first number using this formula would fetch 10. Careful now. Ignoring zeros in ranking WILL NOT effect positive numbers rankings but the negative number rankings will be reduced by number of zeros in the list. Notice above 2 pictures and find out that positive number rankings have not changed. So the second part of the above function evaluates like shown below.

1. Check if A2 is less than 0 (means negative)
2. If true then count number of zeros in the list using COUNTIF
3. If false then result is 0

Result of second part is 2 in our example (number of zeros are 2, right?). This number will be deducted from result of first evaluation (which is 10) and revised rank 8 is given.

Drag it over other cells and you get ranking ignoring zeros. Though our example shows ignoring zeros, with bit of modification, you can set what ever criteria you wish for. 

Post a comment if you have anything to share regarding this. Happy learning!!

Functions: VLOOKUP - Left lookup

Hi again!! We saw about VLOOKUP function in last post. We also saw that it works to find a value which is in the first column of table array or towards right from it. So VLOOKUP fails to lookup a value to the left. However there are some cases where you have to apply lookup but have required value to the left side of lookup_value position in table_array.  You do not have permission to change the column sequence or simply do not want to do that. In this case your hope is LOOKUP or a tweaked version of VLOOKUP.

In this post I will show you a way to tweak the standard function and make it work this way. Let's see a quick recap of how VLOOKUP works.

VLOOKUP function looks for a value (lookup_value) in a set of rows and columns (table_array) and returns a value from the same row where lookup_value has been found but from a different column we ask for (col_index_num). You can also request an exact match or an approximate match (range_lookup). Looking closely at table_array description, we can notice that this argument supports the following; 

1. a range reference (like $A$3:$A$17) or 
2. a named range (example: if you have a table named as MyTable, you can simply type MyTable as the second argument instead of range) or
3. a formula that results in the range reference

What we used in our VLOOKUP example in earlier post is the range reference. To lookup a value to the left we will use 3rd property above that tricks VLOOKUP to believe the given array is on the right side where as in reality it will be towards the left. Let's see one example.

I have a list of asset codes along with asset names in a data base. As you see in the below picture, asset code is the first column and next column (to the right) is asset name.

Given the asset code, I can easily use VLOOKUP to retrieve asset name. But if I have asset name given and have to find out asset code which is on the left, VLOOKUP gives up. See the below formula.

Since VLOOKUP always look for lookup_value in the first column and in our example it will not be able to find a match, it results in to an error (#N/A). So what to do now? In this particular example you can use the flexibility of LOOKUP.

But if you want to use VLOOKUP, let’s see how to tweak this function to work in these cases. Enter the below formula and see how it works, below I will explain what we did.

You can cross check the results manually from data base to see results are in fact correct. Once you made sure we are good with the formula, we will now analyse the above function.

We just followed VLOOKUP syntax as required. As we know that VLOOKUP finds a value in the table_array provided and return corresponding value from a chosen column , we have provided table_array that is created by CHOOSE function by combining column B first and then column A. This tricks VLOOKUP to believe column B is indeed first column and column A is second. We will slow down now and analyse what CHOOSE function is doing, bit by bit.

Let’s have a closure look on how we use CHOOSE and what it means. We used the below as an argument in VLOOKUP above.

CHOOSE({1,2},$B$1:$B$11,$A$1:$A$11)

We are giving 2 ranges to CHOOSE function which is B1 to B11 and A1 to A11 (last part of the function) and asking it to combine them. Notice that we are giving column B first, then column A and we asked it to combine first one and second one. VLOOKUP considers the combined range for second argument instead of our initial database.That’s the trick. You may get a doubt now. Can we give column A first, then column B and ask CHOOSE to combine 2^nd one first and 1^st one next? Answer is YES. Below formula also works perfect.

CHOOSE({2,1},$A$1:$A$11, $B$1:$B$11)

Essentially, both of above syntaxes creates an array with Column B and Column A. Try these steps to see it yourself. We use CHOOSE as an array function below.

1. Select a 11 rows * 2 columns range.
2.  Type / paste the function “= CHOOSE({1,2},$B$1:$B$11,$A$1:$A$11)” without quotes
3.  Press Ctrl+Shift+Enter

Now you can see Excel filled up the selected range like below.

Notice that Asset name is in first column and Asset code later. Our original database is other way around. Once the CHOOSE function does this with in the Excel memory, VLOOKUP does the remaining job of finding and returning the value which is actually to the left of the table.

You can add any number of columns like this to CHOOSE function and extend this function to suite your need. For example, if you have 3 columns in your data (A, B and C) and you want to create an array with  an order B, C & A, just use CHOOSE like below.

CHOOSE({2,3,1} ,$A$1:$A$11, $B$1:$B$11, $C$1:$C$11)

That’s it guys! This way you can make VLOOKUP work with the help of CHOOSE function to return left side value. Post a comment if anything seems confusing above. See you next time!