Recently, Microsoft Excel Product team announced Checkbox in Excel. The new feature allows you to insert checkboxes in cells, and use them with conditional formatting, formulas, and more and it is currently available to users in the Beta channel as at the time of writing this blog. In this video, I will walk you through how to use the Checkboxes to control columns of advanced dynamic formula.
Video
Scenario
In the screenshot below, we have a sample sales data that is formatted as an Excel table and renamed as data. In row 3, we’ve got checkboxes inserted that we’re going to use to control and select desired columns from our advanced calculation. In cells I1:J2 and K2:L2, we’ve got names of two Account Managers and Customer Type segments. In cell M2, we’ve got 3000. We are going to use amazing LET function, three FILTER functions and the VSTACK to vertically stack our data. The screenshot below shows the sample sales data we’re going to use.
Formula Implementation and Explanation
In cell I5, execute the formula:
=LET( header,FILTER(data[#Headers],A3:G3,""), filteredresult, FILTER(FILTER(data,((data[Account Manager]=I2)+ (data[Account Manager]=J2))*((data[Customer Type]=K2)+ (data[Customer Type]=L2))*(data[Total]>=3000)),A3:G3,""), calc,VSTACK(header,filteredresult),calc)
Click Enter
Let Function Variables Declaration:
header: stores the result of the FILTER function by extracting the headers from the checkboxes in A3:G3.
filteredresult: stores the result of another FILTER function applied to the data by filtering the data based on the following conditions:
(data[Account Manager]=I2)+(data[Account Manager]=J2): filters and return the rows where the Account Manager is either equal to unique Account Manager in cell I2 and J2.
(data[Customer Type]=K2)+(data[Customer Type]=L2): Filters and return the rows where the Customer Type is either equal to cell K2 and L2.
(data[Total]>=3000): Filters and return the rows where the Total is greater than or equal to 3000.
calc: stores the result of vertical stacking of the header and filteredresult arrays using the VSTACK function.
In the screenshot above, noticed that Order Date, Customer Type, Units and Total columns were checked in the checkboxes and we have those columns returned in the calculation. This is handy to control what column to return and not to return. Hope you find this blog post useful. See you in the next blog.