Blog Archives

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: Copying Your Page Setup to Multiple Worksheets

Tom’s Tutorials For Excel: Copying Your Page Setup to Multiple Worksheets

Establishing your Page Setup preferences can involve a lot of steps. You won’t want to repeat those same steps over and over for each worksheet where you’ll want the same preferences.

To show the Page Setup dialog box…

Read more ›

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

Tom’s Tutorials For Excel: Coloring Your Worksheet Tabs

Tom’s Tutorials For Excel: Coloring Your Worksheet Tabs

Here’s a tip for newcomers to Excel, for the ability to color your worksheet tabs. This feature has been available starting with Excel version 2002.

Right-click the worksheet tab you want to color, and select Tab Color from the pop-up menu.

Read more ›

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

Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name

Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name

Here are two formulas, one to return the active worksheet’s name, and the other to return the active workbook’s full path and name. In each case, please be sure to save the workbook at least once.

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: Listing Column Letters Across and Down

Tom’s Tutorials For Excel: Listing Column Letters Across and Down

Here are formulas to display the column letter in any individual cell, or to list column letters horizontally across a row, or vertically down a column.

As shown in this first picture, you can display any cell’s column letter with the formula
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),”1″,””)

You can use that same formula,

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: Finding and Replacing on All Worksheets

Tom’s Tutorials For Excel: Finding and Replacing on All Worksheets

Sometimes overlooked is Excel’s ability to Find and Replace across all worksheets in a workbook, without needing to select all the worksheets.

• Select any cell in any sheet.

Read more ›

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

Tom’s Tutorials For Excel: Filling Across Worksheets

Tom’s Tutorials For Excel: Filling Across Worksheets

I previously posted this example for making changes on all sheets at once.

Here’s a quick and easy solution when the changes you want to put on all worksheets have already been made to one worksheet.

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: 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: Summing Cells in Even or Odd Numbered Rows Only

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.

Read more ›

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

Tom’s Tutorials For Excel: Summing Every Nth Cell

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.

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

Read more ›

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

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.

Read more ›

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

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.

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: Preventing a Workbook From SaveAs

Tom’s Tutorials For Excel: Preventing a Workbook From SaveAs

When you want to keep the name of your workbook the same, without the ability to save it as another name, here’s an example of how to kinda sorta accomplish that.

“Kinda sorta”? Three things in life are certain: death,

Read more ›

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

Tom’s Tutorials For Excel: Summing Ranges From Multiple Worksheets

Tom’s Tutorials For Excel: Summing Ranges From Multiple Worksheets

Here’s a visual representation of how to compose a formula on one worksheet that performs a mathematical operation from data on several other worksheets. The numbers in cells J5:J11 on the January, February, and March worksheets are all summed in the Total_Quarter1 worksheet.

Read more ›

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

Tom’s Tutorials For Excel: Listing Your Worksheets

Tom’s Tutorials For Excel: Listing Your Worksheets

Here are a few ways to list the names of the worksheets in your workbook.

For starters, you can right-click on the sheet navigation buttons to the left of your sheet tabs, and a list of sheets will appear.

Read more ›

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

Tom’s Tutorials For Excel: Printing Some Sheets, Not Printing Others

Tom’s Tutorials For Excel: Printing Some Sheets, Not Printing Others

In some cases you want an easy way to print only certain sheets from your workbook.
There are two approaches — identifying which sheets to include, or which sheets to exclude.
Below are two macros,

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: Refreshing All Your PivotTables at Once

Tom’s Tutorials For Excel: Refreshing All Your PivotTables at Once

Here are two macros you can use for refreshing all the pivot tables on your active worksheet, or all the pivot tables in the entire worbook.

Sub RefreshAllActiveSheet()
Dim Pt As PivotTable
For Each Pt In ActiveSheet.PivotTables
Pt.RefreshTable
Next Pt
End Sub

Sub RefreshAllActiveWB()
Dim Sh As Worksheet,

Read more ›

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

Tom’s Tutorials For Excel: Automatically Refreshing Your Pivot Table

Tom’s Tutorials For Excel: Automatically Refreshing Your Pivot Table

If you’d appreciate the convenience of having your pivot table automatically refresh whenever you change its source data, here’s how you can accomplish that.

In the Before picture, the source data’s State field lists American states.

Read more ›

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

Tom’s Tutorials For Excel: Stamping Your Worksheet Changes With Date and Time

Tom’s Tutorials For Excel: Stamping Your Worksheet Changes With Date and Time

Suppose your worksheet requires frequent entries in a column, and you want to automatically record the date and time of whenever a cell in that column changes.

In the picture,

Read more ›

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

Tom’s Tutorials For Excel: Maximizing Your UserForm to Full Screen Size

Tom’s Tutorials For Excel: Maximizing Your UserForm to Full Screen Size

To fill the screen with your UserForm while maintaining the relative position of the form’s controls, stick this Initialize event code into the UserForm’s module:

Private Sub UserForm_Initialize()
With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Width = .Width
Height = .Height
End With
End Sub

Read more ›

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

Tom’s Tutorials For Excel: Preventing Save or Close Unless Cells are Filled

Tom’s Tutorials For Excel: Preventing Save or Close Unless Cells are Filled

Sometimes you may want to prevent the workbook from being saved or closed unless data has been entered into certain cells, such as for on-sheet forms. The VBA code to accomplish this would go into your workbook module.

Read more ›

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

Tom’s Tutorials For Excel: Specifying UserForm Position in Excel’s Application Window

Tom’s Tutorials For Excel: Specifying UserForm Position in Excel’s Application Window

You can automatically position your UserForm almost anywhere on your Excel application’s window, with the following Initialize event code that goes into the UserForm’s module.

Example 1, upper left

Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Dim Top As Double,

Read more ›

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

Tom’s Tutorials For Excel: Set Page Breaks For Specified Areas

Tom’s Tutorials For Excel: Set Page Breaks For Specified Areas

If your worksheet has areas of data that you want to print on separate pages, you can easily establish page breaks based on a wide choice of cell properties or text values. With the following macro for the pictured example,

Read more ›

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

Tom’s Tutorials For Excel: Open a Workbook To a Specific Sheet and Cell

Tom’s Tutorials For Excel: Open a Workbook To a Specific Sheet and Cell

How can you open a workbook to a specific worksheet and cell? For example, regardless of where you were in the workbook when it was last closed, if you always want the workbook to open by taking you to cell A1 of Sheet3,

Read more ›

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

Tom’s Tutorials For Excel: How to Show Your Workbook’s Name in a Cell

Tom’s Tutorials For Excel: How to Show Your Workbook’s Name in a Cell

Here are a few ways to show your workbook’s name in a cell, example, YourWorkbookName.xls.

Formula (goes on one line but broken into 2 lines for browser readability)
=MID(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))+1,FIND(“]”,

Read more ›

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

Tom’s Tutorials For Excel: Name your sheet tab same as cell value.

Name your sheet tab same as cell value.

You can rename a worksheet in real time the moment you change a cell’s value.

A Worksheet_Change event can look at the entry, evaluate it for worthiness, and immediately rename the worksheet tab to be the same as what you entered in the cell.

Read more ›

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

Tom’s Tutorials For Excel: Does a Worksheet Exist, and Create One if Not?

Tom’s Tutorials For Excel: Does a Worksheet Exist, and Create One if Not?

There are three scenarios usually associated with this question.

Scenario #1:
Simply yes or no, does a certain worksheet name exist in the active workbook?

Sub TestSheetYesNo()
Dim mySheetName As String,

Read more ›

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

Tom’s Tutorials For Excel: Workbook Creation Date

Tom’s Tutorials For Excel: Workbook Creation Date

If you want to know the creation date and time of the active workbook:

Sub MyCreationDate()
Dim myDate As Date
myDate = ActiveWorkbook.BuiltinDocumentProperties(“Creation date”).Value
MsgBox Format(myDate, “mmmm d, yyyy at h:mm:ss AM/PM”), , _
“I was born on…”
End Sub

If you want to know the creation date and time of a closed or other workbook:

Sub CreationDateWB()
Dim objWB As Object,

Read more ›

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

Tom’s Tutorials For Excel: Open, Modify, and Close All Workbooks in a Folder

Tom’s Tutorials For Excel: Open, Modify, and Close All Workbooks in a Folder

This topic touches on a surprise we all got when Microsoft released version 2007, which stopped supporting the VBA FileSearch method. When you need to open all the workbooks in a folder, modify them in some way,

Read more ›

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

Tom’s Tutorials For Excel: Save Each Worksheet As Its Own Workbook

Tom’s Tutorials For Excel: Save Each Worksheet As Its Own Workbook

For the active workbook, this macro saves each visible worksheet as its own workbook in the current path. Workbooks are named as their sheet tab name, example, Sheet1’s workbook name would be “Sheet1.xls”.

Sub SaveWorksheetsAsWorkbooks()
Application.ScreenUpdating = False
Dim ws As Worksheet,

Read more ›

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

Tom’s Tutorials For Excel: Move Embedded Charts to Their Own Chart Sheet

Tom’s Tutorials For Excel: Move Embedded Charts to Their Own Chart Sheet

Here is a macro that will move all embedded charts on a worksheet to their own Chart sheet.

The Select method for cell A1 (it could be any cell but I chose A1) is to avoid a run time error which could occur if an embedded chart is active when a new sheet is attempted to be added.

Read more ›

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

Tom’s Tutorials For Excel: Summing Cells Across Worksheets

Tom’s Tutorials For Excel: Summing Cells Across Worksheets

Here are formulas that sum numbers in a single cell, and in a range of cells, across multiple worksheets. The first picture shows a section from four different worksheets, each displaying Income and Expenses for their respective Quarter.

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: Spreadsheet Passwords — The Facts About Protection

Tom’s Tutorials For Excel: Spreadsheet Passwords — The Facts About Protection

Spreadsheet password protection is a topic of major concern for Excel users, rightly so. Information in worksheets can be confidential, needing to remain undisturbed with formulas that must be protected from deletion.

It’s wise for an Excel user to voice his or her curiosity of spreadsheet protection,

Read more ›

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