One of the most common questions in my youtube channel is “How to Write NOTES inside a Function in Excel”
Sometimes I build complex functions with multiple levels of nesting.
To make it easy to understand the concept I followed, I add some descriptive notes in the context of the function for future reference. These notes remind me of what I did in building the function. I am not talking about adding comments to the cell (Review Tab ►New Comment), but rather regular text description, within the function itself.
In this article, I will show you how to type ANY text inside a function, that does not change the result of the function.
I will also give you 3 Amazing Bonus Tips on how to do the same thing in Power Query or in Power BI or in VBA.
Now let’s see how I do that in Excel.
Table of Contents
How to Write Notes inside Function in Excel
Writing notes inside a function in excel can be done easily by following these simple steps.
I want to talk about a little-used function consisting of one single character: That’s the “N” function, which returns a value converted to a number. It requires a single argument, which is the value to convert.
This is what the function does for each type of argument:
When the value provided to the “N” function is a Number, it returns the same number. If it’s a Date, it returns the serial number for the date. A TRUE, returns 1 while a FALSE returns 0. Finally, any TEXT used as input returns a zero. This is the key to creating notes. Because if you have any number and you add 0, the result will be the same number, no change.
Let’s clarify the concept with some examples.
Example # 1
Extracting Records Using An Aggregate Function
Prior to dynamic array functions, extracting records conditionally was difficult, since I had to create a function with 7 levels of nesting to do that.
In this example, I have a list showing a Date, a Region, Rep and Sales in columns B:E. I also have a drop list in cell H3 from which I can select a sales Rep and accordingly, extract all the transactions for the selected Rep in columns G:J
Here is the function I used in Cell G5
=IF(ROWS(B$5:B5)>$I$2,””,INDEX(B$5:B$213, AGGREGATE(15,6,ROW($B$5:$B$213)-ROW($B$5)+1/($D$5:$D$213=$H$2),ROWS(G$4:G4))))
You can learn how I created this function by watching this video
I want to add a little description to remind myself how I built this function. So, after the closing bracket of the aggregate function I typed a little note inside an “N” function which reads:
+N(“The Aggregate function returns the row number where the condition is met, to the INDEX function”)
Since the “N” function converts text to zero, if I add the zero to the result of the AGGREGATE Function, it does not change anything. I copied the function across and down. The function works fine and is dynamic.
Note that text must be typed in double quotation.
Example # 2
A VLOOKUP Function with 2 Table Arrays
In this example, I have a list of books in columns A:F. I want to extract the cost for each title by creating a VLOOKUP function in column F. But, I do not have a single table array, since the titles and cost come from 2 other lists in columns H:I & K:L for the VLOOKUP function to work, I stacked these 2 lists together inside a VSTACK function.
The function I created in Cell F2 reads:
=VLOOKUP($A2,VSTACK($H$2:$I$24,$K$2:$L$34),2,FALSE)
I will add a little note to this function, before the closing bracket of the VSTACK function. This note has a value of zero and it looks as the third argument of the VSTACK function as follows:
=VLOOKUP($A2,VSTACK($H$2:$I$24,$K$2:$L$34,N(“Take Notes Inside a Function”)),2,FALSE)
Notes:
- Adding a zero as a 3rd. argument did not change the result of the function.
- In this example, the “N” Function is separated by comma from the previous argument.
Example # 3
Single Cell Dynamic Array Function.
In this example I have a long list of Student Names with their score in 3 subjects. My goal is to Extract in 1 cell the name(s) of students with highest Score separated by commas, in cell G1.
Here is the function I created in Cell G1:
=ARRAYTOTEXT(LET(
a,BYROW(B2:D18,LAMBDA(x,SUM(x))),
b,A2:A18,FILTER(b,a=MAX(a))))
You can learn how I created this function by watching this video
I want to add a little description to remind myself how I built this function. So, after the closing bracket of the aggregate function I typed a little note inside an “N” function which reads:
=ARRAYTOTEXT(LET(
a,BYROW(B2:D18,LAMBDA(x,SUM(x)))+N(“Returns the Total Score for each Student”),
b,A2:A18,
FILTER(b,a=MAX(a))))
Since the “N” function converts text to 0, adding zero to the result of the SUM function does not change anything.
I also recommend you take a look at this article to learn more about Switching Charts with A Drop list
Three Bonus Tips
Bonus Tips #1
Add Notes or Comments in Power Query.
Any description you add to the applied steps in Power Query or to the M code in the advanced editor, simplifies the concept to other users and reminds you of the concept you used for building a solution.
In this example, I build a dynamic month calendar in Excel for any month in any year. Using 3 drop lists, I build any date in cell E3 and automatically the month calendar corresponding to that date is created.
I created my dynamic solution in Power Query.
You can learn how I did that by watching my YouTube tutorial in the link below:
To switch to the query editor, click on Queries & Connections on the Data Tab, then double click on the query named Calendar.
The Query editor opens. Under Applied Steps right click the step
“ Extracted First Characters” ►and click on Properties ► The Step Properties dialog box opens ► Type any text or description ►OK
Now a little Information icon appears to the right side of the step, and, if you hover over the step you can read the description in a tooltip.
You can also add a comment to the M Code in the advanced editor. So, if you click on “Advanced Editor” either on the Home Tab or the View Tab, the Advanced Editor opens.
To type any comment just precede it with 2 forward slashes. The comment appears in Green.
You may also add multiple lines of text or comment a step(s) so that it will still be available in the code but is not an executable step. To do that precede the step(s) with a forward slash and asterisk then after the step types an asterisk and a forward slash:
Description or Step
Don’t forget that by deactivating a step or more you need to modify the subsequent step in a way that it refers to the last active step above it.
Bonus Tips #2
How to add a comment in VBA?
A comment in VBA can be any text, a description, an explanation or one or multiple steps that you want to keep in the code without executing them.
In this example I have a list in the range A1:E25 and when I select any cell in the list the column and row of the selected cell are highlighted in yellow. But, if I click outside the list nothing is highlighted
If you have any doubts regarding using a Google Sheet or an Excel. You can read this article here.
You can learn how I created this useful functionality, either by using conditional formatting or by using a VBA code, by watching my tutorial on YouTube, here is the Link:
Let’s switch to the visual Basic Editor to write a comment by hitting ALT + F11. You can write any comment, description, explanation, or text just by typing a single quotation preceding your text. So, I’ll be writing:
‘Did You Subscribe to the OfficeInstructor channel on YouTube?
This comment does not affect the functionality of the code.
Another way of commenting steps is to use the “Edit” toolbar. You can add the Edit toolbar by clicking on the VIEW menu ► Toolbars ►Check Edit.
In my code, I currently highlight the column and row of the selected cell in Yellow (ColorIndex= 6) but I have 2 line of code that are commented in green color that show the ColorIndex=4 which is Green.
Using the Edit toolbar, I want to Uncomment the lower 2 lines (Select them and Uncomment Block) and also comment the first 2 lines (Select them and comment Block)
Anny comment in VBA (green text) is NOT an executable part of the code. If you switch back to Excel by hitting ALT + F11, selecting any cell in the list, highlight the column and row in green.
Bonus Tips #3
Adding Notes or Comments in Power BI Report
In this example, I have a report created on the Power BI desktop that shows the population by continent as TreeMap chart.
I saved the report then published it to MyWorkspace on Power BI Services. Home Tab ► Publish (You should have Signed in with your credentials to do that).
Power BI desktop asks where you want to save your report. I will save it to My Workspace (Like saving in My Documents on your local machine).
Once the report is published, a message box appears telling you it was Successfully published. It also gives you the option of clicking on a link to open your browser and see the report in the clouds. I will do that.
My browser opens and I see the report in My Workspace in my Power BI Services account.
Note: To create an account on Power BI services, you need to have an enterprise email.
To add a Note or Comment to the TreeMap visual, select it and towards the upper right corner click on the ellipsis (3 dots) to open a menu. From the menu click on “Add a Comment”
A pane opens on the right side where you can type any comment such as “Africa has the Second Highest Population of the world”. You can also add a mention (@email) to anyone in your organization who will be notified by email about your comment. When done, click Post.
The wording of the comment appears allowing any one with access to your report to reply to your comment. I will hit Close.
Looking at the TreeMap visual, an extra icon, a comment callout appears at the top. Clicking on the icon reopens the comments pane.
This is how we add notes or comments in Power BI.
Conclusion
I showed you in this article how to add notes to your functions in Excel, your steps in PQ, your code in VBA, or your visualization in a Power BI report.
We recommend you also watch this video if you like to follow along with me
