Category Archives: charting

#Excel Super Links #155

 

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

https://techcommunity.microsoft.com/t5/Microsoft-Forms-Blog/Forms-for-Excel-new-experience-for-Excel-survey-in-Office-365/ba-p/109195

Highlight a Specific Data Point using Power BI

http://prathy.com/2017/03/27/

visualizing change via slopegraph

http://www.storytellingwithdata.com/blog/2017/9/19/visualizing-change

Reverse Geocoding Using Google API In VB

https://chandoo.org/forum/threads/reverse-geocoding-using-google-api-in-vb.18005/

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

https://dhexcel1.wordpress.com/2017/09/22/generating-a-realtime-voice-alert-for-the-latest-magnitude-5-or-greater-earthquake/

 

Advertisements

#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 dhExcel1.wordpress.com. Although there are no descriptions, they are fairly self-documenting.

Enjoy!

https://dhexcel1.wordpress.com/2017/09/12/using-excel-to-find-how-far-the-storm-is-from-your-location/

https://dhexcel1.wordpress.com/2017/09/07/adding-multiple-measures-to-non-powerpivot-versions-of-excel-using-an-user-defined-vba-function/

https://dhexcel1.wordpress.com/2017/09/01/excel-impossibly-easy-2-change-sheet-tab-color-with-a-user-defined-worksheet-formula/

https://dhexcel1.wordpress.com/2017/08/26/returning-a-list-of-formulas-based-on-a-specific-excel-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/08/20/excel-impossibly-easy-1-return-a-1d-array-from-non-contiguous-native-3d-ranges/

https://dhexcel1.wordpress.com/2017/08/17/documenting-formulas-in-an-excel-range-using-the-formulatext-isformula-and-textjoin-functions-by-david-hager/

https://dhexcel1.wordpress.com/2017/08/15/extracting-ingredients-from-an-excel-list-by-cost-category-by-david-hager/

https://dhexcel1.wordpress.com/2017/08/10/excel-short-and-sweet-tip-28-jazzing-up-a-star-rating-by-using-the-textjoin-by-david-hager/

https://dhexcel1.wordpress.com/2017/08/08/excel-super-links-1-120-600-excel-and-power-bi-links/

https://dhexcel1.wordpress.com/2017/08/07/excel-super-links-91-120-great-collection-of-excel-and-power-bi-links/

https://dhexcel1.wordpress.com/2017/08/05/creating-a-priority-list-with-excel-by-david-hager/

https://dhexcel1.wordpress.com/2017/08/04/list-of-priority-items-from-another-list-using-excel-textjoin-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/08/03/creating-a-set-of-custom-instructions-using-the-excel-textjoin-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/25/run-your-power-query-m-code-procedures-in-excel-worksheet-cells-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/20/storing-and-running-m-code-from-excel-worksheet-cells-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/13/lookup-a-bible-verse-in-any-language-using-excel-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/12/excel-short-and-sweet-tip-26-showing-an-userform-with-a-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/11/excel-getting-the-sunrise-and-sunrise-times-from-an-address-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/10/getting-the-latest-earthquake-alert-using-the-webservice-and-filterxml-functions-in-excel-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/07/excel-short-and-sweet-tip-25-playing-a-random-sound-with-a-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/07/excel-super-links-61-90-250-excel-and-bi-links/

https://dhexcel1.wordpress.com/2017/07/05/conditional-formatting-gem-in-excel-highlight-the-n-closest-values-to-the-mean-of-a-range-in-a-filtered-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/04/using-conditional-formatting-in-excel-to-highlight-the-n-closest-values-to-the-mean-of-a-range-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/03/lookup-a-bible-verse-using-excel-wo-vba-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/01/automating-word-and-powerpoint-from-excel-with-a-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/26/excel-get-audio-result-for-any-excel-formula-or-cell-with-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/25/excel-conditional-format-rows-in-list-1-that-are-not-in-list-2-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/24/excel-conditional-format-rows-in-one-list-that-are-in-another-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/23/get-information-from-excel-about-the-august-2017-total-eclipse-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/22/creating-dependent-lists-from-a-column-lookup-in-an-excel-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/20/excel-sparklines-display-last-n-values-by-david-hager/

https://dhexcel1.wordpress.com/2014/05/04/excel-did-you-know-tip5/

https://dhexcel1.wordpress.com/2017/06/19/excel-short-and-sweet-tip-23-open-windows-file-explorer-with-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/17/excel-using-multiple-lists-with-data-advanced-filter-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/13/excel-exciting-new-features-using-a-worksheet-udf-to-modify-shapes-on-a-worksheet-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/09/esl31-60-a-valuable-collection-of-excel-and-power-bi-links-to-great-excel-and-modern-excel-techniques-vol-2-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/08/excel-short-and-sweet-tip-22-move-vba-module-copy-to-another-workbook-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/07/excel-exchange-rate-udf-with-symbol-lookup-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/06/excel-short-and-sweet-tip-21-remove-unused-custom-formats-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/05/excel-short-and-sweet-tip-20-create-vba-procedure-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/03/creating-an-excel-translator-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/02/using-excel-to-make-a-round-robin-tournament-schedule-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/01/revisiting-using-the-excel-textjoin-function-to-return-unique-items-from-a-3d-range-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/01/excel-using-power-query-to-return-all-words-from-a-list-of-letters-including-wildcards-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/28/excel-short-and-sweet-tip-19-excel-can-spell-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/27/excel-short-and-sweet-tip-18-toggle-formatting-on-worksheet-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/26/excel-short-and-sweet-tip-16-returning-a-letter-grade-based-on-a-normal-grading-scale-without-lookup-table-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/25/excel-project-tracking-workbook-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/24/excel-short-sweet-tip-16-multiple-delimiters-with-textjoin-for-custom-formatting-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/23/excel-native-3d-ranges-with-the-textjoin-function-plus-bonus-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/23/excel-udf-using-google-api-to-return-the-elevation-of-an-address-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/22/excel-creating-a-list-of-option-expiration-dates-and-triple-witching-dates-with-excel-formulas-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/20/copy-excel-chart-as-a-enhanced-metafile-picture-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/19/generating-random-initials-in-power-bi-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/18/excel-short-and-sweet-tip-14-generating-random-initials-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/17/excel-modifying-control-chart-data-to-remove-outliers-with-excel-formulas-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/16/excel-short-and-sweep-tip-13-keyboard-dfarot-unique-technique-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/15/excel-origin-of-sparklines-linechart-vba-user-defined-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/14/excel-finding-and-visualizing-the-last-record-in-a-table-based-on-criteria-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/13/excel-short-and-sweet-tip-12-documenting-formulas-and-highlighting-those-formulas-with-conditional-formatting-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/12/excel-creating-a-udf-with-the-vba-environ-function-and-using-it-to-make-a-table-of-environmental-variables-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/11/excel-a-valuable-collection-of-excel-links-to-great-excel-and-modern-excel-techniques-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/10/excel-building-a-frequency-summary-table-based-on-an-excel-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/09/excel-short-and-sweep-tip-11-most-frequent-item-in-column-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/08/excel-a-model-using-the-convert-function-containing-categories-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/07/excel-magic-consolidator-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/06/excel-short-and-sweep-tip-10-add-custom-autocorrect-with-vba-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/05/excel-using-conditional-formatting-to-highlight-cells-that-contain-array-formulas-using-the-formulatext-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/04/excel-short-sweet-tip-9-get-ip-address-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/03/excel-generating-a-random-sampling-from-a-list-using-vba-and-the-textjoin-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/01/excel-short-sweet-tip-8-vba-function-to-return-zodiac-sign-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/29/excel-worksheet-udf-that-adds-a-comment-to-any-cell-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/28/excel-short-sweet-tip-7highlighting-external-links-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/25/excel-using-conditional-formatting-to-highlight-3d-formulas-with-defined-names-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/25/excel-short-sweet-tip-6-shuffling-a-string-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/24/excel-using-conditional-formatting-to-highlight-cells-containing-native-3d-formulas-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/23/excel-short-sweet-tip-5-hiding-error-triangles-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/21/excel-short-sweet-tip-4/

https://dhexcel1.wordpress.com/2017/04/20/excel-short-and-sweep-tip-3-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/19/excel-modifying-shapes-from-an-udf-in-a-worksheet-cell-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/18/excel-short-sweet-tip-2/

https://dhexcel1.wordpress.com/2017/04/17/excel-short-and-sweet-tip-1-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/15/excel-combining-and-refining-a-static-filter-list-filter-criteria-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/14/excel-using-advanced-filter-with-a-custom-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/11/excel-vba-create-a-table-of-file-locations-and-urls-for-your-favorites-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/10/excel-identifying-if-a-string-is-the-anagram-of-another-string-using-the-textjoin-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/08/mine3d-for-excel-an-excel-based-game-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/07/using-the-subtotal-function-in-excel-to-aggregate-values-from-multiple-filtered-lists-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/06/using-excel-vba-to-create-a-filter-criteria-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/04/multiple-lookup-values-in-a-single-cell-withwithout-duplicates-using-only-excel-formulas-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/03/excel-shift-calendar-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/01/generating-a-sorted-unique-array-in-excel-using-only-formulas-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/30/excel-creating-a-list-made-up-of-formulas-from-a-filtered-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/30/excel-filtered-list-using-a-calculated-column-to-count-occurrence-number-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/25/using-the-excel-advanced-filter-to-a-list-having-internal-numbers-in-a-string-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/23/using-conditional-formatting-to-highlight-unique-items-in-an-excel-filtered-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/13/chapter-on-conditional-formatting-in-excel-by-david-hager-a-blast-from-the-past/

https://dhexcel1.wordpress.com/2017/03/11/my-collection-of-excel-tips-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/02/using-the-choose-and-aggregate-functions-to-apply-conditional-formatting-to-a-filtered-list-in-excel-by-david-hager/

https://dhexcel1.wordpress.com/2013/12/19/highlighting-top-n-values-in-a-filtered-table-column-using-conditional-formatting/

https://dhexcel1.wordpress.com/2017/02/07/calculating-aggregation-for-internal-numbers-from-strings-in-a-range-by-david-hager/

https://dhexcel1.wordpress.com/2017/02/05/using-the-excel-textjoin-function-to-extract-an-internal-numeric-string-from-a-string-by-david-hager/

https://dhexcel1.wordpress.com/2017/02/03/averaging-of-scientific-results-in-excel/

https://dhexcel1.wordpress.com/2017/01/25/charting-data-with-formula-links-in-excel-by-david-hager/

https://dhexcel1.wordpress.com/2017/01/08/creating-a-unique-delimited-string-from-an-excel-filtered-list-by-using-the-textjoin-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/01/07/creating-an-excel-table-of-components-by-product-from-a-list-using-the-textjoin-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/01/03/creating-a-unique-delimited-string-from-a-delimited-string-excel-formula-method-by-david-hager/

https://dhexcel1.wordpress.com/2017/01/02/using-the-excel-textjoin-function-to-return-unique-items-in-a-one-cell-delimited-string-from-a-2d-and-3d-range-by-david-hager/

https://dhexcel1.wordpress.com/2016/11/29/xlcube-an-excel-game/

https://dhexcel1.wordpress.com/2015/08/31/power-bi-help-for-excel/

https://dhexcel1.wordpress.com/2015/08/16/measuring-distance-in-power-bi-desktop/

https://dhexcel1.wordpress.com/2015/06/21/the-dax-endswithx-function-equivalent/

https://dhexcel1.wordpress.com/2015/03/31/bingo/

https://dhexcel1.wordpress.com/2014/09/13/go-to-special-for-browser-copypaste-to-excel/

https://dhexcel1.wordpress.com/2014/07/07/calculating-the-nth-best-performance-unique-individuals-with-excel-and-powerpivot-2/

https://dhexcel1.wordpress.com/2014/06/13/using-excel-3d-formulas-on-filtered-lists/

https://dhexcel1.wordpress.com/2014/06/09/excel-3d-easy-as-1-2-3-and-a-b-c-and-others/

https://dhexcel1.wordpress.com/2017/01/07/archive-of-excel-experts-e-letter-by-david-hager/

https://dhexcel1.wordpress.com/2014/05/25/returning-the-first-temperature-in-powerpivot/

 

 

#Excel Super Links #149 – Shared by David Hager

 

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

https://peltiertech.com/interactive-formatting-chart-series-with-cells-color/

Flat Rate Loans

http://pistulka.com/Other/?p=2780

Select First Item in a List Box Automatically with VBA

http://www.thesmallman.com/blog/2017/9/3/select-first-item-in-a-list-box-automatically-with-vba

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

http://www.thebiccountant.com/2017/09/12/powerquery-return-clickable-hyperlinks-to-excel/

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

https://dhexcel1.wordpress.com/2017/09/12/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.

https://dhexcel1.wordpress.com/2017/05/17/excel-modifying-control-chart-data-to-remove-outliers-with-excel-formulas-by-david-hager/

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.

=AND(B6<CHOOSE(SigmaKeep,D6,E6,F6),B6>CHOOSE(SigmaKeep,G6,H6,I6))

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.

 ModifyStdData_AdvFilter1

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

 ModifyStdData_AdvFilter2

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.

ModifyStdData_AdvFilter