Tips, Tricks, and Answers

The following articles are available. Click the article's title (shown in bold) to see the associated article.

Adding Leading Zeroes to ZIP Codes If you import some address data into Excel, you may notice that the leading zeroes from your ZIP Codes have been chopped off. Here’s the reason why and what you can do about it. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Adding Ordinal Notation to Dates If you use dates in your worksheet, you may want to add an ordinal indicator to the day of the month. This tip shows the best ways to achieve this result. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Adjusting Comment Printouts Need to print out the comments in a worksheet, but you don’t want the standard preface information Excel provides with each comment? You can copy the comments (without the name and colon) to a new worksheet using the technique in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Adjusting Values with Formulas You can adjust values by using the Paste Special feature, but you may want to do it by applying a formula. This tip describes the rationale for such an approach, as well as a way in which it can be accomplished. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Appending to a Non-Document Text File Do you need to write information out to a text file so it can be used by other programs? You can easily accomplish this task using a macro, as described here. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Automatically Closing a Workbook Walk away from your computer, and your work is visible on the screen for all to see. For security purposes you may want a workbook to close automatically if it isn’t used within a certain period of time. This tip discusses how you can create macros to accomplish that task. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Automatically Converting to GMT Greenwich Meridian Time, or GMT, is a common reference time used around the world. If you want to convert a time to GMT, this tip provides the information you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Automatically Copying Formatting You can easily copy the contents of one cell to another using a formula. There is no way to similarly copy formatting from one cell to another. There are a couple of workarounds you can use, relying either on macros or on the Camera tool. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Automatically Loading Add-ins Want to load a particular add-in for use with a specific worksheet? Here’s a quick way to do it using macros. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Automatically Printing a Range If you want to automatically print a particular area of your worksheet at a specific time of day, you’ll love this tip. With the use of two small macros, you can get just the automatic output you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Breaking Up Variable-Length Part Numbers Part numbers can often be long, made up of other component elements. Breaking up part numbers into individual components is easy if each component is a particular length. The task becomes somewhat more difficult if the components can be variable lengths. This tip presents a variety of methods of extracting variable-length component elements of a part number when those components follow a discernable pattern. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Calculating the Interval Between Occurrences With a long list of items in a worksheet, you may want to determine the last time a particular item appeared in the list. There are a couple of ways you can tackle this problem, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Changing Directories in a Macro Macros can do all sorts of manipulations of files and directories. One command you can use is the ChDir command, which allows you to change from one disk directory to another. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Changing Fonts in Multiple Workbooks If you need to change fonts used in a lot of different workbooks, the task can be daunting—if you need to do it manually. This tip looks at a macro you can use to make the task more automatic. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Changing Months in a Workbook Excel has a few methods to change the months in a workbook. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Changing Section Headers Excel doesn't provide a very easy way to vary headers or footers during a printout. This tip explains how you can use a macro to do your printing and make the changes on the fly. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Changing the Shortcut Menu If you want to change the Context menus used in Excel, on purpose, here’s how to go about it. Just create a macro and make the change to the controls on a CommandBar. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Choosing Direction After Enter On a Workbook Basis Excel allows you to configure what happens when you press Enter in a cell. This change, which controls the direction in which the following cell is selected, is applicable to all instances of Excel you use from that time forward. You may want to vary, on a workbook by workbook basis, how the Enter key behaves. This tip explains how this can be done. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Clearing the Undo Stack in a Macro The Undo stack is cleared automatically whenever you run a macro. This fact of Excel’s behavior allows you to easily clear the stack from within the macro itself. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Closing a Read-Only Workbook When you create a workbook that is read-only, users can still make changes to the workbook. When they exit, they are prompted to save their changes to another workbook (since yours is read-only). If you don’t want users to be prompted to save their changes, you can use a simple macro that tricks Excel into thinking the workbook has already been saved. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Coloring Cells with Formulas Want a quick way to tell where all the formulas are in a worksheet? In a few quick steps you can color all those cells. This tip explains how to do it manually, as well as providing a macro to automate the process. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Combinations for Members in Meetings Got a large group of people listed in a worksheet and you want to make sure that each person has met with every other person in the list? This tip explains a scenario in which you can figure out who needs to meet with whom. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Combining Columns You can concatenate cells using formulas, but you may want a way to concatenate lots of cells using a macro. The macro presented in this tip handles concatenation of many columns in one easy step. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Comparing Workbooks Do you need to compare two workbooks to each other? While you can use specialized third-party software to do the comparisons, a simple formula or two might be all that you really need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Concatenating Ranges of Cells Putting the contents of two cells together is easy. Putting together the contents of lots of cells is more involved, as discussed in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Condensing Multiple Worksheets Into One Excel provides a consolidation tool that allows you to easily combine the data from a bunch of worksheets into a single worksheet. This tip explains how you can use this tool. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Conditional Page Breaks When printing a report from Excel, it is not unusual to want to start a new page when something changes in your data. Using the Subtotals feature of Excel, you can easily have your report paginated on any field in your data. If you prefer, you can instead use the macro provided in this tip to insert the page breaks necessary for your printout. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Conditional Printing If you need to make what Excel prints be based upon conditions in a worksheet, you’ll love the information in this tip. There are a variety of ways you can approach the task and get just what you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Conditionally Deleting Rows Want to delete a bunch of rows in a worksheet based on the value in a certain cell of each row? There are a couple of ways you can approach the problem, as discussed in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Conditionally Displaying a Message Box If you want to display a message box when a particular set of criteria are met, you’ll be interested in the techniques in this tip. The approach discussed here relies on the use of the Change event for a worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Controlling Display of Toolbar Buttons This tip contains a macro that will enable custom toolbar buttons whenever a worksheet is visible. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting an Unsupported Date Format If you import data produced by a program other than Excel, it might include dates stored in a format not automatically recognized by Excel. This tip shows how you can do the necessary data conversion so that Excel can recognize the dates. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Converting Cells to Proper Case An Excel macro to change cells from uppercase to lowercase. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting From Relative to Absolute Addresses used in a formula can be either relative or absolute. If you need to switch between the two types of addressing, here’s a convenient way to do it. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Converting Imported Information to Numeric Values When importing data into Excel that was created in a different program, the results you get are dependant on the characteristics of the data. If numeric values are not in a standard form, then they may be treated as text. This tip explains a few ways you can overcome the problem of non-standard formats for incoming numeric data. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting Phone Numbers Sometimes you receive a phone number that contains alphabetic characters and you need to convert it to a purely numeric phone number. You can easily do that using the macro in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Converting Text Case If your worksheets include lots of text, you may need this VBA macro to convert large ranges of text from upper to lower case. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Converting Text to Numbers If you import information into Excel that was generated by a different program, the numeric formats used by that program may not be automatically recognized by Excel. In that case, you might want to use a macro, such as the one described in this tip, to do the conversion for you so that you can work with the numbers properly. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Copying Comments to Cells Need to copy whatever is in a comment into a cell on your worksheet? If you have lots of comments, manually doing this can be tedious. Here is a macro solution that will make quick work of the copying. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Copying Headers and Footers Got a set of headers and footers in one worksheet that you want copied to another worksheet? This tip explains the various approaches you can use to perform the copy and get just the results you want. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Counting All Characters If you need to count the number of text characters in a worksheet, the macro in this tip can come in very handy. It looks at not only the text in cells, but also in text boxes. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Counting Cells with Text Colors Excel allows you to specify text colors on a character-by-character basis in your cells, if desired. If you want to count how many cells contain text of a specific color, the user-defined function in this tip will come in very handy. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Counting Shaded Cells Ever want to know how many cells in a worksheet (or a selection) are shaded in some way? You can create a handy little macro that will do the counting for you. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Counting the Times a Worksheet is Used Do you need to know how many times a worksheet has been used? Excel doesn’t track that information, but you can develop some ways to track it on your own. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Counting Unique Values If you have a list of values in a range of cells, you may want to know how many unique values appear in the list. The ways you can go about making that determination depend on whether the list has blanks in it or not. This tip explains your options. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Counting Words Do you need to know how many words are in a range of cells? Excel provides no intrinsic way to count the words, but you can create a quick macro that will provide the figure. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Creating a Center Across Selection Button The Merge and Center tool is used to do just what it says—merge cells and center their contents. If you want to center information across cells without merging, then you’ll love the short macro presented in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Creating a Directory in a Macro When you create macro, you might want to use it to create a folder on your hard drive. You can accomplish this with one simple command, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Creating Charts in VBA Most charts you create in Excel are based on information stored in a worksheet. You can also create charts based on information supplied by a macro, however. This tip explains how. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Creating Individual Workbooks As a workbook grows, there may be a time when you need to “break out” all the worksheets to their own workbooks. This tip presents a way that you can easily—in one step—create a new workbook for each and every worksheet in your present workbook. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Creating Superscript and Subscript Buttons Want a quick way to apply superscript and subscript to selected text within a cell? This tip shows how the formatting can be done through a user form and a small set of macros. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Deleting a File in a Macro Need to delete a file within your macro? If you know the path and name of the file, it is easy to do with a simple command. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Deleting All Names but a Few Excel provides an easy way for you to delete one defined name at a time. If you want to delete most of the names in a workbook, but leave a few still intact, then Excel isn’t much help. The macro and ideas in this tip will help in this regard. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Deleting Blank Columns Import data from another program, and you could end up with a lot of blank columns in your data. Here’s the quickest way to get rid of these empty columns. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Deleting Every X Rows Need a quick way to delete every second or third row in a worksheet? Here’s one easy technique that you’ll use again and again. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Deleting Every X Rows 2 A macro comes in handy when certain rows need to be removed from a worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Deleting Unwanted Styles Excel styles are sometimes used to quickly format the way that information looks in a worksheet. If you get a spreadsheet that has many styles defined, you may want to get rid of those styles so you can format the spreadsheet your own way. This tip provides a macro that will quickly remove all the styles you no longer need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Deleting Worksheets in a Macro Do you need to delete a worksheet under the control of a macro you are creating? You’ll love this tip, then. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Deriving an Absolute Value in a Macro Need to figure out an absolute value within your macro code? It’s easy to do using the Abs function, described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Detecting Types of Sheets in VBA When processing workbook information in a macro, you may need to step through each worksheet to make some sort of changes. It is helpful to know what type of worksheets you are working with so that you don’t try to make changes on an inappropriate worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Determining a Random Value One of the better-known VBA functions is Rnd, which allows you to generate a random value between 0 and 1. You can use this for all sorts of purposes, as described here. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Determining Differences Between Dates Need to do a bit of “date math” in a macro? It’s easy using the DateDiff function, described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Determining If a File Exists Macros can be used to access and manipulate data files on your hard drive. It is a good idea for the macro to determine if the file exists before actually trying to do anything with the file. This tip shows a way you can determine if the needed file is actually on the hard drive. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Determining If a Number is Odd or Even If you need to know whether a particular value is odd or even, you can use this simple formula. Designed to be used in a macro, it returns either True or False depending on whether the value being examined is even or not. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Determining if Caps Lock is On If you have a macro that requests user input, you may want to make sure that the user doesn’t make entries if the Caps Lock key is engaged. This tip explains how you can check the condition of the key so that you can make decisions in your macro. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Determining the Current Directory When creating a macro, you may need to determine what the current directory is according to Windows. You can easily figure this out by using the CurDir function. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Determining the Hour of the Day Need to know what hour of the day a macro is running? You can determine the information by using the Hour function, described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Determining the Length of a String In a macro it is often helpful to know how long a particular text string is. That's where the Len() function proves invaluable, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Disabling Printing Don’t want your worksheets to be printed out? You can make it a bit harder to get a printout by applying the techniques in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Displaying the "Last Modified" Date One of the properties that Excel maintains for a workbook is the date it was last modified or saved. This tip explains how you can access that date and place it in a header or footer for a worksheet. (This can only be done with a macro.) Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Displaying the Selected Cell's Address Do you want a cell to contain the address of whatever cell is currently selected in the workbook? There are a couple of ways you can achieve this goal, each involving the use of a macro. This tip demonstrates two techniques that update the cell contents in different ways. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Dynamic Headers and Footers Do you want to change the headers and footers that appear on different pages of your printout? Here’s how you can get just what you want. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Dynamic Worksheet Tab Names Would it be helpful if your worksheet tab names were determined by the contents of a cell in your worksheet? Using the macros described in this tip, you can easily make the worksheet tabs just as dynamic as any other part of an Excel worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Easily Adding Blank Rows If you need to “open up” the data in a worksheet so that there are blank lines available, you’ll like this tip. It explains the easiest ways to add just the space you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Exiting a For ... Next Loop Early One of the most common programming structures used in macros is the For … Next loop. This structure is used to repeat a series of programming statements any number of times. You can jump out of the structure early by using the Exit For statement, which is described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Expiration Date for Excel Programs If you use Excel to create a macro-based application, you may want to make sure that your programs cease working after a certain time. The easiest technique for “expiring” a program is described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Exporting Black and White Charts Excel’s charts are normally created in color, but you can print them in black and white. You may be looking for a way to export the black and white version of a chart so that it can be used in a different program. This tip explains the different ways you can accomplish your desire. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Extracting Proper Words If you’ve got a list of potential words, and you want to know which of those potential words are real, you’ll appreciate the techniques described in this tip. You can either manually perform the analysis, or use the handy macro provided. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Faster Text File Conversions When importing a text file into Excel, you have the opportunity to define the characteristics of the data being imported. If the data has quite a few fields in it, specifying the characteristics of each field can be tedious. This tip explains a couple of ways you can make faster work of this importing process. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Finding and Deleting Links A VBA macro to find and delete external links. Microsoft Excel versions: 97 | 2000

Finding the Last-Used Cell in a Macro In a worksheet, Ctrl+End jumps to the bottom-right cell in the data table. Doing the same thing under the control of a macro, however, takes knowledge of the exact methods and properties to find that last cell. This tip explains how to do it. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Finding the Parent Folder Do you need to know, in a formula, the name of the folder in which the current workbook is stored? This tip provides a formula you can use right away. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Forcing Input to Uppercase If you type information into a workbook, you may want to make sure that what you type is always stored in uppercase. You can implement this type of feature by using a couple of short macros. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Forcing Manual Calculation For a Workbook If you have a large, complex workbook, you may want to make sure that it is always calculated manually instead of automatically. You can do that by including just a couple of small macros in the workbook. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Friendly and Informative Error Handling When writing macros, you need to know how to convey error messages to the user should an error actually crop up. This tip examines one technique that makes the task fast and easy. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Full Path Names in Excel If you want to see the full path name for a particular workbook, you’ll like the macro in this tip. It allows you to display the path on Excel’s status bar. Assign the macro to a toolbar button, and your desired information is only a quick click away. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Full Path Names in Headers or Footers Using a macro to add the full path name into a header or footer in an Excel worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Generating a List of Macros Creating a macro to compile a list of macros in an Excel workbook. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Getting a File Name When creating a macro, you can access the standard Open dialog box so that a user can select a file to process. This tip explains how to do that and grab the selected file name from the dialog box. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Getting Input from a Text File You can use a macro to read information from a text file. The steps are easy, and then you can use that information in any way you see fit. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Getting Rid of Extra Quote Marks in Exported Text Files In an effort to be helpful, Excel may add extra quote marks around text that it exports to a text file. If you don’t like the format in which text is exported, then the solution is to create your own export capability with a macro, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Getting Rid of Many Hyperlinks Got a bunch of hyperlinks you need to get rid of? Here’s a handy (and simple) macro that can do the task for you. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Getting Rid of Non-Printing Characters Intelligently Is your worksheet, imported from an external source, plagued by non-printing characters that show up like small boxes on-screen? You can get rid of them using the various techniques described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Grabbing the MRU List Want to use the list of most recently used files in a macro? You can access it easily by using the technique presented in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Hiding Excel in VBA Want to have you macro completely hide the Excel interface? You can do so by using the Visible property for the Excel Application object. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Highlighting the Rows of Selected Cells If you lose your place on the screen quite often, you might find it helpful to have not just a single cell highlighted, but the entire row. This tip provides a couple of ways you can achieve this goal. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Hyperlinks to Charts You can create hyperlinks to all sorts of worksheets in a workbook, but you cannot create a hyperlink to a chart sheet. This tip provides a workaround that should display just what you want hyperlinked in the first place. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Inconsistent Output for Empty Columns in a CSV File When you create a CSV file in Excel, the information stored in the file may not contain all the fields that you think it should. This tip examines the reasons behind this behavior, along with several different ways to work around the problem. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Inserting and Copying Rows Want an easy way to insert a new row in a worksheet and copy everything from the row above? (You end up with two identical rows this way.) Here’s a handy macro that can do this edit in one quick step. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Inserting Dashes between Letters and Numbers If you need to add dashes between letters and numbers in a string, the work can quickly get tedious. This tip examines some ways you can make the work faster and easier. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Inserting the Saved Date In a Header or Footer Using a macro to set the date the workbook is saved in the header or footer. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Jumping to Alphabetic Worksheets Workbooks can contain many, many worksheets. If you want a quick way to jump to a specific worksheet, there are a couple of techniques you can apply. This tip describes those techniques, and even provides a macro that makes jumping to groups of worksheets even easier. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Jumping to the Start of the Next Data Entry Row Want a quick way to jump to the end of your data entry area in a worksheet? The macro in this tip makes quick work of the task. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Last Saved Date in a Footer It is sometimes helpful to have the page footer of your worksheet contain the date that the workbook was last saved. Unfortunately, Excel doesn’t include a way to add this information. You can, however, add the information with the macros discussed in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Limiting Entry of Prior Dates You can use the data validation feature of Excel to limit what is entered in a cell, including the way that dates are entered. This tip provides an advanced way to limit exactly which dates can be entered. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Magnifying Only the Current Cell You can use the Zoom feature of Excel to magnify what Excel shows of your workbook, but it affects the entire screen. What if you want to only magnify a small portion of the screen, such as the selected cell? There are a variety of ways you can approach this problem, as you’ll learn in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Maintaining the Active Cell Move from one worksheet to another, and Excel selects whatever cell was last used in the worksheet you are selecting. If you don’t want this behavior (you want to have the same cell selected on the new worksheet as on the old), then you can apply the techniques in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Making a Cell's Contents Bold within a Macro If you are formatting or processing data within a macro, you might want to make the contents of a particular cell bold. This tip explains how easy it is, requiring only a single line in your macro code. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Making a Cell's Contents Italics within a Macro Need to make the contents of a cell an italic typeface? It’s easy to do when you are processing information in a macro. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Multiple Line Headers and Footers Using a macro code to set up a multi line header or footer. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Naming Tabs for Weeks A common structure in workbooks is for each worksheet to contain information for a specific week in the year. This tip provides two macros you can use to automatically name worksheets for the various weeks in a year. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Non-standard Sorting If you place data on multiple lines in a cell, there may come a time when you need to sort the data based on the information in the second line of the cell. This is easier conceived than done, but the techniques in this tip will help you achieve the desired results. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Noting the Workbook Creation Date You may want to add, to your worksheet, the date on which a particular workbook was created. Excel doesn’t provide a way to do this, but you can use one or two simple macros to insert the information you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Numbers Spelled Out It is not uncommon to have a need to spell out numbers, such that “123” becomes “one hundred twenty three.” There is no intrinsic way to do this in Excel, but by using a macro you can do the desired conversion. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Opening an HTML Page in Excel Excel is a Web-aware application that allows you to access Internet information from within a worksheet. If you want to display a Web page from within a macro, however, Excel isn’t quite as friendly. The techniques covered in this tip will help you display the Web page just as you want. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Page Numbers in VBA When you print a larger worksheet, Excel breaks the printout across several pages. You may want to know, before you print, which page a particular cell will print on. There is no intrinsic function that delivers this information to you, but you can develop a macro that should provide just what you are looking for. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Parsing Non-Standard Date Formats If you import information into a worksheet from a different program, you may be surprised when Excel doesn’t parse the dates correctly. This tip describes some ways in which you can help Excel to make sense of the imported data. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Positioning a Column on the Screen If you have static columns and dynamic columns on the screen, you may want the dynamic columns to always show a particular range. Getting just what you want is a snap when you apply the techniques discussed in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Positive and Negative Colors in a Chart When creating a line cart, the line can show values both positive and negative values. This tip explains how you can use different colors to display that portion of the line that dips below zero into negative territory. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Preserving the Undo List The undo list is normally wiped out when you run a macro. This makes it impossible for a user to undo the changes that are wrought by your coding. This tip explains some of the ways you can work around this apparent shortcoming. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Printing a Single Column in Multiple Columns The data in some worksheets consists of just a column or two of data, extending for many rows. When you print these worksheets, you end up with a printout that has a lot of white space on the right side of the page. You could use less paper if you were able to “snake” your column on the page so that it occupied multiple columns. This tip shows you how. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Printing a Worksheet List Want a quick way to create a list of worksheets in a workbook? Here’s a super-fast method using a simple little macro. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Printing Multiple Worksheets on a Single Page Do you want to “condense” how Excel prints a workbook so less paper is used? This tip describes a couple of things you can do to get the desired results. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Printing Selected Worksheets Need to print just a few worksheets out of a group of workbooks? There’s no need to manually load each workbook and print the sheets; instead you can use the macro presented in this tip. It allows you to print selected worksheets from each workbook in a particular folder. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Printing Workbook Properties When you take a look at the Properties dialog box associated with any workbook, you’ll notice that Excel tracks quite a bit of information about the workbook itself. This tip shows how you can get this property information into a worksheet so you can print it out. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Progression Indicator in a Macro If you use macros to process data in a workbook, it can often appear like the macro is doing nothing, even though it is working hard behind the scenes. This is the time when you need some sort of indicator on-screen to let users know that the macro is, indeed, working away. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Pulling Apart Cells The Text to Columns tool is a great boon for analyzing textual data in a worksheet. You can use the tool to strip text into component parts that you can work with easier. This tip explains how to use the tool and introduces a macro you can use when the tool won’t work just the way you want it to. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Pulling Apart Characters in a Long String Excel includes some powerful ways that you can process text in a worksheet. This tip covers two ways (both with formulas and macros) that you can pull a string apart to get at its individual characters. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Pulling Cell Names into VBA Excel allows you to create names for cells, ranges of cells, constants, and formulas. These names are very helpful in creating worksheet formulas. The names are part of the Names collection in VBA, and it is through that collection that you can access the names in your own macros. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Pulling Formulas from a Worksheet The formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can then easily make a copy of those formulas for documentation or analysis by applying the ideas in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Putting Cell Contents in Footers Do you need the contents of a particular cell to always appear in the footer of a worksheet? You can take care of this using a macro, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Quickly Changing Windows Word proves several keyboard shortcuts that allow you to cycle through the open workbook windows on your screen. There is no similar “cycling” method for those using a mouse, unless you implement the macro discussed in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Referencing External Cell Colors If you want to reference cell colors external to your current workbook, there is no way to do it using Excel functions. You can, however, create your own macro that will do the referencing for you. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Relative VBA Selections Macros often need to access other cells in a worksheet, relative to the currently selected cell. There are a number of ways you can implement this type of reference in your macros. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Removing Conditional Formats, but Not the Effects Conditional formatting is very powerful, but at some point you may want to make the formatting “unconditional.” In other words, you might want to remove the conditions on which the formatting is based, but still keep the formatting that you see for those cells. This can only be done via a macro, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Reorganizing Data Do you need to completely reformat the data you import into Excel? This tip shows how you can break a single column of data into multiple columns of input. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Replacing and Converting in a Macro Sometimes the data you import from other programs needs to be converted in some way in order to be useful in Excel. This tip explains how you can use a short little macro to get Excel to re-evaluate the information in the cells so that it is converted to a format that can be used. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Resizing a Text Box in a Macro Text boxes are easy to add to a document and manually resize, as needed. If you want to resize the text box in a macro, however, the way to do it effectively may seem elusive. This tip explains how you can resize a text box to cover a desired range of cells. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Resizing Checkboxes User forms, created in VBA, can be very helpful for a user interface. If the default checkboxes in the form are too small for your liking, there are only a limited number of things you can do, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Retrieving Drive Statistics Need to gather some information about the drives on a system? It can be pretty easy to do using a macro, as shown in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Retrieving Worksheet Names If you are constructing a table of contents for a workbook, you need an easy way to gather a list of all the worksheet names in the workbook. This tip provides a macro you can use to gather those names and enter them into a worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Returning Zero When a Referenced Cell is Blank Formulas in Excel must return some sort of value; they cannot return nothing. This can cause problems for some formulas, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Running a Macro in a Number of Workbooks Got a macro that you need to run in a whole bunch of workbooks? Figuring out how to accomplish the task can give you headaches, but the techniques described in this tip can provide relief. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Running a Macro When a Workbook is Closed One of the automatic macros you can set up in Excel is one that is triggered when a workbook is closed. This tip explains how you can create a macro that runs whenever the workbook is closed. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Running a Macro When a Worksheet is Activated One of the automatic macros that Excel allows you to set up is one that will run every time a worksheet is activated. This is relatively simple to do, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Running a Procedure when a Workbook is Opened Do you have a macro that you need to run whenever you open a workbook? The easiest way to do this is to use the special Auto_Open function, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Saving Information in a Text File When writing macros, VBA includes quite a few different commands you can use to manipulate text files. This tip shows how easy it is to use some of those commands to save information into a text file. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Searching a Workbook by Default When you display the Find tab of the Find and Replace dialog box, you’ll notice that any search, by default, will be on the current worksheet. If you want Excel to default to searching the entire workbook, you may be out of luck. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Searching a Workbook by Default, Take Two How to create a macro that will display the correct Find and Replace box to set searching parameters. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Selecting All Visible Worksheets in a Macro Do you need your macro to select all the visible worksheets (and just the visible ones)? It’s not as easy as it sounds, but this tip uncovers the best way to do it. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Selecting Cells of a Specific Color Do you need to find cells that are formatted with a particular color? How you accomplish this task depends on your version of Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Selecting Random Names Got a list of names from which you need to select a few at random? There are several ways you can accomplish this task, using either formulas or macros. Each of the most common methods are described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Selecting the First Cell In a Row Write enough macros, and it is inevitable that you will need to select the left-most cell in a given row. This tip explains the technique to use. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Selective Headers and Footers Excel’s tools for creating headers and footers aren’t that great, which means you might not be able to control the headers and footers like you may need to. This tip explains how you can use a macro to selectively print headers or footers on only some of the pages of your worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Selectively Importing Records Got a huge amount of data you need to import into Excel? Sometimes the best way is through the use of a macro, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Self-Aware Macros Sometimes it may be helpful for a macro to know exactly where it is being executed. This tip provides a way that you can build such functionality into a macro. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Setting Column Width in a Macro Need to change the layout of your worksheet in a macro? You can adjust the width of individual columns by using the ColumnWidth property. This tip shows you how. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Shading Rows for Ease in Reading Output Shading every second, third, or fifth row of a printout can be helpful for reading data. This tip describes how to use a macro to get the formatting you desire. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Sheets for Days Need a quick way to have a worksheet for each day in a month? Here’s a macro that makes the worksheet creation a snap. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Sheets for Months One common type of workbook used in offices is one that contains a single worksheet for each month of the year. If you need to create these types of workbooks frequently, you’ll love the macro highlighted in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Shortcut for Pasting Only Values Do you use Paste Special to quickly convert formulas to their values for large amounts of data? If you use this trick quite often, then information in this tip will help you streamline the pasting so you can do it quicker and easier. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Shortening ZIP Codes US ZIP Codes can be of two varieties: five-digits or nine-digits. Here’s how to convert longer ZIP Codes to the shorter variety. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Showing Filter Criteria on a Printout When you print out a filtered worksheet, you may want some sort of printed record as to what filtering was applied to the worksheet. Here’s a couple of ways you can get that printed record. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Simultaneous Scrolling If you have two worksheets displayed at the same time, you might want those worksheets to remain visually “in sync” with each other. Here’s a couple of small macros you can use to scroll the worksheets simultaneously. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Sizing Text Boxes and Cells the Same Adding text boxes to worksheets is a snap. At some point you may want to make sure that the textbox you are adding is exactly the same size as the cell (or cells) that it covers. This tip explains how to make this happen. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Skipping Hidden Rows in a Macro Macros often have to step through rows of data in a worksheet in order to do their processing. If you have such a macro, you may want it to automatically skip over rows that you may have hidden. This tip explains how to add this functionality to your code. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Snapshots of Excel Worksheets for PowerPoint If you need to get lots of information from Excel to PowerPoint, the task can be daunting. This tip explains different approaches you can take when you need to get data from here to there. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Sorting Data Containing Merged Cells When formatting the layout of your worksheet, Excel allows you to easily merge adjacent cells together. This can cause havoc later, however, since you can’t sort ranges that contain merged cells. This tip provides some guidance on how you can get around this limitation. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Sorting Data on Protected Worksheets Protect a worksheet and you limit exactly what can be done with the data in the worksheet. One of the things that could be limited is the ability to sort the data. This tip explains how you can allow that data to be sorted. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Specifying Date Formats in Headers Dates in headers and footers are formatted using the same pattern used in Windows for the system date. If you need to use a different format, you’ll appreciate the information in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Specifying the Size of Chart Objects Create a new chart object in an existing worksheet, and Excel automatically makes the object 25% of whatever the screen size is. If you want a different size (without manually resizing), you are out of luck unless you use a macro to create the chart object. This tip explains how you can do this. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Spreading Out a Table Need additional space in a table? You can quickly add any number of rows desired between each row of an existing table by using the handy macro presented in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Suppressing the Reviewing Toolbar on E-mailed Workbooks If you get tired of the Reviewing toolbar popping up whenever you open a workbook you received via e-mail. You’ll love the information in this tip. Add the provided macro, and you can turn the toolbar off just as soon as it appears. Microsoft Excel versions: 2002 | 2003

Swapping Two Strings Strings are used quite frequently in macros. You may want to swap the contents of two string variables, and you can do so by using the three-line technique introduced in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Toggling AutoFilter Using the menu sequence Data | Filter | AutoFilter, you can toggle AutoFilter on and off for a data table. This tip explains how to create your own toolbar button that does the same thing (toggling) as the AutoFilter menu option. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Trimming Spaces from Strings It is not unusual for user-generated input or for text from unknown sources to include either leading or trailing spaces. VBA includes a trio of functions that allow you to easily remove any such spaces from the text. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Turning Off AutoFill for a Workbook Don’t want people using your workbook to be able to use AutoFill? You can add two quick macros that disable and enable the feature when your workbook is activated and deactivated. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Understanding Functions in Macros Functions are a special type of subroutine that returns a value to whatever program called it. This tip explains how to create functions and how to pass parameters to a function. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Understanding the If ... End If Structure An explanation of If … End If structure of a VBA macro in Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Unhiding a Single Column In a worksheet with lots of hidden columns it is a real pain to try to unhide just one or two columns. The best solution is to use a macro to do the unhiding, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Unhiding Multiple Worksheets Excel allows you to only unhide a single worksheet at a time. The way around this is to use a macro that allows you to unhide lots of worksheets at once. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Unique Name Entry, Take Two If you need to make sure that a column contains only unique text values, you can use data validation for the task. This won’t help when someone decides to copy and paste information, however. This tip addresses how you can block such actions using a macro. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Unlocking Charts Objects within a workbook are often locked as a form of protection. Your macro, however, may have a need to work with some of those locked objects. You’ll need the information in this tip so you can create the macro code necessary to unlock the objects. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Unprotecting Groups of Worksheets Protecting worksheets is a handy way to make your work safe from other users. If your workbook contains quite a few worksheets, unprotecting them individually can be tedious. The macros in this tip make short work of unprotecting (and protecting) groups of worksheets. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Updating Multiple PivotTables at Once PivotTables are great for analyzing large amounts of data. If you have a workbook that contains multiple PivotTables, you may want to update all those PivotTables at once if you’ve changed the data on which they are based. This tip explains an easy way to do the updating. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using a Different Footer on Secondary Pages When printing a worksheet, you may want to have the footer different on the first page of your document than it is on subsequent pages. Here’s a couple of ways you can get the desired changes. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Using a Progress Indicator in Macros A few tips on adding a progress indicator that runs during long macro calculations. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using an Exact Number of Digits Excel allows you to format numeric data in all sorts of ways, but specifying a number of digits independent of the decimal point is not one of the ways. If you need this very specific type of formatting, you can use some of the ideas in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Using BIN2DEC In a Macro Need a way, in a macro, to convert binary numbers into their decimal equivalents? There are two ways you can get the desired conversion, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Using Excel for Timing Excel allows you to store times in a worksheet. If you want to use Excel to time certain events, there are a couple of ways you can approach the task. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Using InputBox to Get Data Macros often need to prompt users for information. One of the best ways to do this is with the InputBox function. This tip explains how the function works and how you can use the data entered by a user. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using Multiple Print Settings If you routinely print different parts of your worksheet, you know that it is a pain to repeatedly set the print area and print. This tip presents two ways you can get around this pain, one involving a macro and one not. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using Seek In a Macro When writing to or reading from a text file in a macro, you may want to specify where the writing or reading is to take place. The command you use to do this is the Seek command, described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Using the Camera in VBA The camera tool allows you to capture dynamic “pictures” of portions of a worksheet. If you want to use the camera tool from within a macro, you’ll need to apply the techniques discussed in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using the Status Bar Your macros can use the status bar area, at the bottom of the Excel window, to display messages. This is normally done to post status messages that indicate the progress of your macro. This tip explains how you can put information on the status bar in your own macros. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Working With Multiple Printers If you have multiple printers attached to (or available from) your system, you can print to any of them using Excel. For a quick way to switch printers, use the short macro introduced in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Zooming With the Keyboard Zooming in and out on a workbook allows you to magnify or reduce what you see on the screen, without affecting the printout. This tip provides a couple of handy macros that allow you to quickly zoom in and out using a keyboard shortcut. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Helpful Links

Ask an Excel Question
Make a Comment

Tips.Net Home

ExcelTips FAQ
ExcelTips Premium

Learn Access Now

Beauty Tips
Bugs and Pests Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Money Tips
Organizing Tips
Pet Tips
Word2007 Tips
WordTips

Advertise on the
ExcelTips Site

 

RSS Feeds

 Daily Nuggets (an ExcelTip every day)

 Allen Wyatt's ExcelTips (the weekly classic)