Category Archives: filtering

Excel Super Links 1-150

This time you really hit the jackpot! >850 Excel and Power BI Links, all of the best articles. Make this the cornerstone of your Excel resource library. Download the Word document here.

Excel Super Links 1-150




#Excel Super Links #150 – Special Edition


To celebrate the 150th issue of Excel Super Links, here are the links to all of my content at Although there are no descriptions, they are fairly self-documenting.




#Excel Advanced Filter: Selectively Show Control Chart Data by Standard Deviation


For this technique, I am building it on the workbook made for the following article. Please download the example file and read the article, since the functionality is synergistic with it.

When viewing a control chart, it is useful to be able to view only data within set control limits. For example, you would like to view all data within one sigma of the mean. The technique described here allows you to do that for 1, 2, or 3 sigma. The key to accomplish this is Excel’s advanced data filter. The advanced filter uses a boolean formula to filter a table of data, starting at the first row of the table. The formula in A2 gives the desired result.


where SigmaKeep is a worksheet cell (G2) with a data validation list of 1,2,3. Cells D6 and G6 (and the corresponding columns) contain formulas that calculate the +1 and -1 sigma from the mean for the data in column B. The 2nd and 3rd sigma are for E6,H6 and F6,I6 respectively. The following figure shows the advanced filter dialog box and the input ranges required, where SigmaKeep is set at a value of 2.


After the filter is applied, note the difference in the data in the control chart versus the original in the first figure.


The only caveat to this technique is that the advanced filter has to be cleared (Data, Sort & Filter, Clear) before a different KeepSigma value can be applied. This can be used in conjunction with the removal of outliers as discussed in the original article.

The example file can be downloaded here.



#Excel Super Links #148 – Shared by David Hager


Using Custom Lists in Excel

Power Query: a Function Function

Excel Pivot Chart Drill Down Buttons

Adding Multiple Measures to Non-PowerPivot Versions of #Excel using an User-Defined VBA Function

Advanced Filter Magic


Conditional Formatting Gem in #Excel: Highlight the N Closest Values to the Mean of a Range in a Filtered List by David Hager


I am extending the closest value technique I published recently to calculate the same based on a filtered list.

In this demonstration, the goal is to highlight values in a numeric range that are clostest to the average of that range in a filtered list. So, we first make the range dynamic with the following defined name formula.

NumRange =OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1)

Then, we modify that range to include only filtered values.

fNumRange =IFERROR(IF(SUBTOTAL(3,OFFSET(NumRange,ROW(NumRange)-MIN(ROW(NumRange)),,1)),NumRange,””),””)

For more information on the SUBTOATL function as used here, see:

Next, we use that range to make an array of the absolute differences of each value of the range from the average.

ABS_Range =IFERROR(ABS(fNumRange-AVERAGE(fNumRange)),””)

We can then define a cell for the number of values to highlight.

N_Values =$B$2

The heavy work is done by the next formula, which creates an array of the N values to be higlighted.



This formula returns the position of each smallest deviation in the 2nd argument of the INDEX function, which then returns the values corresponding to those deviations, based on a filtered list. The use of the formula syntax needed to do this with the INDEX function is explained at the following link.

This formula can now be used in the creation of the CF, where CF Formula is =SUM(N(A1=Num_Array)), starting at A1 and applied to all of Column A.

The result of this CF is shown below.


The example file can be downloaded here.


Advanced Filter Magic by David Hager


Previously, I discussed the use of a custom list with Data Advanced Filter.

In the article, I only showed one custom list being used in the advanced filter. However, in most situations, a number of specific searches are required. To illustrate this, I combined all of the links from ESL #1-60 in an Excel file. The description of the links are in column A and the links are in column B. The goal is to perform multiple searches with lists while using only a single formula in the advanced filter. A picture of this can be seen in the following figure.


The formula in the advanced filter (B1:B2) on the AdvFilterTable tab is


where Choice=CHOOSE(MATCH(AdvFilterTable!$A$2,CritLists,0),PowerBIList,VBAList,ExcelList)

This is the key formula for returning the desired list into the advanced filter formula.It is controlled by cell A2, which uses Data Validation with the following list.


The criteria lists tied to the CHOOSE function are are shown in the following figure.


These are expanding dynamic lists to add additional criteria if necessary.


VBAList =OFFSET(LList!$B$1,1,,COUNTA(LList!$B:$B)-1)

ExcelList =OFFSET(LList!$C$1,1,,COUNTA(LList!$C:$C)-1)

When a list is selected in cell A2 and the advanced filter is applied to the links table, the result is as shown below.


Functionally, you can search all of my links for the content you desire. Also, you can use this filter model with your own data and criteria. I hope that this is useful.

You can download the file here.



Using the CHOOSE and AGGREGATE Functions To Apply Conditional Formatting to a Filtered List in Excel By David Hager

The very 1st article I wrote for my blog was about highlighting values in a filtered list. I decided that it was time to revisit that subject. Specifically, I had written an article for Rob Collie about using the CHOOSE function with conditional formatting (CF) in Excel. It turned out that the article was too complex to be useful.

So, I decided to incorporate the use of the CHOOSE function into my original article and give it a friendly user interface. The two user inputs were both created using Data Validation with the List option. They are defined as shown below and pictured in the following figure (showing unfiltered list).




These inputs are used to return values needed by the CF conditions. TopN is selected by the user in cell B1. Cell B2 gives a choice of Large or Small. The following formula converts this information into a number to be used by the CHOOSE function.


The CF conditions are made to be applied to column B. When each formula is defined, the active cell needs to be B6.





When combined with the CHOOSE function (the formula used for the CF), they provide a methodology to highlight filtered rows, as shown below (filter set to display values of 50 to 175 in column B).


If a way to visualize TopN and BottomN simultaneously was required, a second CF condition can be appled to column B.

=CHOOSE(CritNumber, SmallCondition, LargeCondition)

The following figure shows the result.


This is just a demonstration of the versatility of using CF condtions in filtered lists. The application of this method to many columns can be done, as well as the use of different CF conditions. I might write another article about this subject later, but for now, enjoy! Click the link below to download the working file.