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

Manage the display of data

When a worksheet contains a large amount of data, it can be challenging to review the data, especially on a small screen such as that of the iPad. If you need to keep all the data at hand, you can rotate the iPad to display more columns or more rows at the same magnification; hide headings, worksheet tabs, and other user interface elements to increase the space available for the worksheet; or zoom out to display more content in the app window. You can freeze the column and row labels so they stay visible—and identify the on-screen content—while you flick through the data range.

If you’re focusing on specific data, you can hide columns and rows that you don’t need to review. To really narrow things down, you can hide data that isn’t relevant to your needs by filtering it, and then present different aspects of the data for evaluations by changing the sort order.

Freeze panes

When a worksheet contains more data than you can display on one screen, you must scroll vertically or horizontally to display additional fields and entries. When you scroll a worksheet that contains a data range, the lettered column headings and numbered row headings can help you to identify the visible data, but it’s easy to lose track of specific fields or entries. To simplify this process, you can “freeze” the columns and rows that contain labels so they stay in place when you flick through a worksheet.

For a typical data range that starts in the upper-left corner of a worksheet (cell A1), the top row contains the column labels and the first column contains the row labels. Because this is common, Excel provides options to freeze the top row and the first column. Alternatively, you can select the first cell that you want to scroll and then choose the option to freeze the worksheet panes above and to the left of that.

Frozen panes are indicated by thin lines on the worksheet that start between the column headings or row headings. When the display of gridlines is turned off, the lines are visible in the worksheet background.

To freeze the panes to the left of and above a specific cell

1. Position the worksheet so that the rows you want to have visible after you freeze the panes are the first rows in the window.

   IMPORTANT

   

   In Excel for iPad, freezing rows prevents the frozen rows from scrolling, so if you want to have multiple rows visible when scrolling, ensure that they are exposed before you freeze the rows.

2. Select the first cell that you want to scroll (this cell will not be frozen).
3. On the View tab, tap Freeze Panes. Then on the Freeze Panes menu, tap Freeze Panes.

To freeze the first visible column

1. Position the worksheet so that the one column you want to freeze as you scroll horizontally is the first column in the window.
2. On the View tab, tap Freeze Panes. Then on the Freeze Panes menu, tap Freeze First Column.

To freeze the first visible row

1. Position the worksheet so that the one row you want to freeze as you scroll vertically is the first row in the window.
2. On the View tab, tap Freeze Panes. Then on the Freeze Panes menu, tap Freeze Top Row.

To unfreeze panes

1. On the View tab, tap Freeze Panes.
2. On the Freeze Panes menu, tap the current selection, and then tap a blank area of the ribbon to close the menu.

Sort and filter data

A key feature of Excel is the ability to locate specific data or data that meets specific requirements. You can use the search function to locate specific text or characteristics and then move among the results one by one. For many purposes, however, it’s more useful to manipulate the data range to display data in a certain arrangement or to display only (and all) the records that share specific characteristics.

You can sort a data range or Excel table by the entries in any column to present the data in different ways. For example, if you have a list of products offered by different companies at different prices, you can sort the data by company name, by product name, or by price. Then you can narrow down the options by filtering the data to display only (and all) the records that share specific characteristics.

Filtering is off by default for data ranges, but you can easily turn it on. When you do, Excel evaluates the data and displays a Sort & Filter button at the right edge of each data column heading. The button label changes to indicate the column status, as follows:

• When a column is neither sorted nor filtered, the button is labeled with a downward-pointing triangle.
• When data is sorted by a specific column, the button is labeled with an arrow that points up to indicate an ascending sort order from smallest to largest (or A to Z) or down to indicate a descending sort order from largest to smallest (or Z to A).
• When the data range is filtered by a specific column, the button is labeled with a funnel-shaped symbol that represents a filter.

Filtering a data range by one or more columns displays the entire entry (row) that matches the filter criteria specified for the columns.

To display the Sort & Filter buttons for a data range

1. Select any cell in the data range.
2. On the Home tab, tap the Sort & Filter button, and then tap the Filter slider to change its background to green.

To sort a data range by a specific column

1. In the heading of the column that contains the sort criteria, tap the Sort & Filter button, and then tap Ascending or Descending.

Or

1. Select any cell in the column that contains the sort criteria.
2. On the Home tab, tap the Sort & Filter button.
3. On the Sort & Filter menu, tap Ascending or Descending.

To filter a data range by a specific column entry

1. Display the Sort & Filter buttons for the data range.
2. In the heading of the column that contains the filter criteria, tap the Sort & Filter button.
3. On the Sort & Filter menu, tap to select or clear the selection of values to be displayed.

To clear a filter

1. In the heading of the column that contains the filter criteria, tap the Sort & Filter button.
2. On the Sort & Filter menu, tap Clear Filter.