Clean formulas

Modified on Sat, 14 Nov at 10:08 AM

Clean formulas is a feature of the Power-user add-in for Excel that removes the redundant sheet references from the formulas of the current sheet, such as Sheet1!A1 written in Sheet1, in one click. Your formulas become shorter and easier to audit or to share, while the references to other sheets are kept.

In short

  • Where: Clean formulas in the Power-user tab of Excel
  • Scope: all the formulas of the current sheet
  • What changes: references to the current sheet are shortened (Sheet1!A1 becomes A1); references to other sheets stay

How to clean formulas in Excel

  1. Open the sheet whose formulas you want to simplify.
  2. On the Power-user tab of Excel, click Clean formulas. Power-user removes the redundant references from all the formulas of the sheet.

Example: a formula before and after

Here is a formula of Sheet1 before Clean formulas:

Excel formula before Clean formulas, with redundant references to Sheet1

Excel formula before Clean formulas, with redundant references to Sheet1

And the same formula after:

The same Excel formula after Clean formulas: references to Sheet1 removed, references to Sheet2 kept

The same Excel formula after Clean formulas: references to Sheet1 removed, references to Sheet2 kept

All the references to Sheet1 are removed, because the formula is already in Sheet1. The references to Sheet2 are kept, because they are needed. The formula is shorter and easier to read.

Why Excel formulas get longer than needed

Excel formulas quickly become complex. When you audit them or share a workbook, they should be as simple as possible. Yet Excel often adds redundant information: when you type a formula in Sheet1, Excel may write Sheet1!A1 instead of just A1. Clean formulas removes this noise in one click.

See also: other Power-user tools to clean your Excel files, such as Delete empty cells, UnPivot table and the advanced functions.

Frequently asked questions about cleaning Excel formulas

How do I remove the sheet name from a formula in Excel?

With the Power-user add-in, click Clean formulas in the Power-user tab of Excel. It removes the name of the current sheet from all the formulas of the sheet, for example Sheet1!A1 becomes A1 in Sheet1.

Does Clean formulas change the results of my formulas?

No. Power-user Clean formulas only removes references to the sheet the formula is already in. References to other sheets are kept, so the formulas point to the same cells.

Which formulas does Clean formulas change?

Power-user Clean formulas runs on all the formulas of the current sheet.

How do I simplify complex Excel formulas?

Start by removing redundant references with Power-user Clean formulas: references to the current sheet are shortened, which makes long formulas easier to read and audit.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article