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.
Can't Empty the Clipboard
The Clipboard is essential to move or copy information from one place in Excel to another. If you get an error when you try to complete an operation that relies on the Clipboard, this can be disconcerting.
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.
Changing Multiple Cells at Once
Excel includes several different methods of editing information in your cells. If you want to edit multiple cells all at the same time, you can use the techniques described in this tip.
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.
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
You can edit cell information either in the Formula bar or in the cell itself. Here's how you can configure Excel to specify where you want to perform your edits.
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.
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 a Shortcut for Pasting 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.
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.
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
Do you need to use symbols frequently in your Excel data? The common way to insert them is by using the Symbol dialog box. Here are some other, quicker ways to use the symbols you need.
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 Everything Up to a Character Sequence
Sometimes you have too much information in a cell and you need to “pare down” what is there to get to the info you really need. This tip discusses how you can delete information in a cell that may precede a particular sequence of characters.
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.
Disabling Dragging and Dropping
Excel allows you to easily paste information into a worksheet, including through simply dragging and dropping the information. If you don't want people to be able to place information in your worksheet that easily, it can be a challenge to disable the editing ability.
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
Grading in schools is often done using numeric values. However, you may want to change those numeric values into letter grades based upon a determined scale. This can be done most easily using Excel's lookup functions.
Displaying Row and Column Labels
When you create a worksheet, it is common to place headings at the top of each column and the left of each row so you can know the meaning of the data in the worksheet. It is a bother to have those rows and columns scroll off the screen when you are entering data. Here's how to make those headings stay visible at all times.
Easily Entering Dispersed Data
Need to enter information into a bunch of cells that aren't anywhere near each other in the worksheet? Here's a handy way to make data entry easier in this scenario.
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.
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.
Ensuring Standard Units During Data Entry
Need to make sure that information entered in a worksheet is always in a given unit of measurement? It’s not as easy of a task as one might hope. Here’s a discussion of some different techniques you can try out.
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
Need to enter the current time into a cell? It’s easy to do using this keyboard shortcut. The shortcut is a handy one to know when you need to log events into your worksheet.
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
Combining workbooks that have cross-links to each other can offer some special challenges. This tip examines how you can get around those challenges.
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 allows you to edit your cell contents in two places. What if you want to limit where editing occurs, so it can only be done in the cell itself?
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. There is no inherent way to do this in Excel, but you can add the capability with 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?
Want a quick way to tell how may rows and columns you've selected? Here's what I do when I need to know that information.
Ignoring Paragraph Marks when Pasting
Paste information in a worksheet, and you may end up with Excel placing it into lots of different cells. If you want it to appear only in a single cell, you'll love the technique presented in this tip.
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 a Row or Column
When editing worksheets, it is important to know how to add rows and columns. Excel provides a couple of quick ways you can perform the task.
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.
Need to insert rows in your worksheet? Excel provides a few techniques you can use to do this. Here are some ideas you can put to work.
Limiting Choices in a Cell
Want to limit what a person can enter into a particular cell? You can use Excel's data validation feature to help enforce what people enter.
Limiting Entry of Prior Dates
Want to establish a "bottom limit" on what dates can be entered in a cell? This tip presents two different ways you can limit date entries.
Limiting Number of Characters in a Cell
Need to limit the number of characters that can be entered into a cell? One easy way to do it is through the use of Data Validation, as described in this tip.
Merging Cells to a Single Sum
One way to make your worksheets less complex is to get rid of detail and keep only the summary of that detail. Here's how you can accomplish this task while still ensuring that your data isn't messed up.
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
Do you need to know when a workbook was last changed? There are a couple of ways you can go about keeping track of the change date, as discussed in this tip.
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. Here are three of them you'll find useful.
Picking a Group of Cells
Excel makes it easy to select a group of contiguous cells. However, it also makes it easy to select non-contiguous groups of cells, as described in this tip.
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
You can easily adjust the values in a range of cells by a certain amount. The key is to modify how you use the pasting capabilities of Excel.
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.
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.
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.
Need to find that misplaced comment in your worksheet? It's easy to do using the Find and Replace capabilities of Excel.
Selecting a Word
There are a few editing tricks you can apply in Excel the same as you do in Word. Selecting a word from the text in a cell is one of those tricks.
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 entering data in a worksheet, you may only want to add information to the cells in a particular range. You can easily do that by using the technique described in this tip.
Setting a Length Limit on Cells
Limiting what can be entered in a cell can be an important part of developing a worksheet that other people use. Here's a couple of ways you can check or limit the length of what goes into a cell.
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 Selecting a Data Range
Want to select all the data in a contiguous section of a worksheet? The shortcut discussed in this tip makes it very easy.
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.
Switching Editing Location
Excel allows you to edit the contents of a cell in two places—the cell itself or in the Formula bar. If you want to switch from one place to the other while you are editing, you'll need to rely on the mouse.
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?
Two lists of similar data can be challenging to synchronize. Here are some ways that you can align data in two different lists so that the values can be more easily understood in relation to each other.
Excel makes it easy to transpose your data so that rows become columns and columns rows. It doesn’t have a built-in capability to transpose data in three dimensions, across worksheets. Here’s some ways you can accomplish the data transformation, if you desire.
Transposing and Linking
Do you need to both transpose and link information you are pasting in a worksheet? It isn't as impossible to do as it appears at first.
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.
Excel provides the ability to define names that refer to cells or ranges of cells. These can then be used in your formulas to make them more understandable. This tip provides a very "high level" view of what names are in the Excel environment.
Undoing an Edit
We all make mistakes. Fortunately, Excel makes it rather easy to undo your makes, right after you make them.
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
Have you ever typed something in Excel, only to have it replace whatever is to the right of the insertion point? That's known as Overtype mode, and it can be frustrating unless you know how to turn it off.
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
If you have trouble seeing the information presented in a worksheet, you can use Excel's zooming capabilities to ease the trouble. Here's a quick discussion of all the ways you can adjust the size at which information is displayed on-screen.