Nov 3 2015

Free Microsoft Excel Webinar – November 5th at 12 noon pdt.

We are putting on a free Excel Training webinar for the Irvine Chamber of Commerce this Thursday. You are cordially invited to attend. You do not need to be a member. Just click on the link about 5 minutes before the webinar and you will benefit from Paul’s decades of experience providing Excel services to business.

This is not a webinar to sell our services, but rather a free webinar to teach you more ways to use Excel. This is a $375 value, if you were to pay to attend. So take full opportunity of the training.

Topic: “Use Excel Like a Pro and Master Your Data”.

Presenter: Paul Delke, Talon Computer Associates, Irvine California.

Date and Time: Thursday November 5th at 12 noon sharp, PDT.

You can attend the webinar by following this link, no registration needed: You can attend by clicking on the link at

Presented by: Excel and Access, LLC and Talon Computer Associates.

Oct 15 2015

We retooled our original site and we added a New blog, Videos, Webinars, and More!!!

We are excited to announce that our original site has been retooled and is now up and running! More than just a blog, there are videos, case studies, free downloads, free webinars, free reference cards and our NEW blog!!!!!

For those of you that do not know, at Excel and Access, LLC our team of programmers, instructors, and advisors truly enjoys getting to know our clients and partners. We’re glad we’ve been able to help you solve your technological troubles, streamline your systems, and make better use of Microsoft for your business. Thank you for giving us the opportunity to assist you with this ExcelHints.Com blog. We couldn’t do what we do without you.

As we know quite well, IT is a constantly evolving field—just when you think you’ve got it all figured out, something changes! We wanted to let you know that we’re always here to answer any questions you might have. You can check out our weekly blog updates for free tips and tricks for Excel, straight from our software specialists. If you need help with power pivot tables, macros, chart titles, and more, you know where to go.

Our blog can also give you an inside look at the latest news in IT. For example, you may have heard that Office 2016 was recently released. As you can imagine, this was big news for us Microsoft enthusiasts at the Excel Consultant offices, and we’ve published a post to help you decide if (or, more accurately, when) you should make the switch. Also, if you’ve ever wondered what a “Microsoft MVP” is, you can find out on our blog (hint: it’s not Most Valuable Player, but we do have one!).

If you need the answer to a more specific question or have a more complicated IT problem to handle, we would be delighted to help you out. We often complete multiple projects for our clients—in fact, we’ve got a repeat client rate of more than 80 percent! Our consultants love building long-term relationships with companies so we can really learn their needs and help them grow.

Has an annoying glitch been slowing you down? Are you thinking about upgrading your system but you aren’t sure what might be involved? Do your employees need more tech training? As you might remember, we offer comprehensive free consultations. During these appointments, we’ll answer all of your questions, address all of your concerns, and create a customized plan complete with all the important details so you can decide if you want to move forward with your next project or solution.

We’d love to stay in touch with your company and hear how you’re doing. You can follow us @ExcelandAccess on Twitter and connect with us on LinkedIn to see what we’re up to or get in touch. We also encourage you to check back for regular news and updates at our site,

Once again, thank you for being one of our wonderful members.

The Excel and Access, LLC Team and the Microsoft Office Experts at Vbaexpress.Com, lead by Jacob Hilderbrand, Microsoft Excel MVP

Jun 29 2015

Get your free Microsoft Excel 2013 Mortgage Calculator (Amortization Table)

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.


You can use the CNN mortgage calculator online.


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.


Download your free Microsoft Excel 2013 amortization workbook.

 Download your free Microsoft Excel 2013 amortization workbook.


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.




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:


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.


  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?!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.



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 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.



Page 1 of 21123451020...Last »

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks