Text formats

Test your skills at worksheet text entry and formatting. Figure 1 shows 4 examples of text entries in Excel. Examples (index in column B of figure 1), 2.1, 2.2, and 2.3 are each in a single cell, and 2.4 is a formatted multi column table overlaid (on the drawing layer) on a single column on the top layer. Each example has an associated << task # name.

Required

Your task is to reproduce the four text entries in Excel.

1. Open a new Excel workbook and save the file with an xlsx extension
2. The technical specifications for the worksheet, and each text entry is described in the Excel online file – see figure 2
3. For example, task 1 (item 2.1) has specifications:
   Cell C16: (yes, it’s in column C but overflows into column D)
   Font: Calibri 11 point
   Font Color – Red: “Red”
   Font Color – Green: “Green”
   Alignment: Increase indent x 2

4. Use Alt + Enter for the hard line break
5. Note: Excel Online drops the Picture image in item 2.4. Scroll to the right for the top layer rendition
6. Most of the format items are available on the Home tab

Additional features

Several formulas are provided to illustrate construction of dynamic text strings – this material is covered in 90045 session 3. Basically, dynamic text allows references to remain valid if the user inserts/deletes rows, or performs similar changes to the worksheet.

• =SUBSTITUTE(CELL("address", D19),"$","")&": "&FORMULATEXT(D19) returns Cell: C16
• ="Range: " & ADDRESS(ROW(_3x3.table), COLUMN(_3x3.table), 4) & ":" & ADDRESS(ROW(_3x3.table) + ROWS(_3x3.table) - 1, COLUMN(_3x3.table) + COLUMNS(_3x3.table) - 1, 4) returns Range: N52:N55, or
• ="Range: " &
ADDRESS(ROW(_3x3.table), COLUMN(_3x3.table), 4) &
":" &
ADDRESS(ROW(_3x3.table) + ROWS(_3x3.table) - 1, COLUMN(_3x3.table) + COLUMNS(_3x3.table) - 1, 4) (with hard-coded line breaks to improve readability) returns Range: N52:N55