Reference to a cell without source number format

You probably knew it… when we make a direct reference to a cell, i.e. input =A1 in A2, the cell A2 will then follow the number format of A1.  Put it in other words, if the number format of A1 is Date, then the format of A2 will turn into Date automatically once you make a direct reference to it (by inputting =A1).  As usual, a picture tells thousand words; an animated picture tell even more: 🙂

Excel Tips - Trick of referencing a cell without its format.gif(Please pay attention to the “Number Format” on the top…)

But did you know that you may make the reference without using source number format?

I couldn’t find a way to turn that option off.  Nevertheless, a formula trick would do.

Excel Tips - Trick of referencing a cell without its format1.gif

So what’s the formula trick? 

Ctrl+Shift+Enter.

Try it and you will see.

 

 

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.