Useful Excel Formulas and Shortcuts to Make Your Life Easier

Whether you are a student or a professional, Microsoft Excel plays a crucial role in your college and workplace. In order to utilize this wonderful software and to make it work well for you, it is highly beneficial to become well-versed in a few Excel formulas and shortcuts to make your life easier.

Let us take a look at a few handy formulas and shortcuts that will turn you into an Excel pro!

Easy Excel Formulas:

1. SUMIF

SUMIF is a worksheet function in MS Excel which can be used to find the sum of all the cells that fit a specific criterion. For example, if you wanted to find the sum of all values above 5,000.

The syntax for SUMIF:

=SUMIF (range, criteria, [sum_range])

Range: Cells to which you wish to apply the SUMIF function.

Criteria: The particular specification to add the cells.

Sum_range: Cells that needed to be summed.

In the above example, the SUMIF function has been used to add together all the values that contain an asterisk (*)
 

2. IFERROR

This useful Excel function helps to identify errors in any Excel formulas you may have used. These mistakes can then be replaced with a blank cell or an alternative message.

The syntax for IFERROR:

=IFERROR(value, value_if_error)

Value: The particular value, reference cell or formula that has to be checked for errors.

Value_if_error: What to return if a mistake is detected.

In the above example, the IfError function identifies and eliminates the #DIV/0! an error message that appears when there aren’t any values in a particular field (“orders” column in this case). Instead of showing an error, a blank cell is returned as shown.
 

3.VLOOKUP

VLOOKUP is an abbreviation for “Vertical look up”. This function helps you to find particular information. It is extremely useful in case of large spreadsheets with a lot of data.

Syntax for VLOOKUP

=VLOOKUP (value, table, col_index, [range_lookup])

Value: The value to search for within the first column.

Table: The specific table where the value may be found.

Col_index: The particular column in the table where you may find the value.

Range_lookup: A choice to find the exact value (FALSE) or an approximate one (default).

Let us assume, we need to find the price for “Soap” in the above example.

E2 is selected as the cell to enter the formula (any cell can be chosen).

Applying the syntax, first we need to enter the name of the item to search for, here it is “Soap”.

Next, we enter the range of cells within which our information can be found, i.e., A2:B16.

Now, we enter the column index where our required data may be retrieved. In the example, we need the price of the handmade
soap which is in the second column. Thus the col_index is 2.

The last argument is a choice of whether to find an exact value or an approximate one. In order to choose an exact value, we need to enter FALSE.

The final formula is: =VLOOKUP(“Soap”, A2:B16, 2, FALSE)
 

4. INDEX and MATCH

INDEX and MATCH functions are used together to find specific values in an Excel sheet.

Syntax for INDEX:

=INDEX(array, row_num, [column_num])

Array: Cell range where the value must be found.

Row_num: The row number where the value has to be found.

Column_num: The column number from where the value should be found. Either row_num OR column_num maybe entered.

Syntax for MATCH:

MATCH(lookup_value, lookup_array, [match_type])

Lookup_value: Value you are searching for.

Lookup_array: Cell range to search within.

Match_type: Can be 1 (highest value less than or equal to lookup_value) 0 (exact value) or -1 (lowest value that is less than or equal to lookup_value).

In this example, using the formula:

INDEX(data, MATCH(value, lookup_column, FALSE), column) helps to find the information required, that is, the release year of the Cars movie.
 
Quick short-cuts for Excel:

1. Ctrl + Page Up or Down: Helps in easy navigation of an Excel sheet

2. CTRL + F: Retrieve and replace values

3. Ctrl + 9: Hide rows

4. Ctrl +0: Hide columns

5. Ctrl + Spacebar: Select the whole column

6. Shift + Spacebar: Select the whole row

7. Ctrl +y: Repeat the previous step

8. CTRL + ↑& CTRL + ↓: Go to the top of the column/ Go to the bottom of the column

9. Ctrl + z: Undo the previous step

10. Ctrl + k: Insert a hyperlink

These user-oriented formulae and shortcuts are sure to eliminate time spent working out data manually. They will make you more efficient, capable and faster in college and at the workplace. Try these out the next time you use Excel, and with practice, you will soon become an expert.

Check here to find out more useful Excel hacks:

Statistical functions in Excel

Date and time functions in Excel

Logical functions in Excel Part I

Logical functions in Excel Part II

Using Autosum function in Excel