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.
Your task is to reproduce the four text entries in Excel.
- Open a new Excel workbook and save the file with an xlsx extension
- The technical specifications for the worksheet, and each text entry is described in the Excel online file – see figure 2
- 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
- Use Alt + Enter for the hard line break
- Note: Excel Online drops the Picture image in item 2.4. Scroll to the right for the top layer rendition
- Most of the format items are available on the Home tab
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: " &(with hard-coded line breaks to improve readability) returns Range: N52:N55
ADDRESS(ROW(_3x3.table), COLUMN(_3x3.table), 4) &
ADDRESS(ROW(_3x3.table) + ROWS(_3x3.table) - 1, COLUMN(_3x3.table) + COLUMNS(_3x3.table) - 1, 4)