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 Putting each line of a text string on its own line is easy if you know the trick. This tip explains how you can “break” text so that each word appears on its own line within a cell. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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 Does a particular cell contain something, or is it completely empty? Such questions are the minutia of which worksheets are made, and you are left with figuring out exactly how to determine the desired information. This tip explains how you can examine a cell, with formula, to determine if it is empty or not. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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 The ampersand (&) operator and the CONCATENATE function are great for combining the contents of a limited number of cells. If you want to combine the contents of a range of cells, you must resort to a user-defined function, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Contingent Validation Lists Setting up data validation lists that are contingent on each another validation list. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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 You can edit formulas and easily convert relative to absolute values. The task becomes very mundane and bothersome if you need to perform the conversion with a large number of formulas. This top contains a macro that can make short work of your formula conversion needs. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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

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 Excel is not a word processor, but you can count the words in a group of cells. The way to do this is with a macro, as outlined in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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 Formulas in Excel can, at times, be very long. By judicious use of Alt+Enter, you can make your formulas easier to understand and less overwhelming. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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

Displaying a Hidden First Row If someone has hidden the first rows of a worksheet, figuring out how to unhide them can throw you for a loop—unless you know the handy secret in this tip. How to select those hidden rows and make them reappear, just as you want. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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 What really happens when you enter information into a cell? Well, Excel tries to make sense of what you entered—it tries to parse the information. If you enter a numeric value, exactly what Excel does is based on a well-defined set of rules. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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 Remembering the different parameters you can use with a worksheet function is sometimes difficult, particularly if the function is one you use only periodically. This tip describes a handy help feature provided by Excel that makes recalling the parameters very easy. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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 How you inserting the symbol for a radical is not immediately obvious to may Excel users. This tip describes a couple of ways you can not only insert the symbol itself, but also add the symbol to the beginning of a cell format. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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

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 Do you want to reference whatever is stored in the last-used cell in a column? How you go about accessing that information in a formula depends on whether the column contains blank cells or not. This tip discloses the various ways you can access the desired info. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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 Do you need to remove spaces from textual data in a worksheet? This tip presents two methods, one of which uses a macro, to get rid of those spaces. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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 options, a feature new to Excel 2002 and Excel 2003, can be distracting to some Excel users. Fortunately you can turn off the feature by following the steps outlined in this tip. Microsoft Excel versions: 2002 | 2003

Typing Check Marks into Excel There are a few ways to get check marks into an Excel worksheet as told in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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