Blog Archives

Tom’s Tutorials For Excel: Importing an Access database table into Excel — a Quick Shortcut

Tom’s Tutorials For Excel: Importing an Access database table into Excel — a Quick Shortcut

Here’s a shortcut to manually import an Access database table into your spreadsheet. It’ll come with the alternating shaded rows and field header drop-down arrows, but it’s fast and easy. The first picture shows an access table just for example,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Showing Your Column Headers as Letters or Numbers

You can show your column headers as letters or numbers. Here’s how to change column headers depending on your preferred look.

TTFE027a
TTFE027b
TTFE027c

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Introduced in Version 2016, the “Tell me what you want to do” Field

Introduced in Excel 2016, you can click into the “Tell me what you want to do” field as shown in the picture, or press Alt+Q from your worksheet, to enter a topic for information. This is a fairly intuitive new tool, while saving the interim step of hitting the F1 key for help about a topic.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Customizing the Status Bar

Tom’s Tutorials For Excel: Customizing the Status Bar
You can right-click the status bar for additional edit, formatting, and analysis options.
TTFE0020a
The following picture shows a Before and After comparison of the additional information you can choose for the status bar to show. In this example,

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Using a Ribbon Group’s Dialog Launcher

Tom’s Tutorials For Excel: Using a Ribbon Group’s Dialog Launcher
To see all the options for a Ribbon group, click the dialog launcher in the group’s bottom right corner.
TTFE0018

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Displaying a Column’s Width

Tom’s Tutorials For Excel: Displaying a Column’s Width
You can display a column’s width manually, or in a cell with a User-Defined Function. The first picture shows that when you press and hold your left mouse button between column headers, a column’s width appears in a tooltip.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Using the Home key with Scroll Lock

Tom’s Tutorials For Excel: Using the Home key with Scroll Lock
Here are a few navigation tips using the Home key, with or without the Ctrl key, with or without Scroll Lock activated, and with Freeze Panes on or off.
TTFE0011a
TTFE0011b

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Using Undo For Several Actions at Once

Tom’s Tutorials For Excel: Using Undo For Several Actions at Once
You can Undo up to 100 previous actions on your workbook at the same time. Clicking the drop-down arrow next to the Undo button shows the list of your most recent actions, where you can select the point at which to undo the action(s) you want to reverse.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Bypassing the Start screen when opening Excel

Tom’s Tutorials For Excel: Bypassing the Start screen when opening Excel
When you open Excel, here is how to bypass the Start screen when you prefer to see a new workbook instead.
TTFE4a
TTFE4b
TTFE4c

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Opening Excel in a New Instance

Tom’s Tutorials For Excel: Opening Excel in a New Instance
In Excel versions 2013 and later, new workbooks open in the same instance of Excel. Here are 2 options to open your workbook in its own instance.
Option 1
TET1a
TET1b
Option 2
TET1c
TET1d
TET1e

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Formatting 0 to Look Like “Zero”

Tom’s Tutorials For Excel: Formatting 0 to Look Like “Zero”

There are times when you want to see a number as text in order to visually set it apart from other numbers, while maintaining its value as a number. A custom format can accomplish this, because formatting a cell’s value only affects what you see,

Read more ›

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

Tom’s Tutorials For Excel: Toggling to Show or Hide Your Group and Outline Buttons

Tom’s Tutorials For Excel: Toggling to Show or Hide Your Group and Outline Buttons

When you have a worksheet with rows and/or columns that have been grouped…

…you can press your keyboard’s Ctrl+8 keys…

…to quickly and easily toggle to show or hide your Group and Outline buttons.

Read more ›

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

Tom’s Tutorials For Excel: Modifying Your List of Recently Viewed Files

Tom’s Tutorials For Excel: Modifying Your List of Recently Viewed Files

You may know that the keyboard shortcut Alt+F reveals your list of most recently used files.

You can press Alt+T+O to show the dialog box to modify your list of viewable files.

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: Entering an Ampersand in Your Header or Footer

Tom’s Tutorials For Excel: Entering an Ampersand in Your Header or Footer

If you’ve ever wondered why a custom header or footer does not show an ampersand (&) when you want to show it, there’s a small trick to making that happen. All it takes is two ampersands in succession,

Read more ›

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

Tom’s Tutorials For Excel: “Did You Know…”

Tom’s Tutorials For Excel: “Did You Know…”

Did you know:
You can insert a new worksheet by pressing Shift+F11. Immediately thereafter, to continue inserting as many new worksheets as you want, press the F4 key.

Did you know:
You can enter any value in a cell,

Read more ›

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

Tom’s Tutorials For Excel: Converting a trailing negative sign to a real negative number

Tom’s Tutorials For Excel: Converting a trailing negative sign to a real negative number

As any Excel developer or workplace guru knows, raw downloads from external storage warehouses can throw some strange data at you. An example is a list of numbers, some of which are meant to be negative but are actually text values because of a trailing negative sign,

Read more ›

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

Tom’s Tutorials For Excel: Using AutoCorrect for Faster Data Entry

Tom’s Tutorials For Excel: Using AutoCorrect for Faster Data Entry

Here’s a tip for shortening the effort of entering recurring lengthy text.

Suppose there are words or phrases you need to type often in your spreadsheets, such as boilerplate legal text, or the name of your company.

Read more ›

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

Tom’s Tutorials For Excel: Making Changes on All Sheets at Once

Tom’s Tutorials For Excel: Making Changes on All Sheets at Once

Here’s a time-saving tip for newcomers or casual users of Excel. When you need to make the same changes on all your worksheets, you only need to make the changes one time and still have every worksheet show those changes.

Read more ›

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

Tom’s Tutorials For Excel: Careful! That was *Alt*+F11 !

Tom’s Tutorials For Excel: Careful! That was *Alt*+F11 !

From your active workbook, a fast and easy way to get into the Visual Basic Editor is to press Alt+F11 on your keyboard. You can do this from any worksheet.

Careful, that was *Alt*+F11! The Ctrl key is commonly used in conjunction with other keys for keyboard shortcuts.

Read more ›

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

Tom’s Tutorials For Excel: Entering International Currency Symbols

Tom’s Tutorials For Excel: Entering International Currency Symbols

While it’s true that you can insert currency symbols from the Symbol dialog box into your cell’s text, it’s faster and easier to produce those symbols by using the keyboard as you are typing your text.

Here are four examples to show how you can use the keyboard to enter currency symbols in your non-numeric text entries.

Read more ›

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

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

Read more ›

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

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 —

Read more ›

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

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.

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

Read more ›

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

Tom’s Tutorials For Excel: Toggling the GETPIVOTDATA Function On and Off

Tom’s Tutorials For Excel: Toggling the GETPIVOTDATA Function On and Off

When you are doing calculations in cells outside of a pivot table that involve cells inside of (that is, belonging to) a pivot table, sometimes you want just the pivot table’s cell value, and other times you want the relative position and content of the cell with the GETPIVOTDATA function.

Read more ›

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

Tom’s Tutorials For Excel: Filling Empty Cells With Value From Above

Tom’s Tips For Excel Videos: Filling Empty Cells With Value From Above

Sometimes you get data in a column which has a lot of blank cells that represent whatever text was in the last filled cell above them.

When you need to fill in hundreds or thousands of empty cells,

Read more ›

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

Tom’s Tutorials For Excel: Quickly Summing a Table of Numbers by Rows and Columns

Tom’s Tutorials For Excel: Quickly Summing a Table of Numbers by Rows and Columns

Here are a couple of shortcuts to quickly sum the totals for rows and columns in a data table.

The first pair of pictures shows what you have and what you want.

Read more ›

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

Tom’s Tutorials For Excel: Selecting Functions From the SUM icon’s Drop-Down List

Tom’s Tutorials For Excel: Selecting Functions From the SUM icon’s Drop-Down List

As the pictures show, you can click the small drop-down arrow to the immediate right of the SUM icon for a list of other functions to select. As the “Version 2003” picture shows, clicking the More Functions item shows the Insert Function dialog box.

Read more ›

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

Tom’s Tutorials For Excel: Emptying the Recycle Bin

Tom’s Tutorials For Excel: Emptying the Recycle Bin

This macro with API empties the Recycle Bin.

Recycle
In a new fresh standard module:

Declare Function EmptyRecycleBin _
Lib “shell32.dll” Alias “SHEmptyRecycleBinA” _
(ByVal hwnd As Long, _
ByVal pszRootPath As String,

Read more ›

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

Tom’s Tutorials For Excel: Creating a Right Click Event For ActiveX Controls

Tom’s Tutorials For Excel: Creating a Right Click Event For ActiveX Controls

Free and safe downloadable workbook example.

There is no built-in menu for the right-click event of ActiveX objects. Here is a utility for that, using a CommandButton as an example.

Read more ›

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

Tom’s Tutorials For Excel: Updating Links (or Not)

Tom’s Tutorials For Excel: Updating Links (or Not)

You’ve no doubt seen this pop-up message upon opening a workbook:

To automatically update links sans VBA, if the work being done is always on your computer…
…In versions 2003 or before: Tools >

Read more ›

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

Tom’s Tutorials For Excel: VBA Help — Usually Right, Sometimes Wrong

Tom’s Tutorials For Excel: VBA Help — Usually Right, Sometimes Wrong

The above picture shows a simple macro that calls a Message Box to display a tab name by referring to the active sheet’s CodeName property. Suppose you’ve never before heard the term CodeName,

Read more ›

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

Tom’s Tutorials For Excel: Finding and Deleting Phantom Links

Tom’s Tutorials For Excel: Finding and Deleting Phantom Links

If you’ve ever wondered why this message pops up after you are sure you’ve deleted all your workbook’s links, well, join the club. It’s a common problem because some links can be difficult to find.

In my 20 years working with Excel,

Read more ›

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

Tom’s Tutorials For Excel: Enter Zip Codes and Social Security Numbers With Leading Zeros

Tom’s Tutorials For Excel: Enter Zip Codes and Social Security Numbers With Leading Zeros

When you enter numbers that start with a leading zero such as Social Security Numbers or United States zip codes, you must stop Excel from automatically hiding the leading zero, so your full number including the leading zero is displayed.

Read more ›

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