Blog Archives

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

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,

Read more ›

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

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.

Instead of mouse clicks and menus that are commonly used,

Read more ›

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

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.

Read more ›

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

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

Read more ›

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

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.

Read more ›

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

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.

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

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

Read more ›

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

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,

Read more ›

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

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.

Read more ›

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

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.

Read more ›

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

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.

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

Read more ›

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

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\]

Read more ›

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

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-“;

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

Read more ›

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

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.

Read more ›

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

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,

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: Calculations Using the F9 Key

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

Here are four keystrokes involving the F9 key.

Read more ›

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

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.

Read more ›

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

Tom’s Tutorials For Excel: Inserting a Word Document Onto Your Spreadsheet

Tom’s Tutorials For Excel: Inserting a Word Document Onto Your Spreadsheet

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,

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