Training on Creating Formulas for Microsoft Excel
If you’re spending hours inputting information and calculations into Microsoft Excel manually, there’s some good news and some bad news. The bad news is, you’ve been wasting your time. The good news – you don’t have to do that anymore!
Excel comes equipped with countless built-in formulas that can simplify your life, accelerate your workflow and make calculating easier and more accurate. Once you get the hang of them, you’ll be amazed at the amount of time you can save with these handy Excel formulas, and as an added bonus, they are often more accurate than typing away at a calculator by hand.
1. LENFormula: =LEN(A1)
This nifty formula counts the number of characters in a cell. But be careful: it will also count spaces.
2. SUMFormula: =SUM(A1:A13)
No rocket science here! This formula easily and quickly calculates the sum value of a series of cells.
3. COUNT and COUNTA
Formula: =COUNT(A1:A13)
Use the COUNT formula to count numbers in a range, while ignoring blank and empty cells.
Formula: =COUNTA(A1:A13)
Similar to COUNT, COUNTA counts all characters in a range while also ignoring blank/empty cells.
4. TODAY (Date)Formula: =TODAY()
Need a regularly updating date cell for your spreadsheet? Use this formula to enter today’s
date.
5. AVERAGEFormula: =AVERAGE(A1:A13)
The average, or mean, formula takes the value of cells in a range, adds them up, and then divides that number by the number of cells, giving you an average value.
6. MAX and MINFormula: =MAX(A1:A13)
Use this formula to return the maximum value contained within a given list.
Formula: =MIN(A1:13)
Use this formula to return the minimum value contained within a given list.
7. DAYS and NETWORKDAYS
Need to calculate the number of days between two dates? Simply put the dates in their respective cells (end date in A2, start date in A1) and use this formula:
Formula: =DAYS(A2,A1)
Similarly, use this formula to calculate the number of workdays within the same specified time frame.
8. TRIM
Need to copy and paste a whole bunch of data into your spreadsheet? Chances are you’ll get some messiness in the conversion, including empty cells. Use the TRIM formula to clean it up in a snap!
Formula: =TRIM(“copied cell range”)
9. RANDOM NUMBER
If you need to generate a random number to stimulate some activity in your spreadsheet, use this formula to make it happen.
Formula: =RANDBETWEEN(10,100)
10. LEFT and RIGHT
Need to create some user names based off of text within a series of cells? These formulas work by taking a specified number of letters from the left or right side of the text entered in a cell.
If you have the text “Hello” in cell A2, and you want to pull the first two letters from it (“He”), your formula is =LEFT(A2, 2).
Do the same to pull numbers from the right: =RIGHT(A2, 2), to get “lo.”Using these formulas can not only make your life easier, it can save you a ridiculous amount of time, especially if you’re working with huge data sets. It’s also a more accurate way of working with your data! The formulas outlined here, though, just barely scratch the surface of what Excel is capable of.
Need more Excel formula help? If you’re interested in taking your knowledge of Excel to the next level, and therefore achieving greater success in your job or business through that knowledge, consider becoming Excel certified with ONLC Training Centers. You (and your boss, if it applies!) will be glad you did.
