Tom’s Tutorials For Excel: Programming the Anatomy of a Pivot Table and Pivot Chart

Tom’s Tutorials For Excel: Programming the Anatomy of a Pivot Table and Pivot Chart

Here is a step by step example of how to program a pivot table and pivot chart, including how to place those objects exactly where you want them on your worksheet.

The programming code is in the downloadable workbook, which you can also see at the bottom of this web page following the pictures.

Click here to download this free and safe example workbook.

Sub AnatomyOfPivotTableAndChart()

'Delete all pivot tables and pivot charts that might be on the worksheet,
'to start off with a clean worksheet for this demonstration.

If ActiveSheet.ChartObjects.Count > 0 Then ActiveSheet.ChartObjects.Delete
Dim objPT As PivotTable, iCount As Integer
For iCount = ActiveSheet.PivotTables.Count To 1 Step -1
Set objPT = ActiveSheet.PivotTables(iCount)
objPT.PivotSelect ""
Selection.Clear
Next iCount

'It is a good practice to deselect any embedded object that may be selected.
Application.Goto Range("A1"), 1

'This tells the user about the demonstration process, starting from the beginning.
MsgBox _
"Any pivot table(s) and/or pivot chart(s) that were" & vbCrLf & _
"previously on this worksheet have been deleted, so" & vbCrLf & _
"this demonstration can be shown from the start." & vbCrLf & vbCrLf & _
"This macro in Module1 contains bonus VBA code that" & vbCrLf & _
"shows how to identify a pivot table's range location," & vbCrLf & _
"and how to delete all pivot tables and/or pivot charts" & vbCrLf & _
"on a worksheet." & vbCrLf & vbCrLf & _
"Click OK for the step-by-step demonstration.", 64, _
"Step by step anatomy of a pivot table and pivot chart."

'Declare and define the necessary variables.
Dim lngDestinationColumn As Long, strPivotCache As String
Dim rngChartReportRange As Range, chtChartReport As ChartObject
Dim pvtLastRow As Long, pvtLastColumn As Long, LastRowSourceRange As Long
lngDestinationColumn = Range("B6").CurrentRegion.Columns.Count + 3
strPivotCache = Cells(8, lngDestinationColumn).Resize(2, 2).Address(0, 0)
LastRowSourceRange = Cells(Rows.Count, 2).End(xlUp).Row

'Step 1 - Create the pivot cache.
ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=Range("B6").CurrentRegion).CreatePivotTable _
TableDestination:="R8C" & lngDestinationColumn
Application.Goto Range("A1"), 1
MsgBox "A new pivot cache has been created in" & vbCrLf & _
"range " & strPivotCache & ". It looks like four empty cells" & vbCrLf & _
"with a thin black border around them." & vbCrLf & vbCrLf & _
"Click OK for Step 2.", , "Step 1 of 11."

'Open a With structure for the pivot table.
With ActiveSheet.PivotTables(1)

'Step 2 - First (outer) row field.
With .PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
MsgBox "The first (outer) row field was added" & vbCrLf & _
"to the pivot cache, for ''Region''." & vbCrLf & vbCrLf & _
"Click OK for Step 3.", , "Step 2 of 11."

'Step 3 - Second (inner) row field.
With .PivotFields("Store ID")
.Orientation = xlRowField
.Position = 2
End With
MsgBox "The second (inner) row field was added" & vbCrLf & _
"to the pivot cache, for ''Store ID''." & vbCrLf & vbCrLf & _
"Click OK for Step 4.", , "Step 3 of 11."

'Step 4 - Column field.
With .PivotFields("When")
.Orientation = xlColumnField
.Position = 1
End With
MsgBox "A column field was added to the" & vbCrLf & _
"pivot cache, for ''When''." & vbCrLf & vbCrLf & _
"Click OK for Step 5.", , "Step 4 of 11."

'Step 5 - Filters area.
With .PivotFields("Item")
.Orientation = xlPageField
.Position = 1
End With
MsgBox "This step shows how to add a" & vbCrLf & _
"Filters area, and a field to it." & vbCrLf & _
"The ''Item'' field was added there." & vbCrLf & vbCrLf & _
"Click OK for Step 6.", , "Step 5 of 11."

'Step 6 - Revenue in the Values field.
.AddDataField ActiveSheet.PivotTables(1).PivotFields("Revenue"), "Sum of Amount", xlSum
MsgBox "The ''Revenue'' field was added to the" & vbCrLf & _
"pivot table's Values section." & vbCrLf & vbCrLf & _
"Click OK for Step 7.", , "Step 6 of 11."

'Step 7 - Format numbers in the Values field as Accounting.
.PivotFields("Sum of Amount").NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
Columns(9).AutoFit
MsgBox "The ''Revenue'' field was formatted for Accounting." & vbCrLf & vbCrLf & _
"Click OK for Step 8.", , "Step 7 of 11."

'Step 8 - Define the pivot table's last row and last column so you can know what the row
' 2 rows below that will be, and how far to extend the pivot chart width-wise.

With .TableRange1
pvtLastRow = .Rows.Count + .Row - 1
pvtLastColumn = .Columns.Count + .Column - 1
End With
MsgBox _
"The last row of the pivot table is " & pvtLastRow & "." & vbCrLf & _
"The last column of the pivot table is " & pvtLastColumn & "," & vbCrLf & _
"which is column O." & vbCrLf & vbCrLf & _
"This information will be used in Step 9" & vbCrLf & _
"to define the range where the pivot" & vbCrLf & _
"chart will ultimately be placed." & vbCrLf & vbCrLf & _
"Click OK for Step 9.", , "Step 8 of 11."

'Step 9 - Create the pivot chart in 5 substeps.
'Step 9a - Set the objPT variable for the one (and only in this example) pivot table.

Set objPT = ActiveSheet.PivotTables(1)
'Step 9b - Select the pivot table.
objPT.PivotSelect ""
'Step 9c - Add the pivot chart.
Charts.Add
'Step 9d - Place the pivot chart on the PivotTable's worksheet.
ActiveChart.Location Where:=xlLocationAsObject, Name:=objPT.Parent.Name
'Step 9e - Position the pivot chart to cover a specific range of cells, in this example
' from the row that is 2 rows below the last row of the pivot table
' (which will be row 25), and from the column that is 2 columns to the right
' of the source range's right-most column (which will be column I).
' That is, the pivot table and its pivot chart will occupy the same columns, and
' the pivot chart's last row will be the same last row as its source data range.

Set chtChartReport = ActiveChart.Parent
Set rngChartReportRange = Range(Cells(pvtLastRow + 2, lngDestinationColumn), Cells(LastRowSourceRange, pvtLastColumn))
With chtChartReport
.Left = rngChartReportRange.Left
.Width = rngChartReportRange.Width
.Top = rngChartReportRange.Top
.Height = rngChartReportRange.Height
End With
MsgBox _
"The pivot chart is created and is located" & vbCrLf & _
"exactly where you wanted it in this example." & vbCrLf & vbCrLf & _
"Notice at this moment that the pivot chart" & vbCrLf & _
"is selected. Click OK for Step 10.", , "Step 9 of 11."

'Close the With structure for the pivot table.
End With

'Step 10 - Deselect the pivot chart by going to cell A1.
Application.Goto Range("A1"), True
MsgBox _
"Deselect the pivot chart that you just created." & vbCrLf & _
"A good, user-friendly method is to go to the top" & vbCrLf & _
"of the worksheet, and select cell A1." & vbCrLf & vbCrLf & _
"Click OK for final Step 11.", , "Step 10 of 11."

'Step 11 - Release system memory from the Set object variables.
Set objPT = Nothing
Set chtChartReport = Nothing
Set rngChartReportRange = Nothing
MsgBox _
"The object variables' memory has been cleared." & vbCrLf & _
"The programming process is completed!", , "Step 11 of 11."

End Sub

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Conditional Formatting examples for repeated items in a list.

Tom’s Tutorials For Excel: Conditional Formatting examples for repeated items in a list.

The Conditional formatting rule for unique entries in range A3:A14 is
=COUNTIF($A$3:$A$14,A3)=1

The Conditional formatting rule for non-unique entries in range B3:B14 is
=COUNTIF($B$3:$B$14,B3)>1

The Conditional formatting rule for repeated entries in range C3:C14 is
=COUNTIF($C$3:C3,C3)>1

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Running a PowerPoint Presentation From Excel

Tom’s Tutorials For Excel: Running a PowerPoint Presentation From Excel

Download this Excel workbook that runs a PowerPoint presentation.
Download this accompanying PowerPoint presentation.

Here is how you can run a PowerPoint presentation from Excel, with the below macro named “PowerPointSlideshow”. This macro is also in the downloadable workbook named “RunPowerPointExample” through a link at the top of this blog entry, which runs a slideshow from your PowerPoint presentation. A PowerPoint presentation example that accompanies this Excel workbook’s macro can also be downloaded from the above link.

When the slideshow is completed, you can hit the Esc key or click anywhere on the screen to show the presentation which will still be open. I placed notes in the macro to explain what the code is doing along the way, and to indicate where you can adjust the PowerPoint presentation’s name, path location, and slide viewing time.

The accompanying downloadable presentation contains 3 slides, each shown for 5 seconds that will look like the following 3 pictures:

Slide 1 of 3

Slide 2 of 3

Slide 3 of 3

Here’s the macro, same as in the downloadable workbook:

Sub PowerPointSlideshow()

'Declare and define String variables (modify as desired).
Dim strFilePath As String, strFileName As String, strFilePathAndName
strFilePath = "C:\YourFilePath\"
strFileName = "SlideshowTest.pptx"
strFilePathAndName = strFilePath & strFileName

'Verify that the declared PowerPoint presentation name exists
'in the declared path.
If Len(Dir(strFilePathAndName, vbDirectory)) = 0 Then
MsgBox "There is no file named ''" & strFileName & "''" & vbCrLf & _
"in the path ''" & strFilePath & "''." & vbCrLf & vbCrLf & _
"Please check your PowerPoint presentation's" & vbCrLf & _
"name, and its expected location in the path" & vbCrLf & _
strFilePath & ".", 48, "Cannot continue -- file and path not found."
Exit Sub
End If

'Declare and define Object variables.
Dim ppApp As Object, ppPres As Object
Set ppApp = CreateObject("PowerPoint.Application")
Set ppPres = ppApp.Presentations.Open(strFilePath & strFileName)

'Establish the slides' viewing time duration.
'This example is for 5 seconds.
With ppPres.slides.Range.slideshowtransition
.advanceontime = True
.advancetime = 5
End With

'Run the slide show.
ppPres.slideshowsettings.Run

'Express as True that the PowerPoint presentation was saved,
'if you want to close it without being prompted to save it.
ppPres.Saved = True

'Release the Object variables from memory.
Set ppPres = Nothing
Set ppApp = Nothing

End Sub

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Importing an Access database table into Excel — a Quick Shortcut

Tom’s Tutorials For Excel: Importing an Access database table into Excel — a Quick Shortcut

Here’s a shortcut to manually import an Access database table into your spreadsheet. It’ll come with the alternating shaded rows and field header drop-down arrows, but it’s fast and easy. The first picture shows an access table just for example, noting that in actual practice, the database file will be closed during this quick task that involves no programming code.

Step 1

Step 2

Step 3

Step 4

Your import is complete.

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Showing Your Column Headers as Letters or Numbers

You can show your column headers as letters or numbers. Here’s how to change column headers depending on your preferred look.

TTFE027a
TTFE027b
TTFE027c

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Knowing Any Workbook’s Last Saved Date and Time

You can get the last saved date and time for any workbook whether it is open or closed. In Step 3, for demonstration purposes in the Immediate window, I preceded that line of code with the then-current date and time. In the following pictures, Book1 is the only workbook open in the Visual Basic Editor’s Project window. In the Immediate window of the VBE, I typed the following line of code and pressed Enter to know the last saved date and time for a closed workbook named YourFileName.xlsm located in the path
"C:\Your\File\Path\YourFileName.xlsm"
TTFE026a
TTFE026b
TTFE026c
TTFE026d

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Moving a Cell’s Lines of Text into Separate Columns

I previously posted this example showing how to enter separate lines of text into a single cell. This example shows how to move each line of a cell’s text into its own cell.

The first picture shows five cells in range A3:A7 that each have three lines of text for a person’s name, street address, and their city, state, and zip code.

TTFE025a

Step 1: Parse each cell’s lines of text. Select the range, and press the Alt+A+E keys to show the Text to Columns Step 1 Wizard.
TTFE025b

Step 2: In the Text to Columns Step 1 Wizard, choose the Delimited option and click the Next button.
TTFE025c

Step 3: In the Text to Columns Step 2 Wizard, select Otheras the delimiter, and in the Other field press the Ctrl+J keys. This will create an invisible character that delimits the cells’ individual lines of text. You do not need to go to Step 3; just click the Finish button.
TTFE025d

The final result.
TTFE025e

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Introduced in Version 2016, the “Tell me what you want to do” Field

Introduced in Excel 2016, you can click into the “Tell me what you want to do” field as shown in the picture, or press Alt+Q from your worksheet, to enter a topic for information. This is a fairly intuitive new tool, while saving the interim step of hitting the F1 key for help about a topic.
For example, I typed “array formulas” into that field and the following information was provided.
TTFE0023a
TTFE0023b

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Introduced in Version 2016, the Multi-Select Icon for Pivot Table Slicers

Introduced in Excel 2016 is the Multi-Select icon for pivot table slicers, a welcome convenience to the heretofore requirement of pressing the Ctrl key to select multiple labels.
In the picture for example, I clicked the Multi-Select icon, then clicked to deselect the states of Arizona, Iowa, and Michigan from appearing in the pivot table’s Sum of Sales.
TTFE0022

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Summing a Range of Separate Pairs of Delimited Numbers

Here are two formula options to sum a range of cells, when each cell holds a delimiter (in this example, a hyphen character), and you want to separately sum the cells’ numbers to the left and right sides of the delimiter.
In the picture, the array formula in cell B14 that sums the San Francisco Giants’ hits per at-bat is
=SUM(LEFT(B5:B13,FIND("-",B5:B13)-1)+0)&"-"&SUM(RIGHT(B5:B13,LEN(B5:B13)-FIND("-",B5:B13))+0)
Recall, being an array formula, it is committed to the cell with Ctrl+Shift+Enter, not just with Enter.
This formula in cell E14 (not an array formula) sums the Oakland A’s hits per at-bat:
=SUMPRODUCT((LEFT(E5:E13,FIND("-",E5:E13)-1))+0)&"-"&SUMPRODUCT((MID(E5:E13,FIND("-",E5:E13)+1,10))+0)
Important to note, before the numbers were entered into ranges B5:B13 and E5:E13, the cells in those ranges were formatted as Text. Otherwise, Excel would have regarded most of those entries as dates.
TTFE0021

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Customizing the Status Bar

Tom’s Tutorials For Excel: Customizing the Status Bar
You can right-click the status bar for additional edit, formatting, and analysis options.
TTFE0020a
The following picture shows a Before and After comparison of the additional information you can choose for the status bar to show. In this example, the Minimum and Maximum values were also chosen for the selected range.
TTFE0020b

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Using a Formula to Transpose a Vertical Range Horizontally

Tom’s Tutorials For Excel: Using a Formula to Transpose a Vertical Range Horizontally
As the picture shows by example, you can horizontally transpose a vertical range at any cell outside the vertical range with the formula
=INDEX($A$2:$A$25,COLUMNS($A$2:A25))
Note the absolute and relative references.
TTFE0019

Posted in Tom's Tutorials for Excel
Tags: ,

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:

Tom’s Tutorials For Excel: Conditionally Formatting Early, Late, and Acceptable Times

Tom’s Tutorials For Excel: Conditionally Formatting Early, Late, and Acceptable Times
Here is how you can use Conditional Formatting to identify times that are more than, or less than, a margin of acceptability. In the picture, the cells holding airline flight arrival times that occur more than 60 seconds earlier than their scheduled arrival time are shaded yellow. The Conditional Formatting formula in cell B12 copied down to cell B22 for the yellow cells is
=B12 < A12-(1/1440).
Cells holding an actual arrival time more than 1 minute later than their scheduled time are shaded blue. Their Conditional Formatting formula in cell B12 copied down to cell B22 for the blue cells is
=B12 > A12+(1/1440).
TTFE0008

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function.

Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function
The INDIRECT function can refer to a named range for quick data analysis, especially if you don’t need or want to use a pivot table. In this example, columns B:F hold several years of daily sales activity for a department store. The next picture shows a list of named ranges in the name box that correspond to the header labels.
TTFE7a
The cells in range H2:J2 are data validated. Suppose you want to see historical data for Clothing sales in February 2015. In the next picture, year 2015 is selected in cell H2.
TTFE7b
Next, month 2 is selected, for February in cell I2.
TTFE7c
Finally, the Clothing item is selected in cell J2.
TTFE7d
This formula in cell K2 returns Sales of $13,756:
=SUMPRODUCT((YEAR(Activity)=H2)+0,(MONTH(Activity)=I2)+0,INDIRECT(J2))
TTFE7e
Verifying the formula, here is the range for Clothing sales in February 2015.
TTFE7f

Posted in Tom's Tutorials for Excel
Tags: , ,

Tom’s Tutorials For Excel: Using Undo For Several Actions at Once

Tom’s Tutorials For Excel: Using Undo For Several Actions at Once
You can Undo up to 100 previous actions on your workbook at the same time. Clicking the drop-down arrow next to the Undo button shows the list of your most recent actions, where you can select the point at which to undo the action(s) you want to reverse. The Redo button, next to the Undo button, restores what you reverse.
TTFE6a
TTFE6b
TTFE6c

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Bypassing the Start screen when opening Excel

Tom’s Tutorials For Excel: Bypassing the Start screen when opening Excel
When you open Excel, here is how to bypass the Start screen when you prefer to see a new workbook instead.
TTFE4a
TTFE4b
TTFE4c

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Using Label Headers as Intersecting Lookup Criteria

Tom’s Tutorials For Excel: Using Label Headers as Intersecting Lookup Criteria
I previously posted this example of using the spacebar character as the mathematical operator in a formula to sum numbers in the intersecting range of multiple rows and columns. Also is this other example of using a formula to lookup an intersecting value. Here is a combination of those two methods, using the location of row and column header labels to return their intersecting cell’s value. In the first picture, a worksheet holds data for each region’s quarterly numbers in range B2:F6. Yellow cells H3 and I3 are data validated for lists of Quarter labels in column A, and Region labels in row 1.

The green cells hold the lookup result.
The formula in cell H8 is
=IF(LEN(I8)=0,"","Lookup result:")
The formula in cell I8 is
=IFERROR(INDIRECT(ADDRESS(MATCH(H3,A:A,0),MATCH(I3,1:1,0))),"")
TET4a
In the next picture, you select your Quarter field name.
TET4b
In the next picture, you select your Region field name.
TET4c
Here is the resulting value at the intersection of those 2 selected criteria.
TET4d

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Copying Only a PivotTable’s PivotField to Another Worksheet

Tom’s Tutorials For Excel: Copying Only a PivotTable’s PivotField to Another Worksheet
Here is how you can copy a specific pivot field and display its data on another worksheet. In the following pictures, only the columns of a pivot table’s Sales field are copied to Sheet2 so you can work with that Sales data independent of the original pivot table. The CopyPivotField macro’s code purposely includes the State headers and the Grand Total rows in this example with the Offset and Resize methods, which you can modify if you don’t want those rows in your copied data.
TET3a
TET3b

Sub CopyPivotField()
With ActiveSheet.PivotTables(1)
With .PivotFields("Sum of Sales").DataRange
.Offset(-1).Resize(.Rows.Count + 2).Copy Sheet2.Range("A1")
End With
End With
End Sub

This one-liner (the line continuation underscore character is for readability on the web page) in a macro or in the Immediate window copies a PivotField’s data without a header or totals row:

ActiveSheet.PivotTables(1).PivotFields("Sum of Sales") _
.DataRange.Copy Sheet2.Range("A1")

TET3c

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Reverse Lookup For Times in Milliseconds

Tom’s Tutorials For Excel: Reverse Lookup For Times in Milliseconds
You can format, analyze, and lookup (in this example, reverse VLOOKUP with CHOOSE) times measured in milliseconds.
In the first picture, times for a swimming race are measured in minutes, seconds, and milliseconds. The times in range B4:B12, and in cell B14, are custom format m:ss:000 as shown in the second picture.
TET2a
TET2b

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Opening Excel in a New Instance

Tom’s Tutorials For Excel: Opening Excel in a New Instance
In Excel versions 2013 and later, new workbooks open in the same instance of Excel. Here are 2 options to open your workbook in its own instance.
Option 1
TET1a
TET1b
Option 2
TET1c
TET1d
TET1e

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Averaging Positive and Negative Numbers With Multiple Criteria

Tom’s Tutorials For Excel: Averaging Positive and Negative Numbers With Multiple Criteria

When crunching numbers, there are many ways to slice and dice the Average onion, depending on what criteria you want to include, exclude, combine, or isolate.

The picture shows a list of positive and negative numbers, and an analysis calculation table with 12 formula examples of varying multiple criteria. Notice the subtle syntax differences in formulas between the AVERAGEIF function, and the AVERAGE function with nested IF and ABS arguments.

Some of these formulas are arrays, which I’ve noted below. An array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed by you; Excel places them automatically when the array formula is properly applied. If you are unfamiliar with array formulas, see my video and explanation of arrays here.

AveragingPositiveNegative_MultipleCriteria

Calculating “Average yardage result of all plays”:
The formula in cell F13 is =AVERAGE(C5:C24)

Calculating “Average yardage change all plays (as if all positive)”:
The array formula in cell F14 is =AVERAGE(ABS(C5:C24))

Calculating “Average of positive plays (only for yards gained)”:
The formula in cell F15 is =AVERAGEIF(C5:C24,">0")

Calculating “Average of negative plays (only for yards lost)”:
The formula in cell F16 is =AVERAGEIF(C5:C24,"<0")

Calculating "Average of all Run plays only":
The formula in cell F17 is =AVERAGEIF(B5:B24,"=Run",C5:C24)

Calculating "Average of all Pass plays only":
The formula in cell F18 is =AVERAGEIF(B5:B24,"=Pass",C5:C24)

Calculating "Average Run yardage change (as if all positive)":
The array formula in cell F19 is =AVERAGE(IF(B5:B24="Run",ABS(C5:C24)))

Calculating "Average Pass yardage change (as if all positive)":
The array formula in cell F20 is =AVERAGE(IF(B5:B24="Pass",ABS(C5:C24)))

Calculating "Average of positive Run plays (that gained yards)":
The array formula in cell F21 is =AVERAGE(IF(B5:B24="Run",IF(C5:C24>0,C5:C24)))

Calculating "Average of negative Run plays (that lost yards)":
The array formula in cell F22 is =AVERAGE(IF(B5:B24="Run",IF(C5:C24<0,C5:C24)))

Calculating "Average of positive Pass plays (that gained yards)":
The array formula in cell F23 is =AVERAGE(IF(B5:B24="Pass",IF(C5:C24>0,C5:C24)))

Calculating "Average of negative Pass plays (that lost yards)":
The array formula in cell F24 is =AVERAGE(IF(B5:B24="Pass",IF(C5:C24<0,C5:C24)))

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Numbers By Fractions or Decimals

Tom’s Tutorials For Excel: Rounding Numbers By Fractions or Decimals

When rounding numbers to a particular decimal factor, you can express that rounding factor in your formula as either a fraction or as its decimal equivalent. In the pictures, the fraction one-eighth can be stated in a formula as 1/8 or by its decimal equivalent of .125.

Rounding up
Expressing a fraction, the formula in cell B4 copied to cell B8 is =CEILING(A4,1/8)
Expressing a decimal, the formula in cell D4 copied to cell D8 is =CEILING(A4,0.125)
Notice the results in range B4:B8 are the same as the results in range D4:D8.

Rounding down
Expressing a fraction, the formula in cell C4 copied to cell C8 is =FLOOR(A4,1/8)
Expressing a decimal, the formula in cell E4 copied to cell E8 is =FLOOR(A4,0.125)
Notice the results in range C4:C8 are the same as the results in range E4:E8.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Evaluating Numbers as Being Whole or Decimal

Tom’s Tutorials For Excel: Evaluating Numbers as Being Whole or Decimal

There are times when you want to identify a number as being a whole number (such as 47) or a decimalized number (such as 23.5).

The picture shows three ways to apply this idea. You might want to identify a number as being whole or decimalized in a separate cell; or in the same cell, or disallow its attempt at entry.

In the TRUE or FALSE image, the formula in cell C3 is =MOD(B3,1)=0 to identify decimal numbers.
In the Data Validation image, that same formula rule is used to disallow a decimal upon entry.
In the Conditional Formatting image, the formula to identify decimal numbers is =MOD(B3,1)<>0.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using VLOOKUP With MIN, MAX, and AVERAGE

Tom’s Tutorials For Excel: Using VLOOKUP With MIN, MAX, and AVERAGE

You can nest a function as the lookup_value argument with VLOOKUP, to return an item relating to the lookup_value function. In the pictured example, MIN, MAX, and AVERAGE are nested to return the name of the salesperson associated with those functions.

The formula in cell D2 is =VLOOKUP(MIN(A4:A22),A4:B22,2,0)
The formula in cell E2 is =VLOOKUP(MAX(A4:A22),A4:B22,2,0)
The formula in cell F2 is =VLOOKUP(AVERAGE(A4:A22),A4:B22,2,1)

The VLOOKUP function’s fourth argument is range_lookup. For the MIN and MAX functions, the range_lookup is 0, which could have also been written as FALSE or simply omitted altogether. This deals with your desire and expectation that an exact lookup value will be found. In this example, you know that a specific minimum and maximum number will be found in the list in column A.

However, you cannot be assured that the average of those numbers will specifically exist in the list. The average of the numbers in column A is 5218, not found in column A, so, the 1 (which could have been written as TRUE) was necessary in order for the formula to look up an approximate match.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates

Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates

In yesterday’s tutorial, I showed how to place a date and time on separate lines in the same cell.

That example involved a formula with the TEXT function, meaning the date and time value in cell A3 was not a real date or real time, but a constant text value that was not unlike a text sentence such as you are reading now.

Common wisdom has it that you cannot perform mathematical functions on text dates. For example, if you try to add a 1 to the text value, you will return a #VALUE! error.

However, you start to have some luck with a formula to return all to the left of CHAR(10)
=(LEFT(A3,FIND(CHAR(10),A3)-1)+0)+1
…which returns the serial number of (in this example) the date in cell C3 + 1, which is July 18, 2013.

All that remains is to format the cell for a readable date.
Right-click the cell and select Format Cells from the popup menu.

In the Format Cells dialog box:
• Click onto the Number tab.
• In the Category list box, select Custom.
• In the Type field, enter your desired date format, such as MMMM D, YYYY
• Click the OK button

Your final result would look like this — date math successfully perfomed on a text non date value.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell

Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell

Here’s a tip to place the date and time in the same cell, in separate lines.

The first step is to enter the formula
=TEXT(NOW(),"MMMM D, YYYY")&CHAR(10)&TEXT(NOW(),"HH:MM AM/PM")
The CHAR(10) notation refers to ascii character number 10, which is a carriage return character. In the cell, it looks like a small square, for example as pointed to by the red arrow.

Next,right-click the cell and select Format Cells from that pop-up menu.

In the Format Cells dialog box, click onto the Alignment tab. Click the dropdown arrow for the Horizontal field in the Text alignment section, and select Center.

Lastly, select the option for Wrap text, and click OK.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String

Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String

When you are faced with alphanumeric strings, such as those esoteric-looking serial numbers that represent a store’s stock items, here is how you can deal with parsing them based on the position of their first integer.

To find the position of the first integer, the formula in cell B2 and copied down to cell B13 is
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
Notice for example in cell B2, the formula returns 3. That is because cell A2 contains the alphanumeric string GS5453GDGD5. The first integer in that string is 5 and it is the third character (that is, in the third position) of that string.

To extract everything to the left of the first-found integer, the formula in cell C2, copied to cell C13 is
=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

A final example, to extract all characters to the right of — while including — the first found integer, the formula in cell D2 and copied down to cell D13 is
=TRIM(REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,""))

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding the Number Closest to Zero

Tom’s Tutorials For Excel: Finding the Number Closest to Zero

Here are two formulas, one to tell you the number closest to zero in a list, and the other to tell you the address of the cell holding that number. When you know a list does not contain a zero (if it did, you could simply do a VLOOKUP to find it), you can apply these array formulas as shown.

Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Array formula to return the number itself:
=INDEX(C7:C20,MATCH(MIN(ABS(C7:C20)),ABS(C7:C20),0))

Array formula to return the cell address:
=ADDRESS(MATCH(MIN(ABS(C7:C18)),ABS(C7:C18),0)+ROW(C7)-1,3)

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Returning a Value From Every Nth Cell

Tom’s Tutorials For Excel: Returning a Value From Every Nth Cell

Here’s a formula to help you list (that is, return) the values from every (in this case) 6 cells. This is a useful method when your data is structured such that you know the incremental factor of rows that are in between cells that carry the data you want to separately list. It is especially handy when your source list is hundreds or thousand of rows deep.

In the pictured example, the formula in cell F2, which was copied down to cell F5 is
=OFFSET($C$2,ROW(C2)*6-6,0)

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds

Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds

Formula examples for hours, minutes, and seconds being added or subtracted from time.

Hours
Example to add 3 hours: =$B$1+TIME(3,0,0)
Example to subtract 3 hours: =$B$1-TIME(3,0,0)

Minutes
Example to add 16 minutes: =$B$1+TIME(0,16,0)
Example to subtract 16 minutes: =$B$1-TIME(0,16,0)

Seconds
Example to add 48 seconds: =$B$1+TIME(0,0,48)
Example to subtract 48 seconds: =$B$1-TIME(0,0,48)

Combination of Hours, Minutes, and Seconds
Example to add 3 hours, 16 minutes, and 48 seconds: =$B$1+TIME(3,16,48)
Example to subtract 3 hours, 16 minutes, and 48 seconds: =$B$1-TIME(3,16,48)

When you first enter your formulas, the range will look odd, similar to this:

To fix that, select the range, press Alt+O+E and format the range as Time.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Validating an Entry as a Real Date

Tom’s Tutorials For Excel: Validating an Entry as a Real Date

One way to verify that a bona fide date is being entered into a cell is to use Data Validation.

In the pictured example, dates are being entered into a list in column E. The attempted entry in cell E6 is being rejected because it is not a date.

Step 1 of 4: Before you enter dates, set up your worksheet:
• Start by selecting the range where dates will be entered.
• Then, from your keyboard press Alt+D+L to show the Data Validation dialog box.

Step 2 of 4: In the Data Validation dialog box:
• Go to the Settings tab.
• Click the drop-down arrow for the Allow field.
• In the list of allowable settings, click to select Custom.

Step 3 of 4: Still in the Data Validation dialog box:
In the Formula field, enter your formula rule.
Notice the range being Data Validated (the selected range in Step 1) is E3:E16.
The Data Validation formula being used, with cell E3 as the active cell in that selection, is
=AND(ISNUMBER(E3),LEFT(CELL("format",E3),1)="D")

Step 4 of 4: Still in the Data Validation dialog box:
• Click onto the Error Alert tab.
• Click to select the option for (that is, click to put a check in the box next to) “Show error alert after invalid data is entered”.
• For the Style field, click the drop-down arrow and select “Stop”.
• In the Title field, enter a short headline such as you see here, and as you see in practice in the picture at the top of this tutorial.
• In the Error message field, enter an informative explanation as to why the attempted entry is being rejected, and what the user should do to correct that action.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Conditionally Format Five Highest or Lowest Numbers in a List

Tom’s Tutorials For Excel: Conditionally Format Five Highest or Lowest Numbers in a List

Here’s how you can utilize Conditional Formatting to identify the highest or lowest numbers in a list. Despite the literal title of this lesson, you can highlight the highest or lowest 3, 21, or any numerical measure in your list of numbers; it need not be a measure of 5.

As you’ll see, the relative formula rule for the 5 highest numbers is
=$B3>=LARGE($B$3:$B$22,5)

If for example you wanted to identify the 8 lowest numbers, the formula would be
=$B3<=SMALL($B$3:$B$22,8)

Being a TRUE / FALSE rule, notice the subject cell's (B3 in this example) absolute reference for the column, because the list of numbers is in column B, and the relative reference for the row, because the list being conditionally formatted extends several rows.

To accomplish this, follow these steps:

Step 1 (all versions of Excel) - -
• Select the range of interest.
• Press Alt+O+D to establish your Conditional Formatting.

IF YOU ARE USING EXCEL VERSION 2003 OR BEFORE:

Step 2 (version 2003 or before) - - In the Conditional Formatting dialog box:
• From the drop down list, select Formula Is.
• Enter your formula, which in this example is =$B3>=LARGE($B$3:$B$22,5)
• Click the Format button.

Step 3 (version 2003 or before) - - In the Format Cells dialog box:
• Go to the Patterns tab.
• Select a color from the palette. I chose yellow.
• Click OK.

Step 4 (version 2003 or before) - -
In the Conditional Formatting dialog box, click OK, and you are done.

IF YOU ARE USING EXCEL VERSION 2007 OR AFTER:

Step 2 (version 2007 or after) - -
In the Conditional Formatting Rules Manager dialog box:
• In the "Show Formatting Rules for" field, select Current Selection.
• Click on the item labeled "New Rule".

Step 3 (version 2007 or after) - - In the New Formatting Rule dialog box:
• Select "Use a formula to determine which cells to format"
• Enter your formula, which in this example is =$B3>=LARGE($B$3:$B$22,5)
• Click the Format button.

Step 4 (version 2007 or after) - - In the Format Cells dialog box:
• Go to the Fill tab.
• Select a color from the palette. I chose yellow.
• Click OK.

Step 5 (version 2007 or after) - -
In the New Formatting Rule dialog box, click OK.

Step 6 (version 2007 or after) - -
In the Conditional Formatting Rules Manager dialog box, click OK, and you are done.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Transposing a Dynamic List From Horizontal to Vertical

Tom’s Tutorials For Excel: Transposing a Dynamic List From Horizontal to Vertical

I previously posted this example, of transposing a range by copying it, and selecting the Transpose method in the Paste Special dialog box.

There are plenty of projects that require an immediate transposition using a formula to avoid the burden of manual Copy and Paste Special for Transpose every time a header cell changes.

I showed an example formula on this page that uses the INDIRECT function to transpose a list from vertical to horizontal.

Today’s example shows a different formula solution that transposes a list from horizontal to vertical, using the INDEX and ROWS functions.

In the above picture, the formula =INDEX($B$2:$F$2,ROWS($A$3:A3)) is in cell A3 and copied down to cell A7.

The yellow cells in range B2:F2 hold the original header labels which, if changed, will be automatically reflected in the vertical list in range A3:A7.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Counting Words in a Sentence or String

Tom’s Tutorials For Excel: Counting Words in a Sentence or String

Here is a formula to count the words in a sentence or string of text.
=IF(LEN(A2)=0,0,LEN(A2)-LEN(SUBSTITUTE(A2, " ", ""))+1)

The SUBSTITUTE function handles the possibility of the cell containing no text or value.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Extracting Text to Left of the Second Space (or Specified Character) in a String

Tom’s Tutorials For Excel: Extracting Text to Left of the Second Space (or Specified Character) in a String

Previously, I posted this example of extracting text to the left of the first space, or of some specified character.

When you need the first two words in a string, or text to the left of the second instance of some specified character, here is a formula to extract all text to the left of the second space. I added the SUBSTITUTE function for cases, as shown in cell A5 of the picture, when a trailing comma after the second word is not wanted.

The formula in cell B2, copied down to cell B5, is
=SUBSTITUTE(TRIM(LEFT(A2,FIND(" ",A2&" ",FIND(" ",A2)+2))),",","")

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Formatting 0 to Look Like “Zero”

Tom’s Tutorials For Excel: Formatting 0 to Look Like “Zero”

There are times when you want to see a number as text in order to visually set it apart from other numbers, while maintaining its value as a number. A custom format can accomplish this, because formatting a cell’s value only affects what you see, leaving the actual underlying value unaffected.

In this example, you want the numeral 0 to look like “zero”, as such:

• Select the range of interest. In the pictured example, that would be range B5:D16.
• From your keyboard, press Alt+O+E to show the Format Cells dialog box.
• Click onto the Number tab.
• From the Category list, select Custom.
• In the Type field, enter the custom format [=0]"zero";General
• Click the OK button.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Toggling to Show or Hide Your Group and Outline Buttons

Tom’s Tutorials For Excel: Toggling to Show or Hide Your Group and Outline Buttons

When you have a worksheet with rows and/or columns that have been grouped…

…you can press your keyboard’s Ctrl+8 keys…

…to quickly and easily toggle to show or hide your Group and Outline buttons.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Copying Your Page Setup to Multiple Worksheets

Tom’s Tutorials For Excel: Copying Your Page Setup to Multiple Worksheets

Establishing your Page Setup preferences can involve a lot of steps. You won’t want to repeat those same steps over and over for each worksheet where you’ll want the same preferences.

To show the Page Setup dialog box…

…If you are using Excel version 2003 of before, from the menu bar click File > Page Setup.

…If you are using Excel version 2007 or after, from the Ribbon, click onto the Page Layout tab. Click the small expansion button in the lower right corner of the Page Setup panel.

Here’s the Page Setup dialog box, where you set your preferences and click the OK button.

In this example, I established Page Setup preferences for Sheet1. When you want to copy the same Page Setup preferences to other worksheets, the first step is to select that original worksheet.

The next step is to press the Ctrl key on your keyboard, and use your mouse to select the worksheets as a group to which you want to apply the same Page Setup preferences. In this example, I am copying Sheet1’s Page Setup preferences to Sheet3, Sheet5, Sheet6, and Sheet8.

Next, show the Page Setup dialog box again. Here are two keyboard shortcut tips to do that:

With the desired worksheets selected as a group as shown above, simply click the OK button on the Page Setup dialog box.

Your final step is important to remember: ungroup the worksheets!

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Evaluating a Number Within an Absolute Value Range of Another Number

Tom’s Tutorials For Excel: Evaluating a Number Within an Absolute Value Range of Another Number

You will sometimes be faced with a long list of numbers, maybe thousands of rows deep, and you’ll just want to know if the sum total is within a plus or minus range of a benchmark number. For example, suppose you oversee a region of several retail stores, and you want to know if their total sales dollars is within a percentage range of your regional sales goal.

In the picture, the formula in cell F2 is
=IF(ABS(C22-F1)/F1<=0.07,"In range","Out of range")

The ABS function allows you to evaluate a "plus or minus" range of (in this example) 7% for the total regional sales goal that is in cell F1.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Conditionally Formatting Locked and Unlocked Cells

Tom’s Tutorials For Excel: Conditionally Formatting Locked and Unlocked Cells

There are times when your users will find it easier to enter data directly into worksheet cells, instead of a userform interface. You’ll want to protect all the cells containing formulas and static header labels, while allowing certain cells to be unprotected for users to input data.

Here is how you want to present your worksheet to your users, so they know where to enter data:

Users appreciate seeing exactly where they can — and should — enter data.
You can conditionally format unlocked cells with this boolean (True or False) formula:
=CELL("PROTECT",A1)=0

The above formula is what is being used in this example, shown in the following pictured steps.
As a quick aside, you can conditionally format locked cells with this True/False formula:
=CELL("PROTECT",A1)=1

Although not absolutely necessary, it is advisable 99.9% of the time that you protect your worksheet after you have installed the conditional formatting. This will ensure that users will only have access to the unlocked cells.

Step 1 — Select the entire used range.

Step 2 — Press Alt+O+E to show the Format Cells dialog box:
• Go to the Protection tab.
• Select the option for Locked.
• Click OK, to make sure all cells in that range are locked.

Step 3 — Select the range(s) where you want to unlock the cells for users to enter data.

Step 4 — Press Alt+O+E to show the Format Cells dialog box:
• Go to the Protection tab.
• Deselect the option for Locked.
• Click OK.

IF YOU ARE USING EXCEL VERSION 2003 OR BEFORE:

Step 5 (version 2003 or before) — Press Alt+O+D to show the Conditional Formatting dialog box:
• From the drop down list, select Formula Is.
• Enter the formula =CELL("PROTECT",A1)=0
• Click the Format button.

Step 6 (version 2003 or before) — In the Format Cells dialog box:
• Go to the Patterns tab.
• Select a color from the palette. I chose yellow.
• Click OK.

Step 7 (version 2003 or before) —
In the Conditional Formatting dialog box, click OK, and you are done.

IF YOU ARE USING EXCEL VERSION 2007 OR AFTER:

Step 5 (version 2007 or after) —
Press Alt+O+D to show the Conditional Formatting Rules Manager dialog box:
• In the “Show Formatting Rules for” field, select Current Selection.
• Click on the item labeled “New Rule”.

Step 6 (version 2007 or after) — In the New Formatting Rule dialog box:
• Select “Use a formula to determine which cells to format”
• Enter the formula =CELL("PROTECT",A1)=0
• Click the Format button.

Step 7 (version 2007 or after) — In the Format Cells dialog box:
• Go to the Fill tab.
• Select a color from the palette. I chose yellow.
• Click OK.

Step 8 (version 2007 or after) —
In the Conditional Formatting Rules Manager dialog box, click OK.

Step 9 (version 2007 or after) —
In the New Formatting Rule dialog box, click OK, and you are done.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,