Category Archives: Other

#Excel Super Links #102 – shared by David Hager

 

Using Power BI to Extract Data from Office 365 Reporting Web Services

https://eriksvensen.wordpress.com/2014/11/10/using-power-bi-to-extract-data-from-office-365-reporting-web-services-powerbi-office365/

Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

https://powerpivotpro.com/2017/07/visualize-data-with-maps-dax-reanimator-series-part-2/

Built In and Custom Lists in Excel

https://peltiertech.com/built-in-custom-lists-excel/

How To Customize The Order Of Your Excel Slicer Buttons

https://www.thespreadsheetguru.com/blog/how-to-customize-order-of-excel-slicer-buttons

Bible Verse In Any Language Using #Excel by David Hager

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

 

 

#Excel Sparklines: Display Last N Values by David Hager

Sparklines are a relatively recent addition to the myriad of tools in Excel (version 2010). They are very useful in creating a number of different ways to visualize data. In this example, creating sparklines from a vertical range of data in a table reveals some interesting options. If you make a data table and create a sparkline from it, the process goes smoothly, generating a sparkline that will auto-expand its chart data along with added data to the table as shown in the figure.

sline1a

But, what if you want to display only the last N values in the table. In that case, you need to use OFFSET formulas as ranges, as first discussed in

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

The formula needed to do this (with input from cell F5) is defined as:

LastN1=OFFSET(Sheet1!$A$4,COUNT(TrendData)-Sheet1!$F$5+1,,Sheet1!$F$5)

The method I used to apply this formula range was to create a sparkline from a random selection in the table body and then go back and Edit the sparkline source data as shown below (initially, I had to enter as =LastN1).

sline2

A similar formula was made accepting input from cell F6.

I hope that this technique adds to your data visualization portfolio.

You can download the example file here.

sline1

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

Using conditional formatting to highlight external links has been used before

https://www.extendoffice.com/documents/excel/1539-excel-highlight-external-links.html

but it required a VBA solution. Now, with Excel’s new FORMULATEXT function, it can be accomplished using only Excel formulas. So, using the following formula defined as IsExternalLink

=FIND(“[“,FORMULATEXT(D10))

Conditional formatting will highlight the cells containing “[“, which is associated with external link formulas. But, a more robust formula can also be used, as shown below.

=FIND(“[“,FORMULATEXT(D10))+FIND(“]”,FORMULATEXT(D10))

cfextlink

 

Excel Short & Sweet Tip #6 (Shuffling a String) by David Hager

The ability to randomly scramble a string using Excel formulas does not appear to be possible without helper cells. So, a VBA procedure is needed to accomplish this.

Originally posted at:

https://www.mrexcel.com/forum/excel-questions/37340-word-scramble.html

this VBA function procedure uses a string as the argument and shuffles that string. Copy/paste this procedure into a module in the VBE.

Function ShuffleString(s As Variant)

On Error Resume Next

Dim CL As New Collection

Application.Volatile

ShuffleString = “”

Do Until CL.Count = Len(s)

R = Int(1 + Rnd * Len(s))

CL.Add R, CStr(R)

Loop

For i = 1 To CL.Count

ShuffleString = ShuffleString & Mid(s, CL(i), 1)

Next

End Function

So, the string in A1 is rearranged with =ShuffleString(A1) entered on the worksheet. For example, the string “evert” is shuffled to “rteve”.

Excel Short & Sweet Tip #5 (Hiding Error Triangles) by David Hager

Would you like a way to remove those green error checking triangles from worksheet cells, yet retain error checking? Go to File, Options, Formulas, Error Checking. Leave the “Enable background error checking” box checked, and change the Indicate errors using the color (default is green) to white. Of course, this only works if the cells are white. To turn off the error checking and the green triangles, change the “Enable background error checking” box to unchecked.

 

#Excel Short and Sweep Tip #3 (Freeze Values) by David Hager

Highlight a worksheet range that contains formulas or a mixture of formulas and values. Grab the edge of the range with right-click and pull away, then immediately place back in the same range location. The right-click menu will appear. Click the option labelled “Copy here as Values only”. Every formula in the range will be converted to its underlying value.