Blog Archives

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,

Read more ›

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

Read more ›

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,

Read more ›

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.

Read more ›

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.

Read more ›

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

Tom’s Tutorials For Excel: Finding the Absolute Row Number of an Item in a List

Tom’s Tutorials For Excel: Finding the Absolute Row Number of an Item in a List

In yesterday’s tutorial, I posted this example to return the relative row number of an item in a list.

Today’s example offers a formula to return the actual row number of an item in a given range,

Read more ›

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

Tom’s Tutorials For Excel: Finding the Relative Position of an Item in a List or Table

Tom’s Tutorials For Excel: Finding the Relative Position of an Item in a List or Table

You’ll sometimes need to know the relative position, such as the relative row in a list or table of an item. This is usually different than the item’s actual row on the Excel spreadsheet grid.

Read more ›

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

Tom’s Tutorials For Excel: Using Data Validation to Disallow Entry of Item in a List

Tom’s Tutorials For Excel: Using Data Validation to Disallow Entry of Item in a List

Here’s an example of using Data Validation to NOT allow a particular data item entry.

Suppose you want to insure that anything can be entered into a cell, EXCEPT for certain items you specify.

Read more ›

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

Tom’s Tutorials For Excel: Using Data Validation to Force a Decimalized Numeric Entry

Tom’s Tutorials For Excel: Using Data Validation to Force a Decimalized Numeric Entry

Data Validation is an excellent way to control data entry to meet a certain condition.

Suppose you want to insure that numbers entered in the yellow cells are OK for decimals,

Read more ›

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

Tom’s Tutorials For Excel: Going To the Precedent Cell with a Keyboard Shortcut

Tom’s Tutorials For Excel: Going To the Precedent Cell with a Keyboard Shortcut

Here’s a cool tip for the keyboard shortcut Ctrl+[ which takes you from the formula cell you are on, to the precedent cell (if there is one) of that formula.

In this first example,

Read more ›

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

Tom’s Tutorials For Excel: Doing a Lookup for Last Number or Last Text in a List

Tom’s Tutorials For Excel: Doing a Lookup for Last Number or Last Text in a List

Here is how you can look up items in one column, based on the last cell in a different column of that list which contains a number or text.

The formula in cell E2 is =INDEX(A3:C17,MATCH(9.99999999999999E+307,A3:A17,1),3).

Read more ›

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

Tom’s Tutorials For Excel: Reverse Lookup of nth Highest and nth Lowest Numbers

Tom’s Tutorials For Excel: Reverse Lookup of nth Highest and nth Lowest Numbers

Here are several examples rolled into one screen shot that show how to:
• Return the minimum and maximum numbers in a list.
• Return the 2nd, 3rd, and nth highest and lowest numbers in a list.

Read more ›

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

Tom’s Tutorials For Excel: Selecting All Cells With Comments or Data Validation

Tom’s Tutorials For Excel: Selecting All Cells With Comments or Data Validation

I previously posted this example of selecting only constants or formulas.

You can do the same with cells that contain comments or data validation.

Select the range of interest.

Read more ›

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

Tom’s Tutorials For Excel: Entering a Function’s Arguments Tooltip in a Cell

Tom’s Tutorials For Excel: Entering a Function’s Arguments Tooltip in a Cell

You can press Ctrl+Shift+A in mid-entry of your worksheet functions to show their tooltips’ argument text directly in your cell, and type your function arguments right over those text tips.

Step 1
Start by entering the function name,

Read more ›

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

Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List

Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List

Here is a formula that returns the address of the cell in a list that contains a particular item. In the picture, cell D2 contains a Widget Stock Number, and cell E2 contains this array formula to return the item’s address:
=ADDRESS(MIN(IF(A3:A15=D2,ROW(3:15))),1)

Recall,

Read more ›

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

Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

You can use the SUBTOTAL function to look up the minimum or maximum number in a filtered list.

In the picture, the formula in cell B1 that returns Sue Flay’s minimum sales number is
=SUBTOTAL(5,B5:B100)

The formula in cell B2 that returns Sue Flay’s maximum sales number is
=SUBTOTAL(4,B5:B100)

The first argument for SUBTOTAL is Function_Num,

Read more ›

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

Tom’s Tutorials For Excel: Linking a Drawing Object to a Cell

Tom’s Tutorials For Excel: Linking a Drawing Object to a Cell

Here’s how you can link a cell’s value to be displayed inside a drawing object. In this example, a Text Box is being used, but this method will also work for Rectangles and other draw-able Forms shapes.

Read more ›

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

Tom’s Tutorials For Excel: Ranking a List in Random Order

Tom’s Tutorials For Excel: Ranking a List in Random Order

Here’s an example of setting up your worksheet to randomly rank a list of items. In this first set of pictures, the list of names is randomly ranked three times, simply by hitting the F2 key on any cell in the worksheet,

Read more ›

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

Tom’s Tutorials For Excel: Sorting a List in Random Order

Tom’s Tutorials For Excel: Sorting a List in Random Order

Here’s how you can sort a table in a random order, instead of strictly as ascending or descending.

In the example, a company has 10 coveted parking spaces that are nearer to the office building.

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

Tom’s Tutorials For Excel: Averaging the Four Highest Numbers in a List

Tom’s Tutorials For Excel: Averaging the Four Highest Numbers in a List

I recently posted this example of an array constant to avoid a lookup table for VLOOKUP.

Today, an array constant is employed to collect — and then calculate the average of —

Read more ›

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

Tom’s Tutorials For Excel: Averaging Only Positive Numbers, Ignoring Blanks and Text

Tom’s Tutorials For Excel: Averaging Only Positive Numbers, Ignoring Blanks and Text

When you have a mixed list of cells that contain negative numbers, positive numbers, text, and blanks, here is a formula to calculate the average for only the positive numbers.

In the picture,

Read more ›

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

Tom’s Tutorials For Excel: Formatting Cell Contents as Invisible

Tom’s Tutorials For Excel: Formatting Cell Contents as Invisible

Here’s how you can make a cell’s contents be invisible, while keeping the cell’s rows and/or columns unhidden. In the following pair of pictures, employee salaries are in column F, but you want them hidden while column F remains visible.

Read more ›

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

Tom’s Tutorials For Excel: Showing Your Named Ranges At 39% Zoom

Tom’s Tutorials For Excel: Showing Your Named Ranges At 39% Zoom

Here’s a tip to get a bird’s eye view of the named ranges on your worksheet.

All you need to do is set your worksheet’s Zoom to 39% or less.

Read more ›

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

Tom’s Tutorials For Excel: Flipping a List

Tom’s Tutorials For Excel: Flipping a List

If you want to flip a list so the order of items is vertically reversed, here’s one way to do that using this formula in the pictured example:
=INDEX(A:A,COUNTA(A:A)+1-ROW())

Read more ›

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

Tom’s Tutorials For Excel: Using the Keyboard to Select a Row, Column, or All Cells

Tom’s Tutorials For Excel: Using the Keyboard to Select a Row, Column, or All Cells

Here are a few keyboard shortcuts for selecting your worksheet cells.

To select the entire row of your active cell, press the Shift+Spacebar keys.

To select the entire column of your active cell,

Read more ›

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

Tom’s Tutorials For Excel: Identifying Your Active Window’s Top Left Cell

Tom’s Tutorials For Excel: Identifying Your Active Window’s Top Left Cell

Here are a few macros that identify the top left cell of your active window’s spreadsheet.

The first example relates to this picture, where cell HX63524 happens to be the top left cell in the active window.

Read more ›

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

Tom’s Tutorials For Excel: Selecting Only Constants or Formulas

Tom’s Tutorials For Excel: Selecting Only Constants or Formulas

Sometimes you want to do something — such as format, delete or edit — all cells containing constants or formulas at the same time. Here’s how to select all such cells so you can thereafter do to them what you want.

Read more ›

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

Tom’s Tutorials For Excel: Updating a Comment to List Unique Items

Tom’s Tutorials For Excel: Updating a Comment to List Unique Items

Here is how you can automatically update a comment that shows unique items in sorted order from the larger worksheet list, whenever a new unique item is added to that list in the worksheet.

In the pictured example,

Read more ›

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

Tom’s Tutorials For Excel: Using an InputBox to Find a Row, Column, and Address

Tom’s Tutorials For Excel: Using an InputBox to Find a Row, Column, and Address

This blog entry shows many examples, such as…
• Include a default entry in an InputBox.
• Trap the Cancel and OK buttons in an InputBox.
• Test to find an InputBox entry in a worksheet cell.

Read more ›

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

Tom’s Tutorials For Excel: Summing Varying Ranges Along One Row or at Each Next Row

Tom’s Tutorials For Excel: Summing Varying Ranges Along One Row or at Each Next Row

Sometimes you want options in your VBA arsenal for requests to show results in ad hoc fashions.

In the picture, a table has several columns, each containing a varying count of numeric entries.

Read more ›

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

Tom’s Tutorials For Excel: Checkmarking a Cell With Standard and Conditional Formatting

Tom’s Tutorials For Excel: Checkmarking a Cell With Standard and Conditional Formatting

Here’s how you can automatically show a checkmark in a cell, using standard formatting for a font type, and Conditional Formatting to identify when the checkmark exists. There is no programming code involved; it’s all native Excel formatting.

Read more ›

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

Tom’s Tutorials For Excel: Searching a Cell’s Value With Alt and a Mouse Click

Tom’s Video Tutorials For Excel: Searching a Cell’s Value With Alt and a Mouse Click

Here’s a video where I show how you can turn a cell’s contents into keywords for an online search, by pressing the Alt key while clicking your mouse on that cell.

Read more ›

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

Tom’s Tutorials For Excel: Inserting a Picture at a Specific Cell on Multiple Worksheets

Tom’s Tutorials For Excel: Inserting a Picture at a Specific Cell on Multiple Worksheets

Suppose you have a workbook, with several worksheets that often require your company logo to be inserted at a particular cell’s location. Assuming…
• You want the picture at the top left corner of cell D3.

Read more ›

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

Tom’s Tutorials For Excel: Mousing Over a Cell to Run a Macro

Tom’s Tutorials For Excel: Mousing Over a Cell to Run a Macro

Here is how you can trigger a macro by mousing over a range of cells.

This is accomplished without any assistance whatsoever from ActiveX controls or any embedded objects. The cells being moused over (not selected,

Read more ›

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

Tom’s Tutorials For Excel: Deleting or Keeping Rows With a Multiple Criteria Array

Tom’s Tutorials For Excel: Deleting or Keeping Rows With a Multiple Criteria Array

Here’s a “this way or that way” pair of macros that use an array to hold a set of items to determine which rows you want to keep or delete. In the picture,

Read more ›

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

Tom’s Tutorials For Excel: Concatenating Multiple Items For Unique Partners

Tom’s Tutorials For Excel: Concatenating Multiple Items For Unique Partners

Here’s how you can take several associated items that are located in their own cells, and concatenate them as a single string to be housed into one cell.

In the picture, you see Primary Items in column A,

Read more ›

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