Join Dennis Taylor for an in-depth discussion in this video Using the FORMULATEXT and N functions for worksheet and formula documentation, part of Excel Tips Weekly.
- Providing documentation for a worksheet is sometimes a cumbersome process, and not exactly exciting. But let's say, that in this worksheet, you've got some formulas, and you'd like to make sure that others who are using it understand what's going on. I've got formulas in column H, let me zoom in by holding down ctrl and moving the mouse wheel. Formula in column H, that in effect says, bonuses are going to be achieved, by one of two methods, possibly both. If your job rating is over 3, you get a $2000 bonus.
If your years of service are over 14, you get a $500 bonus. And notice how they're being added, so possibly, you'll have a bonus of as much as 2500, or if not, maybe only 2000, or only 500. We see some of those results below. But others might be looking at this, and we want to expand this, and explain what's going on. So, double clicking here. A function called N, think for the word note, maybe. +n, as you type it, doesn't have to be capitalized.
Then in (", and practically anything you wanna type. So, this is a Special bonus, just for 2015, for years of service > 14. Say it any way I wish, "). So all we're doing is using the N function to put in some text to explain what's going on. It's not changing the result here, and I'll double click, copy this down the column.
But it explains what's going on, and I could have done this by putting in additional information about the first part of the bonus as well, the part relating to Job Rating. But nevertheless, you can see what's going on here. Now, if you hadn't heard about that function, and somehow discovered it from the formulas tab in the ribbon, if you go to more functions. It is in the category called information, but the description here doesn't even come close to what we just used it for. It has other uses, I don't think many people use this function at all, in fact, it's there for compatibility with some other and older versions of Excel, it converts non-number value to a number, that's not even close to what we did.
So we used it for documentation purposes. Now, another approach to this, is actually displaying the formula next to a cell. Now, one way to do this is to double click a cell, highlight all the data while editing, press ctrl+c, escape, go to an adjacent cell, put in the spacebar, and then a ctrl V. In this case it's pretty long, you wouldn't see it all, unless you made the column really, really wide, so, not a great use of that capability there.
There is another feature that might work better, though. Although, with a long entry, it's gonna be pretty tough. We've got some statistics here, calculating something here, but we're not quite sure what. And what is the function being applied here? It's going to tabulate an average here, for the data that we see in the worksheet to the left, based on something to do with cell L 2. Now, sometimes rather than actually explaining the function, what if we could display it here? Now, when we display a function, the actual function itself, and you can do that with a shortcut called ctrl ~, keystroke shortcut ctrl ~.
That displays the function, but while it's being displayed, we're not seeing the result. So we can press ctrl ~ again, come back here. Here's an alternate, now we can put this right next to the formula, we could put it on the left side, possibly. And the words we could make column, an example here. We could make column K a lot narrower, trying it this way. And just have these. And then to the left of this, we could display what this formula's doing. I'm going to put it off to the right, since we have unlimited text.
And I'll do it for all these, because we see different kind of functions here. I'll just say they're being used in a different way. So, you can display what a formula is doing with the function =formulatext. Now, it's not always that you're doing this for multiple cells, but I'm doing it for four cells at once here, by highlighting those cells, typing in formulatext, ( and referring to cell K2, and I'll simply press ctrl enter. Now, that's going to stay there. And if you change your mind, for example, as to what the function is going to be doing, so for example, I might come back here and say, well, rather than doing an AVERAGE here, maybe I'm more interested in a sum instead, so I'll change that to sum.
Obviously it's gonna be a different total, and because this function right here is called FORMULATEXT, it's reflecting what that formula is, we see it being displayed. So, it provides documentation in a different kind of way, obviously, than the N function. But this is valuable at times, and again, it depends upon the environment that you're working in, and how much documentation you really need. But being able to see a function, and seeing the actual formula being displayed could be helpful. Now, you can also make a case in this example, since the function itself is using column L, full time, we don't necessarily have to have this here.
Most of the time, when I see formula text used, it's used right next to the data. But you can put it anywhere you want, so I could possibly move these over here, and then take this data and move it here. It all depends upon what's likely to work best for you. Once again, making the columns a little bit wider, and so on. So now we have formula text in each case, referring to the formula that's in the cell to its adjacent left. Formula text and the letter N, two different functions used for documentation.
Author
Updated
1/26/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 14m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Using the FORMULATEXT and N functions for worksheet and formula documentation