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

  1. From sheet tab short cut menu choose Select all sheets.
  2. 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.

  1. From the File menu, select Save as.
  2. Select Options. In Excel 2002 you will find new option, select Tools, Options, Security tab..
  3. 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:

  1. From the Tools menu, select Options, General, and User name.
  2. 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
End Function

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.

  1. Select a cell in the sheet, and press Ctrl+F3.
  2. In the Define Name dialog box, type the name FormulaInCell.
  3. Type the formula =GET.CELL(48,INDIRECT("rc",FALSE)) in the Reference field.
  4. Select all the cells in the sheet by pressing Ctrl+A.
  5. From the Format menu, select Conditional formatting.
  6. In Condition 1, select Formula is.
  7. In the formula box, type =FormulaInCell.
  8. Click Format.
  9. From the Font tab, select the color yellow, and click OK.
  10. Click OK.

Compliments of ExcelTip.com

You may like these other stories...

Event Date: May 29, 2014 In this presentation Excel expert David Ringstrom, CPA brings you up to speed on the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both...
No field likes its buzzwords more than technology, and one of today's leading terms is "the cloud." But it's not just a matter of knowing what's fashionable. Accounting professionals who know how to use...
There is a growing trend of accountants moving away from traditional compliance work to more advisory work. Client demand is there, but it is up to the accountants to capitalize on that. What should accountants' roles be...

Upcoming CPE Webinars

Apr 22
Is everyone at your organization meeting your client service expectations? Let client service expert, Kristen Rampe, CPA help you establish a reputation of top-tier service in every facet of your firm during this one hour webinar.
Apr 24
In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
Apr 25
This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.
Apr 30
During the second session of a four-part series on Individual Leadership, the focus will be on time management- a critical success factor for effective leadership. Each person has 24 hours of time to spend each day; the key is making wise investments and knowing what investments yield the greatest return.