How to Store and Retrieve Data in Microsoft Excel for the iPad

Practice tasks

The practice files for these tasks are located in the iPadOfficeSBS\Ch07 folder.

Create workbooks

Start Excel, and then perform the following tasks:

1. Create a blank workbook, and then save the workbook on your iPad as My Blank Workbook.
2. Create a new workbook based on the built-in Movie List template.
3. Starting in cell C9, add information about your three favorite children’s movies to the table. Notice that Excel continues the banded row striping automatically.
4. Save the workbook on your iPad as My Movie Workbook.
5. Create a new workbook based on any of the Excel Online templates.
6. After Excel saves the workbook to your OneDrive, open it in Excel for iPad and notice the file name.
7. Save a duplicate copy of the workbook on your iPad as My Online Workbook. Then navigate from the Open page of the Backstage view to the Documents folder on your OneDrive and open the workbook that has the name you identified in step 6.
8. Verify that the open workbook is the one you created from the Office Online website.
9. On the Open page of the Backstage view, tap the File Actions button next to the workbook name and then follow the process to delete the open workbook from your OneDrive.

Create and manage worksheets

Open the ManageWorksheets workbook, and then perform the following tasks:

1. Review the information on the Month 1 worksheet.
2. Create a new worksheet after the Month 2 worksheet. Name the new worksheet Our Goals.
3. Insert two copies of the Month 1 worksheet as the last worksheets in the workbook. Name the worksheets Month 3 and Month 4.
4. Move the Our Goals worksheet to the right end of the sheet tab area, and then hide it.
5. On the Month 1 worksheet, hide the Formula Bar, gridlines, and headings. Then verify that the gridlines and headings are still visible on the other worksheets.
6. Redisplay the hidden worksheet, and then redisplay the Formula Bar.

Enter and edit data on worksheets

Open the EnterData workbook, and then perform the following tasks:

1. Review the information on the January worksheet. Then display the February worksheet.
2. In cell A9, add a new employee to the schedule by replacing Employee 5 with the name Jean.
3. Without leaving Edit mode, move to cell AG4 and insert a line break immediately before the word Days. Then complete the edit and return to Ready mode.
4. Move the content of cells M7:N7 to Q7:R7 so there are only two people out of the office on February 13th.
5. Extend Kathy’s vacation for the rest of the week by filling the pattern from Q7:R7 through to cell U7.
6. On the March worksheet, update cell A9 to add Jean to the schedule. Schedule an offsite training for Jean on the first weekday of the month by entering a T in cell C9 and completing the edit.
7. Cancel two of Susie’s vacation days by deleting the content of cells Q5:R5.

Modify columns and rows

Open the ManageStructure workbook, and then perform the following tasks:

1. Manually change the width of column B and the height of row 2 to more closely fit their content. Then use the AutoFit feature to make the column and row exactly the right sizes to fit their content.
2. Insert a new column to the left of column C. Enter Teacher in the column header.
3. Insert a copy of column E in columns F and G. Change the new column headers to Quarter 3 and Quarter 4, and then delete the grades from the new columns without clearing the formatting.
4. Move the Teacher column so it is between the Period and Class columns.
5. Insert two new rows above row 5. Enter Lunch in B5 and Recess in B6.
6. Hide the Lunch row. Then unhide the Lunch row and hide the Recess row instead.

Modify cells and cell content

Open the ManageCells workbook, and then perform the following tasks:

1. Review the Team Jerseys worksheet. This worksheet contains a list of team members, the number that appears on the back of each player’s uniform shirt, and a space to indicate the person who picked up the shirt from the coach. The entries are split into two sets of columns.
2. Change the number format in columns B and F to display whole numbers (without any decimal places).
3. The numbers printed on the players’ shirts are all two digits. Apply a number format that won’t remove leading zeros. Then enter a 0 before each number from 1 through 9.
4. Select the three cells that contain information about Jane. Insert a set of three cells above Jane’s (without deleting Jane’s information), and then enter the name Jaime in the new Player Name cell.
5. Cells E16:G17 contain two entries for the same girl, as evidenced by the matching names and shirt numbers. Delete the three cells in row 16 that contain information for Presley K, and shift the cells upward to fill the gap.
6. In the second set of columns, create space for two new entries in rows 14 and 15, below the entry for Mallory. Enter Marcella in row 14 and Mary in row 15.
7. Format cells C1 and G1 so that the column headings no longer wrap within the cells. Then use the AutoFit feature to size the columns to the minimum width required to fit the text.
8. Merge cells G10:G11, and enter Lola’s mom in the merged cell to indicate that she picked up both girls’ shirts. Then format the cell so its content is left-aligned like those above and below it.
9. Select cells A1:G31, and add a thick border around the outside of the selection.
10. Apply a cell fill color that you like to cells A1:G1. Then remove the fill from cell D1 so only the headings are shaded.

Manage the display of data

Open the DisplayData workbook, and then perform the following tasks:

1. Freeze rows 1 and 2. Then flick down and up through the worksheet to confirm that the two rows remain visible.
2. Freeze column A. Then flick right and left through the worksheet to confirm that the column remains visible.
3. Unfreeze the frozen rows and column, and then move the worksheet up in the app window so that cell A10 is the first cell visible in the upper-left corner of the worksheet. Freeze the panes to the left of and above cell B13, and then move around the worksheet to see the effect.
4. Select any cell in the Daily Living data range, and then display the Sort & Filter buttons for that data range.
5. Sort the Home data in ascending alphabetical order.
6. Filter the Daily Living data range to display only data related to child care, dining out, and dog walking.