Five Quick and Easy Excel Tips
Complimenets of ExcelTip.com
Tip 1: Searching all Sheets in a Workbook
To search for text, use the keyboard shortcut Ctrl+F or choose Edit, Find.
To search and replace text, use the keyboard shortcut Ctrl+H or choose Edit, Replace.
Searching and replacing all sheets in the Workbook
- From sheet tab short cut menu choose Select all sheets.
- Press Ctrl+F or Ctrl+H to find and replace.
Note: The Ctrl+F keyboard combination works in Excel 97 version only single sheet.
Tip 2: Password Protection to Prevent Opening a Workbooks
In all Excel versions, you can use a password to prevent opening a workbook.
- From the File menu, select Save as.
- Select Options. In Excel 2002 you will find new option, select Tools, Options, Security tab..
- Type the password twice, and click OK.
Tip 3: Changing the Name of the Comment Author
By default, each Comment includes the author's name.
To change or cancel the name of the Comment author, perform the following steps:
- From the Tools menu, select Options, General, and User name.
- Change or delete the user name as desired.
The change will only apply to new Comment that you insert.
Tip 4: Printing Comments
From the File menu, select Page Setup, and click the Sheet tab. Before printing, select one of the following options in the Comments box:
- None - Will not print comments.
- At end of sheet - Will print the comments on a separate page after printing the sheet.
- As displayed on sheet - Will only print the comments that are displayed.
Print a Single Comment
Select a cell containing a Comment. From the File menu, select Page Setup, Sheet. In the Comments box, select At end of sheet. Now click OK, and then click the Print icon
Tip 5: Identifying and Formatting Cells with Formulas
Excel does not provide a formula that identifies formulas. VBA has a function called HasFormula.
The solution is to create a custom function to identify a cell containing a formula.
Function FormulaInCell(Cell) As Boolean
FormulaInCell = Cell.HasFormula
Use the technique described below to combine the Get.Cell formula with Conditional Formatting to format cells containing formulas.
After creating the formula FormulaInCell, combine it with Conditional Formatting.
- Select a cell in the sheet, and press Ctrl+F3.
- In the Define Name dialog box, type the name FormulaInCell.
- Type the formula =GET.CELL(48,INDIRECT("rc",FALSE)) in the Reference field.
- Select all the cells in the sheet by pressing Ctrl+A.
- From the Format menu, select Conditional formatting.
- In Condition 1, select Formula is.
- In the formula box, type =FormulaInCell.
- Click Format.
- From the Font tab, select the color yellow, and click OK.
- Click OK.
Compliments of ExcelTip.com