Jazzing Up Spreadsheets With AutoFormat

Jazzing Up Spreadsheets With AutoFormat

The program comes with a set of predefined table formats that can help jazz up a spreadsheet for printing. You can apply those, or create your own and apply one.

Applying an AutoFormat

To apply one of these AutoFormats to a spreadsheet:

  1. Select the area to which you want to apply an AutoFormat.

  2. Choose Format > AutoFormat.

  3. In the AutoFormat window, select the format and options you want. Use Figure 19-9 for guidance.

    Figure 19-9. Applying an AutoFormat to a spreadsheet

    graphics/19fig09.jpg

  4. Click OK.

If you don't like the results of the AutoFormat, click the Undo icon in the function bar until your spreadsheet is back to where it was before you applied the AutoFormat. If you don't have enough undos to get back to your starting format, reformat the spreadsheet manually.

A more automated way to get back to a previous format is if you add the previous format as an AutoFormat that you can select in the AutoFormat list ( next procedure).

Adding an AutoFormat

You can add your own spreadsheet formats to the list of the program's AutoFormats.

  1. Format the spreadsheet the way you want it.

    You don't have to include data in the spreadsheet; just column, row, and cell formatting.

  2. Select the formatted area, and choose Format > AutoFormat.

  3. In the AutoFormat window, click the Add button.

  4. In the Add AutoFormat window, type a name for the new format, and click OK.

    Your format is added to the list.

  5. Click OK in the AutoFormat window.

You can delete or rename AutoFormats by selecting them in the AutoFormat window and clicking the Delete or Rename buttons .

Quick Cell Formatting

This section provides information for basic cell formatting techniques in Calc you perform manually by selecting cells and applying different attributes to them. Cell formats apply to cells and to the data contained within them.

If you work with spreadsheets a lot, and you reuse a lot of common elements and formatting such as headings, totals, borders, colors, and so on, we highly recommend using styles. See Power Formatting With Styles and Templates on page 554.

For simple, quick-and-dirty cell formatting such as changing font and font size, applying bold, italic, and underline, applying vertical and horizontal text alignment, applying background colors and borders to cells, and making cells display currency or percentage formats, use the object bar. For more advanced character formatting options, see Applying Formatting and Security Using the Cell Attributes Window on page 542.

Protected cells cannot be formatted. Format menu options are not available for cells that are protected. For more information, see Protecting Cells From Modification on page 602.

Conditional Formatting

Because conditional formatting is closely related to cell contents that change as a result of formulas, it is covered in the next chapter. See Conditional Formatting on page 578.

Quick Number Formats

You can apply basic currency and percentage formats to cells by selecting the cells and clicking either the Currency or Percent icons on the object bar. You can also remove currency or percent formatting by selecting the cells you want and clicking the Standard icon in the object bar.

graphics/19inf09.jpg

Quick Decimal Control

You can add decimals places to and remove them from selected cells by clicking the Add Decimal Place or Delete Decimal Place icons on the object bar.

graphics/19inf10.jpg

Removing decimal places means that calculated or manually entered amounts that have decimals are rounded to the nearest decimal place. For example, if a cell is set to show a currency amount with no decimal places, and you enter the number 15.75 , Calc displays $16 .

Changing the Default Number of Decimal Places

By default, Calc includes two decimal places in number formats. You can change the default number of decimal places. Removing decimal places means that calculated or manually entered amounts that have decimals are rounded to the nearest decimal place. For example, if a cell is set to show a currency amount with no decimal places, and you enter the number 15.75 , Calc displays $16 .

  1. Choose Tools > Options > Spreadsheet > Calculate.

  2. Change the number in the Decimal places field and click OK.

Quick Font and Cell Background Color

You can change font and background colors from the object bar for selected cells.

  1. Select the cells whose font or background color you want to change.

  2. Click the Font Color or Background Color icons on the object bar, in Figure 19-10.

    Figure 19-10. Setting font and cell background colors

    graphics/19fig10.jpg

  3. Select the color you want. The colors are from the standard color palette.

Quick Cell Borders

Select the cells you want to apply a border to, click the Borders icon on the object bar, and select the type of border you want to apply.

graphics/19inf11.jpg

For different types of lines, use the Borders tab in the Cell Attributes window. See Applying Formatting and Security Using the Cell Attributes Window on page 542.

Quick Vertical Alignment

If row heights are larger than the height of the data inside the row cells, you can change the vertical alignment of the cell data, as shown in Figure 19-11. Select the cells you want to vertically align and click one of the Align icons on the object bar.

Figure 19-11. Different vertical alignments

graphics/19fig11.gif

graphics/19inf12.jpg

Hiding Zero Values

You can show only relevant spreadsheet data by hiding cell values that are zero.

  1. Choose Tools > Options > Spreadsheet > Contents.

  2. Deselect the Zero Values option and click OK.

Merging Cells

You can select multiple cells and make them, in appearance, as one cell. This is particularly useful, for example, when you want to apply background formatting to text that spans multiple cells. When the multiple cells are merged, you only have to set the background color for one cell. If you don't merge the cells, you'd have to set the background color for each cell the text spans .

  1. Select the cells you want to merge.

  2. Choose Format > Merge Cells > Define.

    If more than one of the cells contains data, a dialog box asks you how you want to handle the data, as shown in Figure 19-12.

    Figure 19-12. Merging cells

    graphics/19fig12.jpg

To un-merge cells, select the merged cell and choose Format > Merge Cells > Remove.

Instead of merging cells that contain text and numbers, which removes any formulas that generated the numbers (as shown in Figure 19-12), you can combine those cells in a different part of the spreadsheet through concatenation. Concatenation leaves the original data, including formulas, intact. See Combining Cells on page 581.