So far, youâve learned how to create a basic worksheet with a table of data in it. Thatâs great for getting started, but as power users, professional accountants, and other Excel jockeys quickly learn, some of the most compelling reasons to use Excel involve multiple tables that share information and interact with each other.
For example, say you want to track the performance of your company. You create one table summarizing your firmâs yearly sales, another listing expenses, and a third analyzing profitability and making predictions for the coming year. If you create these tables in different spreadsheets, you must copy the information you want the sheets to share from one location to another, all without misplacing a number or making a mistake. Whatâs worse is that, with your data scattered in multiple places, youâre missing the chance to use some of Excelâs niftiest charting and analytical tools. But cramming a bunch of tables onto the same worksheet page isnât the solution. Not only are you likely to lose your spot in the avalanche of data, youâll face a host of formatting and cell-management problems.
Fortunately, a better solution exists. Excel lets you create spreadsheets with multiple pages of data, each of which can conveniently exchange information with other pages. Each page is called a worksheet, and a collection of one or more worksheets is called a workbook (which is also sometimes called a spreadsheet file).
In this chapter, youâll learn how to manage the worksheets in a workbook. Youâll also take a look at Find and Replace, an Excel tool for digging through worksheets in search of specific data.
Many workbooks contain more than one table of information. For example, you might have a list of your bank account balances and a list of items repossessed from your home in the same financial planning spreadsheet. You might find it a bit challenging to arrange these tables. You could stack them (Figure 4-1) or place them side by side (Figure 4-2), but neither solution is perfect.
Figure 4-1. Stacking tables on top of each other is usually a bad idea. If you add a new column of data to the top table, youâll mess up the bottom table. Youâll also have trouble properly resizing or formatting columns because each one contains data from two different tables.
Figure 4-2. Youâre somewhat better off putting tables side by side, separated by a blank column, than you are stacking them, but side-by-side columns present their own limitations if you need to add more columns to the first table. It also makes for a lot of side-to-side scrolling.
Most Excel masters agree that the best way to arrange different tables of information is to use separate worksheets for each table. When you create a new workbook, you start with a single worksheet, named Sheet1. However, Excel gives you the ability to add plenty more.
Note
In old versions of Excel, every workbook began with three blank worksheets. Excel 2013 abandons this practice, but youâll still find the extra worksheets in older spreadsheet files. Often, these worksheets will be left blankâin fact, the person who created the spreadsheet might not even know theyâre there.
When you start a fresh workbook in Excel, you get a single blank worksheet. To add more sheets, you need to click the âNew sheetâ button, which is a small plus-in-a-circle icon that appears immediately to the right of your last worksheet tab (Figure 4-3). You can also use the HomeâCellsâInsertâInsert Sheet command, which works the same way but inserts a new worksheet immediately to the left of the current worksheet. (Donât panic: Naming and Rearranging Worksheets shows how you can rearrange worksheets after the fact.) Each worksheet contains a fresh grid of cellsâfrom A1 all the way to XFD1048576.
Figure 4-3. Every time you click the âNew sheetâ button, Excel inserts a new worksheet after the existing one and assigns it a new name. For example, if your workbook has a single worksheet, named Sheet1, Excel adds a new worksheet namedâyou guessed itâSheet2.
If you continue adding worksheets, youâll eventually find that all the worksheet tabs wonât fit at the bottom of your workbook window. Excel uses an ellipsis (â¦) to indicate the next tab that doesnât fit. For example, if you workbook has worksheets named Sheet1, Sheet2, and Sheet3, and the tab for Sheet3 doesnât quite fit into view at the end of the list, youâll see the ellipsis instead. (You can click it to select Sheet3.)
If you have way more worksheets than fit into the tab list, youâll need to use the scroll buttons, which are immediately to the left of the worksheet tabs) to review the list of worksheets. Figure 4-4 shows the scroll buttons and the ellipsis.
Figure 4-4. Using the scroll buttons, you can move between worksheets. The scroll buttons control only which tabs you seeâyou need to click a tab to move to the worksheet you want to work on.
Tip
If you have a huge number of worksheets and they donât all fit in the strip of worksheet tabs, thereâs an easy way to jump around. Right-click the scroll buttons to pop up a list of all your worksheets, then move to the worksheet you want by clicking its name.
Removing a worksheet is just as easy as adding one. Simply move to the sheet you want to get rid of, and then choose HomeâCellsâDeleteâDelete Sheet (you can also right-click a tab, and then choose Delete). Excel wonât complain if you ask it to remove a blank worksheet, but if you try to remove a sheet that contains data, Excel displays a warning message asking for your confirmation. Also, if youâre down to one last worksheet, Excel wonât let you remove it. Doing so would create a tough existential dilemma for Excelâa workbook that holds no worksheetsâso the program prevents you from taking this step.
Warning
Be careful when you delete a worksheet, because you canât use Undo (Ctrl+Z) to reverse this change!
Excel starts you off with one worksheet for each workbook, but changing this setting is easy. You can configure Excel to start with up to 255 worksheets. Select FileâOptions, and then choose the General section. Under the heading âWhen creating new workbooks,â change the number in the âInclude this many sheetsâ box, and then click OK. This setting takes effect the next time you create a new workbook.
Note
Although Excel limits you to 255 sheets in a new workbook, it doesnât limit the number of worksheets you can add after you create a workbook. Ultimately, the only factor that limits the number of worksheets your workbook can hold is your computerâs memory. But todayâs computers can easily handle even the most ridiculously large, worksheet-stuffed workbook.
To move from one worksheet to another, you have a few choices:
Click the worksheet tabs at the bottom of Excelâs grid window (just above the status bar).
Press Ctrl+Page Down to move to the next worksheet. For example, if youâre currently in Sheet1, this key sequence jumps you to Sheet2 (assuming your sheets are in order).
Press Ctrl+Page Up to move to the previous worksheet. For example, if youâre currently in Sheet2, this key sequence takes you to Sheet1.
Excel keeps track of the active cell in each worksheet. That means that if youâre in cell B9 in Sheet1, and then move to Sheet2, when you jump back to Sheet1, youâll automatically return to cell B9.
Tip
Excel includes some interesting viewing features that let you look at two different worksheets at the same time, even if these worksheets are in the same workbook. Youâll learn more about custom views in Chapter 7.
Deleting worksheets isnât the only way to tidy up a workbook or get rid of information you donât want. You can also hide a worksheet temporarily.
When you hide a worksheet, its tab disappears, but the worksheet itself remains part of your workbook file, available whenever you choose to unhide it. You canât print a hidden worksheet, either.
To hide a worksheet, right-click the worksheet tab, and then choose Hide. (Or, for a more long-winded approach, choose HomeâCellsâFormatâHide & UnhideâHide Sheet.)
To redisplay a hidden worksheet, right-click any worksheet tab, and then choose Unhide. The Unhide window appears along with a list of all hidden sheets, as shown in Figure 4-5. Select a sheet from the list, and then click OK to unhide it. (Once again, the ribbon can get you to the same windowâpoint yourself to HomeâCellsâFormatâHide & UnhideâUnhide Sheet.)
The standard names Excel assigns new worksheetsâSheet1, Sheet2, Sheet3, and so onâarenât very helpful for identifying what they contain. They become even less helpful if you start adding new worksheets, since the new sheet numbers (Sheet2, and so on) donât necessarily indicate the position of the sheets, just the order in which you created them.
For example, if youâre on Sheet 3 and you add a new worksheet (by choosing HomeâCellsâInsertâInsert Sheet), then the worksheet tabs read: Sheet1, Sheet2, Sheet4, Sheet3. (Thatâs because the Insert Sheet command inserts the new sheet just before your current sheet.) Excel doesnât expect you to stick with these auto-generated names. You can rename them by right-clicking the worksheet tab and selecting Rename, or by just double-clicking the sheet name. Either way, Excel highlights the worksheet tab, and you can type a new name directly in the tab. Figure 4-6 shows worksheet tabs with better names.
Figure 4-6. Your worksheet names can have up to 31 characters and include letters, numbers, some symbols, and spaces. Remember, though, that the longer the worksheet name, the fewer worksheet tabs youâll see at once, and the more youâll need to scroll to see the rest of the tabs. For convenienceâs sake, try to keep your names brief by using titles like Sales13, Purchases, and Jet_Mileage.
Note
Excel reserves a small set of worksheet names that you can never use. To witness this problem, try to create a worksheet named History. Excel doesnât let you, because it uses the History worksheet as part of its change-tracking feature (Examining the Change Log). Use this Excel oddity to impress your friends.
Sometimes Excel refuses to insert new worksheets exactly where youâd like them. Fortunately, you can easily rearrange any of your sheets just by dragging their tabs from one place to another, as shown in Figure 4-7.
Figure 4-7. When you drag a worksheet tab, a tiny page appears beneath the arrow cursor. As you move the cursor around, youâll see a black triangle appear, indicating where the worksheet will land when you release the mouse button.
Tip
You can use a similar technique to create copies of a worksheet. Click the worksheet tab and begin dragging, just as you would to move the worksheet. Before you release the mouse button, press the Ctrl key (youâll see a plus sign [+] appear). Keep holding the Ctrl key until you release the mouse button, at which point Excel creates a copy of the worksheet in the new location. The original worksheet remains in its original location. Excel gives the new worksheet a name with a number in parentheses. For example, a copy of Sheet1 is named Sheet1 (2). As with any other worksheet tab, you can change this name.
Once you get the hang of creating worksheets for different types of information, your Excel files can quickly fill up with more sheets than the bedding department at Macyâs. What happens when you want to shift some of these worksheets around? For instance, you may want to move (or copy) a worksheet from one Excel file to another. Hereâs how:
Open both spreadsheet files.
The file that contains the worksheet you want to move or copy is called the source file; the other file (the one where you want to place the worksheet copy) is the destination file.
Go to the source workbook.
Remember, you can move from one window to another using the Windows task bar, or by choosing the fileâs name from the ribbonâs ViewâWindowsâSwitch Windows list.
Right-click the worksheet you want to transfer, and then, from the shortcut menu that appears, choose Move or Copy.
To transfer multiple worksheets at once, hold down the Ctrl key, and then select all the worksheets you want to move or copy. Excel highlights all the worksheets you select (and groups them together). Right-click the selection, and then choose Move or Copy.
When you move or copy a worksheet, Excel launches the Move or Copy window (shown in Figure 4-8).
Choose the destination file from the âTo bookâ drop-down list.
The âTo bookâ menu shows all the currently open workbooks (including the source workbook).
Figure 4-8. Here, youâre about to move the selected worksheet into the SimpleExpenses.xlsx workbook. (The source workbook isnât shown.) The SimpleExpenses workbook already contains three worksheets (named Sheet1, Sheet2, and Sheet3). Excel inserts the new worksheet just before the first sheet. Because you didnât turn on the âCreate a copyâ checkbox, Excel removes the worksheet from the source workbook when it completes the transfer.
Specify where you want to insert the worksheet.
Choose a destination worksheet from the âBefore sheetâ list. Excel places the copied worksheets just before the worksheet you select. If you want to place the worksheets at the end of the destination workbook, select â(move to end).â Of course, you can always rearrange the worksheets after you transfer them, so you donât need to worry too much about getting the perfect placement.
If you want to copy the worksheet, turn on the âCreate a copyâ checkbox at the bottom of the window.
With this option turned off, Excel copies a worksheet to the destination workbook and removes the original from the source workbook. If you do turn this option on, youâll end up with a copy of the worksheet in both workbooks.
Click OK.
This final step closes the Move or Copy window and transfers the worksheet(s).
As you saw in previous chapters, Excel lets you work with more than one column, row, or cell at a time. The same holds true for worksheets. You can select multiple worksheets and perform an operation on all of them at once. The process of selecting multiple sheets is called grouping, and itâs helpful if you need to hide or format several worksheets (for example, to make sure all your worksheets start with a bright yellow first row), and you donât want the hassle of selecting them one at a time. Grouping sheets doesnât let you do anything you couldnât do ordinarilyâitâs just a nifty timesaver.
Here are some operationsâall of which are explained in detail belowâthat you can simultaneously perform on worksheets grouped together:
Move, copy, delete, or hide the worksheets.
Apply formatting to individual cells, columns, rows, or even entire worksheets.
Enter new text, change text, or clear cells.
Cut, copy, and paste cells.
Adjust some page layout options, like paper orientation (on the Page Layout tab).
Adjust some view options, like gridlines and the zoom level (on the View tab).
To group worksheets, hold down Ctrl while clicking multiple worksheet tabs. When you finish, release the Ctrl key. Figure 4-9 shows an example.
Figure 4-9. In this example, you grouped Sheet2 and Sheet3. When you group worksheets, their tab colors change from gray to white. Also, in workbooks with groups, the title bar of the Excel window includes the word [Group] at the end of the file name.
Tip
As a shortcut, you can select all the worksheets in a workbook by right-clicking any tab, and then choosing Select All Sheets.
To ungroup worksheets, right-click one of the tabs and then select Ungroup Sheets, or just click one of the worksheet tabs that isnât in your group. You can also remove a single worksheet from a group by clicking it while holding down Ctrl. However, this technique works only if the worksheet you want to remove from the group is not the currently active worksheet.
As your workbook grows, youâll often need better ways to manage the collection of worksheets youâve accumulated. For example, you might want to temporarily hide a number of worksheets, or move a less important batch of them from the front (that is, the left side) of the worksheet tab holder to the end (the right side). And if a workbookâs got way too many worksheets, you might even want to relocate several of them to a brand-new workbook.
You can easily perform an action on a group of worksheets. For example, you can drag a group of selected worksheets from one location to another using the worksheet tab holder. To delete or hide a group of sheets, just right-click one of the worksheet tabs in your group, and then choose Delete or Hide. Excel then deletes or hides all the selected worksheets (provided that action leaves at least one visible worksheet in your workbook).
Note
Excel keeps track of print and display settings on a per-worksheet basis. In other words, when you set the zoom to 50 percent in one worksheet, it doesnât affect the zoom in another worksheet. However, when you make the change for a group of worksheets, that change affects all the sheets in the same way.
When you format cells inside one grouped worksheet, it triggers the same changes in the cells in the other grouped worksheets. So you have another tool you can use to apply consistent formatting over a batch of worksheets. Itâs mainly useful when you structure all your worksheets the same way.
For example, imagine you create a workbook with 10 worksheets, each representing a different customer order. If you group all 10 worksheets together and then format just the first one, Excel formats all the worksheets in exactly the same way. Or say you group Sheet1 and Sheet2, and then change the font of column B in Sheet2âExcel automatically changes the font in column B in Sheet1, too. The same is true if you change the formatting of individual cells or the entire worksheetâExcel replicates these changes across the group. (To change the font, select your cells and pick what you want from the HomeâFont section of the ribbon. Youâll learn much more about the different types of formatting in Chapter 5.)
With grouped worksheets, you can also modify the contents of individual cells, including entering or changing text and clearing cell contents. For example, if you enter a new value in cell B4 of Sheet2, Excel enters the same value in cell B4 of the grouped Sheet1. Even more interesting, if you modify a value in a cell in Sheet2, the same value appears in the same cell in Sheet1, even if Sheet1 didnât previously have a value in that cell. Similar behavior happens when you delete cells.
Editing a group of worksheets at once isnât as useful as moving and formatting them, but it does have its moments. Once again, it makes most sense when all the worksheets have the same structure. For example, you could use this technique to put the same copyright message in cell A1 on every worksheet, or to add the same column titles to multiple tables (assuming you arranged them in exactly the same way). One example where grouped sheets make sense is if you have a different worksheet for every month of a year, but each one has the same overall structure.
Warning
Be careful to remember the magnified power your keystrokes possess when you work on grouped sheets. For example, imagine youâre in cell A3 of Sheet1, which happens to be empty. If you click Delete, you see no change. However, if any of the other worksheets have data in cell A3, that data in now gone. Groupers beware.
Cut and paste operations work the same way as entering or modifying grouped cells. When you take an action on one grouped sheet, Excel performs the same action on the other grouped sheets. For example, consider what happens if you group Sheet1 and Sheet2, and you copy cells A1 to A2 in Sheet1. The same action takes place in Sheet2âin other words, Excel copies the contents of cell A1 in Sheet2 to cell A2 in Sheet2. Obviously, the contents of cells A1 and A2 in Sheet1 may differ from the contents of cell A1 and A2 in Sheet2âthe grouping simply means that whatever was in cell A1 will now also be in cell A2.
When you deal with great mounds of information, you may have a tough time ferreting out the nuggets of data you need. Fortunately, Excelâs Find feature is great for locating numbers or text, even when itâs buried within massive workbooks holding dozens of worksheets. And if you need to make changes to a bunch of identical items, the find-and-replace option can be a real timesaver.
The Find and Replace feature includes both simple and advanced options. In its basic version, youâre only a quick keystroke combo away from a word or number you know lurks somewhere in your data pile. With the advanced options turned on, you can do things like search for cells that have certain formatting characteristics and apply changes automatically. The next few sections dissect these features.
Excelâs Find feature is a little like the Go To tool (The Go To Feature), which lets you move across a large expanse of cells in a single bound. The difference is that Go To moves to a known location, using the cell address you specify. The Find feature, on the other hand, searches every cell until it finds the content you asked Excel to look for. Excelâs search works similarly to the search feature in Microsoft Word, but itâs worth keeping in mind a few additional details:
When Excel searches, it compares the content you enter with the content in each cell. If you search for the word Date, for example, Excel identifies as a match the cell containing the phrase Date Purchased.
When you search for cells with numeric or date information, Excel always searches the cell content, not the display text. (For more information on the difference between the way Excel displays a numeric value and the underlying value Excel actually stores, see How Excel Identifies Numbers.)
Say a cell displays dates using the day-month-year format, like 2-Dec-13. Internally, Excel stores that date as 12/2/2013, which youâll see if you move to the cell and look up in the formula bar. Thus, if you search for 2013 or 12/2 youâll find the cell, because your search text matches part of the stored content. But if you search for Dec or 2-Dec-13, you wonât find a match. You see similar behavior with numbers. For example, the search string $3 wonât match the currency value $3.00, because the dollar sign isnât part of the stored cell valueâitâs just a formatting detail. You can change this behavior and search for what the cell actually displays using the âLook inâ setting described on More Advanced Searches.
Excel searches one cell at a time, from left to right. When it reaches the end of a row, it moves to the first column of the next row.
To use the Find feature, follow these steps:
Move to the cell where you want to begin the search.
If you start halfway down the worksheet, for example, the search covers the cells from there to the end of the worksheet, and then âloops overâ and starts at cell A1. If you select a group of cells, Excel restricts the search to just those cells. You can search across a set of columns, rows, or even a noncontiguous group of cells.
Choose HomeâEditingâFind & SelectâFind, or press Ctrl+F.
The Find and Replace window appears, with the Find tab selected.
In the âFind whatâ combo box, enter the word, phrase, or number youâre looking for.
If you recently searched for a term, Excel makes it easy to search for the same term again laterâit keeps a temporary record of your search terms in the âFind whatâ list. Choose the search term you want from the drop-down menu.
Click Find Next.
Excel jumps to the next matching cell, which becomes the active cell. However, Excel doesnât highlight the matched text or in any way indicate why it decided the cell was a match. (Thatâs a bummer if youâve got, say, 200 words crammed into a cell.) If it doesnât find a matching cell, Excel displays a message telling you it couldnât find the requested content.
If the first match isnât what youâre looking for, keep looking by clicking Find Next again to move to the next match. Keep clicking Find Next to move through the worksheet. When you reach the end, Excel resumes the search at the beginning, potentially bringing you back to a match youâve already seen. When you finish searching, click Close to get rid of the Find and Replace window.
One of the problems with searching in Excel is that youâre never quite sure how many matches there are in a worksheet. Sure, clicking Find Next gets you from one cell to the next, but wouldnât it be easier for Excel to let you know right away how many matches it found?
Enter the Find All feature. With Find All, Excel searches the entire worksheet in one go, and compiles a list of matches, as shown in Figure 4-10.
Figure 4-10. In the example shown here, the search for âPriceâ matched three cells in the worksheet. The list shows you the matching cellâs complete text and cell reference number (for example, $C$1 is a reference to cell C1).
The Find All button doesnât lead you through the worksheet like the Find feature does. Itâs up to you to select one of the results in the list, at which point Excel automatically moves you to the matching cell.
Excel keeps the text and numbers in your Find All list synchronized with any changes you make in the worksheet. For example, if you change cell D5 to Total Price, the change appears in the Value column in the found-items list automatically. This tool is great for editing a worksheet because you can keep track of multiple changes at a single glance. However, Excel wonât pick up on new matches if you add data to your worksheetâfor that, you need to run a new search.
Finally, the Find All feature is the heart of another great Excel trick: It gives you another way to change multiple cells at once. After you finish the Find All search, select all the entries you want to change from the list by clicking them while holding down Ctrl (so you can select several at once). Click the formula bar, and then start typing in the new value. When you finish, hit Ctrl+Enter to apply your changes to every selected cell. Voilà âitâs like Find and Replace, but youâre in control!
Basic searches are fine if all you need to find is a glaringly unique phrase or number (Pet Snail Names or 10,987,654,321). But Excelâs advanced search feature gives you lots of ways to fine-tune your searches or even search more than one worksheet. To conduct an advanced search, begin by clicking the Options button in the Find and Replace window, as shown in Figure 4-11.
Figure 4-11. In the standard Find and Replace window (top), when you click Options, Excel gives you a slew of additional settings (bottom) so you can configure things like search direction, case sensitivity, and format-matching.
You can set any or all of the following options:
The Within box controls the span of your search. The standard option, Sheet, searches all the cells in the currently active worksheet. If you want to continue the search on the other worksheets in your workbook, choose Workbook. When Excel searches a workbook, it examines your worksheets from left to right, starting with the current one. When it finishes searching the last worksheet in your workbook, it loops back and starts again at the first worksheet.
The Search box chooses the direction of the search. The standard option, By Rows, searches each row from top to bottom before moving on to the next one. That means that if you start in cell B2, Excel first searches C2, D2, E2, and so on. Once it moves through every column in the second row, it moves on to the third row and searches from left to right.
On the other hand, if you choose By Columns, Excel searches all the rows in the current column before moving to the next column. That means that if you start in cell B2, Excel searches B3, B4, and so on, until it reaches the bottom of the column, and then starts at the top of the next column (column C).
The âLook inâ box tells Excel what to examine in each cell. If you choose Formulas (the standard option), Excel tries to match your search term and the cellâs content (for example, the number 3.5 or the date 12/2/2013). If you choose Values, Excel tries to match your search term and the cellâs display text (for example, the formatted number $3.50 or the formatted date 2-Dec-10). And if you choose Comments, Excel searches any comments attached to a cell (Adding Comments), but ignores the cell content itself.
The âMatch caseâ option specifies whether capitalization is important. If you select âMatch case,â Excel finds only words or phrases whose capitalization matches. Thus, searching for Date matches the cell value Date, but not date.
The âMatch entire cell contentsâ option lets you restrict your searches to the entire contents of a cell. Excel ordinarily looks to see if your search term is contained anywhere inside a cell. So, if you specify the word Price, Excel finds cells containing text like Current Price and even Repriced Items. Similarly, a number like 32 will match cell values like 3253, 10032, and 1.321. Turning on the âMatch entire cell contentsâ option forces Excel to be precise.
Excelâs Find and Replace feature is an equal opportunity search tool: It doesnât care what the contents of a cell look like. But what if you know, for example, that the data youâre looking for is formatted in bold, or that itâs a number that uses the Currency format? You can use these formatting details to help Excel find the cells you want and ignore irrelevant ones.
To use formatting details as part of your search criteria, follow these steps:
Launch the Find tool.
Choose HomeâEditingâFind & SelectâFind, or press Ctrl+F. Click the Options button to make sure the Find and Replace window displays the advanced settings.
Decide how you want to specify the formatting.
You have two options, and they both involve the Format button next to the âFind whatâ search box.
The quickest way to target a cellâs format is to copy the format information from another cell. To do that, click the arrow to the right of the Format button to pop open a menu with additional options, and then click Choose Format From Cell. The mouse pointer changes to a plus symbol with an eyedropper next to it. Next, click any cell that has the formatting you want to match. Keep in mind that when you use this approach, you copy all the format settings.
A more controlled approach is to specify the exact formatting settings you want to hunt down. To do this, click the Format button. The Find Format window appears (Figure 4-12). Using the Find Format box, you can specify any combination of settings for number format, alignment, font, fill pattern, and borders. (Chapter 5 explains these settings in detail.) You can also search for protected and locked cells, which are described in Chapter 21. When you finish, click OK to return to the Find and Replace window.
Figure 4-12. In the Find Format window, Excel wonât use the formatting option as part of its search criteria if itâs blank or grayed out. For example, here, Excel wonât search based on alignment because itâs blank. Checkboxes are a little differentâif they look like theyâre filled with a solid square (as with the âWrap textâ setting in this example), Excel wonât use them as part of its search.
Review your formatting and start your search.
Next to the âFind whatâ search box, a preview appears indicating the format of the cells you want to find, as shown in Figure 4-13. If everything checks out, click Find All or Find Next to get started.
To remove these formatting restrictions in subsequent searches, click the arrow on the right of the Format button, and then choose Clear Find Format.
You can use Excelâs search muscles to find not only the information youâre interested in, but to modify cells quickly and easily, too. You can make two types of changes using Excelâs Replace tool:
Automatically change cell content. For example, you can replace the word Colour with Color or the number $400 with $40.
Automatically change cell formatting. For example, you can search for every cell that contains the word Price or the number $400 and change the fill color. Or, you can search for every cell that uses a specific font, and then change that font.
Hereâs how to replace characters in a cell. Once you mastered this technique, check out the box on Mastering the Art of Replacement, which describes some super-handy tricks this process lets you do.
Move to the cell where you want to start the search.
Remember, if you donât want to search the entire spreadsheet, select a range of cells (Selecting Cells).
Choose HomeâEditingâFind & SelectâReplace, or press Ctrl+H.
The Find and Replace window appears, with the Replace tab selected, as shown in Figure 4-14.
In the âFind whatâ box, enter your search term. In the âReplace withâ box, enter the replacement text.
Type the replacement text exactly as you want it to appear. If you want to set any advanced options, click Options (see the earlier sections More Advanced Searches for more on your choices).
Execute the search.
Youâve got three options here. Replace All changes all the matches your search identifies. Find All works just like the Find All feature described earlier (Find All)âit searches the entire worksheet in one go, and compiles a list of matches. Find Next moves to the next match, where you can click Replace to drop in your new characters and keep going, or Find Next to skip to the next match without making any changes. The replace options are good if youâre confident you want to make a change; the find options work well if you first want to see what changes youâre about to make (although you can reverse either option using Ctrl+Z to fire the Undo command).
Get Excel 2013: The Missing Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.