Welcome to the tour. Take a tour In just 10 steps, you'll be up and running with Excel, the world's most popular spreadsheet app. Go back to top by pressing CTRL+HOME. To start the tour, press CTRL+PAGE DOWN. Add numbers like a champ Add numbers like a champ Here are some ways to add up numbers in Excel: Cells C3 through D7 some contain datato with for Fruit, and one for Amount. Here are ways addtwo upcolumns. numbersOne in Excel: Go to D8 by pressing CTRL+G, type D8, and then press ENTER. Type =SUM(D4:D7), then press ENTER. 1 The result is 170. Here's another way to add, using a shortcut key. Cells F3 through G7 contain data with two columns: Meat and Amount. Go to cell G8.2Press ALT+=, then press ENTER. The result in cell G8 is 140. Here's another way to add. Cells C10 through D15 have two columns of data: Item and Amount. 3 Now add only the numbers over 50. Go to cell D16. Type =SUMIF(D11:D15,">50"), then press Enter. The result is 100. EXTRA CREDIT: Cells F10 through G15 contain data with two columns: Item and Amount. Go to cell G16. Try adding another S 4 more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN. Dive down for 5 More about the SUM function In some of the above tips, we taught you how to use the SUM function. Here are more details about it. Cells C37 through D41 contain data with two columns: Fruit and Amount. The formula in cell D42: =SUM(D38:D41). some in cell D42 could talk, it would say this: Sum up the values in cells D38, D39, D40, and D41. If the SUMInfunction details about Double Here's another way it canit.be used: below. D48 contain data with two columns: Item and Amount. Cells C47 through Cells F47 through G51 contain data with two columns: Item and Amount. If the SUM could talk, would say this: Cells E53 through E54 function contain data with oneitcolumn: Total. The formula in cell E54: =SUM(D48, G48:G51,100). If the formula in up cellthe E54 could talk, it would say: Sum the following: the value in cell D48, the values in cells G48, G49, G50, a Sum The formulafollowing: in cell E54 uses the following: • A single cell reference, which is the "address" or "name" of a cell. D48 is the single cell reference in the formula above. • A range of cells, which is a series of cells starting at one cell and ending at another. G48:G51 is the range of cells in the form • A constant, which is the number 100. IMPORTANT DETAIL: Go to cell E54. You'll notice the 100 toward the end of the formula. Although it's possible to put number Go to cell A66 for the next instruction. More about the SUM function =SUM(D38:D41) Here's another way it can be used: Here's another way it can be used: Sum the following: =SUM(D48,G48:G51,100) The formula above uses the following: • A single reference in the formula above. • A range of cells G48:G51 is the range of cells in the formula. • A constant More about the SUMIF function We also showed you the SUMIF function at the top of this sheet in cells A10 and A11. The SUMIF function sums up totals base Cells C72 through D77 contain data with two columns: Item and Amount. The formula in cell D78: =SUMIF(D73:D77,">50"). If the SUMIF could talk, would sayfunction this: Sumat upthe some onThe this SUMIF criterion, look through Wefunction also showed you it the SUMIF topvalues of thisbased sheet. function sums these cells D73 th NOTE: If you youbased are making a lot of SUMIF formulas, you might find that a PivotTable is a better solution. See the PivotT up find totals on a criterion. Cells F72 through G77 contain data with two columns: Item and Amount. GOOD TO Sum up some Go to cell A86 to go to the next instruction. values based on this criterion: More about the SUMIF function =SUMIF(D73:D77,">50") NOTE: is a better solution. More information on the web All about the SUM function All about the SUMIF function Use Excel as your calculator Free Excel training online Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN. More information on the web Add numbers like a champ Here are some ways to add up numbers in Excel: 1 Select the yellow cell under the amounts for fruit. 2 Type =SUM(D4:D7), and then press enter. When you're done, you'll see the result of 170. 3 Here's another way to add, using a shortcut key. Select the yellow cell under the amounts for meat. 4 Press 5 Now add only the numbers over 50. Select the last yellow cell. Type =SUMIF(D11:D15,">50") and then press Enter. The result is 100. Alt = first. Then, press Enter. Dive down for more detail Fruit Apples Oranges Bananas Lemons Item Bread Donuts Cookies Cakes Pies Next step More about the SUM function In some of the above tips, we taught you how to use the SUM function. Here are more details about it. Double-click a yellow cell on the right, and then read along with the text below. If the SUM function could talk, it would say this: Fruit Apples Oranges Bananas Lemons ORTANT DETAIL: Go to cell E54. You'll notice the 100 toward the end of the formula. Although it's possible to put numbers in a formula li Sum up the following: …the values in cells D38, D39, D40, and D41. =SUM(D38:D41) Here's another way it can be used: Here's another way it can be used: Sum the following: …the value in cell D48… …the values in cells G48, G49, G50, and G51... …and 100. Item Table =SUM(D48,G48:G51,100) The formula above uses the following: • A single cell reference, which is the "address" or "name" of a cell. D48 is the single cell reference in the formula above. • A range of cells, which is a series of cells starting at one cell and ending at another. G48:G51 is the range of cells in the formula. • A constant. The constant in this formula is the number 100. More about the SUMIF function also showed you the SUMIF function at the top of this sheet in cells A10 and A11. The SUMIF function sums up totals based on a criterion he SUMIF could talk, would sayfunction this: Sumat upthe some onThe this SUMIF criterion, look through Wefunction also showed you it the SUMIF topvalues of thisbased sheet. function sums these cells D73 through D77, and TE: If you youbased are making a lot of SUMIF you might find talk, that ait PivotTable a better solution. See the PivotTable worksheet up find totals on a criterion. If theformulas, SUMIF function could would sayisthis: Item Bread Sum up some ....Look through ...and if the Donuts values based these cells... value is on this greater than Cookies criterion: 50, sum it up. Cakes Pies =SUMIF(D73:D77,">50") NOTE: If you find you are making a lot of SUMIF formulas, you might find that a PivotTable is a better solution. See the PivotTable worksheet for more information. More information on the web All about the SUM function All about the SUMIF function Use Excel as your calculator Free Excel training online Back to top Next step Fruit Apples Oranges Bananas Lemons Amount 50 20 60 40 170 Meat Beef Chicken Pork Fish Amount 50 30 10 50 140 Item Bread Donuts Cookies Cakes Pies Amount 50 100 40 50 20 100 Item Bread Donuts Cookies Cakes Pies Amount 50 100 40 50 20 EXTRA CREDIT Try adding another SUMIF formula here, but add amounts that are less than 100. The result should be 160. Fruit Amount CHECK THIS OUT Apples 50 Select these cells. Then in the Oranges 20 lower-right corner of the Excel Bananas 60 window, look for this: Lemons 40 ble to put numbers in a formula170 like this, we don't recommend it unless it's absolutely necessary. This is known as a constant, and it's easy That's just another way to quickly find a total. find a total. Item Table Amount 20 Item Cars Trucks Bikes Skates Amount 20 10 10 40 Total: 200 IMPORTANT DETAIL Double-click this cell. You'll notice the 100 toward the end. Although it's possible to put numbers in a formula like this, we don't recommend it unless it's absolutely necessary. This is known as a constant, and it's easy to forget that it's there. We recommend referring to another cell instead, like cell D16. That way it's easily seen and not hidden inside a formula. ums up totals based on a criterion. these cells D73 through D77, and if the value is greater than 50 sum it up. on. See the PivotTable worksheet for more information. Item Amount Item Amount Bread 50 Bread 50 Donuts 100 Donuts 100 Cookies 40 Cookies 40 Cakes 50 Cakes 50 Pies 20 Pies 20 100 200 GOOD TO KNOW Double-click this cell and you'll see that the formula is different. Specifically, the sum criteria is ">=50" which means greater than or equal to 50. There are other operators you can use like " Sort & Filter > Sort A to Z. Department Bakery 2 Sort December's amounts from largest to smallest. Click any cell in the Dec column, Bakery and then click Home > Sort & Filter > Sort Largest to Smallest. Deli Deli 3 Now you'll filter the data so that only the Bakery rows appear. Press CTRL+A to Meat select all of the cells, and then click Home > Sort & Filter > Filter. Meat Filter buttons appear on the top row. On the Department cell, click the filter button 4 Produce and then click to clear the Select All checkbox. Then, click to select Bakery. Produce 5 Click OK and only the Bakery rows appear. Now clear the filter by clicking the filter button for Department and then click Clear filter... Dive down for more detail Next step Sort by date, or by color even want the Expense dates in order. So, select Expense date header, cell C31, then press ALT+DOWN ARROW and use the arrow keys to find There are many ways to sort in Excel. Here are just two more ways to sort, but this time you'll use Expense date the right-click menu: 9/18/2021 9/21/2021 1 You want the dates in order. So, right-click a date and then click 9/23/2021 Sort > Sort Oldest to Newest. The rows get sorted in ascending date order by the 9/20/2021 Expense date. 9/17/2021 9/19/2021 2 Someone filled three cells with yellow. You can sort the rows by that color. Rightclick a yellow cell, and then click Sort > Put Selected Cell Color on Top. More ways to filter data More ways to filter data to cell F49: Hotel. Press ALT+DOWN ARROW, then use the arrow keys to find Number Filters option. Press RIGHT ARROW to enter Numbe w add a second filter. Go to cell E49: Food. Press ALT+DOWN ARROW, then use the arrow keys to find the Number Filters option. Press RIG Many people type formulas to find amounts that are above average, or greater than a certain amount. But there's no need to type formulas when special filters are available. 1 On the Hotel cell, click the filter button and then click Number Filters > Above Average. Excel calculates the average amount of the Hotel column, and then shows only rows with amounts greater than that average. 2 Now add a second filter. On the Food cell, click the filter button and then click Number Filters > Greater than..., and then type 25. Click OK. Of the three rows that were filtered for above average, Excel shows two rows with Food amounts greater than 25. More information on the web Sort data in a range or table Filter data in a range or table Back to top Next step Expense date 9/23/2021 9/18/2021 s CTRL+G, type C5, then press Enter. Now press ALT+H to enter the Home tab above the ribbon, then press S to enter the Sort & Filter opt through G13. Press ALT+H to enter the Home tab above the ribbon, then press S for Sort & Filter options. Notice the options changed fro Department Category Oct Nov Dec Bakery Desserts $25.000 $80.000 $120.000 Bakery Breads $30.000 $15.000 $20.000 Deli Salads $90.000 $35.000 $25.000 Deli Sandwiches $80.000 $40.000 $20.000 Meat Beef $90.000 $110.000 $120.000 Meat Chicken $75.000 $82.000 $2.000.000 Produce Fruit $10.000 $30.000 $40.000 Produce Veggies $30.000 $80.000 $30.000 EXTRA CREDIT When you're done with step 5, try sorting alphabetically by two columns. Here's how: First sort Department alphabetically (that's step 1 on the left). Then click Home > Sort & Filter > Custom Sort. Add a second level for Category. After you click OK, Department will be sorted, and within each department, Category rows will be sorted in alphabetical order as well. W and use the arrow keys to find Sort Oldest to Newest. Press Enter. The rows get sorted in ascending date order by the Expense date. IMPORTANT DETAIL Expense date Employee Food Hotel You can't clear a sort order like 9/18/2021 Laura $45 $5.050 you can a filter. So if you don't 9/21/2021 Jackie $21 $3.820 want your sort to stick, undo it 9/23/2021 Tricia $30 $3.085 by pressing CTRL+Z. 9/20/2021 Mark $62 $2.112 9/17/2021 Dave $25 $1.611 9/19/2021 Jeff $69 $528 s RIGHT ARROW to enter Number Filters list and use the arrow keys to find the Above Average option, then press Enter. Excel calculates t e Number Filters option. Press RIGHT ARROW to enter the Number Filters list. Use the arrow keys to find the Greater than... option, then t Expense date Employee 9/23/2021 Tricia 9/18/2021 Laura Food Hotel $30 $3.085 $45 $5.050 ess S to enter the Sort & Filter options. Use the arrow keys to find the option to Sort A to Z, or press S, then press Enter. s. Notice the options changed from Sort A to Z to Sort Largest to Smallest and so forth. Use the arrow keys to find the option to Sort Large hen press Enter. Excel calculates the average amount of the Hotel column, and then shows only rows with amounts greater than that ave the Greater than... option, then type 25 and press Enter. Of the three rows that were filtered for above average, Excel shows two rows w nd the option to Sort Largest to Smallest, then press Enter. e, Excel shows two rows with Food amounts greater than 25 Tables make things a lot easier Tables make things a lot easier A table gives you special features and conveniences. Here’s how to create one: Cells C5 through G13 contain data. Go to any cell within that region, for example, cell D8. Press CTRL+G, type D8, then press E A table gives you special features and conveniences. Here’s how to create one: Press ALT+N to enter the Insert tab above the ribbon, then press T and press Enter. Or, press shortcut key combination CTRL+ Now you have 1 a table, which is a collection of cells that has special features. For starters: A table gives you banded rows for e You can also create new rows easily. Go to the empty cell under cell C13: Meat. Type some text, then press Enter. A new row You can also create columns easily: Go to any cell between H5 and H14, for example H10. Type some text, then press Enter. 2 two columns are created, formatted, and the text Jan and Feb are filled in cells H5 and I5 for you. Notice how the EXTRA CREDIT: Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN. 3 4 5 Calculated columns in tables One example of a convenience that tables give you: calculated columns. You type a formula once, and it gets automatically fil Cells C33 through H41 contain data with six columns: Department, Category, Oct, Nov, Dec, and Total. Go to cell H34: Total. One example of a convenience Press ALT+=, then press Enter. formula once, and it getsfilled automatically filled down you.have Here’s how it works: The SUM formula gets automatically down for you so that youfor don’t to do it yourself. Go to cell A47 for the next instruction. Calculated columns in tables 1 2 3 4 Total rows in tables Another convenience in tables are total rows. Instead of typing a SUM formula, Excel can make that total for you with a flip o Cells C54 through E61 contain data with three columns: Department, Category, and Sales. Go to any cell within the range above, for example cell D57. Another convenience in Table tablesTools are Design tab will appear. Press ALT+JT to enter the Design tab above the ribbon At the top of the Excel window, the totalatfor you with aofflip a switch. And same goes A new row that is added the bottom theoftable in cells C62the through E62. for the AVERAGE formula, and many others. Here’s how it works: The total of $24,000 is added to the total row, in cell E62 . Total rows in tables But what if you wanted to know the average? Select cell E62: $24,000. 1 Press ALT+DOWN ARROW, then use the arrow keys to find the Average option and press Enter. The average amount of $3,00 GOOD TO KNOW: There's a shortcut for showing and hiding the total row. Select inside the table, then press CTRL+SHIFT+T. Go to cell A722for the next instruction. 3 4 5 6 More information on the web Overview of Excel tables Total the data in an Excel table Use calculated columns in an Excel table Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN. More information on the web Tables make things a lot easier s C5 through G13 contain data. Go to any cell within that region, for example, cell D8. Press CTRL+G, type D8, then press Enter. A table gives you special features and conveniences. Here’s how to create one: ss ALT+N to enter the Insert tab above the ribbon, then press T and press Enter. Or, press shortcut key combination CTRL+T, then Enter. Department 1 Click inside the data to the right, and then click Insert > Table > OK. Produce Produce 2 Now you have a table, which is a collection of cells that has special features. For Bakery starters: A table gives you banded rows for easier reading. Bakery Deli 3 You can also create new rows easily. In the empty cell under Meat, type some Deli text and then press Enter. A new row for the table appears. Meat You can also create columns easily: In the lower-right corner of the table, click Meat 4 the resize handle and drag it to the right 2 columns. food 5 Notice how the two columns are created, formatted, and the text Jan and Feb are filled for you. Dive down for more detail Next step Calculated columns in tables e example of a convenience that tables give you: calculated columns. You type a formula once, and it gets automatically filled down for yo One example of a convenience that tables give you: calculated columns. You type a formula once, and it gets automatically filled down for you. Here’s how it works: 1 Select the cell under Total. 2 Press Alt 3 Press Enter 4 The SUM formula gets filled down for you so that you don’t have to do it yourself. = Department Produce Produce Bakery Bakery Deli Deli Meat Meat al rows in tables ther convenience in tables are total rows. Instead of typing a SUM formula, Excel can make that total for you with a flip of a switch. And t s C54 through E61 contain data with three columns: Department, Category, and Sales. to any cell within the range above, for example cell D57. Another convenience in Table tablesTools are total rows. Instead of typing a SUM to formula, Excel can make he top of the Excel window, the Design tab will appear. Press ALT+JT enter the Design tab above the ribbon, then press T to totalatfor you with aofflip a switch. And same goes ew row that is added the bottom theoftable in cells C62the through E62. for the AVERAGE formula, and many others. Here’s how it works: Department Total rows in tables what if you wanted to know thewithin average? cellthe E62: $24,000. any cell the Select table on right. 1 Select ss ALT+DOWN ARROW, then use the arrow keys to find the Average option and press Enter. The average amount of $3,000 appears. OD TO KNOW: There's a shortcut for showing and hiding the total row. Select inside the table, then press CTRL+SHIFT+T. the instruction. top of the Excel window, the Table Tools Design tab will appear. to cell A722for theAtnext 3 On that tab, click Total Row. 4 The total of $24,000 is added to the bottom of the table. 5 But what if you wanted to know the average? Click the cell with $24,000. 6 Click the down arrow appears. and then click Average. The average amount of $3,000 re information on the web rview of Excel tables al the data in an Excel table calculated columns in an Excel table press CTRL+PAGE DOWN. Overview of Excel tables More information on the web Total the data in an Excel table Use calculated columns in an Excel table Back to top Next step e D8, then press Enter. ombination CTRL+T, then Enter. Department Category Produce Veggies Produce Fruit Bakery Breads Bakery Desserts Deli Sandwich Deli Salads Meat Beef Meat Chicken food Oct Nov 30000 10000 30000 25000 80000 90000 90000 75000 Dec 80000 30000 15000 80000 40000 35000 110000 82000 Jan Feb 30000 40000 20000 120000 20000 25000 200000 150000 EXTRA CREDIT Try changing the table style. First click inside the table, and the Table Tools Design tab will appear at the top of Excel. Click that tab, and then pick a style you like. s automatically filled down for you. Here’s how it works: Department Produce Produce Bakery Bakery Deli Deli Meat Meat Category Oct Nov Veggies $30.000 $80.000 Fruit $10.000 $30.000 Breads $30.000 $15.000 Desserts $25.000 $80.000 Sandwiches $80.000 $40.000 Salads $90.000 $35.000 Beef $90.000 $110.000 Chicken $75.000 $82.000 Dec $30.000 $40.000 $20.000 $120.000 $20.000 $25.000 $200.000 $150.000 Total $140.000 $80.000 $65.000 $225.000 $140.000 $150.000 $400.000 $307.000 EXPERIMENT After putting in the calculated column, try typing over one of the cells in the column. What happens? If you see a green triangle, After putting in the calculated column, try typing over one of the cells in the column. What happens? If you see a green triangle, click it and then click the exclamation mark. You'll see that Excel's watchin' out for ya... r you with a flip of a switch. And the same goes for the AVERAGE formula, and many others. Here’s how it works: above the ribbon, then press T to select Total Row from within the Table Styles Options. Category Produce Produce Bakery Bakery Deli Deli Meat Meat Sales Column1 Total Row Veggies $1.000 $24.000 Fruit $2.000 Breads $3.000 Desserts $1.000 Sandwiches $2.000 Salads $3.000 Beef $4.000 Chicken $8.000 GOOD TO KNOW There's a shortcut for showing and hiding the total row. Click inside the table, and then press CTRL+SHIFT+T. Insert a drop Insert a drop-down list Drop-down lists make data entry easier for people. Here's how to do one: Cells C3 through D15 contain data data with two columns: Food and Department. Drop-down lists make entry easier for people. Here's how to do one: We want only three department names to be valid entries for each of the foods on the right. Those departments are Produce Go to cell D4.1Press CTRL+G, type D4, then press Enter. Select all cells from D4 to D15. On the Data tab, select Data Validation, or press ALT+A,V to open the Data Validation dialogue box. Tab to Allow and select Li In the Source text box, type Produce, Meat, Bakery. Make sure to put commas in between each name. Press Enter when you’ 2 D4, which is the cell next to Apples in C4. Press ALT+DOWN ARROW. You'll see a drop-down menu with the th Now select cell GOOD TO KNOW: Drop-down lists help ensure people enter valid data. So it makes sense that drop-downs are a part of a large Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN. 3 4 5 Best practice for drop-downs: Use a table. We just taught you how to insert a drop-down menu for the list of departments. But what if that list changes? For example, w Cells C31 through D43 contain data with two columns: Food and Department. Cells F31 through F34 contain data with one co From cells F31 to F34, select a cell with a department. For example, select cell F33: Meat. Create a table pressing then Weby just taughtCTRL+T, you how to Enter. insert a drop Now you’ll that set up the data validation again. Under D31:isDepartment, select allcalled of the Dairy? blank cells from D32 through D43. list changes? For example, what cell if there a new department You’d have On the Datatotab, selectthe Data Validation, or press ALT+A,V to there’s open thea Data dialogue box. Tabatotable Allow and press DO update data validation dialog box. But moreValidation efficient way by creating In the Source text box, type =$F$32:$F$34, then press Enter. first: You selected the values within the single column starting in cell F31: Department. Now go to cell1D32 and press ALT+DOWN ARROW. There are only three departments in the drop-down list: Produce, Meat an EXPERT TIP: Often people put their validation lists like this out of the way on another sheet. That way others won't be tempte Go to cell A60 for the next instruction. Best practice for drop 2 3 4 4 5 6 7 8 More information on the web Apply data validation to cells Create a drop-down list Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN. More information on the web Insert a drop-down list Drop-down lists make data entry easier for people. Here's how to do one: 1 We want only three department names to be valid entries for each of the foods on the right. Those departments are Produce, Meat and Bakery. 2 Click and drag to select the yellow cells under Department. 3 On the Data tab, click Data Validation. Under Allow, click List. 4 In the Source box, type Produce, Meat, Bakery. Make sure to put commas in between them. Click OK when you’re done. 5 Now click the yellow cell next to Apples, and you'll see a drop-down menu. Food Apples Beef Bananas Lemons Broccoli Kale Ham Bread Chicken Cookies Cakes Pies Next step Dive down for more detail Best practice for drop-downs: Use a table. just taught you how to insert a drop-down menu for the list of departments. But what if that list changes? For example, what if there is a s C31 through D43 contain data with two columns: Food and Department. Cells F31 through F34 contain data with one column: Departme We just taught you how to insert a drop-down menu for the list of departments. But what if that list changes? For example, what if there is a new department called Dairy? You’d have to update the data validation dialog box. But there’s a more efficient way by creating a table first: 1 In column F, click a cell with a department. For example, click Meat. 2 Create a table by pressing 3 Now you’ll set up the data validation again. In column D, select all of the blank cells under Department. 4 On the Data tab, click Data Validation. Under Allow, click List. Ctrl T and then OK. Food Apples Beef Bananas Lemons Broccoli Kale Ham Bread Chicken Cookies Cakes Pies 4 On the Data tab, click Data Validation. Under Allow, click List. 5 Click inside the Source box, then click the up arrow button 6 Click and drag to select just the Produce, Meat and Bakery cells in column F. Then click the down arrow button 7 You should see this in the Source box: =$F$32:$F$34. (If you don’t see that you can type it in.) Click OK. 8 Now click the drop-down arrow. There are only three departments: Produce, Meat and Bakery. But if you add a new department in column F under Bakery, it will get updated with the new department. More information on the web Apply data validation to cells Create a drop-down list Back to top Next step Food Apples Beef Bananas Lemons Broccoli Kale Ham Bread Chicken Cookies Cakes Pies Department GOOD TO KNOW Drop-down lists help ensure people enter valid data. So it makes sense that drop-downs are a part of a larger group of features known as data validation. There are other data validation methods. For example, you can restrict entry to whole numbers, dates, or even minimum and maximum amounts. There are many options available, and you can read more about them by clicking the link at the bottom of this sheet. s? For example, what if there is a new department called Dairy? You’d have to update the data validation dialog box. But there’s a more e data with one column: Department. Food Apples Beef Bananas Lemons Broccoli Kale Ham Bread Chicken Cookies Cakes Pies Department Department Produce Meat Bakery EXPERT TIP Often people put their validation lists like this out of the way on another sheet. That way others won't be tempted to change the list. log box. But there’s a more efficient way by creating a table first: Analyze data quickly Analyze data quickly Here’s how to analyze data so that you can spot patterns and trends quickly: Cells C5 through data data in fivesocolumns: Category, Oct, Nov, and Dec. Here’sG13 howcontain to analyze that youDepartment, can spot patterns and trends quickly: Go to a cell in the table between cells C5 through G13, for example go to cell E9, then press CTRL+Q. A Quick Analysis panel a Press Tab key1to enter Formatting options, then press Enter to select Data Bars. The cells under Oct, Nov, and Dec columns, cells E6 through G13 get special data bars that visualize their amounts. Now let's say you want to get rid of the data bars. Select the entire range of cells from C5 through G13, then press CTRL+Q to Press Tab key2to enter Formatting options, then press RIGHT ARROW to find Clear…, then press Enter. GOOD TO KNOW: When you select cells, this Quick Analysis button appears. Aptly named, don't you think? You can always ac Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN. 3 4 Quickly make a chart You can always use the Insert tab and create a chart. But here is another way to make a chart, using the Quick Analysis option Cells C34 through G42 contain data with five columns: Department, Category, Oct, Nov, and Dec. Go to a cell inside the table between cells C34 and G42, for example go to cell D38, then press Ctrl+Q. You canpanel always use thePress RIGHT ARROW until you find Charts. The Quick Analysis appears. using Press the Tab key to enter the Charts options and Enter to select Clustered… . A new clustered column chart appears and is selected. Use your arrow keys to move it anywhere you’d like. Within the chart Go to cell A47 for the next instruction. Quickly make a chart 1 2 3 4 Quickly make sparklines Let's say you want little trend lines to the right of this data to show how the amounts go up or down during the three months Cells C54 through G62 contain data with five columns: Department, Category, Oct, Nov, and Dec. Go to a cell inside the table between cells C55 and G62, then press Ctrl+Q. Let's say you want little trend lines the rightARROW of this data show the amounts go up On the Quick Analysis panel that appears, pressto the RIGHT untilto you findhow Sparklines, then press Taborto select the Line down during the three You don't make 8H62. littleEach line line charts. You canthe make Sparklines appear to the right of themonths. Dec column in cellshave H55to through represents data for that row, and sho sparklines instead. To clear the sparklines, select cells H55 through H62. Press ALT+JD to enter The Sparkline Tools Design tab above the ribbon. Go to cell A68 for the next instruction. Quickly make sparklines 1 2 3 4 More information on the web Analyze your data instantly Analyze trends in data using sparklines Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN. More information on the web Analyze data quickly Here’s how to analyze data so that you can spot patterns and trends quickly: to a cell in the table between cells C5 through G13, for example go to cell E9, then press CTRL+Q. A Quick Analysis panel appears. Department 1 Click and drag to select all cells on the right, and then click this button in the lowerProduce right corner: Produce 2 On the panel that appears, click Data Bars. The cells under Oct, Nov, and Dec Bakery columns get special data bars that visualize their amounts. Bakery Deli 3 Now let's say you want to get rid of the bars. Click this button again: Deli Meat On the panel that appears, click the Clear Format button on the right. Meat 4 Dive down for more detail Next step Quickly make a chart can always use the Insert tab and create a chart. But here is another way to make a chart, using the Quick Analysis option. This time thou You can always use the Insert tab and create a chart. But here is another way to make a chart, using the Quick Analysis button. This time though, we'll use the keyboard shortcut: ew clustered column chart appears and is selected. Use your arrow keys to move it anywhere you’d like. Within the chart each product ha Department Ctr l Q 1 Click a cell inside the data to the right, and then press Bakery Bakery 2 On the panel that appears, click Charts. Deli Deli Meat 3 Click the first Clustered… button. Meat Produce Produce 4 A new clustered column chart appears. Move it anywhere you’d like. Notice that each product has three columns, one for each month of sales. Quickly make sparklines s say you want little trend lines to the right of this data to show how the amounts go up or down during the three months. You don't have Let's say you want little trend lines the rightARROW of this data show the amounts go up the Quick Analysis panel that appears, pressto the RIGHT untilto you findhow Sparklines, then press Taborto select the Line option. Press En down during the three You don't make 8H62. littleEach line line charts. You canthe make rklines appear to the right of themonths. Dec column in cellshave H55to through represents data for that row, and shows whether the sparklines instead. clear the sparklines, select cells H55 through H62. Press ALT+JD to enter The Sparkline Tools Design tab above the ribbon. Press C to select Department Ctr l Q 1 Click a cell inside the data to the right, and then press Bakery Bakery Deli 2 On the panel that appears, click Sparklines, and then click the Line button. Deli Meat 3 Sparklines appear to the right of the Dec column. Each line represents the data for Meat that row, and shows whether the amounts go up or down. Produce Produce To clear the sparklines, click and drag to select them. The Sparkline Tools Design 4 tab will appear at the top of the window. Go to that tab, and then click the Clear button. More information on the web Analyze your data instantly Analyze trends in data using sparklines Back to top Next step k Analysis panel appears. Department Produce Produce Bakery Bakery Deli Deli Meat Meat Category Veggies Fruit Breads Desserts Sandwich Salads Beef Chicken Oct Nov $30.000 $10.000 $30.000 $25.000 $80.000 $90.000 $90.000 $75.000 $80.000 $30.000 $15.000 $80.000 $40.000 $35.000 $110.000 $82.000 Dec $30.000 $40.000 $20.000 $120.000 $20.000 $25.000 $200.000 $150.000 GOOD TO KNOW When you select cells, this button appears: It's called the Quick Analysis button. Aptly named, don't you think? If you ever have a question about the data, click this button and see if it gives you some answers. ck Analysis option. This time though, we'll use the keyboard shortcut: Within the chart each product has three columns, one for each month of sales: Oct, Nov, and Dec. Department Category Oct Nov Dec Bakery Breads $30.000 $15.000 $20.000 Bakery Desserts $25.000 $80.000 $120.000 Deli Sandwiches $80.000 $40.000 $20.000 Deli Salads $90.000 $35.000 $25.000 Meat Beef $90.000 $110.000 $200.000 Meat Chicken $75.000 $82.000 $150.000 Produce Veggies $30.000 $80.000 $30.000 Produce Fruit $10.000 $30.000 $40.000 the three months. You don't have to make 8 little line charts. You can make sparklines instead. to select the Line option. Press Enter to add Sparklines to the table. that row, and shows whether the amounts go up or down. bove the ribbon. Press C to select the Clear option, then press C again to select to Clear Selected Sparklines. Department Category Oct Nov Dec Bakery Breads $30.000 $15.000 $20.000 Bakery Desserts $25.000 $80.000 $120.000 Deli Sandwiches $80.000 $40.000 $20.000 Deli Salads $90.000 $35.000 $25.000 Meat Beef $90.000 $110.000 $200.000 Meat Chicken $75.000 $82.000 $150.000 Produce Veggies $30.000 $80.000 $30.000 Produce Fruit $10.000 $30.000 $40.000 Great charts recommended for you Great charts recommended for you Cells C5 through D11 contain data with two columns: Year and Conference attendance. Go to any cell in the table between cells C5 and D11, for example go to cell C6. Press CTRL+G, type C6, then press Enter. Now press ALT+N 1 to enter The Insert Charts tab above the ribbon. Press R to bring up Recommended Charts options. Several recommendations will appear. Press Tab to enter the list and use the arrow keys to find an option called Clustered Co A column chart appears showing total number of conference attendees per year. Use the arrow keys to move the chart anyw 2 a trendline. Select the chart you just created, and press ALT+JC to enter the Chart Tools Design tab above the Now you'll add Press A to Add chart element, then press DOWN ARROW to find the Trendline option. Press RIGHT ARROW to open the Trend EXTRA CREDIT: 3 Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN. 4 5 Horizontal and vertical axes In school you might have learned that there is an x-axis and a y-axis. Excel has these two axes as well, but it calls them someth In Excel this is what they are called: • The x-axis along the bottom is called the horizontal axis. school might have learned that thereaxis. is an x • The y-axisInthat runsyou up and down is called the vertical well, but be it calls them something different. Each axis can either a value axis or a category axis. • A value axis represents numerical values. For example, a value axis can represent dollars, hours, duration, temperature, and Inaxis Excel this is what theylike aredates, called:people names, product names. The horizontal axis in the chart on the right star • A category represents things Go to cell A52 for the next instruction. • The x • The y Horizontal and vertical axes Each axis can either be a value axis or a category axis. • A value axis hours, duration, temperature, and so on. The vertical axis on the right is a value axis. • A category axis axis on the right has years Secondary axis You can also use a secondary axis in a chart. A secondary axis is an additional value axis that can show different values than th A popular example is in the chart on the right starting in cell D52. It's the same as the chart above, but it has an additional sec Cells D67 through F73 contain data with three columns: Date, Conference attendance, and Food sales. Food Sales column con You can also use a EXTRA CREDIT: canfor show values than the other value axis. Go to cell A68 thedifferent next instruction. Secondary axis A popular example is on the right. It's the same as the chart above, but it has an additional secondary vertical axis that represents the sales amounts for each month. Some would say that by having a secondary axis, you almost have “two charts in one.” That’s true. This chart is both a column chart and a line chart. These kind of charts are called interested in this kind of chart, click the link at the bottom of this sheet. More information on the web Create a chart from start to finish Create a combo chart with a secondary axis Available chart types in Office Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN. More information on the web Great charts recommended for you to any cell in the table between cells C5 and D11, for example go to cell C6. Press CTRL+G, type C6, then press Enter. 1 Click anywhere in the data to the right, and then click Insert > Recommended Charts. Year 2 You'll see several recommendations. Click the second one on the left called Clustered Columns. Then click OK. 3 A column chart appears showing total number of conference attendees per year. Feel free to move it anywhere you'd like. 4 Now you'll add a trendline. Select the chart, and the Chart Tools tab will appear at the top of the Excel window. 5 On the Chart Tools tab, click Design. Then click Add chart element > Trendline > Linear. Now you have a trendline that shows the general direction of the units sold over time. Dive down for more detail 2016 2017 2018 2019 2020 2021 Next step Horizontal and vertical axes chool you might have learned that there is an x-axis and a y-axis. Excel has these two axes as well, but it calls them something different. In school you might have learned that there is an x-axis and a y-axis. Excel has these two axes as well, but it calls them something different. value axis represents numerical values. For example, a value axis can represent dollars, hours, duration, temperature, and so on. The ver Inaxis Excel this is what theylike aredates, called:people names, product names. The horizontal axis in the chart on the right starting in cell D30 h category represents things • The x-axis along the bottom is called the horizontal axis. • The y-axis that runs up and down is called the vertical axis. Each axis can either be a value axis or a category axis. • A value axis represents numerical values. For example, a value axis can represent dollars, hours, duration, temperature, and so on. The vertical axis on the right is a value axis. • A category axis represents things like dates, people names, product names. The horizontal axis on the right has years so this is a category axis. Vertical axis (Value axis) Secondary axis can also use a secondary axis in a chart. A secondary axis is an additional value axis that can show different values than the other value a opular example is in the chart on the right starting in cell D52. It's the same as the chart above, but it has an additional secondary vertical s D67 through F73 contain data with three columns: Date, Conference attendance, and Food sales. Food Sales column contains data that You can also use a secondary axis in a chart. A secondary axis is an additional value axis that can show different values than the other value axis. A popular example is on the right. It's the same as the chart above, but it has an additional secondary vertical axis that represents the sales amounts for each month. Some would say that by having a secondary axis, you almost have “two charts in one.” That’s true. This chart is both a column chart and a line chart. These kind of charts are called Combo charts in Excel. If you’re interested in this kind of chart, click the link at the bottom of this sheet. Dat More information on the web Create a chart from start to finish Create a combo chart with a secondary axis Available chart types in Office Back to top Next step Conference attendance 1400 press Enter. 1200 Year Conference attendance 2016 2017 2018 2019 2020 2021 1000 500 800 1000 900 1000 1200 800 600 400 200 0 2021 2020 2019 2018 2017 2016 EXTRA CREDIT Want a data table directly under the chart? Click the chart. On the Chart Tools tab, click Design. Then click Add Chart Element > Data Table > With Legend Keys. calls them something different. Conference attendance 1400 temperature, and so on. The vertical axis in the chart on the right starting in cell D30 is a value axis. t on the right starting in cell D301200 has years so this is a category axis. 1000 Vertical axis (Value axis) 800 600 400 200 0 2016 2017 2018 2019 Horizontal axis 2020 2021 Horizontal axis (Category axis) $35.000 ent values than the other value1400 axis. an additional secondary vertical axis that represents the sales amounts for each month. Some would say that by having a secondary axis, 1200 $30.000 Sales column contains data that supports the secondary axis for the chart described above. 1000 $25.000 800 $20.000 600 $15.000 400 $10.000 200 $5.000 0 Secondary axis $0 2016 2017 2018 2019 Conference attendance Date 2020 Food sales Conference attendance 2016 2017 2018 2019 2020 2021 EXTRA CREDIT Try making a combo chart. Select the data above, and then click Insert > Recommended Charts. At the top, click the All Charts tab, and then click Combo at the bottom. On the right, click the Secondary Axis checkbox for Food sales. 2021 Food sales 500 800 1000 900 1000 1200 $5.000 $11.200 $30.000 $25.000 $5.000 $8.000 Data that supports the secondary axis t by having a secondary axis, you almost have “two charts in one.” That’s true. This chart is both a column chart and a line chart. These kin Secondary axis Data that supports the secondary axis above and a line chart. These kind of charts are called Combo charts in Excel. If you’re interested in this kind of chart, select the hyperlink in cel select the hyperlink in cell A70. Summarize data with PivotTables Summarize data with PivotTables Cells C3 through F9 contain data with four columns: Date, Salesperson, Product, and Amount. Look through the Date, Salesperson, Product and Amount columns. Can you quickly identify which product is the most profita 1 the PivotTable, we clicked a few buttons so that the data could be summarized. Now we know which produ When we created Next you’ll pivot the data so that you can find out which salesperson is the leading seller. Press CTRL+G, type E12, then press Press SHIFT+F6 until you enter the PivotTable Fields pane. If the pane isn't open, press ALT+JT, then L to launch the PivotTabl Now, press TAB 2 until you access the categories list: Date, Salesperson, Product and Amount. Use your arrows to find the Sale Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN. 3 4 5 Create a PivotTable Now you’ll create the PivotTable yourself so that you know how to make one when you need to summarize data. Cells C34 through F40 contain data with four columns: Date, Salesperson, Product, and Amount. Select a cell inside the table. For example go to cell E38, then press ALT+JT to enter the Design menu above the ribbon. Press Now you’ll createappears. the PivotTable that you knowLeave how this to make when youselected press Tab to A Create PivotTable dialogue Focus is yourself on Selectso a table or range. radio one button option needFields to summarize data. The PivotTable pane appears on the right. Press SHIFT+F6 until you come to the Search text box: Type words to search Press Tab to Now press 1 Congratulations, you made a PivotTable. But there is a lot more you can do. So go to cell A60 if you want to learn more. Go to cell A58 for the next instruction. Create a PivotTable 2 3 4 5 6 More information on the web Create a PivotTable to analyze worksheet data Use the Field List to arrange fields in a PivotTable Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN. More information on the web Summarize data with PivotTables 1 Look at the Date, Salesperson, Product and Amount columns. Can you quickly identify which product is the most profitable? Or which salesperson is the leading seller? That’s where the PivotTable below can help. 2 When we created the PivotTable, we clicked a few buttons so that the data could be summarized. Now we know which product is the most profitable. 3 Now you’ll pivot the data so that you can find out which salesperson is the leading seller. Right-click any cell inside the PivotTable, and then click Show Field List. 4 The PivotTable Fields pane appears. At the bottom of the pane, under Rows, click Product and then click Remove Field. 5 Date 7/28/2021 8/2/2021 8/19/2021 8/23/2021 9/12/2021 9/23/2021 At the top of the pane, click the checkbox for Salesperson. Now you can see who’s the leading salesperson. Dive down for more detail Next step Create a PivotTable ect a cell inside the table. For example go to cell E38, then press ALT+JT to enter the Design menu above the ribbon. Press V to insert a Piv Now you’ll createappears. the PivotTable that you knowLeave how this to make when youselected press Tab to choose where reate PivotTable dialogue Focus is yourself on Selectso a table or range. radio one button option needFields to summarize data. PivotTable pane appears on the right. Press SHIFT+F6 until you come to the Search text box: Type words to search for edit. 1 Click a cell inside the data on the right, and then on the Insert menu, click PivotTable. 2 In the dialog that appears, click Existing Worksheet, and then type C42 in the Location box. Click OK. 3 The PivotTable Fields pane appears on the right. 4 At the top of the pane, click the checkbox for Product. When you do that, the Product field gets added to the Rows area at the bottom of the pane. And, the product data appears as Row labels in the new PivotTable. Date 7/28/2021 8/2/2021 8/19/2021 8/23/2021 9/12/2021 9/23/2021 When you do that, the Product field gets added to the Rows area at the bottom of the pane. And, the product data appears as Row labels in the new PivotTable. 5 At the top of the pane, click the checkbox for Amount. When you do that, the Amount field will get added to the Values area at the bottom of the pane. And, at the same time the amounts are totaled for each product in the PivotTable. 6 Congratulations, you made a PivotTable. But there is a lot more you can do. So click the link at the bottom of this sheet if you want to learn more. More information on the web Create a PivotTable to analyze worksheet data Use the Field List to arrange fields in a PivotTable Back to top Next step Date 7/28/2021 8/2/2021 8/19/2021 8/23/2021 9/12/2021 9/23/2021 Salesperson Anne Mark Anne Mark Mariya Laura PivotTable Product Beer Wine Beer Soda Soda Wine Amount $ 1.400 $ 1.010 $ 750 $ 510 $ 1.600 $ 680 Row Labels Beer Soda Wine Grand Total Sum of Amount $2.150 $2.110 $1.690 $5.950 the ribbon. Press V to insert a PivotTable. ected press Tab to choose where you want the PivotTable report to be placed. The default option is selected: New Worksheet. Press DOW e words to search for edit. Date 7/28/2021 8/2/2021 8/19/2021 8/23/2021 9/12/2021 9/23/2021 Salesperson Anne Mark Anne Mark Mariya Laura Product Beer Wine Beer Soda Soda Wine Amount $ 1.400 $ 1.010 $ 750 $ 510 $ 1.600 $ 680 New Worksheet. Press DOWN ARROW to select Existing Worksheet. Press Tab to enter the Location text box and type C42, then press En nd type C42, then press Enter. More questions about Excel? Press ALT+Q and type what you want to know. Keep going. There is more to learn with Excel: LinkedIn Learning: Video courses for all levels—from beginner to advanced. Take at your own pace. Community: Ask questions and connect with other Excel fans. What else Give us feedback on this tour More questions about Excel? Click the Help button to find out more about Excel. Keep going. There is more to learn with Excel: LinkedIn Learning Community Video courses for all levels—from beginner to advanced. Take at your own pace. Ask questions and connect with other Learn more Learn more Give us feedba What else is new? Office 365 subscribers get continual updates and new features. Learn more Give us feedback on this tour 
Purchase answer to see full attachment