Ms Office Tune Up

One of Excel’s most important features is its ability to distinguish between different types of information. A typical worksheet contains both text and numbers. There isn’t a lot you can do in Excel with ordinary text (other than alphabetize a list, perform a simple spell check, and apply some basic formatting). On the other hand, Excel gives you a wide range of options for numeric data. For example, you can string your numbers together into complex calculations and formulas, or you can graph them on a chart. Programs that don’t try to separate text and numbers like Microsoft Word, for example can’t provide these features.

Most of the time, when you enter information in Excel, you don’t explicitly indicate the type of data. Instead, Excel examines the information you’ve typed in, and, based on your formatting and other clues, classifies it automatically. Excel distinguishes between four core data types:

• Ordinary text. This data type includes column headings, descriptions, and any content that Excel can’t identify as one of the other data types.

• Numbers. This data type includes prices, integers, fractions, percentages, and every other type of numeric data. Numbers are the basic ingredient of most Excel worksheets.

• Dates and times. This data type includes dates (like Oct 3, 2007), times (like 4:30 p.m.), and combined date and time information (like Oct 3, 2007, 4:30 p.m.). You can enter date and time information in a variety of formats.

• True or false values. This data type (known in geekdom as a Boolean value) can contain one of two things: TRUE or FALSE (displayed in all capitals). You don’t need Boolean data types in most worksheets, but they’re useful in worksheets that include Visual Basic macro code or that use complex formulas that evaluate conditions.

Read the rest of this entry »

Popularity: 7% [?]

You’ve probably seen or read books where the introductory material displays page numbers using lower-case Roman numerals (i, ii, iii, and so on), whereas the rest of the book dis­plays page numbers using Arabic numerals (1, 2, 3, and so on). Using multiple page num­ber formats in this way is an easy method for differentiating two different sections of a book, and it’s a technique you can apply to your own Word documents.

The trick here is to create a section break between the parts of the document where you want to use the different page numbers. A section is a document part that stores page layout options such margins, page size, page orientation, columns, line numbering, and footnotes and endnotes. The transition from one section to another is called a section break. Sections also include headers and footers, and we’ll use that fact to set up our different numbering schemes.

The first step is to create the section break, which you do by following these steps:

1. Position the cursor at the point where you want the new section to start.
2. Choose Page Layout, Breaks. Word displays a menu of break options.
3. In the Section Breaks group, click the type of section break you want:

Next Page-Starts a new section on a new page.

Continuous-Starts a new section at the cursor. (Note that this command doesn’t create a page break, so you probably won’t use it in this context.)

Even Page-Starts a new section on the next even-numbered page.

Odd Page-Starts a new section on the next odd-numbered page. Read the rest of this entry »

Popularity: 20% [?]

Numbered lists (choose Home, Numbering) are a great way to present a sequential series of items-steps in a procedure, rankings, Top Ten lists, and so on. When you create one numbered list, press Enter twice after the last item and then start a second numbered list, (Word starts the second list at 1). That’s usually the behavior you want, but there may be times when you have to insert text between two steps. In this case, you want to make sure that Word continues the numbering when the list continues after the inserted text.

Word gives you a couple of ways to do this, and the method you choose depends on when you want to insert the text: while you’re creating the numbered list or after you’ve finished the numbered list.

The easiest method is to insert the text after you have completed the numbered list:

1. Navigate to the end of the step before which you want to insert the non-numbered text.
2. Press Enter. Word inserts a new step.
3. Choose Home, Numbering to deactivate the Numbering button. Word converts the new step into a regular paragraph.
4. Insert your text.

To insert the text while you’re creating the numbered list, follow these steps: Read the rest of this entry »

Popularity: 22% [?]

In these complex and uncertain times, forecasting business performance is increasingly important. Today, more than ever, managers at all levels need to make intelligent predic­tions of future sales and profit trends as part of their overall business strategy. By forecast­ing sales six months, a year, or even three years down the road, managers can anticipate related needs such as employee acquisitions, warehouse space, and raw material require­ments. Similarly, a profit forecast enables the planning of the future expansion of a com­pany.

Business forecasting has been around for many years, and various methods have been developed-some more successful than others. The most common forecasting method is the qualitative “seat-of-the-pants” approach, in which a manager (or a group of managers) estimates future trends based on experience and knowledge of the market. This method, however, suffers from an inherent subjectivity and a short-term focus because many man­agers tend to extrapolate from recent experience and ignore the long-term trend. Other methods (such as averaging past results) are more objective but generally useful for fore­casting only a few months in advance.

In business, it’s becoming increasingly popular to use a statistical tool called regression analy­sis to determine the relationship between one phenomenon that depends on another. For example, car sales might be dependent on interest rates, and units sold might be dependent on Read the rest of this entry »

Popularity: 29% [?]

One of my goals in this book is to present you with tricks that are practical in the sense that you can use them right away to be more productive or more efficient. I also try to focus on reducing drudgery, mostly by automating tedious or routine tasks with macros. This section’s trick isn’t even remotely practical, but I think it falls somewhat into the “reducing drudgery” category, as you’ll see (I hope!).

We all have certain documents that we use almost every day. A good example is a to-do list that is checked constantly throughout the day and that is added to or deleted from as nec­essary. Any document that you look at many times a day gets boring awfully fast. To add some visual interest, Listing 1 presents a macro that applies a random background texture to a specified document.

Listing 1 A Macro That Applies a Random Background Texture to a Specified Document

Public Sub ApplyRandomTexture(doc As Document)
Dim i As Integer
‘
‘ Initialize the random number generator
‘
Randomize
‘
‘ Generate a random number between 1 and 24
‘
i = Int(24 * Rnd + 1)
‘
‘ Apply the random number as a background texture constant
‘
doc.Background.Fill.PresetTextured i
End Sub

Read the rest of this entry »

Popularity: 33% [?]

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: 27% [?]

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: 32% [?]

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: 28% [?]

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: 34% [?]

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: 29% [?]