Pro Office for iPad: How to Be Productive with Office for iPad (2014)

Chapter 8. Building and Formatting Worksheets

In this chapter, you will examine how to build and format worksheets quickly and efficiently in Excel for iPad. You will start by creating the structure of your workbook, inserting and deleting worksheets as needed, and renaming and rearranging them. You will move on to inserting and deleting rows, columns, and cells; setting column height and row width; and hiding any rows or columns you don’t want people to see. After that, you will dig into formatting cells and ranges, using the Find and Replace features, and sorting and filtering your data to show the records you need. You will finish by looking at how to work with comments and how to print all or part of a workbook.

Creating the Structure of Your Workbook

Excel for iPad gives each new blank workbook you create a single worksheet, but you can easily insert other worksheets as needed. If you find you have surplus worksheets, you can delete them. You can rename the worksheets with descriptive names, and you can reshuffle them into the order that makes most sense.

Inserting, Deleting, and Duplicating Worksheets

When you need a new worksheet, you can either insert a new worksheet after an existing worksheet or duplicate an existing worksheet so that you can reuse its content or formatting. You can delete any worksheet that you no longer need.

Inserting a Blank Worksheet

Here’s how to insert a new worksheet.

1. Tap the tab of the existing worksheet after which you want to position the new worksheet. (If the workbook contains only a single worksheet, you don’t need to do this.)

2. Tap the + button after the last worksheet in the Worksheets bar. Excel inserts the worksheet after the current worksheet, giving it a default name such as Sheet2.

3. Double-tap the default name to select it.

4. Type the name you want to give the worksheet (see Figure 8-1).

Figure 8-1. Type the name for the new worksheet and then tap the Done button

Note You can use the same technique to rename the first sheet (or any sheet) in a workbook: double-tap its current name to select it, type the new name, and then tap the Done button on the keyboard or simply tap in the worksheet.

Worksheet names can be up to 31 characters long, so you have enough space for several words. Excel for iPad displays only about the first 20 characters of longer names, truncating them with an ellipsis (…). So put the key words at the beginning of the name rather than the end to enable yourself to identify your worksheets easily. For example, the name “2015 Sales by Territory” works better than the name “Sales by Territory 2015” because you can see the year even when Excel truncates the name.

5. Tap the Done button on the keyboard.

Deleting a Worksheet

Here’s how to delete a worksheet.

1. Tap the worksheet’s tab to select it.

2. Tap the tab again to display the Edit menu (see Figure 8-2).

Figure 8-2. To delete a worksheet, tap the active worksheet’s tab, and then tap the Delete button on the Edit menu

3. Tap the Delete button. Excel displays the Delete the Selected Sheet? dialog box (see Figure 8-3).

Figure 8-3. Tap the Delete button in the Delete the Selected Sheet? dialog box to delete the selected worksheet and all its data

4. Tap the Delete button.

Tip If you delete the wrong worksheet, you can’t use Undo to recover it. Instead, tap the File button on the Ribbon, tap Restore, and then tap the latest version of the workbook to go back to that version. You’ll lose any other changes you’ve made since that version was saved, so be sure that recovering the deleted worksheet is worth this cost.

Duplicating a Worksheet

Often, you can save time by duplicating an existing worksheet and then making any necessary changes to the duplicate rather than creating a new worksheet and entering data on it.

Here’s how to duplicate a worksheet.

1. Tap the worksheet’s tab to select it.

2. Tap the tab again to display the Edit menu.

3. Tap the Duplicate button. Excel creates a duplicate of the worksheet, placing it before the original worksheet and adding “ (2)” to its name—for example, the duplicate of a worksheet named Summary receives the name Summary (2).

4. Double-tap the duplicate sheet’s tab to select its name.

5. Type the new name.

6. Tap the Done button.

Renaming, Reordering, and Hiding Your Worksheets

To rename a worksheet, double-tap its tab button, type the new name of up to 31 characters, and then tap the Done button.

Tip If you need to make a particular worksheet tab stand out, use a desktop version of Excel to change its color. Right-click or Ctrl+click the tab, click Tab Color, and then click the color you want to apply. Excel for iPad displays the color as a discreet line across the top of the tab rather than as the background for the whole tab (as the desktop versions do), but the color still makes the tab easier to pick out in a busy workbook.

To change the order of worksheets, tap and hold a worksheet tab until it becomes mobile, drag it to its new position, and then drop it there.

Note At this writing, Excel provides no easy way to copy or move a worksheet into another workbook. To work around this, select all the data on the appropriate worksheet, copy it, and then move to the destination workbook and paste in the data. If necessary, tap the Paste Options button and tap Keep Source Formatting or Match Destination Formatting to fix any formatting problems. If you’re moving the worksheet rather than copying it, you can now go back to the source workbook and delete the worksheet from it.

When you don’t want a worksheet to be visible, but you want to keep it in the workbook rather than remove it, you can hide the worksheet. Follow these steps.

1. Tap the worksheet’s tab to make the worksheet active.

2. Tap the tab again to display the Edit menu.

3. Tap the Hide button. Excel hides the worksheet.

Excel doesn’t display any indicator to show that a workbook contains hidden worksheets. To find out if it does, tap the current worksheet tab to display the Edit menu and see if it contains the Unhide button (see Figure 8-4).

Figure 8-4. The Unhide button on the Edit menu for a worksheet tab indicates that the workbook contains one or more hidden worksheets

Tap the Unhide button to display a menu of the hidden worksheets (see Figure 8-5). You can then tap the worksheet you want to unhide.

Figure 8-5. Tap the Unhide button on the Edit menu to display the menu of hidden worksheets, and then tap the worksheet you want to unhide

LAYING OUT YOUR WORKSHEETS THE SMART WAY

Excel gives you a great deal of flexibility in how you lay out your worksheets. Each worksheet contains more than 16 billion cells, and you can put pretty much as many worksheets in a workbook as you want—although if you want to keep Excel on your iPad running snappily, you’ll do well to keep the number of worksheets and the number of occupied cells down to a small fraction of the limits.

To make your worksheets easy to use, follow these suggestions for laying them out:

· Put different topics on different worksheets: Split your data up by topic (or by date, if that makes more sense) and put different topics on different worksheets. Usually, it’s best to keep the most important data in the upper-left corner of any worksheet, where you can most easily access it, rather than having to trek off into the wilds of high-numbered rows or exotically lettered columns.

· Name the worksheets clearly: Give the worksheet tabs descriptive labels so you can easily pick the worksheet you want.

· Provide navigation tools: If a workbook has so many worksheets that navigating with worksheet tabs is awkward, create a summary worksheet at the front containing a list of the other worksheets and their contents—and hyperlinks you can tap to jump directly to each sheet. To add a hyperlink to a cell, you insert the HYPERLINK() function in the cell. Chapter 9 explains how to use functions.

· Divide up your data by columns: Lay out the data so that the cells in any given column contain the same type of data. Doing this enables you to sort and filter the range by rows.

· Put important data above the range rather than beside it: When you need to include notes or other explanatory data, put that data above the range it’s related to. Don’t put the data to the left or right of the range, because Excel may hide the data if you filter the range to show only certain values.

· Separate your data ranges from each other: If you include several different types of data in the same worksheet, put each type in a separate range, with one or more blank columns and one or more blank rows between the ranges. Having this extra space makes it easier for both Excel and you to identify and select the ranges individually.

Inserting and Deleting Rows, Columns, and Cells

As you lay out the data in your worksheets, you may need to insert and delete rows, columns, or blocks of cells. Often, inserting or deleting rows or columns is easier than moving your existing data, especially when there’s a large amount of data involved.

Sometimes you may also need to insert or delete a block of cells without inserting or deleting entire columns or rows.

Inserting Rows or Columns

The easiest way to insert a row or column is to tap the heading of the existing row or column before which you want to insert the new one and then tap the Insert button on the Edit menu, which appears automatically. For rows, this button is called Insert Above (see Figure 8-6); for columns, it’s called Insert Left.

Figure 8-6. Tap a row heading and then tap the Insert Above button on the Edit menu to insert a new row above the existing row

To insert more than one column or row, select the same number of columns or rows first. For example, here’s how to insert three columns before column F.

1. Tap the heading for column F to select it. The Edit menu appears automatically.

2. Drag the right-hand selection handle to the right to expand the selection to columns G and H as well.

3. Tap the Insert Left button on the Edit menu (see Figure 8-7). Excel inserts the new columns to the left of the existing ones.

Figure 8-7. You can insert multiple columns or rows at once by selecting the same number of existing rows or columns before giving the Insert command

Note Excel selects the new columns or rows after it inserts them. If the columns or rows you had previously selected are blank, it may appear that Excel hasn’t inserted the new items. If cells onscreen have contents, you’ll be able to see the change more clearly.

You can also insert a column or row by using the Insert & Delete Cells pop-up panel by following these steps.

1. Tap a cell in the column or row before which you want to insert the column. You can also tap the column heading or row heading.

2. If the Home tab of the Ribbon isn’t currently displayed, tap the Home tab to display its controls.

3. Tap the Insert & Delete Cells button to display the Insert & Delete Cells pop-up panel (see Figure 8-8).

Figure 8-8. You can insert cells, rows, or columns by using the Insert & Delete Cells pop-up panel on the Home tab of the Ribbon

4. Tap the Insert Sheet Columns button or the Insert Sheet Rows button.

Note When the selection is one or more columns, tapping the Shift Cells Right command has the same effect as the Insert Sheet Columns command. Similarly, when the selection is one or more rows, tapping the Shift Cells Down command has the same effect as the Insert Sheet Rows command.

Deleting Rows and Columns

To delete a row or column, tap the heading for the row or column, and then tap the Delete button on the Edit menu. Excel deletes the row or column without further confirmation.

To delete multiple rows or columns, tap the heading of the first row or column, drag the selection handle through the other rows or columns, and then tap the Delete button on the Edit menu. You can also select the row, rows, column, or columns; tap the Insert & Delete Cells button on the Home tab of the Ribbon; and then tap the Delete Sheet Rows button or the Delete Sheet Columns button, as appropriate.

Inserting Individual Cells and Blocks of Cells

When you don't need insert entire rows or columns, you can insert an individual cell or a block of cells. Follow these steps.

1. Select the existing cell or cells before which you want to insert the new cell or cells.

2. If the Home tab of the Ribbon isn’t currently displayed, tap the Home tab to display its controls.

3. Tap the Insert & Delete Cells button to display the Insert & Delete Cells pop-up panel.

4. Tap the Shift Cells Down button or the Shift Cells Right button, as needed.

Similarly, you can delete an individual cell or a block of cells without deleting the entire row or column.

1. Select the cell or cells you want to delete.

2. Tap the Home tab of the Ribbon to display its controls.

3. Tap the Insert & Delete Cells button to display the Insert & Delete Cells pop-up panel.

4. Tap the Shift Cells Left button or the Shift Cells Up button, as needed.

Setting Column Width and Row Height

To set the width of a column, tap the column heading, and then drag the sizing handle to the right or left as needed.

To have Excel resize a column to fit its contents, tap the column heading, and then tap the AutoFit button on the Edit menu that appears (see Figure 8-9).

Figure 8-9. Tap the AutoFit button on the Edit menu to resize a column to fit its contents

Note AutoFit works only for the current contents of the column or row—it’s not a persistent setting, so if you add an entry that’s wider or taller than the contents to which you applied AutoFit, Excel doesn’t adjust the width or height to accommodate the new entry. Instead, you need to apply AutoFit again—preferably after entering all the items in the column or row, or at least entering the widest and tallest items.

Similarly, you can change a row’s height by tapping the row heading and then dragging the sizing handle up or down as needed. To have Excel adjust the row’s height to fit its contents, tap the row heading, and then tap the AutoFit button on the Edit menu.

Hiding Rows and Columns

Sometimes it’s helpful to hide particular columns and rows so that they’re not visible in the worksheet. You may want to do this to keep sensitive data from showing or simply to make the part of the worksheet you’re actually using fit on the iPad’s screen all at once.

To hide a column or row, tap its column heading or row heading, and then tap the Hide button on the Edit menu. Excel hides the row or column, but you can easily tell it’s gone; first, the column letter or row number is missing in the sequence; and second, Excel makes the border between the columns or rows before and after the hidden one heavy and bold. Figure 8-10 shows a worksheet with column B and row 2 hidden.

Figure 8-10. Excel displays a heavier border between row or column headings to indicate a hidden row or column, as with row 2 and column B here

Tip If you don’t want other people to be able to see that you’ve hidden rows or columns, turn off the display of row headings and column headings. Tap the View tab of the Ribbon and then set the Headings switch to the Off position. Turning off the display of headings makes it harder to determine cell references, so don’t hide the headings if anyone will be editing the structure of the workbook.

To display a hidden row or column again, you unhide it. Tap the heavy row border or column border to display the Edit menu, and then tap the Unhide button (see Figure 8-11). If you find it hard to tap the border of the hidden column or row, select the columns or rows before or after the hidden one, and then tap Unhide on the Edit menu.

Figure 8-11. To unhide a row or column, tap the heavy row border or column border, and then tap the Unhide button on the Edit menu

Formatting Cells and Ranges

In Excel, you can format cells in a wide variety of ways—everything from choosing how to display the borders and background to controlling how Excel represents the text you enter in the cell. This section shows you how the most useful kinds of formatting work and how to apply them.

Each cell comes with basic formatting applied to it—the font and font size to use and usually the General number format, which you’ll meet shortly. So when you create a new workbook and start entering data in it, Excel displays the data in the font and font size applied to the cells you use.

Excel for iPad puts all the formatting options on the Home tab of the Ribbon, as you can see in Figure 8-12. These break down into four categories:

· Character Formatting: Use the Fonts pop-up panel and the Font Size pop-up panel to select the font and font size. Tap the Bold button, the Italic button, or the Underline button to apply those attributes where needed.

· Cell Formatting: Use the Cell Borders pop-up panel, the Fill Color pop-up panel, and the Text Color pop-up panel to control the appearance of cells. Tap the Alignment button and select the appropriate horizontal and vertical alignment on the Alignment panel. If you need to merge cells together, select them, and then tap the Merge button.

Figure 8-12. The Home tab of the Ribbon gives you quick access to Excel for iPad’s formatting tools

Caution When you merge together cells that contain data values, Excel keeps only the data value in the upper-left cell in the range. Excel displays the Merging Multiple Data Values dialog box to warn you of this change. Tap the Continue button to continue with the merge or tap the Cancel button if you want to move the data from the other cells before merging the cells.

· Number Formatting: Tap the Number Formatting button and then select the number format on the Number Formatting pop-up panel and its subpanels. You’ll learn about the number formatting options a little later in this chapter.

· Cell Styles: Tap the Cell Styles button and then tap the appropriate style on the Cell Styles pop-up panel. The cell styles are preformatted appearance settings that you can apply quickly to cells.

Understanding How Formatting Works in Excel

In Excel, you can apply direct formatting just as you can in most other apps. For example, you can select a cell and then tap the Bold button on the Home tab of the Ribbon to make the cell’s contents bold, or tap the Fill Colors button and use the Fill Colors pop-up panel to choose a fill color for the cell.

Direct formatting is easy, but you can usually save time and effort—and make your formatting more consistent—by using the cell styles that Excel provides. A cell style is a complete set of formatting for a cell, including any font formatting, number formatting, borders, alignment, fills, and protection needed.

UNDERSTANDING HOW EXCEL STORES DATES AND TIMES

Excel stores dates as serial numbers starting from 1 (Sunday, January 1, 1900) and running way into the future. To give you a couple of points of reference, 42005 represents Thursday, January 1, 2015, while 42370 represents Friday, January 1, 2016.

You can enter a date by typing the serial number (if you know it or care to work it out), but it’s much easier to type a date in a conventional format, because Excel recognizes most of them. For example, if you type 1/1/2016, Excel converts it to 42370 and displays the date in whichever format you’ve chosen.

Excel stores times as decimal parts of a day. For example, 42370.25 is 6 a.m. (one quarter of the way through the day) on January 1, 2016.

Controlling How Data Appears by Applying Number Formatting

When you enter a number in a cell, Excel displays it according to the number formatting applied to that cell. For example, if you enter 42300 in a cell formatted with General formatting, Excel displays it as 42300. If the cell has Currency formatting, Excel displays a value such as $42,300.00 (depending on the details of the format). And if the cell has Date formatting, Excel displays a date such as 23 October 2015 (again, depending on the details of the format). In each case, the number stored in the cell remains the same—so if you change the cell’s formatting to a different type, the way that Excel displays the data changes to match.

Table 8-1 explains Excel’s number formats, with brief examples.

Table 8-1. Excel’s Number Formats

Tip Excel for iPad displays custom formats correctly, and the word Custom appears in the Number Formatting box and on the Number Formatting pop-up panel when a cell with a custom format is selected, but you cannot edit the custom format. You can apply a custom format to another cell by copying and pasting the format from the cell that contains it.

Here’s how to apply a number format.

1. Select the cell or cells that need the format.

2. Tap the Home tab of the Ribbon to display its controls, if it is not already displayed.

3. Tap the Number Formatting button to display the Number Formatting pop-up panel (see Figure 8-13).

Figure 8-13. To apply a number format to selected cells, tap the Number Formatting button, and then tap the appropriate number format on the Number Formatting pop-up panel

4. Tap the number format you want. This example uses the Currency number format. Excel applies the number format to the cell or cells.

5. If the cells now look the way you want them to, tap in the worksheet to close the Number Formatting pop-up panel. Otherwise, tap the Options button (the button with the i icon) on the right of the button for the number format you selected in Step 4. Excel displays the Options pop-up panel for the number format—for example, the Currency Options pop-up panel (see Figure 8-14).

Figure 8-14. Choose options for the number format in its Options pop-up panel

Note The General number format and the Text number format have no options.

6. Use the controls in the Options pop-up panel to specify how you want the number format to appear. For example, in the Currency Options pop-up panel, you can choose these options:

a. Decimal Places: Tap the + button or the – button to adjust the number of decimal places.

b. Symbol: Tap this button to display the Symbols pop-up panel (see Figure 8-15), tap the symbol you need, and then tap the Back button.

Figure 8-15. For number formats such as Currency and Accounting, use the Symbols pop-up panel to specify the currency symbol to display

c. Negative Numbers: In this list, tap the format to use for negative numbers. Your options include showing negative numbers in red instead of the default color, either with or without a leading minus sign.

7. Tap outside the Options pop-up panel to close the pop-up panel and return to your worksheet.

Applying Direct Formatting

You can quickly format a cell, or its contents, by tapping the cell and then tapping the appropriate button on the Home tab of the Ribbon. For example, you can apply a fill color by following these steps.

1. Tap the cell to select it. Alternatively, select a range of cells.

2. Tap the Home tab of the Ribbon to display its controls, if it is not already displayed.

3. Tap the Fill Color button to display the Fill Color pop-up panel (see Figure 8-16).

Figure 8-16. You can apply direct formatting in seconds by using the controls on the Home tab of the Ribbon

4. Tap the Theme Colors tab or the More Colors tab, as needed.

5. Tap the color you want to apply.

Note In Excel for iPad, you can’t format part of the text in a cell—you must format the entire contents, even if you select part of it before giving the formatting command. If you open a workbook with cells that include text with different formatting in the same cell, Excel for iPad displays the cells correctly, but any formatting changes you make to such a cell apply to all its text.

Formatting with Cell Styles

As you’ve seen earlier in this chapter, you can give any cell exactly the formatting you want by using the controls on the Home tab of the Ribbon. But applying formatting one aspect at a time—font, font size, alignment, and so on—is slow work, and it’s easy to apply formatting inconsistently.

To save time and ensure your formatting is consistent, you can use Excel’s cell styles. Each cell style is a collection of formatting that you can apply to one or more cells and contains six types of formatting:

· Number: For example, General, Currency, or Percentage.

Note At this writing, Excel for iPad enables you to apply existing cell styles but not to edit them or create new cell styles. To create or edit cell styles, you must use a desktop version of Excel.

· Alignment: Horizontal alignment (for example, General, Center, or Justify), vertical alignment (for example, Top, Center, or Bottom), and other alignment formatting (such as wrapping the text to the window).

· Font: The font, font size, font color, and so on.

· Border: Any borders included in the cell style, or No Borders if it has no borders.

· Fill: Any fill included in the cell style, or No Shading if it’s plain.

· Protection: Locked, Hidden, both, or No Protection.

Here’s how to apply a cell style.

1. Select the cell or the range to which you want to apply the cell style.

2. Tap the Home tab of the Ribbon to display its controls.

3. Tap the Cell Styles button to display the Cell Styles pop-up panel (see Figure 8-17).

Figure 8-17. The quick way to format cells or ranges is to apply a cell style from the Cell Styles pop-up panel

4. Tap the cell style you want to apply. These are the four main categories of cell styles:

o Good, Bad, and Neutral: This category has Good, Bad, and Neutral cell styles that you can use to apply color coding to cells. Here is also where you will find the Normal cell style that Excel applies to any cell that doesn’t have another cell style.

o Data and Model: This category contains the Calculation, Check Cell, Explanatory, Followed Hyperlink, Hyperlink, Input, Linked Cell, Note, Output, and Warning Text cell styles. Most of these cell styles are used for data modeling. Excel automatically applies the Hyperlink cell style to cells containing hyperlinks you have not clicked yet, changing their cell style to Followed Hyperlink once you have clicked them.

Note The Hyperlink cell style appears in the Data and Model category in the Cell Styles panel only if the workbook contains hyperlinks. Similarly, the Followed Hyperlink cell style appears only if the workbook contains hyperlinks you’ve followed.

o Titles and Headings: This category contains four cell styles for descending levels of headings (Heading 1, Heading 2, Heading 3, and Heading 4), the Title cell style for giving a worksheet a title, and the Total cell style for easily formatting cells that contain totals.

o Themed Cell Styles: This category contains four shaded cell styles featuring six of the theme colors, with four degrees of shading for each. Each color’s cell styles are named 20%, 40%, 60%, and 100%, so there’s a 20% blue cell style, a 20% red cell style, a 20% green cell style, and so on, depending on the theme colors.

Applying Table Formatting

To save you time with formatting, Excel provides preset table styles that you can apply to a table to give it an overall look. After applying a table style, you can choose style options to customize the look.

Note Chapter 7 explains how to insert a table.

Here’s how to apply a table style.

1. Tap anywhere in the table you want to format. Excel adds the Table tab to the Ribbon and displays its controls.

2. Tap the Table Styles button to display the Table Styles pop-up panel (see Figure 8-18). This panel breaks up the styles into three categories: Light, Medium, and Dark. Scroll up or down if necessary to display styles that don’t fit in the panel.

Figure 8-18. You can format a table quickly by applying a style from the Table Styles pop-up panel

3. Tap the table style you want to apply. The table takes on the style’s formatting.

4. Tap the Style Options button to display the Style Options pop-up panel (see Figure 8-19).

Figure 8-19. In the Style Options pop-up panel, tap to turn an option on or turn it off

5. Tap to place a check mark next to the options you want to use or to remove a check mark from an option that you want to stop using. These are the options:

a. Header Row: Select this option to apply different formatting to the header row.

b. Total Row: Select this option to apply different formatting to the total row.

c. Banded Row: Select this option to apply different formatting to alternate rows, making the rows easier to distinguish from each other. Banded rows tend to be helpful on large worksheets, especially if you’ve suppressed the display of gridlines.

d. First Column: Select this option to apply different formatting to the first column. You’d normally do this if the first column contains headings.

e. Last Column: Select this option to apply different formatting to the last column.You might do this if the last column contains totals or some form of evaluation or summary.

f. Banded Column: Select this option to apply different formatting to alternate columns, making it easier to distinguish one column from another.

Tip Usually, it’s best to use either the Banded Row option or the Banded Column option rather than both, but you can use both if you find the resulting look helpful.

Using Find and Replace

Excel for iPad provides Find and Replace features that enable you to locate specific text in your workbooks easily and replace it as needed. Here’s how to use Find and Replace.

1. Tap the magnifying glass icon to the right of the Ribbon tabs. Excel displays the Find bar, replacing the Ribbon at the top of the screen.

2. To choose options for Find, tap the Options button, the cog icon at the left end of the Find box. Excel displays the Options pop-up panel (see Figure 8-20).

Figure 8-20. On the Options pop-up panel, choose between the Find feature and the Find and Replace feature, choose where to search, and select matching options

3. At the top of the Options pop-up panel, tap the Find button or the Find and Replace button to specify which feature you want to use.

4. In the Search In area, tap the Workbook button or the Sheet button to specify the area you want to search.

5. At the bottom of the Options pop-up panel, set the Match Case switch to the On position if you want Find to be case sensitive (for example, “Sales” matches “Sales” but not “sales,” “SALES,” or any other different capitalization).

6. Also at the bottom of the Options pop-up panel, set the Match Cell switch to the On position if you want to match only the entire contents of cells instead of finding matches within cell contents as well.

7. Tap in the Find box and type your search term. Excel searches automatically as you enter the term, displays the number of instances found at the right side of the Find box, and selects the cell containing the first instance (if there are any).

8. If you’re using Find and Replace rather than Find, tap in the Replace box and type the replacement text.

9. Use the buttons on the Find bar or Find and Replace bar (see Figure 8-21) to work with what Excel has found:

a. >: Tap this button to select the cell containing the next instance.

b. >: Tap this button to select the cell containing the previous instance.

c. Replace: Tap this button to replace the current instance of the Find text with the Replace text and select the cell containing the next instance.

d. All: Tap this button to replace all instances of the Find text with the Replace text.

Figure 8-21. Use the > button, < button, Replace button, and All button to work through the instances of the search term

Tap in the worksheet when you’re ready to close the Find bar or the Find and Replace bar.

Sorting and Filtering Your Data

After entering data on a worksheet or creating a table, you may need to sort the data so that you can see how the values relate to each other. You may also need to filter a table’s data to make the table display only the records that you want to see.

Sorting Data

Excel for iPad enables your to sort your data by rows quickly by following these steps:

1. Tap a cell in the column you want to sort.

2. Tap the Home tab of the Ribbon to display its controls, if it’s not already displayed.

3. Tap the Sort and Filter button to display the Sort And Filter pop-up panel (see Figure 8-22).

Figure 8-22. You can sort data in the selected column by opening the Sort and Filter pop-up panel and tapping the Ascending button or the Descending button

4. Tap the Ascending button (to sort A to Z, low values to high values, early dates to later dates, and so on) or the Descending button (high values to low, and so on).

Filtering Data

When you need to find records in a worksheet that match the terms you specify, you can filter it. Filtering makes Excel display only the records that match your search terms, hiding all the other records.

Tip Filtering tends to be most useful in tables, but you can use it in other data areas as well.

Here’s how to filter data.

1. Tap a cell in the column by which you want to filter.

2. Tap the Home tab of the Ribbon to display its controls, if it’s not already displayed.

3. Tap the Sort and Filter button to display the Sort And Filter pop-up panel.

4. Set the Filter switch to the On position. Excel displays a down-arrow button on the right of the heading of the data range in the column. If there’s no heading, Excel displays a down-arrow button on the empty cell directly above the data range

5. Tap the down-arrow button. Excel displays another Sort And Filter pop-up panel, this time from the down-arrow button (see Figure 8-23).

Figure 8-23. Tap the down-arrow button to display the Sort And Filter pop-up panel, then place a check mark on each item you want to include in the filtered view

6. Place a check mark on each item you want to include in the filtered view. If you want only a few items, tap the “(Select All)” button at the top of the list to deselect all the items and then tap to select each you want. Excel displays the filtered rows (see Figure 8-24).

Figure 8-24. Tap the down-arrow button to display the Sort And Filter pop-up panel, then place a check mark on each item you want to include in the filtered view

Note Tap the Clear Filter button on the Sort And Filter pop-up panel when you want to remove the filtering.

Working with Comments

Excel provides comments mostly as a collaboration tool, but you can also use them to make notes for yourself about what you’re trying to achieve in a workbook, what you still need to add, and other details that might otherwise slip your mind.

At this writing, Excel for iPad provides an incomplete implementation of comments: you can review the existing comments and delete any you no longer need, but you can’t yet add new comments. This is a feature that Microsoft is likely to add in an update, so if Excel still can’t add comments when you read this, make sure you’re using the latest version.

A cell with a comment attached displays a red triangle in its upper-right corner, as you can see in Figure 8-25.

Figure 8-25. Display the Review tab of the Ribbon to work with comments

To work with comments, tap the Review tab of the Ribbon to display its controls. You can then use the buttons as follows:

· Next: Tap this button to select the next comment and display it in a comment balloon.

· Previous: Tap this button to select the previous comment and display it in a comment balloon.

Tip You can also display a comment by tapping its square and then tapping the comment symbol that appears above and to the right of the selected cell. After displaying a comment balloon, you can toggle its format between square-ish and a longer, flatter rectangle by tapping the balloon.

· Show Comment: Tap this button to toggle the display of the current comment balloon.

· Show All: Tap this button to toggle the display of all comments. Showing all comments can help you get an overview of what you have to deal with, but if the balloons are packed closely together, you may find it easier to work with one at a time.

· Delete: Tap this button to delete the selected comment.

Tap the Show Comment button again when you want to stop displaying comments.

Printing a Workbook

Excel enables you to print the parts you need of a workbook. You can choose what to print, choose between portrait orientation and landscape orientation, and print either at full size or scaled to fit the paper size you choose. You need to have either an AirPrint-compatible printer or another AirPrint technology; see Chapter 2 for more information.

Here’s how to print part of the active worksheet or workbook.

1. If you want to print a worksheet, make it active. If you want to print only a selection, select the range that encompasses it.

2. Tap the File button to display the File menu.

3. Tap the Print button to display the Layout Options pop-up panel (see Figure 8-26).

Figure 8-26. In the Layout Options pop-up panel, choose what to print, select the orientation and paper size, and apply any scaling needed

4. In the Orientation section, tap the Landscape button or the Portrait button as needed, placing a check mark on it.

5. If the Size button shows a different paper size than you want to use, tap the Size button, tap the appropriate size on the Size pop-up panel, and then tap the Layout Options button to return to the Layout Options pop-up panel.

6. Further down the Layout Options pop-up panel, specify what to print by tapping the Print Active Sheet button, the Print Entire Workbook button, or the Print Selection button.

7. If you need to apply scaling, tap the Scaling button to display the Scaling Options pop-up panel (see Figure 8-27); tap the Fit Sheet on One Page button, the Fit All Rows on One Page button, or Fit All Columns in One Page button; and then tap the Layout Options button to return to the Layout Options pop-up panel.

Figure 8-27. In the Scaling Options pop-up panel, choose any scaling needed for the printout

8. Tap the Next button to display the Printer Options pop-up panel.

9. If the Printer button doesn’t show the right printer, tap the Printer button, tap the printer in the Printer pop-up panel, and then tap the Printer Options button.

10.If you need to adjust the range of pages, tap the Range button, use the controls in the Page Range pop-up panel to specify what to print, and then tap the Printer Options button.

11.Tap the + button or – button to adjust the number of copies as needed.

12.Tap the Print button.

Summary

In this chapter, you learned how to set up your workbooks with the worksheets they need; how to rearrange the rows, columns, and cells in those worksheets as needed; and how to hide rows and columns. You also learned how to format cells and ranges, use the Find and Replace features, and sort and filter data—not to mention how to work with comments and how to print your worksheets and workbooks.