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,
…
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.
…
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,
…
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.
…
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.
…
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,
…
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.
…
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.
…
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,
…
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,
…
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).
…
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.
…
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,
…
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,
…
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,
…
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.
…
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,
…
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.
…
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,
…
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,
…
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;
…
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.
…
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.
…
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;
…
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.
…
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,
…
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,
…
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,
…
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.
…
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.
…
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,
…
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,
…
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 —
…
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,
…
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.
…
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.
…
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())
…
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,
…
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.
…
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.
…
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,
…
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.
…
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.
…
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.
…
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.
…
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.
…
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,
…
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,
…
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,
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
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 ›
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Comments / Screen Tips, Data Science, Data validation, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis