{"text_format":"2","workbook":"xlsx","n_sheets":12,"sheets":["Start","1. Add","2. Fill","3. Split","4. Transpose","5. Sort & filter","6. Tables","7. Drop-downs","8. Analyze","9. Charts","10. PivotTables","Learn more"],"not_rendered":{"charts":2,"images":40,"macros":0,"external_links":0,"defined_names":12,"comments":0,"pivot_tables":1},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"run_cost":"one","rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Start","index":0,"n_rows":4,"n_cols":1,"rows_kept":4,"cut":false,"hidden":false,"merged":[]}
["Welcome to the tour. \nInstructions for screen readers: In just 10 steps, you'll be up and running with Excel, the world's most popular spreadsheet app. \nThere are 11 more sheets in this tour. The instructions for each sheet start in cell A1, and each subsequent step is in cell A2, A3, and so on. \nThe instructions will indicate which cells to navigate to for use of a feature, or further reading.\nTo start, press CTRL+PAGE DOWN."]
["Take a tour"]
["In just 10 steps, you'll be up and running with\nExcel, the world's most popular spreadsheet app."]
["Go back to top by pressing CTRL+HOME. To start the tour, press CTRL+PAGE DOWN."]

{"sheet":"1. Add","index":1,"n_rows":91,"n_cols":7,"rows_kept":91,"cut":false,"hidden":false,"merged":[]}
["Add numbers like a champ"]
["Here are some ways to add up numbers in Excel:"]
["Cells C3 through D7 contain data with two columns. One for Fruit, and one for Amount. ",null,"Fruit","Amount",null,"Meat","Amount"]
["Go to D8 by pressing CTRL+G, type D8, and then press ENTER.",null,"Apples",50,null,"Beef",50]
["Type =SUM(D4:D7), then press ENTER.",null,"Oranges",20,null,"Chicken",30]
["The result is 170. ",null,"Bananas",60,null,"Pork",10]
["Here's another way to add, using a shortcut key. Cells F3 through G7 contain data with two columns: Meat and Amount.",null,"Lemons",40,null,"Fish",50]
["Go to cell G8. Press 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.",null,"Item","Amount",null,"Item","Amount"]
["Now add only the numbers over 50. Go to cell D16. Type =SUMIF(D11:D15 comma \">50\"), then press Enter. The result is 100.",null,"Bread",50,null,"Bread",50]
["EXTRA CREDIT: Cells F10 through G15 contain data with two columns: Item and Amount. Go to cell G16. Try adding another SUMIF formula here. Add amounts in column G, cells G11 to G15 but add amounts that are less than 100. The result should be 160.",null,"Donuts",100,null,"Donuts",100]
["Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN.",null,"Cookies",40,null,"Cookies",40]
[null,null,"Cakes",50,null,"Cakes",50]
[null,null,"Pies",20,null,"Pies",20]
{"$rows_empty":11}
["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)."]
["If the SUM function in cell D42 could talk, it would say this: Sum up the values in cells D38, D39, D40, and D41."]
["Here's another way it can be used:"]
["Cells C47 through D48 contain data with two columns: Item and Amount. "]
["Cells F47 through G51 contain data with two columns: Item and Amount. "]
["Cells E53 through E54 contain data with one column: Total."]
["The formula in cell E54: =SUM(D48 comma G48:G51 comma 100)."]
["If the formula in cell E54 could talk, it would say: Sum the following: the value in cell D48, the values in cells G48, G49, G50, and G51, and 100.",null,"Fruit","Amount"]
["The formula in cell E54 uses the following:",null,"Apples",50]
["• A single cell reference, which is the \"address\" or \"name\" of a cell. D48 is the single cell reference in the formula above. ",null,"Oranges",20]
["• 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. ",null,"Bananas",60]
["• A constant, which is the number 100. ",null,"Lemons",40]
["IMPORTANT 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 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. ",null,null,{"$f":"SUM(D38:D41)","v":170}]
["Go to cell A66 for the next instruction."]
[]
[]
[]
[null,null,"Item","Amount",null,"Item","Amount"]
[null,null,"Table",20,null,"Cars",20]
[{"$nulls":5},"Trucks",10]
[{"$nulls":5},"Bikes",10]
[{"$nulls":5},"Skates",40]
[]
[{"$nulls":4},"Total:"]
[{"$nulls":4},{"$f":"SUM(D48,G48:G51,100)","v":200}]
{"$rows_empty":11}
["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 based on a criterion. "]
["Cells C72 through D77 contain data with two columns: Item and Amount. "]
["The formula in cell D78: =SUMIF(D73:D77 comma \">50\")."]
["If the SUMIF function could talk, it would say this: Sum up some values based on this criterion, look through these cells D73 through D77, and if the value is greater than 50 sum it up."]
["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."]
["Cells F72 through G77 contain data with two columns: Item and Amount. ",null,"Item","Amount",null,"Item","Amount"]
["GOOD TO KNOW: Go to cell G78. The formula in cell G78: =SUMIF(G73:G77 comma \">=50\") is different from the formula in cell D78. Specifically, the sum criteria is \">=50\" which means greater than or equal to 50. There are other operators you can use like \"<=50\" which is less than or equal to 50. And there's \"<>50\" which is not equals 50. \n",null,"Bread",50,null,"Bread",50]
["Go to cell A86 to go to the next instruction.",null,"Donuts",100,null,"Donuts",100]
[null,null,"Cookies",40,null,"Cookies",40]
[null,null,"Cakes",50,null,"Cakes",50]
[null,null,"Pies",20,null,"Pies",20]
[null,null,null,{"$f":"SUMIF(D73:D77,\">50\")","v":100},null,null,{"$f":"SUMIF(G73:G77,\">=50\")","v":200}]
{"$rows_empty":7}
["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."]

{"sheet":"2. Fill","index":2,"n_rows":67,"n_cols":8,"rows_kept":67,"cut":false,"hidden":false,"merged":[]}
["Save time by filling cells automatically"]
["Here’s how to use the fill feature in Excel:"]
["Cells C3 through G7 contain data with five columns: \"This:\" column, which contains the number 50 in each cell; \"Plus this:\" column, which contains the numbers 50, 60, 70, and 80; \"Equals:\" column, with cell E4 containing a sum of cells C4 and D4; \"Plus this:\" column, which contains the number 75 in each cell; and \"Equals:\" column with cell G4 containing the sum of cells E4 and F4.",null,"This:","Plus this:","Equals:","Plus this:","Equals:"]
["Go to cell E4. Press CTRL+G, type E4, then press Enter. ",null,50,50,{"$f":"SUM(C4:D4)","v":100},75,{"$f":"SUM(E4:F4)","v":175}]
["Select cells E4, E5, E6, and E7 by holding the SHIFT key while pressing the ARROW DOWN key, then press CTRL+D. Excel will automatically fill the cells with the totals: 110, 120, and 130. People call this “filling down.”",null,50,60,null,75]
["EXTRA CREDIT: Go to cell G4 and repeat the fill down steps above.",null,50,70,null,75]
["Cells C10 through G14 contain data with five columns. These columns contain the heading from C3 to G3 and the values from cells C4 to G7 after executing steps in cells A5 and A6.",null,50,80,null,75]
["Go to cell C15. Select cells C15, D15, E15, F15, and G15. This time press CTRL+R to fill the cells. This is known as “filling right.”"]
["Dive down for more detail: Go to cell A27. Or, to proceed to the next step, press CTRL+PAGE DOWN."]
[null,null,"This:","Plus this:","Equals:","Plus this:","Equals:"]
[null,null,50,50,{"$f":"SUM(C11:D11)","v":100},75,{"$f":"SUM(E11:F11)","v":175}]
[null,null,50,60,{"$f":"SUM(C12:D12)","v":110},75,{"$f":"SUM(E12:F12)","v":185}]
[null,null,50,70,120,75,195]
[null,null,50,80,130,75,205]
[null,null,{"$f":"SUM(C11:C14)","v":200}]
{"$rows_empty":11}
["Use the fill handle to copy cells"]
["Sometimes you don't need the numbers to change as you fill. Instead, you just want to copy values to other adjacent cells. Here's how to do that:"]
["Cells C33 to cell F37 contain four columns: Department, Category, Product, and Count. "]
["Go to cell C34. Select C34, C35, C36, C37 and press CTRL+D. The value in C34 is filled down into the selected cells."]
["Go to cell A64 for the next instruction."]
[]
[null,null,"Dept.","Category","Product","Count"]
[null,null,"Produce","Fruit","Apple",100]
[{"$nulls":4},"Orange",200]
[{"$nulls":4},"Banana",50]
[{"$nulls":4},"Pears",100]
{"$rows_empty":8}
[null,null,null,"Jan"]
[null,null,"Week 1",35,44,79,null,"Qrtr 1"]
[null,null,null,74,64,56]
[null,null,null,82,50,83]
[null,null,null,90,22,89]
{"$rows_empty":9}
[null,null,"Intervals"]
[null,null,15,30]
[]
[]
["More information on the web"]
["Fill data automatically in worksheet cells"]
["Fill a formula down into adjacent cells"]
["Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN."]

{"sheet":"3. Split","index":3,"n_rows":86,"n_cols":8,"rows_kept":86,"cut":false,"hidden":false,"merged":[]}
["Data stuffed into one column? Split it."]
["Go to cell D5. Press CTRL+G, type D5, then press Enter. Type the first name that is in the Email column in cell C5: Nancy."]
["Go to cell D6. Press CTRL+E, a shortcut for Flash Fill."]
["Flash Fill detects when you type a consistent pattern, and fills the cells once the pattern is detected. ",null,"Email","First name","Last name"]
["Try another way to Flash Fill: Go to cell E5.",null,"Nancy.Smith@contoso.com",null,"Smith"]
["Press ALT+H to enter the Home tab above the ribbon, then press FI to select Fill options. Arrow down to select Flash Fill from the list or press F. Now the last names are in their own column, from cells E5 through E9.",null,"Andy.North@fabrikam.com"]
["Dive down for more detail: Go to cell A27. Or, to proceed to the next step, press CTRL+PAGE DOWN.",null,"Jan.Kotas@relecloud.com"]
[null,null,"Mariya.Jones@contoso.com"]
[null,null,"Yvonne.McKay@fabrikam.com "]
{"$rows_empty":17}
["Split a column based on delimiters."]
["Flash Fill is pretty handy. But if you want to split data into more than one column all at once, then it's not the best tool for the job. Try Text to Columns in this situation:"]
["Go to cell C32. Select all cells C32 through C39: Nancy all the way down to Yvonne. "]
["Press ALT+A to enter the Data tab above the ribbon, then press E to select Text to Columns within the Data Tools section. Convert Text to Columns Wizard - Step 1 of 3 appears. Make sure that Delimited radio button is selected, then press Enter. Use the Tab key to navigate the dialogue."]
["Convert Text to Columns Wizard - Step 2 of 3: Tab to find the Comma option under Delimiters. Make sure that Comma is the only checkbox selected, then tab to select Next and press Enter.",null,"Data","First name","Last name","Company name"]
["Convert Text to Columns Wizard - Step 3 of 3: press Tab and select only the General option. ",null,"Nancy,Smith,Contoso Ltd."]
["Finally, press tab until you enter the Destination text box. Type $D$32, then press Enter.",null,"Andy,North,Fabrikam Inc."]
["WORTH EXPLORING: There's another way of working with data. You can query an external source, and you can split the data that comes from the source. You do that once, and the data is refreshable and easy to work with from that moment on. Curious? Select the Data tab (ALT+A), then explore the options in the Get & Transform area (press one of the following: PN, FT, FW, PT, PR, or X). Or, go to cell A80 for More information on the web.",null,"Jan,Kotas,Relecloud"]
["Go to cell A49 for the next instruction.",null,"Mariya,Jones,Contoso Ltd."]
[null,null,"Steven,Thorpe,Relecloud"]
[null,null,"Michael,Neipper,Fabrikam Inc."]
[null,null,"Robert,Zare,Relecloud"]
[null,null,"Yvonne,McKay,Contoso Ltd."]
{"$rows_empty":9}
["Split a column with formulas"]
["You might want to write a formula to split data. This way, if the original data gets updated, then the split data will get updated as well. This is more advanced. But it is possible when using a handful of functions: LEFT, RIGHT, FIND, and LEN. For more information on each these functions, see the links at the bottom of this sheet under More information on the web starting in cell A80. But if you’re curious, here’s how we split cell C56."]
["Go to cell E56: Yvonne.  We used the LEFT function to extract characters from the left side of cell C56. And to specify the number of characters to extract, we used the FIND function. Here's how the formula \"=LEFT(C56 comma FIND(\" \" comma C56)-1)\" works:"]
["The left function extracts a specified number of characters from the left side of cell C56.\n\n"]
["The Find function is used to determine the number of characters to extract. Here's how the Find function works: Find the character position number of the first space in cell C56. Then subtract 1 to exclude the space itself.\n"]
["The result is Yvonne."]
["Then we created a [Helper column]. This was just to “help” extract the other text in the cell. It’s meant to be temporary and something one could always hide later. ",null,"Name inside one cell",null,"First name","[Helper column]","Middle name","Last name"]
["Select cell F56: Francis McKay in the [Helper column]. You’ll see that we used the RIGHT, LEN, and FIND functions to extract characters from the first space in cell C56, up until the end of the cell. ",null,"Yvonne Francis McKay",null,{"$f":"LEFT(C56,FIND(\" \",C56)-1)","v":"Yvonne"},{"$f":"RIGHT(C56,LEN(C56)-FIND(\" \",C56))","v":"Francis McKay"},{"$f":"LEFT(F56,FIND(\" \",F56)-1)","v":"Francis"},{"$f":"RIGHT(F56,LEN(F56)-FIND(\" \",F56))","v":"McKay"}]
["Here's how this formula \"=RIGHT(C56 comma LEN(C73)-FIND(\" \" comma C56))\" works:"]
["The Right function extracts a specified number of characters from the right side of cell C56."]
["In this case, the LEN function is used to determine the number of characters to extract. Here's how the LEN function works: Count the number of characters in cell C56 and subtract the number of characters from the Find function, which finds the character position number of the first space in cell C56 and returns the number of characters up until the space. "]
["The result is Francis McKay."]
["Select cell G56: Francis. Here we used almost the same formula as in cell A51, but instead of extracting characters from cell C56, it extracts them from cell F56. \n"]
["Select cell H56: McKay. This is the same formula as in step A56, but it extracts characters from F56 instead of cell C56. "]
["Go to cell A79 to go to the next instruction."]
{"$rows_empty":15}
["More information on the web"]
["Split text into different columns"]
["All about Get & Transform "]
["All about the LEFT function"]
["All about the RIGHT function"]
["All about the FIND function"]
["All about the LEN function"]
["Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN."]

{"sheet":"4. Transpose","index":4,"n_rows":76,"n_cols":8,"rows_kept":76,"cut":false,"hidden":false,"merged":[]}
["Switch data around by transposing it"]
["When you need to rotate columns and rows, you transpose them in Excel."]
["Cells C5 to H6 contain two rows of Items and Amounts. Select cells C5 to H6."]
["Now you'll copy the cells. Press CTRL+C."]
["Select cell C9.",null,"Item","Bread","Donuts","Cookies","Cakes","Pies"]
["Press ALT+H to enter the Home tab above the ribbon, then press V to select Paste options. Arrow down or press S to select Paste Special. ",null,"Amount",50,100,40,50,20]
["Press Tab until you find Transpose. Press the Space Bar to select Transpose, then press Enter."]
["EXPERT TIP: The shortcut key for Paste Special is CTRL+ALT+V. \n"]
["Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN."," to proceed to the next step"]
{"$rows_empty":17}
["Transpose with a formula"]
["Sometimes you don't want to copy and paste to transpose. In this case, you can use a formula to transpose rows and columns. Here's how to do that:"]
["To transpose this data, you need to select some blank cells first. Since the data in cells C33 to H34 on the right has six columns and two rows, you need to select the opposite: two columns and six rows. Do this by selecting cells C40 to D45. "]
["This is kind of tricky, so pay close attention. With those cells still selected, type the following: =TRANSPOSE(C33:H34) but don’t press Enter. Instead press CTRL+SHIFT+ENTER. If you get an error or #VALUE! as a result, try again starting at instruction in cell A29. \n"]
["Select any of the transposed cells, for example cell C41. Look at the formula at the top of Excel. You’ll see that the formula looks like this: {=TRANSPOSE(C33:H34)}"]
["Select  another transposed cell from cells C40 to D45, for example cell D43. Look at the formula bar again. The formula is the same as in cell C41. Why? Because this is an array formula.\n"]
["Go to cell A54 for the next instruction.",null,"Item","Bread","Donuts","Cookies","Cakes","Pies"]
[null,null,"Amount",50,100,40,50,20]
{"$rows_empty":19}
["What's an array formula?"]
["An array formula can perform calculations on more than one cell in an array. In the example above, the array is the original data set is cells C33:H34. The TRANSPOSE function then switches the horizontal orientation of the cells to a vertical orientation. "]
["You always finish an array formula with CTRL+SHIFT+ENTER, not just ENTER. Pressing CTRL+SHIFT+ENTER calculates the function against the array. When you're done, Excel puts special brackets { } around the formula. These brackets are a visual clue that the selected cell is part of an array formula. You can't type these brackets yourself. Excel puts them in when you press CTRL+SHIFT+ENTER. "]
["KEEP IN MIND…\nThere are three things to keep in mind when using an array formula: \n1) Always select multiple cells first, and then with those cells selected, start typing the array formula. That's the key: Select multiple cells first, then start typing.\n2) When you're done typing an array formula, press  CTRL+SHIFT +ENTER.\n3) Once you enter an array formula, you cannot interrupt that new array. For example, you cannot type over or delete just one of the cells. You also cannot insert a new row or column within that array.  If you need to any of that, select all of the cells that have the array formula, press Delete, and then make your changes and recreate the formula."]
["EXCEL SPEAK: Because array formulas require CTRL+SHIFT+ENTER, some people informally call array formulas, \"CSE formulas.\" \n"]
["Go to cell A72 for the next instruction."]
{"$rows_empty":12}
["More information on the web"]
["Transpose (rotate) data from rows to columns or vice versa"]
["All about the TRANSPOSE function"]
["Create an array formula"]
["Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN."]

{"sheet":"5. Sort & filter","index":5,"n_rows":63,"n_cols":7,"rows_kept":63,"cut":false,"hidden":false,"merged":[]}
["Sort and filter with ease"]
["Cells C5 to G13 contain five columns: Departments, Categories, and Amounts for the months Oct, Nov, Dec."]
["Let's say you want the departments in alphabetical order. Select the Department column, go to cell C5. Press 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 options. Use the arrow keys to find the option to Sort A to Z, or press S, then press Enter. "]
["Sort December's amounts from big to small. Select the December column cell, go to G5, then select cells G5 through G13. Press ALT+H to enter the Home tab above the ribbon, then press S for Sort & Filter options. 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 Largest to Smallest, then press Enter. "]
["Now you'll filter the data so that only the Bakery rows appear. Go to cell G5, Dec. Press CTRL+A to select all of the cells, then press ALT+H to enter the Home tab. Press S to enter the Sort & Filter options, then use the arrow keys to find the Filter option or press F.",null,"Department","Category","Oct","Nov","Dec"]
["Filter buttons appear on the top row from cell C5 to G5. Go to the Department cell, C5, and press ALT+DOWN ARROW then DOWN ARROW and SPACE BAR to clear the Select All checkbox. Then, use the arrow keys to find Bakery and press Space Bar, then press Enter.",null,"Meat","Beef",90000,110000,120000]
["EXTRA CREDIT: Try sorting alphabetically by two columns. Here's how: First sort Department alphabetically (refer to steps in cell A3 above). Then select the Home tab and Sort & Filter options. Find Custom Sort and add a second level for Category. After you select OK, Department will be sorted, and within each department, Category rows will be sorted in alphabetical order as well. ",null,"Bakery","Desserts",25000,80000,120000]
["Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN.",null,"Produce","Fruit",10000,30000,40000]
[null,null,"Produce","Veggies",30000,80000,30000]
[null,null,"Deli","Salads",90000,35000,25000]
[null,null,"Meat","Chicken",75000,82000,2000000]
[null,null,"Bakery","Breads",30000,15000,20000]
[null,null,"Deli","Sandwiches",80000,40000,20000]
{"$rows_empty":13}
["Sort by date, or by color even"]
["There are many ways to sort in Excel. Here are just two more ways to sort:"]
["Cells C31 through F31 contain data with four columns: Expense date, Employee, Food, and Hotel."]
["You 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 Sort Oldest to Newest. Press Enter. The rows get sorted in ascending date order by the Expense date."]
["Someone filled three cells with yellow. You can sort the rows by that color. Go to cell F31, then press ALT+DOWN ARROW and use the arrow keys to find the Sort by Color option.  Press Right Arrow to select the highlight color \"yellow\" rgb color 255, 255, 0, then Press Enter. The highlighted cells are automatically sorted to the top within the column. To verify this, go to cell F32 and press INS+F to hear the cell's format. ",null,"Expense date","Employee","Food","Hotel"]
["IMPORTANT DETAIL: You can't clear a sort order like you can a filter. So if you don't want your sort to stick, undo it by pressing CTRL+Z.\n",null,{"$f":"TODAY()-2","v":"2017-09-03"},"Jackie",21,3820]
["Go to cell A43 for the next instruction.",null,{"$f":"TODAY()-3","v":"2017-09-02"},"Mark",62,2112]
[null,null,{"$f":"TODAY()-6","v":"2017-08-30"},"Dave",25,1611]
[null,null,{"$f":"TODAY()","v":"2017-09-05"},"Tricia",30,3085]
[null,null,{"$f":"TODAY()-4","v":"2017-09-01"},"Jeff",69,528]
[null,null,{"$f":"TODAY()-5","v":"2017-08-31"},"Laura",45,5050]
[]
[]
[]
[]
[]
["More ways to filter data"]
["Cells C49 through F49 contain data with four columns: Expense date, Employee, Food, and Hotel."]
["Go to cell F49: Hotel. Press ALT+DOWN ARROW, then use the arrow keys to find Number Filters option. Press RIGHT ARROW to enter Number Filters list and use the arrow keys to find the Above Average option, then press Enter. Excel calculates the average amount of the Hotel column, and then shows only rows with amounts greater than that average. "]
["Now 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 RIGHT ARROW to enter the Number Filters list. Use the arrow keys to find the Greater than... option, then type 25 and press Enter. Of the three rows that were filtered for above average, Excel shows two rows with Food amounts greater than 25"]
["Go to cell A60 for the next instruction."]
[]
[null,null,"Expense date","Employee","Food","Hotel"]
[null,null,{"$f":"TODAY()-2","v":"2017-09-03"},"Jackie",21,3820]
[null,null,{"$f":"TODAY()-3","v":"2017-09-02"},"Mark",62,2112]
[null,null,{"$f":"TODAY()","v":"2017-09-05"},"Tricia",30,3085]
[null,null,{"$f":"TODAY()-6","v":"2017-08-30"},"Dave",25,1611]
[null,null,{"$f":"TODAY()-5","v":"2017-08-31"},"Laura",45,5050]
[null,null,{"$f":"TODAY()-4","v":"2017-09-01"},"Jeff",69,528]
[]
[]
[]
[]
["More information on the web"]
["Sort data in a range or table"]
["Filter data in a range or table"]
["Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN."]

{"sheet":"6. Tables","index":6,"n_rows":76,"n_cols":8,"rows_kept":76,"cut":false,"hidden":false,"merged":[]}
["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 Enter."]
["Press 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."]
["Now you have a table, which is a collection of cells that has special features. For starters: A table gives you banded rows for easier reading.",null,"Department","Category","Oct","Nov","Dec"]
["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 for the table appears. ",null,"Produce","Veggies",30000,80000,30000]
["You can also create columns easily:  Go to any cell between H5 and H14, for example H10. Type some text, then press Enter. A new column for the table appears. Repeat the process to add a new column in column I.",null,"Produce","Fruit",10000,30000,40000]
["Notice how the two columns are created, formatted, and the text Jan and Feb are filled in cells H5 and I5 for you.",null,"Bakery","Breads",30000,15000,20000]
["EXTRA CREDIT: Try changing the table style. First select a cell inside the table between cells C5 and I14. The Table Tools Design tab will appear at the top of Excel. Press ALT+JT to enter the Design tab above the ribbon, then press S to enter Table Styles. Use arrow keys to navigate the options and select a table style you like.\n",null,"Bakery","Desserts",25000,80000,120000]
["Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN.",null,"Deli","Sandwich",80000,40000,20000]
[null,null,"Deli","Salads",90000,35000,25000]
[null,null,"Meat","Beef",90000,110000,200000]
[null,null,"Meat","Chicken",75000,82000,150000]
{"$rows_empty":13}
["Calculated columns in tables"]
["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:"]
["Cells C33 through H41 contain data with six columns: Department, Category, Oct, Nov, Dec, and Total."]
["Go to cell H34: Total."]
["Press ALT+=, then press Enter."]
["The SUM formula gets automatically filled down for you so that you don’t have to do it yourself. "]
["Go to cell A47 for the next instruction.",null,"Department","Category","Oct","Nov","Dec","Total"]
[null,null,"Produce","Veggies",30000,80000,30000]
[null,null,"Produce","Fruit",10000,30000,40000]
[null,null,"Bakery","Breads",30000,15000,20000]
[null,null,"Bakery","Desserts",25000,80000,120000]
[null,null,"Deli","Sandwiches",80000,40000,20000]
[null,null,"Deli","Salads",90000,35000,25000]
[null,null,"Meat","Beef",90000,110000,200000]
[null,null,"Meat","Chicken",75000,82000,150000]
[]
[]
[]
[]
[]
["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 of a switch. And the same goes for the AVERAGE formula, and many others. Here’s how it works:"]
["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."]
["At the top of the Excel window, the Table Tools Design tab will appear. Press ALT+JT to enter the Design tab above the ribbon, then press T to select Total Row from within the Table Styles Options."]
["A new row is added at the bottom of the table in cells C62 through E62."]
["The total of $24,000 is added to the total row, in cell E62 . ",null,"Department","Category","Sales"]
["But what if you wanted to know the average? Select cell E62: $24,000.",null,"Produce","Veggies",1000]
["Press ALT+DOWN ARROW, then use the arrow keys to find the Average option and press Enter. The average amount of $3,000 appears.",null,"Produce","Fruit",2000]
["GOOD TO KNOW: There's a shortcut for showing and hiding the total row. Select inside the table, then press CTRL+SHIFT+T.\n",null,"Bakery","Breads",3000]
["Go to cell A72 for the next instruction.",null,"Bakery","Desserts",1000]
[null,null,"Deli","Sandwiches",2000]
[null,null,"Deli","Salads",3000]
[null,null,"Meat","Beef",4000]
[null,null,"Meat","Chicken",8000]
{"$rows_empty":10}
["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."," press CTRL+PAGE DOWN."]

{"sheet":"7. Drop-downs","index":7,"n_rows":63,"n_cols":6,"rows_kept":63,"cut":false,"hidden":false,"merged":[]}
["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 with two columns: Food and Department.",null,"Food","Department"]
["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.",null,"Apples"]
["Go to cell D4. Press CTRL+G, type D4, then press Enter. Select all cells from D4 to D15.",null,"Beef"]
["On the Data tab, select Data Validation, or press ALT+DL to open the Data Validation dialogue box. Tab to Allow and select List. Press the tab key again.",null,"Bananas"]
["In the Source text box, type Produce, Meat, Bakery. Make sure to put commas in between each name. Press Enter when you’re done.",null,"Lemons"]
["Now select cell D4, which is the cell next to Apples in C4. Press ALT+DOWN ARROW. You'll see a drop-down menu with the three items you added: Produce, Meat, and Bakery.",null,"Broccoli"]
["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. \nThere 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 selecting the link in cell A61.",null,"Kale"]
["Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN.",null,"Ham"]
[null,null,"Bread"]
[null,null,"Chicken"]
[null,null,"Cookies"]
[null,null,"Cakes"]
[null,null,"Pies"]
{"$rows_empty":11}
["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, 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:"]
["Cells C31 through D43 contain data with two columns: Food and Department. Cells F31 through F34 contain data with one column: Department."]
["From cells F31 to F34, select a cell with a department. For example, select cell F33: Meat. "]
["Create a table by pressing CTRL+T, then Enter.",null,"Food","Department",null,"Department"]
["Now you’ll set up the data validation again. Under cell D31: Department, select all of the blank cells from D32 through D43.",null,"Apples",null,null,"Produce"]
["On the Data tab, select Data Validation, or press ALT+DL to open the Data Validation dialogue box. Tab to Allow and press DOWN ARROW to select List. Press the tab key again.",null,"Beef",null,null,"Meat"]
["In the Source text box, type =$F$32:$F$34, then press Enter.",null,"Bananas",null,null,"Bakery"]
["You selected the values within the single column starting in cell F31: Department.",null,"Lemons"]
["Now go to cell D32 and press ALT+DOWN ARROW. There are only three departments in the drop-down list: Produce, Meat and Bakery. But if you add a new department in column F under cell F35: Bakery, this list will get updated with the new department. Try it.",null,"Broccoli"]
["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.\n",null,"Kale"]
["Go to cell A60 for the next instruction.",null,"Ham"]
[null,null,"Bread"]
[null,null,"Chicken"]
[null,null,"Cookies"]
[null,null,"Cakes"]
[null,null,"Pies"]
{"$rows_empty":16}
["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."]

{"sheet":"8. Analyze","index":8,"n_rows":71,"n_cols":7,"rows_kept":71,"cut":false,"hidden":false,"merged":[]}
["Analyze data quickly"]
["Here’s how to analyze data so that you can spot patterns and trends quickly:"]
["Cells C5 through G13 contain data in five columns: Department, Category, Oct, Nov, and Dec."]
["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 appears."]
["Press Tab key to enter Formatting options, then press Enter to select Data Bars.",null,"Department","Category","Oct","Nov","Dec"]
[" The cells under Oct, Nov, and Dec columns, cells E6 through G13 get special data bars that visualize their amounts.",null,"Produce","Veggies",30000,80000,30000]
["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 bring up the Quick Analysis panel again.",null,"Produce","Fruit",10000,30000,40000]
["Press Tab key to enter Formatting options, then press RIGHT ARROW to find Clear…, then press Enter.",null,"Bakery","Breads",30000,15000,20000]
["GOOD TO KNOW: When you select cells, this Quick Analysis button appears. Aptly named, don't you think? You can always access the button by using the shortcut key: CRTL+Q. If you ever have a question about the data you selected, select this option and see if it gives you some answers. ",null,"Bakery","Desserts",25000,80000,120000]
["Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN.",null,"Deli","Sandwich",80000,40000,20000]
[null,null,"Deli","Salads",90000,35000,25000]
[null,null,"Meat","Beef",90000,110000,200000]
[null,null,"Meat","Chicken",75000,82000,150000]
{"$rows_empty":13}
["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. This time though, we'll use the keyboard shortcut:"]
["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."]
["The Quick Analysis panel appears. Press RIGHT ARROW until you find Charts."]
["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 each product has three columns, one for each month of sales: Oct, Nov, and Dec."]
["Go to cell A47 for the next instruction.",null,"Department","Category","Oct","Nov","Dec"]
[null,null,"Bakery","Breads",30000,15000,20000]
[null,null,"Bakery","Desserts",25000,80000,120000]
[null,null,"Deli","Sandwiches",80000,40000,20000]
[null,null,"Deli","Salads",90000,35000,25000]
[null,null,"Meat","Beef",90000,110000,200000]
[null,null,"Meat","Chicken",75000,82000,150000]
[null,null,"Produce","Veggies",30000,80000,30000]
[null,null,"Produce","Fruit",10000,30000,40000]
[]
[]
[]
[]
["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. You don't have to make 8 little line charts. You can make sparklines instead."]
["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."]
["On the Quick Analysis panel that appears, press the RIGHT ARROW until you find Sparklines, then press Tab to select the Line option. Press Enter to add Sparklines to the table."]
["Sparklines appear to the right of the Dec column in cells H55 through H62. Each line represents the data for that row, and shows whether the amounts go up or down."]
["To 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 the Clear option, then press C again to select to Clear Selected Sparklines."]
["Go to cell A68 for the next instruction.",null,"Department","Category","Oct","Nov","Dec"]
[null,null,"Bakery","Breads",30000,15000,20000]
[null,null,"Bakery","Desserts",25000,80000,120000]
[null,null,"Deli","Sandwiches",80000,40000,20000]
[null,null,"Deli","Salads",90000,35000,25000]
[null,null,"Meat","Beef",90000,110000,200000]
[null,null,"Meat","Chicken",75000,82000,150000]
[null,null,"Produce","Veggies",30000,80000,30000]
[null,null,"Produce","Fruit",10000,30000,40000]
[]
[]
[]
[]
[]
["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."]

{"sheet":"9. Charts","index":9,"n_rows":73,"n_cols":6,"rows_kept":73,"cut":false,"hidden":false,"merged":[]}
["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 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 Columns, then press Enter.",null,"Year","Conference attendance"]
["A column chart appears showing total number of conference attendees per year. Use the arrow keys to move the chart anywhere you'd like.",null,{"$f":"YEAR(TODAY())-5","v":2012},500]
["Now you'll add a trendline. Select the chart you just created, and press ALT+JC to enter the Chart Tools Design tab above the ribbon. ",null,{"$f":"YEAR(TODAY())-4","v":2013},800]
["Press A to Add chart element, then press DOWN ARROW to find the Trendline option. Press RIGHT ARROW to open the Trendline options then DOWN ARROW to navigate to the Linear option, then press Enter. Now you have a trendline that shows the general direction of the units sold over time.",null,{"$f":"YEAR(TODAY())-3","v":2014},1000]
["EXTRA CREDIT: Want a data table directly under the chart? Select the chart. Press ALT+JC, to enter the Chart Tools Design tab. Then press A to Add Chart Element. Press DOWN ARROW to find the Data Table option then press RIGHT ARROW to open the Data Table options. Press DOWN ARROW until you find the With Legend Keys option. Select With Legend Keys, then press Enter to add Legend Keys to the chart.\n",null,{"$f":"YEAR(TODAY())-2","v":2015},900]
["Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN.",null,{"$f":"YEAR(TODAY())-1","v":2016},1000]
[null,null,{"$f":"YEAR(TODAY())","v":2017},1200]
{"$rows_empty":15}
["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 something different. "]
["In Excel this is what they are called:"]
["• 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 in the chart on the right starting in cell D30 is a value axis. "]
["• A category axis represents things like dates, people names, product names. The horizontal axis in the chart on the right starting in cell D30 has years so this is a category axis. "]
["Go to cell A52 for the next instruction."]
{"$rows_empty":16}
["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 the other value axis."]
["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 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, select the hyperlink in cell A70."]
["Cells D67 through F73 contain data with three columns: Date, Conference attendance, and Food sales. Food Sales column contains data that supports the secondary axis for the chart described above."]
["EXTRA CREDIT: Try making a combo chart. Select the entire table from cells D67 through F73. Use the Quick Analysis option, CTRL+Q, to find the Charts option. Press Tab to enter the Charts options, then press RIGHT ARROW to select More... . The Recommended Charts options appear. Press RIGHT ARROW to select the All Charts tab, then press DOWN ARROW until you find the Combo option, listed at the bottom. Tab twice to enter the Series name: . Press DOWN ARROW twice to find \"Food Sales\", then press Tab twice to select the Secondary Axis option. Press SPACE BAR to enable this option, then press Enter. \n"]
["Go to cell A68 for the next instruction."]
{"$rows_empty":9}
[null,null,null,"Date","Conference attendance","Food sales"]
["More information on the web",null,null,{"$f":"YEAR(TODAY())-5","v":2012},500,5000]
["Create a chart from start to finish",null,null,{"$f":"YEAR(TODAY())-4","v":2013},800,11200]
["Create a combo chart with a secondary axis",null,null,{"$f":"YEAR(TODAY())-3","v":2014},1000,30000]
["Available chart types in Office",null,null,{"$f":"YEAR(TODAY())-2","v":2015},900,25000]
["Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN.",null,null,{"$f":"YEAR(TODAY())-1","v":2016},1000,5000]
[null,null,null,{"$f":"YEAR(TODAY())","v":2017},1200,8000]

{"sheet":"10. PivotTables","index":10,"n_rows":61,"n_cols":6,"rows_kept":61,"cut":false,"hidden":false,"merged":[]}
["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 profitable? Or which salesperson is the leading seller? That’s where the PivotTable in cells E11 through F15 can help.",null,"Date","Salesperson","Product","Amount"]
["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. ",null,{"$f":"TODAY()-57","v":"2017-07-10"},"Anne","Beer",1400]
["Next you’ll pivot the data so that you can find out which salesperson is the leading seller.  Select any cell inside the PivotTable, for example go to cell E12. Press CTRL+G, type E12, then press Enter. The PivotTable Fields pane may open on the right of the Excel Window. ",null,{"$f":"TODAY()-52","v":"2017-07-15"},"Mark","Wine",1010]
["Press SHIFT+F6 until  you enter the PivotTable Fields pane or press ALT+JT, then L to launch the PivotTable Fields pane. Focus will default to the Search text box: Type words to search for edit. Do not type anything, instead, tab twice to get through the categories, then tab once more to enter the Rows section of the Fields pane. Products is selected. Press Spacebar to enter the context menu, then press DOWN ARROW until you find Remove field. Press Enter.",null,{"$f":"TODAY()-35","v":"2017-08-01"},"Anne","Beer",750]
["Now, press Tab three times to enter the categories list: Date, Salesperson, Product and Amount . Select the checkbox for Salesperson. Note, you may have to UP ARROW to find the Salesperson checkbox. Once selected you can now see who’s the leading salesperson in cells E11 through F16.",null,{"$f":"TODAY()-31","v":"2017-08-05"},"Mark","Soda",510]
["Dive down for more detail: Go to A27. Or, to proceed to the next step, press CTRL+PAGE DOWN.",null,{"$f":"TODAY()-11","v":"2017-08-25"},"Mariya","Soda",1600]
[null,null,{"$f":"TODAY()","v":"2017-09-05"},"Laura","Wine",680]
[]
[{"$nulls":4},"Row Labels","Sum of Amount"]
[{"$nulls":4},"Beer",2150]
[{"$nulls":4},"Soda",2110]
[{"$nulls":4},"Wine",1690]
[{"$nulls":4},"Grand Total",5950]
{"$rows_empty":11}
["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 V to insert a PivotTable."]
["A Create PivotTable dialogue appears. Focus is on Select a table or range. Leave this radio button option selected press Tab to choose where you want the PivotTable report to be placed. The default option is selected: New Worksheet. Press DOWN ARROW to select Existing Worksheet. Press Tab to enter the Location text box and type C42, then press Enter."]
["The PivotTable Fields pane appears on the right. Press SHIFT+F6 until you come to  the Search text box: Type words to search for edit. "]
["Press Tab to enter the list of categories. Press DOWN ARROW to find Product checkbox. Press Spacebar to select Product.\nWhen 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."]
["Now press DOWN ARROW to find Amount checkbox. \nWhen 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.\n",null,"Date","Salesperson","Product","Amount"]
["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.",null,{"$f":"TODAY()-57","v":"2017-07-10"},"Anne","Beer",1400]
["Go to cell A58 for the next instruction.",null,{"$f":"TODAY()-52","v":"2017-07-15"},"Mark","Wine",1010]
[null,null,{"$f":"TODAY()-35","v":"2017-08-01"},"Anne","Beer",750]
[null,null,{"$f":"TODAY()-31","v":"2017-08-05"},"Mark","Soda",510]
[null,null,{"$f":"TODAY()-11","v":"2017-08-25"},"Mariya","Soda",1600]
[null,null,{"$f":"TODAY()","v":"2017-09-05"},"Laura","Wine",680]
{"$rows_empty":17}
["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."]

{"sheet":"Learn more","index":11,"n_rows":7,"n_cols":1,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
["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.\n"]
["Community: Ask questions and connect with other Excel fans.\n"]
["What else is new?\nOffice 365 subscribers get continual updates and new features.\n\n"]
["Give us feedback on this tour"]