4 Most Useful Excel Functions

4 Most Useful Excel Functions

4 Most Useful Excel Functions

Microsoft excel is a powerful tool used for data manipulation and organization. The mastery of its functions and formulas can help you exceed expectations and make you a Rockstar among your cubicle mates. However, new excel users may find the multitude of options overwhelming. If you are unfamiliar with excel, here are five of the most important functions a beginner can pick up and produce results quickly.

IF Statements

The If Statement formula is the ultimate validation tool within excel. The aim is to test a logical statement and return a corresponding statement if that value is true or different value if the statement false.

=IF(logicalTest,valueIfTrue,valueIfFalse)

To take this function to the next level try Nesting If statements. With the same beginning structure of an IF function the formula evaluates the Logical Test and provides a value if that statement is True. However, if that statement is false a new statement is evaluated. You can keep nesting IF Statements as long as it takes you to find a truthful result or otherwise. In some programming languages this is similar to an IF ELSE statement.

Here’s an example of  an IF statement evaluating three consecutive conditions. Notice the last value returns the default value if all of the conditions are FALSE.

=IF(logicalTest,valueIfTrue, IF(logicalTest2,valueIfTrue, IF(logicalTest3,valueifTrue, valueIfFalse)

VLOOKUP

My nomination for Microsoft Excel’s  Most Valuable Function would have to go to VLOOKUP. The VLOOKUP looks at a cell value and tries to find a match in another specified range. Then it returns a corresponding value from a column specified by the user.I know that may sound confusing so let’s dive into a sample VLOOKUP and see how it operates.

=VLOOKUP(valueToLookup, rangeToSearch, ColumnToReturn,TrueOrFalse) .

ValueToLookup- It’s the value you are trying to find a match for. You can input a value, a reference or a string of text.

RangeToSearch- In excel known as the Table array, it’s a range of cells being referenced to search for that sneaky value above.

ColumnToReturn -In the column furthest left of the table that you specified in “RangeToSearch” Excel is going to track down the value and then look to this field to churn out a corresponding value.

TrueOrFalse- Pretty straightforward field this section of the formula accepts two values; one being True, the other, wait for it…False! True will return the closest matches and False will only return a value for an exact match(False is most accurate unless you are okay with fuzzy answers).

While VLOOKUP is a great formula and I still use it all the time, it isn’t without limitations. One is that It can only be used when the value being searched for is to the left the values being returned.

The combination formula IndexMatch provides a workaround in some cases.

LEFT, RIGHT, MID

The LEFT, RIGHT and MID functions are three ways to extract characters from a string in a cell. Each one allows you to extract from a different portion of the cell as broken down below:

LEFT(Cell Value, Number of Characters)- Beginning with leftmost character, returns number of characters specified.

RIGHT(Cell Value, Number of Characters)- Beginning with rightmost character, returns number of characters specified.

MID(Cell Value, Starting Point in String, Number of characters to return)- MID uses the same fundamental concept as LEFT and RIGHT but with a slightly different execution.MID  Begins at point specified by user and returns the amount of characters determined in the formula.

CONCATENATE

The definition of concatenate is to chain or link together in a series and excel’s reluctance to abbreviate this command leaves little mystery as to the utility of this function. Actually excel does allow you to type CONCAT for short.

Use this function to join strings from two or more cells into one word.

CONCATENATE(Cell1, Cell2, Cell3 And so on); This function can also be used with a range of cells. If you want CONCATENATE to generate spaces or punctuation in your formatting, enter the space or punctuation in between as one of the items in the formula.

CONCATENATE(Cell1, “ “  , Cell2);

keepersecurity.com

Latest Blog Posts

Learn about SQL joins using Excel

Learn about SQL joins using Excel

Why do we join tables in SQL? Tables that logically represent information are the foundation of relational databases. The ancient rules of database normalization remind us to arrange database tables to reduce redundancy and increase flexibility. An adequately...

Super Bowl Squares Template

elink.io | See Original The Superbowl is almost here. If you are tasked with creating blocks for a pool, there's still time to pull out your ruler, draw up a grid, and wrangle a bunch of friends into writing their names in the squares. Or, spare yourself some time...

How to Return Multiple Matches in one Cell Using a lookup?

VLOOKUP is great for looking up a value and returning another corresponding value. But what happens when the first match just isn't enough. I've been tasked with finding a way to put return not just the first but the second, and third match. At first, I tried to...

How to Delete every other Row in Excel

How to Delete every other Row in Excel

Have you ever needed to delete every other row in Excel? You are not alone.   There are a few ways to accomplish it. It's a pretty good candidate for a VBA solution, but I'm going to show you how to quickly delete every other row without using VBA or a plugin. I...

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Microsoft Excel has roughly 750 million users all over the world. It is an essential, all-purpose business tool. This spreadsheet software can fill a wide range of business needs. Every business professional needs to get familiar with Excel. Your proficiency level...

Select the Email Service Provider that is right for you.

Select the Email Service Provider that is right for you.

  Excelshortcut.com commits to continuous improvement. I've wondered if our Communication distribution process is lacking. Is there a more effective tool to help us share valuable content? Mailchimp has been ExcelShortcut.com's tool for email creation and...

Planning Software
Share This