Microsoft is rolling out several new features in Excel 2019, some of which are already available to Office 365 Subscribers. Here we will highlight a few of our favorites: Funnel Charts and the IFS, MAXIFS, MINIFS and SWITCH functions.
Funnel Charts
Funnel charts are commonly used in sales organizations to show how leads are converted to customers, with attrition each step of the way. The following table shows the data layout that is typical for the Sales Pipeline and suitable for funnel charts:
To display this data in a funnel chart first select the entire table, including the header but excluding the title, “Sales Pipeline.”
Next select “Insert” and “Recommended Charts.”
The funnel chart is one of the recommended charts for this data, as shown in the following image. As you can see, it is essentially just a re-arranged and centered bar chart. The funnel makes for a useful visualization of the sales process and can also be used to visualize data related to other processes.
The default font color of black can easily be changed to white by selecting the data labels and changing the font color in the home tab.
Finally, the chart title can be changed by typing directly in the chart title text box.
IFS Function
The IFS function is an upgraded version of the IF function. It accomplishes the same thing but allows for making more logical comparisons and is much easier to read and write. The IFS function allows for testing of up to 127 logical comparisons versus the limit of 64 associated with nested IF statements. However, whether using IF or IFS, it’s always better not to push the limits of these functions.
The value returned by the IFS function is the value associated with the first logical comparison that is TRUE. For example, an IFS function can be used to assign grades to students, as shown below. Here, the IFS statement is entered in cell H2.
In cell H2, note that the value of TRUE is used in place of a final logical comparison. Whenever there is a default value, this is the appropriate technique to use. Since dollar signs were used in cell H2 to anchor the cell references, the formula was entered once and dragged down to the remaining cells in the student grade roster.
MAXIFS, MINIFS
The MAXIFS and MINIFS functions return the max or min numeric values from a specified range of numeric data, where one or more conditions are met. In the following example, we find the highest level of Math that the student took and achieved a grade of A.
With these functions, it is important that the range for each criterion has the same dimensions as the min/max range. In the above example, rows 4 through 20 are used for each range.
Switch Function
The Switch function is useful when you want to convert an expression to something else, where the “something else” depends on the value of the expression. For instance, say you want to convert the name of the month to the number of the month. The Switch function can handle this conversion, as shown below. The function was entered once in cell C3 and then copied down.
Entering a default value is optional for the Switch function. If a default value were used in the above example, it would have been entered as a final argument, after the 12.
Contact ExcelHelp.com for A Free Consultation
Let us help you design and develop a rock-solid solution for your firm. Contact our team to schedule a free consultation by calling 1-800-682-0882 or visit our website at ExcelHelp.com to submit an inquiry online.