Feb 10 2015

Excel ? IF Statements, LOOKUPS & INDEX/MATCH. When to choose which one? – Part One.

This article will focus on the appropriate use of IF statements, and also show you when it’s better to move into more robust formulas like LOOKUP, VLOOKUP, HLOOKUP & INDEX/MATCH.

 

IF statements are one of the core formula models you can use in Excel, and they can be very powerful with regards to their logic. Very simply they follow this methodology: IF(something is True, then do something, otherwise do something else).

 =IF(A1=”Yes”,1,2), which simply says if A1 = Yes, then return a 1, otherwise return a 2.

You can also combine IF statements to evaluate multiple criteria by nesting them, however, many people try to get IF statements to do too much. Previous versions of Excel allowed up to 7 levels of nested IF statements, which means that you could evaluate up to 7 different criteria. Excel 2007+ allows 64 levels of nested IF statements, but just because you can do something doesn’t mean you should!

One of the most common uses of IF statements is to return data that matches specific criteria, or falls within a range of values. Here is a relatively common example of a nested IF statement to return letter grades based on test scores:

=IF(A1>=90,”A”,IF(A1>=80,”B”,IF(A1>=70,”C”,IF(A1>=60,”D”,IF(A1<60,”F”)))))

Another is calculating commission statements:

=IF(A1>25000,A1*2%,IF(A1>15000,A1*1.5%,IF(A1>5000,A1*1%,0)))

Note that in both formulas, the criteria need to be ordered sequentially in order for the formula to calculate correctly. So in the first example A1>=90 gets evaluated first, and if that condition is true, then the formula performs the calculation associated with that condition. If the condition isn’t true, then it moves onto the second, and so on. But if you get your conditions out of order, then one condition can invalidate the next and render your formula(s) useless. One of the inherent weaknesses with IF statements is that they need to be precise and ordered.

While both of these formulas work fine, they’re unwieldy and should be avoided if at all possible. Why? Primarily because the data in the formulas is static, so if the conditions driving the formula ever need to be changed, the formula needs to be manually adjusted as a result. Granted, the grades example isn’t too bad, because it’s not likely that information will change too often, but just imagine how much work you’d have to do if you have a lot of formulas like the commission example, and you have to change the criteria. Ideally, you’ll get into the habit of only using IF statements for Text comparisons like the earlier example. Yes/No/Maybe or Male/Female evaluations are very common, and the nice thing about them is that criteria aren’t likely to change very often. If you find yourself with situations like this then by all means use IF statements, otherwise it’s time to move up to more robust alternatives, starting with LOOKUP.

One of the primary reasons to move away from IF statements for multiple criteria is so that you can use “table based” reference data. This gives you the ability to have your data points on a worksheet, where the values can be easily changed, as opposed to hardcoded in a formula, where changing the values can be a challenge. Many workbooks have broken because of numerous IF statements that someone didn’t update. In the commission example above, what would happen if you needed to change the 2% and you had hundreds of formulas depending on that one? It wouldn’t be fun, and that’s a relatively small example. Imagine one with 64 conditions! And that’s if you can even find the formula in the first place! Table based dependencies are much easier to change on the fly, which can mean a lot especially if you’re dealing with complex models and testing multiple criteria.

Note: data tables that drive referential formulas don’t need to be housed in the open where users can change them.  Very often you’ll find sensitive tables housed on hidden worksheets, where only the creator/administrator can change the data.

The first formula in the family of referential formulas is LOOKUP (they’re called referential formulas because they can return a reference to a value, or a value in a range of values). LOOKUP has plenty of uses, but it’s also the most fragile and least flexible in the Lookup family. And that will be the subject of part two of the posts, how to use the Microsoft Excel VLookup.

 

Here are a few quality sources on the subject.  Each of these sites offers free quality education pertaining to Microsoft Excel.  One is a Microsoft help forum, one is Microsoft’s site itself, and the third is a site owned and operated by one of the most respected professionals in the business.

 

  1. Do you prefer to learn via free Microsoft Excel training videos?  If so, here is the link where you can see not only Excel Vlookup, Index, Match, etc., training at the expert level.
  2.  Do you want to see what Microsoft has to say about Index/Match?  If so, click this link.
  3. Or do you prefer to learn how to use the Index/Match functions by Debra of Contextures.Com, one of the top sites on Microsoft Excel in the world, and one of the Microsoft Excel MVPs in Canada.  If so, click here to see what Debra says about Microsoft Excel’s Index Match.

 

We originally wrote this post a few years back. I uncovered it the other day and I thought it still had value to the visitor’s of this site. It is too long for one post, so we will break it up into a few, or you can click this link to read the full article now.

 

NOTES: this article is written for Excel 2010 and its Ribbon Interface. The examples you see were created in Excel 2010, but saved as Excel 97?2003. Working examples are hosted on Microsoft’s [url=http://cid?8cffdec0ce27e813.skydrive.live.com/redir.aspx?resid=8CFFDEC0CE27E813!195]SkyDrive service[/url] for you to download

.

 

Stay tuned for part two of this article, where we go over the Microsoft Excel VLookp in detail. That then followed by the use of the Index/Match Functions.

 

 

TAGS:
Feb 4 2015

Free Excel Training

Would you like to receive the same Microsoft Excel training that clients pay $250 an hour for, for free?  No, really. Here are 10 free training videos on Microsoft Excel (See the links below).

If your company was to hire us to do a webinar on Microsoft Excel formulas and functions, Pivot Tables, POWER Tips & Tricks, Excel Tables, or the like, your company would pay $250 an hour.  Same exact topics, same trainers, but you can view these recordings which go over our most popular topics,  for free.

Two of the top websites that offer CPEs for CPAs hire us to provide Excel training.  Once again, same topics, same speakers, but you can see them for free.

We are not trying to sell you anything.  Nor are we trying to get you to get your company to pay for training.  So why do this?  Simple, ExcelHints.Com is owned by the VBAX Group.  The VBAX Group owns and operates Vbaexpress.Com, one of the leading Microsoft help forums in the world.  The forum is there to help people with their Microsoft application problems, including VBA. One way that Vbaexpress.com helps its members is by providing free training webinars on Microsoft Excel.  And when we do we record them, we add them to the two video pages on the website, so anyone, anywhere, can view them at anytime. Interested?  If so, read below, click the link, view the videos, and come back here and leave a comment, letting us know your thoughts.

See part-one and part-two, two hours each, covering Microsoft Excel Functions and Formulas. That is four full hours of free Excel training by Dennis Taylor.

Information on this series of free webinars: Working with Microsoft Excel 2013 formulas and functions is absolutely critical to building powerful financial and analytical worksheets and is probably the most important skill set for many Excel users. This session gives you perspective on what functions are available and explains (with extensive examples) how to use many of them, including: the IF function (with AND and OR), the powerful VLOOKUP function, selected Date, Statistical, Financial, and Mathematical functions, and the frequently overlooked Text functions.

Part-two of the four-hour series on working with Microsoft Excel 2013 formulas and functions by Dennis Taylor: Index, Match and other the frequently overlooked functions.

  • Learn formula-building shortcuts and how to use formula-auditing tools
  • Learn how to build 3-D formulas to tabulate totals from multiple worksheets.
  • Learn how to use the powerful IF function and the related AND and OR functions for programming-like capability.
  • Learn how to look up information in tables using the VLOOKUP function
  • Use the AGGREGATE function to circumvent error-laden data
  • Learn how to use selected Text functions to extract and manipulate character and numerical data for sorting and analytical purposes.
  • Learn how to harness the power gained from using multiple functions in a nested fashion
  • Learn how and when to use the powerful COUNTIF/SUMIF family of functions
  • Learn about new Excel 2013 functions – ARABIC, ISFORMULA, FORMULATEXT
  • Learn formula-building shortcuts and how to use formula-auditing tools
  • Learn how to build 3-D formulas to tabulate totals from multiple worksheets.

 

See the Microsoft Excel POWER Tips & Tricks Excel training video.

 

See part-one and part-two of Microsoft Excel Pivot Tables training video.

 

We add Excel training videos every few months, so please check back and see what else we have to offer.  Oh, speaking of offers, we are in the process of adding three 2-hour training videos on Microsoft Access.

When you visit Vbaexpress.Com please take the time to register and to become a member.

 

 

Jan 25 2015

The Second Most Used Excel Keyboard Shortcut Might be ‘Ctrl’ ‘Z’.

Let’s face it, using keyboard shortcuts in Excel, as well as the other Microsoft applications is one way to increase efficiency. Why reach for the mouse, move it to the ribbon of choice, and select a command, when you can simply hit ‘Ctrl’ ‘Z’? ‘Ctrl’ ‘C’, ‘Ctrl’ ‘V‘, ‘Ctrl’ ‘Y’ and ‘F4′ are some of the most used keyboard commands, and they are not used just by Lotus 123 users, but newbies alike.

Microsoft Excel Ctrl Z KeyBoard ShortCut

So what does the keyboard shortcut combination of the Ctrl key and the Z key do?  Simple, it undoes what was just done.  Now you have to admit that if we work in Excel for any period of time, we could use Ctrl Z easily ten times a day. I mean personally I could use it 20 or more times a day, and I often find myself wishing that there was an undo key for life.

 

So there you have it, Ctrl Z will allow you to erase an action just performed.  Undo the deletion of a row, the formatting of a cell, the pasting of data, etc. (Please post a comment on the ways you usually use the short cut.)

 

So what are some of the sites that list these shortcuts as well as others?  Look below and see a few of our favorites.

 

According to ShortCutWorld.Com (A wiki-style reference database for keyboard shortcuts). This is one of the most complete pages on Microsoft Excel Keyboard shortuts.  I have it as a favorite.

3. Insert and Edit Data

minusplusUndo / Redo Shortcuts
Ctrl+z Undo last action (multiple levels).
Ctrl+y Redo last action (multiple levels).

 

 

Microsoft also has some pretty amazing pages on Keyboard shortcuts in Excel. This article describes what Key Tips are and how you can use them to access the ribbon. It also lists Ctrl combination shortcut keys, function keys, and some other common shortcut keys for Microsoft Excel 2013. At The VBAX Group we not only help the world with the Microsoft applications, for free via forums, videos, etc., we also do work for Microsoft itself.  Jacob just completed a PowerPivot project for the internal Microsoft team.  In terms of the keyboard shortcut ‘Ctrl’ ‘Z’, Microsoft says:

Ctrl+Z Uses the Undo command to reverse the last command or to delete the last entry that you typed.

 

But as always, the site that is defintly my favorite is Contextures by Debra. We are happy to say that we actually work with several of Debra’s clients.  Debra and Ken are both Microsoft Excel MVPs, and both are up north in Canada.  Ken is also active on VBAExpress.Com, one of the top forums on Microsoft Applications. Ken runs ExcelGuru.Ca, which is the only Excel forum in Canada that I know about.

 

Below is a short video we did on the Microsoft Excel KeyBoard ShortCut ‘Ctrl’ ‘Z’.  Take a look.

See the Excel VIDEO Hint on this topic, on YouTube.

 

We at the VBAX Group hope this post helps you in your daily Excel efforts.  If not please pst a comment or contact us directly.  If you are not already a member of VBAExpress.Com you should be. It is one of the largest forums on the Microsoft applications, not only Excel, but the rest of MS Office, in the USA.

Jan 19 2015

Favorite Excel Keyboard Shorcut Survey Results

This survey simply asked what are your five favorite keyboard shortcuts for Microsoft Excel.  So far 31 people have responded, their detailed results are below. Take the time to review them, and you will learn at least one more shortcut that you can use. Note, this is a raw data dump so you can see what is actually submitted.  We will present a clean summary when the survey closes.

Still want to take the survey, then click here.

 

First Preference
1 alt-=
2 Ctrl-C Ctrl-V
3 strg+a
4 ctr + z
5 alt-i-r
6 cntl-c (and v)
7 Control & c
8 cut
9
10 <ctrl>;
11 ctrl c
12 Alt + F11
13 ctrl-c
14 Ctrl-S
15 ctrl *
16 ctrl+c
17 ctrl c / ctrl v
18 Ctrl+D
19 Ctrl + C
20 ctrl + ;
21 Ctrl+C & Ctrl+V
22 CTRL S
23 control +
24 Cntrl+Shift+*
25 ctr + c
26 CTRL+SHIFT+L
27 Ctrl+C
28 ctrl+c
29 Crl+C/Crl+V copy/paste
30 control+c
31 Ctrl+1 (Format Cells)

 

 

Second Preference
1 alt-f11
2 Ctrl-T
3 strg+c
4 ctr + c
5 alt-i-c
6 end-cursor up   (and down, left, right)
7 control & v
8 copy
9
10 <shift><ctrl>:
11 ctrl v
12 Ctrl + C
13 ctrl-v
14 Ctrl-F6
15 ctrl home
16 ctrl+p
17 ctrl shift end
18 Ctrl+C
19 Ctrl + V
20 ctrl + c
21 F4 & F2
22 F12
23 control space
24 Cntrl+Shift+Down arrow
25 ctr + v
26 CTRL+Arrow keys
27 Ctrl+V
28 ctrl+v
29 Crl+shift+L to turn on/off filters
30 control+v
31 Alt+F11 (VBA Editor)

 

Third Preference
1 alt+shift+drag border to move/insert range
2 Alt-N V T
3 strg+v
4 ctr + v
5 F2
6 end-home (with shift as needed)
7 end & arrow
8 past
9
10 <ctrl>c
11 ctrl d
12 Ctrl + V
13 ctrl-tab
14 Ctrl-C
15
16 ctrl+f
17 ctrl home
18 Ctrl+V
19 Shift + END + Arrow Keys
20 ctrl + v
21 Ctrl+A
22 CTRL D
23 shift space
24 Cntrl+Shift+D
25 ctr + x
26 ALT+N+V+T
27 Ctrl+F
28
29 F2 to edit cell
30 control+A
31 F12 (SaveAs)

 

Fourth Preference
1 ctrl-arrows
2 Alt-W-I
3 strg+f
4 ctr + x
5 F4
6 cntl-home (with shift as needed)
7 control & h
8 print
9
10 <ctrl>v
11 ctrl :
12 F4 in absolute range cycling
13 alt ind
14 Ctrl-V
15
16 ctrl+a
17 ctrl page down / ctrl page up
18 Ctrl+Z
19 Ctrl + Z
20 ctrl + x
21 Alt+F11 & Alt+F8
22 ALT U U
23 control L
24
25
26 CTRL+-
27
28
29 Crl+1 to format
30 control+F
31 Ctrl+Shift+v (Format Paste)

 

1 Fifth Preference
2 Application.Onkey shortcuts for various macros in PERSONAL.XLSB
3 Ctrl Shft L
4 strg+1
5 shift + ctr + enter
6 alt-e-d
7 cntl-break
8 control & home
9 save
10
11 <ctrl>home
12 ctrl s
13 Ctrl + +/- to insert/delete
14 alt dp
15 Alt-F8
16
17 alt+F4
18 ctrl tab
19 Ctrl+Y
20 F2
21 ctrl + p
22 Alt+Enter while editing cell
23 CTRL F
24 control `
25
26
27 ALT+H+B+A
28
29
30 F4 to change cell references
31
Ctrl+H (Replace)

 

Open-Ended Response
1 Also: highlight range + ctrl-. ; f3; shift-space & ctrl-space & ctrl-a; alt-n-v; alt-a-s-s; ctrl-t; alt-e-s-v;
2
3
4
5
6
7
8
9 I don’t use any keyboard shortcuts.   I would love to know the results of this survey so I can learn to use the most common/popular ones.
10
11
12
13 alt ind for names (old menus), alt dp for pivot table
14 The first and second were easy. It was fun to figure out the order of the second three since I use so many keyboard shortcuts.
15
16
17
18
19 I use a lot of keyboard shortcuts as well as mouse shortcuts. These 5 are the ones I use the most.
20 I also have several keyboard shortcuts for macros I developed.
21
22 and anorher and another
23 Hard to choose, I extensively use keyboard shortcuts as they make your work in Excel so much faster.
24 In no particular order of preference.   Can’t recall any others I use regularly.
25 now I ussially use only copy-past shortcuts
26
27
28
29 There are so many…
30 all these very usefl
31 I love excel and still I feel I know little about the power of excel. Among the many applications which I used, no doubt, it is excel that excels everything. Hats off.

 

Here are a few of our favorite pages on the web on this topic.

Excel Made Easy

Vbaexpress.Com

ExcelConsultant.Net

 

 

 

 

 

 

 

 

 

Jan 19 2015

This may be the best keyboard shortcut in Excel – F4

This may be the best keyboard shortcut in Excel F4 (Repeat last action)

There are so many useful functions and hotkeys available in Microsoft Excel that many are not known nor used. That said, most intermediate to advanced users use at least one or two of these, such as ‘Ctrl‘ ‘C‘ to copy, ‘Ctrl‘ ‘V‘ to paste, ‘Ctrl‘ ‘Z‘ to undo, ‘F2‘ to edit a formula or what I use frequently, ‘F4‘ to repeat an action. It is said that the ‘F4‘ key is the most used keyboard shortcut in Excel.

Today’s Excel hint is on the ‘F4‘ hotkey. There are various ways to use it, they save keystrokes and mouse movement.  While it is incredibly useful it is important to note that the ‘F4‘ key does not repeat every action possible, and that there are also reported problems with the hotkey.  The best way to know what it will and will not do is to try it as you work on your workbook. But before you do, read what is below, look at the links that take you to some of the best resources on the web when it comes to Microsoft Excel. Then you will have an idea of what it possible.

————————————-

Examples of how to use the ‘F4” hotkey:

 

Repeat last action:

If you want to insert a row in Excel, you have several ways to do so, some use the mouse, some use the keyboard.  The way I do it is to activate the cell where I want to insert rows.  I then hit these keys:  ‘Alt’ ‘I’ ‘R’ = Insert new row in Excel. That inserts the first row.  For each additional row I then hit ‘F4’ key and it will repeat the action. If I want to insert 6 rows, I hit it 6 times. Quick, straightforward and simple.

 

Set Cell References: 

F4‘ Cycles through all 4 types of cell references (absolute, mixed reference (2x) and relative). For example, select a cell with a formula and press ‘F4‘. Notice what happens to the formula each time you it the ‘F4‘ key.  Why show this to you?  Because if you want to take the formula from one cell and place it in other cells, and still have it display the correct result, you will need to understand this, like it or not. Cell references should be your friend.

 

————————————-

 

There are also reported problems on the use of the hotkey, both Jon Peltier and Microsoft address the issue.

While the ‘F4 shortcut to “repeat last command” seems to not work in Excel 2010/2013 like it did with Excel 2003, I still use it often when I work in Excel, for example after I hit ‘Alt‘ ‘I‘ ‘R‘ (Insert Row), I often hit the ‘F4‘ key to repeat the action, and each time I hit the ‘F4′ key, it inserts another row.  However, in Excel 2010/2013 it does not seem to work anymore for charts.  To learn more about the problems, See what Jon Peltier – Microsoft Excel MVP has to say about it.

The Microsoft website says that the ‘F4‘ keyboard shortcut does not work as expected in Microsoft Excel 2013, Excel 2010, or Excel 2007. For example, when you sort a row (row A), and then you press F4 to repeat the action on another row (row B), row B is not sorted. Or, when you copy and paste a cell, and then you press F4, the cell is pasted. However, when you press F4 again, the cell is not pasted.  This is a known issue in Excel 2013, Excel 2010, and Excel 2007.  If you cannot use the F4 keyboard shortcut to repeat a command or action, manually perform the command or action that you want to repeat. See what Microsoft has to say about this.

 

————————————-

 

Good sources of information on keyboard shortcuts and Excel hotkeys.

 

 

Easy-Excel has some great examples of Microsoft Excel functions keys, such as the F4 key, click here to learn more.

 

————————————-

 

http://theexceladdict.hubpages.com/hub/F4-One-Of-Microsoft-Excels-Hidden-Gems Has great coverage on this hotkey.  You won’t want to skip this.

 

The Excel Addict says: This may be the best keyboard shortcut in Excel, according to the Excel Addict.

This is one of those rare shortcuts in Excel that is so useful, it’s amazing that it is never mentioned in basic training. As a matter of fact, I’ve rarely heard it mentioned anywhere. That’s why few Excel users ever use it.

When you want to repeat the last action that you’ve just taken, just select the range of cells where you want to repeat it an press F4.

For example, if you have just deleted a row and you want to delete another row, simply select the range where you want to delete the next row and press F4. If you have just added cell borders to a range and you format another range the same way, F4 will repeat the previous action for you. This is often most useful when you want to repeat an action over and over.

————————————-

 

Chandoo is one of the best resources on Microsoft Excel.  He presents topics in an easy to understand style.

http://chandoo.org/wp/2011/08/08/must-have-excel-keyboard-shortcuts

Chandoo says: 5. F4 – Change cell reference style while editing formulas

Change cell referencing style - F4 - Excel

Excel runs a pretty tight mafia when it comes to cell references. You must pay it a couple of $s if you want to change the reference style from relative (A2) to absolute ($A$2). But whos got the time to carefully navigate between A and 2 and then type SHIFT+4? So I use F4 key and Excel changes the referencing style for me.

————————————-

 

Have a question, either post a comment here or post your question on our Microsoft help forum, VBAExpress.Com

 

Please post your favorite uses of the ‘F4‘ key in the comments section.

 

844-VBA-Help

 

 

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives