Category Archives: conditional formatting

#Excel Super Links #154

 

Excel Magic Trick 1465: Conditional Formatting Holiday Dates with Red Fill & Word “Holiday”

https://www.youtube.com/watch?v=DkchPSWIwHE

Text Files in VBA

http://www.excelgaard.dk/Lib/Text%20Files/

Find level of Directories / Find String in Power BI

http://prathy.com/2017/02/1801/

Connecting to APIs with Power BI (Power Query)–Part 1 of 2

https://www.poweredsolutions.co/2017/01/24/connecting-to-apis-with-power-bi-power-query-part-1-of-2/

Highlighting Actual Words in an #Excel List Using the Hyperlink Rollover Method

https://dhexcel1.wordpress.com/2017/09/15/highlighting-words-in-an-excel-list-using-the-hyperlink-rollover-method/

 

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 #143 – shared by David Hager

 

Excel Magic Trick 1459: Adding Food or Accessory Costs For Each Dog with SUMIFS & SUMPRODUCT

https://www.youtube.com/watch?v=ya7thmoKdO4

Convert unevenly spaced list to table [Data from Hell]

https://chandoo.org/wp/2017/08/30/d4h-unevenly-spaced-pq/

DAX Reanimator Series: Moving Averages Controlled by Slicer (Part 1)

https://powerpivotpro.com/2017/08/dax-moving-averages-slicer-part-1/

Excel: Conditional Format Rows in One List that are in Another List

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

Excel Short and Sweet Tip #18 (Toggle Formatting on Worksheet)

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

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.

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/

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:

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/

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.

Num_Array=INDEX(NumRange,N(IF(1,TRANSPOSE(MATCH(SMALL(ABS_Range,ROW(

INDIRECT(“1:”&N_Values))),ABS_Range,0)))))

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.

https://excelxor.com/2014/09/05/index-returning-an-array-of-values/

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.

CF_Closest_Filtered1

The example file can be downloaded here.

CF_Closest_Filtered

Using Conditional Formatting in #Excel to Highlight the N Closest Values to the Mean of a Range by David Hager

 

Conditional Formatting (CF) is one of the most powerful tools in Excel for visualizing data. Because CF can use formulas as input to the CF process, the ability to create formulas based on different data visualization requirements is important. In this demonstration, the goal is to highlight values in a numeric range that are clostest to the average of that range. So, we first make the range dynamic with the following defined name formula.

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

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

ABS_Range =ABS(NumRange-AVERAGE(NumRange))

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.

Num_Array=INDEX(NumRange,N(IF(1,TRANSPOSE(MATCH(SMALL(ABS_Range,ROW(

INDIRECT(“1:”&N_Values))),ABS_Range,0)))))

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. The use of the formula syntax needed to do this with the INDEX function is explained at the following link.

https://excelxor.com/2014/09/05/index-returning-an-array-of-values/

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.

 CF_Closest1

The example file can be downloaded here.

CF_Closest

 

Excel: Conditional Format Rows in List 1 that are Not in List 2 by David Hager

 

I was watching one of Mike Girvin’s excellent Excel videos today. The technique demonstrated in the video was to use Power Query to extract items in list 1 that are NOT In list 2:

https://www.youtube.com/watch?v=JztEKJ-XkCU

I realized that this was the opposite of the conditional formatting technique I had just published.

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

So, for the sake of completeness, I decided to modifyvmy technique to emulate Mike’s technique.

Only a minor modification of the the CF formula was neccesary to produce the opposite condition.

=ISERROR(FIND(CONCAT($A2:$E2),CONCAT($I$2:$M$6)))

You can see the results in the following figure.

 TableAntiCompareCF1

You can download the example file here.

TableAntiCompareCF