Category Archives: conditional formatting

#Excel Super Links #100 (Special Edition) – shared by David Hager

 

My Top 10 Most Underviewed Posts (IMO)

Please check out these posts on my website. There are some powerful techniques to be found here.

#Excel: Getting the Sunrise and Sunrise Times From an Address by David Hager

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

#Excel: Removing Outliers with Excel Formulas to Modify Control Limits by David Hager

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

#Excel: Using Conditional Formatting to Highlight 3D Formulas with Defined Names by David Hager

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

Getting the Latest Earthquake Alert Using the WEBSERVICE and FILTERXML Functions in #Excel 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/

Creating a Unique Delimited String from an Excel Filtered List by Using the TEXTJOIN Function 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/

Excel: Conditional Format Rows in List 1 that are Not in List 2 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/

#Excel Short and Sweet Tip #17 (Returning a Letter Grade Based on a Normal Grading Scale Without Lookup Table) 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/

The DAX ENDSWITHX Function Equivalent

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

Returning the First Temperature in PowerPivot

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

#Excel Short and Sweet Tip #25 (Random Sound – Worksheet UDF) 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/

 

 

#Excel Super Links #99 – shared by David Hager

 

Calculate the Sum of Varying Ranges Based on a Condition and Across Multiple Sheets

http://www.xl-central.com/blog.html#complex-sum

Specify a dis-contiguous range for an Excel function argument

http://www.tushar-mehta.com/excel/tips/multi_area_argument.html

Transparent UserForm Background with VBA

http://wellsr.com/vba/2017/excel/transparent-userform-background-with-vba/

Handling Different Granularities In DAX

http://www.daxpatterns.com/handling-different-granularities/

Excel Short & Sweet Tip #7(Highlighting External Links) by David Hager

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

#Excel Super Links #91 – shared by David Hager

 

Copy a Data Table from PDF into Excel

https://exceloffthegrid.com/copy-a-data-table-from-pdf-into-excel/

Filtering PivotTables with VBA? Deselect Slicers First!

http://dailydoseofexcel.com/archives/2015/11/17/filtering-pivottables-with-vba-deselect-slicers-first/

Use Multiple Connections Between Power BI Tables

https://powerbi.tips/2017/07/use-multiple-connections-between-tables/

Database Functions in Excel

https://roymacleanvba.wordpress.com/2010/09/21/database-functions-in-excel/

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/05/conditional-formatting-gem-in-excel-highlight-the-n-closest-values-to-the-mean-of-a-range-in-a-filtered-list-by-david-hager/

#Excel Super Links #61-90: > 150 Excel and BI Links

 

I had mentioned previously that I used this wonderful Word technique to combine 30 Word files to make these collections.

https://www.dickinson.edu/download/downloads/id/2534/word_combine_files_pdf

One thing I did not mention was that there was a lot of blank space creating during the file consolidation. On the previous 2 ESL collections the space was laboriously removed manually. Not wanting to have to do this again, I searched the net and found this comment in a discussion about removing blank space from Word documents.

 

“Replacing ^p^p with ^p actually is the proper way to remove empty paragraphs.

However, when my documents contains e.g. ^p^p^p^p or similar, it won’t pick them all up and will again leave ^p^p. So I’ve to run this search/replace operation a few times until all cases have been covered.”

 

In order to get rid of all of the empty paragraghs, I needed 8 repeat operations of this type. You can download the resulting Word file containing >250 links to great Excel and Power BI content here.

Excel Super Links 61-90

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