Category Archives: charting

#Excel Super Links #155


Forms for Excel, new experience for Excel survey in Office 365

Highlight a Specific Data Point using Power BI

visualizing change via slopegraph

Reverse Geocoding Using Google API In VB

Generating a “Realtime” Voice Alert for the Latest Magnitude 5 or Greater Earthquake



#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 Super Links #149 – Shared by David Hager


Interactive Formatting of a Chart Series with a Cell’s Color

Flat Rate Loans

Select First Item in a List Box Automatically with VBA

How Power Query can return clickable hyperlinks with friendly names to Excel

Using #Excel to Find How Far the Storm is from Your Location


#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.