We all know how intimidating Microsoft Excel can be at times, especially, for a beginner. You know, with all the rows and columns of cells and hidden formatting tools — all which have been known to drive a tech ‘newb’ mad!
And it’s true: without proper excel training; many users waste a lot of time; blindly plugging away on their computers for hours, stressed out beyond belief. Sounds familiar? Good news: it doesn’t have to be that way. Below, Learn iT! Anytime highlights easy-to-use excel formulas sure to help maximize efficiency.
Formula: =SUM(5,5) or =SUM(A1,B1) or =SUM(A1:B5)
An easy one, but extremely useful when performing basic calculations. Add two or more numbers together in a simple formula or refer to different cells in the formula to get the values you need. Customize your formulas a bit can you can subtract, multiply and divide too.
The count formula simply counts the number of cells that contain numbers. For example, if you have a list of cities with over 650,000 people, you can simply execute the formula on the list – finding out how many cities exactly fit the criteria.
Formula: =ROUND(A5,2), ROUND(number,num_digits)
Rounding up numbers on a sales spreadsheet could be time consuming – if you wanted to make things easier, round up to two decimal places entering the value 2 as shown in the above formula on a specific range, definitely makes a difference when you have pages of number crunching to do.
Right, Left, Mid Formulas
Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters)
These are the easiest ways to manipulate a string of info in a cell by using the left, right and mid formulas. Sometimes you will only need a portion of info in that string, this will save you time and a few headaches giving you the ability to take only what you need.
Date and Time Functions
Formula: =YEAR(A1), DATE(YEAR(A1)+4)
Aside from adding the current date you can also sort data by date, or add a number of years, months and/or days to a date. The formula above extracts the ‘Year’ from cell A1. The second formula adds 4 years to the date in cell A1. In more powerful formulas with the date and time embedded, you could find when bills are due or when invoices need to be sent out.
Formula: =VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
One of the most used of the lot, this formula can help you find specific information in large data tables such as an inventory list or a large membership contact list. It looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. Sounds tough, but after a handful of tries you’ll appreciate the ease of Vlookup.
If Statement Formulas
Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)
The IF function tests to see whether a given condition is true or false, so if we wanted to find out who from a retail staff met their quota or exceeded it, we could plug in the formula in a new column.
SUMIF, COUNTIF, AVERAGEIF Formulas
Formulas: =SUMIF(range, criteria, sum_range), =COUNTIF(range, criteria), =AVERAGEIF(range, criteria, average_range)
The IF function can also be combined with the SUM, COUNT and AVERAGE formulas. Using IF with these can give you more flexibility as you gather important information. For example, SUMIF function allows you to add up specific values in a selected range of data that meets specific criteria, so you can calculate the total sales of clients who have made more than 250 orders.
And voila! You now you have eight new Microsoft Excel tricks to help you along your way. For more Excel tips, visit our training page. If you feel like we missed some key formulas, please let us know in the comment box below!