47 Find and Replace Data

 <  Day Day Up  >  

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

See Also

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.

  1. Find Data

    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.

  2. Enter Search Data

    Type the data you want to find in the Search for text box.

  3. 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.

  4. Type Replacement Data

    If you want Calc to replace found data with new data, type the new data into the Replace with text box.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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.)

  10. 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.

  11. 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.

 <  Day Day Up  >  

Sams Teach Yourself OpenOffice.org All In One
Sams Teach Yourself OpenOffice.org All In One
ISBN: 0672326183
EAN: 2147483647
Year: 2003
Pages: 205
Authors: Greg Perry

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net