Worksheet formatting

You can use commands from the Format menu to change and improve the appearance of the worksheet and to modify the way number values are displayed. 

---

Verification of data format entered into the computer Worksheet generation... 

Figure 6.4 Example Failure Compilation Worksheet Note The format used to enter failure data is X[Y,Z], where X is the total number of failures, Y is the number of demand-related failures, and Z is the number of time-related failures. From Science Applications International Corporation.

The MathCad worksheets used for this Chemometrics in Spectroscopy collaborative study series are given below in hard copy format. Unless otherwise noted, the worksheets have been written by the authors. The text files for the MathCad v7.0 Worksheets used for the statistical tests in this report are attached as Collabor GM, Collabor TV, ANOVA s4, ANOVA s2, CompareT, and Comp Meth. References [1-11] are excellent sources of information of the details on these statistical methods. 

For this chapter we continue to describe the use of confidence limits for comparison of X, Y data pairs. This subject has been addressed in Chapters 58-60 first published as a set of articles in Spectroscopy [1-3]. A MathCad Worksheet ( 1986-2001 MathSoft Engineering Education, Inc., 101 Main Street Cambridge, MA 02142-1521) provides the computations for interested readers. This will be covered in a subsequent chapter or can be obtained in MathCad format by contacting the authors with your e-mail address. The Worksheet allows the direct calculation of the f-statistic by entering the desired confidence levels. In addition the confidence limits for the calculated slope and intercept are computed from the original data table. The lower limits for the slope and the intercept are displayed using two different sets of equations (and are identical). The intercept confidence limits are also calculated and displayed. 

Table 4.7. Typical Format for a What-if Analysis Worksheet...

The next type of standard output is the spreadsheet. These file formats use comma-separated ASCII code, but also add calculation information and addressing information for the columns and rows they occupy. The simplest of these are. DIF files, which originated to allow information transfer between VisiCalc worksheets in the Apple II computer and have been retained as a standard format.. WK files are Lotus-1,2,3 formats and. XLS are Microsoft Excel formats that have become spreadsheet standards, allowing transfer of data, calculations, addresses, and macro programs. 

Figure 1.11 Demonstration of the error trapping routines in the code controlling worksheet calculations activated through the Character from Orbits command button. Note that the second screen dump has been enhanced to emphasise that a non-integer entry was made, because the default format for the of orbits input cells is integer.

This chapter covers the basics of working with Excel navigating around the worksheet, entering values and formulas, and formatting and editing a worksheet. If you are an experienced Excel user, you can probably skip this chapter however, even experienced users may find a few useful tips in this chapter,... 

In Excel 2000 for Windows, the Worksheet Menu Bar has the following pulldown menus File, Edit, View, Insert, Format, Tools, Data, Window and Help. The File, Edit, Format and Window menus are discussed in this chapter. Commands in other menus will be discussed in later chapters. 

Excel also provides "context-sensitive" shortcut menus. If you press the right mouse button (Windows) or press COMMAND+OPTION (Macintosh) while you select a worksheet element with the mouse pointer, a menu is displayed containing commands that apply to the selection. For example, if you select a column while holding down the right mouse button, a shortcut menu containing editing and formatting commands appropriate for a column appears. 

To use the Across Worksheets option in the Fill submenu, you must have selected multiple sheets beforehand. Select adjacent or non-adjacent sheets in the same way you select cells. To select an adjacent range of worksheets click on the tab of the first sheet, then hold down the SHIFT key and click on the tab of the last sheet in the range. To select non-adjacent sheets, hold down the CONTROL key (Windows) or the COMMAND key (Macintosh) while selecting. When you choose Across Worksheets from the submenu, the Fill Across Worksheets dialog box (Figure 1-18) will appear you can choose to fill Contents, Formats or both. 

Excel Tip. You can apply the same formatting to multiple worksheets simply by grouping the sheets (click on the first sheet tab in the range of sheets to be formatted, then hold down the SHIFT key and click on the last sheet in the range). When you apply the desired formatting to the active sheet it will be applied to all sheets in the group. 

Choose Cells...from the Format menu and choose the Protection tab, uncheck the Locked option, and press the OK button. This un-protects all cells in the worksheet. 

To hide a worksheet in a workbook, choose Sheet from the Format menu and choose Hide from the submenu. 

With an axis selected, the Scale tab (Figure 2-6) enables you to change the scale range and where the X Axis crosses the Y Axis, for example. The Number tab (not shown) permits you to use the same number formats available for worksheet cells in the Format Cells... command to change the number formatting of the axis labels. 

You ll find Create Names very useful if you have worksheets with constants or other values arranged in a table format, as in Figure 3-3. This command allows... 

Times are represented by the decimal part of the serial number. You can use either number formatting or worksheet functions to convert these date serial values into comprehensible dates. 

As you ve already seen, dates and times can be entered into worksheet cells using any one of several convenient formats July 1 can be entered as 7-1, 7/1, July 1, Jul 1 or 1 July, among others. All these date entries produce the date 7/1/xxxx in the formula bar (xxxx is the four-digit year) and the displayed date 1-Jul. Excel enters the current year unless a different year is specified. If you enter the year, either as a two-digit or four-digit number. Excel displays the date in a different format, as e.g., 7/1/xx. 

The following worksheet functions operate on the date serial value to return a date or time YEAR(value), MONTH(va/ue) and DAY value). Value canhe a serial value, a cell reference or a date as text. MONTH returns a number between 1 and 12, DAY a number between 1 and 31. The text function TEXT can also be used to format a date serial value any custom date format can be applied (see "The FIXED and TEXT Functions" later in this chapter). 

If you enter 10 AM in cell A1 of a worksheet, and =A1 +3 in cell B1, you may at first be confused when cell B1 displays 10 00 AM. But remember that you ve added three days, not three hours if you apply the date format m/d/yy to the cell, you ll see that you ve calculated a date three days from the current date. If you change the formula to =A1+3 00, you get an "Error in formula" message. That s because 3 00 is not a numerical value. To obtain the desired result, 1 00 PM, use the formula =A1+"03 00 . Excel recognizes that 03 00 is text and evaluates it just as it would if you d typed it into a cell. 

Plotting experimental data points and a smooth calculated curve is one of the most common applications of custom formatting. To do this you need to plot two y data series — the experimental data points and a series of points to describe the calculated curve. The yobsd data should be formatted as a series of symbols with no connecting line, the ycalc data as a line with no symbols, as in Figure 5-11. To generate a smooth calculated curve, you ll need to have the ycalc points fairly close together. But since having too many points can slow recalculation of a worksheet, you should try to strike a balance between the two requirements. 

You set the properties of a control by choosing Control... from the Format menu or Format Control... from the shortcut menu to display the Format Control dialog box for the specific control, then choose the Control tab. Some control properties function only when the control is u ed with a macro. You can set these control properties but they will have no effect on a control installed on a worksheet. 

To install the list box on a worksheet, follow the procedure outlined earlier in this chapter. First, display the Forms toolbar. Click on the List Box button (second from the right in the top row in Figure 8-1) and use the mousepointer to draw the outline of a list box. While the list box is still selected (has handles around it), choose Control... from the Format menu. In the Input Range box, enter the external reference to the range of cells containing the list to be displayed. In the Cell Link box, enter a cell reference, e.g., C5, then press OK. 

If you want to use the coefficients for calculations, youTl have to copy them from the chart and paste them into worksheet cells. Usually the coefficients as displayed in the chart are not precise enough for calculations, but you can number-format the text to display more figures before copying the coefficients. 

To illustrate the use of the Recorder, we ll record the action of applying scientific number formatting to a number in a cell. First, select a cell in a worksheet and enter a number. Now choose Macro from the Tools menu, then Record New Macro... from the submenu. The Record Macro dialog box (Figure 13-5) will be displayed. 

As a first example, well create the ChemicalFormat macro to format text in a selected cell as a chemical formula. The macro will be a simple one. It will examine each character of a text string if the character is a number, the character will be subscripted. You ll find this macro useful if you label rows or columns in your worksheets with chemical formulas, such as "CH3CH=CH2" or "moles of H3PO4" the macro will be a real timesaver. 

Finally, we d like our macro to be able to format text in a worksheet cell or range, in a chart title, or in a textbox. As well, we don t want the macro to crash... 

MAKE SURE WE ARE ON A WORKSHEET OR CHART SHEET If TypeName(ActiveSheet) <> "Worksheet" And TypeName(ActiveSheet) <> "Chart" Then Beep Exit Sub GO TO CORRECT (X)DE FOR SELECTION TO BE FORMATTED Select Case TypeName(Selection)... 

The inner-loop code in Figure 16-2 was used as a general subroutine (now called DoFormat) to examine the text, find the number characters and format them. The main program simply ensures that a worksheet or chart is active, determines the kind of text to be formatted, then calls the subroutine. The subroutine call passes two arguments, a simple variable containing the text to be examined (Formulastring) and an object variable containing the text to be subscripted (FormulaObject). 

Chapter 13 provided an introduction to Sub procedures and Function procedures. By now it should be clear that a Sub procedure (a command macro) is a computer program that you "run" it can perform actions such as formatting, opening or closing documents, etc. A Function procedure (a user-defined fimction) is a computer program that calculates a value and returns it to the cell in which it is typed. A Function procedure cannot change the worksheet environment ( e.g., it can t make a cell Bold). 

---