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