You'll find yourself working with small, single worksheets quite a bit in Calc because each sheet usually represents one aspect of a financial analysis, such as weekly sales figures for a division. Nevertheless, you'll also work with large spreadsheets quite often too. Many times, a company needs to consolidate numbers from several different regions , companies, or departments into a single spreadsheet. Therefore, you'll combine numerous smaller sheets into a spreadsheet and consolidate them, report totals from them, and analyze them against one another.
Before You Begin
40 Open an Existing Spreadsheet
42 About Moving Around Calc
52 Enter Calc Functions
Whether you have one large sheet or multiple smaller ones, being able to locate numbers and text easily is important. Calc provides powerful find and replace tools you can use to locate and change the data you want.
| || |
Unlike Writer's find and replace tools, when you search for Calc data, Calc highlights the entire cell that contains the data. So, if you were to search for the number 1 in a large spreadsheet, Calc locates and highlights the first cell (searching from left to right, row to row) that contains a 1 , even if other numbers and text are in that cell.
Select the Find & Replace option from the Edit menu to display the Find & Replace dialog box. You can also click the Find button on the Main toolbar or press Ctrl+F to display the Find & Replace dialog box.
Enter Search Data
Type the data you want to find in the Search for
Start the Search
Click the Find
button. Calc searches from the current cell cursor's position in the sheet to the end of the sheet. If Calc finds the data anywhere within a cell, Calc highlights that cell. (If you click the Find All
button instead of Find
, Calc will highlight every cell that contains the matched data.)
| || |
If you've searched for the same data before, you can click the down arrow to open the Search for drop-down list box and select the data to search for it once again.
Type Replacement Data
If you want Calc to replace found data with new data, type the new data into the Replace with text box.
Replace the Data
Click Replace . If the Search for data is found, Calc replaces that data with the data you entered in the Replace with text box.
Replace All Occurrences
Instead of Replace (or after you perform one or more replacements ), if you click the Replace All button, Calc replaces all the matches with your replacement data throughout the sheet. Such a change is more global and possibly riskier because you may replace data you didn't really want replaced . By clicking Find before each replace operation, you can be sure that the proper data in the correct cell is being replaced, but such a single-occurrence find and replacement takes a lot of time in a long spreadsheet.
Replace All Previous Matches
Click to select the Backwards option before doing a find or replacement if you want to find or replace from the current cursor's position back to the start of the sheet.
Control the Search
To control the way Calc searches the current sheet, or to enable Calc to search all sheets within the current spreadsheet, click the More
button. Calc expands the Find & Replace
dialog box with additional options.
| || |
Selecting Formulas enables Calc to locate your search term in either formulas or results. Selecting Values only returns a match if your search term is found in an actual number or text and not if it's the result of a formula.
Specify How to Look
Perhaps you only want Calc to search formulas for calculated results that match your search term. If so, click to select Formulas in the Search in section. If you want Calc to search only in values and text you've typed, but not formulas, click to select Values . (The Notes option is available if you want to search for text inside notes you've attached to cells within the sheet.)
Indicate a Direction
Although Calc normally searches from left to right and from the top row down, you can specify that Calc search completely down the first column of data before searching the second column, moving from column to column only after searching down the entire previous column. Click to select the Columns option if you want to search down entire columns before moving to the next one.
Determine the Search Depth
If you want Calc to search throughout all sheets inside the current spreadsheet, click to select the Search in all sheets option. Unless you check this option, Calc only searches the currently active and displayed sheet within the current spreadsheet.
When you finish finding and replacing all the data for this search session, click the Find & Replace dialog box's Close button to close the dialog box and return to the sheet's work area.