If you are like many Americans you have at one time or another had the need to calculate mortgage payments. In the past these calculations were done on paper, using a calculator. But in recent years you have been given the option of doing them online or in a workbook such as Excel. Now while online is a good option we feel a Microsoft Excel 2013 spreadsheet is the better option, for several reasons. For one, you can save the workbook, data intact, to your computer or tablet. There is no need to have internet access. Printing options are also more advanced. So what we have decided to do is to develop a free amortization utility in Microsoft Excel 2013, and we are offering it free to our readers. You are encouraged to share the workbook with those that you know.
To use the CNN online tool simply click on the image below.
To download the Microsoft Excel 2013 Workbook click the on the image below, once you are on the Excel and Access, LLC website, you will be taken to the download page where you can download an ever increasing number of utilizes and training materials, all for free.
We have several other free utilities on the same page. Some are for Excel, Access, Word or Outlook. All are free and you are encouraged to share them as that is why we created them.
If you would like assistance with your workbook, please call 844-MS-Excel for assistance. At the VBAX Group we can help you with all of your programming and training needs.
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:
Another is calculating commission statements:
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.
- 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.
- Do you want to see what Microsoft has to say about Index/Match? If so, click this link.
- 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.
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.
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.
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.
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
|Undo / 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.
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.
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.
|4||ctr + z|
|6||cntl-c (and v)|
|7||Control & c|
|12||Alt + F11|
|17||ctrl c / ctrl v|
|19||Ctrl + C|
|20||ctrl + ;|
|21||Ctrl+C & Ctrl+V|
|25||ctr + c|
|31||Ctrl+1 (Format Cells)|
|4||ctr + c|
|6||end-cursor up (and down, left, right)|
|7||control & v|
|12||Ctrl + C|
|17||ctrl shift end|
|19||Ctrl + V|
|20||ctrl + c|
|21||F4 & F2|
|25||ctr + v|
|29||Crl+shift+L to turn on/off filters|
|31||Alt+F11 (VBA Editor)|
|1||alt+shift+drag border to move/insert range|
|2||Alt-N V T|
|4||ctr + v|
|6||end-home (with shift as needed)|
|7||end & arrow|
|12||Ctrl + V|
|19||Shift + END + Arrow Keys|
|20||ctrl + v|
|25||ctr + x|
|29||F2 to edit cell|
|4||ctr + x|
|6||cntl-home (with shift as needed)|
|7||control & h|
|12||F4 in absolute range cycling|
|17||ctrl page down / ctrl page up|
|19||Ctrl + Z|
|20||ctrl + x|
|21||Alt+F11 & Alt+F8|
|22||ALT U U|
|29||Crl+1 to format|
|31||Ctrl+Shift+v (Format Paste)|
|2||Application.Onkey shortcuts for various macros in PERSONAL.XLSB|
|3||Ctrl Shft L|
|5||shift + ctr + enter|
|8||control & home|
|13||Ctrl + +/- to insert/delete|
|21||ctrl + p|
|22||Alt+Enter while editing cell|
|30||F4 to change cell references|
|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;|
|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.|
|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.|
|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.|
|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|
|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.