chapter 14 formatting a workbook - calhoun unit 40 · • lo14.2: format cells and ranges •...

Chapter 14 Formatting a Workbook

Learning Objectives

• LO14.1: Format text, numbers, dates, and time • LO14.2: Format cells and ranges • LO14.3: Create an Excel table • LO14.4: Highlight cells with conditional formatting • LO14.5: Hide worksheet data • LO14.6: Format a worksheet for printing

CMPTR Chapter 14: Formatting a Workbook 2

LO14.1: Formatting Data in Cells

• Topics Covered: – Formatting Text – Formatting Numbers – Formatting Dates and Times


Formatting for readability and appeal • Clearly identify the worksheet’s purpose • Don’t crowd a worksheet with too much information • Place important information first • Use consistent formatting • Pay attention to formatting of printed workbook

CMPTR 4

Formatting Text

• Formatting text involves changing fonts, font sizes, font styles, and color.


Formatting Text


Formatting Numbers

• The numbers displayed in cells are either values entered directly in cells or values calculated with formulas.

• Can be formatted using: – Number format: displays values in a way that makes it

easy for them to be understood and interpreted – General number format: default number format, which,

for the most part, displays values exactly as they are typed


Formatting Dates and Times

• Because Excel stores dates and times as numbers and not as text, you can apply different formats without affecting the date and time value.


Formatting Dates and Times


Formatting Color

• Formatting tools are organized by themes. Fonts and colors have theme and non-theme choices

• A spreadsheet can be assigned a theme which would apply a specific font, bullet, and color.

• Colors are organized into theme and non-theme colors • Color transforms a plain workbook and captures user attention • Excel displays black as font color by default • Non-theme colors are the standards colors: dark red, red,

orange, yellow, etc. These are available regardless the theme of the workbook

• Theme colors are 12 colors designed to work well together in readable combinations

CMPTR 10

LO14.2: Formatting Cells and Ranges • Topics Covered:

– Applying Cell Styles – Aligning Cell Content – Indenting Cell Content – Merging Cells – Adding Cell Borders – Changing Cell Background Color – Using the Format Cells Dialog Box


Applying Cell Styles

• One way to ensure that you are using consistent formats is to copy and paste the formats using the Format Painter.

• A second way is to use cell styles – A style is a selection of formatting options using a specific

font and color from the current theme. • Excel has a variety of built-in styles to format worksheet titles,

column and row totals, and cells with emphasis.


Style

• Selection of formatting options using a specific font and color from the current theme

• Cell styles gallery offers a variety of built in styles • Some styles are based on the current theme and will change if

the theme changes

CMPTR 13

Aligning Cell Content

• Unless modified, cell text is aligned with the left and bottom borders of a cell, and cell values are aligned with the right and bottom borders.


Indenting Cell Content

• Sometimes you want a cell’s content moved a few spaces from the cell left edge.

• Each time you click the Increase Indent button in the Alignment group on the Home tab, you increase the indentation by roughly one character space.

• To decrease or remove an indentation, click the Decrease Indent button.


Indenting Cell Content


Merging Cells

• Merging combines two or more cells into one cell. • Merge options:

– Merge & Center – Merge Across – Merge Cells – Unmerge Cells


Merging Cells


Adding Cell Borders

• A border is a line you add along an edge of a cell.


Changing Cell Background Color

• You can add background colors, also known as fill colors, to cells using the theme color palette.


Using the Format Cells Dialog Box

• The Format Cells dialog box has the following six tabs, each focusing on a different set of formatting options: – Number – Alignment – Font – Border – Fill – Protection


LO14.3: Creating an Excel Table

• Topics Covered: – Using Banded Rows – Selecting Table Style Options – Adding Formulas to an Excel Table


LO14.3: Creating an Excel Table


Using Banded Rows

• Banded rows (banded columns): formatting that displays alternate rows (or columns) in an Excel table with different fill colors.

• Makes data easier to read, especially in large tables with many rows.


Using Banded Rows


Selecting Table Style Options

• After you apply a table style, you can change whether to show or hide the header row, total row, banded rows, and banded columns in the table, as well as whether to format the first column and last column of the table.


Selecting Table Style Options

CMPTR Chapter 14: Formatting a Workbook 29 By default the last column is added or if it is text the records are counted

Adding Formulas to an Excel Table

• When you enter a formula in one cell of an Excel table, the formula is automatically copied to all other cells in that column; this is called a calculated column.


Adding Formulas to an Excel Table


LO14.4: Highlighting Cells with Conditional Formatting • Topics Covered:

– Highlighting a Cell Based on Its Value – Clearing a Conditional Formatting Rule


Highlighting a Cell Based on Its Value • Cell highlighting changes a cell’s font color or background fill

color or both based on the cell’s value.


Highlighting a Cell Based on Its Value


Clearing a Conditional Formatting Rule • If you no longer want to highlight cells using the conditional

formatting, you can remove, or clear, the current highlighting rule.


LO14.5: Hiding Worksheet Data

• One way to manage the contents of a large worksheet is to selectively hide (and later unhide) rows and columns containing extraneous information.


LO14.6: Formatting a Worksheet for Printing • Topics Covered:

– Setting the Print Area – Inserting and Removing Page Breaks – Adding Print Titles – Creating Headers and Footers – Setting the Page Margins – Centering Content on a Page


Setting the Print Area

• The region that is sent to the printer from the active sheet is known as the print area.

• The easiest way to set the print area is in Page Layout view or Page Break Preview.


Inserting and Removing Page Breaks • Often the contents of a worksheet

do not fit onto a single page. • Automatic page break: Excel

inserts when no more content will fit on the page.

• Manual page break: you insert to specify where a page break occurs.


Adding Print Titles

• A good practice is to include descriptive information such as the company name, logo, and worksheet title on each page of a printout in case a page becomes separated from the other pages.

• A print title is information from a workbook that appears on every printed page.


Creating Headers and Footers

• Recall that headers and footers contain helpful and descriptive text that is usually not found within the worksheet, such as the workbook’s author, the current date, or the workbook file name.

• A header is information that appears in the top margin of each printed page.

• A footer is information that is printed in the bottom margin of each printed page.


Creating Headers and Footers


Setting the Page Margins

• Another way to fit a large worksheet on a single page is to reduce the size of the page margins.


Centering Content on a Page

• To center the content on the page, open the Page Setup dialog box, display the Margins tab, and then select the Horizontally and Vertically check boxes to center the content of the current sheet on the page.


chapter 14 formatting a workbook - calhoun unit 40 · • lo14.2: format cells and ranges •...

Documents