Click here to get more tips like this every week

Excel In Seconds Tips and Tricks from The Excel Addict - Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

January 17, 2017

Greetings from The Excel Addict

Hi fellow Excel Addict,

Francis Hayes - The Excel AddictWelcome to 2017.

I hope you've had a wonderful and safe Christmas and New Year. Thankfully I made it back from my Christmas break unscathed as well.

Christmastime is a lot of fun, getting together with family and friends, going to parties. It was nice that Tina and I got to visit our daughter Stephanie in Halifax, Nova Scotia for a few days. It was her first Christmas away from home so it was a very special holiday treat for us all.

Although I've thoroughly enjoyed this long break away from my newsletter, I can't say that I'm well rested because it was a pretty hectic schedule to fit into such a short amount of time. Luckily though, the weather here hasn't been too bad. Enough snow for a 'white Christmas' but not too much to hamper getting around — that was until yesterday! We woke up to an unexpected 30cm of snow. But it wasn't so bad. That's what winter is all about, right?

Francis Hayes - The Excel Addict

I am looking forward to sharing more of my Excel addiction with you in 2017. I especially enjoy hearing from readers who discover that the more they learn about Excel's hidden capabilities the more excited they get about new possibilities. I sense a little 'Excel addiction' creeping in. I hope that is you too.

If you have any comments, questions or suggestions for me for this coming year, please let me know. I can't guarantee that I will deliver on them all but I really do welcome your feedback.

In today's 'Excel in Seconds' tip, I'm going to show you how you can quickly 'Show the Actual Formula Not the Result'.

If you missed my last 'Excel in Minutes' tip, (way back on December 15th) 'Add A Dynamic Date Range Title To Your Report', you can read it here now.

Please feel free to share my newsletter with your Excel friends — they will thank you for it.

Hope you're having a great week and keep on Excelling,
Francis Hayes (The Excel Addict)

Francis Hayes (TheExcelAddict.com)

TheExcelAddict.com Quote of the Day

"Knowing Is Not Enough; We Must Apply.
Wishing Is Not Enough; We Must Do."

-- Johann Wolfgang Von Goethe --

If you missed my last newsletter, you can click here to view it online.

Excel in Seconds with TheExcelAddict.com

Show the Actual Formula Not the Result

Starting with Excel 2013 there was a new function was added that Excel users have been asking for for decades. But, apparently, many Excel users still haven't discovered it.

Let's say you have a worksheet where
, in a cell next to a formula, you want to show the actual formula for descriptive purposes.

Using this new FORMULATEXT function is an easy solution.

The syntax of the function is:


reference = can be a single cell or range of cells. If a range of cells is used for reference, only the formula from the top left cell of that range is returned. You can also reference cells from other worksheets and workbooks, however, if a referenced workbook is closed, the formula will result in an #N/A error.

Formula Text Function in Microsoft Excel 2013 2016 365

This is a great way to show end users, at a glance, the formulas that are used in certain cells.

Toggle Between Displaying Formulas and Results: If you just need to temporarily view the formulas in your worksheet, you can toggle between displaying formulas and results by using the keyboard shortcut CTRL+` (use ^` on a Mac).

Keyboard Shortcut Toggle Formulas in Microsoft Excel 2013 2016 365

Since the FORMULATEXT function displays the formula as a sting of text, if you want, you can include additional text in the same cell. You simply put any additional text within double quotes and join it to the FORMULATEXT function by using an ampersand (&).

For example:
="<<< This is the formula I used: " & FORMULATEXT(B9)
Show Formula With Descriptive Text in Microsoft Excel 2013 2016 365

Unfortunately, since this is still a formula you cannot apply different formats to different parts. For example, you cannot use different font colors for the descriptive text and the formula text.

Additionally, if you use this function in situations where the referenced formula won't be changing, you can convert your description to static text by copying it and clicking the 123 icon on the Paste menu or choosing Paste Special, Values. That way you won't need to clutter up your worksheet with unnecessary formulas.

I hope you will find uses for this function.

Thanks for supporting this newsletter and website

Earnings Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that for a few dollars. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are ever not completely satisfied with anything I recommend, please let me know and I will get your money back for you...GUARANTEED. You can't lose.
 'Spreadsheets Tips From An Excel Addict' and 'Excel in Seconds' are publications of TheExcelAddict.com.
Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630