The Find feature can be helpful if you are trying to locate information not currently visible on the screen. You can search for information used in formulas, values, and comments.
Finding Information
1.From the Ribbon, select the Home command tab
2.In the Editing group, click FIND & SELECT» select Find...
The Find and Replace dialog box appears, with the Find tab selected.
3.In the Find what text box, type the text or data to be found
4.OPTIONAL: Adjust the searching options
5.Click FIND NEXT
The information is found and selected. NOTES:
Clicking FIND NEXT again finds subsequent occurrences of the information in the document.
If Excel cannot find the information you are looking for, a message to that effect will be displayed.
6.OPTIONAL: To see the each location that this text or data is located, click FIND ALL
A pane opens below the Find and Replace dialog box with a list of each location.
7.To close the dialog box, click CLOSE
Searching Options
If you would like to narrow your search to find more specific results, you can limit your results by using the available search options.
1.From the Find and Replace dialog box, click OPTIONS >>
Additional options appear in the Find and Replace dialog box.
Option
Description
Format...
Match content with specific formatting
Within
Specify the search area (i.e., worksheet or workbook)
Search
Control the order of the search: left to right (columns) or top to bottom (rows)
Look In
Limit the search to type of content: values, formulas, comments
Match case
Limit search results to instances of the text or data that have the same case (i.e., uppercase or lowercase) as the text in the Find what text box EXAMPLE: Spring instead of spring.
Match entire cell contents
Limit search results to cells where an exact match occurs EXAMPLE: Smith will locate Smith but not Chris Smith.
You may choose to paste many different aspects of your copied data.
All
pastes all information in the copied cell; this is the default option
Formulas
pastes only the formula from the selected cell NOTE: Any relative cell references within the formula are changed to reflect its new location.
Values
pastes only the result of the formula from the selected cell
Formats
pastes only the formatting applied to the cell (e.g., font or color of the text)
Comments
pastes only annotations that were made to the cell
Validation pastes any rules about what text can be entered in the cell (e.g., only numbers between 1 and 25)
All except borders
pastes the formatting and contents of the cell, excluding any cell borders
Column widths
pastes only the width of the selected column
Formulas and number formats
pastes the formula from the selected cell and any formatting applied to numbers (e.g., number of decimal places displayed)
Values and number formats
pastes the numerical result of the formula from the selected cell, as well as any formatting applied to numbers (e.g., number of decimal places displayed)
None the values that are being pasted override any data already in the destination cells; this is the default option
Add adds the values that were copied to the data in the destination cells
Subtract
subtracts the values that were copied from the data in the destination cells
Multiply
multiplies the values that were copied by the data in the destination cells
Divide
divides the values that were copied by the data in the destination cells
Skip blanks
pastes the contents of all cells except for empty cells NOTE: This option is only useful when pasting a range of cells into an area that already contains information. This will ensure that a blank cell will not be pasted in place of information that is already in place.
Transpose
causes information that extended down a column to extend across rows and vice versa NOTE: If you select more space for the information to be pasted into than you have cells to be pasted, the information will be repeated to fill the selected space. For example, if you copy one column and paste it into four rows while using Transpose, the information from the column will be repeated in every row.
WindowsOnly:
All using Source theme
when using a document theme, pastes all information using formatting from the theme that was applied to the copied cells
Paste Link
connects the cells that were copied with the pasted cells; if the data in one group changes, so will the data in the other group
The Paste Special dialog box allows you to select which aspects of the copied data you would like to paste.
1.Select the cell(s) whose values should be copied
2.Windows: Press [Ctrl] + [C] Macintosh: Press [] + [C] OR Windows: From the Home command tab, click COPY
Macintosh:From the Standard toolbar, click COPY
3.Select the cell(s) where the results should be pasted
4.Windows: From the Home command tab, click the PASTE arrow» select Paste Special... Macintosh: From the Edit menu, select Paste Special... The Paste Special dialog box appears.
5.Select the desired options EXAMPLE: To paste only the results of a formula, select Values
6.Click OK
The results of the formula are pasted into the selected cell(s).
This option copies (repeats) the information from one cell to another. To extend the series, refer to the mouse option.
1.Type the information (cell contents or formula) in the first cell of the group
2.Starting with the cell containing the contents to be copied, select the group of cells to be filled
3.From the Ribbon, select the Home command tab
In the Editing group, click FILL » select the appropriate option:
Down
repeats the contents of the first cell throughout the selected cells below it.
Right
repeats the contents of the first cell throughout the selected cells to the right of it.
Up
repeats the contents of the first cell throughout the selected cells above it.
Left
repeats the contents of the first cell throughout the selected cells to the left of it.
Across Worksheets
repeats the contents of the selected cells in another selected worksheet.
Series...
fills in data that is part of a pattern, rather than simply repeating the data across selected cells. This option brings up the Series dialog box, which allows you to select the type of pattern you would like to use. EXAMPLE: If the first cell contains the day Sunday, Excel will fill the following cells with Monday, Tuesday, etc.
Justify
distributes text in the first selected cell throughout several selected rows. NOTE: This option changes the contents of these cells by moving the data to fit within the new rows.
OPTIONAL: If you selected the Series option
In the Series dialog box, select the desired options:
Series in
determines whether the additional information will appear in a vertical line (rows) or in a horizontal line (columns).
Type
determines whether the additional information will differ from the previous entry by a specific interval (Linear), by multiplying the previous entry by a specific value (Growth), by creating a series of dates according to the selected date format (Date), or by automatically detecting what type of fill you would like to apply (AutoFill).
Date Unit
when Date is selected as the type, determines whether numbers are listed for each day (Day) for each day in the weekday (Weekday), for that day of every month (Month), or for that day of every year (Year).
Trend
when Linear or Growth is selected as the type, calculates the average difference between values existing in selected cells and uses that difference to calculate the fill values.
Step Value
when Linear or Growth is selected as the type, determines the amount that the previous value is added to or multiplied by to come up with the results for the next cell. When Date is selected as the type, it determines how many dates should be skipped between each recorded value.
Stop Value
determines a value at which the series should no longer be continued.
Click OK
The fill is applied.
Filling Cells: Mouse Option
Using this option will extend the data in the series to the selected cells.
1.Type the information (cell contents or formula) in the first cell of the group
2.In this cell, move your pointer over the fill corner so your pointer changes into crosshairs NOTE: For this option to work, you must ensure that the pointer changes into a crosshairs before filling.
3.Click and hold the crosshairs
4.Drag the mouse in the direction you want the information to be copied NOTES:
You can drag the corner in any one direction; left, right, up, or down.
5.Release the mouse button
The fill is applied.
Working with Custom Fills
Custom fills allow you to select what information you want repeated. For example, if you typed the UW-Eau Claire terms (Fall, Winterim, Spring, Interim and Summer) several times, custom fills allow you to automate the process. Using the regular Fill function, Excel would repeat "Fall" in all selected cells. With a custom fill, when you type Fall and use the Fill command, Winterim, Spring, Interim, and Summer will be entered in the adjacent cells. This feature is also handy when using Excel to create a grade book. For example, you could create a custom fill for letter grades (e.g., A, A-, B+, B, B-, ..., F).
Custom fills can be created from scratch or from an existing section of your worksheet. After creation, Excel will automatically apply your custom fills when using the AutoFill function. NOTES:
Custom fills will work consistently only with word lists.
Lists of values do not work consistently.
Creating a Custom Fill
1.From the OfficeButton menu, click EXCEL OPTIONS
The Excel Options dialog box appears.
2.From the Categories list, select Popular The dialog box refreshes to show the Popular options.
3.In the Top options for working with Excel section, click EDIT CUSTOM LISTS...
The Custom Lists dialog box appears.
4.In the Custom lists scroll box, select NEW LIST
5.In the List entries text box, type the information you want to be added in the custom fill, pressing [Enter] after each entry EXAMPLE: A, A-, B+, B, B-, ..., F NOTE: Be sure to type every unit in the order you want it to appear.
6.Click ADD
Your new entries appear in the Custom Lists scroll list.
7.Click OK
The Custom Lists dialog box closes.
8.Click OK
The Excel Options dialog box closes.
Importing Custom Fills
If you have a group of cells that contains information you would like to repeat elsewhere, you can create a custom fill from them.
1.Select the group of cells with the information you want to make into a custom fill
2.From the Office Button menu, click EXCEL OPTIONS
The Excel Options dialog box appears.
3.From the Categories list, select Popular The dialog box refreshes to show the Popular options.
4.In the Top options for working with Excel section, click EDIT CUSTOM LISTS...
The Custom Lists dialog box appears.
5.Click IMPORT Your selection appears in the List entries text box.
6.Click OK
The Custom Lists dialog box closes.
7.Click OK
The Excel Options dialog box closes.
Using a Custom Fill
1.To use your custom fill, place the cell pointer in any cell and type the first entry EXAMPLE: Type A HINT: If you have created several custom fills that begin with the same entry, you may need to type the first two entries in the first two cells in order for Excel to know which fill you want to use.
2.In this cell, move your pointer over the fill corner so your pointer changes into crosshairs NOTE: For this option to work, you must ensure that the pointer changes into a crosshairs before filling.
3.Click and hold the crosshairs
4.Drag the corner in the direction you want the information to be copied NOTE: For more information about filling cells, refer to Filling Cells: Mouse Option.
5.Release the mouse button
The cells will be filled with the information you provided.
Adding a comment to a cell allows you append additional information to that cell in a pop-up message. The comment, along with the username of the person who inserted the comment, appears when you point to the cell.
Adding Comments: Ribbon Option
1.Select the cell to which the comment will be added
2.From the Ribbon, select the Review command tab
3.In the Comments group, click NEW COMMENT
The Comment box appears, displaying your username.
4.Type your comment
5.When finished, click another cell
A red triangle appears in the upper right-hand corner of the cell indicating a comment has been attached. When you place your mouse over the cell, the comment appears.
Cell with comment
Text of comment revealed
Adding Comments: Mouse Option
1.Right click the cell to which you want to add a comment » select Insert Comment
The Comment box appears, displaying your username.
2.Type your comment
3.When finished, click another cell
A red triangle appears in the upper right-hand corner of the cell indicating a comment has been attached. When you place your mouse over the cell, the comment appears.
Editing Comments
Editing a comment allows you to change or add to the information you have placed within a cell as a comment.
Editing Comments: Ribbon Option
1.Select the cell that contains the comment to be edited
2.From the Ribbon, select the Review command tab
3.In the Comments group, click EDIT COMMENT
The Comment box appears, containing the text of the comment.
4.Make the appropriate changes to the comment
5.When finished, click another cell
Editing Comments: Mouse Option
1.Right click the cell which contains the comment » select Edit Comment
The Comment box appears, containing the text of the comment.
2.Make the appropriate changes to the comment
3.When finished, click another cell
Displaying/Hiding Comments
Usually, comments appear only when your mouse is hovering over the cell containing the comment. However, you can choose to have comments remain on the screen or disappear from the screen when your pointer is not over the cell.
Displaying/Hiding Comments: Single Cell
1.To display a comment, right click the cell which contains the comment » select Show/Hide Comments
That comment is displayed.
2.To hide a comment which has been displayed, right click the cell which contains the comment » select Hide Comment
That comment is hidden until you place your mouse over the cell.
Displaying/Hiding Comments: Ribbon Option
1.Select the cell which contains the comment
2.From the Ribbon, select the Review command tab
3.In the Comments group, click SHOW/HIDE COMMENT
NOTE: When you click to show, the comment appears next to the selected cell. When you click to hide, the comment disappears.
Displaying/Hiding Comments: All Cells
1.From the Ribbon, select the Review command tab
2.In the Comments group, click SHOW ALL COMMENTS
NOTE: This button acts as a toggle switch. Clicking the button once will display your comments: clicking it again will hide all comments.
Moving Comments
If your comments are overlapping text that you would like to read, you can move the comment to a more convenient place. The comment will still remain attached to the cell it was originally created for, but you will be able to move it to a more suitable location.
1.Display the comment you would like to move
2.Move your mouse over the border of the comment
The pointer becomes a four-headed arrow.
3.Click and hold the border of the comment
4.Drag the comment to the desired position
An arrow appears, connecting the comment to the cell.
5.Release the mouse button
Deleting Comments
Deleting comments allow you to either remove the comment you have placed within an individual cell, or to remove all of the comments you have placed within a document.
Removing Comments from a Single Cell: Mouse Option
1.Right click the cell which contains the comment » select Delete Comment
Removing Comments from a Single Cell: Ribbon Option
1.Select the cell which contains the comment to be deleted
2.From the Ribbon, select the Review command tab
3.In the Comments group, click DELETE COMMENT
Removing Comments from All Cells
1.Open the sheet with the comments to be deleted
2.From the Ribbon, select the Home command tab
3.In the Editing group, click FIND & SELECT» select Comments
All comments on the active sheet will be selected.
4.From the Editing group, click CLEAR» select Clear Comments
All comments are cleared.
Printing Comments
Comments must be displayed before printing. For more information, refer to Displaying Comments.
1.Open the worksheet that contains the comments you want to print
2.From the Ribbon, select the Page Layout command tab
3.In the Page Setup group, click PAGE SETUP
The Page Setup dialog box appears.
4.Select the Sheet tab
In the Print section, from the Comments pull-down list, select the desired option:
At end of sheet
Prints all comments separately, as a group at the end of the printed document. These comments will display a cell reference to inform you of which cell they are attached to.
As displayed on sheet
Prints all displayed comments as they appear in your document. NOTE: These comments may extend past the page when printed. However, it is possible to avoid this by using the Page Layout view and making sure to move the comment boxes within the printable area of the page.
6.Click PRINT... The Print dialog box appears.
7.Make the appropriate selections NOTE: For more information on printing, refer to Printing Basics.
] + [C]
» select Paste Special...
