Tech

Guides

Business Applications ▪ Database Management ▪ Disaster Recovery ▪ Enterprise Servers & Storage ▪ IT Security ▪ Java ▪ Microsoft Office Suite ▪ Network Admin ▪ Open Source ▪ SMB ▪ SOA ▪ Tech Management ▪ Software/Web Development ▪ Windows Server ▪ Wireless Tech

Microsoft Office Suite TechGuides

Spot invalid imported data with help of custom Excel function

By Mary Ann Richardson and Susan Harkins, Special to ZDNet Asia
Thursday, May 28, 2009 11:59 AM

Anytime you have to manually review worksheet data to look for errors, something can slip through. Here's a handy safety net: a user-defined function that will flag one type of invalid data.

Microsoft Excel

Spot invalid imported data with the help of a custom Excel function

You're getting some unexpected data type errors from your analysis of imported data. Examination of the first few cells in the worksheet shown in Figure A confirms that some data has been entered as text rather than numbers.

Figure A

Rather than manually examine each cell, you can create a function that will mark the invalid cells for you. Follow these steps:

Open the worksheet containing the imported data and press [Alt] + [F11].
Go to Insert | Module.
At the prompt, type the following code:

Function ISNUM(range) As Boolean
If range.Value <> "" Then
    ISNUM = IsNumeric(range.Value)
    End If
End Function

Press [Alt] + Q (Figure B).

Figure B

Select the cells containing the imported data. (For this example, we selected A1:A6.)
Go to Format | Conditional Formatting, click the drop-down arrow, and select Formula Is. In Excel 2007, select the Home tab, click Conditional Formatting in the Styles group, click New Rule, and select Use A Formula to Determine Which Cells To Format.
Enter the following formula in the formula text box:

=ISNUM(A1)=True (Figure C).

Figure C

Click the Format button.
Click Green under Color.
Click OK twice.

Now the cells that contain numerical data are marked in a green font (Figure D). All others need to be reentered or deleted.

Figure D

Microsoft Word

Quickly jump from table to table in a Word document

If a document contains several tables, you might want to move from table to table without scrolling through all the text between them. The good news is, you can. Actually, there are two ways to jump from one table, avoiding text, to another in a Word document: Go To and Select Browse Object.

Using Go To (or [F5]) is probably the best known method, but this method leaves the Find And Replace dialog box on top of the document, which might obscure the text. You can always move it, but it's still a bit of a nuisance. To use Go To to move from table to table, do the following:

Press [F5].
In the resulting Find And Replace dialog box, choose Table from the Go To What list.
Click Previous or Next, appropriately.
Repeat step 3 until you’re done.
Click Close to close the Find And Replace dialog box.

If the dialog box is a problem, use the Select Browse Object instead. This button is at the bottom right of your screen, just below the vertical scroll bar. There's a small round dot with arrows just above and below. You might find this method of jumping from table to table a bit easier:

Click the Select Browse Object button to display a palette of objects.
From the palette, choose Browse By Table.
Click Next (the down arrow below the button) to select the next table in the document or click Previous (the up arrow above the button) to select the previous table.

Either method is better than scrolling through pages of text, but the Select Browse Object doesn't display a dialog box on top of the document. You can use this tool to find several types of objects, not just tables.

Microsoft Powerpoint

How to animate a PowerPoint chart or table

Animation, used correctly, can make text or a graph really stand out. But you may not realize that you can animate a chart or table.

For instance, you might want to highlight individual product or regional totals using a graph that analyzes them all as a group. Animating a table by displaying additional information for each row--row by row--can be effective.

To add animation to a chart, do the following:

Click Slide Show on the Standard toolbar and choose Custom Animation. In PowerPoint 2007, click the Animations tab and choose Custom Animation.
Select the chart.
To add a Wipe effect, when PowerPoint displays the chart, click Add Effect, choose Entrance, select Wipe, and click OK. (If you don't see the Wipe animation, choose More Effects.)
By default, PowerPoint will add the animation to the chart as a whole. To animate elements, right-click the appropriate animation item in the task pane and choose Effect Options.
Click the Chart Animation tab.
Choose the appropriate object from the Group Chart drop-down control to animate the chart as a single object, by series, by category, or by the elements in the series or category. Click OK.

You can animate the chart as a whole or individual elements, and most of the time that's adequate. However, you can't use different animations for each element. In addition, you can't easily animate individual rows of a table this way.

To animate a table or to provide more enhanced animation to a chart, upgroup the chart or table first. Then, you can animate each element individually. To ungroup a chart or table, do the following:

Right-click the chart or table and choose Grouping.
Select Ungroup.
When asked if you want to convert the chart or table, click Yes.

Use caution when deciding to ungroup a chart or table. Once you do so, PowerPoint will no longer treat the combined elements as a chart or table. You won't be able to update an ungrouped chart by changing its underlying data and you might lose special formatting.

You can't ungroup a chart or table in PowerPoint 2007.