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

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.
TTFE0018

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Hiding Your Named Ranges

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.
TTFE0017a
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
TTFE0017b

Posted in Tom's Tutorials for Excel
Tags: ,

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

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.
TTFE0016a
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

TTFE0016b

Posted in Tom's Tutorials for Excel
Tags: ,

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

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.
TTFE0015a
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)
TTFE0015b
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)
TTFE0015c
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)
TTFE0015d
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)
TTFE0015e
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)
TTFE0015f

Posted in Tom's Tutorials for Excel
Tags:

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

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)))
TTFE0014

Posted in Tom's Tutorials for Excel
Tags:

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

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)))
TTFE0013a
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)))
TTFE0013b

Posted in Tom's Tutorials for Excel
Tags: ,

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

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
TTFE0012a
Picture #2 – – match found
TTFE0012b

Posted in Tom's Tutorials for Excel
Tags: ,

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

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.
TTFE0011a
TTFE0011b

Posted in Tom's Tutorials for Excel
Tags: ,

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

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.
TTFE0010a
The next picture shows the first formula revised with the TEXT function:
=IF(A2 > =B2,A2-B2,"- "&TEXT(B2-A2,"mm:ss.00"))
TTFE0010b
The cells are custom-formatted mm:ss.00
TTFE0010c

Posted in Tom's Tutorials for Excel
Tags: ,

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

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.
TTFE0009

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

Posted in Tom's Tutorials for Excel
Tags: