Formatting Worksheets

 

FORMATTING WORKSHEETS

The StatsDirect workbook supports a rich set of data formatting capabilities. You can use number formats to display numbers in a certain way - as dates, for example, or as scientific format numbers. You can use cell formats to change the size of cells and to add colours and borders. You can use font formats to change the typeface and style of the characters in your worksheet.

 

Using Built-in Number Formats

Number formats determine how numbers look when they appear in the worksheet. When you create a new worksheet, by default all cells in that worksheet use the General format, which means that as you enter data in the worksheet, StatsDirect determines the type of data and applies the appropriate number format (e.g., if you enter a date, a date format is applied).

 

To specify formats other than General, you can apply a number of built-in number formats. If you do not find the number format you want, you can create your own custom number format.

 

To apply number formats:

1. Select the cells for which you want to change the number format.

2. Select Format_Cells and select the Number tab.

3. Select a category for the number format from the Category list.

4. Type a number format or select a format type from the Type combo box.

5. You can type a built-in format or a custom format.

6. Click OK.

 

The following table shows the built-in number formats for a UK English locale (the locale of your copy of Microsoft Windows is set using the Windows control panel) and the result after the format is applied to a positive, negative, and decimal number.

 

Category and Format	3	-3	.3
General	3	-3	.3
Currency
£#,##0_);(£#,##0)	£3	(£3)	£0
£#,##0_);[Red](£#,##0)	£3	(£3)	£0
£#,##0.00_);(£#,##0.00)	£3.00	(£3.00)	£0.30
£#,##0.00_);[Red]£(#,##0.00)	£3.00	(£3.00)	£0.30
Fixed
0	3	-3	0
0.00	3.00	-3.00	0.30
#,##0	3	-3	0
#,##0.00	3.00	-3.00	0.30
#,##0_);(#,##0)	3	(3)	0
#,##0_);[Red](#,##0)	3	(3)	0
#,##0.00_);(#,##0.00)	3.00	(3.00)	0.30
#,##0.00_);[Red](#,##0.00)	3.00	(3.00)	0.30
Percent
0%	300%	-300%	30%
0.00%	300.00%	-300.00%	30.00%
Fraction
# ?/?	3	-3	2/7
# ??/??	3	-3	3/10
Scientific
0.00E+00	3.00E+00	-3.00E+00	3.00E-01
##0.0E+0	300.0E-2	-300.0E-2	300.0E-3

 

The following table shows the built-in date formats for a UK English locale and the result after the format is applied to a date.

 

Format	18/04/95
m/d/yy	18/4/95
d-mmm-yy	18-Apr-95
d-mmm	18-Apr
mmm-yy	Apr-95
m/d/yy h:mm	18/4/95 0:00

 

The following table shows the built-in time formats for a UK English locale and the result after the format is applied to a time.

 

Format	12:02:02
h:mm AM/PM	12:02 PM
h:mm:ss AM/PM	12:02:02 PM
h:mm	12:02 PM
h:mm:ss	12:02:02
m/d/yy h:mm	18/4/95 12:02 PM
mm:ss	02:02
[h]:mm:ss	12:02:02
mm:ss.0	02:02.0.116

 

Applying Number Formats to Rows and Columns

If you apply a number format to a row or column, that format is applied to all cells in the row or column. When you enter data in a cell in a formatted row or column, the data assumes the designated format.

 

The StatsDirect workbook allocates memory by rows. Formatting empty rows or columns does not use memory. A format is merely attached to a row or column. Formatting empty ranges is treated differently. If you format a range of empty cells, a group of formatted, empty cells is created. Each new formatted, empty cell consumes memory.

 

Creating Custom Number Formats

In addition to the built-in number formats, you can define custom formats. Each custom format can have as many as four sections: one for positive numbers, one for negative numbers, one for zeros, and one for text. Each section is optional; the sections are separated by semicolons. The following example shows a custom format.

 

#,###;(#,###);0;"Error: Entry must be numeric"

 

To define a custom number format:

1. Select the cells for which you want to create the custom number format.

2. Select Format_Cells and select the Number tab.

3. Select a category for the custom number format from the Category list.

4. Type a custom number format built from the custom format characters described below.

5. Click OK.

 

Format Symbol	Description
General	Displays the number in General format.
0	Digit placeholder. If the number contains fewer digits than the format contains placeholders, the number is padded with 0’s. If there are more digits to the right of the decimal than there are placeholders, the decimal portion is rounded to the number of places specified by the placeholders. If there are more digits to the left of the decimal than there are placeholders, the extra digits are retained.
#	Digit placeholder. This placeholder functions the same as the 0 placeholder except the number is not padded with 0’s if the number contains fewer digits than the format contains placeholders.
?	Digit placeholder. This placeholder functions the same as the 0 placeholder except that spaces are used to pad the digits.
. (period)	Decimal point. Determines how many digits (0’s or #’s) are displayed on either side of the decimal point. If the format contains only #’s left of the decimal point, numbers less than 1 begin with a decimal point. If the format contains 0’s left of the decimal point, numbers less than 1 begin with a 0 left of the decimal point.
%	Displays the number as a percentage. The number is multiplied by 100 and the % character is appended.
, (comma)	Thousands separator. If the format contains commas separated by #'s or 0's, the number is displayed with commas separating thousands. A comma following a placeholder scales the number by a thousand. For example, the format 0, scales the number by 1000 (e.g., 10,000 would be displayed as 10).
E- E+ e- e+	Displays the number as scientific notation. If the format contains a scientific notation symbol to the left of a 0 or # placeholder, the number is displayed in scientific notation and an E or an e is added. The number of 0 and # placeholders to the right of the decimal determines the number of digits in the exponent. E- and e- place a minus sign by negative exponents. E+ and e+ place a minus sign by negative exponents and a plus sign by positive exponents.
$ - + / ( ) : space	Displays that character. To display a character other than those listed, precede the character with a back slash (\) or enclose the character in double quotation marks (" "). You can also use the slash (/) for fraction formats.
\	Displays the next character. The backslash is not displayed. You can also display a character or string of characters by surrounding the characters with double quotation marks (" "). The backslash is inserted automatically for the following characters: ! ^ & ` (left quote) ' (right quote) ~ { } = < >
* (asterisk)	Repeats the next character until the width of the column is filled. You cannot have more than one asterisk in each format section.
_ (underline)	Skips the width of the next character. For example, to make negative numbers surrounded by parentheses align with positive numbers, you can include the format _) for positive numbers to skip the width of a parenthesis.
"text"	Displays the text inside the quotation marks.
@	Text placeholder. If there is text in the cell, the text replaces the @ format character.
M	Month number. Displays the month as digits without leading zeros (e.g., 1-12). Can also represent minutes when used with h or hh formats.
Mm	Month number. Displays the month as digits with leading zeros (e.g., 01-12). Can also represent minutes when used with the h or hh formats.
Mmm	Month abbreviation. Displays the month as an abbreviation (e.g., Jan-Dec).
Mmmm	Month name. Displays the month as a full name (e.g., January-December).
d	Day number. Displays the day as digits with no leading zero (e.g., 1-2).
dd	Day number. Displays the day as digits with leading zeros (e.g., 01-02).
ddd	Day abbreviation. Displays the day as an abbreviation (e.g., Sun-Sat).
dddd	Day name. Displays the day as a full name (e.g., Sunday-Saturday).
yy	Year number. Displays the year as a two-digit number (e.g., 00-99).
yyyy	Year number. Displays the year as a four-digit number (e.g., 1900-2078).
h	Hour number. Displays the hour as a number without leading zeros (e.g., 0-23). If the format contains one of the AM or PM formats, the hour is based on a 12-hour clock. Otherwise, it is based on a 24-hour clock.
hh	Hour number. Displays the hour as a number with leading zeros (e.g., 00-23). If the format contains one of the AM or PM formats, the hour is based on a 12-hour clock. Otherwise, it is based on a 24-hour clock.
m	Minute number. Displays the minute as a number without leading zeros (e.g., 0-59). The m format must appear immediately after the h or hh symbol. Otherwise, it is interpreted as a month number.
mm	Minute number. Displays the minute as a number with leading zeros (e.g., 00-59). The mm format must appear immediately after the h or hh symbol. Otherwise, it is interpreted as a month number.
s	Second number. Displays the second as a number without leading zeros (e.g., 0-59).
ss	Second number. Displays the second as a number with leading zeros (e.g., 00-59).
AM/PM	12-hour time. Displays time using a 12-hour clock. Displays AM, am, A, am/pm or a for times between midnight and noon; displays PM, pm, P, or p for A/P times from noon until midnight.
[h]	Outputs total number of hours.
[m]	Outputs total number of minutes.
[s]	Outputs total number of seconds.
s.0, s.00, s.000, ss.0,	Outputs fractional part of second.
[Black]	Displays cell text in black.
[Blue]	Displays cell text in blue.
[Cyan]	Displays cell text in cyan.
[Green]	Displays cell text in green.
[Magenta]	Displays cell text in magenta.
[Red]	Displays cell text in red.
[White]	Displays cell text in white.
[Yellow]	Displays cell text in yellow.
[Colon]	Displays cell text using the corresponding colour in the colour palette. n is a colour in the colour palette.
[conditional value]	Each format can have as many as four sections: one each for positive numbers, negative numbers, zeros, and text. Using the conditional value brackets ([ ]), you can designate a different condition for each section. For example, you might want positive numbers displayed in black, negative numbers in red, and zeros in blue. The following string formats a number for these conditions: [>0][Black]General; [<0][Red]General; [Blue]General

 

The following table shows some examples of custom number formats and numbers

displayed using the custom formats.

 

Format	Cell Data	Display
#.##	123.456	123.46
0	.2	.2
#.0#	123.456	123.46
	123	123.0
#,##0"CR";#,##0"DR";0	1234.567	1,235CR
	0	0
	-123.45	123DR
#,	10000	10
"Sales="0.0	123.45	Sales=123.5
	-123.45	-Sales=123.5
"X="0.0;"x="-0.0	-12.34	x=-12.3
$* #,##0.00;$* -#,##0.00	1234.567	$ 1,234.57
	-12.34	$ -12.34
000-00-0000	123456789	123-45-6789
"Cust. No." 0000	1234	Cust. No. 1234
;;;	Anything	(Not Displayed)
"The End"	123.45	The End
	-123.45	-The End
	text	text
m-d-yy	2/3/94	2-3-94
mm dd yy	2/3/94	02 03 94
mmm d, yy	2/3/94	Feb 3, 94
mmmm d, yyyy	2/3/94	February 3, 1994
d mmmm yyyy	2/3/94	3 February 1994
hh"h" mm"m"	1:32 AM	01h 32m
h.mm AM/PM	14:56	2.56 PM
hhmm "hours"	3:15	0315 hours
#?/?	1.25	1 1/4
#?/8	1.25	1 2/8

 

Formatting Fonts

You may choose from many different fonts, font sizes, font styles, and font colours for any cells in your workbook.

 

The Format_Default Font menu option enables you to set a default font, font size, and style that will apply to all cells in all worksheets in the workbook. Later you can change these settings for individual cells. Changes you make to individual cells will remain, even if you change the default settings.

 

Note that by default, the StatsDirect workbook uses Arial as the default font. Be sure you always use a TrueType font as the default font in order for print and display scaling to work correctly.

 

To set font formats for individual cells:

1.  Select the cells you wish to format.

2.  Choose Format_Cells and click the Font tab.

3.  Select the font format settings you want, and click OK.

 

Aligning Data

The StatsDirect workbook enables you to specify how data is aligned within a cell. The standard alignment places text along the left edge of the cell and numbers along the right edge of the cell. Logical and error values are centred.

 

To align text in a worksheet:

1. Select the cells for which you want to align the contents.

2. Select Format_Cells and select the Alignment tab.

3. Specify the horizontal and vertical alignment of data in the selected cells using the Horizontal and Vertical lists.

4. Select the Wrap Text option to wrap long strings of data to multiple lines within the cell.

5. Click OK.

 

Merging Cells

You may merge two or more cells in order to create headings that span many columns or a column entries that span many rows. You can use merged cells to, for example, create a heading for several different columns of data, or to insert a block of text on a worksheet.

 

The StatsDirect workbook removes the cell borders between the merged cells and replaces any data in the cells with the data in the top left cell in the selection. To include all data in the range in the merged cell, copy all the data into the upper-leftmost cell within the range before merging.

 

Merged cells function as a single cell on the worksheet, with the row/column reference of the cell in the top-left corner of the range. For example, if you merge cells A1:B5, the resulting cell will have the cell reference A1.

 

To merge cells:

1. Select the cells you want to merge.

2. Select Format_Cells and select the Alignment tab.

3. Select the Merge Cells option.

4. Click OK.

 

Pasting the entire merged cell range

Whenever you paste a range that contains merged cells, the StatsDirect workbook requires you to paste one or more copies of the entire range. It will not paste portions of the range containing the merged cell(s).

 

This means that you must select a destination range (the range you are pasting into) that is:

• equal to the number of rows and columns in the source range, or

• a multiple of the number of rows and columns in the source range, or

• a single cell.

For example: A range two rows deep and two columns wide that contains merged cells can be pasted into a range four rows deep and two columns wide, or two rows deep and four columns wide. It cannot, however, be pasted into a range two rows deep and three columns wide.

 

Paste Special with merged cells

Some of the Paste Special command do not work when the source or destination of the paste is a range that contains merged cells. Specifically, you cannot use Paste Special to paste formulas or values to or from a range that contains merged cells.

 

Changing Row Height and Column Width

You can set the width of columns and the height of rows using menu commands, click and drag actions, or properties and methods. In addition, the StatsDirect workbook provides commands that allow you to define the default row height and default column width for your worksheet.

 

Setting Default Row Height and Width

The StatsDirect workbook provides menu commands that allow you to define the default row height and default column width for your entire worksheet.

 

To define the default row height of a worksheet:

1. Select Format_Row_Default Height to display the Default Row Height dialog box.

2. Select the Custom option button define the default height of rows (or for StatsDirect to automatically adjust the height of rows based on the values in cells, select the Auto option button).

3. Enter a custom setting for the row height in the Custom text box.

4. Select whether the custom row height is entered as inches or centimetres from the Units drop-down list.

5. Click OK.

 

To define the default column width of a worksheet:

1. Select Format_Column_Default Width to display the Default Column Width dialog box.

2. Enter a default width for columns and select a unit from the Units drop-down list.

3. Click OK.

 

Sizing Rows and Columns Using Menu Commands

You can set the width of selected columns and the height of selected rows using menu commands.

 

To set the row height of a selection:

1. Select the rows for which you want to set the height.

2. Select Format_Row_Height to display the Row Height dialog box.

3. Select the Custom option button define a custom row height for the selected rows (or for StatsDirect to automatically adjust the height of rows based on the values in cells, select the Auto option button.).

4. Enter a custom setting for the row height in the Custom text box.

5. Select whether the custom row height is entered as inches or centimetres from the Units drop-down list.

6. Click OK.

 

To set the column width of a selection:

1. Select Format_Column_Width to display the Column Width dialog box.

2. Enter a custom width for the selected columns and select a unit from the Units drop-down list.

3. Click OK.

 

Sizing Rows and Columns Using Click and Drag Actions

When you position the pointer on the right edge of a column heading or the bottom edge of a row heading, the pointer changes to a double arrow to indicate that the row or column can be resized. Simply click and drag to resize the column or row.

 

If multiple rows are selected when you resize a row, all selected rows are resized as you drag a row border. Multiple columns can be resized in the same manner.

 

Double-click the bottom border of a row heading to automatically adjust the height of all cells in the row to accommodate the largest font size in the row. Double-click the right side of a column heading to automatically adjust the width of all cells in the column to accommodate the largest entry.

 

You can also set the size of a selected group of columns or rows to match the size of an existing row or column. First, select the group of rows or columns you want to resize, including the row or column whose size you want to match. Then, click the right border of the column heading or the bottom border of the row whose size you want to match. The selected rows are resized to match the size of the row or column you clicked.

 

Freezing Horizontal and Vertical Panes

To scroll through your worksheet and see designated headings for columns or rows, split the worksheet into panes by "freezing" them.

 

Data contained in frozen panes cannot be edited. You must perform any data editing in these panes prior to freezing. If you attempt to select a cell in a frozen row or column, the entire row or column is selected, just as if you selected a row or column heading.

 

To freeze horizontal panes:

1. Select a cell in the row below where you want to split the panes.

2. Select Format_Freeze Panes. The rows above the split are frozen.

 

To freeze vertical panes:

1. Select a cell in the column to the right of where you want to split the panes.

2. Select Format_Freeze Panes. The columns to the left of the split are frozen.

 

To freeze panes using the Format Sheet dialog box:

1. Select Format_Sheet Settings and select the View tab.

2. Enter a range for the cells that you want to freeze in the Fixed Rows and Fixed Columns text boxes. For example, to freeze columns A - B and rows 1- 4 enter $1:$4 in the Fixed Rows text box and $A:$B in the Fixed Columns text box.

3. Click OK.

 

Setting Cell Borders

Borders can be applied to the top, bottom, left, and right sides of a cell. When you add a border to a range, you can place a border around the outside of the range.

 

To format cells with borders:

1. Select the cells you want to format.

2. Select Format_Cells and select the Border tab from the Format Cells dialog box. Note that you must select a line style and colour before you select the location of the borders.

3. Select borders.

4. Click OK.

 

Setting Cell Fill Colours and Patterns

When you apply colours and patterns to a cell or range, you specify the pattern and foreground and background colours used to fill the cells.

 

To format cells with colours and patterns:

1. Select the cells you want to format.

2. Select Format_Cells and select the Patterns tab from the Format Cells dialog box.

3. Click a fill colour in the Fill Colour palette to select it.

4. Click a pattern colour in the Pattern Colour palette to select it.

5. Click a pattern style in the Fill Pattern palette to select it.

6. Click OK.

 

Formatting Row and Column Headings

In addition to formatting worksheet cells, many aspects of row and column headings can be formatted. Worksheet headings contain three areas: the row headings, column headings, and the box in the top left corner of the worksheet where the row and column headings intersect.

 

To select headings interactively:

• Press CTRL+Shift and click the heading area.

 

After a heading area is selected, you can set:

• the alignment of the heading text.

• the font and colour of the heading text.

• the pattern and fill colour of the heading area.

• the border used to frame heading cells.

 

To interactively change the size of column headings:

• Click and drag the bottom edge of the top left corner.

 

To interactively change the size of row headings:

• Click and drag the right edge of the top left corner.

 

To interactively change the text for a row or column heading:

1. Double-click the heading for which you want to enter text to display the Header Name dialog box.

2. Enter one or more lines of text to serve as the heading name.

3. Click OK.

 

Note that rows, columns, and cells are still referred to by their default numbers and letters in functions, properties, and formulas even if the heading text for rows and columns has been changed. For example, the cell at the intersection of column B and row 2 is still referred to as B2 even if the heading text for row 2 has been set to "Outcomes."