Tag: formulas

Referencing an Excel Sheet With An Apostrophe in its Name – Solved!

Posted on by Bb

The Problem – Apostrophes in Sheet Names

You are trying to use a formula to reference an Excel Sheet with an apostrophe in its name and you keep receiving an error message or a #REF! reference error.

The Solution (Very Easy!)

Great news! Simply replace the apostrophe with two apostrophes in your formula. That’s right, two consecutive apostrophes, not a quote. Very simple solution indeed!

Below I provide two examples. You can manually update the name in your formula (or in the cell you are referencing if using an indirect lookup), or if working with many sheet names, you may opt to use the substitute solution shown below (formula displayed in E5). 

=substitute(A4,"'","''")

The substitute can be nested in an indirect formula also.

Excel Sheet with an apostrophe solution
Excel Sheet With An Apostrophe Solution

Please comment below on any other challenges you would like for me to cover. I am contemplating doing a quick solution series in this format!

Also, be sure to check out Dose for Excel (click the image below)! Add over 100 functions to Excel to increase your productivity and more!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in. Thanks for supporting my blog!

Dose for Excel - +100 Functions

Some topics addressed in this post:
Ref Error in lookup
#REF! Error in lookup
Ref Error in formula
#REF! Error in formula
How to fix ref error in Excel from apostrophe
Apostrophe in Excel sheet name issue



multiply an entire column

Multiply an Entire Column by a Number in Excel (without using a formula)

Posted on by Annie D

Are you looking to multiply an entire column or range by a number? Get ready for a quick and easy tutorial!

You could always use another column and have the formula to do this, but this way is quick and easy if you do not plan on changing the values.

  1. All you need to do is have the number that you want to multiply in a cell (any cell).
  2. Now copy this cell and paste as special in the cells/range/column where you want to multiply it.
  3. In the Paste Special dialog box, choose the multiply option (in the Operation section) and click OK.  Your column/cells/range should be multiplied!
  4. If desired, can delete the cell that has the value which you multiplied by.

The resulting data is static/fixed so there’s no need to change any formula to value after multiplying using the paste special function.

Multiply An Entire Column

As always, I hope this helped you! Simple solutions are the best!

If I helped you, please consider buying me a coffee via PayPal! Thanks!!

Subscribe for more great content!