Blog Archives

Tom’s Tutorials For Excel: Knowing Any Workbook’s Last Saved Date and Time

You can get the last saved date and time for any workbook whether it is open or closed. In Step 3, for demonstration purposes in the Immediate window, I preceded that line of code with the then-current date and time. In the following pictures, Book1 is the only workbook open in the Visual Basic Editor’s Project window.

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: 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: Seeing Values and Formulas on the Same Spreadsheet at the Same Time

Tom’s Tutorials For Excel: Seeing Values and Formulas on the Same Spreadsheet at the Same Time

Did you ever want to watch your spreadsheet in two separate windows in real time, where in one window you can see its values, and in the other window you can see its formulas?

Read more ›

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

Tom’s Tutorials For Excel: Zooming In and Out With Your Mouse Wheel

Tom’s Tutorials For Excel: Zooming In and Out With Your Mouse Wheel

You can press the Ctrl key on your keyboard while turning your mouse wheel forward to zoom in, or backward to zoom out.

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: “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: 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: History Lesson — A Reserved Sheet Name

Tom’s Tutorials For Excel: History Lesson — A Reserved Sheet Name

If you try to rename an Excel worksheet or chart sheet as History, you’ll be stopped in your tracks, as the following picture sequence shows.

The question is,

Read more ›

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

Tom’s Tutorials For Excel: Going Home — Using the Home shortcut key

Tom’s Tutorials For Excel: Going Home — Using the Home shortcut key

The keyboard’s Home key by itself takes you to the cell in column A of your active cell’s row, or with Ctrl+Home takes you to cell A1. If Freeze Panes is being used, Home takes you to the Freeze Panes column of the active cell’s row,

Read more ›

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

Tom’s Tutorials For Excel: Entering Fractions In Your Text

Tom’s Tutorials For Excel: Entering Fractions In Your Text

While it’s true that you can insert fraction 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 three examples that show how you can use the keyboard to enter the common fractions for one-fourth,

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

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

Read more ›

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

Tom’s Tutorials For Excel: Customizing Your RightClick Menu to List & Run Macros

Tom’s Tutorials For Excel: Customizing Your RightClick Menu to List & Run Macros

Download this example of a custom right-click menu to list and run your macros.

I received an intriguing request on Twitter from a follower named Brandi Leath,

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: Fill Formula to Last Used Row

Tom’s Tutorials For Excel: Fill Formula to Last Used Row

The picture on the left shows a list of numbers in column A starting in cell A2. You have a formula in cell B2 which you want to quickly copy down to the last used cell in column A.

Read more ›

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

Tom’s Tutorials For Excel: Delete Rows With Blank Cells

Tom’s Tutorials For Excel: Delete Rows With Blank Cells

Sometimes you have a list for which you need to delete rows where a cell is blank in a particular column. If the list is small, you can eyeball each blank cell, select it, and delete the row manually,

Read more ›

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

Tom’s Tutorials For Excel: Show Your Named Ranges

Tom’s Tutorials For Excel: Show Your Named Ranges

If you actually want to see your named ranges labeled on your worksheet, you can set your window’s Zoom to 39% or less. Here’s an example.

In this first picture is a company’s annual budget organized for Income,

Read more ›

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

Tom’s Tutorials for Excel: Selecting a Current Region

Tom’s Tutorials for Excel: Selecting a Current Region

There are times when you will only want to select a particular range of cells on your worksheet, such as a table to be sorted, or from which you will create a pivot table or chart.

Plan ahead by separating that table’s range from the rest of the worksheet’s other data.

Read more ›

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