You can show your column headers as letters or numbers. Here’s how to change column headers depending on your preferred look.
…
Blog Archives
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…
…
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.
…
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.
…
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?
…
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.
…
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,
…
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,
…
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.
…
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.
…
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.
…
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,
…
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,
…
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.
…
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.
…
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.
…
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.
…
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.
…
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,
…
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: 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: 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: 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,
…
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: 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: 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: 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,
…
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,
…
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.
…
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.
…
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,
…
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 >
…
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,…
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.
…
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,
…
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…
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.
…
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,
…
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,
…
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,
…
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(“]”,
…
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.
…
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,…
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,…
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,
…
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,…
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.
…
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.
…
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,
…
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,
…
