January 17, 2017
Hi fellow Excel Addict,
Welcome 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?
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)
If you missed my last newsletter, you can click here to view it online.
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.
This is a great way to show end users, at a glance, the formulas that are used in certain cells.
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 (&).
="<<< This is the formula I used: " & FORMULATEXT(B9)
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.
If you've found this tip helpful, please click here to share it.
| 'Spreadsheets Tips
Excel Addict' and 'Excel in Seconds' are publications of
Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630