Ms Office Tune Up

Some of Excel’s time saving frills can make your life easier when you’re entering data in a worksheet. This section covers four such features: AutoComplete, Auto-Correct, AutoFill, and AutoFit, along with Excel’s top candidates for the Lifetime Most Useful Achievement award: Undo and Redo.

Figure 2-6. Tweaking the regional settings on your computer gives you complete control over how Excel recognizes dates. Use the pull-down menus to specify the date separator, order of month, day, and year components in a date, and how Excel should interpret two-digit years. You can mix and match these settings freely, although you could wind up with a computer that’s completely counter intuitive to other people. Read the rest of this entry »

Popularity: 8% [?]

By looking at cell alignment, you can easily tell how Excel is interpreting your data. That’s helpful. But what happens when Excel’s interpretation is at odds with your wishes? For example, what if you type in something you consider a number but Excel freakishly treats it as text, or vice versa? The first step to solving this problem is grasping the logic behind Excel’s automatic decision-making process.

How Excel decides your data is text

If your cell meets any of the following criteria, Excel automatically treats the content as ordinary text:

• It contains any letters. Thus, C123 is text, not a number.

• It contains any punctuation that Excel can’t interpret numerically. Punctuation allowed in numbers and dates includes the comma (,), the decimal point (.), and the forward slash (/) or dash (-) for dates. When you type in any other punctuation, Excel treats the cell as text. Thus, 14! is text, not a number.

Occasionally, Excel reads your data the wrong way. For example, you may have a value like a social security number or a credit card number that’s made up entirely of numeric characters but that you want to treat like text because you don’t ever want to perform calculations with it. But Excel doesn’t know what you’re up to, and so it automatically treats the value as a number. You can also run into problems when you precede text with the equal sign (which tells Excel that you have a formula in progress), or when you use a series of numbers and dashes that you don’t intend to be part of a date (for example, you want to enter 1-2-3 but you don’t want Excel to read it as January 2, 2007which is what it wants to do).

In all these cases, the solution’s simple. Before you type the cell value, start by typing an apostrophe (’). The apostrophe tells Excel to treat the cell content as text. Figure 2-3 shows you how it works. Read the rest of this entry »

Popularity: 8% [?]

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

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

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

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

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

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

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

Most chart data consists of an entire range of cells, but it’s sometimes necessary to chart only a portion of a range. If you have stock data, for example, you may want to visualize only a particular 7-, 14-, or 30-day portion of the data. If the chart doesn’t exist yet, just select the portion of the range you want to work with and then insert the chart. If the chart already exists, there are two methods you can use to adjust the data series:

• Click the chart and then choose Design, Select Data to open the Select Data Source dialog box. For the value range, click the Edit button in the Legend Entries (Series) section to display the Edit Series dialog box, adjust the Series Values reference, and then click OK. For the categories, click the Edit button in the Horizontal (Category) Axis Labels section to display the Axis Labels dialog box, adjust the Axis Label Range reference, and then click OK.
• Click the chart, click any point in the data series, and then adjust the range references used in the SERIES() function that displays in the formula bar. I explain the function as follows.

Here’s the syntax for the SERIES() function:

SERIES([name,][ category_labels,] values, order)

For example, here’s the SERIES() function for the line chart that displayed earlier: Read the rest of this entry »

Popularity: 9% [?]