## Tom’s Tutorials For Excel: Using the RAND Function to Extract Random Data

Tom’s Tutorials For Excel: Using the RAND Function to Extract Random Data

Here are examples of how numbers or data can be extracted using the `RAND` function.
The examples and formulas listed below relate to the following picture.

Example 1: Randomly return a number between numbers, such as from 1 to 10.
Formula in cell A4 is `=INT(RAND()*10)+1`

Example 2: Example 1 in practice, to randomly return an item in a 10-item list.
Formula in cell E7 is `=INDEX(A8:A17,INT(RAND()*(10)+1))`

Example 3: Similar to Example 1, this time from 65 to 90.
Formula in cell A20 is `=INT(RAND()*(91-65)+65)`

Example 4: Example 3 in practice, to randomly return a letter of the alphabet.
Formula in cell A23 is `=CHAR(INT(RAND()*(91-65)+65))`

Example 5: Random time between two times such as from 9:00 AM and 2:00 PM.
Formula in cell A26 is `=RAND()*(TIME(9,0,0)-TIME(14,0,0))+TIME(14,0,0)`

## Tom’s Tutorials For Excel: Inserting Numbered Rows of Varying Increments

Tom’s Tutorials For Excel: Inserting Numbered Rows of Varying Increments

As any Excel VBA guru of the workplace knows, it’s you who gets called upon to make sense of whacky downloaded data.

Here, your company’s data warehouse shoots out a file with numbered records in column A that vary because some records are empty. Your job is to fill in the missing record numbers with inserted rows, as seen in the next picture of side-by-side Before and After screen shots.

There might be tens of thousands of these records, so a macro will be most practical. The first pictured example deals with row 1 being used for a header label.

```Sub InsertNumberRowsWithHeaderRow1()
Application.ScreenUpdating = False
Dim xNumber&, xRow&, xDiff%, LastRow&
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For xRow = LastRow To 3 Step -1
If Cells(xRow, 1).Value <> Cells(xRow - 1, 1).Value Then
xDiff = Cells(xRow, 1).Value - Cells(xRow - 1, 1).Value - 1
If xDiff > 0 Then Rows(xRow).Resize(xDiff).Insert
End If
xDiff = Range("A2").Value
Next xRow
If xDiff > 1 Then Rows(2).Resize(xDiff - 1).Insert
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
With Range("A2:A" & LastRow)
.FormulaR1C1 = "=ROW()-1"
.Value = .Value
End With
Application.ScreenUpdating = True
End Sub```

The next macro applies to the following pictured circumstance when no header row is used; that is, the actual data starts on row 1.

```Sub InsertNumberRowsNoHeader()
Application.ScreenUpdating = False
Dim xNumber&, xRow&, xDiff%, LastRow&
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For xRow = LastRow To 2 Step -1
If Cells(xRow, 1).Value <> Cells(xRow - 1, 1).Value Then
xDiff = Cells(xRow, 1).Value - Cells(xRow - 1, 1).Value - 1
If xDiff > 0 Then Rows(xRow).Resize(xDiff).Insert
End If
xDiff = Range("A1").Value
Next xRow
If xDiff > 1 Then Rows(1).Resize(xDiff - 1).Insert
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
With Range("A1:A" & LastRow)
.FormulaR1C1 = "=ROW()"
.Value = .Value
End With
Application.ScreenUpdating = True
End Sub```

## Tom’s Tutorials For Excel: Formatting Numbers for Thousands or Millions

Tom’s Tutorials For Excel: Formatting Numbers for Thousands or Millions

When you have a range of large numbers that can be difficult to comprehend because of their size, it helps to make the range more readable by formatting the numbers as thousands or millions.

The first step in formatting the cells is to select the range, right-click the selected range, and click Format Cells from the popup menu to show that Format Cells dialog box. Alternatively, with the range selected, from your keyboard you could press `Alt+O+E`.

To format the numbers in thousands, and with an indicative “K” suffix:
• Click onto the Number tab.
• In the Category list, select Custom.
• In the Type field, enter `#,##0,K`
• Click OK.

Here is the result of that custom format for Thousands.

To format the numbers in millions, with an indicative “M” suffix and a decimal for fun:
• Click onto the Number tab.
• In the Category list, select Custom.
• In the Type field, enter `#,##0.0,,\M`
• Click OK.

Here is the result of that custom format for Millions, with an optional single decimal.

## Tom’s Tutorials For Excel: Using AutoCorrect for Faster Data Entry

Tom’s Tutorials For Excel: Using AutoCorrect for Faster Data Entry

Here’s a tip for shortening the effort of entering recurring lengthy text.

Suppose there are words or phrases you need to type often in your spreadsheets, such as boilerplate legal text, or the name of your company. You can use AutoCorrect to tell Excel that when you type a certain combination of characters, what you really want is the lenghtier text to be entered.

For example, the name of my company is Atlas Programming Management. As a shortcut to save myself time and effort, I simply type APM to have my full company name be entered, as seen in the following Before and After pictures.

To use AutoCorrect in this way, press `Alt+T+A` to show the AutoCorrect dialog box.

In the AutoCorrect dialog box:
• Click onto the AutoCorrect tab.
• Enter the short text in the Replace field.
• Enter the lengthy text in the With field.
• Click the OK button.

## Tom’s Tutorials For Excel: Setting 2007+ PivotTables for Drag and Drop

Tom’s Tutorials For Excel: Setting 2007+ PivotTables for Drag and Drop

Excel’s PivotTable models changed starting with version 2007. If you miss the ability to drag and drop field items from the field list into a pivottable’s drop zones the way you could do in the good old days of versions 2003, here’s how you can adjust that setting.

This setting also provides the functionality of dragging (pivoting) fields directly on the pivottable, for example from rows to columns or from columns to rows.

Default look of a pivottable being built in version 2010.

With the Data area selected, from the Ribbon:
• Click the Options tab in the PivotTable Tools section.
• Click the PivotTable icon in the far left panel of the Ribbon.
• From the drop-down menu, click Options, then click Options again.

In the PivotTable Options dialog box:
• Click the Display tab.
• Select the option “Classic PivotTable layout (enables dragging of fields in the grid)”
• Click the OK button.

Here are the drag and drop zones.

## Tom’s Tutorials For Excel: Using The Legacy PivotTable Wizard in Versions 2007 and After

Tom’s Tutorials For Excel: Using The Legacy PivotTable Wizard in Versions 2007 and After

Are you using Excel version 2007, 2010, or 2013? Are you a pivottable builder who misses the legacy PivotTable and PivotChart Wizard? Instead of going the Ribbon’s route of clicking the PivotTable icon on the Insert tab, press `Alt+D+P` on your keyboard to build your pivottable from the familiar-looking Wizard, as pictured below.

## Tom’s Tutorials For Excel: Finding and Replacing on All Worksheets

Tom’s Tutorials For Excel: Finding and Replacing on All Worksheets

Sometimes overlooked is Excel’s ability to Find and Replace across all worksheets in a workbook, without needing to select all the worksheets.

• Select any cell in any sheet.
• From your keyboard press `Ctrl+H` to show the Find and Replace dialog box.

On the Replace tab, select these options and click the Replace All button:
• In the Within field, select Workbook from the dropdown list.
• Selecting Match case kept “Tom” untouched on Sheet3 in cell A3.
• I kept the entire Match entire cell contents field deselected.

## Tom’s Tutorials For Excel: Filling Across Worksheets

Tom’s Tutorials For Excel: Filling Across Worksheets

I previously posted this example for making changes on all sheets at once.

Here’s a quick and easy solution when the changes you want to put on all worksheets have already been made to one worksheet. If your workbook only holds one or two other worksheets, you could copy and paste, but if your workbook holds dozens of worksheets, you’ll want a better way.

Right-click any worksheet tab, and click Select All Sheets from the popup menu.

Select the cell(s) that have been changed on the one worksheet.
In this example, that would be A1:D1.

Then…

…If you are using Excel version 2003:
• Click the Edit button on the menu bar.
• Click the Fill flyout menu item.
• Select the Across Worksheets item.
• Select the All option in the Fill Across Worksheets dialog box, and click OK.

…Or if you are using Excel version 2007 or after:
• Get onto the Home tab and find the Editing panel on your Ribbon.
• On the Editing panel, click the Fill icon and select the Across Worksheets menu item.
• Select the All option in the Fill Across Worksheets dialog box, and click OK.

Remember to ungroup (deselect) all worksheets!
To do that, simply click onto any worksheet tab that is not the active worksheet.

## Tom’s Tutorials For Excel: Formatting Negative Numbers Red With Minus Sign

Tom’s Tutorials For Excel: Formatting Negative Numbers Red With Minus Sign

Absent from Excel’s standard negative number formats is one that colors negative numbers red with a preceding negative (minus) sign. You need a custom format to make the Before picture look like the After picture, as shown below.

Select the range of interest, right-click the selection, and click Format Cells.

In the Format Cells dialog box:
• Click onto the Number tab.
• In the Category list, select Custom.
• In the Type field, enter `#,###;[Red]-#,###;0`
• Click the OK button.

## Tom’s Tutorials For Excel: SUMIF With Character Count Criteria

Tom’s Tutorials For Excel: SUMIF With Character Count Criteria

With the question mark wildcard character, which represents one placeholder character of any kind, you can sum or count items in a list based on a minimum, maximum, or exact count of characters.

In the pictured example, column A contains stock number items, but also for “Gift” items. Because the word “Gift” contains 4 characters, and all stock numbers contain at least 5 characters, you can sum for numbers in column B that correspond to stock items that have at least 5 characters.

The formula in cell D2 is
`=SUMIF(A3:A15,"?????*",B3:B15)`
Note that the five wildcard question mark characters followed by the wildcard asterisk character, represents the criteria requirement of at least five characters.

## Tom’s Tutorials For Excel: Making Changes on All Sheets at Once

Tom’s Tutorials For Excel: Making Changes on All Sheets at Once

Here’s a time-saving tip for newcomers or casual users of Excel. When you need to make the same changes on all your worksheets, you only need to make the changes one time and still have every worksheet show those changes.

All it takes is the act of selecting all the sheets, making your changes once, and deselecting the sheets. Here’s how, with a pictorial example.

## Tom’s Tutorials For Excel: Using a Data Validation List From a Named Range

Tom’s Tutorials For Excel: Using a Data Validation List From a Named Range

Here is how you can apply a Data Validation list for cells in one sheet, with that source list existing on another sheet. The process involves creating a named range for the source list, as shown by the following 7 steps.

Step 1 of 7: Select your source list, and from your keyboard press `Ctrl+F3`.

Step 2 of 7: In the Name Manager dialog box, click the New button.

Step 3 of 7: In the New Name dialog box…
• Enter a name for your range, in this example PositionTitles.
• Select Workbook as the Scope.
• Click the OK button.

Step 4 of 7: You’ll return to the Name Manager dialog box; just click the Close button.

Step 5 of 7:
• Select the worksheet where your range is located that will be Data Validated.
• Select the range of cell(s) that will be Data Validated.
• From your keyboard, press `Alt+D+L` to show the Data Validation dialog box.

Step 6 of 7: In the Data Validation dialog box…
• Click onto the Settings tab.
• For the Allow field, click the drop-down arrow and select List.
• In the Source field, enter the equals sign and your named source range from Step 3, example =PositionTitles.
• Click the OK button.

Step 7 of 7:
Your selected range is now Data Validated.
Click the drop-down arrow for your active cell, and select an item from the source list.

## Tom’s Tutorials For Excel: Careful! That was *Alt*+F11 !

Tom’s Tutorials For Excel: Careful! That was *Alt*+F11 !

From your active workbook, a fast and easy way to get into the Visual Basic Editor is to press `Alt+F11` on your keyboard. You can do this from any worksheet.

Careful, that was `*Alt*+F11!` The `Ctrl` key is commonly used in conjunction with other keys for keyboard shortcuts. By force of habit, you might mistakenly press `Ctrl+F11` instead of `Alt+F11` when attempting to go to the VBE.

Pressing `Ctrl+F11` has a curious result. You won’t be taken to the VBE, but instead you will have created and find yourself on an outdated type of sheet called a macro sheet, with the strange tab name of Macro1. What’s up with that?

Prior to Excel version 97, macros were stored on macro sheets, which can still be created, though they have no practical use with today’s Excel, and they no longer hold any programming code. It’s OK to just delete the macro sheet if you create one, and take another stab at the `Alt` key with `F11` to get into the VBE.

## Tom’s Tutorials For Excel: Calculating Times Between Time Zones

Tom’s Tutorials For Excel: Calculating Times Between Time Zones

With the `TIME` function you can add or subtract times between time zones.
Then you can adjust the formula cells for a recognizable time format.

The formula in cell D3 copied down to cell D9 is
`=IF(C3>=0,B3-TIME(C3,,),B3+TIME(ABS(C3),,))`

Select the range, right-click the selection, and select Format Cells.

In the Format Cells dialog box, on the Number tab, select Time from the Category list, select a Time format, and click OK.

## Tom’s Tutorials For Excel: Commenting a Cell With Data Validation

Tom’s Tutorials For Excel: Commenting a Cell With Data Validation

As a practical alternative to using a comment for supplying information about a cell, consider using Data Validation instead. The advantage is, the user sees the popup message for directions or tips when they select the cell as they enter or edit data.

In the pictured sequence for example, select the range of cells that you want to supply a popup message for when a cell in that range is selected. Then, from your keyboard press `Alt+D+L` to show the Data Validation dialog box.

Next:
• Click the Input Message tab.
• Check “Show input message when cell is selected”
• Enter a short title for the main idea of the cell entry.
• Enter a brief tip for the entry of data in that cell.
• Click OK.

You can see the result of the pictured settings below, when a cell in that range is selected.

## Tom’s Tutorials For Excel: History Lesson — A Reserved Sheet Name

Tom’s Tutorials For Excel: History Lesson — A Reserved Sheet Name

If you try to rename an Excel worksheet or chart sheet as History, you’ll be stopped in your tracks, as the following picture sequence shows.

The question is, why is the word History so special that you cannot use it as a sheet name?
The answer is, Excel uses the word History as part of its history tracking feature. Some background:

In Excel, you can save a file as a shared workbook, so that more than one user can work on the same data simultaneously. To keep everyone informed with changes that have occurred among users in a shared workbook, Excel creates a worksheet named “History” to display those changes.

Whether or not your workbook is shared, Excel takes no chances, and prevents you from naming a worksheet “History”. To view a History sheet example, follow these pictured steps.

Step 1
From your keyboard, press `Alt+T+T+H` to show the Highlight Changes dialog box.

Step 2
In the Highlight Changes dialog box:
• Check the “Track changes while editing…” option.
• Check the When field, and from the drop-down menu, select All.
• Deselect the Who and the Where fields.
• Check “List changes on a new sheet”
• Click OK.

Here is a typical-looking History sheet. You can click the AutoFilter drop-down arrows to limit or expand the historical changes you want to see, and who made them.

## Tom’s Tutorials For Excel: Going Home — Using the Home shortcut key

Tom’s Tutorials For Excel: Going Home — Using the Home shortcut key

The keyboard’s `Home` key by itself takes you to the cell in column A of your active cell’s row, or with `Ctrl+Home` takes you to cell A1. If Freeze Panes is being used, `Home` takes you to the Freeze Panes column of the active cell’s row, and `Ctrl+Home` takes you to the Freeze Panes’ intersecting cell.

Using `Home`, go to column A of the active cell’s row.

Using `Home`, go to the Freeze Panes column of the active cell’s row.

Using `Ctrl+Home`, go to cell A1.

Using `Ctrl+Home`, go to cell B3 in this case, where Freeze Panes is set.

## Tom’s Tutorials For Excel: Counting All Data Types in a Range

Tom’s Tutorials For Excel: Counting All Data Types in a Range

Any data that you put in a cell will always fall into one of four types:
• Text
• Numeric
• Boolean (TRUE or FALSE)
• Error

In the picture, you see a representation of all these data types in column A. The table in the colored cells of range C2:E6 contain formulas that count each of these four data types, along with a formula that counts the full collection of all cell data type entries.

The formula in yellow cell E2 that counts text only is
`=COUNTIF(A3:A28,"*?")`

The formula in brown cell E3 that counts numbers only is
`=COUNT(A3:A28)`

The formula in blue cell E4 that counts boolean TRUE/FALSE only is
`=SUM(COUNTIF(A3:A28,{TRUE,FALSE}))`

The array formula in pink cell E5 that counts errors only is
`=SUM(IF(ISERROR(A3:A28),1))`
Recall that an array formula is applied to the cell with `Ctrl+Shift+Enter`, not just with `Enter`.

The formula in gray cell E6 that counts all data type entries is
`=COUNTA(A3:A28)`

## Tom’s Tutorials For Excel: Showing Text Only by Hiding Numbers

Tom’s Tutorials For Excel: Showing Text Only by Hiding Numbers

You can custom format a cell to show or hide only positive numbers, negative numbers, zeros, or text. Here, a range of cells is formatted to show only text with the custom format `;;` (that is, with two semicolon characters) as seen in the following pictures.

Note that in column D, dates are hidden because Excel regards dates as numbers.

• Select the range of interest.
• Right-click that selection.
• Click to select Format Cells from the popup menu.

• In the Format Cells dialog box, select the Number tab.
• Select Custom from the Category list.
• Enter the custom format `;;` into the Type field.
• Click OK.

## Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells

Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells

When you want to center a cell’s value across several columns, please avoid the Merge Cells method. Merged cells cause more programming and design headaches than any single feature in Excel.

There is a better way, by formatting the cells using “Center Across Selection”, shown by example in the following Before and After set of pictures.

To accomplish this, simply select the cells where you want the header value to be centered across. In this example, the header value is in cell A1, and range A1:D1 has been selected. Next, right-click the selected range, and from the popup menu, click on Format Cells.

In the Format Cells dialog box, click onto the Alignment tab.
Click the drop down arrow for the Horizontal field in the Text Alignment section.
Select “Center Across Selection”, and click OK.

## Tom’s Tutorials For Excel: Finding the Attributes of the Maximum Length Value in a List

Tom’s Tutorials For Excel: Finding the Attributes of the Maximum Length Value in a List

Here are formulas that return various attributes of the lengthiest value in a list. In the pictures are formulas for the lengthiest value itself; its length; its row in the list; and its cell address.

These are all array formulas. Recall, an array formula is applied to the cell by pressing
`Ctrl+Shift+Enter`, not just `Enter`.

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

Formula for maximum value:
`=INDEX(A4:A10,MATCH(MAX(LEN(A4:A10)),LEN(A4:A10),0))`

Formula for maximum value length:
`=MAX(LEN(A4:A10))`

Formula for maximum value row (index number row in range of interest):
`=MATCH(MAX(LEN(A4:A10)),LEN(A4:A10),0)`

Formula for maximum value cell address:
`=ADDRESS(MAX(ROW(A4:A10)*(LEN(A4:A10)=MAX(LEN(A4:A10)))),1)`

## Tom’s Tutorials For Excel: Stripping the Last Nth Characters From a String

Tom’s Tutorials For Excel: Stripping the Last Nth Characters From a String

Here are formulas to strip away the last Nth characters from a string, where N is the count of characters you want to strip. Notice that if you have a list of items with varying locations of the character of interest, the formula in column E is a one-size fits all, in cases like this when only one such character would be present in the string.

The formula in cell B3 copied down to B5 is `=LEFT(A3,LEN(A3)-4)`.

The formula in cell C3 copied down to C5 is `=LEFT(A3,LEN(A3)-5)`.

The formula in cell D3 copied down to D5 is `=LEFT(A3,LEN(A3)-6)`.

The formula in cell E3 copied down to E5 is `=LEFT(A3,SEARCH(".",A3)-1)`.

## Tom’s Tutorials For Excel: Calculating Elapsed Hours Between Start and End Times

Tom’s Tutorials For Excel: Calculating Elapsed Hours Between Start and End Times

Here are two formulas showing elapsed time in hours between a Start Time and an End Time.

In the first picture, the formula in cell E1 is `=(B2-A2)*24`.
You can format that cell as Number and set as many or few decimals as you like.

In the next picture, the formula in cell E2 is `=ROUND((B2-A2)*24,2)`.
The `num_digits` argument is entered as 2 which sets the numeric value for two decimal places.

## Tom’s Tutorials For Excel: Summing Cells in Even or Odd Numbered Rows Only

Tom’s Tutorials For Excel: Summing Cells in Even or Odd Numbered Rows Only

Here are formulas using the `SUMPRODUCT` function that sum a range of cells that reside in only the even-numbered or odd-numbered rows.

Summing only the even-numbered rows.
The formula in selected cell C21 copied to cell F21 is
```=SUMPRODUCT(C4:C18,MOD(ROW(C4:C18)+1,2)) Summing only the odd-numbered rows. The formula in selected cell C22 copied to cell F22 is =SUMPRODUCT(C5:C19,MOD(ROW(C5:C19)+0,2)) ```

``` ```
``` Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis, Worksheets Workbooks ```
``` Tom’s Tutorials For Excel: Verifying a Time as AM or PM by Tom Urtis 7 Comments ↓ Tom’s Tutorials For Excel: Verifying a Time as AM or PM Here’s how you can verify a cell’s time value as either morning (AM), or afternoon or evening (PM). This is useful if you encounter a cell whose formatting does not show the time itself, such as in the pictured example below. The formula in cell B2 is =IF(MOD(A2,1)>=0.5,"PM","AM"). The “>0.5” portion of the formula sets the condition of the half-day mark, that being 12:00 noon, which separates the AM and PM halves of the 24-hour day. Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Date Time, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis Tom’s Tutorials For Excel: Summing Every Nth Cell by Tom Urtis 44 Comments ↓ Tom’s Tutorials For Excel: Summing Every Nth Cell Here’s an array formula that allows you to sum every Nth cell, where “N” is the interval number between cells. In the pictured example, you see that every 7 cells are summed in the budget worksheet with the array formula =SUM(IF(MOD(ROW(B7:B28),7)=0,B7:B28)) You are not limited to the SUM function. To average every 7 cells, modify the formula as =AVERAGE(IF(MOD(ROW(B7:B28),7)=0,B7:B28)) Recall, an array formula is applied to the cell by pressing Ctrl+Shift+Enter, not just Enter. If you are unfamiliar with array formulas, see my video and explanation of arrays here. Posted in Tom's Tutorials for Excel Tags: Analytics, Array Formulas, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis, Worksheets Workbooks Tom’s Tutorials For Excel: Incremental Summing: Every 5 Cells by Tom Urtis 71 Comments ↓ Tom’s Tutorials For Excel: Incremental Summing: Every 5 Cells Here’s how you can incrementally sum a range of cells. In the pictured example, each set of five cells in range D2:D26 are summed in range G2:G6. The formula in cell G2 and copied down to cell G6 is =SUM(OFFSET(\$D\$2,(ROW()-ROW(\$D\$2))*5,0,5,1)) You can do this with any incremental count; it need not be 5 cells. For example, if you want to sum each set of 9 cells, your formula would be modified like this: =SUM(OFFSET(\$D\$2,(ROW()-ROW(\$D\$2))*9,0,9,1)) Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis Tom’s Tutorials For Excel: Entering Fractions In Your Text by Tom Urtis 7 Comments ↓ Tom’s Tutorials For Excel: Entering Fractions In Your Text While it’s true that you can insert fraction symbols from the Symbol dialog box into your cell’s text, it’s faster and easier to produce those symbols by using the keyboard as you are typing your text. Here are three examples that show how you can use the keyboard to enter the common fractions for one-fourth, one-half, and three-fourths in your non-numeric text entries. Each sentence is a little different but you’ll quickly get the idea. Example 1 text for the one-fourth: The fraction symbol for one-fourth is ¼. You enter (without the square brackets): The fraction symbol for one-fourth is [Alt+0188]. Example 2 text for one-half: ½ of 10 is 5. You enter (without the square brackets): [Alt+0189] of 10 is 5. Example 3 text for three-fourths: My car's gasoline tank is ¾ full. You enter (without the square brackets): My car's gasoline tank is [Alt+0190] full. Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Keyboard Mouse, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Numbers and Decimals, Text and Constants, Tom Urtis Tom’s Tutorials For Excel: Random Ramblings by Tom Urtis No Comments ↓ Tom’s Tutorials For Excel: Random Ramblings Here’s something to think about regarding random numbers in Excel. Random numbers are based on a predictable algorithm. If you know the previous number and the algorithm, you can predict the next number generated by the random function. This sounds counter intuitive, because random numbers generated by computers are not truly random. A computer uses something called a “random number generator” to produce a series of numbers that satisfy what most people need for randomness, but really, the numbers are not random. You can test this yourself. Run the following macro on a blank worksheet to see just how “random” random numbers are, and are not. It will produce two columns of 50000 rows each of random numbers. In a truly random environment, what would be the odds of any one pair of rows (that is, for example, the value in cell A32476 equaling the value of cell B32476)? What would be the odds of ALL 50000 pairs equaling each other? You and I would have better luck winning the Powerball lottery jackpot on a day when there’s world peace and the Chicago Cubs winning the World Series. Sub RandomTest() Application.ScreenUpdating = False Dim x As Long Rnd -1 Randomize 1 For x = 1 To 50000 Cells(x, 1).Value = Rnd Next x Rnd -1 Randomize 1 For x = 1 To 50000 Cells(x, 2).Value = Rnd Next x Application.ScreenUpdating = True End Sub Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Macros Events Classes API, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis Tom’s Tutorials For Excel: Entering International Currency Symbols by Tom Urtis 5 Comments ↓ Tom’s Tutorials For Excel: Entering International Currency Symbols While it’s true that you can insert currency symbols from the Symbol dialog box into your cell’s text, it’s faster and easier to produce those symbols by using the keyboard as you are typing your text. Here are four examples to show how you can use the keyboard to enter currency symbols in your non-numeric text entries. Each sentence is a little different but you’ll quickly get the idea. Example 1 text for the United States Dollar symbol: The currency symbol for the United States Dollar is \$. You enter (without the square brackets): The currency symbol for the United States Dollar is [Alt+0036]. Example 2 text for the European Union Euro symbol: € is the European Union's Euro currency symbol. You enter (without the square brackets): [Alt+0128] is the European Union's Euro currency symbol. Example 3 text for the British Pound symbol: The currency symbol £ represents the British Pound. You enter (without the square brackets): The currency symbol [Alt+0163] represents the British Pound. Example 4 text for the Japanese Yen symbol: My California home would cost ¥70,968,965 in Japan. You enter (without the square brackets): My California home would cost [Alt+0165] 70,968,965 in Japan. Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Formats Fonts Styles Colors, Keyboard Mouse, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Numbers and Decimals, Tips and Tricks, Tom Urtis Tom’s Tutorials For Excel: Setting Alignments For Date Left and Time Right in a Single Cell by Tom Urtis 2 Comments ↓ Tom’s Tutorials For Excel: Setting Alignments For Date Left and Time Right in a Single Cell If you have the date and time in a single cell, such as when you enter the NOW function, you can custom format the cell as MMM D, YYYY * H:MM AM/PM to separate the date (flush left inside the cell), and the time (flush right inside the cell). Notice the asterisk in the format statement that causes the visual separation in the cell of the date and time portions. Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Date Time, Excel Expert, Excel Guru, Formats Fonts Styles Colors, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tips and Tricks, Tom Urtis Tom’s Tutorials For Excel: Selecting a Large Range Without Scrolling or Dragging. by Tom Urtis 5 Comments ↓ Tom’s Tutorials For Excel: Selecting a Large Range Without Scrolling or Dragging. Here is a handy shortcut tip when you need to select a large range. In the example, I selected a small range of B2:H5 so the pictures can fit onto this web page for demonstration purposes. But what if you need to select a large range from B2 to, say, HM62000. It would be a burdensome task to scroll down all those rows, and across all those columns, while dragging your mouse from B2:HM62000. There’s a faster and easier way to select a large range of cells. Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Keyboard Mouse, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tips and Tricks, Tom Urtis Tom’s Tutorials For Excel: Comparing Lists For Identical Match by Tom Urtis No Comments ↓ Tom’s Tutorials For Excel: Comparing Lists For Identical Match Here’s an example with the formula =AND(A1:A10=B1:B10) that returns TRUE when all pairs of cells match in a two-column list, and FALSE when at least one pair is different in any way. This is an array formula, where you need to enter it with Ctrl+Shift+Enter, not just with Enter. If you are new to array formulas, see my video and explanation of arrays here. Posted in Tom's Tutorials for Excel Tags: Analytics, Array Formulas, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis Tom’s Tutorials For Excel: Repeating Your Last Action FAST! The Magic of the F4 Key. by Tom Urtis 9 Comments ↓ Tom’s Tutorials For Excel: Repeating Your Last Action FAST! The Magic of the F4 Key. Take a look at this gem: The F4 key is a little-used but highly efficient shortcut for speeding up your Excel tasks, when you need to repeat the same action once, twice, or dozens of times. In Excel (and other Office apps as well), many tasks can be instantly completed by hitting the F4 key for as many times as you want to repeat your last action. The following pictures (formatting ranges, inserting new worksheets, and deleting rows) are just three examples that are repeatable with F4. First example — Formatting ranges Second example — Inserting worksheets Third example — Deleting rows Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Keyboard Mouse, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tips and Tricks, Tom Urtis Tom’s Tutorials For Excel: Filling Series For Linear and Growth by Tom Urtis 3 Comments ↓ Tom’s Tutorials For Excel: Filling Series For Linear and Growth Here is an example of building a table of projected sales and income, using three Fill methods of Series, Linear, and Growth. TIP: First Example: Fill Series (add a year number to each filled cell) • Enter the first year of your data, example 2010 in cell A4. • Select cell A4 and drag its fill handle down while pressing the right mouse button. • Release the right mouse button, and from the popup menu, select Fill Series. Second Example: Series, Growth (project your Unit Sales to double every year) • Enter your first item of data in the Unit Sales column, example 31 in cell B4. • Select cell B4 and drag its fill handle down while pressing the right mouse button. • Release the right mouse button, and from the popup menu, select Series. • In the Series dialog box, select Growth (as Type). • I entered 2 for Step value, projecting each year’s Unit Sales to double. • Click OK. Third Example: Series, Linear (add a specific static number to its previous number) • Enter your first item of data in the Unit Price column, example \$49 in cell C4. • Select cell C4 and drag its fill handle down while pressing the right mouse button. • Release the right mouse button, and from the popup menu, select Series. • In the Series dialog box, select Linear (as Type). • I entered 3 for Step value, because I expect to raise my Unit Price by \$3 every year. • Click OK. Final Result !! Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Cut Copy Paste Clear Fill, Data Science, Excel Expert, Excel Guru, Keyboard Mouse, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tips and Tricks, Tom Urtis Tom’s Tutorials For Excel: Extracting the Last Word in a Cell by Tom Urtis 4 Comments ↓ Tom’s Tutorials For Excel: Extracting the Last Word in a Cell When you want to extract the last word in a cell, be it a last name or whatever the word or character is that follows the last space in a string, this formula can do that, as seen in the picture. I purposely broke the formula into two lines so it will display on this web page for all browsers, but you would enter it as one continuous statement, just as any formula. =MID(A2,1+FIND("~",SUBSTITUTE(A2," ","~", LEN(A2)-LEN(SUBSTITUTE(A2," ","")))),255) Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Find Replace Substitute, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis Tom’s Tutorials For Excel: Programming Multiple Drawing Shapes by Tom Urtis 8 Comments ↓ Tom’s Tutorials For Excel: Programming Multiple Drawing Shapes Here’s how you can assign one macro to multiple drawing shapes on your worksheet, and have them all behave uniquely, depending on which shape was clicked. In the pictured example, four Oval drawing shapes were placed on the worksheet, keeping their default names of “Oval 1”, “Oval 2”, “Oval 3”, and “Oval 4” but changing their captions to “Quarter 1” and so on.. The same macro (posted below) was assigned to each Oval shape. When an Oval is clicked, its color becomes red for easier identification, and the other 3 Oval shapes become yellow, using RGB. Also, depending on the column location of the clicked Oval, the associated range of cells is selected. Sub OvalColor() Dim intOval As Integer With ActiveSheet For intOval = 1 To 4 .Shapes("Oval " & intOval).Fill.ForeColor.RGB = RGB(255, 255, 0) Next intOval With .Shapes(Application.Caller) .Fill.ForeColor.RGB = RGB(255, 0, 0) With .TopLeftCell Range(Cells(5, .Column), Cells(22, .Column + 2)).Select End With End With End With End Sub Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Macros Events Classes API, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Pictures Shapes Graphics, Tom Urtis Tom’s Tutorials For Excel: Using the YEARFRAC Function For Fractional Years Between Dates by Tom Urtis 2 Comments ↓ Tom’s Tutorials For Excel: Using the YEARFRAC Function For Fractional Years Between Dates The YEARFRAC function returns a decimalized number as a fraction of year(s) that represents the count of whole days betweeen the start date (“Hire Date” in the pictured example), and the end date (today’s date in cell F1). In its simplest form, YEARFRAC takes two primary arguments of start date and end date. The formula in cell C4 and copied down is =yearfrac(B4,\$F\$1). Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Date Time, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis Tom’s Tutorials For Excel: Converting Formula References From Absolute to Relative by Tom Urtis No Comments ↓ Tom’s Tutorials For Excel: Converting Formula References From Absolute to Relative I previously posted this manual example, and this programmed example for converting formula references from relative to absolute. The manual example showed how the row and / or column references can be kept or omitted. The programmed example showed how to convert all relative row and column references to absolute. This time, here’s a fast and easy way to convert all absolute references to relative references, by eliminating the “\$” character from your formulas. Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Find Replace Substitute, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tips and Tricks, Tom Urtis Remembering Microsoft Excel MVP Nate Oliver, One Year Ago by Tom Urtis 4 Comments ↓ Remembering Microsoft Excel MVP Nate Oliver, One Year Ago. December 4, 2012 marks the one year anniversary for the sad loss of personal friend and Excel MVP Nate Oliver. This is my blog entry from that weekend after learning of Nate’s sudden passing. During this past year, Nate’s name has come up countless times in Excel and Access forums by friends and Office users. Some posts were technical questions, redirected to an answer Nate has already provided. Other posts were simply made to recall Nate’s name, missing him in absence. While I was at the 2012 MVP Summit last February, many MVPs, some of whom I’d never met of technologies other than Excel or Access, raised Nate’s name in conversation and fond memory. I took this video on February 27, 2012 at the Rock Bottom Brewery in Bellevue, Washington, where Excel and Access MVPs had convened after hours, during the week of the Microsoft MVP Summit. Access MVP Duane Hookom recounts Nate’s memorial service that was held two months earlier in Minnesota, with the MVP award representation of lapel pins, guestbook insignias, and Nate’s MVP trophy. Duane also shared a picture collage of Nate, and led a toast in Nate’s honor. Meanwhile, Nate’s Facebook page remains as active as ever. And there have been several more tributes to Nate. Everyone is welcome and encouraged to express your comments at the bottom of my page here, or on the pages at these tribute links: Crystal Long’s UtterAccess forum with links to pictures. Bill Jelen’s MrExcel forum. Microsoft Office MVP Round Up, special edition. Microsoft Office Excel Blog. Posted in MVP Tom’s Tutorials For Excel: Shortcuts to Select the Next, Many, or All Sheets by Tom Urtis No Comments ↓ Tom’s Tutorials For Excel: Shortcuts to Select the Next, Many, or All Sheets Here are a few shortcut tips for selecting worksheets. Pressing the Ctrl+PageDown keys will select the next (to the right) visible sheet. Pressing the Ctrl+PageUp keys will select the previous (to the left) visible sheet. To select individual sheets as a group, click their tab while pressing the Ctrl key. All worksheets can be selected as a group by either of two ways: • Select the first sheet tab, then click the last sheet tab while pressing the Shift key. or • Right-click on any sheet tab, and left-click on Select All Sheets. Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Keyboard Mouse, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tips and Tricks, Tom Urtis, Worksheets Workbooks Tom’s Tutorials For Excel: Formatting Dates as Past or Future by Tom Urtis 2 Comments ↓ Tom’s Tutorials For Excel: Formatting Dates as Past or Future Here’s how you can mark dates in the past and/or in the future, based on some time interval such as less than or more than 30 days from today’s date. For example, in the first picture, today’s date is held in cell E2 using the TODAY function. Conditional Formatting in column C colors dates more than 30 days ago as blue, and more than 30 days from today as yellow. To do this, select the cells of interest, in this case range C5:C15. Then, press Alt+O+D on your keyboard to show the Conditional Formatting dialog box. If you are using Excel version 2003 or before, the Conditional Formatting dialog box looks like the next picture, where you can enter the two formula rules for past and future time. The formula for dates more than 30 days ago is: =AND(LEN(C5)>0,C5<\$E\$2-30) The formula for dates more than 30 days in the future is: =AND(LEN(C5)>0,C5>\$E\$2+30) Two points about these formulas: • The LEN function keeps empty cells uncolored, which Excel would interpret as a date. • Cell E2 houses today's date and is an efficient reference for the Conditional Formatting formulas, rather than using the TODAY function in all the formatted cells' formula rules. Also, if you change your base time element, for example from today to yesterday, you only need to change the formula in cell E2 from =TODAY() to =TODAY()-1. If you are using Excel version 2007, 2010, or 2013, you'll first see the Rules Manager dialog box. Click the New Rule button as indicated. Go ahead and enter the same formulas as shown above for Excel versions up to 2003: The formula for dates more than 30 days ago is: =AND(LEN(C5)>0,C5<\$E\$2-30) The formula for dates more than 30 days in the future is: =AND(LEN(C5)>0,C5>\$E\$2+30) But wait! What if you just want to use one color? Fair question! You might not care about seeing a visual difference between 30+ days ago and 30+ days from now, but you would want to see the visual indication of dates that are either 30+ days past or present. For this, you only need one color, which means means you only need one formula rule: =AND(LEN(C5)>0,OR(C5<\$E\$2-30,C5>\$E\$2+30)) In Excel versions 2003 or before, the Conditional Formatting dialog box looks like this: In Excel versions 2007 or after, the Conditional Formatting dialog box looks like this: Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Conditional Formatting, Data Science, Date Time, Excel Expert, Excel Guru, Formats Fonts Styles Colors, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis Tom’s Tutorials For Excel: Using VLOOKUP for Reverse Lookups by Tom Urtis 30 Comments ↓ Tom’s Tutorials For Excel: Using VLOOKUP for Reverse Lookups I previously posted this example of a reverse lookup, using the INDEX and MATCH functions. The VLOOKUP function is commonly used with a table of data where the item being looked up is in a column to the left of the column holding the corresponding item you want to return, for example: When you need to return a value from a column to the left of where your criteria is listed in the table, the VLOOKUP function can be used with an array constant (explained here), for example: =VLOOKUP(I2,IF({1,0,0},C2:C13,A2:A13),3,0) If your table has many columns between the lookup column and the corresponding item’s column, this formula from fellow Excel MVP Bob Umlas uses the more efficient CHOOSE function: =VLOOKUP(I2,CHOOSE({1,2},G2:G13,A2:A13),2,0) Here’s some info about why the formula with the array constant works. Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis Tom’s Tutorials For Excel: Listing Subfolders by Tom Urtis 2 Comments ↓ Tom’s Tutorials For Excel: Listing Subfolders I previously posted this example to list files kept in a parent folder. Today’s example shows how you can list the subfolder names belonging to a parent folder. Here’s the macro that does this: Sub ListSubfolders() 'Declare and define variables. Dim SourcePath As String, NextRow As Long Dim objFSO As Object, objFolder As Object, objSubfolder As Object SourcePath = "C:\Drivers\" Set objFSO = CreateObject("Scripting.FileSystemObject") 'Set the folder object associated with your directory path. 'Halt the macro if the directory folder does not exist. On Error Resume Next Set objFolder = objFSO.GetFolder(SourcePath) If Err.Number <> 0 Then Err.Clear MsgBox "This parent folder does not exist:" & vbCrLf & _ SourcePath & vbCrLf & _ "Cannot continue.", 16, "No such animal." Exit Sub End If 'Define the NextRow variable as row 2. 'Row 1 will contain a header label. NextRow = 2 'Turn off screen updating. Application.ScreenUpdating = False 'The subfolder names will be listed in column A. 'Clear column A to start with a clean column. Columns(1).Clear 'Place a header label in cell A1. Range("A1").Value = "Subfolders in " & SourcePath 'Loop through the subfolders in the primary folder 'to list its subfolder names. For Each objSubfolder In objFolder.SubFolders Cells(NextRow, 1).Value = objSubfolder.Name NextRow = NextRow + 1 Next objSubfolder 'Release the Objects variables from memory. Set objFSO = Nothing Set objFolder = Nothing Set objSubfolder = Nothing 'For easier readability, autofit column A. Columns(1).AutoFit 'Turn ScreenUpdating back on. Application.ScreenUpdating = True End Sub Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Macros Events Classes API, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis, Worksheets Workbooks Tom’s Tutorials For Excel: Returning text with your formula’s numeric results by Tom Urtis No Comments ↓ Tom’s Tutorials For Excel: Returning text with your formula’s numeric results I previously posted this example with text, and this example with dates, to show how a custom format can display text in cells without affecting the actual underlying values of those cells. Today’s example shows how text can be displayed in a cell that returns a numeric result, while keeping that cell’s returned numeric value as a true, unaffected number. Recall, formatting a cell only changes the appearance of the cell’s contents. The cell’s actual value remains unchanged, be it a number, text, or a boolean (True or False) value. The first picture is a Before and After comparison with a custom format in cell F7. To make this happen, select your cell and press the Alt+O+E keys. The Format Cells dialog box will appear. • Select the Number tab. • In the Category list, select Custom. • In the Type field, enter "Total Income: " #,### and click OK. Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Formats Fonts Styles Colors, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Numbers and Decimals, Text and Constants, Tom Urtis Tom’s Tutorials For Excel: Listing Your Excel Files From a Folder by Tom Urtis 7 Comments ↓ Tom’s Tutorials For Excel: Listing Your Excel Files From a Folder Here’s a macro to list the names of all your Excel workbooks from a specified folder into column A of your worksheet. In the first picture, you see several different types of files in the selected folder. The following macro will list only the Excel workbook names, as shown in the second picture. To modify the macro to list, say, only the PowerPoint files, change ".xls" to ".ppt". If you want to list all files of all types, simply delete or comment out the lines If InStr(objFile.Name, ".xls") > 0 Then and the End If statement three lines below that. Here’s the macro, which works in all version of Excel. Sub ListExcelFiles() 'Declare and define variables. Dim SourcePath As String, NextRow As Long Dim objFSO As Object, objFolder As Object, objFile As Object SourcePath = "C:\Your\File\Path\" Set objFSO = CreateObject("Scripting.FileSystemObject") 'Set the folder object associated with your directory path. 'Halt the macro if the directory folder does not exist. On Error Resume Next Set objFolder = objFSO.GetFolder(SourcePath) If Err.Number <> 0 Then Err.Clear MsgBox "This path does not exist:" & vbCrLf & _ SourcePath & vbCrLf & _ "Cannot continue.", 16, "No such animal." Exit Sub End If 'Define the NextRow variable as row 2. 'Row 1 will contain a header label. NextRow = 2 'The workbook names will be listed in column A. 'Clear column A to start with a clean column. Columns(1).Clear 'Place a header label in cell A1. Range("A1").Value = "Excel files in the path " & SourcePath 'Loop through the Files in the source directory folder 'to list only the Excel file names. For Each objFile In objFolder.Files If InStr(objFile.Name, ".xls") > 0 Then Cells(NextRow, 1).Value = objFile.Name NextRow = NextRow + 1 End If Next objFile 'Release the Objects variables from memory. Set objFSO = Nothing Set objFolder = Nothing Set objFile = Nothing 'For easier readability, autofit column A. Columns(1).AutoFit 'Optional, sort the list. Range("A1").CurrentRegion.Sort _ Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes End Sub Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Macros Events Classes API, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis, Worksheets Workbooks Tom’s Tutorials For Excel: Avoiding the #DIV/O! (Cannot Divide by Zero) Error by Tom Urtis 1 Comment ↓ Tom’s Tutorials For Excel: Avoiding the #DIV/O! (Cannot Divide by Zero) Error I previously posted this example with the SUMIF function for bypassing the #DIV/O! error. Some errors are unavoidable, depending on the nature of your project. You probably know about the ISERROR function, but ordinarily you shouldn’t use a catch-all error function like that. Some errors, such as #REF!, #N/A, or #NAME? are invaluable for identifying other kinds of errors which, if undetected, will destroy the integrity of your workbook’s calculations without you realizing it. The simple formula =B4/C4 in the picture on the left returns that familiar (and ugly-looking) #DIV/0! error when a zero or empty cell is the denominator in a division formula. The alternative formula =IF(C4,B4/C4,"") in the picture on the right avoids the visible #DIV/0! error, without trying to catch every error under the sun. You can replace the null string with text if you prefer, such as “No prior years”, but the idea is to have a pleasant-looking worksheet for users who’d rather see something meaningful in a cell, or nothing at all, instead of the #DIV/0! error. Note that this =IF(C4,B4/C4,"") syntax will work in all versions of Excel. Starting with version 2007, you can also use the =IFERROR function, for example =IFERROR(B4/C4,""). Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Numbers and Decimals, Tom Urtis Tom’s Tutorials For Excel: Highlighting the Current and Prior Selected Cells. by Tom Urtis No Comments ↓ Tom’s Tutorials For Excel: Highlighting the Current and Prior Selected Cells. I previously posted this example for highlighting the selected cell. Today, here is how you can highlight not only the current cell, but the cell you’d selected before you selected your current cell. To make it easy to distinguish between the two cells, the currently selected cell is colored cyan, and the prior selected cell is colored magenta. Take a look at the next two pictures. In the first picture, cell C5 is the active (currently selected) cell. You can tell by its cyan color, and also you can see its address in the address bar. Before this picture was taken, cell H12 had been selected, evidenced by its magenta color. This next picture shows that the currently selected cell is L18, colored cyan. Notice that now, cell C5, which was selected before as seen in the first picture, is colored magenta. Here is the code that accomplishes this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = 0 Static PriorCell As Range If Not PriorCell Is Nothing Then PriorCell.Interior.Color = vbMagenta Target.Interior.Color = vbCyan Set PriorCell = Target End Sub Here are the steps to install the code: Step 1 Step 2 Copy the above code and paste it into the worksheet module where shown. Step 3 Press Alt+Q on your keyboard to return to your worksheet. Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Formats Fonts Styles Colors, Macros Events Classes API, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis Tom’s Tutorials For Excel: Toggling a formula for relative or absolute references by Tom Urtis 1 Comment ↓ Tom’s Tutorials For Excel: Toggling a formula for relative or absolute references I previously posted this macro example for converting a bunch of formula-containing cells all at once, from relative to absolute references. Alternatively, you can quickly toggle a single formula’s row and/or column references for relative and absolute with an easy keyboard shortcut. There’s no VBA or programming involved. Just double-click the cell, or select the cell and hit the F2 key to edit the cell… …then hit the F4 key to toggle for relative all; absolute row; absolute column; or absolute all. In this example, cell D15 is selected, which is averaging a few ranges. With cell D15 selected, hit the F2 key, or double-click the cell to go into Edit mode. I selected all the range arguments between the parentheses. You don’t need to select all range arguments, just the one(s) you want to toggle. With the original formula having all relative references, hitting the F4 key the first time changes all selected row and column references to absolute. Notice that the changes are concurrently reflected in the formula bar. The second time you hit F4, the column references are relative and the row references are absolute. The third time you hit F4, the column references are absolute and the row references are relative. The fourth time you hit F4, you have completed the toggle cycle and started a new one, right back where you started in this example, with all range references being relative as they originally were. Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Formulas Functions, Keyboard Mouse, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tips and Tricks, Tom Urtis Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets by Tom Urtis 107 Comments ↓ Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets Here’s how one formula can return the value of the same cell address from multiple worksheets. In the picture, you have an expense workbook with five worksheets. Four of the worksheets (named Sheet2, Sheet3, Sheet4, and Sheet5) are set up the same way, with their regional total expense amount in cell B12. On the Summary sheet of your workbook is “B12” (without the quotes) in some cell, for this example cell E1 as seen in the following picture. Now, using the INDIRECT function to refer to that cell value in E1, and the ROW function to help refer to each of the four source sheet names, you can enter this formula as seen in cell B3… =INDIRECT("'Sheet"&ROW()-1&"'!"&\$E\$1) …and copy it down as needed. This returns the value of cell B12 in each worksheet. The trick is simply to plan ahead when you design your workbook to allow for a single formula to do the work of many formulas. And the beauty of this system is, if you change the location of the Total cell on the regional worksheets, such as if another expense item is added, simply modify cell E1 on the Summary sheet for that new Total cell address. Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis, Worksheets Workbooks ‹ Older posts Newer posts › ```
``` CATEGORIESActiveX / Form Controls (10)Array Formulas (27)Cells Ranges Lists (122)Charts and Chart Sheets (1)Comments / Screen Tips (9)Conditional Formatting (15)Cut Copy Paste Clear Fill (16)Data validation (12)Date Time (53)Duplicates Uniques (13)Filter Subtotal Group (9)Find Replace Substitute (18)Formats Fonts Styles Colors (49)Formulas Functions (152)Keyboard Mouse (35)Links Hyperlinks Web Email (12)Macros Events Classes API (110)Numbers and Decimals (49)Pictures Shapes Graphics (4)Pivot Tables / Pivot Charts (10)Printing Page Setup (7)Sort (7)Text and Constants (19)Tips and Tricks (55)User-Defined Functions (6)UserForms (10)Word Outlook Access PPT (8)Worksheets Workbooks (60) Tweets by @TomUrtis !function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0],p=/^http:/.test(d.location)?'http':'https';if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src=p+"://platform.twitter.com/widgets.js";fjs.parentNode.insertBefore(js,fjs);}}(document,"script","twitter-wjs"); ```