In this article on Excel Functions and Formulas :
1.
Excel Intellisense: Formula AutoComplete
2.
Working with Insert Excel Functions Wizard
3.
Debug Complex Formulas: The Magic F9 and Ctrl+Z
4.
Display items using display triggers
This is intended to be a short guide to help you build and debug your own formulas.
First thing to know is that there is a major difference between excel functions and excel formulas:
Excel Functions are built into Excel and their functionality and arguments cannot be modified by users. An example of excel functions: SUM, COUNTIF, ROW, COLUMN, INDEX, MATCH, VLOOKUP, and so on.An Excel Formula is designed by the user and it can contain multiple Excel Functions, Defined Names, Constants, Range references.
Excel Formulas can be very complex, but there are some limits:
Length of formula contents: 8,192 characters
Internal length of formula: 16,384 bytes
Iterations: 32767
Worksheet arrays: Limited by available memory
Selected ranges: 2048
Arguments in a function: 255
Nested levels of functions: 64
Note: For more about Excel specifications and limits see:
https://support.office.com/ 1. Excel Intellisense and Formula AutoComplete
When you start typing in a cell one of these 3 signs: the equal sign: “=”, the plus sign: “+”, or the minus sign: “-“, excel Function AutoComplete Intellisense is activated, any letters you type after will bring you the list of Excel Function that starts with that letter. If you continue to type more letters, the list will shrink to display only the ones that match to what you typed. (if you don’t want to see the list, you can always click the Escape key and continue, or just ignore it).
You can click once on any Function from the short list, Excel Intellisense will display a short description for that function (Tool Tips):
%22%20transform%3D%22translate(1.3%201.3)%20scale(2.65234)%22%20fill-opacity%3D%22.5%22%3E%3Cellipse%20fill%3D%22%23bbb%22%20cx%3D%2217%22%20cy%3D%2216%22%20rx%3D%2255%22%20ry%3D%22107%22%2F%3E%3Cpath%20fill%3D%22%23fff%22%20d%3D%22M271%2041l-48-57L26%2016z%22%2F%3E%3Cellipse%20fill%3D%22%23aaa%22%20cx%3D%226%22%20cy%3D%2217%22%20rx%3D%2215%22%20ry%3D%2252%22%2F%3E%3Cellipse%20fill%3D%22%23e4e4e4%22%20cx%3D%2246%22%20cy%3D%2217%22%20rx%3D%2226%22%20ry%3D%2290%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
Excel Intellisense
If you double click any function from list, Excel will insert the function in your formula, and automatically display the list of arguments for that function:
%22%20transform%3D%22translate(.8%20.8)%20scale(1.66406)%22%20fill-opacity%3D%22.5%22%3E%3Cellipse%20fill%3D%22%23cfcfcf%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22matrix(19.28722%20-38.78963%20165.55917%2082.32037%2033.7%2037)%22%2F%3E%3Cellipse%20fill%3D%22%23fff%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22matrix(19.56575%20-47.47006%20169.4644%2069.84819%20177%200)%22%2F%3E%3Cellipse%20fill%3D%22%23fff%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22rotate(3.7%20-81.3%202869.6)%20scale(117.62846%2025.92587)%22%2F%3E%3Cellipse%20fill%3D%22%23fff%22%20cx%3D%22171%22%20cy%3D%2226%22%20rx%3D%2252%22%20ry%3D%2252%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
Function Arguments list
More on Formula AutoComplete on section 4:
Display items using display triggers
Another good thing that might be useful to know is that both the function description and the list of function arguments rectangles are movable, you can drag them anywhere you want, just hover the mouse over the rectangle sides and the pointer will change to a quad arrow symbol; when you see the crossed arrows, just click and drag.
2. Working with the Insert Function Wizard:
You can start writing a new formula by pressing the Insert Function button, from the Formulas Tab in Ribbon, or from the shortcut next to the Formula bar, as in the image below:
%22%20transform%3D%22matrix(2.75%200%200%202.75%201.4%201.4)%22%20fill-opacity%3D%22.5%22%3E%3Cellipse%20fill%3D%22%23fff%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22matrix(-44.72067%20132.98626%20-40.54978%20-13.6361%20234.2%2061.6)%22%2F%3E%3Cellipse%20fill%3D%22%23a9a9a9%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22rotate(1.5%20-118%201658.5)%20scale(141.23844%2024.20071)%22%2F%3E%3Cellipse%20fill%3D%22%23fff%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22matrix(42.86537%20-101.9727%2033.50889%2014.08584%20239.3%2038.2)%22%2F%3E%3Cellipse%20fill%3D%22%23e8e8e8%22%20cx%3D%22104%22%20cy%3D%2228%22%20rx%3D%2231%22%20ry%3D%2211%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
Functions Menu
This action will open the Insert Function Wizard:
%27%20fill-opacity%3D%27.5%27%3E%3Cellipse%20fill%3D%22%23fff%22%20fill-opacity%3D%22.5%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22matrix(126.26093%209.75405%20-4.09986%2053.07052%20253.5%20220.7)%22%2F%3E%3Cellipse%20fill%3D%22%23a6a6a6%22%20fill-opacity%3D%22.5%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22matrix(-3.15393%20-39.53936%20353.9426%20-28.23285%20250.9%20.8)%22%2F%3E%3Cellipse%20fill%3D%22%23b2b2b2%22%20fill-opacity%3D%22.5%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22rotate(95.2%20-38.7%2058)%20scale(119.81898%2025.25346)%22%2F%3E%3Cellipse%20fill%3D%22%23fff%22%20fill-opacity%3D%22.5%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22rotate(5.7%20-2112.4%202730.5)%20scale(118.71233%2047.87462)%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
Insert Function Wizard
After you select a function and click ok, the Function Arguments window (see below image) will show up, with more useful information, like: argument data type, argument short description, formula result and a link to the Help section, with more details and examples for that function.
%22%20transform%3D%22translate(1.1%201.1)%20scale(2.26563)%22%20fill-opacity%3D%22.5%22%3E%3Cellipse%20fill%3D%22%23a5a5a5%22%20cx%3D%22133%22%20cy%3D%224%22%20rx%3D%2218%22%20ry%3D%227%22%2F%3E%3Cellipse%20fill%3D%22%23e1e1e1%22%20cx%3D%2257%22%20cy%3D%2295%22%20rx%3D%22108%22%20ry%3D%22108%22%2F%3E%3Cellipse%20fill%3D%22%23e1e1e1%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22matrix(65.15376%20-5.37259%2019.35016%20234.66075%20221.6%2055.3)%22%2F%3E%3Cpath%20fill%3D%22%23bebebe%22%20d%3D%22M111-3l36%2013%2014-26z%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
Function Arguments window
3. Debug Complex Formulas: The Magic F9 and Ctrl+Z
Again, Excel Intellisense will be very helpful, especially when you are dealing with a long and complex formula.Here is a video example:
To quickly identify which part of the formula failed, click after the first open paranthesis of the function to view the function arguments in tool tips, then click the logical argument link to select the entire argument, and click the F9 key to calculate only that argument. This action will replace the argument with its calculated value, you have to press Ctrl+Z to return to your formula or reference. For the example function, the steps to debug are :
1. Click in formula bar click after the first open paranthesis of the function;
2. Click the tooltips link to select the first argument (logical_test)
3. Press F9, the result should be TRUE or FALSE, Press Ctrl+Z to restore the argument. In this case, the result for this argument is TRUE.
4. We know from the previous step that the logical test returns TRUE, click now on the tooltip link to select the value_if_true argument. Pressing the F9 key will return an error for this argument, we have to evaluate all the functions used in this argument, to see which one returns an error. Remember to press CTRL+Z after each use of F9 key!
5. inside the value_if_true argument, we have another formula: IF(AND(B82>=12001,C82>2250),””Yes””,””No””)”
Repeat the above steps for each function used in this argument formula, select each argument one by one, press F9 to see its calculated value, then return with Ctrl+Z after each F9 key pressed.
For this example, the error is obvious, no debug is really needed, but with large data sets and multiple nested functions, it’s an easy way to understand what’s wrong there…
4. Formula AutoComplete: Display items using display triggers.
Remember that the AutoComplete feature will not be active if the first character in the cell is not one of the following:
– the equal sign: “=”
– the plus sign: “+”,
– or the minus sign: “-”
Display triggers for Excel Functions, Defined Names and Defined Table names:
– type one or more beginning letters, excel will display the items that starts with those letters, in alphabetical order.
For Function arguments, there are no display triggers!
Only a few Excel functions have arguments with enumerated constants that will be displayed automatically in list: CELL, FV, HLOOKUP, MATCH, PMT, PV, RANK, SUBTOTAL, VLOOKUP and AGGREGATE in newer versions of Excel.
Display triggers for Defined Tables special items: Headers, Totals, ThisRow, All, Data.
There are 3 display triggers for table components:
– the opening bracket: “[” , used after the table name: =Table1[
%27%20fill-opacity%3D%27.5%27%3E%3Cellipse%20fill%3D%22%23cfcfcf%22%20fill-opacity%3D%22.5%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22matrix(-171.46886%206.32433%20-3.87646%20-105.10094%20174.5%2045.3)%22%2F%3E%3Cellipse%20fill%3D%22%23fff%22%20fill-opacity%3D%22.5%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22matrix(148.6395%20-17.98735%2014.35872%20118.6541%20330%20241.8)%22%2F%3E%3Cellipse%20fill%3D%22%23d1d1d1%22%20fill-opacity%3D%22.5%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22matrix(19.58167%20-54.91397%2070.58265%2025.16893%20506.5%20146.6)%22%2F%3E%3Cellipse%20fill%3D%22%23fff%22%20fill-opacity%3D%22.5%22%20rx%3D%221%22%20ry%3D%221%22%20transform%3D%22matrix(485.65152%20122.88873%20-20.3423%2080.39198%20138.9%20238.6)%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
Table display triggers – bracket
– the comma: “,” used after a table component: =Table1[[#All],
– or the colon sign: “:” (also known as the Range Operator), used after a column name: =Table1[[Column1]:
Another excellent article from THE excel guru.
Very helpful, especially debugging complex formulas.
Thanks for the tips!
You’re welcome John 🙂