Differences between Clearing and Deleting

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.

As you are editing your Excel workbooks, one of the most common actions to take is to clear or delete cells. Clearing cells means erasing everything within them, whereas deleting actually deletes the entire cell (or cells). This may sound like an odd distinction, but it is not really. If you compare the cells in your worksheet to paper cups, you can either remove a cup entirely (deleting the cup) or simply remove any contents from the cup (clearing the cup). When you delete a cell, the other cells around that cell move to fill where the cell used to be.

To clear the contents of cells you have selected, display the Home tab of the ribbon and click the Clear tool in the Editing group. When you do, you will see a submenu asking what you want to clear. Your choices allow you to clear any of the following:

· Clear All. Everything related to the cell except the cell itself.

· Clear Formats. Any formatting applied to the cell.

· Clear Contents. The information in the cell. Any formatting and comments remain in place.

· Clear Comments. The comments attached to the cell.

You should make your selection based on what you want cleared and then choose the appropriate menu item. If you like, you can also use the Del key to clear a cell. When you use this shortcut key, it is the same as choosing Contents from the Clear submenu.

Deleting is done by selecting the cells you want to delete and then choosing Delete from the Edit menu. When you do this, Excel attempts to determine how the remaining cells in the worksheet should be moved to close up the hole that will be created by deleting the cells. If it can figure it out (for instance, if you are deleting an entire row or column), then the operation is completed. On the other hand, if it is not obvious how the remaining cells should be moved, Excel displays the Delete dialog box, which allows you to specify whether cells should be moved up or to the left.

Posted courtesy of Tips.Net

What is the quickest way to...

Copy a formula down the whole column?
Select the cell that contains the formula and double-click the small square
on the bottom, right-hand corner of the cell. (It is called the Fill
Handle. Your cursor turns into a skinny Plus sign when it is positioned
over it.) Your formula will be automatically copied over everything below
it, as far down as there is data. (If there is no data in the same column,
the formula is copied as far down as there is data in the columns next to
it.)

Fill in a series of data?
Enter at least two items in a range of cells. Select those cells as a
group and drag the group's fill handle down to fill the column (or drag to
the right to fill the row). This works with many dates, month names, and
numbers, as well as any custom lists you have set up.

Enter the same value into multiple cells?
Select the range of cells, type the value, and press Ctrl+Enter.

Enter values in a specific range of cells?
If you are in the habit of using the Tab key when you enter data, and you
only want to enter data into one specific range of cells, first select the
cells in which you want to enter data. Each time you press Tab, you will
move to the next cell. In other words, if you select cells A1 through D4,
enter data into A4, and press Tab, your cursor moves to B1, staying within
the selected range.

Posted courtesy of Tips.Net

Using Conditional Formatting to Shade Rows

If you need to shade alternating rows in a data table, you'll want to examine how you can accomplish the task with conditional formatting.

If you haven't tried out the conditional formatting features of Excel before, they can be quite handy. One way to use this feature is to cause Excel to shade every other row in a table. This is great when you have a particularly wide table, and you want to make it a bit easier to read on printouts. Simply follow these steps:

1. Select the table whose alternate rows you want to shade.

2. Make sure the Home tab of the ribbon is displayed.

3. Click the Conditional Formatting tool. Excel displays a series of choices.

4. Click New Rule. Excel displays the New Formatting Rule dialog box.

5. In the Select a Rule Type area at the top of the dialog box, choose "Use a Formula to Determine Which Cells to Format"

6. In the formula space, enter the following formula:

=MOD(ROW(),2)=0

7. Click on the Format button. Excel displays the Format Cells dialog box.

8. Make sure the Fill tab is selected.

9. Select the color you want used for the row shading.

10. Click on OK to close the Format Cells dialog box.

11. Click on OK to close the New Formatting Rule dialog box.

Those familiar with Excel may wonder why anyone would use conditional formatting to highlight different rows of a table when you can use the table formatting feature (available in the Styles group of the Home tab of the ribbon) to accomplish the same thing. The reason is simple—using conditional formatting provides much more flexibility in the formatting applied as well as in the interval of the rows being shaded.

Posted courtesy of Tips.Net

Printing Different Parts of the Same Worksheet

Do you have a worksheet from which you need to print only portions of the data available? There are two ways you can control what gets printed—by using a macro or by using custom views.

If you have multiple areas that you print in a worksheet, you may get tired of repeatedly specifying what area you want to print and then printing it. Such a task is well suited to being done with a macro. The macro can take care of specifying a print area and then actually printing the information.

For instance, let's assume that you have two print ranges defined in your worksheet: Range1 and Range2. Further, Range1 should be printed in portrait orientation and Range2 should be printed in landscape orientation. The following macros can be used to print each of the print ranges:

Sub PrintRange1()

ActiveSheet.PageSetup.PrintArea = Range("range1").Address

ActiveSheet.PageSetup.Orientation = xlPortrait

ActiveWindow.SelectedSheets.PrintOut

End Sub

Sub PrintRange2()

ActiveSheet.PageSetup.PrintArea = Range("range2").Address

ActiveSheet.PageSetup.Orientation = xlLandscape

ActiveWindow.SelectedSheets.PrintOut

End Sub

These are very simple macros, but you get the idea—all you need to do is set up the print job in the macro, and then print from the macro itself. You could even attach the macros to toolbar buttons or to a menu option.

If you prefer to not use macros, you could also use the custom views feature of Excel. Simply set the print area, orientation, margins, and other settings desired. Then define this as a custom view. To define a custom view, follow these steps:

1. Make sure the View tab of the ribbon is displayed.

2. In the Workbook Views group, click Custom Views. Excel displays the Custom Views dialog box. (Click here to see a related figure.)

3. Click on Add. Excel displays the Add View dialog box. (Click here to see a related figure.)

4. Enter a descriptive name for the view you are defining.

5. Make sure the Print Settings check box is selected.

6. Click OK.

You can continue to define and save additional views, as desired. Your custom views are saved with your workbook, and you can later use them to print what you want. (Just display the custom view and then print your worksheet.)

Posted courtesy of Tips.Net

Changing a Link's Source

If your worksheet is linked to data in other worksheets, you may need to change the link from time to time. Here's how to do it:

You may already know that Excel allows you to establish links between your workbook and other objects, such as graphics, Word documents, and other items. (regular links, not hyperlinks.) There may come a time when you wish to change the links. For instance, you may have a picture linked in your workbook, and you want to change the location of the picture (to a different directory or drive). Excel allows you to change your link so it can point to the proper file as the source of the object in the workbook.

To change links in this manner, follow these steps:

1. Click the Office button, then click Prepare | Edit Links to Files. Excel displays the Edit Links dialog box.

2. Select the link you want to change.

3. Click on Change Source. Excel displays the Change Source dialog box.

4. Use the controls in the dialog box to locate and select the file that will serve as the new source of the link.

5. Click Open. Excel updates the link to reflect the new source you specified.

6. Make any additional link changes necessary by repeating steps 2 through 5.

7. Click on OK.

I hope this is helpful! Let me know if you have questions on Excel or any other MS Office product!

Posted courtesy of Tips.Net