Ms Office Tune Up

Earlier in this chapter, you learned how to create a chart that expands automatically as you add new data points (see “Automatically Expanding a Chart to Include New Data”). It’s a useful trick that I use all the time, but it does lead to one problem: You can end up with a lot of data points in the chart, and after awhile you have to resize the chart horizontally to maintain the visual integrity of the plot. Of course, that leads to an entirely different prob­lem: Eventually your chart becomes wider than the Excel window, so you need to use the horizontal scroll bar to see different parts of the chart.

A better solution is to keep your chart the same size but add a scrolling element to it that enables you to move back and forth through the data. You set this up by adding a scroll bar to the worksheet and using the values generated by the scroll bar to create dynamically named ranges that change as the scroll bar value changes. Then, as you saw earlier in this chapter (see “Charting a Dynamic Range”), you configure your chart to use these dynami­cally named ranges. The result is that as you scroll forward and backward in the scroll bar, the data plotted on the charts moves forward and backward by the same amount.

You begin by converting your worksheet data to a table. Figure 1 shows the example data that I use in this section. It’s a table (named Table2) of monthly sales that runs from January, 1998 to December, 2007. I plot the Month and Actual columns in the chart. Read the rest of this entry »

Popularity: 9% [?]

The vast majority of documents look best when formatted with dark text on a light back­ground-especially a white background. However, there may be times when you need something a little more interesting. For example, light text on a dark background is a great way to make text box text and table headers stand out. For the document as a whole, you want to avoid busy backgrounds because they almost always render the document text unreadable. An occasionally useful exception is to use a background that resembles some kind of specialty paper. This can add a sense of realism to the document, which can be effective (if used sparingly, of course).

One of the nicest of these specialty paper backgrounds is parchment paper, which can give documents a classy, slightly old-time feel. Follow these steps to apply a parchment paper background:

1. Choose Page Layout, Page Color, Fill Effects. Word opens the Fill Effects dialog box.
2. Click the Texture tab.
3. In the Texture list, click the Parchment swatch.
4. Click OK. Word applies the texture to the background

Technorati Tags: parchment paper background, word 2007

Popularity: 7% [?]

In earlier Figure, I exploded one of the pie chart slices and then added a shape with some explanatory data about the composition of the Others slice. In most pie charts, you use an “Others” slice because the components data that makes up this item are too small to show properly on the pie chart. That is, the slices would be so thin that they’d be hard to pick out from the surrounding slices.

If you want others to see the data represented by an “Others” slice, Excel offers another pie chart type that’s ideal: The Bar of Pie type. With this chart type, the smallest data items are gathered into a single slice, as before, but then the separate items that comprise that slice are displayed in a separated stacked bar marker.

To create a Bar of Pie chart, select your data, choose Insert, Pie, Bar of Pie. Figure 1 shows an example.

Figure 1 A Bar of Pie chart shows the data series’ small val­ues in a separate stacked bar marker.

Technorati Tags: chart small values, bar pie chart, excel 2007

Popularity: 5% [?]

A watermark is a bit of text (or sometimes an image) that displays “behind” the text on each page and is visible both onscreen and in the printed version of the document. The purpose of a watermark is to display a broad message to the reader about an aspect of the document. For example, a watermark consisting of the word “DRAFT” tells the reader that this is not the final version of the document. Similarly, the watermark “ASAP” tells the reader you want the document handled as soon as possible.

Word offers a number of built-in watermarks. In the Watermarks gallery (choose Page Your, Watermark), for example, you can select from watermarks that use the following text: CONFIDENTIAL, DO NOT COPY, DRAFT, SAMPLE, ASAP, or URGENT, each of which you can display diagonally or horizontally across the page.

If none of these predefined watermarks is quite right for your needs, you can build a cus­tom watermark that consists of the text you want to display and the font, size, color, and layout you prefer. Here are the steps to follow: Read the rest of this entry »

Popularity: 5% [?]

A pie chart shows the proportion of the whole that is contributed by each value in a single data series. The whole is represented as a circle (the “pie”), and each value is displayed as a proportional “slice” of the circle. You can use pie charts to represent sales figures propor­tionally by region or by product, or to show population data such as age ranges or voting patterns.

To create a pie chart, select your data, choose Insert, Pie, and click the pie chart type you want. In the pie chart gallery, notice that Excel includes an Exploded Pie type. In pie chart lingo, exploding a slice means separating that slice by some amount so that it appears on its own. This is a useful way to highlight a special slice. However, the Exploded Pie type explodes every slice, which isn’t that useful. If you want to explode just a single slice, you need insert a regular pie chart and then follow these steps:

1. Click the pie chart to select it.
2. Click any slice to select the series and then click the slice you want to explode. This should now be the only data point selected.
3. Choose Layout, Format Selection to display the Format Data Point dialog box.
4. In the Series Options tab, click and drag the Point Explosion slider towards the Separate end (to the right). You can also enter a percentage value in the associated text box. Read the rest of this entry »

Popularity: 5% [?]

Using linked text boxes is a great way to get control over your layout while still maintain­ing text flow. However, as convenient as it is for you, it may not be all that convenient for your readers, who might find it difficult to follow the story from one text box to the next.

One solution would be to include an instruction below the first text box that tells the reader where to go to continue reading the article (for example, Continued on page 4). Make sure you put this instruction outside of the text box, or you’ll interrupt the flow between the text boxes.

In the publishing world, such an instruction to the reader is called jump text. In the Word world, however, it does suffer from a fairly major problem: In most cases, you can only be sure of the page number after you’ve completed the entire document. This means that after your document is finished, you need to run through the whole thing and insert the correct page numbers into all the jump text. That is inefficient and labor-intensive.

A much better solution-and one that takes advantage of one of the inherent strengths of electronic documents-is to set up the jump text as a link to the next text box. This enables you to use a more generic instruction-such as Ctrl+click here to continue this arti­cle-and it makes life much easier for the reader by creating true “jump” text. Read the rest of this entry »

Popularity: 5% [?]

If you plot two different series on the same chart, the result won’t look good if the two series use wildly different data ranges. A good example is a stock chart that includes one series for closing prices and another series for volume. The prices might be measured in tens of dollars, whereas the volumes might be measured in tens of thousands of units. How can you combine these two disparate data sources so that you can see both series properly?

The trick here is to add another vertical axis-called the secondary axis-and tell Excel to plot one of the series using that axis. Here are the steps to follow:

1. Click the chart to select it.
2. Click the data series that you want to plot on the secondary axis.
3. Choose Layout, Format Selection to display the Format Data Series dialog box.
4. In the Series Options tab, click the Secondary Axis option.
5. Click Close.

Figure 1 shows an example chart with two vertical axes-the primary axis (on the left) plots the Close series, and the secondary axis (on the right) plots the Volume series.

Figure 1 If you have series with wildly different data val­ues, plot one of them on the secondary axis.

Technorati Tags: dispaly second vertical axis

Popularity: 8% [?]

When you read a newspaper, magazine, or newslet­ter, you often come across an article that starts on one page and then continues on a different page later in the publication. In some cases, you may have to make several “jumps” from one page to another to complete the article. This is a useful technique because it enables the publisher to place several articles on the front page of the publication, which helps create interest. It also helps on the inner pages of the publication because it gives the publisher more flexibility to lay out the articles (whether they’re continuations from the front page or new articles).

If you’re creating a multi-page publication in Word, how can you break up articles so that they appear on different pages? The most obvious way is to insert the first part of each arti­cle on the front page and then copy the rest to an inner page. That might work if all your text is complete and no longer requires editing. However, you may still have trouble fitting the text properly on the pages, so it may require quite a bit of cutting and pasting to get a suitable layout. And, certainly, this type of setup is a nightmare waiting to happen if your text or formatting still needs to be revised.

The problem with cutting and pasting to get this effect is that the different parts of any one article aren’t connected in any way, so there’s no flow. In Word, you usually create flowing text by converting that text into two or more columns: The text flows from one column to the next and from the last column on one page to the first column on the next. Even better, each time you add, format, move, or delete text in one column, Word automatically adjusts the position of the text in the other columns to compensate. Unfortunately, you must keep columns together; you can’t start an article in columns on one page and then continue it in columns on a different page. Read the rest of this entry »

Popularity: 4% [?]

Most charts use static data as their data source. However, it’s not uncommon for a chart to use a data source that constantly expands. On a stock price worksheet, for example, you might enter daily prices for a given month. Rather than wait until the end of the month to create the chart, it is useful to chart the existing data and update the chart as you add new prices each day.

The hard way to accomplish this is to edit the range references for the category axis labels and data series values each time you add new data. Fortunately, Excel 2007 gives you a much easier method. As with the dynamic ranges you learned about in the previous section, the trick is to convert your data to a table. When the category axis label range and data series range are part of a table, Excel automatically expands the chart to include any new data that you add to the table. Note that you get this advantage without any other fuss and bother. As soon as you convert the data range to a table, your chart becomes dynamic based on the table data. You don’t need to edit the SERIES() function or perform any other arcane tasks.

NOTE

Another advantage you get with this trick is that it doesn’t matter when you convert the data to a table.You can perform the conversion either before or after you’ve created your chart; Excel will still expand the chart automatically to accommodate new table data.

Technorati Tags: expanding chart automatically, new data excel

Popularity: 6% [?]

When you’re working with fields, it’s common to need to update all of a document’s fields at one time. One way to do this is to select the entire document and press F9. This works, but it’s a hassle because not only must you perform the extra step of selecting the entire document, but that extra step also means that you lose your current cursor position.

To avoid this problem, use the VBA macro in Listing 1 to update all the document’s fields.

Listing 1 A Macro to Update All the Fields in the Active Document

Sub UpdateAllFields()
ActiveDocument.Fields.Update
End Sub

Add this macro to a Quick Access toolbar button or assign it a keyboard shortcut (I use Ctrl+Alt+Shift+F9).

It’s also often useful to update all of a document’s fields when the document is opened. Word doesn’t do that by default, but you can create a macro that does.

Launch the Visual Basic Editor and open the project that corresponds to your document. In the project’s Microsoft Word Objects branch, double-click the ThisDocument object. In the code window that displays, select Document in the object list and select Open in the event list. Add the following statement to the Document_Open() stub that displays: Read the rest of this entry »

Popularity: 5% [?]