Tom’s Tutorials For Excel: Finding and Replacing Wildcards

Tom’s Tutorials For Excel: Finding and Replacing Wildcards

In this previous Tom’s Tutorial blog post, I showed an example of using wildcards in formulas. Today’s Tutorial shows how to find and replace wildcard characters themselves on your worksheet.

To review, in Excel,

Tom’s Tutorials For Excel: Shortcutting a Cell for PasteSpecial to Values

Tom’s Tutorials For Excel: Shortcutting a Cell for PasteSpecial to Values

Here’s a quick way to use your keyboard for achieving the effect of copying a cell, and pasting it special for values.

Tom’s Tutorials For Excel: Converting Time to Minutes and Seconds

Tom’s Tutorials For Excel: Converting Time to Minutes and Seconds

Here is how you can take an elapsed time originally represented in Hours, Minutes, and Seconds, and show it as just Minutes, or as Minutes and Seconds. For example, the picture shows the top 10 finishing times in column B for runners of the 2012 Boston Marathon.

Tom’s Tutorials For Excel: Shortcut to Activate the Next or Last Open Workbook

Tom’s Tutorials For Excel: Shortcut to Activate the Next or Last Open Workbook

If you have a bunch of workbooks open in the same instance of Excel, and you want to cycle through them in turn to activate the next ones (to the right on your task bar),

Tom’s Tutorials For Excel: Extracting the First Word or String in a Cell

Tom’s Tutorials For Excel: Extracting the First Word or String in a Cell

Here is an example for returning the first word in a cell, with a formula that can be easily modified to return all contents to the left of the first found specified character in a cell.

Tom’s Tutorials For Excel: Summing Numbers While Omitting the Lowest Two

Tom’s Tutorials For Excel: Summing Numbers While Omitting the Lowest Two

I recently posted two examples of array constants:
how to avoid a lookup table for VLOOKUP, and how to average the 4 highest numbers in a list.

Here’s how you can sum all numbers in a list except for the two lowest or the two highest.

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 —

Tom’s Tutorials For Excel: Calculating the Date for Easter

Tom’s Tutorials For Excel: Calculating the Date for Easter

Easter (often referred to as Easter Sunday) is a Christian festival and holiday. Easter always falls on a Sunday, but its date varies depending on the calendar year and the phase of the moon.

Specifically,

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,

Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number

Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number

Most businesses account their annual operations on a January to December schedule. For those businesses, their quarterly calendar looks like this:
Quarter 1: January, February, March.
Quarter 2: April, May, June.
Quarter 3: July,

Tom’s Tutorials For Excel: Hiding Zeroes in Cells

Tom’s Tutorials For Excel: Hiding Zeroes in Cells

Here’s a way to hide zeroes in cells, to improve the readability of your spreadsheet by making only the relevant non-zero numeric values visible.

Select the range of interest, right-click on that selection,

Tom’s Tutorials For Excel: Formatting a Date With Text

Tom’s Tutorials For Excel: Formatting a Date With Text

Here’s how you can enter a date in a cell, and apply a custom date format to make the cell look as if it contains text. You can use that date in math functions elsewhere, as shown below.

Tom’s Tutorials For Excel: Entering Fractions

Tom’s Tutorials For Excel: Entering Fractions

Here’s how to enter a fractional value in a cell, make it look like a fraction in the cell, and be able to work with it mathematically as you would with any number.

Tom’s Tutorials For Excel: AutoFilling Dates For Weekdays Only

Tom’s Tutorials For Excel: AutoFilling Dates For Weekdays Only

Here’s how you can fill in a range of dates that only includes traditional workdays of Monday through Friday, while bypassing the weekend dates for Saturdays and Sundays.

Step 1
You start by entering your beginning date into a cell.

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.

Tom’s Tutorials For Excel: Formatting Numbers in Decimalized Millions

Tom’s Tutorials For Excel: Formatting Numbers in Decimalized Millions

When you have large numbers that you want to display in millions for easier reading, you can custom-format those cells as
#.0,,;#.0,,;0

Tom’s Tutorials For Excel: Formatting a Negative Number In Square Brackets

Tom’s Tutorials For Excel: Formatting a Negative Number In Square Brackets

Here’s a quick method for displaying a negative number in square brackets.
The custom format is 0.00;\[0.00\]

Tom’s Tutorials For Excel: VLOOKUP Without a Lookup Table

Tom’s Tutorials For Excel: VLOOKUP Without a Lookup Table

When using a lookup function such as VLOOKUP, you can avoid a lookup table on a worksheet and insert the lookup elements directly into the formula itself. This is done by using an array constant which is shown by example in the pictured formula
=VLOOKUP(B4,{0,”D”;71,”C-“;72,”C”;76,”C+”;79,”B-“;

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.

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

Tom’s Tutorials For Excel: Forcing entries in UPPER case

Tom’s Tutorials For Excel: Forcing Entries in UPPER Case

Here’s how you can force users to enter all alpha (letter) characters in UPPER case, using Data Validation and no programming code.

To make this happen, press Alt+D+L on your keyboard to show the Data Validation dialog box.

Tom’s Tutorials For Excel: Putting Comments In Formulas With The N Function

Tom’s Tutorials For Excel: Putting Comments In Formulas With The N Function

You can add a text notation to a worksheet formula by using the N function. This is useful when you want to look at a formula and know something about it by reading actual descriptive words.

Tom’s Tutorials For Excel: Entering a Static Date and Time

Tom’s Tutorials For Excel: Entering a Static Date and Time

As you probably know, you can enter =TODAY() and get the current date, or =NOW() and format the cell as a time to get the current time.

Those functions belong to a category of volatile functions,

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,

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.

Tom’s Tutorials For Excel: Calculations Using the F9 Key

Tom’s Tutorials For Excel: Calculations Using the F9 Key

Here are four keystrokes involving the F9 key.

Tom’s Tutorials For Excel: Toggling to Show All Formulas or All Values

Tom’s Tutorials For Excel: Toggling to Show All Formulas or All Values

By pressing the Ctrl+~ keys on your keyboard, you can toggle between showing all the worksheet’s formulas or showing only values.

Here are the simple steps for inserting a Word document directly onto your worksheet.

Step 1
From the Ribbon, click the Insert tab and then click the Object icon.

If you are using Excel’s version 2003 or before,

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.

Tom’s Tutorials For Excel: Populating TextBoxes From Last Row Upwards

Tom’s Tutorials For Excel: Populating TextBoxes From Last Row Upwards

When you have a list and you want to populate userform TextBoxes for, say, the last 10 records of the items in that list, here’s how.

In the above picture,

Tom’s Tutorials For Excel: Doing Math Without Formulas or Programming

Tom’s Tutorials For Excel: Doing Math Without Formulas or Programming

You can copy a helper cell and perform a mathematical operation in the Paste Special dialog box to work with numbers in Excel. There are no formulas or programming code involved — just a few simple mouse clicks.

Tom’s Tutorials For Excel: Showing Excel Help While Entering a Function

Tom’s Tutorials For Excel: Showing Excel Help While Entering a Function

Here’s a tip for showing Excel’s Help window for the function you are in the process of entering.

In the picture, the numbers in column C are wanting to be subtotaled. The SUBTOTAL function has 11 different mathematical argument numbers —

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,

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.

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.

Tom’s Tutorials For Excel: Showing a Progress Bar From a Worksheet Formula

Tom’s Tutorials For Excel: Showing a Progress Bar From a Worksheet Formula

In yesterday’s blog entry, I showed an example of using the Wingdings2 font style with Conditional Formatting to show a checkmark in a cell for completed items. Here’s how you can employ a progress bar using a the REPT function with the Wingdings font style.

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.

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.

Tom’s Tutorials For Excel: Adding Text With a Custom Format

Tom’s Tutorials For Excel: Adding Text With a Custom Format

There are times when you want to append text to a cell’s existing text, without using a helper column with a concatenation formula, and without any programming methods.

As you know, formatting a cell only changes the visual appearance of the cell;

Tom’s Tutorials For Excel: Viewing All Worksheets With One INDIRECT Formula

Tom’s Tutorials For Excel: Viewing All Worksheets With One INDIRECT Formula

Here’s how you can use Data Validation with the INDIRECT function in a single formula to quickly and easily view the contents of other worksheets in your workbook.

Suppose your workbook is the company budget,

Tom’s Tutorials For Excel: Verifying Your Internet Connection

Tom’s Tutorials For Excel: Verifying Your Internet Connection

With the help of an API declaration, here’s a method for verifying if the active workbook’s system is connected to the internet.

Thanks to MS Excel MVP Mike Alexander at Datapig Technologies for his help with this to include wireless connections in the verification process.

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.

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,

Tom’s Tutorials For Excel: Exporting Code to Find and Replace

Tom’s Tutorials For Excel: Exporting Code to Find and Replace

Following up last week’s blog entry about exporting code from a module to a text file, here is how to create a copy of that text file and make changes to the code for Find and Replace.

Tom’s Tutorials For Excel: Exporting VBA Module Code to a Text File

Tom’s Tutorials For Excel: Exporting VBA Module Code to a Text File

Here’s a quick way to export all the VBA code from a workbook module, assuming for example:
• The module of interest is named Module1.
• The text file that will receive this code is named “YourFileName.txt”.

Tom’s Tutorials For Excel: Parsing Data To Create and Populate Separate Workbooks

Tom’s Tutorials For Excel: Parsing Data To Create and Populate Separate Workbooks

Sometimes you need to organize a large table of data by creating and populating individual workbooks based on rows belonging to each primary subject item.

For example, the next picture shows a table of company Stores and their activities.

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,

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,

Tom’s Tutorials For Excel: Getting Unique Items From a List

Tom’s Tutorials For Excel: Getting Unique Items From a List

In the following picture, you see a list of names, many of which are repeated 2, 3, or more times. Here’s how you can create a shorter list to show each unique item only once.

To create a list of unique names in column C based on the list of many repeated names in column A,