Text Formatting

Introduction

Text Formatting gives complete control to the way a cell displays its content. To format text on a worksheet, you use the Object bar, the Cell Attributes dialog box, the Style Catalog dialog box, or a combination of two or three or them.

The Object bar provides a quick means of selecting a font, a font size, and/or a font style. The same Object bar toolbar is equipped with many other buttons that we will review as the lessons progress.

The Cell Attributes dialog box provides more features and controls than the Object bar. Using this dialog, you have an extended list of colors and underlines. We will also review more details of it.

The Style Catalog dialog box allows you to create a style or particular styles to apply to various sections of the worksheet.
 

The Starting Worksheet

Once again we need to create a starting application before continuing with our exercise. This is done on purpose for various reasons. First, I believe that experience comes from using the software over and over again, that's how you get used to it. Second, if or when I ask you to open a workbook, I don't want you wondering how it was created.

We are about to embark in the work of text, rows, columns, and cells editing. One of your main design capabilities as an application developer is revealed in the way you create an interface. But before we get there, let's review how to enter data in StarCalc. 

Practical Learning: Creating a Starting Worksheet

1. In cell C2, type Drug Mentions in the DAWN System for 1992
2. In cell A3, type Emergency Room Episodes
3. In cell F3, type Drug-Related Deaths
4. In cell D4, type Percentage
5. In cell C5, type Number of and press Tab
6. In cell D5, type 'of total
7. Click cell A6, type Rank and press Tab
8. In cell B6, type Drug Name and press the right arrow key.
9. In cell C6, type 'mentions and press the right arrow key.
10. In cell D6, type 'episodes
11. In cell A7, type '1. and press the down arrow key.
12. In cell A8, type '2. and press Enter
13. In cell A9, type '3.
14. In cell A10, type '4.
15. In cell A11, type '5.
16. In cell A12, type '6.
17. In cell A13, type '7.
18. In cell A14, type '8.
19. In cell A15, type '9.
20. In cell A16, type '10.
21. Press Enter.
22. Save the workbook as The DAWN Report1

Copying and Pasting Review

If you have StarCalc, take advantage of its clipboard.
 

Practical Learning: Copying and Pasting Cells Contents

1. Select cells from A4 to D16 and press Ctrl + C.
2. Click cell F4 and press Ctrl + V.
3. Click cell B7 and type Alcohol-in-combination press Enter.
4. In cell B8, type Cocaine and press Enter.
5. In cell B9, type Heroine/Morphine and press Enter.
6. In cell B10, type Acetaminophen and press Enter.
7. In cell B11, type Marijuana/Hashish and Enter.
8. In cell B12, type Aspirin
9. In cell B13, type Alprazolam (Xanox)
10. In cell B14, type Ibuprofen
11. In cell B15, type Diazepam (Valium)
12. In cell B16, type Amitriptyline and press Enter
13. In cell B17, type m and press Enter (typing M should auto-complete the cell with Marijuana/Hashish).
14. Click cell B8. On the main menu, click Edit -> Copy.
15. Click cell G7. On the main menu, click Edit -> Paste.
16. Click cell B7. On the Function bar, click the Copy button .
17. Click cell G8. On the Function bar, click the Paste button .
18. Click cell B9 to give it focus.
19. Press and hold Shift. Then click cell B9 again and release Shift.
20. Press Alt. Position your mouse on the lower border of the selected cell (cell B9). Click and drag in the right direction until you get to cell G9 (a small box displaying on the mouse pointer will guide you):
     
    
21. Then release the mouse and the Alt key. Notice that the cells content has been copied
22. Click cell G10 and type Codeine
23. Click cell B15 to select it. Right-click cell B15 and click Copy.
24. Click cell G11 to select it. Right-click cell G11 and click Paste.
25. Click cell G12 and type Methadone
26. Click cell B16 to make it active. Right-click cell B16 and click Copy.
27. Click cell G13 and press Ctrl + V
28. Click cell G16 and press Ctrl + V.
29. Click cell G14 and type D-propoxyphene
30. Click cell B17 to give it focus.
31. Press and hold Shift. Then click cell B17 again
32. Position your mouse on the left border of the selected cell (cell B17). Click and drag to the right and up direction until you get to cell G15 (a small box displaying on the mouse pointer will guide you). Then release the mouse. That will move the content of B17 to G15.
33. Double-click cell G16, press Home, press Delete three times, and type Nor and press Enter. That will change the content of G16 from Amitriptyline to Nortriptyline
     
    
34. To save the workbook, on the main menu, click File -> Save.
35. Now we will enter the right statistical values in the table. We will enter one value in one cell and use it to pre-configure all the others.
    In cell C7, type 141773 and press Enter
36. Now we will use the content of cell C7 to format the other cells.
    Select cells C7:D16.
37. Press and hold Ctrl, then select cells H7:I16. Release Ctrl.
38. Right-click in the selection and click Format Cells...
39. In the Cell Attributes dialog box, click the Numbers property sheet. In the Category list box, make sure Number is selected.
40. In the Options section, click the Thousands Separator check box
     
    
41. Click OK.
42. Click cell C8 and type 119843 and press Enter. Notice that the number has been formatted automatically.
43. In cell H7, type 3465, press the down arrow key, type 2944 and press Enter.
44. Now we will review how to "Clear cell formats".
    In cell D7, type 3270. In cell I7, type 4600 and press Enter. The numbers display as 3,270 and 4,600 respectively. That's not the way we want our numbers displayed. The numbers in columns D and I represent percentage values; so, instead of 3,270 and 4,600, we need to have 32.70% and 46.00% respectively.
    Click cell D7 to select it.
45. Right-click cell D7 and click Format Cells...
46. In the Cell Attributes dialog box, click the Numbers property sheet if necessary. To clear the format, in the Format list box, click General and click OK. Now the number displays as a normal number. What a high percentage!
47. To configure cell D7 as holding a percentage number, while cell D7 is still selected, click the Number Format:Percent button on the Object bar. Now, it is still (very) bad.
48. To reset the number in cell D7, on the Standard toolbar, click Undo (or press Ctrl + Z)
49. Since the column headers in cells D4:D6 and I4:I6 are explicitly saying that their values represent percentage numbers, we don't need to display the % sign in their children cells, just the values but formatted as percentage numbers. That's a very good reason to create our own format values.
    Select cells D7:D16 and cells I7:I16 altogether.
50. Right-click in the selection and click Format Cells.
51. You should be in the Numbers property sheet. In the Category list, click User-Defined.
52. In the Format Code text box, type ##\.#0
53. Click OK.
54. In cell C9, type 31355 and press the down arrow key. Type 23997 and press the down arrow key. Type 18834, down. Type 16498, down. Type 16400, down. Type 13947, down. Type 10132, press the right arrow key.
55. In cell D16, type 234 and press the up arrow key. Type 322, press the up arrow key. Type 378, up. Type 381, up. Type 434, up. Type 554, up. Type 723, up. Type 1107, up. Type 2765 and press Enter.
56. Notice how each number is automatically formatted to suit our need.
    Complete the worksheet as follows:
     
    
57. Notice that, in column H, the numbers that have less than three digits don't display a comma sign.
58. Also notice that, unlike the traditional percentage values that ignore the most right zero on a number, our format displays 46.00 (with two zero decimal values) and 8.50 and 5.50 display their right zeros, in column I.
59. Save the workbook. 

Introduction to Text Formatting

Just like the Function bar, the Object bar is equipped with a lot of buttons. This time, the toolbar starts on its left with two combo boxes. To find out what any item is used for, position your mouse on top of a button for one or two seconds, a tool tip will give you a hint.

Besides the Object bar, you can format a cell and its content using the Cell Attributes dialog box. You can access the Cell Attributes dialog box from the Format -> Cells... menu, or you can right-click on a selected cell or a group of selected cells and choose Format Cells...
 

Practical Learning: Exploring the Text Format Features

1. Open the DAWN Report2 workbook. Click Sheet1.
2. Click cell C2 to give it focus.
3. On the Object bar, click the arrow of the Font combo box, scroll down in the list and click Times New Roman (Times New Roman should be available on both Microsoft and Linux operating systems).
4. Click the arrow of the Font Size combo box and select 12.
5. Click the Bold button.
6. Click the Font Color button to see a list of colors. Select Blue (2nd column - 1st row)
    
   
7. Click cell A3 to select it. Right-click cell A3 and choose Format Cells... That will call the Cell Attributes dialog box.
8. From the Format Cells dialog box, click the Font property sheet.
9. From the list of Fonts, select Arial (Arial  should be available on both Microsoft and Linux operating systems).
10. In the Typeface list, click Bold.
11. In the Size list, click 10.5
12. Click the Color combo box and select Red 2
     
    
13. Click OK.
14. Click cell F3 to select it. On the main menu, click Format Cells. Click the Font property sheet and make the same selections as for cell A3 and click OK
15. Select cells A4:I6. Right-click in the selection and click Format Cells...
16. In the Fonts property sheet, select the font as Verdana (if you don't have Verdana, select Arial). Set the Typeface as Bold and the size to 10. Set the color to Light Red and click OK
17. Select cells A7:B16.
18. Click the arrow of the Font Color button and select the Light Blue (2nd column - 2nd row).
19. Select cells F7:G16. Click the Font Color button on the Object bar and select Light Blue.
20. Select cells C7:D16 and H7:I16
21. Click the arrow of the Font Color button and select the Green color (3rd column - 1st row).
22. Press Ctrl + Home to see the result
23. To save the workbook, on the Function bar, click the Save button .