There are over four hundred different formulas available in Excel. These built-in formulas make Excel one of the most powerful and most popular spreadsheet applications on the market today. A basic knowledge of the most popular formulas can save you time and effort in your work and personal life. The Excel 2013: Introducing Excel course from Udemy offers a complete introduction to Excel 2013 with video tutorials and step-by-step instructions to help you harness the power of Excel.
This cheat sheet contains instructions for some of the most popular formulas available in Excel as well as a guide to cell references and names.
Excel Cell Reference Cheat Sheet
Most formulas in Excel require a cell reference. There are a number of ways you can reference a cell, range, column, or row in Excel. How you define the cell reference will affect how the formula is applied and copied from one to another. Following shortly is the list of the most common ways to reference a cell in Excel.
For more information on the basics of Excel and how to get the most out of this powerful application, sign up for the Excel for beginners course.
Relative Cell Reference
A relative cell reference refers to the address of a particular cell and that cell reference is automatically adjusted when you copy a formula from one cell to the next. A relative cell reference consists of the address of the cell. Here is a worksheet with relative references:
If you use a relative reference, as the above example shows in column D, then when you copy the formula +A1+B1, to another cell Excel automatically changes the formula to reflect the new column or row numbers like +A2+B2.
Absolute Cell References
To stop Excel changing the row or column reference when you copy a formula, you can use absolute cell references in your formulas instead of relative references. To create an absolute cell reference you need to insert the “$” symbol into the cell reference to indicate that that value should not be adjusted.
To create an absolute column reference, for example, you add a “$” in front of the column name. So +A1 becomes +$A1. To assign an absolute reference to the row number, add a “$” in front of the row number: +A$1. To create an absolute reference for an entire cell, you need to add absolute references to both the column and row:
Excel Cell References and How to Use Them in Your Worksheets and Formulas is a step-by-step tutorial that will teach you how to use cell references in Excel.
Excel Mathematical Function Cheat Sheet
Excel is great for quickly manipulating numbers. If you need to add up numbers, keep track of sales, or create a personal financial budget then Excel should be your go to application. Here are the most commonly used mathematical functions you may need.
For comprehensive video tutorials that will teach you to harness the power of Excel in your business, sign up for the popular Excel for Business – Learn Excel Online course.
The SUM function
The sum function allows you to add up columns or rows of numbers. To access the SUM function, you can type =SUM in a cell and then select the range, column or group of numbers you want to SUM or you can select the “autosum” function on the home tab.
The SUM function can be used for columns of data. To sum an entire column or row of data you need to specify the range as ( column:column) or ( row:row) for example to SUM column A the formula would be =SUM(A:A). You can also use your mouse to select specific cells by holding down the CNTRL key and then selecting the individual cells you want to add together.
Excel Subtotal Function: What It Is and How It can Save You Time is a great tutorial on how to use the SUM and SUBTOTAL functions in Excel.
The AVERAGE Function
The AVERAGE function calculates an average based on a group of numbers you select. To use the AVERAGE function, you can either type “=AVERAGE” in the cell itself or you can select AVERAGE from the Editing functions found on the home tab.
The AVERAGE formula to calculate the average all of the numbers contained in row 2 for example would look like this:
The Average Function in Excel: Using AVERAGE in Microsoft Excel will show you how to use the AVERAGE function in your worksheets.
The COUNT Function
If you want to know how many items you have in a column or row then you can use the COUNT function. The COUNT function counts the number of cells in a specific range that contains a number. The count function only counts cells containing a number value, not text. To access the count function, select COUNT from the Editing functions on the Home tab.
The formula to count the number of items in a range would look like this:
Excel COUNTA: A Step-by-Step Tutorial to Show You How to Use COUNTA will teach you how to use the COUNTA function.
The MAX Function
If you want to find the largest number in a range, column or row, then you can use the MAX function to find the largest number quickly. To use the MAX function, type “=MAX“ to enter the formula into the cell or select the MAX function from the Editing functions on the Home tab.
The MAX formula to find the largest number in a range of columns would look like this:
The SUMIF Function
We often need to count values based on a condition. Excel’s built-in SUMIF function allows you to add all of the values within a range, column or row that meet a specified condition. To use the SUMIF function, you need to select the range of numbers you want to add together as well as specifying the conditions that must be met. The Excel Formulas course from Udemy is a great resource for learning how to apply various formulas to your worksheets.
The SUMIF function syntax can be expressed as follows:
SUMIF( range; criteria)
Range specifies the range of data to be summed. Criteria specifies what criteria must be met to be considered part of the sum. So if you want to sum all the number in the range A1:A20 if the numbers are greater than 20 then the formula would look like this:
Excel SUMIF: Using This Function to Calculate Sales of a Particular Item is a step by step tutorial on how to use this function in Excel.
The COUNTIF function
If you need to count the number of items in a range that meet a specific criteria, then the COUNTIF function is the function you need. The COUNTIF function is similar to the COUNT function because it counts the number of cells that contain a number value, but the COUNTIF function offers the added functionality of only counting numbers that meet your specific needs.
Let’s say you need to count all of the cells in a worksheet that are less than 12 to determine items in your store that don’t sell well. To use the COUNTIF function, you can either type “=COUNTIF” into your target cell or you can access the function via the Excel formulas tab. The COUNTIF function is part of the statistical functions, found under the More Functions tab.
The COUNTIF formula will look like this:
Excel is truly the most powerful spreadsheet application available today. Here are some great courses and resources that will help you learn to harness the power of the application: