Blog Archives

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.

Read more ›

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

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,

Read more ›

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

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.

Read more ›

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

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,

Read more ›

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

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,

Read more ›

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

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.

Read more ›

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

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.

Read more ›

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

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,

Read more ›

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

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,

Read more ›

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

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.

Read more ›

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

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,

Read more ›

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

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.

Read more ›

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

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

TimeDifferences_BeforeFormat_AfterFormat

Select the range,

Read more ›

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

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.

Read more ›

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

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,

Read more ›

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

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,

Read more ›

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

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,

Read more ›

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

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,

Read more ›

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

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,

Read more ›

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

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;

Read more ›

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

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,

Read more ›

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

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.

Read more ›

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

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.

Read more ›

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

Tom’s Tutorials For Excel: Verifying a Time as AM or PM

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.

Read more ›

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

Tom’s Tutorials For Excel: Summing Every Nth Cell

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.

Read more ›

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

Tom’s Tutorials For Excel: Incremental Summing: Every 5 Cells

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;

Read more ›

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

Tom’s Tutorials For Excel: Entering Fractions In Your Text

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,

Read more ›

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

Tom’s Tutorials For Excel: Random Ramblings

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.

Read more ›

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

Tom’s Tutorials For Excel: Entering International Currency Symbols

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.

Read more ›

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

Tom’s Tutorials For Excel: Setting Alignments For Date Left and Time Right in a Single Cell

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

Read more ›

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

Tom’s Tutorials For Excel: Selecting a Large Range Without Scrolling or Dragging.

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.

Read more ›

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

Tom’s Tutorials For Excel: Comparing Lists For Identical Match

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,

Read more ›

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

Tom’s Tutorials For Excel: Repeating Your Last Action FAST! The Magic of the F4 Key.

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,

Read more ›

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

Tom’s Tutorials For Excel: Filling Series For Linear and Growth

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,

Read more ›

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

Tom’s Tutorials For Excel: Extracting the Last Word in a Cell

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,

Read more ›

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

Tom’s Tutorials For Excel: Programming Multiple Drawing Shapes

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,

Read more ›

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

Tom’s Tutorials For Excel: Using the YEARFRAC Function For Fractional Years Between Dates

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

Read more ›

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

Tom’s Tutorials For Excel: Converting Formula References From Absolute to Relative

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.

Read more ›

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

Tom’s Tutorials For Excel: Shortcuts to Select the Next, Many, or All Sheets

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.

Read more ›

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

Tom’s Tutorials For Excel: Formatting Dates as Past or Future

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,

Read more ›

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

Tom’s Tutorials For Excel: Using VLOOKUP for Reverse Lookups

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,

Read more ›

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

Tom’s Tutorials For Excel: Listing Subfolders

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.

Read more ›

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

Tom’s Tutorials For Excel: Returning text with your formula’s numeric results

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.

Read more ›

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

Tom’s Tutorials For Excel: Listing Your Excel Files From a Folder

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.

Read more ›

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

Tom’s Tutorials For Excel: Avoiding the #DIV/O! (Cannot Divide by Zero) Error

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,

Read more ›

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

Tom’s Tutorials For Excel: Highlighting the Current and Prior Selected Cells.

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.

Read more ›

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

Tom’s Tutorials For Excel: Toggling a formula for relative or absolute references

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.

Read more ›

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

Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets

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,

Read more ›

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

Tom’s Tutorials For Excel: Entering New Lines and Bullets in a Cell

Tom’s Tutorials For Excel: Entering New Lines and Bullets in a Cell

Here is how you can enter text on its own line in a single cell. In the picture, some food items have been entered into cell A2. As you can see in the pictures that follow,

Read more ›

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

Tom’s Tutorials For Excel: Deleting Duplicate Records

Tom’s Tutorials For Excel: Deleting Duplicate Records

When you have a list of data, sometimes it is not enough to simply delete rows with duplicated information based only on the items in one column. Multi-column lists can have duplicated records based on the fact that every item in every column of a row’s data matches that of another row’s entire data,

Read more ›

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