Tag Archives: find formulas

Tame your computer – fail-safe your formulas

6 Aug

By default all cells in a worksheet can be modified by anyone who has access to that file.

If you want to prevent other users from changing, moving, or deleting formulas you can make those cells read-only. Other data in the sheet can still be changed.

Here’s how:

  1. Select all cells in the worksheet by clicking on the top left corner of the sheet. (Or click in any empty area and press CTRL + A.)
  2. Right-click anywhere in the sheet and click on Format Cells (or use CTRL+1).
  3. On the Protection tab untick Locked and Hidden.
  4. Click OK or press ENTER.
  5. On the Home tab, in the Editing group, click on the Find & Select arrow and click on Formulas.
  6. Right-click any of the selected cells and click on Format Cells (or use CTRL+1).
  7. On the Protection tab tick Locked and Hidden.
  8. Click OK or press ENTER.
  9.  On the Review tab, click Protect Sheet.
  10. Enter a password in the Password to unprotect sheet box and click OK. Re-enter the password in the Confirm Password dialog box.
  11. Click OK or press ENTER.

Step 10 is optional, but if you do not set a password, anybody can remove the protection by simply clicking on the Unprotect Sheet button on the Review tab.  The warning message that pops up even tells you the way to unprotect the sheet.

* Unless stated otherwise, these tips were written for Microsoft Office 2010.

Advertisements

Tame your computer – find a formula

1 Nov

Have you ever wondered where the formulas are in your workbook? Perhaps you’ve inherited a file and don’t have a clue what they’ve done. Or you haven’t looked at the data for a while and would like a quick way to spot all the cells that contain a formula.

Here’s how:

  1. Press F5.
  2. Click on the Special button.
  3. Click on the Formulas radio button.
  4. Click OK or press ENTER.

If the workbook does not contain formulas you will see the message “No cells were found.” If it does, all cells will be highlighted, ready to be protected from being modified. But let’s make that the content of a future tip. (With thanks to Erica Adarve for inspiring me to write this tip!)

By the way, you might want to have another look at tip 478 which explained how you can use the Go To Special functionality to find hidden rows and columns in your worksheet.

 * Unless stated otherwise, these tips were written for Microsoft Office 2010.

Tame your computer – a formula for success

5 May

Have you ever had the need to keep your formulas in a worksheet, but get rid of all the other data? For example, you might want to create a new sheet for the new tax year, containing exactly the same headers and formulas, but not the data from the previous tax year.

Here’s how:

  1.  Select the rows and/or columns that contain the formulas. (Remember the quick way to select a range of cells?)
  2. On the Home tab, in the Editing group, click Find & Select, and then click Go To Special. (Or press CTRL + G and click on the Special button.)
  3. In the Go To Special dialog box, click the Constants radio button.
  4. Click OK.
  5. Press the Delete key on your keyboard.

This will delete all the data, but leave the formulas, ready for next year’s tax year.  (Thanks for the inspiration, Melissa.)