ExcelMatic

Home / Tips and Tricks

Showing posts with label Tips and Tricks. Show all posts

What is an alternative to multiple-nested IF-Then-Else functions?

IF-Then-Else - one of the functions that Excel has taken from the programming languages. And it is one of those things that one learns as ba...

How to locate formulas in a worksheet

Dhakkanz 12:34 AM 8 Comments

In one of my earlier works, I was supposed to crack an already-built spreadsheet. The workbook was basically a complex model that contained ...

How to define Range Names?

Dhakkanz 12:04 AM Add Comment

Excel generally provides more than one ways to perform an action. For defining range names also, it has provided 2 ways: U sing the Insert ...

Working out with Data Validation list

Dhakkanz 10:07 PM Add Comment

Recently one of the readers sent me an email with a question on data validation issue that most of us would have faced. I'll call this ...

List of Essential Excel Shortcuts

Dhakkanz 12:05 AM 1 Comment

As promised, here is a list of some Excel shortcut keys that are essential for an Excel user to become Power-Excel-User. Note that you can a...

Do not know about a particular function?

Dhakkanz 10:38 PM Add Comment

What do you do when you do not know about a specific function? One of the most common tecniques which one applies is - search on google (if ...

Is Sum () better or '+'

Dhakkanz 9:47 PM Add Comment

You should use Sum() in place of using '+' symbol to add up the cells (or ranges), even if the range to be summed up is small. Sum(...

Subscribe to: Posts ( Atom )

Showing posts with label Tips and Tricks. Show all posts

Sunday, September 6, 2009

What is an alternative to multiple-nested IF-Then-Else functions?

IF-Then-Else - one of the functions that Excel has taken from the programming languages. And it is one of those things that one learns as basics of programming.

If-Then-Else (or simply IF function) is used to make a choice based on condition(s) provided by the user. For example:

In one of the cells, I want to show "True" IF cell A1 contains 1, otherwise it should show "False".

To achieve the above, one can write a simple formula in Excel, i.e. - =If (A1=1,"TRUE","FALSE")

Great! Now let us change the above task a bit, something as - the cell B1 should show the following text based on the number input:

If cell A1 = 1, then the cell should read "One"
If cell A1 = 2, then the cell should read "Two"
If cell A1 = 3, then the cell should read "Three"
If cell A1 = 4, then the cell should read "Four"
Otherwise should say - "Above 4"

In this case, one can write a multiple IF formula as this:

= IF (A1=1,"One", IF(A1=2,"Two", IF(A1=3,"Three", IF(A1=4,"Four","Above 4") ) ) )

Cool! It works. Now let us make things more complex. Lets keep 7 conditions instead of 4. You can still write the IF function on above lines and it will work fine.

But what if there are more than 7 conditions? And what about the processing time?

Excel does provide you with a useful tool such as IF function, but it comes with a limitation. The limitation is that a cell in Excel can contain only 7 nested IF functions, not more than that.

The other issue with using multiple IF functions is that it makes processing a bit slow, as there are 7 IF functions to deal with. You will not be able to notice the speed if you have only one cell with multiple IF functions, but in case you had a sheet with many such cells, the calculations will take time.

The alternative to IF functions is to use Choose function. This functions allows you to provide several options within the function, which will be processed on the basis of number provided to it as input. I have provided more text on this function in a separate post.

Thursday, September 3, 2009

How to locate formulas in a worksheet

In one of my earlier works, I was supposed to crack an already-built spreadsheet. The workbook was basically a complex model that contained a lot of data and has almost 40 worksheets and each worksheet had lots of numbers.

Now, to understand the functioning of each of the sheets, I had to find out which cells on each sheet contained functions / formulas! But with each sheet (MS Excel 2003) having 256 columns and 65536 rows (which means 16777216 cells!!) it was really impossible for me to dig down each cell.

But MS people are very intelligent. They did not provide you only a tool to create spreadsheets, but also a tool which one can use to decipher a the tool.

To view which cells in a worksheet contains functions, one can use this tool. This tool basically toggles the worksheets view from normal to a view function view*. The function view can be activated by pressing the following key combinations: CTRL and ~

The key above the TAB key is used in the above key combination. The same combination is also used to bring back the view to normal view.

Notes: *The functions view is not a standard terminology. I have used this terminology to be explain the context.

Monday, August 31, 2009

How to define Range Names?

Excel generally provides more than one ways to perform an action. For defining range names also, it has provided 2 ways:

Using the Insert –> Name –> Define command in Menu Bar
Using the Name box (adjacent to formula bar) directly

Here are the steps to define Range names by both the above methods:

Using the Menu Bar:

Select the cell / range of cells which you would like to name
Go to Insert –> Name –> Define command
Now you would see a dialogue box (shown below)
On the top text area (where the cursor would be blinking by default) type the name that you would like to give to the range selected
Press Add and then OK

...and you are done!

Using the Name Box:

Select the cell / range of cells which you would like to name
Go to Name Box (as shown below)
Type the name for the range in the Name box and then press Enter

...and you are done!

Now you have learned the usage of and the steps to define range names. But, there are some important points that one should keep in mind while defining the range names.

Thursday, August 6, 2009

Working out with Data Validation list

Recently one of the readers sent me an email with a question on data validation issue that most of us would have faced. I'll call this person as Tom as this person has asked for anonymity of his identity.

As you remember, a cell can be validated for numerous things, text entries, numerical ranges, pre-defined lists. etc. [Refresh your data validation concepts here]. Tom has asked me a question related to data validation for a pre-defined list. He asks - when defining the validation rule for a list, we have to put a range where the list has been defined. Now when you are defining the rule, it asks for the list range and you have to either manually enter the range or you can select the range with the help pf mouse.

At this stage, there is one thing you cannot do, i.e. input the range which falls out of that sheet. What it means is you cannot select any range which is not on the same sheet where the validation is being put.

Tom is correct partially. He is correct when he said that users cannot select the range which is on the other sheet, but it is not true that you cannot enter the range that is not on the same sheet. One can very well enter the range manually and have the list located on any sheet other than where it is being used for validation.

One simply has to mention the full range of the list (the address where the list is located) together with the sheet name and it will work normally. Just remember to put a '=' sign so that the data validation works fine. For an example - I will refer to a list which is on 'sheet2' and I am applying this validation on a cell in 'sheet1'. My formula for this would be:

= 'Sheet1'!A4:A10

There is another way you can do this (and in my opinion this is the best way) - using Range Names.

A Range Name is a name given to a cell or range of cells. One can use this name anywhere in the workbook for any kind of processing without having to worry about the sheet names and the cell ranges. In our case, while using range name, the validation rule should simply refer to the range name given to the list. The list address should instead show this formula (assuming the range name given is 'ListName'):

=ListName

...and that is it!

Range Names are one of the coolest tool that Excel has provided. I have not yet written about it, but will soon do it. Range Names can actually make your life very simple when writing complex formulas and making heavy and dynamic Excel models.

Wednesday, June 3, 2009

List of Essential Excel Shortcuts

As promised, here is a list of some Excel shortcut keys that are essential for an Excel user to become Power-Excel-User. Note that you can also download FREE PDF e-Book on Essential Excel Shortcut Keys from the link given at the bottom of this list.

Here is the list of shortcut keys:

Application Shortcut Keys:

ALT+TAB: Toggle between running programs - While working with multiple applications, it is sometime required to toggle between applications to perform certain tasks. Use these keys to toggle between the windows without using the mouse
CTRL + TAB: Toggle between open spreadsheets - In case you are working with multiple worksheets, use these keys to toggle between the spreadsheets while they are open
CTRL + Page UP OR CTRL + Page Down: Toggle between worksheets of the same workbook - Sometimes you need to toggle between multiple worksheets of the same workbook, use CTRL+PGUP to toggle ahead in the arranged list of worksheets and CTRL+PGDN to Toggle backward in the list of worksheets
CTRL + N: Create and open a new spreadsheet
CTRL + O: Open an existing spreadsheet
CTRL + W: Close an open spreadsheet - These keys would not save your workbook and would attempt to close the workbook without exiting Excel. Excel should prompt you to save your work before closing the workbook, unless you have change the default settings

Application Usage Shortcuts:

CTRL + A: Selects whole worksheet - These keys work in 2 ways. If the current cell is located somewhere within a range of cells that are a part of table, in this case it will select the whole range of table. In case there is no table or the current cell is outside such range as mentioned above, then it will select the whole worksheet
CTRL + F: Activates the search wizard in Excel
CTRL + H: Activates find and replace - These keys are an extension of CTRL+F. With this combination, you can directly activate the FIND and REPLACE function
CTRL + G: Activates the GOTO command wizard - GOTO command is a very useful and a handy tool, but is not used by many users. When you are working with Filters or hidden rows, then specially GOTO command is very helpful
CTRL + D: Copies the content of the cell above - With this key, you can copy the above cell exactly to the cell below. It will copy the contents of the cell and the formatting also
CTRL + L: Creates a list with the selected cell - List is an advanced tool that is available in Excel. These lists have specific use which we will be discussing later in our advance topics
CTRL + K: Creates hyperlink on the selected cell - This key combination is used to create hyperlink on the selected cell. The hyperlink can link to a web page, place in the same workbook / worksheet / another file, etc.
CTR: + Arrow Keys: Navigate within a continuous range - These keys will take you to the end of a continuous range of cell, empty or with data. What this means is that if the current cell has some data and next 10 cells on the right of it also have some data. In this case you can use CTRL+RIGHT KEY to go to the end of this range, i.e. range of cells with data. In the current example it will take you to the cell K1 (if the first cell was A1)
CTRL + SHIFT + Enter: Create an Array formula - This combination is used to create a cell entry an Array function. These keys are to be used while in edit mode. Edit mode means – when you are still entering something in a cell and have not yet pressed the Enter (Return) key
CTRL + Spacebar: Selects the current column - Assume that the current cell is B5, i.e. column B and Row 5 and you want to select the whole of column B. Press CTRL+Spacebar to select the column
SHIFT + Spacebar: Selects the current row - Assume that the current cell is B5, i.e. column B and Row 5 and you want to select the whole of row 5. Press SHIFT+Spacebar to select the row
CTRL + ~ : Reveals the actual Formulas in the whole worksheet - Excel does not show exactly what is there in the cells. The real content is hidden under the cells. For example, a cell may be containing a formula, but due to the default view you will see only the results of the function. Use this combination to reveal what exactly lies under the cell
ALT + Down: Opens up the list, if any, in the current cell - This is useful in cases when you want to select any option from a list in the current cell. For example, if the current cell has data validation and has a drop-down list, you can open up the list using ALT+Down keys

Download this list in PDF format - "Excel Matic - Essential Shortcut Keys"

Sunday, May 24, 2009

Do not know about a particular function?

What do you do when you do not know about a specific function?

One of the most common tecniques which one applies is - search on google (if one has internet connection). Right? Or, in case you do not have the internet, you would go to Excel's 'Help' section and find out how to use the function. (The second option is the best if someody ask me)

But what if you do not have time to go through the Excel's documentation to read the whole content and understand the stuff that Microsoft has written for you. And, all what you want to know is just what all arguments go into a function. (You can also refer to Excel Math Functions explained by Excel Matic)

Microsoft has taken care of this also. They have integrated in Excel something which I call as short-cut Help. Whenever you are writing a function, which you do not know about, you can acces this help.

How to get this help

Start writing the function, write the name of the function and only the opening brackets (to input the arguments, but do not input any arguments)

It should look like this -

= FunctionName(

After you have written this much, press CTRL+A. On pressing these key combinations the function wizard will pop up and will guide you through writing that particular function. You can also get this wizard by using the Menu bar (Menu Ribbon for Office 2007).

Whenever you use the menu bar to insert the function, the function wizard pops up and guides you through writing the function.

Friday, May 22, 2009

Is Sum () better or '+'

You should use Sum() in place of using '+' symbol to add up the cells (or ranges), even if the range to be summed up is small. Sum() proves to be more robust and secure in terms of usage

- Read here to know more about this.