Introduction to basic Excel Functions

A function is a predefined formula that performs calculations using specific values in a particular order. Excel includes many common functions that can be useful for quickly finding the sum, average, count, countif, maximum value, and minimum value for a range of cells. In order to use functions correctly, you'll need to understand the different parts of a function and how to create arguments to calculate values and cell references.
Here are some of the most common functions you'll use:

  • SUM: This function adds all of the values of the cells in the argument.
  • AVERAGE: This function determines the average of the values included in the argument. It calculates the sum of the cells and then divides that value by the number of cells in the argument.
  • COUNT: This function counts the number of cells with numerical data in the argument. This function is useful for quickly counting items in a cell range.
  • COUNTIF: For counting more complex criteria or data we tend to use the CountIf or for multiple criteria and comparisons we use CountIFs: =COUNTIFS(B2:B5,"=Yes")
  • MAX: This function determines the highest cell value included in the argument.
  • MIN: This function determines the lowest cell value included in the argument

AutoSum Command

The AutoSum command allows you to automatically insert the most common functions into your formula, including SUM, AVERAGE, COUNT, MIN, and MAX.

Excel practice worksheet
_excel2013_practice_worksheet_1.xlsx
File Size: 11 kb
File Type: xlsx
Download File

Tasks

  1. Calculate the average price per unit for a list of recently ordered items using the AVERAGE function in cell C15
  2. In Cell D2 use the SUM function to calculate the total cost of each item (quantity x price per unit)
  3. Calculate the totals from cell D2 to D14
  4. Use a SUM function to calculate the total quantity of items ordered in cell B15
  5. Calculate the Total costs in cell D16 for the totals in cells D1 to D14
  6. Use a SUM function to calculate the total quantity of items ordered in cell B20
  7. Use the COUNTIF function to count the number of delivery days that are "5 days" and place the function in cell B23
  8. Use a function to calculate the most expensive item and place this into cell B21
  9. Use a function to calculate the average shipping days and place this into cell B22
  10. Create a bar chart with a fully labelled x & y axis for Items against delivery time

Other Functions & Function library

While there are hundreds of functions in Excel, the ones you use most frequently will depend on the type of data your workbooks contains. There is no need to learn every single function, but exploring some of the different types of functions will be helpful as you create new projects. You can search for functions by category, such as Financial, Logical, Text, Date & Time, and more from the Function Library on the Formulas tab.

  • To access the Function Library, select the Formulas tab on the Ribbon. The Function Library will appear.
Microsoft Excel Functions support website link:
https://support.office.microsoft.com/en-us/article/Excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb#__toc309306701