Simply knowing how to enter data and format a spreadsheet may get you in the door of a company but it’s time to equip yourself to go further. Microsoft Excel is a powerful tool for analysing, organising and presenting data, if you know how to use it.
Here are the top Excel functions that you really need to know.
Basic Maths Functions in Excel
The SUM function is super easy to use and should be part of your Excel function toolbox. If you want to add a column or row of numbers, just enter the function with the range of cells to add up all together. For example, if you have numbers in cells B2 to B250, just go to cell B251 and enter =SUM(B2:B250). Then it will add up all your numbers in the excel boxes.
=AVERAGE, =MAX, =MIN, =COUNT, =COUNTA
These work exactly the same way as the SUM function. Just substitute the function name in place of SUM. If you have a row of data (in cells C2:H2) to average, just go to the cell where you want the result of the function displayed and enter =AVERAGE(C2:H2). The MAX function will find the highest number, or maximum, in a group. MIN looks for the lowest, or minimum, number. COUNT will count how many cells in the range have numbers in them. COUNTA is to count cells that have text in them. If the cell is empty, COUNT and COUNTA will not count them.
How to Create Date Functions in Excel
The Now function will put the current date and time in the cell where you enter the formula. Then, use the date in other formulas. Yes, you can do calculations using the date!
Another way to get the current date or time in a cell is:
- Insert current date = Control + ;
- Insert current time = Control + Shift + ;
To calculate the total number of days between two dates, you can use the DAYS function. For example, enter 1/6/17 in cell A1 and 1/7/17 in cell B1. In cell C1, enter the function =DAYS(B1,A1) to get the total number of days between the two dates, which would be 30. If you enter =DAYS(A1,B1), the function will still work but will give you a negative number of days.
The NETWORKDAYS function is almost the same as DAYS, except that it calculates only the number of work days between the two dates. Yep, excel can take out the weekend dates for you! The function is read as NET WORK DAYS. Using our DAYS function example, you would use =NETWORKDAYS(A1,B1) to get your answer in a positive number of days, which would be 22 days.
Creating a Date or Calendar in Excel
In Excel, you can enter a date in one cell and then use the fill handle to create a list of dates that increase in one day increments. The fill handle is the small green square at the bottom-right corner of your active cell or range of cells. I will fill patterns for dates, numbers, lists, etc.
One Day Increment
For one day increments, type a date in a cell. Move your mouse over the bottom-right corner of the cell over the small green square. Your mouse pointer will turn into a black plus sign. Then, click and drag down or across to use the Autofill feature to fill in the dates for you.
To use a pattern besides a single day jump between dates, such as how to automatically write the Monday’s of each month in excel, you would need to enter the first date and then the second date in the next cell. For example, in cell B2 type 05/06/17. In cell B3, type 12/06/17. Select/Highlight cells B2:B3. Then, move your mouse over the fill handle and click and drag down to B10. There you have it!
Analysing and Reading Data in Excel
The IF function is incredibly powerful. You could look at a list of weekly sales data for your salesforce and figure a 3% bonus for those salespeople who had higher than $99,999 in sales for that week and $0 if they had $99,999 or less. In cells B2:B6 would be your weekly sales numbers and in cell C2, you would input the function =IF(B2>99999,B2*1.03,0). Excel will look at the sales number in cell B2 and see if it is above $99,999. If that is true, then it will take the value in cell B2 times 1.03 (which is the amount of the B2 cell plus 3%). If the weekly sales amount is $99,999 or less, then the amount would be $0. You can copy the formula from cell C2 down to cells C3:C6 to have it apply the IF function to all of the sales data.
To add up the cells that meet certain criteria, you want to use SUMIF. For example, if you want to add up the invoice totals for all unpaid invoices, you could have the invoice amounts entered in cells B2:B6. Then, enter unpaid or paid in cells C2:C6. In cell B7, you could enter the function =SUMIF(C2:C6,”unpaid”,B2:B6). Excel would look in column C for any cells that had the word “unpaid” in them. Then, it would use the corresponding cells in column B to add up the amount for the unpaid invoices.
This works the same way as SUMIF, except that it will do a COUNT instead of a SUM.
These are just some of the formulas that are commonly used in the workplace. There are more advanced ones (like VLOOKUP, MATCH, AND, etc.) that can be incredibly useful, too. So, don’t stop learning more about Excel functions, especially if you are in a specialised industry like accounting or engineering.
What’s your favourite Excel function?