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.

Leave a reply

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.

Advertisements

The Hyperlink Rollover technique was discovered by Jordan Goldmeier. It uses the HYPERLINK function with a VBA function procedure as the 1^{st} argument. By passing the cursor over the cell containing this formula, the function procedure is run. See:

http://optionexplicitvba.blogspot.com/2011/04/rollover-b8-ov1.html

And, this function, unlike normal UDFs, can modify the Excel worksheet.

I have been trying for quite some time to develop a way to highlight (format) cells in a list that contain words. It turns out that there is a bug (feature?) in the VBA expression Application.CheckSpelling that prevents its use in an UDF. For example, if I wanted to conditionally format cell A1 to highlight a string that is a word, you might expect that the following UDF could be used as a CF formula.

Function IsWord(WordRange As Range)

IsWord = Application.CheckSpelling(WordRange)

End Function

Well, it does not work. The problem is documented at the following link.

https://stackoverflow.com/questions/10776191/spellcheck-a-single-word-in-excel-function

I tried numerous methods to find something that would work. I won’t bore you with the details, but I used a lot of time on this without success. I even used the Hyperlink Rollover method, and it still did not work. Finally, in the last comment in the link shown above, I found that an early binding process was needed. When added to the regular UDF for use in conditional formatting, it still did not work, but it did work with Hyperlink Rollover. Here is the UDF developed to highlight words:

Function IsWord(WordRange As Range)

Static xlApp As New Excel.Application

For Each cRange In WordRange

If xlApp.CheckSpelling(cRange) Then

With cRange.Font

.Color = -16776961

.Bold = True

End With

End If

Next

IsWord = 0

Set xlApp = Nothing

End Function

By using this UDF with this technique, the following formula entered In cell D1 creates the Hyperlink Rollover location.

=IFERROR(HYPERLINK(IsWord(A1:A20),”Format Words”),”Format Words”)

After passing the cursor over this cell, the result can be viewed in the following figure.

I decided to add another word to that range in cell A9. Without “rolling over”, this cell now showed that A9 contained a word.

I was rather amazed by this. I could not find an example of this in any previous Hyperlink Rollover, but I might have missed seeing it. What I believe is occurring is that any change in the range used in the IsWord function serves as the same action as a rollover. I did verify that this condition persists even after the workbook is closed and reopened.

So…

I decided to use a dynamic range as the argument in the IsWord function, as shown below.

=IFERROR(HYPERLINK(IsWord(OFFSET(A1,,,COUNTA(A:A),)),”Format Words”),”Format Words”)

By adding several more cells with strings, the figure below shows the results.

I hope that you find this technique useful.

You can download the example file here.

Note: It is important to note that this Excel model can be used any place in the world to look at the direction from storms or any other event.

The destructive nature of hurricanes has been dominating the news recently in the Atlantic basin. But, many times it is difficult to get information about how far a storm is from your specific location. This excellent Excel technique will allow you to answer that question. All that you will need is your address (or an address of interest) and the current storm coordinates. Key parts of this technique were found at the following links.

https://www.mrexcel.com/forum/excel-questions/541185-convert-number-deg-direction-text-n-nne.html

In the worbook provided, enter your address information in cell B1and the storm coordinates in C4 and D4. The formulas used to calculate your result are:

LocationXML =WEBSERVICE(“http://maps.googleapis.com/maps/api/geocode/xml?address=”&Address&”+,+&sensor=false”)

Lat_1 =FILTERXML(LocationXML,”//result/geometry/location/lat”)

Lng_1=FILTERXML(LocationXML,”//result/geometry/location/lng”)

Distance=ACOS(SIN(Latitude_1*PI_DIV180)*SIN(Latitude_2*PI_DIV180)+COS(Latitude_1*PI_DIV180)*COS(Latitude_2*PI_DIV180)*COS((Longitude_2*PI_DIV180)-(Longitude_1*PI_DIV180)))*3959

PI_DIV180=PI()/180

Direction =DEGREES(ATAN2(COS(RADIANS(Latitude_1))*SIN(RADIANS(Latitude_2))-SIN(RADIANS(Latitude_1))*COS(RADIANS(Latitude_2))*COS(RADIANS(Longitude_2-Longitude_1)),SIN(RADIANS(Longitude_2-Longitude_1))*COS(RADIANS(Latitude_2))))

TextDirection=CHOOSE(1+ROUND(IF(Direction<0,360+Direction,Direction)/22.5,0),”N”,”NNE”,”NE”,”ENE”,”E”,”ESE”,”SE”,”SSE”,”S”,”SSW”,”SW”,”WSW”,”W”,”WNW”,”NW”,”NNW”,”N”)

The final formula for displaying the desired information (in cell A7) is:

=ROUND(Distance,0)&” miles “&TextDirection&” of “&Address

The result can be viewed here.

I hope that you find this useful. Download the example file:

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.

=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 2^{nd} and 3^{rd} 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.

In this article,

http://dailydoseofexcel.com/archives/2017/07/10/look-ma-no-powerpivot/

Jeff Weir pointed to a video made by Mike Girvin about adding measures to non-PowerPivot versions of Excel (link below)

https://www.youtube.com/watch?v=FVVK-8QZC1M&t=422s

Mike demonstrated how measures can be added to a data model in these “disabled” version through pivot table options. Please view this video to see how Mike did it.

The link to the working file for this video will be referred to in this article (Thanks, Mike!).

https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1269Finished.xlsx

You can download this file and reproduce the technique presented here.

Although it is not well-known, Microsoft started at Excel version 2016 (Office 365) marketing versions that do not have PowerPivot capability. For details on this, see:

So, this article is dedicated to those who purchased non-PowerPivot versions of Excel 2016, although the technique presented here will work on any version of Excel 2013 or greater.

Jeff Weir mentioned in his article that since some Excel 2016 versions did not have the full-blown PowerPivot capability, and that VBA could be used to build a user interface to the data model. Well, I have not created a UI, but I have made a way to add multiple measures to the data model using an user-defined function. The code for the VBA function is shown below. To use this, add astandard module in the VBE and then save the workbook as .xlsm. Then, copy/paste the code into the module.

Function AddMeasure(TableName As String, MeasureName As Range)

Application.Volatile False

With ActiveWorkbook.Model

For Each mCell In MeasureName

mFormat = mCell.Offset(0, 2).Value

.ModelMeasures.Add mCell.Value, .ModelTables(TableName), mCell.Offset(0, 1).Value, _

Switch(mFormat = “Boolean”, .ModelFormatBoolean, mFormat = “Currency”, .ModelFormatCurrency, _

mFormat = “Date”, .ModelFormatDate, mFormat = “DecimalNumber”, .ModelFormatDecimalNumber, _

mFormat = “General”, .ModelFormatGeneral, mFormat = “PercentageNumber”, .ModelFormatPercentageNumber, _

mFormat = “ScientificNumber”, .ModelFormatScientificNumber, mFormat = “WholeNumber”, .ModelFormatWholeNumber), _

mCell.Value

Next

End With

AddMeasure = “DONE”

End Function

Then, place the following information in the range D10:F14.

NetRevenue | SUMX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2)) | DecimalNumber |

MaxRevenue | MAXX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2)) | PercentageNumber |

MinRevenue | MINX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2)) | Currency |

AverageRevenue | AVERAGEX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2)) | General |

CountOfRevenue | COUNTAX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2)) | General |

To run this as a worksheet formula, type this formula in any cell.

=AddMeasure(“fTransactions”,D10:D14)

This will add the 5 measures to the data model, as shown in the Pivot Table Fields list.

After the 5 measures are added to the pivot table, the resulting pivot table will look like this.

Of course, the DAX formulas to be added have to return valid results, or the procedure will fail.

This powerful technique is yet another reason why users should not completely abandon Excel for Power BI desktop, as discussed in this article at powerpivotpro.com

https://powerpivotpro.com/2017/09/excel-is-still-the-best-tool-for-teaching-dax/

And, this technique does not HAVE to be run from a UDF, but I am still amazed that it can. I am sure that you will find this very useful.

What if I told you that I wanted to change tab colors on sheets in a workbook by entering a formula (UDF) on a worksheet. Impossible, right? No, it turns out that it is “easy”.

This simple UDF (code shown below) can be entered on a worksheet and the desired worksheet tab will change to any color you want.

Function ChangeTabColor(sht As String, RED_Color As Integer, GREEN_Color As Integer, BLUE_Color As Integer)

With ActiveWorkbook.Sheets(sht).Tab

.Color = RGB(RED_Color, GREEN_Color, BLUE_Color)

End With

End Function

For example, entering this formula in a cell will turn the tab on Sheet1 red.

=ChangeTabColor(“Sheet1”,255,0,0)

This figure shows the result in the example workbook of entering two cells. Note that the UDF does not have to be entered on the worksheet whose tab color is changed.

I have added a worksheet that has a list of colors along with their respective RGB codes for your convenience. I am sure that you will come up with many novel ways to use this technique.

The example file can be downloaded here.

I recently published an article which showed how to document the existing formulas in a range.

You might want to go back and read that article, since the formula demonstrated there was the starting point of the formula presented here.

I wanted to modify that formula so that it would return formulas from a range that contained a specific Excel function. The desired formula for that purpose is shown below.

=TEXTJOIN(CHAR(10),TRUE,IF(NOT(ISERROR(FIND(Function&”(“,FORMULATEXT(fRange)))),ADDRESS(ROW(fRange),COLUMN(fRange))&”:”&FORMULATEXT(fRange),””))

where fRange is the defined range E2:F14

and

where Function is a defined range (in this case, K1).

One point of interest is that this formula does not require the use of ISFORMULA (as in the previous article) to validate whether a cell contains a formula since, for example, the string “SUM(“ would not be found in a cell not containing a formula.

So, this formula “looks” in each cell in fRange and if the function name in K1 is found in a cell, a string with the cell address and the formula is added to an array, which is processed and formatted by the TEXTJOIN function in cell K2 to afford the desired list. In the following figure, the result can be seen for finding formulas containing “SUM”.

In another example, the cells containing the FIND function are returned.

Note that even though some of the formulas return errors, this demonstration still shows the actual formula from each cell meeting the desired criteria.

Finally, if the value for the cell is desired, an expression for that can be added to the main formula. I do not have any plans to do that.

I hope that you find this technique useful.

You can download the example file here.