Catégorie : Practical Excel

Find or Replace Text and Numbers in a Worksheet

The Find and Replace functions in Excel are used to search for a number or a string of text within a worksheet or an entire workbook. You can use them either to locate the item for reference purposes or to replace it with another value. You can include wildcards such as question marks (?), tildes (~), asterisks (*), or numbers in your search terms. Searches can be performed across rows and columns, within comments or cell values, and across worksheets or entire workbooks.

Basic Find

To find an occurrence of a specific value in a worksheet:

Click on the Find & Select icon (located in the Editing group on the Home tab of the Excel ribbon), then select the Find option.

NOTE: 
The keyboard shortcut for this is Ctrl + F.

The Find and Replace dialog box will appear with the Find tab selected, as shown below:

• In the dialog box:

• Type the text or numeric value you want to find in the Find what field.

• Click the Find Next button.

This will take you to the next occurrence of the desired value in the current worksheet.

Find All

If you want to locate all occurrences of a specific value, click the Find All button in the Find and Replace dialog box. This displays a list of all instances of your search term, as shown in Figure.

Clicking any value in the list will take you to the corresponding cell in your worksheet.

Basic Replace

To replace one or more occurrences of a specific value in an Excel worksheet:

Click the Find & Select button (located in the Editing group on the Home tab), then choose the Replace… option.

The Find and Replace dialog box will open with the Replace tab selected, as illustrated below:

NOTE:
The keyboard shortcut to access this feature is Ctrl + H.

In the dialog box:
a. Type the text you want to find in the Find what field.
b. Type the text you want to replace it with in the Replace with field.
c. Click the Find Next button. This will take you to the first occurrence of the search text.
d. To replace the current instance of the search text with the specified replacement text, click the Replace button. The text will be replaced, and you will be taken to the next occurrence.

NOTE:
You can leave the Replace with field empty if you simply want to remove all instances of the search text (i.e., replace them with nothing).

If you’re certain that you want to replace all occurrences of the search text with the replacement text (without reviewing each case individually), simply click Replace All in the dialog box.

NOTE:
You can use wildcard characters (question mark ?, asterisk *, tilde ~) in your search criteria.

• Use the question mark (?) to find any single character. For example, s?t will match “sat” and “set”.

• Use the asterisk (*) to find any number of characters. For example, t*s* will match “triste” and “saturé”.

• Use the tilde (~) followed by ?, *, or ~ to search for actual question marks, asterisks, or tildes. For example, fy91~? will find “fy91?”.

Advanced Search Options

The Find and Replace command can be refined using several options, which can be displayed by clicking the Options button in the Find and Replace dialog box.

Clicking the Options button expands the dialog box, as shown below:

Most of these options are also available in the Replace tab of the dialog box.

Each option is explained below:

• Within: Allows the user to choose whether the search should be performed in the active worksheet only, or throughout the entire current workbook.

• Search: Determines the direction Excel uses to perform the search:

  • If set to By Rows, Excel searches each row before moving to the next row.

  • If set to By Columns, Excel searches each column before continuing to the top of the next column.

• Look in: Lets the user decide what Excel should search:

  • Formulas: If a cell contains a formula, Excel searches the formula text—not the result.

  • Values (not available in the Replace tab): Excel searches the result of the formula, not the formula itself.

  • Comments (not available in the Replace tab): Only cell comments are searched; cell contents are ignored.

• Match case: Allows the user to make the search case-sensitive.

  • If unchecked (default), the search is not case-sensitive.

  • If checked, the search will distinguish between uppercase and lowercase letters.

• Match entire cell contents: Lets the user specify whether to match the entire content of a cell or just part of it.

  • If unchecked (default), Excel will find the search term even if it’s just part of a cell’s content.

  • If checked, Excel will only return matches where the entire cell exactly matches the search term.

Find and Replace a Formatting Style

In the Find and Replace dialog box, you’ll also find the Format… button. This allows you to specify a format you want to search for, and optionally, a format to replace it with.

Note: If you specify both a format and a text search value, Excel will only find cells that match both the specified format and text.

In the Find Format dialog, you can define formatting based on:

• Number

• Alignment

• Font

• Border

• Fill

• Protection

To search for specific formatting:

• In the Find tab of the Find and Replace dialog box, click Format, then click Choose Format From Cell under the dialog box.

• When the pointer turns into an eyedropper, click on the cell you want to base your search on.

• In the Find and Replace dialog box, click Find Next.

How to Clear a Formatting Style from Find and Replace

If you want to remove a previously specified formatting style from the Find and Replace dialog box:

Click the arrow next to the Format… button and select Clear Find Format.

There are many situations where you may need to create a new worksheet based on an existing one or move a sheet tab from one Excel file to another. For example, you may want to back up an important worksheet or create multiple copies of the same sheet for testing purposes. Fortunately, Excel provides a few simple and quick methods to duplicate worksheets.

Copy a Worksheet in Excel

Excel offers three built-in methods to duplicate worksheets. Depending on your preferred working style, you can use the ribbon, the mouse, or the keyboard.

Method 1: Copy the Worksheet by Dragging

Normally, you use drag-and-drop to move items from one place to another. But this method also works to copy worksheet tabs and is actually the fastest way to duplicate a sheet in Excel.
Simply click on the tab of the worksheet you want to copy, hold down the Ctrl key, and drag the tab to the desired location.

Method 2: Duplicate a Sheet Using Right-Click

Here’s another easy way to duplicate a sheet in Excel:

• Right-click on the sheet tab and select Move or Copy from the context menu. This opens the Move or Copy dialog box.

• Under Before sheet, choose where you want to place the copy.

• Check the Create a copy box.

• Click OK.

For example, this is how you can make a copy of Sheet1 and place it before Sheet3.

Method 3: Copy a Sheet Using the Ribbon

The Ribbon includes all Excel functionalities—you just need to know where to find them. To copy a sheet using the ribbon:

• Go to the Home tab.

• Click Format in the Cells group.

• Select Move or Copy Sheet…

The Move or Copy dialog box appears. Follow the same steps as in the previous method.

Copy a Worksheet to Another Workbook

The most common way to copy a worksheet to another workbook is as follows:

• Right-click the tab of the sheet you want to copy, then click Move or Copy…

• In the Move or Copy dialog box, do the following:

• Under To book, select the target workbook. To place the copy in a new workbook, select (new book).

• Under Before sheet, specify where to place the copied sheet.

• Check the Create a copy box.

• Click OK.

NOTE:
Excel only displays open workbooks in the To book drop-down list, so make sure to open the destination file before copying.

Aside from this traditional method, there is another way to achieve the same result.

Copy a Sheet to Another Workbook by Dragging

If Excel allows duplicating a sheet within the same workbook by dragging, why not try using this method to copy a sheet to another workbook? You just need to view both files at the same time. Here’s how:

• Open both the source and target workbooks.

• On the View tab, in the Window group, click View Side by Side. This arranges the two workbooks horizontally.

           In the source workbook, click the sheet tab you want to copy, hold Ctrl, and drag the sheet into the target workbook.

Copy Multiple Sheets in Excel

All techniques that work to duplicate a single sheet can also be used to copy multiple sheets. The key is to select multiple worksheets. Here’s how:

• To select adjacent sheets, click the first sheet tab, hold Shift, then click the last tab.

• To select non-adjacent sheets, click the first sheet tab, hold Ctrl, and click the other tabs one by one.

With multiple sheets selected, do one of the following:

• Right-click one of the selected tabs and choose Move or Copy.

• Press Ctrl and drag the tabs to the desired location.

• From the Home tab, click Format > Move or Copy Sheet.

Copy a Worksheet with Formulas

In most cases, copying a worksheet with formulas works the same as copying any other sheet. Formula references adjust automatically in a way that works well for most scenarios.

• When copying a sheet within the same workbook, formulas refer to the copied sheet unless external references point to another sheet or file.

Before copying:
=[Workbook1]Sheet2!B1*[Workbook1]Sheet2!C1

After copying:
=Sheet2!B1*Sheet2!C1

• When copying a worksheet to another workbook, formula references behave as follows:

  • References within the same sheet (relative or absolute) point to the copied sheet in the target workbook.

  • References to other sheets in the original workbook still point to the original workbook.
    You’ll notice this by the workbook name (e.g., [Workbook1]) appearing in the formula.

To make copied formulas refer to a sheet with the same name in the new workbook, simply use Excel’s Replace All feature:

• On the copied sheet, select all formulas you want to edit.

• Press Ctrl + H to open the Replace tab of the Find and Replace dialog box.

• In the Find what box, enter the name of the original workbook exactly as it appears (e.g., [Workbook1]).

• Leave the Replace with box empty.

• Click Replace All.

Result:
From
=[Workbook1]Sheet2!B1*[Workbook1]Sheet2!C1
To
=Sheet2!B1*Sheet2!C1

Copy Data from One Sheet to Another Using a Formula

If you don’t want to copy the entire sheet but only a portion of it, select the range of interest and press Ctrl + C to copy. Then switch to another sheet, select the top-left cell of the destination range, and press Ctrl + V to paste.

To ensure the copied data updates automatically when the original data changes, use formulas to reference another sheet.

For example, to copy data from cell A1 in Sheet1 to cell B1 in Sheet2, enter the following formula in B1:
=Sheet1!A1

To copy data from another workbook, include the workbook name in brackets:
=[Workbook1]Sheet1!A1

If needed, drag the formula down or across to extend the range.

Move Worksheets in Excel

Moving sheets in Excel is even easier than copying them and can be done using the same techniques.

Move a Sheet by Dragging

To move one or more sheets, simply select the tab(s) and drag them to a new location.
For example, here’s how to move Sheet1 and Sheet3 to the end of the workbook.

To move a sheet to another workbook, place the files side by side and drag the sheet from one file to the other.

Move a Sheet via the Move or Copy Dialog Box

Right-click the sheet tab and select Move or Copy, or go to the Home tab → Format → Move or Copy Sheet. Then:

• To move a sheet within the same workbook, choose the target location under Before sheet, and click OK.

• To move a sheet to another workbook, select the target workbook in the To book list, choose the sheet position, and click OK.

Note: If the target workbook already contains a sheet with the same name, Excel will add a number in parentheses to the end of the moved sheet’s name. For example, Sheet3 will become Sheet3 (2).

When Move or Copy Doesn’t Work

Normally, Microsoft Excel moves and copies sheets without issue. If a worksheet cannot be moved or copied, it may be due to the following reasons:

• Excel Table in the Sheet
  You cannot move or copy a group of sheets if one of them contains an Excel table. Each of these sheets must be handled individually.

• Protected Workbook
  Moving or copying sheets is not allowed in protected workbooks. To check if the workbook is protected, go to the Review tab and look at the Protect Workbook button in the Protect group. If the button is highlighted, it means the workbook is protected. Click it to unlock the workbook, then move the sheets.

• Named Ranges Conflict
  When copying or moving a sheet from one workbook to another, you may see an error message saying a name already exists. This means that both the source and target workbooks have a table or range with the same name.

  • If it’s just one error, click Yes to use the existing name, or No to rename it.

  • If there are many conflicts, it’s better to review all names before copying.
    To do so, press Ctrl + F3 to open the Name Manager in the active workbook—here you can edit or delete names as needed.

When you create a new workbook, Excel includes a single worksheet that you can use to build a spreadsheet template or store data. If you want to create a new template or store a different dataset that is related to the existing data in the workbook, you can create a new worksheet to contain the new information. Excel supports multiple worksheets in a single workbook, so you can add as many worksheets as needed for your project or template. In most cases, you’ll insert a blank worksheet, but Excel also provides several built-in templates.

Insert a Blank Worksheet

To insert a blank worksheet in a workbook, go to the Home tab, navigate to the Cells group, click the Insert command, and then choose Insert Sheet.

Excel inserts the worksheet.

NOTE:
You can also insert a blank worksheet by pressing Shift + F11.
Another way to add a blank worksheet is by clicking the plus (+) icon, as shown in Figure 1.1.3-b.

Insert a Worksheet from a Template

To insert a worksheet from a template, open the workbook to which you want to add the worksheet. Then, right-click on a worksheet tab and click Insert.

The Insert dialog box appears.

Click the Spreadsheet Solutions tab, select the type of worksheet you want to add, and then click OK.

Excel inserts the worksheet.

NOTE:
You can also click Templates on Office.com to download spreadsheet templates from the web.

Set the Default Number of Worksheets for New Workbooks

If you usually add worksheets to new workbooks, you can save time by configuring Excel to always include your preferred number of worksheets in every new file.

By default, Excel includes one blank worksheet in each new workbook you create. However, you may find that you typically use three, four, or more worksheets in most of your workbooks. If that’s the case, you may be wasting time adding additional sheets manually. You can save time by telling Excel how many worksheets you want in your new workbooks.

To do this, click the File tab, then Save As, and finally click Options.

The Excel Options dialog box appears with the General tab displayed. Use the Include this many sheets box to specify how many worksheets you want in each new workbook. Click OK. From now on, whenever you create a new workbook, Excel will include the number of worksheets you’ve specified.

You often need to import data into an Excel worksheet from a text file. Microsoft Excel provides a Text Import Wizard to import data from various text file formats:
■ Comma-separated files (.csv) or tab-delimited files (.txt), as shown in Figure 1.1.2-a.

■ Files with fixed-width columns, where there are no delimiters between columns and data starts at fixed positions on each line (Figure 1.1.2-b).

You will often receive data in a Microsoft Word document or in a plain text file (.txt) that you need to import into Excel for analysis. To import a Word document into Excel, you must first save it as a text file.
When importing delimited data from a file, Excel evaluates the file and displays a preview of how the data will be imported. If the data exceeds certain size limits, only a portion is previewed, and a message appears indicating that the data has been truncated due to size limits. However, this does not affect the amount of data actually imported.

For example, the file importationdedonnees.docx contains the amount of time each player played for Dallas in multiple games during a season. It also includes the performance rating for each lineup.
For example, the first two lines indicate that against Sacramento, Bell, Finley, LaFrentz, Nash, and Nowitzki were on the court together for 9.05 minutes and played at a level of 19.79 points (per 48 minutes), which is worse than the average NBA lineup.

Figure 1.1.2-c: Sample data

We want to import this lineup information into Excel so that, for each lineup, the following information is listed in separate columns:
■ The name of each player
■ Minutes played by the team
■ Rating range

A problem arises with the player Van Exel (full name: Nick Van Exel). If you choose the Delimited option and use a space to split the data into columns, “Van Exel” will occupy two columns. As a result, the numeric data for lineups that include Van Exel will appear in a different column compared to lineups that don’t include him.
To fix this, the Replace command was used in Word to change all instances of « Van Exel » to just « Exel ». Now, when Excel splits data at spaces, “Van Exel” only takes up one column.
Figure 1.1.2-d: Updated data with “Van Exel” replaced by “Exel”

The key to importing data from a Word or text file into Excel is to use Excel’s Text Import Wizard. As mentioned earlier, the Word file (in this example importationdedonnees.docx) must first be saved as a text file.

To save a Word document as a .txt file (Plain Text):

• Click the File tab.
• Click Save As.
• Click Browse, then choose where to save your file.
• In the Save as type list, select Plain Text (.txt).
• Enter a name for your file, then click Save.

In the File Conversion dialog box, select Windows (Default) for the text encoding, then click OK.
Your file is now saved as importationdedonnees.txt.

Close the Word document. In Excel, to open importationdedonnees.txt, click File, then Open, then Browse. Navigate to the .txt file folder, select All Files (*.*) in the file type list, select the file, and click Open.
You will see Step 1 of the Text Import Wizard, illustrated in the figure below:

Figure 1.1.2-e: Step 1 of the Text Import Wizard

Step 1 – Key elements to consider:

• Original data type: Select Delimited when items in the text file are separated by tabs, semicolons, spaces, or other characters. Select Fixed width when all items in each column are of equal length.
• Start import at row: Enter the row number to specify where to begin the import.
• File origin: Choose the character set used in the text file. Usually, you can keep the default setting. If the text file was created using a different character set than your system, update this option to match.
• Data preview: Shows how the text appears when split into columns in the worksheet.

In our case, we choose Delimited. However, suppose you mistakenly choose Fixed width. Then Step 2 of the wizard will appear as shown in Figure 1.1.2-f, allowing you to create, move, or delete break lines. Adjusting column breaks in fixed-width mode can be imprecise and cumbersome.

Figure 1.1.2-f: Step 2 of the wizard after selecting Fixed width

If you select Delimited in Step 1, you’ll see another version of Step 2, shown in Figure 1.1.2-g.

Figure 1.1.2-g: Step 2 of the wizard after selecting Delimited

Step 2 – Key elements to consider:

• Delimiter: Choose the character that separates values in your file. If it’s not listed, check Other and type the character manually. This option is unavailable for fixed-width files.
• Treat consecutive delimiters as one: Check this if your data contains multiple consecutive delimiters or complex delimiters.
• Text qualifier: Choose the character that encloses text. When Excel sees this character, all the text between this and the next occurrence is treated as one value—even if it contains a delimiter.
  For example, if the delimiter is a comma (,) and the qualifier is a double quote (« ), "Dallas, Texas" is imported as a single cell value. Without a qualifier or if you choose ' as the qualifier, it may be split into two cells: « Dallas » and « Texas ».
  If the delimiter character occurs inside the text qualifier, Excel ignores it; if it occurs outside, it is treated normally.
• Data preview: Review the split data to ensure columns appear as desired.

In this example, we select space as the delimiter. Checking Treat consecutive delimiters as one ensures that multiple spaces don’t create unnecessary columns. It is recommended to keep Tab selected as well since some Excel add-ins rely on it.

Figure 1.1.2-h: Step 2 of the Text Import Wizard with Delimited selected

When you click Next, you reach Step 3, shown in Figure 1.1.2-i.

Figure 1.1.2-i: Step 3 of the wizard, where you can define formats for the imported data

Step 3 – Key elements to consider:

• Advanced button:
  • Specify decimal and thousand separators to match your region settings.
  • Indicate if negative numbers might have a trailing minus sign.
• Column data format: Select a format for each column shown in the data preview.
  If you do not want to import a column, choose Do not import column (skip).

Once a format is selected, the column header in the preview updates. If you select Date, choose the appropriate date format (e.g., DMY) from the list.

Choose the format that best matches the data to ensure Excel converts it correctly. For example:
■ For monetary values: choose General.
■ For pure numbers: choose Text.
■ For date values: choose Date, then select the date format like DMY.

In this example, choosing General lets Excel treat numbers as numeric values and other items as text.

When you click Finish, the wizard imports the data into Excel as shown below.

Figure 1.1.2-j: The Excel file with lineup information

Each player is listed in a separate column (columns A to E);

• Column F contains the rating
• Column G the game number
• Column H the minutes played
• Columns I and J list the two teams in the match

Of course, if you wish, you can globally replace « Exel » with « Van Exel » to restore the player’s full name in the data.

After saving the file as an Excel workbook (.xlsx), you can use all of Excel’s analytical capabilities to analyze Dallas’s lineup performance.

Creating a Workbook

To start a new task in Excel, you must first create a new blank Excel workbook. When you launch Excel, it prompts you to create a new workbook, and you can click on Blank Workbook to start with an empty file containing a blank worksheet. However, for subsequent files, you must use the File tab to create a new blank workbook.
If you prefer to create a workbook based on one of Excel’s templates, refer to the next section, “Create a new workbook from a template.”

Excel creates the blank workbook and displays it in the Excel window.

NOTE:
Excel offers a keyboard shortcut for quicker workbook creation. From the keyboard, press Ctrl + N.

Creating a New Workbook from a Template

You can save time and effort by creating a new workbook based on one of Excel’s template files. Each template includes a working spreadsheet layout that contains predefined headers, labels, formulas, and preformatted colors, fonts, styles, borders, etc. In many cases, you can use the new workbook as is and simply fill in your own data.
Excel provides more than two dozen templates, and many more are available through Microsoft Office Online.
To create a new workbook from a template:

Excel then creates the new workbook and displays it in the Excel window.

NOTE:
If you have a specific workbook structure that you use frequently, you should save it as a template so you don’t have to recreate the same structure from scratch each time.
Open the workbook, click on File, then Save As. In the Save As dialog box, click on Computer, then Browse.
Click the arrow in the Save as type drop-down list, then select Excel Template.
Type a name in the File name text box, then click Save.
To use the template, click on File and then Open; in the Open dialog box, click on Computer, then Browse, and finally click on your template file.

Saving an Excel Workbook

After creating a workbook in Excel and making changes to it, you can save the document to preserve your work. When you edit a workbook, Excel stores the changes in your computer’s memory, which is erased whenever you shut down your computer. Saving the document preserves your changes to the computer’s hard drive.
To avoid losing your work in the event of a computer crash or Excel freeze, you should save your work frequently—at least every few minutes.

Select a folder where the file will be stored; click in the File name text box and type the name you wish to use for the document; click Save. Excel saves the file.

NOTE:
Excel provides a keyboard shortcut for faster workbook saving. From the keyboard, press Ctrl + S.
If you have previously saved the document, your changes are now preserved, and you do not need to follow the remaining steps in this section.

Opening an Excel Workbook

To view or make changes to an Excel workbook that you previously saved, you must open the workbook in Excel. To do this, first locate it in the folder where it was initially saved.
If you used the workbook recently, you can save time by opening it from Excel’s Recent Workbooks menu, which lists the 25 most recently used workbooks.

The Open tab appears. Click Browse or press Ctrl + O.
The Open dialog box appears. Select the folder containing the workbook you wish to open, then click on the workbook and finally click Open.
The workbook appears in a window.

NOTE:
You can click on Recent Workbooks to view a list of your recently used workbooks.
If you see the desired file, click on it and skip the rest of these steps.

 Automatically Opening Workbooks at Startup

You may often need to open the same few workbooks every time you start Excel. The Recent Workbooks list can help you access these files quickly, but an even simpler method is to configure Excel to open them automatically at startup.
To do this, move the workbooks into a folder that contains no other workbooks, then configure Excel to automatically open each workbook in that folder at startup.
This task assumes you’ve already created such a folder and moved into it the workbooks you want to open automatically.
To do this:

Click on Options. The Excel Options dialog box appears.
Click on Advanced, and in the General section, use the field “At startup, open all files in:” to enter the path to the folder containing the workbooks you want Excel to open. Click OK.

The next time you launch Excel, it will automatically open all workbooks in the folder you specified.

NOTE:
If you are not sure of the exact location of the folder you want to use, open File Explorer in Windows 8 or Windows Explorer in earlier versions of Windows and navigate to the folder.
In Windows 8, 7, or Vista, right-click the Address bar, then click Copy address; in Windows XP, select the text in the Address bar and press Ctrl + C.
You can then follow steps 1 through 3, click in the At startup, open all files in text box, and paste the address by pressing Ctrl + V.