Ms Office Tune Up

When analyzing a data set, it’s often handy to rank where a certain value falls within the data. Excel has functions that enable you to calculate both the ordinal and percentage rank of each value in a set.

For the ordinal (first, second, third, and so on) rank, you use the RANK() function:

RANK(number, ref[, order])

number The number for which you want to find the rank.

ref A reference, range name, or array that corresponds to the set of values in which number will be ranked. (Note that ref must include number.)

order An integer that specifies how number is ranked within the set. If order is 0 (this is the default), Excel treats the set as though it was ranked in descending order; if order is any nonzero value, Excel treats the set as though it was ranked in ascending order.

For example, if a test result is in cell C3 and the full results are in the range C5:C35, then the following formula returns the ordinal rank of the test result:

=RANK(C3, C5:C35)

The ordinal rank is only occasionally useful. In data analysis, you’re more likely to work with the percentage rank. This is useful when you want to know what value in the set cor­responds to what percentile. For example, if you want to know what test scores fall in the top 10 percent, you’d calculate the 90th percentile, and every score above that is in the top 10 percent. To calculate the percentage rank, you use the PERCENTILE() function:

PERCENTILE(array, k)

array A reference, range name, or array of values for the set of data. Read the rest of this entry »

Popularity: 4% [?]

If you want other people to view your presentation without you, the easiest way is to either send the presentation file via email as an attachment or to place the presentation file in a shared network folder. The users can then open the presentation in PowerPoint and use the standard Ribbon commands or keyboard methods to run the slide show.

The drawback with this method is that the other users have the presentation open in PowerPoint, so they can make changes to it. If you don’t want that to happen, one solution is to set up the presentation with a modification password. This means that the user cannot change the presentation in any way without providing the correct password. Here are the steps to follow to set this up:

1. Choose Office, Save As (avoid the arrow) to open the Save As dialog box.
2. Choose Tools, General Options to open the General Options dialog box.
3. Type a password in the Password to Modify text box.
4. Click OK. PowerPoint displays the Confirm Password dialog box.
5. Type the password again and click OK.
6. Click Save. PowerPoint asks if you want to replace the existing file.
7. Click Yes.

Read the rest of this entry »

Popularity: 6% [?]

You saw in the previous section that you use the EditTime field to return the total editing time for a document. If you bill by the hour based on the amount of time you have worked on a document, you might want to keep track of how much money you’ve earned so far. If you earn, for instance, $40 per hour, the following formula displays your current billable earnings:

{ = { EDITTIME } / 60 * 40 }

The EditTime result is given in minutes, so you have to divide by 60 to get the number of hours. You then multiply that result by 40 to get the earnings.

To ensure accurate billing, you may want to use the ROUND function to round the result to the nearest dollar:

{ = ROUND( { EDITTIME } / 60 * 40, 0) \#$0.00 }

This formula field also uses a numeric format to display the result with a dollar sign and two decimal places, as shown in Figure 1.

Figure 1 A formatted formula field that calculates the billable charge based on the current EditTime result.

Technorati Tags: calculte bill time charges, word 2007

Popularity: 3% [?]

In the previous section, you learned how to navigate to a hidden slide by using the Go To Slide command or by clicking a text hyperlink or an action button. That works well, but in almost all cases, when you are done with the hidden slide, you should return to the slide you previously viewed. Again, PowerPoint offers two methods:

• Display the slide show menu and choose Last Viewed.
• Set up an action button that, when clicked, takes you to the previously viewed slide.

Here are the steps to follow to create such an action button:

1. Display the slide on which you want to display the action button.
2. Choose Insert, Shapes to display the Shapes gallery and then click an icon in the Action Buttons section. (In this case, a suitable shape is the Back or Previous button.)
3. Click and drag your mouse on the slide to create the action button. When you release the mouse button, PowerPoint displays the Action Settings dialog box.
4. Click the Hyperlink To option.
5. In the list under the Hyperlink To option, click Last Slide Viewed.
6. Click OK.

Technorati Tags: linking last slide, powerpoint 2007

Popularity: 6% [?]

The total amount of time that a document has been edited is useful for freelancers, lawyers, consultants, and other professionals who bill for their time. Knowing how long you have spent working on a document enables you to provide a more accurate accounting of your time.

If you have a time budget that you’re trying to stick to, you may find yourself constantly checking the document’s Properties dialog box to view the Total editing time value. Rather than wasting time performing that chore, add the EditTime field to your document. Word’s EditTime field displays the total time, in minutes, that the document has had the system focus (and, presumably, has been edited) since the time at which the document was created. This is the same as the Total editing time value displayed in the Statistics tab of the document’s Properties dialog box.

NOTE

To display the Properties dialog box for the current document, choose Developer, Document Panel and click OK (or choose Office, Prepare, Properties). Pull down the Document Properties list and then click Advanced Properties.

Follow these steps to add some code that displays the total editing time with the word min­utes added for clarity:

1. Type some descriptive text (such as Total Editing Time:).
2. Press Ctrl+F9 to start a new Word field (signified by the { and } braces).
3. Between the field braces, type the following: EDITTIME \# “0 minutes” \* mergeformat
4. Press F9 to complete and update the field.

Read the rest of this entry »

Popularity: 5% [?]

Data analysis is often more about the relationships between values in a range than it is about the values themselves. For example, in a table of sales where one product sells 600 units and another sells 300, you might be interested only in the fact that the former sold twice as much as the latter, regardless of the actual sales. Excel 2007 comes with a new tool that enables you to quickly and easily perform these kinds of relative analyses: data bars. Data bars are colored, horizontal bars that display “behind” the values in a range. Their key feature is that the length of the data bar that displays in each cell depends on the value in that cell: the larger the value, the longer the data bar. The cell with the highest value has the longest data bar, and the data bars that display in the other cells have lengths that reflect their values. (For example, a cell with a value that is half of the largest value has a data bar that’s half as long as the longest data bar.)

To apply data bars to the selected range, choose Home, Conditional Formatting, Data Bars, and then choose the color you prefer. Figure 1 shows a worksheet of product sales, with data bars applied to the values in the Units column.

Excel configures its default data bars with the longest data bar based on the highest value in the range and the shortest data bar based on the lowest value in the range. However, what if you want to visualize your values based on different criteria? With test scores, for example, you might prefer to see the data bars based on values between 0 and 100 (so for a value of 50, the data bar always fills only half the cell, no matter what the top mark is). Read the rest of this entry »

Popularity: 4% [?]

When you put together your presentation, part of your preparation should always involve anticipating possible audience questions, comments, or concerns. This enables you to pro­vide quick and accurate responses, which impresses a lot of people and enhances the overall message of your presentation.

Most of the time, you can write down possible questions, answers, and other material in the notes pages of your slides. However, you might think of an audience question that requires a more formal response in the form of a separate slide. The only problem is that you don’t want to include that slide in your presentation because the question or comment that leads to it might never come up. The solution is to include the slide in the presentation anyway but mark it as hidden. This means that the slide doesn’t display in your slide show and doesn’t print in your handouts.

TIP

If you want to print your hidden slides, choose Office, Print to display the Print dialog box. Click to activate the Print Hidden Slides check box and then click OK.

To hide a slide, select it in the Normal or Slide Sorter view and then choose Slide Show, Hide Slide. PowerPoint lets you know a slide is hidden by displaying a rectangle with a diagonal slash behind the slide number. (In Normal view, a hidden slide looks as if it is washed out in the Slides tab.)

How do you view a hidden slide when you need it? PowerPoint gives you two methods: Read the rest of this entry »

Popularity: 6% [?]

Another of Excel’s new conditional formatting rules is the top/bottom rule, which applies a format to cells that rank in the top or bottom (for numerical items, the highest or lowest) values in a range. You can select the top or bottom either as an absolute value (for example, the top 10 items) or as a percentage (for example, the bottom 25 percent). You can also apply formatting to those cells that are above or below the average. To create a top/bottom rule, begin by choosing Home, Conditional Formatting, Top/Bottom Rules. Excel displays six choices:

Top 10 Items Choose this command to apply formatting to those cells with values that rank in the top X items in the range, where X is the number of items you want to see (the default is 10).

Top 10% Choose this command to apply formatting to those cells with values that rank in the top X percentage of items in the range, where X is the percentage you want to see (the default is 10).

Bottom 10 Items Choose this command to apply formatting to those cells with values that rank in the bottom X items in the range, where X is the number of items you want to see (the default is 10).

Bottom 10 % Choose this command to apply formatting to those cells with values that rank in the bottom X percentage of items in the range, where X is the percentage you want to see (the default is 10).

Above Average Choose this command to apply formatting to those cells with values that are above the average of all the values in the range. Read the rest of this entry »

Popularity: 4% [?]

Like most modern word processors, Word fits into the category of fritterware-programs with so many formatting bells and whistles that you can end up frittering away hours and hours by tweaking fonts, colors, alignments, and so on. Whether you think of such activity as “frittering” depends on your point of view, but we all agree that a well-formatted docu­ment makes a better impression than a plain or sloppy-looking one. So no matter how much time you’ve devoted to getting your document just so, the last thing you want is another person running roughshod over your careful look and layout.

Fortunately, Word offers the capability to lock your document’s formatting, which prevents others from changing the formatting unless they know the password. Here are the steps to follow:

1. Choose Review, Protect Document (or choose Developer, Protect Document) and then click Restrict Formatting and Editing. Word displays the Restrict Formatting and Editing task pane.
2. Click to activate the Limit Formatting to a Selection of Styles check box.
3. Click Settings to display the Formatting Restrictions dialog box, shown in Figure 1.

Figure 1 Use the Formatting Restrictions dialog box to restrict the formatting another user can apply to a document.

4. In the Checked Styles Are Currently Allowed list, deactivate the check box next to each style that you want to disallow. Alternatively, use the following buttons to set the check boxes:
   • All-Click this button to activate all the check boxes and thus enable unautho­rized users to apply formatting using only the existing styles; these users cannot modify the existing styles or create new styles. Read the rest of this entry »

Popularity: 4% [?]

Most of the time, you use your mouse to operate and navigate a slide show. You can use these two basic techniques:

• Click to move to the next slide in the presentation or to run the next animation in the current slide.
• Turn the mouse wheel backward (toward you) to move to the next slide in the presen­tation or to run the next animation in the current slide; turn the mouse wheel forward (away from you) to advance to return to the previous slide in the presentation or to reverse the most resent animation in the current slide.

For more control over the slide show, either click the Slide Show Menu icon or right-click the slide show. The menu you see contains the following commands:

• Next-Moves to the next slide in the presentation.
• Previous-Moves to the previous slide in the presentation.
• Last Viewed-Jumps to the last slide that you displayed in the presentation. Note that this won’t be the same as the presentation’s previous slide if you used the Go to Slide command (see the next item in this list), a hyperlink, or an action button to jump to the current slide from elsewhere in the presentation.
• Go to Slide-Displays a menu of the slides in the presentation. When you click a slide in the list, PowerPoint jumps to that slide. Read the rest of this entry »

Popularity: 6% [?]