Editing

Tips, Tricks, and Answers

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

A Fast Find-Next
Tired of the Find and Replace dialog box blocking the view of your worksheet when you are searching for information? Do what I do and get rid of the dialog box. Use the technique described in this tip to do your searching.

Adjusting a Range's Starting Point
Select a range of cells, and one of those cells will always be the starting point for the range. This tip explains how to change the starting point for a range without getting rid of the range itself.

Adjusting Formulas when Pasting
The Paste Special feature in Excel can be used to uniformly adjust values and formulas. This tip shows how powerful this feature can be.

Automatically Breaking Text
Want to convert the text in a cell so that it wraps after every word? You could edit the cell and press Alt+Enter after each word, but there's an easier way using a formula.

Automatically Moving from Cell to Cell when Entering Data
As you enter data in a worksheet, you may want to have Excel automatically move from cell to cell based on the length of what you are entering. This is not as easy to do as you might at first think, but there are ways to accomplish the task.

Canceling an Edit
When editing a cell, you may want to cancel the edit at some point. There are two ways to do this, both described in this tip.

Cell Movement After Enter
What happens when you press Enter in a cell depends on how you have Excel configured. Here's the way you can control the action Excel takes.

Changing Months in a Workbook
When you copy a worksheet and then need to make changes to information in that worksheet (such as changing month names), the task can sometimes be daunting. Here are some ideas on how you can make the changes easier.

Character Limits for Cells
Excel places limits on how much information you can enter into a cell and how much of that information it will display. This tip explains those limits, how you can work around them, and why upgrading your version of Excel may be beneficial.

Checking for a Value in a Cell
Need to figure out if a cell contains a number so that your formula makes sense? (Perhaps it would return an error if the cell didn't contain a number.) You can use the ISNUMBER function, described in this tip, to check for the value you need.

Checking for an Entry in a Cell
You may be looking for a way to have a formula determine if a particular cell has anything in it. Here's how you can find the information.

Cleaning Up Lists
When you have huge amounts of data you need to check for matches, Excel may not be the best tool to use. If you can fit the data within a worksheet, however, then you can relatively easily check for matches, as described in this tip.

Combining Columns
Need to concatenate the contents in a number of columns so that it appears in a single column? Excel has no intrinsic way to do it, but a macro can make quick work of the task.

Combining Multiple Rows in a Column
Do you need to concatenate the contents of a range of cells in the same column? Here's a formula and a handy macro to make quick work of your editing needs.

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.

Contingent Validation Lists
Data validation can be used to create lists of choices for entry into a particular cell. Using the techniques in this tip you can create multiple lists in which the second list is contingent on the choice in the first list.

Controlling Where You Edit Cell Contents
Editing information in a worksheet can be done in two places: in the Formula bar or in the cell itself. You can turn off the ability to edit in the cell by making one small change in Excel's configuration parameters.

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.

Converting Text to Values
When you import information originating in a different program, Excel may not do the best job at figuring out what various pieces of that information are used for. Here's a couple of ways to quickly convert imported textual values into the numeric values they should have been all along.

Copying Between Instances of Excel
Copying information between two instances of Excel is different than copying information between two worksheets opened in a single instance of Excel. This tip looks at the differences and discusses how you can make sure that copying works the way you expect.

Correcting a Capital Mistake
As you are entering data in a worksheet, Excel can monitor what you type and make corrections for common mistakes. One such mistake is the accidental use of the Caps Lock key. This tip looks at how Excel can correct for this mistake.

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.

Creating New Windows
If you need to look at different parts of the same worksheet at the same time, the answer is to create windows for your data. It is easy to do, as described in this tip.

Creating Selections
Want a really easy way to create a selection of a group of cells? Discover how to use the Extend key to make this task easier than ever.

Ctrl+V Pasting is Flakey
When you copy and paste a formula, you usually want to see the formula where you pasted. If you don't get the formula, but instead get the result, it could be because of the way you opened your workbooks.

Dealing with Long Formulas
If your worksheet formulas seem to go on forever, here's a handy way to make them more understandable. (All you need to do is judiciously use Alt+Enter when you enter the formula.)

Default Cell Movement when Deleting
Delete a cell or a range of cells, and Excel needs to figure out how to rearrange the void left by the deletion. You can control how the remaining cells are moved, and you can even create tools to do the movement automatically.

Defining Shortcut Keys for Symbols
When you use the Insert Symbol dialog box, you can insert any character or symbol it is possible to access in Excel. If you need to frequently insert a symbol, it can be a bother to always display the dialog box, however. This tip discusses some of the ways you can make inserting your symbols faster.

Deleting All Names but a Few
Want to get rid of most of the names defined in your workbook? You can either delete them one by one or use the handy macro described in this tip.

Deleting Duplicate Columns
Got a worksheet in which there may be entire columns that are duplicates of each other? If you want to delete those duplicate columns you'll appreciate the ideas presented in this tip.

Deleting Old Data from a Worksheet
If you keep on-going data in a worksheet, some of your data—over time—may need to be deleted. If you have an "expiration date" attached to your information, you can use a couple of different methods to get rid of the information that has expired.

Displaying a Hidden First Row
If you hide the first rows of a worksheet, you may have a hard time getting those rows visible again. Here's a simple way to make those rows show up.

Displaying Letter Grades
Excel is great at working with numbers, but it can be confusing to try to convert some numbers (such as numeric grades) into letters (such as letter grades). This tip looks at different ways you can make the conversion easily.

Displaying Row and Column Labels
If you add row and column labels to your worksheet data, you may want them to appear on-screen at all times so that you can better work with the data. This is easily done by freezing rows and columns, as described in this tip.

Easily Entering Dispersed Data
Do you need to enter data in a bunch of widely dispersed cells in a worksheet? You can make the job easier by using Excel's protection capabilities, as described in this tip.

Editing Individual Cells
Need to edit the data within a cell? There are any number of ways you can perform the edit; this tip documents them all.

Enhanced Filling
Using the AutoFill feature of Excel is very handy. If you want to expand the utility offered by the feature, all you need to do is change how you click the AutoFill handle.

Ensuring Rows and Columns are Empty
Before you go about deleting rows and columns helter-skelter, it is a good idea to determine if there is anything in the row or column you are going to delete. Here's the quick way to figure out whether a row or column is empty.

Entering Numbers in Excel
Enter information into a cell, and Excel needs to figure out what type of information it is. Here's how Excel interprets digits and some symbols you input.

Entering the Current Time
Want to enter the current time into a cell? It's easy with the shortcut described in this tip.

Errors when Copying References to External Cells
If you copy a cell that contains a reference to external data, do you get an error? It could be due to the complexity of the information contained in the formula referencing the external data.

Finding Unknown Links
There are several things to try when finding unknown links in Excel.

Finding Unused Names
After months or years of naming things (such as cell ranges), you may find your workbook cluttered with a bunch of names that don't even reference anything. Getting rid of these extraneous names can be tedious, but this tip provides a bit of relief in helping to clean things up.

Finding Wayward Links
Do you need to find links in workbooks that you need to join together? If you don't find them, then your formulas won't work properly in the combined workbook. Here's how to make sure the links still work as expected as you perform the merging.

Fixing the Decimal Point
Don't want to always type the decimal point as you enter information in a worksheet? If you are entering information that always has a certain number of digits after the decimal point, you can instruct Excel to always assume where the decimal point should be placed, even when you don't type one.

Forcing Editing to Be Done in a Cell
Excel can be configured to allow editing in the Formula bar or in the Formula bar and directly in a cell. It cannot be configured to allow only editing in a cell and not the Formula bar. This tip discusses a workaround that might prove satisfactory, however.

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.

Getting Help when Entering Functions
Need a quick memory jog when entering a worksheet function? Here's a shortcut that will be invaluable.

Getting Rid of Everything Except Numbers
Got some numbers and letters mixed up in the same cell? You may need to get rid of those letters so you are left with just the numbers. Here's some ways you can process that data just as you want.

Getting Rid of Spaces in Cells
Importing data into Excel that was generated in other programs can have some interesting side effects. For instance, you may end up with "blank" cells that actually have spaces in them. To get rid of these extraneous characters, you'll appreciate the techniques described in this tip.

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.

How Many Rows and Columns Have I Selected?
When selecting a range of cells, it is often helpful to know exactly how many rows or columns you've selected. Fortunately, Excel provides this information if you know where to look.

Ignoring Paragraph Marks when Pasting
When you paste information from another application into Excel, the information is "parsed" by Excel's import filter, sometimes in ways you may not want. This tip explains an easy way you can bypass the import filter and get Excel to ignore paragraph marks so you can place several paragraphs of information in a single cell.

Increasing Undo Levels
Each time you take some action in Excel, the action is saved in an "undo stack" so that the action can be undone, if desired. The number of undo levels that are available depend on the version of Excel you are using. This tip explains those limits and ways you can change them.

Inserting a Radical Symbol
The radical symbol is used frequently in some branches of mathematics. If you want to insert a radical symbol in a cell, here's how to go about it.

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.

Inserting Rows
Inserting a single row is easy when you use the Insert menu. When you need to insert multiple rows, then you can use the simple techniques described in this tip.

Limiting Choices in a Cell
If you want to limit what a user can enter in a particular cell, the easiest way to do this is to use the data validation feature of Excel. With just a few simple steps you can define what is acceptable for input.

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.

Merging Cells to a Single Sum
Want to get rid of a bunch of values but keep the sum of those values in your worksheet? The technique described in this tip can make quick work of this task.

Moving and Copying Cells
At the very heart of editing is the ability to move and copy cells in a worksheet. Understanding the differences between these operations, as well as how to perform them, is important for any Excel user.

Noting When a Workbook was Changed
You may want to keep track of when changes were last made to a workbook, particularly if the workbook is accessible to multiple people who may change it. This tip discusses a couple of ways you can keep track of such information.

Pasting Leading Zeroes
Paste information into a worksheet, and you may be surprised to see leading zeroes disappear before your eyes. Here's how to get them to stay where you expect them to stay.

Pasting Multiple Paragraphs Into a Single Cell
Copying information from one program (such as Word) to another (such as Excel) is a common occurrence. If you want to paste multiple Word paragraphs into a single cell, it's easy to do if you remember to double-click.

Picking a Contiguous Range of Cells
There are a variety of ways to pick a range of cells in Excel.

Picking a Group of Cells
A group of cells is different from a contiguous range of cells—sometimes a group of cells is called a selection set. Here you learn not only the differences, but how to select both.

Putting Addresses on State-Specific Worksheets
Got a bunch of data on a worksheet that you need to divide onto other worksheets, based on a specific piece of information in the data? You can do this manually or you can do it using a macro that examines your data and makes the move. Here's how.

Quickly Filling a Column
Excel has a great (and little known) shortcut for filling a column with information. It comes in very handy when you need to copy a formula for the entire depth of the column, as described in this tip.

Quickly Selecting Cells
Need to quickly select a range of cells? Perhaps the easiest way is to use both the mouse and the keyboard together, as described in this tip.

Quickly Updating Values
Need to apply a quick, standardized update to all the numeric values in a range? It's very easy to do, without the use of formulas. Just use the Paste Special dialog box, as described in this tip.

Ranges on Multiple Worksheets
Referring to a range of cells on the current worksheet is easy. But what if you want to refer to a range on a number of different worksheets? Here's the easy way to do this very specific type of referencing.

Reference Shortcut
Need to modify how a cell reference, in a formula, is constructed? The shortcut described in this tip will help you step through each of the various referencing modes that can be used by a cell.

Referencing the Last Cell in a Column
When developing formulas, you may need to reference the very last value in a particular column. This can seem perplexing, particularly if information keeps getting added to the column. Here's some ideas on how to access just the cell you need.

Removing Cells from a Selected Range
Select a large range of cells and you may later want to remove a few cells from that selection. This is not as easy as you might expect. Here's how to unselect those cells you don't want in the selection.

Removing Spaces
Need to get rid of spaces in a range of cells? There are two ways you can approach the task, as described here.

Replacing Some Formulas with the Formula Results
Macros are often used to process the data stored in a worksheet. Some of these processing needs can be pretty specific to both the data you are using and the audience for that data. This tip presents a macro that can be useful when you need to distribute your worksheets to other people who may not have access to the information external to the worksheet.

Scroll Wheel Doesn't Work when Editing in Excel 2007
When you start working with a new version of Excel, it can be confusing to figure out why things don't always work the same way as they did in your old version. If you are having problems with the mouse scroll wheel in Excel 2007, here's an idea that may help clear up the problem.

Searching Comments
Need to find the next comment in your workbook? You can use the Find and Replace capabilities of Excel to do just that.

Selecting a Word
How to select individual words in an Excel cell.

Selecting Formulas
Want to select only the formulas in your worksheet? It's easy to do using the Go To Special dialog box.

Selecting the Current Region
Most of Excel's commands affect whatever cells you select prior to invoking the command. Some commands, however, affect more than just the currently selected cell; they affect what is known as the "current region." This tip explains how to figure out what Excel considers the current region.

Sequentially Inputting Information
When inputting information into a worksheet, you can specify the range of cells into which the information should be put before actually doing the data entry. Doing so has the advantage that you can limit input to a specific area of the worksheet.

Setting a Length Limit on Cells
When creating a worksheet for others, you may want to limit the amount of information that can be entered in a cell. There are a couple of different ways you can approach the problem, but the best method may be with the macros presented in this tip.

Shifting Objects Off a Sheet
One day you are just editing your worksheet like you normally do, then you see an error that says "Cannot shift object off this sheet." That can throw your editing for a loop—unless you know the information in this tip, which can help to solve the problem.

Shortcut for Pasting Only Values
Excel's Paste Special command is used quite a bit. If you want to create some shortcuts for the command, here's some ways you can approach the task.

Shortcut for Selecting a Data Range
The easiest way to select a contiguous data range is to use Ctrl+Shift+8. This tip introduces the shortcut and explains how it works.

Splitting Text to Multiple Cells
When processing data, you may have a need to split a long text string into shorter chunks of text consisting of whole words. The built-in tools provided by Excel may not be suitable for this purpose, but there are a couple of ways you can approach the problem.

Symbols Convert to Numbers in Excel
Insert a symbol into a cell, and it should stay there, right? What if the symbol changes to another character, such as a number, when you leave the cell?

Synchronizing Lists
If you have two lists of data, you may need to "synchronize" them so that like records are on the same rows in a worksheet. This can be a daunting task, but the info in this tip will help make the work lighter.

Transposing and Linking
Using different ways to paste information with the Paste Special option.

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.

Turning Off Automatic Capitalization
Type some information into a worksheet, and you may notice that Excel automatically capitalizes some of your information. This happens because of an AutoCorrect setting that you can, if desired, change.

Turning Off Insert Options
When you insert rows, columns, or cells in a worksheet, does the resulting Insert Options icon bother you? Here's how to get rid of it.

Turning Off Paste Options
Paste some information into a worksheet and Excel helpfully displays some options related t the paste operation. If you don't want to see those options, here's how to turn the feature off.

Typing Check Marks into Excel
Need to enter a check mark into a cell? There are a number of ways you can get the desired character, depending on the font you want to use.

Understanding Names
An explanation of naming ranges or cells and how they can assist in Excel.

Undoing an Edit
The many ways to undo an action in Excel.

Using an Input Mask
When you are entering information in a worksheet, it sure would be handy to have a way to "mask" the information being entered. Excel doesn't provide input masks, but you can simulate one as described in this tip.

Using Find and Replace to Pre-Pend Characters
Need to add some characters to the beginning of the contents in a range of cells? It's not as easy as you might hope, but here are some great ideas.

Using Overtype Mode
Using overtype mode in Excel cells.

Using Slashed Zeroes
To reduce the chances of confusion in presenting data, some people like to use zeroes with slashes through them. If you fall into this crowd, you may be wondering how you can get Excel to display your zeroes with the desired slashes.

Viewing Formula Results
When editing information in a cell, you may need to know the result of a portion of your formula. The shortcut described in this tip will save your tons of time in figuring out such intermediate results.

Zooming In On Your Worksheet
The Zoom tool allows you to magnify what you are viewing on the screen. It can be a real boon for old eyes or detailed information. This tip explains the ways you can control the magnification applied to your worksheets.

 

More Information

The following are additional topics related to the subject of 'Editing'. A bracketed number after the topic indicates how many articles are related to that subject.