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.
Accepting Only a Single Digit
Want a quick way to enter a series of single digits into consecutive cells? The best approach is with a macro, and this tip examines a couple that may do the job for you.
Adding Spaces in Front of Capital Letters
Got some text that is 'run together' and needs spaces inserted to improve readability? There are a variety of approaches you can use, as illustrated in this tip.
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.
Alt+Enter Stopped Working Correctly
What do you do if a keypress you know worked correctly before all of a sudden stops working as you expect? This tip outlines a few ideas you can try to get things back to normal.
AutoFilling Numbers with a Trailing Period
The AutoFill tool is very handy when it comes to quickly filling cells with a sequence of values. Sometimes, however, it may not operate exactly as you'd desire. Here's one such instance and how to handle the problem.
Automatically Adding 20% to an Entry
When you are developing a worksheet for others to use, you may want to have entries in a particular cell (or cells) be automatically increased by 20%. This can be easy to do using the techniques in this tip, but the question remains as to whether you really should do it.
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 Copy Data between Workbooks
Edit a group of workbooks at the same time and you probably will find yourself trying to copy information from one of those workbooks to another one. If you aren't able to copy as you expect, it can be frustrating.
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.
Can't Enter Years in a Cell
Sometimes getting the right thing to show up in a cell can be a bit tricky when working with dates. If you enter a year number and Excel does some funky interpretation of what you entered, this tip explains what is happening.
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.
Capitalizing Just a Surname
Changing the capitalization of text is, believe it or not, a common task in Excel. Common or not, it can be frustrating to figure out how to change the capitalization of just part of the text. Here's an easy way to change the capitalization of a surname that is part of a longer name.
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.
Changes in Font Size when Copying
Have you ever copied information from one worksheet to another, only to have the information you paste not look the way you expected? There’s a reason for that, and it has to do with source and target formatting, as discussed in this tip.
Changing Limited Relative References to Absolute
Do you need to change whether a particular reference in a formula uses a relative or absolute reference? If so, you may benefit from the ideas presented in this tip.
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.
Choosing Direction after Enter On a Workbook Basis
Excel lets you specify how it should behave when you press Enter. If you change this behavior, Excel assumes you want it changed for all workbooks on which you might be working. Here's how to adjust it so that the behavior can vary based on which workbook you are using.
Clearing and Deleting Cells
When you want to remove information from a worksheet, you can either clear cells or delete cells. This tip examines the difference between the two, focusing on the different ways you can both delete and clear information.
Clearing Everything Except Formulas
Need to get rid of everything in a worksheet except for your formulas? You can do it rather easily by applying the ideas 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.
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.
Converting Forced Text to Numbers
If you have some numbers stored in cells that are formatted as text, you may get some surprises when you try to use those cells in some of your formulas. Here's how to force those text-formatted cells back to normal numeric-formatted cells.
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.
Converting to ASCII Text
When you work with imported or pasted data in an Excel worksheet, you may see some strange looking characters at times. If you want to get rid of those characters, there are a couple of ways you can approach the task.
Copying a Cell without Formatting
When you are copying a cell from one place to another (perhaps even to a different worksheet), you may not want to copy the formatting from the source cell to the target cell. This is where Paste Special comes in handy, as illustrated in this tip.
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.
Copying Cells to Fill a Range
Excel provides two really helpful shortcuts you can use to fill a range of cells, either horizontally or vertically. These shortcuts will help make your data-entry tasks a breeze.
Copying Data without Leaving the Currently Selected Cell
Copying from one cell to another is easy when editing your worksheet. Doing the copying without selecting a cell other than the currently selected cell becomes a bit trickier.
Copying Rows between Worksheets Based on a Text Value
Want to move data from one worksheet to another based on a text value in a column. There are a couple of ways you can accomplish the task, as described in this tip.
If you have added subtotals to your worksheet data, you might want to copy those subtotals somewhere else. This is easy to do using a special feature of the Go To command.
Copying to Very Large Ranges
Using the mouse to select a large cell range can be frustratingly slow. If you want to make copying to a large range of cells a snap, you'll love the techniques in this tip.
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.
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.
Dates Copied Incorrectly
Under the right circumstances, you may notice problems when copying dates from one workbook to another. This tip explains the cause of these types of problems and what you can do about it.
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.
Defeating Date Parsing when Pasting Information
Paste information directly into a worksheet, and you may be surprised that Excel makes some of the data unusable. This happens because the program tries to be helpful, and in the process it can make improper assumptions about your data. This tip presents five different ways to get around the inappropriate parsing that Excel may peform on your data.
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 Except Formulas
Need to get rid of everything in a worksheet except the formulas? It's easier to make this huge change than you think it is.
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.
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 an Input Format in a Cell
Want to show a user, in a cell, what you expect their input to look like? Unfortuantely it cannot be done natively in Excel. Here are some ideas for working around the missing feature, though.
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.
Dragging to Clear Cells
If you want to get rid of the contents of a range of cells, a quick way to do it is with the Fill handle. Yes, you can use drag the Fill handle back over a selection and remove the contents.
Easily Adding Blank Rows
Want to add a bunch of blank rows to a your data and have those rows interspersed among your existing rows? Here's a quick way to do it using Excel's sorting capabilities.
Easily Dividing Values by 1000
Sometimes the data in a worksheet isn't in the exact format desired. If you want to dividie your values by 1,000, there are a couple of ways you can approach the task, as described in this tip.
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.
Editing the Same Cell in Multiple Sheets
When creating a workbook, you may need to make changes on one worksheet and have those edits appear on the same cells in other worksheets. This is relatively easy to do by working with a range of selected sheets, as described in this tip.
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 Data as Thousands
There are many different ways you may need to enter data in a worksheet. For instance, you might want to enter data in thousands, without the need to type the trailing zeroes. This tip examines several ways you can enter this type of data easily.
Entering Dates in Excel
When you type information into a cell, Excel tries to figure out what type of information you are entering. If Excel can reasonably assume you are entering a date, it will convert the entry into a date, internally, and format the cell as a date.
Entering Info into Multiple Cells
Want to make an entry of the same value into a group of selected cells? It's easy to do with just one small change in how you now enter your data.
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.
Extracting Numbers within a Range
If you have a large number of values in a column, you may want to move the values that meet specific criteria to another column. This tip examines several different approaches to such a need.
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.
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 8-Bit ASCII Characters
When working with data created outside of Excel, you may need to check that data to make sure it contains no unwanted characters. This tip examines how you can look at your data and make sure that unwanted characters (in this case, those that use more than 7 bits) are removed.
Getting Rid of All Rows Except the One for the Latest Date
As you use Excel to collect data over time, sometimes winnowing out the latest data can present a challenge. Here are a couple of ways you can get just the data you need.
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 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.
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.
Importing a Subset of Records
If you only want to import a portion of whatever records are in a text file, Excel provides a number of ways you can accomplish the task. This tip looks at four different techniques you can use.
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 Different Dashes
Excel supports several types of dashes. This tip describes those different types and explains how to enter them in a cell.
Jumping to a Range
Need a quick way to jump to a particular part of your worksheet? You can do it by using the Go To dialog box.
Limiting Input to a Format
When setting up a worksheet for others to use, you might want to make some limitations on what can be entered in certain cells. This tip provides a couple of different techniques you can use to impose the limitations.
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.
Moving and Selecting Rows
If you need to move down a row and then select that row, you may wonder if there is a shortcut to handle such a navigation task. There isn't, but you can create one.
Moving Cells Using the Mouse
Want to easily move information from one cell to another? A quick way to do it is to simply drag and drop using the mouse.
Not Enough Resources to Delete Rows and Columns
Few things are as frustrating as trying to delete rows or columns and having Excel tell you that you can't perform the operation. If you get an "not enough resources" error when you try to perform this basic editing task, then the ideas in this tip may prove helpful to you.
Pasting Excel Data within Word's Page Margins
The programs in the Microsoft Office suite are designed to work with each other easily. Sometimes there can be hiccups along the path of data bliss, however. This tip examines ways you can paste Excel data into a Word document without extending past the document margins.
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.
Pasting without Updating References
Do you need to paste formulas without updating the references in whatever you are pasting? You can accomplish this, depending on what you are pasting and where you are pasting it.
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.
Playing with a Full Deck
Ever need to populate some cells in your worksheet with a range of data, but in random order? Here's a handy macro to get the job done.
Preparing Data for Import into Access
When importing Excel information into Access, you need to be concerned with the condition of the data. Here's how to make sure that two common offenders—Social Security Numbers and ZIP Codes—are imported properly.
Pulling Apart Cells
Separating text values in one cell into a group of other cells is a common need when dealing with text. Excel provides a handy tool that allows you to easily pull apart the cell values. Here’s how to use it.
Quickly Deleting Rows and Columns
Deleting rows or columns is easy when you use the shortcut described in this tip. Just select the rows or columns and then use the keyboard to do your deleting.
Quickly Entering Data
Excel includes a handy shortcut for entering data that is similar to whatever you entered in the cell above your entry point. Add this shortcut to your arsenal of editing tools, and you can increase your editing speed.
Quickly Entering Dates and Times
Excel provides keyboard shortcuts for a variety of purposes. This tip examines two such shortcuts, designed to allow entering times and dates quickly.
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 Transposing Cells
If you want to turn a range of cells by 90 degrees within a worksheet, you need to understand how Excel can handle the transposition for you. This tip discusses your options.
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.
Recording a Data Entry Time
When entering information in a worksheet, it is common to also note a date or time corresponding to the entry. There are several ways you can enter such information, ranging from manual to automatic. Here are a few techniques you can use for recording times.
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.
Relative Worksheet References when Copying
Copy a formula from one place to another and Excel helpfully adjusts the cell references within the formula. That is, it adjusts everything except the names of any worksheets that may be referenced in the formula. Here's how you can get around that lack of change.
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.
If you distribute a workbook that is used by others for data entry, you may want a way to make sure they fill in certain cells before closing the workbook to send it back to you. Excel doesn't provide a way to require input, but you can create a macro to do the task.
Rounding in Results
Rounding is a fact of life when it comes to using formulas in a worksheet. Sometimes that rounding can be a bit confusing, however. This tip examines one such rounding oddity to explain how rounding occurs in formulas.
Scroll Wheel Doesn't Work when Editing
Using the mouse's scroll wheel can help improve how you edit information in a worksheet. Here's how to make sure that the scroll wheel works as you expect.
Searching for All
When you are working on a worksheet (particularly a large one), you may want to search for and possibly copy information from many cells all at the same time. This is easier said than done in Excel, as this tip illustrates.
Want to select only the formulas in your worksheet? It's easy to do using the Go To Special dialog box.
Selecting Noncontiguous Ranges with the Keyboard
It's easy to select non-contiguous ranges using the mouse, but may seem more daunting if you are simply using the keyboard. Here's how you can do it, using just the Go To dialog box.
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 Default for Shifting when Inserting
When you insert cells into a worksheet, Excel needs to know which direction it should shift the displaced cells. If you always want to shift the cells in a particular direction, you can skip the process of answering Excel's questions by using the macro introduced 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 are a couple of ways you can check or limit the length of what goes into a cell.
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 Information into Rows
Got too much information in a single cell? Here's how you can use a macro to pull apart that information and put it into cells in the way you want.
Stopping the Deletion of Cells
You can delete cells from a worksheet, and Excel will move the remaining cells either to the left or upwards. Deletions, however, can mess up the design of a worksheet. If you want to stop people from deleting cells, you'll appreciate the overview in this tip.
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 are some ways you can accomplish the data transformation, if you desire.
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.
Uncovering and Removing Links
Excel allows you to reference data in other workbooks by establishing links to that data. If you later want to get rid of those links, it can be tedious to track them all down. Here are the majority of the places you should look for links in your workbook.
Entering data in a worksheet can be time consuming. One of the tools that Excel provides to make entry easier is AutoComplete, described in this tip.
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 AutoComplete with Disjointed Lists
AutoComplete can help you to more quickly enter information in a worksheet. How it works, behind the scenes, can affect how helpful it is for data entry.
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.
Using the Fill Handle from the Keyboard
The fill handle can save a huge amount of time when you are editing a worksheet. If you are really good at using the keyboard but dislike using the mouse, you may want to use the capabilities of the fill handle without removing your hands from the keyboard.
Using the Same Range Name on Different Worksheets
Defined names can be a great boon when working in a worksheet. Usually names are available throughout an entire workbook, but you may want to limit the scope of a name so that it is only applicable within a specific worksheet. Here's how to do it.
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 you tons of time in figuring out such intermediate results.
Viewing Your Work Full-Screen
Want to use the maximum space possible for displaying information on screen? You'll want to learn how to use the full-screen display mode of Excel, then.