A common occurrence that plagues imported data is the presence of unprintable characters. For
instance, text might contain control characters that indicate carriage returns or line feeds.
When viewed onscreen, they often appear as small squares within cell entries.
You can
easily remove such characters using the CLEAN function.
Let's say your data is in cell
A1. In cell A2, simply enter the formula =CLEAN(A1) This returns a string without the offending characters.
You'll usually want to save the results as static values so that you can eliminate the
original inappropriate entries. To do so, select cell A2 and choose Edit/copy from the menu bar. Then,
choose Edit/Paste Special from the menu bar, select the Values option button, and click OK.
| Return
to top |
| Use a graphic file as a worksheet background |
| You can easily replace the default white background that Excel applies to all worksheet
cells with a picture. To do so, choose Format/Sheet/Background from the menu bar. Then, locate and
select a graphic file. Most common file types are supported, including BMP, TIF, JPG, and GIF. Click the
Insert button to apply the background. Excel displays the image in a tiled format. Note that the
background is only visible onscreen - it won't appear on printouts. To remove a background graphic,
choose Format/Sheet/Delete Background from the menu bar.
|
| Return to top
|
| Overcoming problems when concatenating dates |
When you combine multiple data items as a string, using both the CONCATENATE function and
the ampersand (&) operator, you may be surprised at the result when you have a date value among the
items you're concatenating. For example, open a new worksheet, select cell A1 and enter the following:
"Today's Date Is: ".
Then, select cell B1 and enter:
=TODAY()
Now, select cell A5 and enter the following:
=A1 & B1
Instead of the result you might expect, you find a number in
place of the date. That's because Excel treats dates and times as
serial numbers. To see the number in a date structure, you need
to format it appropriately when it gets converted to a string. To
do so, use the TEXT function. In cell A6, enter the following formula:
=A1 & TEXT(B1,"mm/dd/yyyy")
The result now incorporates B1's value formatted as a date.
|
| Return
to top
|
| Print PivotTable groups on separate pages |
| A PivotTable can group data summaries using multiple row fields. For instance, a PivotTable
report might provide sales revenue data summarized by several nested levels of detail. At the top level,
the PivotTable summarizes by state, then by county, then city, then individual store, and finally by
employee. When a PivotTable incorporates multiple row fields, you can print groups on their own pages. For instance, you may want to print a new page whenever a new state value is encountered. To set up this
printing option, double-click on the appropriate field. Then, click the Layout button. Select the Insert Page
Break After Each Item check box and click OK on the open dialog boxes. Note that you can enable this setting
for all but the lowest level of grouping fields (the rightmost row field).
.
|
| Return
to top
|
| Add a user-friendly button to run a macro in your workbook |
| There are several ways to access your macros, but some of them are better suited to end users. For
instance, it may be easiest for end users who don't know as much about Excel to run a macro by clicking a button. It
only takes a few seconds to make this possible. First, view the Forms toolbar by choosing View/Toolbars/Forms
from the menu bar. Then, click the Button icon and click on your worksheet where you'd like the button to appear. In
the Assign Macro dialog box that displays, select a macro from the Macro Name list box and click OK. Or, if you've
yet to write the macro, you can enter the macro's name in the text box, click the New button and then create the
macro when the Visual Basic Editor opens.
|
| Return
to top
|
Sandy
Pratt
is a director of 4-consulting,
click
here to view his profile. |
