Microsoft Excel

Introduction

One of the most powerful features in Excel is the ability to calculate numerical information using formulas. Just like a calculator, Excel can add, subtract, multiply, and divide. In this lesson, we’ll show you how to use cell references to create simple formulas.

Optional: Download our practice workbook.

Watch the video below to learn more about creating formulas in Excel.

https://youtube.com/watch?v=xc14gFFyiTw%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

Mathematical operators

Excel uses standard operators for formulas: a plus sign for addition (+), minus sign for subtraction (), asterisk for multiplication (*), forward slash for division (/), and caret (^) for exponents.

Mathematical operators in Excel

All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates.

Understanding cell references

While you can create simple formulas in Excel using numbers (for example, =2+2 or =5*5), most of the time you will use cell addresses to create a formula. This is known as making a cell reference. Using cell references will ensure that your formulas are always accurate because you can change the value of referenced cells without having to rewrite the formula.

In the formula below, cell A3 adds the values of cells A1 and A2 by making cell references:

A formula in Excel using cell references

When you press Enter, the formula calculates and displays the answer in cell A3:

The formula automatically calculates the result.

If the values in the referenced cells change, the formula automatically recalculates:

The formula automatically updates if one of the referenced cells changes.

By combining a mathematical operator with cell references, you can create a variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as in the examples below:

Illustration of simple formulas

To create a formula:

In our example below, we’ll use a simple formula and cell references to calculate a budget.

  1. Select the cell that will contain the formula. In our example, we’ll select cell D12.Selecting cells
  2. Type the equals sign (=). Notice how it appears in both the cell and the formula bar.Starting the formula with an equals sign
  3. Type the cell address of the cell you want to reference first in the formula: cell D10 in our example. A blue border will appear around the referenced cell.Entering the cell reference for D10
  4. Type the mathematical operator you want to use. In our example, we’ll type the addition sign (+).
  5. Type the cell address of the cell you want to reference second in the formula: cell D11 in our example. A red border will appear around the referenced cell.Entering the addition operator and the reference for cell D11
  6. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell. If you select the cell again, notice that the cell displays the result, while the formula bar displays the formula.Press enter to complete the formula and display the result

If the result of a formula is too large to be displayed in a cell, it may appear as pound signs (#######) instead of a value. This means the column is not wide enough to display the cell content. Simply increase the column width to show the cell content.

Modifying values with cell references

The true advantage of cell references is that they allow you to update data in your worksheet without having to rewrite formulas. In the example below, we’ve modified the value of cell D10 from $1,200 to $1,800. The formula in D12 will automatically recalculate and display the new value in cell D12.

The formula automatically recalculates if a referenced cell is modified.

Excel will not always tell you if your formula contains an error, so it’s up to you to check all of your formulas. To learn how to do this, you can read the Double-Check Your Formulas lesson from our Excel Formulas tutorial.

To create a formula using the point-and-click method:

Instead of typing cell addresses manually, you can point and click the cells you want to include in your formula. This method can save a lot of time and effort when creating formulas. In our example below, we’ll create a formula to calculate the cost of ordering several boxes of plastic silverware.

  1. Select the cell that will contain the formula. In our example, we’ll select cell D4.Selecting cells
  2. Type the equals sign (=).
  3. Select the cell you want to reference first in the formula: cell B4 in our example. The cell address will appear in the formula.Selecting the first cell to reference in the formula
  4. Type the mathematical operator you want to use. In our example, we’ll type the multiplication sign (*).
  5. Select the cell you want to reference second in the formula: cell C4 in our example. The cell address will appear in the formula.Selecting the second cell to reference in the formula
  6. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell.Press enter to complete the formula and display the result

Copying formulas with the fill handle

Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet. The fill handle is the small square at the bottom-right corner of the selected cell(s).

  1. Select the cell containing the formula you want to copy. Click and drag the fill handle over the cells you want to fill.Drag the fill handle
  2. After you release the mouse, the formula will be copied to the selected cells.The formula is copied into the selected cells.

To edit a formula:

Sometimes you may want to modify an existing formula. In the example below, we’ve entered an incorrect cell address in our formula, so we’ll need to correct it.

  1. Select the cell containing the formula you want to edit. In our example, we’ll select cell D12.Selecting cells
  2. Click the formula bar to edit the formula. You can also double-click the cell to view and edit the formula directly within the cell.Clicking the formula bar to edit the formula
  3. border will appear around any referenced cells. In our example, we’ll change the first part of the formula to reference cell D10 instead of cell D9.Editing the formula
  4. When you’re finished, press Enter on your keyboard or select the Enter command in the formula bar.Press enter to finish editing
  5. The formula will be updated, and the new value will be displayed in the cell.The new formula displays its result

If you change your mind, you can press the Esc key on your keyboard or click the Cancel command in the formula bar to avoid accidentally making changes to your formula.

Cancel an edit to a formula

To show all of the formulas in a spreadsheet, you can hold the Ctrl key and press ` (grave accent). The grave accent key is usually located in the top-left corner of the keyboard. You can press Ctrl+` again to switch back to the normal view.

Challenge!

  1. Open our practice workbook.
  2. Click the Challenge tab in the bottom-left of the workbook.
  3. Create a formula in cell D4 that multiplies the quantity in B4 by the price per unit in cell C4.
  4. Use the fill handle to copy the formula in cell D4 to cells D5:D7.
  5. Change the price per unit for the fried plantains in cell C6 to $2.25. Notice that the line total automatically changes as well.
  6. Edit the formula for the total in cell D8 so it also adds cell D7.
  7. When you’re finished, your workbook should look like this:Intro Formulas Challenge

Lesson 14: Creating More Complex Formulas

Introduction

You may have experience working with formulas that contain only one operator, like 7+9. More complex formulas can contain several mathematical operators, like 5+2*8. When there’s more than one operation in a formula, the order of operations tells Excel which operation to calculate first. To write formulas that will give you the correct answer, you’ll need to understand the order of operations.

Optional: Download our practice workbook.

Watch the video below to learn more about complex formulas.

https://youtube.com/watch?v=J-W3thqtDpQ%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

The order of operations

Excel calculates formulas based on the following order of operations:

  1. Operations enclosed in parentheses
  2. Exponential calculations (3^2, for example)
  3. Multiplication and division, whichever comes first
  4. Addition and subtraction, whichever comes first

A mnemonic that can help you remember the order is PEMDAS, or Please Excuse MDear Aunt Sally.

Click the arrows in the slideshow below to learn how the order of operations is used to calculate formulas in Excel.

  • PEMDAS, 10+(6-3)/2^2*4-1While this formula may look complicated, we can use the order of operations step by step to find the right answer.
  • P parentheses: 10+(6-3)/2^2*4-1First, we’ll start by calculating anything inside parentheses. In this case, there’s only one thing we need to calculate: 6-3=3.
  • E exponents: 10+3/2^2*4-1As you can see, the formula already looks simpler. Next, we’ll look to see if there are any exponents. There is one: 2^2=4.
  • MD multiplication division, whichever comes first: 10+3/4*4-1Next, we’ll solve any multiplication and division, working from left to right. Because the division operation comes before the multiplication, it’s calculated first: 3/4=0.75.
  • MD multiplication division, whichever comes first: 10+0.75*4-1Now, we’ll solve our remaining multiplication operation: 0.75*4=3.
  • AS addition subtraction, whichever comes first: 10+3-1Next, we’ll calculate any addition or subtraction, again working from left to right. Addition comes first: 10+3=13.
  • AS addition subtraction, whichever comes first: 13-1Finally, we have one remaining subtraction operation: 13-1=12.
  • answer: 13-1=12Now we have our answer: 12. And this is the exact same result you would get if you entered the formula into Excel.
  • GCFLearnFree.org

Creating complex formulas

In the example below, we’ll demonstrate how Excel uses the order of operations to solve a more complex formula. Here, we want to calculate the cost of sales tax for a catering invoice. To do this, we’ll write our formula as =(D3+D4+D5)*0.075 in cell D6. This formula will add the prices of our items, then multiply that value by the 7.5% tax rate (which is written as 0.075) to calculate the answer.

Creating a complex formula

Excel follows the order of operations and first adds the values inside the parentheses: (45.80+68.70+159.60) = 274.10. It then multiplies that value by the tax rate: 274.10*0.075. The result will show that the sales tax is $20.56.

Formula result

It’s especially important to follow the order of operations when creating a formula. Otherwise, Excel won’t calculate the results accurately. In our example, if the parentheses are not included, the multiplication is calculated first and the result is incorrect. Parentheses are often the best way to define which calculations will be performed first in Excel.

Formula without parentheses

To create a complex formula using the order of operations:

In the example below, we’ll use cell references along with numerical values to create a complex formula that will calculate the subtotal for a catering invoice. The formula will calculate the cost of each menu item first, then add these values.

  1. Select the cell that will contain the formula. In our example, we’ll select cell C5.Selecting a cell
  2. Enter your formula. In our example, we’ll type =B3*C3+B4*C4. This formula will follow the order of operations, first performing the multiplication: 2.79*35 = 97.65 and 2.29*20 = 45.80. It then will add these values to calculate the total: 97.65+45.80.Creating a complex formula
  3. Double-check your formula for accuracy, then press Enter on your keyboard. The formula will calculate and display the result. In our example, the result shows that the subtotal for the order is $143.45.Formula result

You can add parentheses to any equation to make it easier to read. While it won’t change the result of the formula in this example, we could enclose the multiplication operations within parentheses to clarify that they will be calculated before the addition.

Formula with parentheses

Excel will not always tell you if your formula contains an error, so it’s up to you to check all of your formulas. To learn how to do this, you can read the Double-Check Your Formulas lesson from our Excel Formulas tutorial.

Challenge!

For this challenge, you will work with another invoice like the one in our example. In the invoice, you will find the amount of tax for the order, the order’s total, and the order’s total if you were given a 10% discount.

  1. Open our practice workbook.
  2. Click the Challenge worksheet tab in the bottom-left of the workbook.
  3. In cell D7, create a formula that calculates the tax for the invoice. Use a sales tax rate of 7.5%.
  4. In cell D8, create a formula that finds the total for the order. In other words, this formula should add cells D3:D7.
  5. In cell D9 create a formula that calculates the total after a 10% discount. If you need help understanding how to take a percentage off of a total, review our lesson on Discounts, Markdowns, and Sales.
  6. When you’re finished, your spreadsheet should look like this:Complex Formulas Challenge

Lesson 15: Relative and Absolute Cell References

Introduction

There are two types of cell references: relative and absolute. Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant no matter where they are copied.

Optional: Download our practice workbook.

Watch the video below to learn more about cell references.

https://youtube.com/watch?v=iDg9s7BJ2m4%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

Relative references

By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.

To create and copy a formula using relative references:

In the following example, we want to create a formula that will multiply each item’s price by the quantity. Instead of creating a new formula for each row, we can create a single formula in cell D4 and then copy it to the other rows. We’ll use relative references so the formula calculates the total for each item correctly.

  1. Select the cell that will contain the formula. In our example, we’ll select cell D4.Selecting a cell
  2. Enter the formula to calculate the desired value. In our example, we’ll type =B4*C4.Entering a formula
  3. Press Enter on your keyboard. The formula will be calculated, and the result will be displayed in the cell.
  4. Locate the fill handle in the bottom-right corner of the desired cell. In our example, we’ll locate the fill handle for cell D4.Locating the fill handle
  5. Click and drag the fill handle over the cells you want to fill. In our example, we’ll select cells D5:D13.Dragging the fill handle
  6. Release the mouse. The formula will be copied to the selected cells with relative references, displaying the result in each cell.Formula result

You can double-click the filled cells to check their formulas for accuracy. The relative cell references should be different for each cell, depending on their rows.

Checking a formula

Absolute references

There may be a time when you don’t want a cell reference to change when copied to other cells. Unlike relative references, absolute references do not change when copied or filled. You can use an absolute reference to keep a row and/or column constant.

An absolute reference is designated in a formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both.

$A$2, the column and the row do not change when copied; A$2, the row does not change; $A2, the column does not change

You will generally use the $A$2 format when creating formulas that contain absolute references. The other two formats are used much less frequently.

When writing a formula, you can press the F4 key on your keyboard to switch between relative and absolute cell references, as shown in the video below. This is an easy way to quickly insert an absolute reference.

https://youtube.com/watch?v=SVPlRHK-XbI%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

To create and copy a formula using absolute references:

In the example below, we’ll use cell E2 (which contains the tax rate of 7.5%) to calculate the sales tax for each item in column D. To make sure the reference to the tax rate stays constant—even when the formula is copied and filled to other cells—we’ll need to make cell $E$2 an absolute reference.

  1. Select the cell that will contain the formula. In our example, we’ll select cell D4.Selecting a cell
  2. Enter the formula to calculate the desired value. In our example, we’ll type =(B4*C4)*$E$2, making $E$2 an absolute reference.Entering a formula
  3. Press Enter on your keyboard. The formula will calculate, and the result will display in the cell.
  4. Locate the fill handle in the bottom-right corner of the desired cell. In our example, we’ll locate the fill handle for cell D4.Locating the fill handle
  5. Click and drag the fill handle over the cells you want to fill (cells D5:D13 in our example).Dragging the fill handle
  6. Release the mouse. The formula will be copied to the selected cells with an absolute reference, and the values will be calculated in each cell.Formula result

You can double-click the filled cells to check their formulas for accuracy. The absolute reference should be the same for each cell, while the other references are relative to the cell’s row.

Checking the formula

Be sure to include the dollar sign ($) whenever you’re making an absolute reference across multiple cells. The dollar signs were omitted in the example below. This caused Excel to interpret it as a relative reference, producing an incorrect result when copied to other cells.

Incorrectly copied formula

Using cell references with multiple worksheets

Excel allows you to refer to any cell on any worksheet, which can be especially helpful if you want to reference a specific value from one worksheet to another. To do this, you’ll simply need to begin the cell reference with the worksheet name followed by an exclamation point (!). For example, if you wanted to reference cell A1 on Sheet1, its cell reference would be Sheet1!A1.

Note that if a worksheet name contains a space, you’ll need to include single quotation marks (‘ ‘) around the name. For example, if you wanted to reference cell A1 on a worksheet named July Budget, its cell reference would be ‘July Budget’!A1.

To reference cells across worksheets:

In our example below, we’ll refer to a cell with a calculated value between two worksheets. This will allow us to use the exact same value on two different worksheets without rewriting the formula or copying data.

  1. Locate the cell you want to reference, and note its worksheet. In our example, we want to reference cell E14 on the Menu Order worksheet.Locating a cell
  2. Navigate to the desired worksheet. In our example, we’ll select the Catering Invoice worksheet.Selecting a worksheet
  3. Locate and select the cell where you want the value to appear. In our example, we’ll select cell C4.Selecting a cell
  4. Type the equals sign (=), the sheet name followed by an exclamation point (!), and the cell address. In our example, we’ll type =’Menu Order’!E14.Entering a formula
  5. Press Enter on your keyboard. The value of the referenced cell will appear. Now, if the value of cell E14 changes on the Menu Order worksheet, it will be updated automatically on the Catering Invoice worksheet.Formula result

If you rename your worksheet at a later point, the cell reference will be updated automatically to reflect the new worksheet name.

If you enter a worksheet name incorrectly, the #REF! error will appear in the cell. In our example below, we’ve mistyped the name of the worksheet. To edit, ignore, or investigate the error, click the Error button beside the cell and choose an option from the menu.

Correcting an error

Challenge!

  1. Open our practice workbook.
  2. Click the Paper Goods tab in the bottom-left of the workbook.
  3. In cell D4, enter a formula that multiplies the unit price in B4, the quantity in C4, and the tax rate in E2. Make sure to use an absolute cell reference for the tax rate because it will be the same in every cell.
  4. Use the fill handle to copy the formula you just created to cells D5:D12.
  5. Change the tax rate in cell E2 to 6.5%. Notice that all of your cells have updated. When you’re finished, your workbook should look like this:Paper Goods Tab
  6. Click the Catering Invoice tab.
  7. Delete the value in cell C5 and replace it with a reference to the total cost of the paper goods. Hint: The cost of the paper goods is in cell E13 on the Paper Goods worksheet.
  8. Use the same steps from above to calculate the sales tax for each item on the Menu Order worksheet. The total cost in cell E14 should update. Then, in cell C4 of the Catering Invoice worksheet, create a cell reference to the total you just calculated. Note: If you used our practice workbook to follow along during the lesson, you may have already completed this step.
  9. When you’re finished, the Catering Invoice worksheet should look something like this:cell_references_challenge

Lesson 16: Functions

Introduction

function is a predefined formula that performs calculations using specific values in a particular order. Excel includes many common functions that can be used to quickly find the sumaveragecountmaximum 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.

Optional: Download our practice workbook.

Watch the video below to learn more about working with functions.

https://youtube.com/watch?v=-9d4m79twdA%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

The parts of a function

In order to work correctly, a function must be written a specific way, which is called the syntax. The basic syntax for a function is the equals sign (=), the function name (SUM, for example), and one or more arguments. Arguments contain the information you want to calculate. The function in the example below would add the values of the cell range A1:A20.

=SUM(A1:A20)

Working with arguments

Arguments can refer to both individual cells and cell ranges and must be enclosed within parentheses. You can include one argument or multiple arguments, depending on the syntax required for the function.

For example, the function =AVERAGE(B1:B9) would calculate the average of the values in the cell range B1:B9. This function contains only one argument.

Function with single argument

Multiple arguments must be separated by a comma. For example, the function =SUM(A1:A3, C1:C2, E1) will add the values of all of the cells in the three arguments.

Function with multiple arguments

Creating a function

There are a variety of functions available in Excel. 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.
  • MAX: This function determines the highest cell value included in the argument.
  • MIN: This function determines the lowest cell value included in the argument.

To create a function using the AutoSum command:

The AutoSum command allows you to automatically insert the most common functions into your formula, including SUM, AVERAGE, COUNT, MAX, and MIN. In the example below, we’ll use the SUM function to calculate the total cost for a list of recently ordered items.

  1. Select the cell that will contain the function. In our example, we’ll select cell D13.Selecting a cell
  2. In the Editing group on the Home tab, click the arrow next to the AutoSum command. Next, choose the desired function from the drop-down menu. In our example, we’ll select Sum.Choosing a function
  3. Excel will place the function in the cell and automatically select a cell range for the argument. In our example, cells D3:D12 were selected automatically; their values will be added to calculate the total cost. If Excel selects the wrong cell range, you can manually enter the desired cells into the argument.Function argument
  4. Press Enter on your keyboard. The function will be calculated, and the result will appear in the cell. In our example, the sum of D3:D12 is $765.29.Function result

The AutoSum command can also be accessed from the Formulas tab on the Ribbon.

AutoSum command on Formulas tab

You can also use the Alt+= keyboard shortcut instead of the AutoSum command. To use this shortcut, hold down the Alt key and then press the equals sign.

Watch the video below to see this shortcut in action.

https://youtube.com/watch?v=4MxeY2m-hcI%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

To enter a function manually:

If you already know the function name, you can easily type it yourself. In the example below (a tally of cookie sales), we’ll use the AVERAGE function to calculate the average number of units sold by each troop.

  1. Select the cell that will contain the function. In our example, we’ll select cell C10.Selecting a cell
  2. Type the equals sign (=), then enter the desired function name. You can also select the desired function from the list of suggested functions that appears below the cell as you type. In our example, we’ll type =AVERAGE.Entering the function name
  3. Enter the cell range for the argument inside parentheses. In our example, we’ll type (C3:C9). This formula will add the values of cells C3:C9, then divide that value by the total number of values in the range.Entering the argument
  4. Press Enter on your keyboard. The function will be calculated, and the result will appear in the cell. In our example, the average number of units sold by each troop is 849.Function result

Excel will not always tell you if your formula contains an error, so it’s up to you to check all of your formulas. To learn how to do this, read the Double-Check Your Formulas lesson from our Excel Formulas tutorial.

The Function Library

While there are hundreds of functions in Excel, the ones you’ll use the most will depend on the type of data your workbooks contain. There’s no need to learn every single function, but exploring some of the different types of functions will help as you create new projects. You can even use the Function Library on the Formulas tab to browse functions by category, including FinancialLogicalText, and Date & Time.

To access the Function Library, select the Formulas tab on the Ribbon. Look for the Function Library group.

Click the buttons in the interactive below to learn more about the different types of functions in Excel.

Functions Library interactive

To insert a function from the Function Library:

In the example below, we’ll use the COUNTA function to count the total number of items in the Items column. Unlike COUNT, COUNTA can be used to tally cells that contain data of any kind, not just numerical data.

  1. Select the cell that will contain the function. In our example, we’ll select cell B17.Selecting a cell
  2. Click the Formulas tab on the Ribbon to access the Function Library.
  3. From the Function Library group, select the desired function category. In our example, we’ll choose More Functions, then hover the mouse over Statistical.Selecting the More Functions / Statistical command
  4. Select the desired function from the drop-down menu. In our example, we’ll select the COUNTA function, which will count the number of cells in the Items column that are not empty.Choosing the COUNTA function
  5. The Function Arguments dialog box will appear. Select the Value1 field, then enter or select the desired cells. In our example, we’ll enter the cell range A3:A12. You can continue to add arguments in the Value2 field, but in this case we only want to count the number of cells in the cell range A3:A12.
  6. When you’re satisfied, click OK.Function argument
  7. The function will be calculated, and the result will appear in the cell. In our example, the result shows that 10 items were ordered.Function result

The Insert Function command

While the Function Library is a great place to browse for functions, sometimes you may prefer to search for one instead. You can do so using the Insert Function command. It may take some trial and error depending on the type of function you’re looking for, but with practice the Insert Function command can be a powerful way to find a function quickly.

To use the Insert Function command:

In the example below, we want to find a function that will calculate the number of business days it took to receive items after they were ordered. We’ll use the dates in columns E and F to calculate the delivery time in column G.

  1. Select the cell that will contain the function. In our example, we’ll select cell G3.Selecting a cell
  2. Click the Formulas tab on the Ribbon, then click the Insert Function command.Insert Function command
  3. The Insert Function dialog box will appear.
  4. Type a few keywords describing the calculation you want the function to perform, then click Go. In our example, we’ll type count days, but you can also search by selecting a category from the drop-down list.Searching for a function
  5. Review the results to find the desired function, then click OK. In our example, we’ll choose NETWORKDAYS, which will count the number of business days between the ordered date and received date.Selecting a function
  6. The Function Arguments dialog box will appear. From here, you’ll be able to enter or select the cells that will make up the arguments in the function. In our example, we’ll enter E3 in the Start_date field and F3 in the End_date field.
  7. When you’re satisfied, click OK.Function argument
  8. The function will be calculated, and the result will appear in the cell. In our example, the result shows that it took four business days to receive the order.Function result

Like formulas, functions can be copied to adjacent cells. Simply select the cell that contains the function, then click and drag the fill handle over the cells you want to fill. The function will be copied, and values for those cells will be calculated relative to their rows or columns.

Using the fill handle

To learn more:

If you’re comfortable with basic functions, you may want to try a more advanced one like VLOOKUP. Review our lesson on How to Use Excel’s VLOOKUP Function for more information.

To learn even more about working with functions, visit our Excel Formulas tutorial.

Challenge!

  1. Open our practice workbook.
  2. Click the Challenge tab in the bottom-left of the workbook.
  3. In cell F3, insert a function to calculate the average of the four scores in cells B3:E3.
  4. Use the fill handle to copy your function in cell F3 to cells F4:F17.
  5. In cell B18, use the AutoSum command to insert a function that calculates the lowest score in cells B3:B17.
  6. In cell B19, use the Function Library to insert a function that calculates the median of the scores in cells B3:B17Hint: You can find the median function by going to More Functions > Statistical.
  7. In cell B20, create a function to calculate the highest score in cells B3:B17.
  8. Select cells B18:B20, then use the fill handle to copy all three functions you just created to cells C18:F20.
  9. When you’re finished, your workbook should look like this:Functions Challenge

Lesson 17: Basic Tips for Working with Data

Introduction

Excel workbooks are designed to store a lot of information. Whether you’re working with 20 cells or 20,000, Excel has several features to help you organize your data and find what you need. You can see some of the most useful features below. And be sure to review the other lessons in this tutorial to get step-by-step instructions for each of these features.

Freezing rows and columns

You may want to see certain rows or columns all the time in your worksheet, especially header cells. By freezing rows or columns in place, you’ll be able to scroll through your content while continuing to view the frozen cells. In this example, we’ve frozen the top two rows, which allows us to view the dates no matter where we scroll in the spreadsheet.

freezing rows

Sorting data

You can quickly reorganize a worksheet by sorting your data. Content can be sorted alphabetically, numerically, and in several other ways. For example, you can organize a list of contact information by last name.

sorting a worksheet alphabetically

Filtering data

Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need. In this example, we’re filtering the worksheet to show only rows that contain the words Laptop or Projector in column B.

applying a filter

Summarizing data

The Subtotal command allows you to quickly summarize data. In our example, we’ve created a subtotal for each T-shirt size, which makes it easy to see how many we’ll need in each size.

subtotals and groups

Formatting data as a table

Just like regular formatting, tables can improve the look and feel of your workbook, but they’ll also help organize your content and make your data easier to use. For example, tables have built-in sorting and filtering options. Excel also includes several predefined table styles, allowing you to create tables quickly.

data formatted as a table in Excel

Visualizing data with charts

It can be difficult to interpret Excel workbooks that contain a lot of data. Charts allow you to illustrate your workbook data graphically, which makes it easy to visualize comparisons and trends.

a chart inserted into a worksheet

Adding conditional formatting

Let’s say you have a worksheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw information. Conditional formatting allows you to automatically apply cell formatting—including colorsicons, and data bars—to one or more cells based on the cell value.

conditional formatting applied to a cell range

Using Find and Replace

When working with a lot of data, it can be difficult and time consuming to locate specific information. You can easily search your workbook using the Find feature, which also allows you to modify content using the Replace feature.

using the find and replace feature

Lesson 18: Freezing Panes and View Options

Introduction

Whenever you’re working with a lot of data, it can be difficult to compare information in your workbook. Fortunately, Excel includes several tools that make it easier to view content from different parts of your workbook at the same time, including the ability to freeze panes and split your worksheet.

Optional: Download our practice workbook.

Watch the video below to learn more about freezing panes in Excel.

https://youtube.com/watch?v=zuhsUNBeNHw%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

To freeze rows:

You may want to see certain rows or columns all the time in your worksheet, especially header cells. By freezing rows or columns in place, you’ll be able to scroll through your content while continuing to view the frozen cells.

  1. Select the row below the row(s) you want to freeze. In our example, we want to freeze rows 1 and 2, so we’ll select row 3.Selecting cells
  2. On the View tab, select the Freeze Panes command, then choose Freeze Panes from the drop-down menu.The Freeze Rows dropdown menu
  3. The rows will be frozen in place, as indicated by the gray line. You can scroll down the worksheet while continuing to view the frozen rows at the top. In our example, we’ve scrolled down to row 18.The top two rows are now frozen data

To freeze columns:

  1. Select the column to the right of the column(s) you want to freeze. In our example, we want to freeze column A, so we’ll select column B.selecting column B
  2. On the View tab, select the Freeze Panes command, then choose Freeze Panes from the drop-down menu./content/561fbc87287fc10e146dbf44_10_15_2015/freeze-rows-dropdown-menu.png
  3. The column will be frozen in place, as indicated by the gray line. You can scroll across the worksheet while continuing to view the frozen column on the left. In our example, we’ve scrolled across to column E.The first column is now frozen

If you only need to freeze the top row (row 1) or first column (column A) in the worksheet, you can simply select Freeze Top Row or Freeze First Column from the drop-down menu.

The Freeze Top Row command

To unfreeze panes:

If you want to select a different view option, you may first need to reset the spreadsheet by unfreezing panes. To unfreeze rows or columns, click the Freeze Panes command, then select Unfreeze Panes from the drop-down menu.

The Unfreeze Panes command

Other view options

If your workbook contains a lot of content, it can sometimes be difficult to compare different sections. Excel includes additional options to make your workbooks easier to view and compare. For example, you can choose to open a new window for your workbook or split a worksheet into separate panes.

To open a new window for the current workbook:

Excel allows you to open multiple windows for a single workbook at the same time. In our example, we’ll use this feature to compare two different worksheets from the same workbook.

  1. Click the View tab on the Ribbon, then select the New Window command.The New Window button on the View tab
  2. new window for the workbook will appear.Two windows for the same workbook
  3. You can now compare different worksheets from the same workbook across windows. In our example, we’ll select the 2013 Sales Detailed View worksheet to compare 2012 and 2013 sales.You can compare different parts of the same workbook

If you have several windows open at the same time, you can use the Arrange All command to rearrange them quickly.

The Arrange All button on the View tab

To split a worksheet:

Sometimes you may want to compare different sections of the same workbook without creating a new window. The Split command allows you to divide the worksheet into multiple panes that scroll separately.

  1. Select the cell where you want to split the worksheet. In our example, we’ll select cell D6.Selecting cells
  2. Click the View tab on the Ribbon, then select the Split command.The Split command on the View tab
  3. The workbook will be split into different panes. You can scroll through each pane separately using the scroll bars, allowing you to compare different sections of the workbook.Scrolling through split panes
  4. After creating a split, you can click and drag the vertical and horizontal dividers to change the size of each section.

To remove the split, click the Split command again.

Challenge!

Within our example file, there is A LOT of sales data. For this challenge, we want to be able to compare data for different years side by side. To do this:

  1. Open our practice workbook.
  2. Open a new window for your workbook.
  3. Freeze First Column and use the horizontal scroll bar to look at sales from 2015.
  4. Unfreeze the first column.
  5. Select cell G17 and click Split to split the worksheet into multiple panes. Hint: This should split the worksheet between rows 16 and 17 and columns F and G.
  6. Use the horizontal scroll bar in the bottom-right of the window to move the worksheet so Column N, which contains data for January 2015, is next to Column F.
  7. Open a new window for your workbook and select the 2012-2013 Sales tab.
  8. Move your windows so they are side by side. Now you’re able to compare data for similar months from several different years. Your screen should look something like this:Freezing Panes Challenge

Lesson 19: Sorting Data

Introduction

As you add more content to a worksheet, organizing this information becomes especially important. You can quickly reorganize a worksheet by sorting your data. For example, you can organize a list of contact information by last name. Content can be sorted alphabetically, numerically, and in several other ways.

Optional: Download our practice workbook.

Watch the video below to learn more about sorting data in Excel.

https://youtube.com/watch?v=Ep5q1cUhQas%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

Types of sorting

When sorting data, it’s important to first decide if you want the sort to apply to the entire worksheet or just a cell range.

  • Sort sheet organizes all of the data in your worksheet by one column. Related information across each row is kept together when the sort is applied. In the example below, the Contact Name column (column A) has been sorted to display the names in alphabetical order.A sorted worksheet
  • Sort range sorts the data in a range of cells, which can be helpful when working with a sheet that contains several tables. Sorting a range will not affect other content in the worksheet.Sorting a range

To sort a sheet:

In our example, we’ll sort a T-shirt order form alphabetically by Last Name (column C).

  1. Select a cell in the column you want to sort. In our example, we’ll select cell C2.selecting cell C2
  2. Select the Data tab on the Ribbon, then click the A-Z command to sort A to Z, or the Z-A command to sort Z to A. In our example, we’ll sort A to Z.sorting A to Z
  3. The worksheet will be sorted by the selected column. In our example, the worksheet is now sorted by last name.a spreadsheet sorted by last name

To sort a range:

In our example, we’ll select a separate table in our T-shirt order form to sort the number of shirts that were ordered in each grade.

  1. Select the cell range you want to sort. In our example, we’ll select cell range G2:H6.Selecting cells
  2. Select the Data tab on the Ribbon, then click the Sort command.The Sort button on the Data tab
  3. The Sort dialog box will appear. Choose the column you want to sort. In our example, we want to sort the data by the number of T-shirt orders, so we’ll select Orders.The Sort dialog box
  4. Decide the sorting order (either ascending or descending). In our example, we’ll use Largest to Smallest.
  5. Once you’re satisfied with your selection, click OK.The Sort dialog box
  6. The cell range will be sorted by the selected column. In our example, the Orders column will be sorted from highest to lowest. Notice that the other content in the worksheet was not affected by the sort.The range has been sorted

If your data isn’t sorting properly, double-check your cell values to make sure they are entered into the worksheet correctly. Even a small typo could cause problems when sorting a large worksheet. In the example below, we forgot to include a hyphen in cell A18, causing our sort to be slightly inaccurate.

A typo can ruin a sort

Custom sorting

Sometimes you may find that the default sorting options can’t sort data in the order you need. Fortunately, Excel allows you to create a custom list to define your own sorting order.

To create a custom sort:

In our example, we want to sort the worksheet by T-Shirt Size (column D). A regular sort would organize the sizes alphabetically, which would be incorrect. Instead, we’ll create a custom list to sort from smallest to largest.

  1. Select a cell in the column you want to sort. In our example, we’ll select cell D2.Selecting cells
  2. Select the Data tab, then click the Sort command.The Sort button on the Data tab
  3. The Sort dialog box will appear. Select the column you want to sort, then choose Custom List… from the Order field. In our example, we will choose to sort by T-Shirt Size.The Sort dialog box
  4. The Custom Lists dialog box will appear. Select NEW LIST from the Custom Lists: box.
  5. Type the items in the desired custom order in the List entries: box. In our example, we want to sort our data by T-shirt size from smallest to largest, so we’ll type Small, Medium, Large, and X-Large, pressing Enter on the keyboard after each item.The Custom Lists dialog box
  6. Click Add to save the new sort order. The new list will be added to the Custom lists: box. Make sure the new list is selected, then click OK.Clicking OK
  7. The Custom Lists dialog box will close. Click OK in the Sort dialog box to perform the custom sort.Clicking OK
  8. The worksheet will be sorted by the custom order. In our example, the worksheet is now organized by T-shirt size from smallest to largest.The sheet has now been sorted with our custom criteria.

Sorting levels

If you need more control over how your data is sorted, you can add multiple levels to any sort. This allows you to sort your data by more than one column.

To add a level:

In our example below, we’ll sort the worksheet by T-Shirt Size (Column D), then by Homeroom # (column A).

  1. Select a cell in the column you want to sort. In our example, we’ll select cell A2.Select cells
  2. Click the Data tab, then select the Sort command.The Sort button on the Data tab
  3. The Sort dialog box will appear. Select the first column you want to sort. In this example, we will sort by T-Shirt Size (column D) with the custom list we previously created for the Order field.
  4. Click Add Level to add another column to sort.The Sort dialog box
  5. Select the next column you want to sort, then click OK. In our example, we’ll sort by Homeroom # (column A).The Sort dialog box
  6. The worksheet will be sorted according to the selected order. In our example, the orders are sorted by T-shirt size. Within each group of T-shirt sizes, students are sorted by homeroom number.The multi-level sort is applied to the worksheet

If you need to change the order of a multilevel sort, it’s easy to control which column is sorted first. Simply select the desired column, then click the Move Up or Move Down arrow to adjust its priority.

Move levels up or down

Challenge!

  1. Open our practice workbook.
  2. Click the Challenge tab in the bottom-left of the workbook.
  3. For the main table, create a custom sort that sorts by Grade from Smallest to Largest and then by Camper Name from A to Z.
  4. Create a sort for the Additional Information section. Sort by Counselor (Column H) from A to Z.
  5. When you’re finished, your workbook should look like this:Sorting Challenge

Lesson 20: Filtering Data

Introduction

If your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need.

Optional: Download our practice workbook.

Watch the video below to learn more about filtering data in Excel.

https://youtube.com/watch?v=_OdsZR_rL1U%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

To filter data:

In our example, we’ll apply a filter to an equipment log worksheet to display only the laptops and projectors that are available for checkout.

  1. In order for filtering to work correctly, your worksheet should include a header row, which is used to identify the name of each column. In our example, our worksheet is organized into different columns identified by the header cells in row 1: ID#TypeEquipment Detail, and so on.The header row in a spreadsheet
  2. Select the Data tab, then click the Filter command.The Filter button on the Data tab
  3. A drop-down arrow will appear in the header cell for each column.
  4. Click the drop-down arrow for the column you want to filter. In our example, we will filter column B to view only certain types of equipment.Dropdown arrows in the header row
  5. The Filter menu will appear.
  6. Uncheck the box next to Select All to quickly deselect all data.The dropdown menu for sorting and filtering
  7. Check the boxes next to the data you want to filter, then click OK. In this example, we will check Laptop and Projector to view only these types of equipment.Selecting filter categories
  8. The data will be filtered, temporarily hiding any content that doesn’t match the criteria. In our example, only laptops and projectors are visible.The worksheet is now filtered

Filtering options can also be accessed from the Sort & Filter command on the Home tab.

The Filter menu on the Home tab

To apply multiple filters:

Filters are cumulative, which means you can apply multiple filters to help narrow down your results. In this example, we’ve already filtered our worksheet to show laptops and projectors, and we’d like to narrow it down further to only show laptops and projectors that were checked out in August.

  1. Click the drop-down arrow for the column you want to filter. In this example, we will add a filter to column to view information by date.Dropdown arrows in the header row
  2. The Filter menu will appear.
  3. Check or uncheck the boxes depending on the data you want to filter, then click OK. In our example, we’ll uncheck everything except for August.The dropdown menu for sorting and filtering
  4. The new filter will be applied. In our example, the worksheet is now filtered to show only laptops and projectors that were checked out in August.The worksheet now has two filters

To clear a filter:

After applying a filter, you may want to remove—or clear—it from your worksheet so you’ll be able to filter content in different ways.

  1. Click the drop-down arrow for the filter you want to clear. In our example, we’ll clear the filter in column D.The dropdown button for the filter applied in this column
  2. The Filter menu will appear.
  3. Choose Clear Filter From [COLUMN NAME] from the Filter menu. In our example, we’ll select Clear Filter From “Checked Out“.The Clear Filter menu option
  4. The filter will be cleared from the column. The previously hidden data will be displayed.The filter has been cleared

To remove all filters from your worksheet, click the Filter command on the Data tab.

The Filter button on the Data tab

Advanced filtering

If you need a filter for something specific, basic filtering may not give you enough options. Fortunately, Excel includes several advanced filtering tools, including searchtextdate, and number filtering, which can narrow your results to help find exactly what you need.

To filter with search:

Excel allows you to search for data that contains an exact phrase, number, date, and more. In our example, we’ll use this feature to show only Saris brand products in our equipment log.

  1. Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you’ve already added filters to your worksheet, you can skip this step.
  2. Click the drop-down arrow for the column you want to filter. In our example, we’ll filter column C.Dropdown arrows in the header row
  3. The Filter menu will appear. Enter a search term into the search box. Search results will appear automatically below the Text Filters field as you type. In our example, we’ll type saris to find all Saris brand equipment. When you’re done, click OK.Applying a search filter
  4. The worksheet will be filtered according to your search term. In our example, the worksheet is now filtered to show only Saris brand equipment.The search filter has been applied to the worksheet

To use advanced text filters:

Advanced text filters can be used to display more specific information, like cells that contain a certain number of characters or data that excludes a specific word or number. In our example, we’d like to exclude any item containing the word laptop.

  1. Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you’ve already added filters to your worksheet, you can skip this step.
  2. Click the drop-down arrow for the column you want to filter. In our example, we’ll filter column C.Dropdown arrows in the header row
  3. The Filter menu will appear. Hover the mouse over Text Filters, then select the desired text filter from the drop-down menu. In our example, we’ll choose Does Not Contain… to view data that does not contain specific text.Adding a custom filter in the dropdown menu
  4. The Custom AutoFilter dialog box will appear. Enter the desired text to the right of the filter, then click OK. In our example, we’ll type laptop to exclude any items containing this word.The custom filter dialog box
  5. The data will be filtered by the selected text filter. In our example, our worksheet now displays items that do not contain the word laptop.The custom filter has been applied to the worksheet

To use advanced number filters:

Advanced number filters allow you to manipulate numbered data in different ways. In this example, we’ll display only certain types of equipment based on the range of ID numbers.

  1. Select the Data tab on the Ribbon, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you’ve already added filters to your worksheet, you can skip this step.
  2. Click the drop-down arrow for the column you want to filter. In our example, we’ll filter column A to view only a certain range of ID numbers.Dropdown arrows in the header row
  3. The Filter menu will appear. Hover the mouse over Number Filters, then select the desired number filter from the drop-down menu. In our example, we’ll choose Between to view ID numbers between a specific number range.Adding a number filter in the dropdown menu
  4. The Custom AutoFilter dialog box will appear. Enter the desired number(s) to the right of each filter, then click OK. In our example, we want to filter for ID numbers greater than or equal to 3000 but less than or equal to 6000, which will display ID numbers in the 3000-6000 range.The number filter dialog box
  5. The data will be filtered by the selected number filter. In our example, only items with an ID number between 3000 and 6000 are visible.The number filter has been applied to the worksheet

To use advanced date filters:

Advanced date filters can be used to view information from a certain time period, such as last year, next quarter, or between two dates. In this example, we’ll use advanced date filters to view only equipment that has been checked out between July 15 and August 15.

  1. Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you’ve already added filters to your worksheet, you can skip this step.
  2. Click the drop-down arrow for the column you want to filter. In our example, we’ll filter column D to view only a certain range of dates.Dropdown arrows in the header row
  3. The Filter menu will appear. Hover the mouse over Date Filters, then select the desired date filter from the drop-down menu. In our example, we’ll select Between… to view equipment that has been checked out between July 15 and August 15.Adding a date filter in the dropdown menu
  4. The Custom AutoFilter dialog box will appear. Enter the desired date(s) to the right of each filter, then click OK. In our example, we want to filter for dates after or equal to July 15, 2015, and before or equal to August 15, 2015, which will display a range between these dates.The date filter dialog box
  5. The worksheet will be filtered by the selected date filter. In our example, we can now see which items have been checked out between July 15 and August 15.The date filter has been applied to the worksheet

Challenge!

  1. Open our practice workbook.
  2. Click the Challenge tab in the bottom-left of the workbook.
  3. Apply a filter to show only Electronics and Instruments.
  4. Use the Search feature to filter item descriptions that contain the word Sansei. After you do this, you should have six entries showing.
  5. Clear the Item Description filter.
  6. Using a number filter, show loan amounts greater than or equal to $100.
  7. Filter to show only items that have deadlines in 2016.
  8. When you’re finished, your workbook should look like this:Filtering Challenge

Lesson 21: Groups and Subtotals

Introduction

Worksheets with a lot of content can sometimes feel overwhelming and can even become difficult to read. Fortunately, Excel can organize data into groups, allowing you to easily show and hide different sections of your worksheet. You can also summarize different groups using the Subtotal command and create an outline for your worksheet.

Optional: Download our practice workbook.

Watch the video below to learn more about groups and subtotals in Excel.

https://youtube.com/watch?v=7JJOBFSHbZk%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

To group rows or columns:

  1. Select the rows or columns you want to group. In this example, we’ll select columns BC, and D.Selecting cells
  2. Select the Data tab on the Ribbon, then click the Group command.The Group button on the Data tab
  3. The selected rows or columns will be grouped. In our example, columns BC, and D are grouped.The selected cells are now grouped

To ungroup data, select the grouped rows or columns, then click the Ungroup command.

The Ungroup button on the Data tab

To hide and show groups:

  1. To hide a group, click the minus sign, also known as the Hide Detail button.The Hide Detail button
  2. The group will be hidden. To show a hidden group, click the plus sign, also known as the Show Detail button.The Show Detail button

Creating subtotals

The Subtotal command allows you to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help summarize your data. For example, the Subtotal command could help to calculate the cost of office supplies by type from a large inventory order. It will create a hierarchy of groups, known as an outline, to help organize your worksheet.

Your data must be correctly sorted before using the Subtotal command, so you may want to review our lesson on Sorting Data to learn more.

To create a subtotal:

In our example, we’ll use the Subtotal command with a T-shirt order form to determine how many T-shirts were ordered in each size (Small, Medium, Large, and X-Large). This will create an outline for our worksheet with a group for each T-shirt size and then count the total number of shirts in each group.

  1. First, sort your worksheet by the data you want to subtotal. In this example, we’ll create a subtotal for each T-shirt size, so our worksheet has been sorted by T-shirt size from smallest to largest.Selecting cells
  2. Select the Data tab, then click the Subtotal command.The Subtotal button on the Data tab
  3. The Subtotal dialog box will appear. Click the drop-down arrow for the At each change in: field to select the column you want to subtotal. In our example, we’ll select T-Shirt Size.
  4. Click the drop-down arrow for the Use function: field to select the function you want to use. In our example, we’ll select COUNT to count the number of shirts ordered in each size.selecting the Count option
  5. In the Add subtotal to: field, select the column where you want the calculated subtotal to appear. In our example, we’ll select T-Shirt Size. When you’re satisfied with your selections, click OK.adding subtotals to T-shirt Size
  6. The worksheet will be outlined into groups, and the subtotal will be listed below each group. In our example, the data is now grouped by T-shirt size, and the number of shirts ordered in that size appears below each group.Subtotal rows and groups have been applied to the worksheet

To view groups by level:

When you create subtotals, your worksheet it is divided into different levels. You can switch among these levels to quickly control how much information is displayed in the worksheet by clicking the Level buttons to the left of the worksheet. In our example, we’ll switch among all three levels in our outline. While this example contains only three levels, Excel can accommodate up to eight.

  1. Click the lowest level to display the least detail. In our example, we’ll select level 1, which contains only the Grand Count, or total number of T-shirts ordered.Setting the group detail to level 1
  2. Click the next level to expand the detail. In our example, we’ll select level 2, which contains each subtotal row but hides all other data from the worksheet.Setting the group detail to level 2
  3. Click the highest level to view and expand all of your worksheet data. In our example, we’ll select level 3.Setting the group detail to level 3

You can also use the Show Detail and Hide Detail buttons to show and hide the groups within the outline.

Using the Show and Hide Detail buttons in a subtotal

To remove subtotals:

Sometimes you may not want to keep subtotals in your worksheet, especially if you want to reorganize data in different ways. If you no longer want to use subtotaling, you’ll need remove it from your worksheet.

  1. Select the Data tab, then click the Subtotal command.The Subtotal button on the Data tab
  2. The Subtotal dialog box will appear. Click Remove All.Removing subtotals in the Subtotal dialog
  3. All worksheet data will be ungrouped, and the subtotals will be removed.

To remove all groups without deleting the subtotals, click the Ungroup command drop-down arrow, then choose Clear Outline.

Clear Outline in the Ungroup menu on the Data tab

Challenge!

  1. Open our practice workbook.
  2. Click the Challenge tab in the bottom-left of the workbook.
  3. Sort the workbook by Grade from smallest to largest.
  4. Use the Subtotal command to group at each change in Grade. Use the SUM function and add subtotals to Amount Raised.
  5. Select level 2 so you only see the subtotals and grand total.
  6. When you’re finished, your workbook should look like this:Grouping and Subtotals Challenge

Lesson 22: Tables

Introduction

Once you’ve entered information into your worksheet, you may want to format your data as a table. Just like regular formatting, tables can improve the look and feel of your workbook, and they’ll also help organize your content and make your data easier to use. Excel includes several tools and predefined table styles, allowing you to create tables quickly and easily.

Optional: Download our practice workbook.

Watch the video below to learn more about working with tables.

https://youtube.com/watch?v=iroIA8_3soo%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

To format data as a table:

  1. Select the cells you want to format as a table. In our example, we’ll select the cell range A2:D9.selecting a range
  2. From the Home tab, click the Format as Table command in the Styles group.format as table command
  3. Select a table style from the drop-down menu.choosing a table style
  4. A dialog box will appear, confirming the selected cell range for the table.
  5. If your table has headers, check the box next to My table has headers, then click OK.confirmating cell range
  6. The cell range will be formatted in the selected table style.table

Tables include filtering by default. You can filter your data at any time using the drop-down arrows in the header cells. To learn more, review our lesson on Filtering Data.

Modifying tables

It’s easy to modify the look and feel of any table after adding it to a worksheet. Excel includes several options for customizing tables, including adding rows or columns and changing the table style.

To add rows or columns to a table:

If you need to fit more content into your table, you can modify the table size by including additional rows and columns. There are two simple ways to change the table size:

  • Enter new content into any adjacent row or column. The row or column will be roped into the table automatically.adding a row
  • Click and drag the bottom-right corner of the table to create additional rows or columns.adding several rows

To change the table style:

  1. Select any cell in your table, then click the Design tab.navigating to the design tab
  2. Locate the Table Styles group, then click the More drop-down arrow to see all available table styles.more tables styles
  3. Select the desired table style.choosing a table style
  4. The table style will be applied.table

To modify table style options:

You can turn various options on or off to change the appearance of any table. There are several options: Header RowTotal RowBanded RowsFirst ColumnLast ColumnBanded Columns, and Filter Button.

  1. Select any cell in your table, then click the Design tab.
  2. Check or uncheck the desired options in the Table Style Options group. In our example, we’ll check Total Row to automatically include a total for our table.table style options
  3. The table style will be modified. In our example, a new row has been added to the table with a formula that automatically calculates the total value of the cells in column D.total row

Depending on the type of content you have—and the table style you’ve chosen—these options can affect your table’s appearance in various ways. You may need to experiment with a few options to find the exact style you want.

To remove a table:

It’s possible to remove a table from your workbook without losing any of your data. However, this can cause issues with certain types of formatting, including colors, fonts, and banded rows. Before using this option, be prepared to reformat your cells if necessary.

  1. Select any cell in your table, then click the Design tab.
  2. Click the Convert to Range command in the Tools group.convert to range command
  3. A dialog box will appear. Click Yes.convert to range confirmation
  4. The range will no longer be a table, but the cells will retain their data and formatting.converted table

To restart your formatting from scratch, click the Clear command on the Home tab. Next, choose Clear Formats from the menu.

clear command

Challenge!

  1. Open our practice workbook.
  2. Click the Challenge tab in the bottom-left of the workbook.
  3. Select cells A2:D9 and format as a table. Choose one of the light styles.
  4. Insert a row between rows 4 and 5. In the row you just created, type Empanadas: Banana and Nutella, with a unit price of $3.25 and a quantity of 12.
  5. Change the table style to Table Style Medium 10.
  6. In Table Style Options, uncheck banded rows and check banded columns.
  7. When you’re finished, your workbook should look like this:Tables Challenge

Lesson 23: Charts

Introduction

It can be difficult to interpret Excel workbooks that contain a lot of data. Charts allow you to illustrate your workbook data graphically, which makes it easy to visualize comparisons and trends.

Optional: Download our practice workbook.

Watch the video below to learn more about charts.

https://youtube.com/watch?v=_Wu7jYTr1Pk%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

Understanding charts

Excel has several types of charts, allowing you to choose the one that best fits your data. To use charts effectively, you’ll need to understand how different charts are used.

Click the arrows in the slideshow below to learn more about the types of charts in Excel.

  • Slide 1Excel has a variety of chart types, each with its own advantages. Click the arrows to see some of the different types of charts available in Excel.
  • Slide 1Column charts use vertical bars to represent data. They can work with many different types of data, but they’re most frequently used for comparing information.
  • Slide 1Line charts are ideal for showing trends. The data points are connected with lines, making it easy to see whether values are increasing or decreasing over time.
  • Slide 1Pie charts make it easy to compare proportions. Each value is shown as a slice of the pie, so it’s easy to see which values make up the percentage of a whole.
  • Slide 1Bar charts work just like column charts, but they use horizontal rather than vertical bars.
  • Slide 1Area charts are similar to line charts, except the areas under the lines are filled in.
  • Slide 1Surface charts allow you to display data across a 3D landscape. They work best with large data sets, allowing you to see a variety of information at the same time.
  • Slide 1

In addition to chart types, you’ll need to understand how to read a chart. Charts contain several elements, or parts, that can help you interpret data.

Click the buttons in the interactive below to learn about the different parts of a chart.

Book sales chart

To insert a chart:

  1. Select the cells you want to chart, including the column titles and row labels. These cells will be the source data for the chart. In our example, we’ll select cells A1:F6.Selecting cells
  2. From the Insert tab, click the desired Chart command. In our example, we’ll select Column.Selecting Column charts from the Insert tab
  3. Choose the desired chart type from the drop-down menu.Selecting a chart type
  4. The Selected chart will be inserted into the worksheet.Chart inserted into worksheet

If you’re not sure which type of chart to use, the Recommended Charts command will suggest several charts based on the source data.

Selecting recommended charts command

Chart and layout style

After inserting a chart, there are several things you may want to change about the way your data is displayed. It’s easy to edit a chart’s layout and style from the Design tab.

  • Excel allows you to add chart elements—including chart titleslegends, and data labels—to make your chart easier to read. To add a chart element, click the Add Chart Element command on the Design tab, then choose the desired element from the drop-down menu.Selecting Add Chart Element command from the Design tab
  • To edit a chart element, like a chart title, simply double-click the placeholder and begin typing.Editing the chart title
  • If you don’t want to add chart elements individually, you can use one of Excel’s predefined layouts. Simply click the Quick Layout command, then choose the desired layout from the drop-down menu.Selecting the quick layout command
  • Excel also includes several chart styles, which allow you to quickly modify the look and feel of your chart. To change the chart style, select the desired style from the Chart styles group. You can also click the drop-down arrow on the right to see more styles.Selecting a chart style

You can also use the chart formatting shortcut buttons to quickly add chart elements, change the chart style, and filter chart data.

Chart formatting shortcut buttons

Other chart options

There are many other ways to customize and organize your charts. For example, Excel allows you to rearrange a chart’s data, change the chart type, and even move the chart to a different location in a workbook.

To switch row and column data:

Sometimes you may want to change the way charts group your data. For example, in the chart below Book Sales data is grouped by genre, with columns for each month. However, we could switch the rows and columns so the chart will group the data by month, with columns for each genre. In both cases, the chart contains the same data—it’s just organized differently.

chart of book sales grouped by genre
  1. Select the chart you want to modify.
  2. From the Design tab, select the Switch Row/Column command.Selecting the Switch Column Row command
  3. The rows and columns will be switched. In our example, the data is now grouped by month, with columns for each genre.book sales grouped by month

To change the chart type:

If you find that your data isn’t working well in a certain chart, it’s easy to switch to a new chart type. In our example, we’ll change our chart from a column chart to a line chart.

  1. From the Design tab, click the Change Chart Type command.Selecting the Change Chart type command from the Design tab
  2. The Change Chart Type dialog box will appear. Select a new chart type and layout, then click OK. In our example, we’ll choose a Line chart.Selecting a line chart
  3. The selected chart type will appear. In our example, the line chart makes it easier to see trends in sales data over time.Line chart

To move a chart:

Whenever you insert a new chart, it will appear as an object on the same worksheet that contains its source data. You can easily move the chart to a new worksheet to help keep your data organized.

  1. Select the chart you want to move.
  2. Click the Design tab, then select the Move Chart command.Selecting the move chart command
  3. The Move Chart dialog box will appear. Select the desired location for the chart. In our example, we’ll choose to move it to a New sheet, which will create a new worksheet.
  4. Click OK.Selecting OK button to close dialog box
  5. The chart will appear in the selected location. In our example, the chart now appears on a new worksheet.a chart in a new worksheet

Keeping charts up to date

By default, when you add more data to your spreadsheet, the chart may not include the new data. To fix this, you can adjust the data range. Simply click the chart, and it will highlight the data range in your spreadsheet. You can then click and drag the handle in the lower-right corner to change the data range.

Screenshot of adjusting a data range

If you frequently add more data to your spreadsheet, it may become tedious to update the data range. Luckily, there is an easier way. Simply format your source data as a table, then create a chart based on that table. When you add more data below the table, it will automatically be included in both the table and the chart, keeping everything consistent and up to date.

Watch the video below to learn how to use tables to keep charts up to date.

https://youtube.com/watch?v=a1osBWySSvk%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

Challenge!

  1. Open our practice workbook.
  2. Click the Challenge tab in the bottom-left of the workbook.
  3. Select cells A1:E6 and insert a 2D Clustered Column chart.
  4. Change the chart title to September to December Sales.
  5. Use the Switch Row/Column command. The columns should now be grouped by month, with a different color for each salesperson.
  6. Move the chart to a new sheet.
  7. Change the chart type to line with markers.
  8. Use the Quick Layout command to change the layout of the chart.
  9. When you’re finished, your workbook should look something like this:Charts Challenge

Lesson 24: Conditional Formatting

Introduction

Let’s say you have a worksheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw information. Similar to charts and sparklines, conditional formatting provides a way to visualize data and make worksheets easier to understand.

Optional: Download our practice workbook.

Watch the video below to learn more about conditional formatting in Excel.

https://youtube.com/watch?v=zfQ8uOBoIj8%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

Understanding conditional formatting

Conditional formatting allows you to automatically apply formatting—such as colorsicons, and data bars—to one or more cells based on the cell value. To do this, you’ll need to create a conditional formatting rule. For example, a conditional formatting rule might be: If the value is less than $2000, color the cell red. By applying this rule, you’d be able to quickly see which cells contain values less than $2000.

Conditional Formatting in Excel

To create a conditional formatting rule:

In our example, we have a worksheet containing sales data, and we’d like to see which salespeople are meeting their monthly sales goals. The sales goal is $4000 per month, so we’ll create a conditional formatting rule for any cells containing a value higher than 4000.

  1. Select the desired cells for the conditional formatting rule.Selecting cells
  2. From the Home tab, click the Conditional Formatting command. A drop-down menu will appear.
  3. Hover the mouse over the desired conditional formatting type, then select the desired rule from the menu that appears. In our example, we want to highlight cells that are greater than $4000.The Conditional Formatting menu on the Home tab
  4. A dialog box will appear. Enter the desired value(s) into the blank field. In our example, we’ll enter 4000 as our value.
  5. Select a formatting style from the drop-down menu. In our example, we’ll choose Green Fill with Dark Green Text, then click OK.The Greater Than dialog box
  6. The conditional formatting will be applied to the selected cells. In our example, it’s easy to see which salespeople reached the $4000 sales goal for each month.A conditional formatting rule is now applied to the worksheet

You can apply multiple conditional formatting rules to a cell range or worksheet, allowing you to visualize different trends and patterns in your data.

Multiple conditional formatting rules on the same area of a worksheet

Conditional formatting presets

Excel has several predefined styles—or presets—you can use to quickly apply conditional formatting to your data. They are grouped into three categories:

  • Data Bars are horizontal bars added to each cell, much like a bar graph.Data bars
  • Color Scales change the color of each cell based on its value. Each color scale uses a two- or three-color gradient. For example, in the Green-Yellow-Red color scale, the highest values are green, the average values are yellow, and the lowest values are red.Color scales
  • Icon Sets add a specific icon to each cell based on its value.Icon sets

To use preset conditional formatting:

  1. Select the desired cells for the conditional formatting rule.Select cells
  2. Click the Conditional Formatting command. A drop-down menu will appear.
  3. Hover the mouse over the desired preset, then choose a preset style from the menu that appears.The presets on the Conditional Formatting menu
  4. The conditional formatting will be applied to the selected cells.The preset has been applied to the worksheet

Removing conditional formatting

To remove conditional formatting:

  1. Click the Conditional Formatting command. A drop-down menu will appear.
  2. Hover the mouse over Clear Rules, then choose which rules you want to clear. In our example, we’ll select Clear Rules from Entire Sheet to remove all conditional formatting from the worksheet.Clear Rules on the Conditional Formatting menu
  3. The conditional formatting will be removed.The conditional formatting has been removed

Click Manage Rules to edit or delete individual rules. This is especially useful if you’ve applied multiple rules to a worksheet.

The Conditional Formatting Rules Manager

Challenge!

  1. Open our practice workbook.
  2. Click the Challenge worksheet tab in the bottom-left of the workbook.
  3. Select cells B3:J17.
  4. Let’s say you’re the teacher and want to easily see all of the grades that are below passing. Apply Conditional Formatting so it Highlights Cells containing values Less Than 70 with a light red fill.
  5. Now you want to see how the grades compare to each other. Under the Conditional Formatting tab, select the Icon Set called 3 Symbols (Circled)Hint: The names of the icon sets will appear when you hover over them.
  6. Your spreadsheet should look like this:spreadsheet after completing Challenge
  7. Using the Manage Rules feature, remove the light red fill, but keep the icon set.

Lesson 25: Comments and Co-authoring

Introduction

There may be times when you’re working on a workbook and find that you need the help of others. Excel offers two powerful features that allow you to work with others on the same spreadsheet: comments and co-authoring.

Optional: Download our practice workbook.

Watch the video below to learn more about comments and co-authoring.

https://youtube.com/watch?v=eK23Fzttlyo%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

The Track Changes feature can also be helpful to review changes before making them permanent. It’s still available in Office 365, but it’s now hidden by default. You can learn more about Track Changes in our Excel 2016 lesson here.

Sharing a workbook with others

In order for others to collaborate on a workbook, you’ll need to share it with them first. 

  1. Click the Share button in the top-right corner.screenshot of the Share button in Microsoft Excel
  2. Click the OneDrive option associated with your account in order to upload the workbook.screenshot of selecting the OneDrive option in the Share menu
  3. The Share pane will appear on the right side of the screen. Type the email address of the person you’d like to share the workbook with.screenshot of entering an email address in the Share pane
  4. Select Can edit from the drop-down menu to allow this person to edit the workbook.screenshot of selecting "Can edit" from the drop-down menu in the Share menu
  5. Type a message if you want to include one, then click Share.screenshot of clicking the Share button after typing a message
  6. Your collaborators will now be able to access the workbook.

Comments

One way to collaborate on a workbook is through commenting. Sometimes you may want to provide feedback or ask a question without editing the contents of a cell. You can do this by adding a comment.

To add a comment:

  1. Select the cell where you want the comment to appear. In our example, we’ll select cell D17.Selecting cell E8
  2. From the Review tab, click the New Comment command.Clicking the New Comment command
  3. comment box will appear. Type your comment, then click anywhere outside the box to close the comment.Adding a comment
  4. The comment will be added to the cell, represented by a red triangle in the top-right corner.Comment indicator
  5. Select the cell again to view the comment.Selecting a cell to view a comment

To edit a comment:

  1. Select the cell containing the comment you want to edit.
  2. From the Review tab, click the Edit Comment command.Clicking the Edit Comment command
  3. The comment box will appear. Edit the comment as desired, then click anywhere outside the box to close the comment.Editing a comment

To show or hide comments:

  1. From the Review tab, click the Show All Comments command to view every comment in your worksheet at the same time.Clicking the Show All Comments command
  2. All comments in the worksheet will appear. Click the Show All Comments command again to hide them.Viewing all comments at the same time

You can also choose to show and hide individual comments by selecting the desired cell and clicking the Show/Hide Comment command.

Showing and hiding individual comments

To delete a comment:

  1. Select the cell containing the comment you want to delete. In our example, we’ll select cell E13.Selecting cell E8
  2. From the Review tab, click the Delete command in the Comments group.Clicking the Delete command
  3. The comment will be deleted.After deleting the comment

Co-authoring

Another collaboration tool is co-authoring, which allows others to view and edit your workbook in real time. This makes it easier and quicker to collaborate on a workbook with your team. After sharing a workbook with others, they’ll be able to co-author it.

Real-time co-authoring requires an Office 365 subscription.

When you co-author a workbook, you can see others at work because each will have their own unique color. If you want to see who’s currently editing a workbook, you can hover over the activity to see their name.

screenshot of hovering over a colored selection to see who is currently co-authoring a workbook

Restoring a previous version

When you or anyone else makes changes to a workbook, the changes are saved automatically. However, if you’re unhappy with the changes, you can always restore a previous version.

  1. Click the clock icon next to the Share button.screenshot of clicking the clock icon to access the Version History menu
  2. Version History pane will appear on the right side of the screen. Double-click the version you’d like to restore.screenshot of selecting a previous version to restore
  3. Once you’ve decided this is the version you want, click Restore.screenshot of clicking the Restore button to restore a previous version
  4. The previous version will be restored.

Challenge!

  1. Open our practice workbook.
  2. Add four comments to the worksheet.
  3. Delete one of the comments.
  4. Make all of the comments visible, using Show All Comments.
  5. When you’re finished, your workbook should look something like this:screenshot of a worksheet with some comments added
  6. Optional: Share the document with someone you know, and experiment with some of the various co-authoring features.

Lesson 26: Inspecting and Protecting Workbooks

Introduction

Before sharing a workbook, you’ll want to make sure it doesn’t include any spelling errors or information you want to keep private. Fortunately, Excel includes several tools to help finalize and protect your workbook, including the Document Inspector and Protect Workbook feature.

Optional: Download our practice workbook.

Watch the video below to learn more about inspecting and protecting workbooks.

https://youtube.com/watch?v=fsXS_VSuBvs%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

The Document Inspector

Whenever you create or edit a workbook, certain personal information may be added to the file automatically. You can use the Document Inspector to remove this information before sharing a workbook with others.

Because some changes may be permanent, it’s a good idea to save an additional copy of your workbook before using the Document Inspector to remove information.

To use the Document Inspector:

  1. Click the File tab to access Backstage view.
  2. From the Info pane, click Check for Issues, then select Inspect Document from the drop-down menu.Clicking Inspect Document
  3. You may be prompted to save your file before running the Document Inspector.Document Inspector warning
  4. Document Inspector will appear. Check or uncheck boxes, depending on the content you want to review, then click Inspect. In our example, we’ll leave everything selected.Inspecting the workbook
  5. The inspection results will appear. In our example, we can see that our workbook contains comments and some personal information, so we’ll click Remove All on both items to remove this information from the workbook.Removing personal information from the workbook
  6. When you’re done, click Close.Closing the Document Inspector

Protecting your workbook

By default, anyone with access to your workbook will be able to open, copy, and edit its content unless you protect it. There are several ways to protect a workbook, depending on your needs.

To protect your workbook:

  1. Click the File tab to access Backstage view.
  2. From the Info pane, click the Protect Workbook command.
  3. In the drop-down menu, choose the option that best suits your needs. In our example, we’ll select Mark as Final. Marking your workbook as final is a good way to discourage others from editing the workbook, while the other options give you even more control if needed.Selecting Mark as Final
  4. A dialog box will appear, prompting you to save. Click OK.Clicking OK to save the workbook
  5. Another dialog box will appear. Click OK.Clicking OK
  6. The workbook will be marked as final.A workbook marked as final

Marking a workbook as final will not prevent others from editing it. If you want to prevent people from editing it, you can use the Restrict Access option instead.

Challenge!

  1. Open our practice workbook.
  2. Use the Document Inspector to check the workbook and remove anything it finds.
  3. Protect the workbook by Marking As Final.
  4. When you’re finished, your workbook should look something like this:Inspecting and Protecting

Lesson 27: Intro to PivotTables

Introduction

When you have a lot of data, it can sometimes be difficult to analyze all of the information in your worksheet. PivotTables can help make your worksheets more manageable by summarizing data and allowing you to manipulate it in different ways.

Optional: Download our practice workbook.

Watch the video below to learn more about PivotTables.

https://youtube.com/watch?v=N4K3xjM76kI%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

Using PivotTables to answer questions

Consider the example below. Let’s say we wanted to answer the question What is the amount sold by each salesperson? Answering it could be time consuming and difficult; each salesperson appears on multiple rows, and we would need to total all of their different orders individually. We could use the Subtotal command to help find the total for each salesperson, but we would still have a lot of data to work with.

worksheet of sales data

Fortunately, a PivotTable can instantly calculate and summarize the data in a way that will make it much easier to read. When we’re done, the PivotTable will look something like this:

sales total by salesperson

Once you’ve created a PivotTable, you can use it to answer different questions by rearranging—or pivoting—the data. For example, let’s say we wanted to answer What is the total amount sold in each month? We could modify our PivotTable to look like this:

sales total by month

To create a PivotTable:

  1. Select the table or cells (including column headers) you want to include in your PivotTable.selecting a table
  2. From the Insert tab, click the PivotTable command.pivottable command
  3. The Create PivotTable dialog box will appear. Choose your settings, then click OK. In our example, we’ll use Table1 as our source data and place the PivotTable in a new worksheet.pivottable dialog box
  4. A blank PivotTable and Field List will appear in a new worksheet.blank pivottable
  5. Once you create a PivotTable, you’ll need to decide which fields to add. Each field is simply a column header from the source data. In the PivotTable Fields list, check the box for each field you want to add. In our example, we want to know the total amount sold by each salesperson, so we’ll check the Salesperson and Order Amount fields.selecting fields
  6. The selected fields will be added to one of the four areas below. In our example, the Salesperson field has been added to the Rows area, while Order Amount has been added to Values. You can also drag and drop fields directly into the desired area.fields added to areas
  7. The PivotTable will calculate and summarize the selected fields. In our example, the PivotTable shows the amount sold by each salesperson.finished pivottable

Just like with normal spreadsheets, you can sort the data in a PivotTable using the Sort & Filter command on the Home tab. You can also apply any type of number formatting you want. For example, you may want to change the number format to Currency. However, be aware that some types of formatting may disappear when you modify the PivotTable.

sorted and formatted

If you change any of the data in your source worksheet, the PivotTable will not update automatically. To manually update it, select the PivotTable and then go to Analyze > Refresh.

Pivoting data

One of the best things about PivotTables is that they can quickly pivot—or reorganize—your data, allowing you to examine your worksheet in several ways. Pivoting data can help you answer different questions and even experiment with your data to discover new trends and patterns.

To add columns:

So far, our PivotTable has only shown one column of data at a time. To show multiple columns, you’ll need to add a field to the Columns area.

  1. Drag a field from the Field List into the Columns area. In our example, we’ll use the Month field.adding a column
  2. The PivotTable will include multiple columns. In our example, there is now a column for each person’s monthly sales, in addition to the grand total.finished pivottable

To change a row or column:

Changing a row or column can give you a completely different perspective on your data. All you have to do is remove the field in question, then replace it with another.

  1. Drag the field you want to remove out of its current area. You can also uncheck the appropriate box in the Field List. In this example, we’ve removed the Month and Salesperson fields.removing fields
  2. Drag a new field into the desired area. In our example, we’ll place the Region field under Rows.dragging a field
  3. The PivotTable will adjust—or pivot—to show the new data. In our example, it now shows the amount sold by each region.finished pivottable

To learn more:

Once you’re comfortable with PivotTables, review our Doing More with PivotTables lesson for additional ways to customize and manipulate data.

Challenge!

  1. Open our practice workbook.
  2. Create a PivotTable in a separate sheet.
  3. We want to answer the question What is the total amount sold in each region? To do this, select Region and Order Amount. When you’re finished, your workbook should look like this:PivotTable 1
  4. In the Rows area, remove Region and replace it with Salesperson.
  5. Add Month to the Columns area.
  6. Change the number format of cells B5:E13 to CurrencyNote: You might have to make columns C and D wider to see the values.
  7. When you’re finished, your workbook should look like this:Intro to PivotTables

Lesson 28: Doing More with PivotTables

Introduction

As you learned in our previous lesson, PivotTables can be used to summarize and analyze almost any type of data. To manipulate your PivotTable—and gain even more insight into your data—Excel offers three additional tools: filtersslicers, and PivotCharts.

Optional: Download our practice workbook.

Watch the video below to learn more about enhancing PivotTables.

https://youtube.com/watch?v=FVzX4I88DBs%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

Filters

Sometimes you may want focus on a certain section of your data. Filters can be used to narrow down the data in your PivotTable so you can view only the information you need.

To add a filter:

In the example below, we’ll filter out certain salespeople to determine how their individual sales are impacting each region.

  1. Drag a field from the Field List to the Filters area. In this example, we’ll use the Salesperson field.dragging a field
  2. The filter will appear above the PivotTable. Click the drop-down arrow, then check the box next to Select Multiple Items.enabling select multiple items
  3. Uncheck the box next to any item you don’t want to include in the PivotTable. In our example, we’ll uncheck the boxes for a few salespeople, then click OK.unchecking/deselecting items
  4. The PivotTable will adjust to reflect the changes.filtered pivottable

Slicers

Slicers make filtering data in PivotTables even easier. Slicers are basically just filters but are easier and faster to use, allowing you to instantly pivot your data. If you frequently filter your PivotTables, you may want to consider using slicers instead of filters.

To add a slicer:

  1. Select any cell in the PivotTable.
  2. From the Analyze tab, click the Insert Slicer command.insert slicer command
  3. A dialog box will appear. Check the box next to the desired field. In our example, we’ll select Salesperson, then click OK.field dialog box
  4. The slicer will appear next to the PivotTable. Each selected item will be highlighted in blue. In the example below, the slicer contains all eight salespeople, but only five of them are currently selected.slicer and pivottable
  5. Just like filters, only selected items are used in the PivotTable. When you select or deselect an item, the PivotTable will instantly reflect the change. Try selecting different items to see how they affect the PivotTable. Press and hold the Ctrl key on your keyboard to select multiple items at once.adjusting the pivottable

You can also click the Filter icon in the top-right corner of the slicer to select all items at once.

PivotCharts

PivotCharts are like regular charts, except they display data from a PivotTable. Just like regular charts, you’ll be able to select a chart typelayout, and style that will best represent the data.

To create a PivotChart:

In the example below, our PivotTable is showing a portion of each region’s sales figures. We’ll use a PivotChart so we can see the information more clearly.

  1. Select any cell in your PivotTable.
  2. From the Insert tab, click the PivotChart command.pivotchart command
  3. The Insert Chart dialog box will appear. Select the desired chart type and layout, then click OK.chart dialog box
  4. The PivotChart will appear.pivottable, slicer, and pivotchart

Try using filters or slicers to narrow down the data in your PivotChart. To view different subsets of information, change the columns or rows in your PivotTable. In the example below, we’ve changed the PivotTable to view the monthly sales for each salesperson.

adjusted pivotchart

Challenge!

  1. Open our practice workbook.
  2. In the Rows area, remove Region and replace it with Salesperson.
  3. Insert a PivotChart and choose the type Line with Markers.
  4. Insert a slicer for Regions.
  5. Use the slicer to only show the South and East regions.
  6. Change the PivotChart type to Stacked Column.
  7. In the PivotChart Fields pane to the right, add Month to the Legend (Series) area. Note: You can also click the PivotTable and add Month to the Columns area for the same results.
  8. When you’re finished, your workbook should look something like this:More PivotTables

Lesson 29: What-if Analysis

Introduction

Excel includes powerful tools to perform complex mathematical calculations, including what-if analysis. This feature can help you experiment and answer questions with your data, even when the data is incomplete. In this lesson, you will learn how to use a what-if analysis tool called Goal Seek.

Optional: Download our practice workbook.

Watch the video below to learn more about what-if analysis and Goal Seek.

https://youtube.com/watch?v=STTYxT6iFio%3Frel%3D0%26showinfo%3D0%26enablejsapi%3D1%26origin%3Dhttps%253A%252F%252Fedu.gcfglobal.org

Goal Seek

Whenever you create a formula or function in Excel, you put various parts together to calculate a resultGoal Seek works in the opposite way: It lets you start with the desired result, and it calculates the input value that will give you that result. We’ll use a few examples to show how to use Goal Seek.

To use Goal Seek (example 1):

Let’s say you’re enrolled in a class. You currently have a grade of 65, and you need at least a 70 to pass the class. Luckily, you have one final assignment that might be able to raise your average. You can use Goal Seek to find out what grade you need on the final assignment to pass the class.

In the image below, you can see that the grades on the first four assignments are 58, 70, 72, and 60. Even though we don’t know what the fifth grade will be, we can write a formula—or function—that calculates the final grade. In this case, each assignment is weighted equally, so all we have to do is average all five grades by typing =AVERAGE(B2:B6). Once we use Goal Seek, cell B6 will show us the minimum grade we’ll need to make on that assignment.

the average function being used to average the other class grades
  1. Select the cell with the value you want to change. Whenever you use Goal Seek, you’ll need to select a cell that already contains a formula or function. In our example, we’ll select cell B7 because it contains the formula =AVERAGE(B2:B6).selecting cell B7
  2. From the Data tab, click the What-If Analysis command, then select Goal Seek from the drop-down menu.clicking the Goal Seek option from the What-if Analysis command drop-down menu
  3. A dialog box will appear with three fields. The first field, Set cell:, will contain the desired result. In our example, cell B7 is already selected.

    The second field, To value:, is the desired result. In our example, we’ll enter 70 because we need to earn at least that to pass the class.

    The third field, By changing cell:, is the cell where Goal Seek will place its answer. In our example, we’ll select cell B6 because we want to determine the grade we need to earn on the final assignment.
  4. When you’re done, click OK.
  5. The dialog box will tell you if Goal Seek was able to find a solution. Click OK.a successful result in the goal seek dialog box
  6. The result will appear in the specified cell. In our example, Goal Seek calculated that we will need to score at least a 90 on the final assignment to earn a passing grade.the value in cell B6 (90) determined by goal

To use Goal Seek (example 2):

Let’s say you’re planning an event and want to invite as many people as you can without exceeding a budget of $500. We can use Goal Seek to figure out how many people to invite. In our example below, cell B5 contains the formula =B2+B3*B4 to calculate the total cost of a room reservation, plus the cost per person.

  1. Select the cell with the value you want to change. In our example, we’ll select cell B5.selecting cell B5
  2. From the Data tab, click the What-If Analysis command, then select Goal Seek from the drop-down menu.clicking the Goal Seek option from the What-if Analysis command drop-down menu
  3. A dialog box will appear with three fields. The first field, Set cell:, will contain the desired result. In our example, cell B5 is already selected.

    The second field, To value:, is the desired result. In our example, we’ll enter 500 because we only want to spend $500.

    The third field, By changing cell:, is the cell where Goal Seek will place its answer. In our example, we’ll select cell B4 because we want to know how many guests we can invite without spending more than $500.
  4. When you’re done, click OK.setting parameters in the goal seek dialog box
  5. The dialog box will tell you if Goal Seek was able to find a solution. Click OK.
  6. The result will appear in the specified cell. In our example, Goal Seek calculated the answer to be approximately 18.62. In this case, our final answer needs to be a whole number, so we’ll need to round the answer up or down. Because rounding up would cause us to exceed our budget, we’ll round down to 18 guests.the value in cell B4 (18) determined by Goal Seek

As you can see in the example above, some situations will require the answer to be a whole number. If Goal Seek gives you a decimal, you’ll need to round up or down, depending on the situation.

Other types of what-if analysis

For more advanced projects, you may want to consider the other types of what-if analysis: scenarios and data tables. Instead of starting from the desired result and working backward, like with Goal Seek, these options allow you to test multiple values and see how the results change.

  • Scenarios let you substitute values for multiple cells (up to 32) at the same time. You can create as many scenarios as you want and then compare them without changing the values manually. In the example below, we’re using scenarios to compare different venues for an upcoming event.using the Scenarios Manager

For more information on scenarios, read this article from Microsoft.

  • Data tables allow you to take one or two variables in a formula and replace them with as many different values as you want, then view the results in a table. This option is especially powerful because it shows multiple results at the same time, unlike scenarios or Goal Seek. In the example below, we can view 24 possible results for a car loan.using a data table

For more information on data tables, read this article from Microsoft.

Challenge!

  1. Open our practice workbook.
  2. Click the Challenge tab in the bottom-left of the workbook.
  3. In cell B8, create a function that calculates the average of the sales in B2:B7.
  4. The workbook shows Dave’s monthly sales amounts for the first half of the year. If he reaches a $200,000 mid-year average, he will receive a 5% bonus. Use Goal Seek to find how much he needs to sell in June in order to make the $200,000 average.
  5. When you’re finished, your workbook should look like this:Goal Seek Challenge

About the Author

Leave a Reply

Your email address will not be published. Required fields are marked *

You may also like these