Editing
There are many ways you can edit the data in your Excel sheets. Excel provides plenty of tools and features not only to edit your document, but also to customize the editing process. The following articles discuss techniques to edit efficiently in Excel and how to utilize the Review tab of the ribbon.
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 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.
Changing References in a Lot of Defined Names
Need to change some cell references in your defined names? Changing one or two is easy; changing dozens is a good deal harder. This tip looks at two fast ways you might use to do the necessary edits.
Changing the Default Paste Mode
Don't like the way that Excel pastes when you press CTRL+V? Here are some ways that you can modify the normal paste mode used by Excel.
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.
Closing Up Cut Rows
When you cut and paste rows using Ctrl+X and Ctrl+V, Excel leaves empty the rows where the cut information was previously located. There are ways to close up those previously used rows, however. Those methods are described in this tip.
Colors No Longer Work
It can be disconcerting if you are editing a workbook and can no longer change colors for cells in the workbook. This tip presents some ideas as to what might cause this condition and provides ways to correct it.
Combining Columns
Need to concatenate the contents in a number of columns so that it appears in a single column? Excel has no intrinsic way to do it, but a macro can make quick work of the task.
Combining Multiple Rows in a Column
Do you need to concatenate the contents of a range of cells in the same column? Here's a formula and a handy macro to make quick work of your editing needs.
Concatenating Ranges of Cells
Putting the contents of two cells together is easy. Putting together the contents of lots of cells is more involved, as discussed in this tip.
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.
Controlling Formula Reference Jumping
When you select a cell, you typically do so to see what the cell contains to to make changes to the cell. How Excel behaves when you select a cell can sometimes be confusing. This tip examines how to understand what is happening if you select a cell and Excel jumps to a different cell.
Controlling the Automatic Copying of Formulas
When you add a new row of data to the bottom of the data of a worksheet, Excel may (or may not) copy formulas downward to the new row you are adding. You can control this behavior, as described 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.
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 and Pasting Non-Contiguous Ranges of Cells
Excel makes it easy to copy and paste a range of cells. Easy, that is, unless the range isn't contiguous. If you have a need to copy and paste such data, the only way to do it is through the use of a macro, as described in this tip.
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 from the Task Bar
When you select cells in a worksheet, there is a good chance that if you glance at the Task Bar, you'll see some aggregated information about your selection. If you want to copy some of this information, you're out of luck because Excel doesn't provide a way to do it. You can use the workarounds in this tip, though, to get the info you need.
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.
Copying Subtotals
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.
Counting Words
Do you need to know how many words are in a range of cells? Excel provides no intrinsic way to count the words, but you can create a quick macro that will provide the figure.
Creating Selections
Want a really easy way to create a selection of a group of cells? Discover how to use the Extend key to make this task easier than ever.
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.
Defaulting Input to Negative Values
When entering many negative values in a worksheet, you could save time if you didn't need to enter the minus sign for each value. This tip shows you a few ways you can eliminate the need to use the minus sign.
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 All Rows Except for the End of Month
If you use a worksheet to track day-to-day data, you might want to delete all of the data except the data for the last day of each month. This tip looks at a few ways you can tackle such a data-reduction task.
Deleting Duplicate Columns
Have 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.
Deleting Rows before a Cutoff Date
If you are using Excel to work with data that is date-centered, you may want to delete some of the data before a specific cutoff date. This tip provides a few ways you can easily perform the task.
Deleting Rows Containing Struck-Through Text
Excel makes it easy to delete rows in a worksheet, but it can be more difficult to figure how to delete rows if you only want to delete the ones that contain struck-through text. This macro examines a couple of ways you can perform the deletion manually, along with a macro to make short work of the task.
Deleting Stubborn Links
Deleting unwanted links in a workbook can be a challenge, particularly if you are not sure how those links got there. This tip looks at some ideas for not only deleting the links, but also how to prevent their addition in the first place.
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? Unfortunately, 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 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 divide 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.
Enhanced Filling
Using the AutoFill feature of Excel is very handy. If you want to expand the utility offered by the feature, all you need to do is change how you click the AutoFill handle.
Ensuring Rows and Columns are Empty
Before you go about deleting rows and columns helter-skelter, it is a good idea to determine if there is anything in the row or column you are going to delete. Here's the quick way to figure out whether a row or column is empty.
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 Characters with Diacritical Marks
Entering characters that use diacritical marks is easy as pie in Word and some other programs. Not so in Excel, it takes a bit more work. This tip, however, provides you with several different ways you can get the exact characters you need.
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.
Error Generated when Trying to Copy a Worksheet
How successful you are in copying information in Excel depends on lots of issues. This tip examines how those issues can affect copying all the cells in a 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 Cells Containing Only Spaces
If you have a worksheet that contains a bunch of cells that contain nothing but spaces, you may be looking for a way to delete all those cells that are otherwise empty. This tip provides two quick approaches to cleaning up your data a bit.
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.
Identifying Duplicates
Do you need to flag duplicate values in your data? This tip shows three different ways you can do the flagging you need.
Identifying Unused Named Ranges
Named ranges can make it easier to refer to ranges of cells in an understandable way. If you want to delete named ranges that may no longer be used, then you'll be interested in the ideas presented in this tip.
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.
Ignoring Special Characters when Double-Clicking
If a word contains a special character within it, Excel actually thinks the single word is two words. This tip examines this behavior and presents a workaround that allows you to easily select the entire word.
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 the 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.
Limiting the Scope of the Undo Command
As you make edits in Excel, the program remembers your actions so that you can later undo them. If you have multiple workbooks open, you may want to control where the actions are undone. This tip looks at some ways you can affect how the Undo command works.
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.
Mouse Scroll Wheel Doesn't Work when Editing Formulas
Using your mouse to select cells for inclusion in a formula can be an exercise in futility on some systems. Here's why that is and, possibly, what you can do about it.
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.
Moving More than One Cell to the Right
Enter some data in a cell, and Excel typically moves to the next cell when you press Enter. If you want to move more than one cell, then you need to apply one of the techniques detailed in this tip.
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.
Numbering Filtered Rows
When you filter rows in your data, you may want to later number those rows. This tip provides a variety of ways you can get the numbering you need.
Pasting and Matching Destination Formatting
Sometimes, getting numbers from a program into Excel, using the formatting you want, can be a challenge. This tip examines several ways you can approach the challenge.
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.
Previous Column Values in a Drop-Down List
Want a quick way to add non-unique values into a column? You probably know you can do this by starting to simply type in the column, but there are other ways to speed up your data entry, as well. This tip looks at those techniques.
Proper Case Conversion with Exceptions
The PROPER worksheet function allows you to change the case of text so that only the first letter of each word is capitalized. This may not be exactly what you want, however. This tip looks at a couple of ways you could modify the way that PROPER does its work.
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 Spaces
Need to get rid of spaces in a range of cells? There are two ways you can approach the task, as described here.
Repeating a Pattern when Copying or Filling Cells
The fill tool can be a great help in copying patterns of information in a column. It isn't so great, though, when the pattern isn't easy for Excel to discern. This tip shows some ideas on how you can fill a range with one of those types of patterns.
Requiring Input
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.
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.
Seeing Formula Cells on Different Worksheets
Edit a cell in the Formula bar, and Excel highlights the cells referenced in that formula. If you want to see cells referenced on different worksheets, though, you'll need to rely on the Formula Auditing capabilities of Excel.
Selecting Formulas
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.
Separating a Date into Component Columns
Do you need a way to split dates out into the individual parts that make up that date? This tip provides two easy ways you can get the splits you need.
Separating Text by Color
Excel allows you to use multiple colors to format the text in a cell. If you want to later separate that text to different cells based on those colors, you can be in for a challenge. This tip looks at two ways you can do the text splitting you need.
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.
Setting an Upper Threshold for a Cell
Do you want to limit what can be entered into a particular cell in your worksheet? Here are three separate ways you can approach the task at hand.
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.
Showing Text when a Cell is Empty
Wouldn't it be great if you could have Excel display some text in a cell only when that cell is empty? Unfortuantely, Excel doesn't provide that capability, but there are some workarounds provided in this tip.
Splitting Cells to Individual Columns
When you are working with data created by other systems or other people, you often need to convert the data into something you can use. This tip looks at the different ways you can start with a bunch of combined data and then split that data out into individual columns.
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.
Splitting Sentences to Cells
If you have a lot of text in your workbook, at some point you might want to split out sentences into individual cells. This process is a bit more complex then you might think at first. This tip looks at ways you can do the data extraction.
Stopping a Conditional Formatting Rule from Breaking into Smaller Ranges
When you paste information into a row that is conditionally formatted, you may end up messing up the rules applied to surrounding rows. If you don't want this to happen, Excel provides a number of ways you can paste your information.
Stopping Feet and Inches from Converting to Dates
When pasting information into a worksheet, Excel tries to helpfully convert that information. This can cause undesired results. This tip looks at how to make sure the pasted information is treated as plain text.
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.
Swapping Two Cells
If you need to swap the contents of two cells in your worksheet, Excel provides a number of ways you can approach the task. This tip provides at least four approaches you can try.
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?
Synchronizing Lists
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.
Three-Dimensional Transpositions
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.
Trimming Off All Spaces
The easy way to get rid of spaces at the beginning or end of a cell's contents is to use the TRIM function. Unfortunately, this function doesn't get rid of all possible 'space' characters you may run across.
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 to the paste operation. If you don't want to see those options, here's how to turn the feature off.
Typing a Schwa Character in Excel
How you add special characters to Excel can differ from how you add them in other Office programs, such as Word. This tip looks at one such difference and how you can handle it.
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.
Understanding Auto-Population of Cells
Auto-population of your formulas can be a useful tool when you are adding data to your worksheets. It would be even more useful if there was a rhyme or reason as to how it did its work.
Understanding AutoComplete
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 Actions in Only the Active Workbook
When you undo actions within Excel, those steps you undo may affect the multiple workbooks in which you've been working. If you want the undo actions to affect only a single workbook that is open, you may be out of luck, as 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 the Contents of a Very Wide Cell on Demand
Each cell in a worksheet can hold quite a bit of information. If you want to see the information in the cell without the need to make the cell wider, you'll appreciate the tips presented here.
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.
Weird Pasting Behavior
The Clipboard is integral to editing data in your worksheets. What happens, though, when the Clipboard doesn't allow you to paste information directly? This tip presents an idea or two that can help you track down the culprit.
When Clicking a Cell, Excel Jumps to a Different Cell
When you click on a cell, you expect the cell to be selected. What happens, though, if you are instead taken to an entirely different cell after you click? This tip looks at some potential causes for this behavior.
See Go Deeper at the top of the left column for related topics...