Topic: Editing

The following are additional topics related to the category listed above. A bracketed number after the topic indicates how many articles are related to that subject.

Tips, Tricks, and Answers

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

A Fast Find-Next Does the Find dialog box get in your way when you are looking for information in a worksheet. (I know it gets in my way!) This tip discloses a great way you can still find stuff, even without the Find dialog box visible. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Adjusting a Range's Starting Point When you hold down the Shift key and use the Arrow keys to select a range of cells, you may not know that you can expand your selection in any direction you desire. Using the ideas in this tip, you can make your selecting techniques quite a bit more powerful. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Cell Movement After Enter Most people don’t give a second thought as to which way Excel moves after you press Enter. Excel thinks about it a lot, however, and allows you to tell it which way to go. This tip describes how to pick the direction you want and how to configure Excel properly. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Checking for a Value in a Cell Sometimes it is necessary to know whether a cell contains a numeric value before you perform some operation on the cell contents. This is where the ISNUMBER function comes in handy, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. 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

Combining Multiple Rows in a Column When importing data from other programs, you often end up with information split across separate rows of a worksheet. This tip explains a couple of ways you can combine that information into just the cells you want. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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 Text to Values Got some imported data that is being improperly parsed as text? This tip explains how you can convert the text so you can use it properly. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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 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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Creating Selections Most things you do in Excel—from formatting to copying and creating formulas to deleting cells—requires that you make selections in a worksheet. This tip explains a couple of quick ways you can build your selection using the F8 key. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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.) Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Default Cell Movement when Deleting When you delete cells from a worksheet, you may want the surrounding cells to “fill in” the deleted spot from a particular direction. This tip provides a couple of ways you can have a default movement when doing your deletion. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. 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 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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Editing Individual Cells Excel is flexible in how it allows you to edit information in a cell. In fact, there are several different ways you can edit it. This tip covers your editing options and different shortcut keys you can use. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Enhanced Filling The AutoFill tool in Excel is very handy to use. If you know the little trick disclosed in this tip, you can make AutoFill even handier. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Ensuring Rows and Columns are Empty Need to delete some rows or columns, but you aren’t sure if they are empty? (Deleting data you may need is a bad thing.) This tip shares a technique you can use to quickly find out if a column contains information you may need to keep. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Entering the Current Time Want to enter the current time into a cell? It’s easy with the shortcut described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Finding Unknown Links There are several things to try when finding unknown links in Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Fixing the Decimal Point If you've got a lot of similar data to enter (such as dollars and cents), you can be more productive if you tell Excel to assume a decimal point at a certain place in your entry. This tip explains how to configure Excel for a decimal point at a fixed position. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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

Getting Help when Entering Functions Need a quick memory jog when entering a worksheet function? Here’s a shortcut that will be invaluable. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Getting Rid of Spaces in Cells Getting rid of stray spaces that clutter up otherwise empty cells can be a bother. This tip explains how you can use a couple of different techniques to make the task easier than it might otherwise appear. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. 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 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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. 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

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Moving and Copying Cells As you work with data in a worksheet, you’ll undoubtedly need to move cells from one place to another or copy them. It’s easy to do, once you know how. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Picking a Contiguous Range of Cells There are a variety of ways to pick a range of cells in Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Quickly Filling a Column Once you’ve developed a formula for use in a column, you are faced with the task of copying that formula into all the cells of the column. This tip explains a quick and easy way you can do that. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Ranges on Multiple Worksheets Formulas can refer to ranges of cells, but they can also refer to ranges of worksheets. You may need to create such a range reference if you want to operate on the same cell on different worksheets in a workbook. It’s easy once you know how! Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Reference Shortcut Formulas can utilize both absolute and relative cell references. Excel includes a shortcut, F4, that allows you to cycle through all the various permutations of absolute and relative references for an address. This tip explains how the shortcut works. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Removing Cells from a Selected Range Selecting a large range of cells is easy; unselecting some of the cells in that range is not so easy. In fact, there is no click or shortcut you can use to unselect cells in the range. Instead, you must turn to a macro or a third-party solution, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Selecting a Word How to select individual words in an Excel cell. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Selecting Formulas Selecting all the formulas in a worksheet is easy, if you know how. All you need to do is display the Go To Special dialog box and specify you want all of the formulas selected—quick and simple! Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Selecting the Current Region A region of data is any group of cells bounded by blank columns or rows. Excel provides a shortcut key, described in this tip, that allows you to select all the cells in the region surrounding the active cell. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. 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

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Transposing and Linking Using different ways to paste information with the Paste Special option. 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

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Turning Off Insert Options When you insert a row, column, or cell, Excel displays a helpful Insert Options button. To turn this button off so that it is not displayed again, follow the steps in this tip. Microsoft Excel versions: 2002 | 2003

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. Microsoft Excel versions: 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Understanding Names An explanation of naming ranges or cells and how they can assist in Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Undoing an Edit The many ways to undo an action in Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using an Input Mask Do you need to enter times into a cell, and you want to do it without the necessity of entering the colons? This tip explains how you can create an “input mask” that can help, but such ease doesn’t come without drawbacks. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using Overtype Mode Using overtype mode in Excel cells. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Viewing Formula Results When working with formulas, it is sometimes helpful to see the intermediate results of each operation. You can do this easily by using the F9 shortcut key while editing the formula. This tip explains how this works and why you might want to use it. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

More Topics

Tips, Tricks, and Answers

Perplexed by Pivots?

Helpful Links

Great Info!

RSS Feeds