Excel ConsultantProgrammingMicrosoft ApplicationsMicrosoft ExcelTop 5 Microsoft Excel Formulas And Functions

Top 5 Microsoft Excel Formulas And Functions

That Every Financial Analyst or Accountant Should Know

Top 5 Microsoft Excel Formulas

That Every Financial Analyst Or Accountant Should Know

Five Powerful Excel Functions

While there are hundreds of formulas and functions available in Microsoft Excel, most of those are seldom used by even the most advanced Excel users.  Of those Excel functions and formulas that are used, there are of course some that are used a lot more than others, and knowing those well makes your work with Excel all that much the easier and efficient.

 

5 Most Important Microsoft Excel Functions & Formulas For Finance & Accounting Professionals

  1. VLookup/HLookup
  2. SumIf/SumIfs
  3. Sum
  4. If
  5. Concatenate

If you know how to use these 5 formulas and functions you will be ahead of the pack when it comes to building powerful budgets, forecasts, month-end reports, etc., in Microsoft Excel.

Note, these 5 functions and formulas are quite often used in conjunction with the other formulas and functions on this page as well as many others, the combined use of the VLookup function and the Concatenate function for example is quite common, as it appears in this post.

 

VLookups/HLookups

Perhaps The Most Used Excel Formula

The VLOOKUP is the most useful functions in Microsoft Excel, period. The HLookup is also used, but less so. Both of these are worth their weight in gold.

The Excel VLOOKUP can initially be difficult to learn and to fully understand as it more complex than other simple Excel formulas and functions such as sum, counta, min, max, etc.

This function is so powerful and so useful that it is a requirement for anyone that is serious about Excel development, and especially for those that want to be a top tier Excel consultant,trainer or a senior financial systems analyst.

If you want to see exactly how this function works please watch our three free training videos that cover the Microsoft Excel VLookup in amazing detail. While you are there please check out the other formulas and functions videos we have produced, topics covered are Pivot Tables, Tips & Tricks, Formulas and Functions, Excel Tables, Microsoft Access databases, etc.

If however you are looking for training on the VLookup or on any of the hundreds of formulas and functions available in Microsoft Excel, we can do one-on-one training either in our Irvine California office, or remotely using GoToMeeting.  We can also do onsite group training for up to 30 people, or we can use GoToWebinar to train up to 1,000 people remotely. See our training page to learn more about our Microsoft training services.

Microsoft Excel VLookup and HLookup reference materials: Microsoft Excel VLookup.

2.SumIf/SumIfs

Perhaps The Second Most Used Excel Formula

SumIf Function (Single Criteria):

The SUMIF function is one of the most used formulas in Microsoft Excel for those that work in finance.  The SumIf worksheet function is extremely powerful, it adds all of the numbers in a range of cells based on the one criteria you specify. That is very useful as you can imagine when you are working with BIG data.

An example of using the SumIf function on some of your BIG datasets where there are hundreds of thousands of rows would be summing inventory items by SKU, where each SKU is in multiple rows. This summary data could then be used in your Microsoft Excel Dashboards that are full of KPI data for management’s use. This is where the true power of the SumIf function can be seen.

If you would like to learn how to use the SumIf function you can watch our Formulas & Functions videos where internationally recognized Microsoft Excel trainer Dennis Taylor covers the Excel SUMIF function.

 

Microsoft Exce VLookup and HLookup reference materials Microsoft Excel SumIf .

 

SumIfs Function (Multiple Criteria):

The SUMIFS function allows for the use of multiple criteria. An example of the multi criteria SUMIFS would be summing inventory items by both SKU and State.

 

Do you want to learn more about the Microsoft Excel SumIfs function, visit the Microsoft website for more.

3.Sum

At Some Point Everyone Uses This

Okay, so this is a simple one; not too complex and not too much to it, but there are a variety of ways that you can use this that you may not have been aware of.

When you want to add values in multiple cells, do you do this?

=F9+F10+F11+F12+F13+F14+F15

If you said yes, well, you shouldn’t.

You could do this instead.

Method 1 Writing a Sum Formula – WikiHow
  1. Decide what column of numbers or words you would like to add up.
  2. Select the cell where you’d like the answer to populate.
  3. Type the equals sign then SUM. Like this: =SUM.
  4. Type out the first cell reference, then a colon, then the last cell reference. Like this: =Sum(A2:A4).
  5. Press enter.

4.If

The If Function is a true powerhouse.  You magnify that when you combine it with other Excel functions such as SumIf/SumIfs, VLookups,  and other If statements.

It is very common to nest If Statements in an effort to make a workbook even more dynamic. Learning to use Nested If Statements should be a priority when it comes to programming in Microsoft Excel.

If type functions and logic are usually used in VBA.  You would need to look long and hard not to find If type statements in Excel VBA.
5.Concatenate

Embedded In Other Functions

Concatenate, really, in the top five Microsoft Excel formulas and functions list?  Merging the data from multiple fields does not seem to be that useful?  Well think again.

Concatenation merges/combines data from two or more cells into one value, either placed in a cell or used as an embedded value in a formula. You then use the resulting value in other Excel formulas or functions, such as a VLookup or SumIf.

You would have been unable to use the formulas or functions without the concatenation, unless you wanted to revise the design and layout of the workbook. So while simple in its use, it is very powerful and often necessary function for many financial Excel workbooks.

=VLOOKUP(A10&B10,C2:D7,2,FALSE)

 

 

  • * You can use the Concatenate function with or without the word ‘concatenate’ in the first field as seen above and below.

 

  • ** The VLookup and SumIf functions often have the concatenate function embedded in them.
You Choose How To Contact Us

There are literally hundreds of formulas and functions in Microsoft Excel.  But if you take the time to look at them you will quickly come to this conclusion, no matter what you personally use Excel for, be it for managing inventory, developing month-end financial reports, or to determine stock trades, there are just way too many formulas or functions that you will never ever need to use or use.  But with that said, if you need to use Excel to manage and to manipulate numbers, there are functions and formulas to get it done.

Most users can get Excel to do what they want with just 20 formulas and functions, but of those there are 5 that are the most used, and that we highly recommend you learn how to use, and to use properly.

After 15 years in the trenches building Microsoft Excel solutions for dozens of finance and accounting departments for big business has lead us to this list of 5 Microsoft Excel formulas. So here are what we feel to be the top 5 Microsoft Excel formulas and functions for accounting and finance professionals:

 

 

Call us now and get started today 877-392-3539

 

Free Consultation

 

 

Contact Us
  • This field is for validation purposes and should be left unchanged.