10 Microsoft Excel Formulas For All Kinds Of Work
These top 10 Microsoft excel formulae will help you get on top of all kinds of work and help you perform better as a manager or analyst
Are you still wasting long hours working out calculations and formulas manually? These top 10 formulas are much favored by managers, data analysts and business consultants for making their work easy. How would you like to adopt them too?
1. VLOOKUP( )
VLOOKUP is a smart function that looks for a given value in a MS Excel table and returns the corresponding entry without much ado. Whether you need to know more about the product name that has an ID= pO76908, or a student named “Henry”, VLOOKUP would search it for you.
Syntax for VLOOKUP
=VLOOKUP (“Po76908”, product, 5, true)
=VLOOKUP (value being searched, table, column number, whether table is sorted or not)
2. SHEET( )
Microsoft’s Excel's SHEET( ) function helps managers find the number of the referenced sheet with the help of a single argument, which is the name of the sheet in question.
- In case the reference is omitted, the SHEET ( ) the number of the sheet is returned.
- If a valid sheet name is not given as reference, #REF! error is returned.
- In case there is no sheet by the reference name, even though it is a valid name construct, #N/A error is returned.
Suggested read - Eight Useful Microsoft Excel Shortcuts That You Should Know
3. WORKDAY ( ) / NETWORKDAYS ( )
Simple as it may sound, while handling multiple projects, project managers often find it difficult to ascertain the start or end date of a project stage, or find out the number of workdays to be assigned for a particular job.
WORKDAY ( ) : It finds the end date after taking the no: of working days and start date as inputs.
NETWORKDAYS ( ) : Finds out the number of working days between the given dates (with Saturday and Sunday being taken as a weekend or non-working days.)
4. COUNTA ( )
More often than not, analysts find themselves at their wits end with regards to the number of cells that have values, and those that are empty. The COUTA function finds out the total number of non-empty cells present in a selected range; in other words, it tells how many cells have alphabets, characters, special characters, or other inputs in them.
Syntax for COUNTA
=COUNT (A1:A10)
Suggested read - Smarter Ways To Work In Excel
5. SUMIFS ( )
Have you been finding questions like “What was the number of B model motors sold in region A, to customer type C , in the year Y” cumbersome? Fret not. Simply learn the SUMIFS formula and add up any given set of values without going through innumerable filters or selections.
Syntax of SUMIFS
=SUMIFS (what needs to be added, column of condition1, condition1, column of condition2, condition 2….)
Example:
=SUMIFS (sales, areas, “D”, products, “C”, type of customer, year, “Y”)
6. LEN( )
Want to know the total number of characters that fills up a cell? Just be a little careful though; this function counts up the spaces that appears in between the characters!
Syntax for LEN
=LEN (A1)
6. IF ( )
For all you analysts using MS Excel, do know that the IF function would return a certain value in case a condition/ logical statement is TRUE and another if it is FALSE.
IF (logical_test, value_if_true, [value_if_false])
So, if you have been looking for an effective Excel formula for rating your team on the basis of their performance matrix, especially after determining if a particular condition was satisfied or not, the IF formula is what you should be implementing in your reports.
Suggested read - Most Used Shortcut Keys in Microsoft Excel
7. AVERAGE ( )
Have a long list of sales figures pertaining to different regions and have little time to calculate the average sales that took place in any given month? This statistical function would return the average (arithmetic mean) of the range of numbers provided by you in a spreadsheet. Here, the values in the range can be in the form of arrays, defined ranges, numbers, or number references.
=AVERAGE (number1, [number2,…. number n ] )
40% OFF on our best selling course on MS Excel. GET IT NOW!
8. ISFORMULA( )
The ISFORMULA function is a yet another MS Excel tool that helps managers reference any defined cell to return a TRUE / FALSE value in case there is a formula present in the cell /or not. Here, you need to be extra cautious as your idea of a formula may not match that of Excel’s!
9. CONCATENATE ( )
This Microsoft Excel function helps in collating data from two different cells into a single cell. Say, you want to add the first name (“Mark” as in cell A1) and second name (“Anthony” as in cell B1) of a specific employee in cell B11, use:
=CONCATENATE (A1, “ “, B1)
The resultant data in cell B11 will be “Mark Anthony.” Voila!
10. IFERROR
You are okay with handling your errant staff and dysfunctional coffee vending machines; but you throw your hands up when it comes to figuring out errors such as VLOOKUP #N/As. The evergreen function comes to the aid:
IFERROR (formula, action to take in case there is an error)
For instance, IFERROR (VLOOKUP (….), “Value found!”)
So, are you all geared to hook up to these Microsoft Excel formulas to make your work more manageable and easier than before? Just go for them – you will be glad you did!