Top 5 Excel Formulas to Increase Productivity
There are over 300 built in functions in Excel. If you’re not keen on memorising them all, don’t worry. We’ve picked 5 of our favourites to get you started and increase the productivity of your spreadsheeting.
Formula: =COUNT(First cell in range:Last cell in range)
The COUNT formula counts the number of cells in a range that have numbers in them. The formula is extremely useful, but only works with numbers. If you want to count up all cells with any information in you can use =COUNTA.
2. IF Statements
Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)
When analysing data in Excel, there are many scenarios where you’d want the data to react differently depending on the situation. Logical functions return only two possible answers – either TRUE or FALSE. The basic logical functions are:
1. < smaller than
2. > greater than
3. = equal to
In the example below, the formula tells us whether our salesmen have met their targets or not.
Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
When using a VLOOKUP, you define a value (lookup_value) and the formula looks for it in different column of the same row (table_array). Why is it useful? A VLOOKUP allows you to find a specific value within a large range of data and, once the value has been found, pick some additional information associated with such value and display it somewhere else in the same row. Useful, for example, when deciding a commission rate based on the level of sales.
4. SUMIF, COUNTIF, AVERAGEIF
=SUMIF(range, criteria, sum_range),
=AVERAGEIF(range, criteria, average_range)
SUM, COUNT and AVERAGE are relatively basic formulas, but if you want to use them with any additional criteria then familiarize yourself with SUMIF, COUNTIF and AVERAGEIF. They are a quick way to count, sum up or average the number of cells based on a given criteria.
Formula: =CONCATENATE(A1,” “,B1)
This function joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items. For example, if your worksheet contains a person’s first name in cell A1 and the person’s last name in cell B1, you can combine the two values in another cell by using: =CONCATENATE(A1,” “,B1). It’s particularly useful to clean large databases quickly by avoiding repetitive manual work. You can also use an “&” to connect cells for example =A1&A2.
All this and more is covered in the Skillsology Excel course, where we’ll teach you the key functions of Excel.
If you enjoyed this post, then why not check out how to Count Occurrences Quickly Using COUNTIF.