**Tom’s Tutorials For Excel: Using a Ribbon Group’s Dialog Launcher**

To see all the options for a Ribbon group, click the dialog launcher in the group’s bottom right corner.

**Tom’s Tutorials For Excel: Using a Ribbon Group’s Dialog Launcher**

To see all the options for a Ribbon group, click the dialog launcher in the group’s bottom right corner.

**Tom’s Tutorials For Excel: Hiding Your Named Ranges**

In the below picture, your workbook’s named ranges can be shown by clicking the down arrow next to the Name box.

The following macro hides the named ranges, and as the next picture shows, you can work with hidden named ranges the same as you would when they are visible.

`Sub HideNamedRanges()`

`Dim nName As Name`

`For Each nName In Names`

`nName.Visible = False`

`' = True to reverse`

`Next nName`

`End Sub`

**Tom’s Tutorials For Excel: Displaying a Column’s Width**

You can display a column’s width manually, or in a cell with a User-Defined Function. The first picture shows that when you press and hold your left mouse button between column headers, a column’s width appears in a tooltip. Column C is 16.43 (120 pixels) wide.

You can write a user-defined function (or copy this one) to return a column’s width in the cell, as seen in the next picture.

`Public Function myWidth(iCol As Integer) As Double`

myWidth = Columns(iCol).ColumnWidth

End Function

**Tom’s Tutorials For Excel: Using SUMPRODUCT on Multiple Columns**

Most examples of the SUMPRODUCT function show a single list of numbers being evaluated for a particular criteria. The following 6 pictures show a simple modification involving SUMPRODUCT, to demonstrate some versatility with that function.

The first picture shows a range of monthly sales of a few warehouse items that are listed as data validated criteria for cell A2. Here, I selected the criteria SKU 567GHI.

In the next picture, cell D2 sums the numbers to 6021 for January, February, and March that belong to SKU 567GHI with this formula that simply includes columns B, C, and D for the SUMPRODUCT range of inclusion:code>=SUMPRODUCT((A7:A100=A2)*B7:D100)

The next picture shows 567GHI summed to 6507 in cell F2 for April, May, and June with this formula:

`=SUMPRODUCT((A7:A100=A2)*E7:G100)`

The next picture shows 567GHI summed to 6433 in cell H2 for July, August, and September with this formula:

`=SUMPRODUCT((A7:A100=A2)*H7:J100)`

The next picture shows 567GHI summed to 8614 in cell J2 for October, November, and December with this formula:

`=SUMPRODUCT((A7:A100=A2)*K7:M100)`

Finally, SKU 567GHI’s total of 27575 in cell M2 for all 12 months is returned by this formula:

`=SUMPRODUCT((A7:A100=A2)*B7:M100)`

**Tom’s Tutorials For Excel: Summing and Averaging Dynamic Lists, Including or Excluding Blank Cells**

The below picture shows a side-by-side comparison of summing and averaging the last 5 cells in a dynamic list, depending on if blank cells should or should not be included in the formula results. Today the list only reaches to row 13; next week the list might comprise one hundred rows or longer.

In Image 1, the 5 bottom-most cells are identified in the formulas’ calculations, including cell A10 which is blank. Cell B2 in Image 1 displays an average of $585.75 returned from this formula:

`=AVERAGE(OFFSET(A2,COUNTA(A1:A100)-5,0,5,1))`

Cell C2 of Image 1 displays a sum of 2343 returned from this formula:

`=SUM(OFFSET(A2,COUNTA(A1:A100)-5,0,5,1))`

In Image 2, the 5 bottom-most occupied cells are identified in the formulas’ calculations, excluding cell A10 which is blank. Array formulas are utilized in this case, which are committed to the cell by pressing `Ctrl+Shift+Enter`

, not just with `Enter`

. Cell B2 displays an average of 538, returned from this array formula:

`=SUM(A100:INDEX(A:A,LARGE(IF(A1:A100<>"",ROW(A1:A100)),5)))/5`

Cell C2 of Image 2 displays a sum of 2690 returned from this array formula:

`=SUM(A100:INDEX(A:A,LARGE(IF(A1:A100<>"",ROW(A1:A100)),5)))`

**Tom’s Tutorials For Excel: Summing Historic Numbers by Date, Excluding Weekends or Weekdays**

When you have a list of numbers for previous dates, such as with payroll or sales activity, here is an example of how you can sum the past 14 days for weekdays only and for weekends only. You can compile any count of previous days by changing the number 14 in the formula to some other count of days.

The first picture shows the sum of Sales activity for the past 14 days, excluding weekend dates, for this example’s current date of April 3, 2017 in cell F1. Cell F2 is shaded yellow, returning $5,444 from this formula:

`=SUMPRODUCT((WEEKDAY(OFFSET(A2,MATCH(MAX(A2:A367),A2:A367,0)-1,,-14),2)<6)*(OFFSET(A2,MATCH(MAX(A2:A367),A2:A367,0)-1,1,-14)))`

The second picture shows the same period of time, with cell F3 shaded blue and returning $2,116 for just the weekends, from this formula:

`=SUMPRODUCT((WEEKDAY(OFFSET(A2,MATCH(MAX(A2:A367),A2:A367,0)-1,,-14),2)>5)*(OFFSET(A2,MATCH(MAX(A2:A367),A2:A367,0)-1,1,-14)))`

**Tom’s Tutorials For Excel: Finding Matches Among Horizontal and Vertical Ranges**

Here is how you can verify if a matching value is found in both a horizontal and vertical range. In Picture #1 a match is not found, but in Picture #2 a match is found.

The formula in cell A1 is

`=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(A3:E3,F6:F17,0)))+0=1,"Found","Not found")`

Conditional formatting is applied to cell A1 for two conditions. The formatting rule for a non-match, which colors cell A1 yellow, is

`=(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(A3:E3,F6:F17,0)))+0)=0`

The formatting rule for a match, which colors cell A1 green, is

`=(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(A3:E3,F6:F17,0)))+0)=1`

__Picture #1 – – no match was found__

__Picture #2 – – match found__

**Tom’s Tutorials For Excel: Using the Home key with Scroll Lock**

Here are a few navigation tips using the Home key, with or without the Ctrl key, with or without Scroll Lock activated, and with Freeze Panes on or off.

**Tom’s Tutorials For Excel: Calculating Negative Time Differences in Hundredths of a Second**

When you want to calculate differences in time when the measurements are in hundredths of a second, special consideration must be given for cases when the result is negative. The next pictures show examples of calculations when the Actual time is less than expected; exactly as expected, and more than expected.

If every calculation was to have a positive result, you could use the formula

`=IF(A2 > =B2,A2-B2,"- "&B2-A2)`

A problem happens when, as shown in cell C4 using that formula, the result is a negative number displayed in an unintuitive format.

The next picture shows the first formula revised with the `TEXT`

function:

`=IF(A2 > =B2,A2-B2,"- "&TEXT(B2-A2,"mm:ss.00"))`

The cells are custom-formatted `mm:ss.00`

**Tom’s Tutorials For Excel: Making Your Hyperlinks User-Friendly**

When you put hyperlinks in cells on a worksheet, it’s a good practice to present them in a user-friendly, intuitive way. For example, the pictures show an original list of hyperlinked cells with their Microsoft-induced wordy screen tips. The modified list was created by the below macro to show those same cells more intuitively, with displayed text and screen tips that are concise and easy to read.

Sub EditLinks()

Dim myHype As Hyperlink

Dim iLink As Integer, strText As String

iLink = 1

For Each myHype In Columns(1).Hyperlinks

strText = myHype.Parent.Text

myHype.TextToDisplay = "Link #" & iLink

myHype.ScreenTip = strText

iLink = iLink + 1

Next myHype

End Sub