advanced spreadsheet

Lola2230

NPExcel2019_M02_PPT-CE.pptx

Home >Business & Finance homework help >Management homework help >advanced spreadsheet

New Perspectives on Microsoft Excel 2019

Module 2: Formatting Workbook Text and Data

‹#›

Objectives, Part 1

Change fonts, font style, and font color

Add fill colors and a background image

Create formulas to calculate sales data

Format numbers as currency and percentages

Format dates and times

Align, indent, and rotate cell contents

Merge a group of cells

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

‹#›

Objectives, Part 2

Use the AVERAGE function

Apply cell styles

Copy and paste formats with the Format Painter

Find and replace text and formatting

Change workbook themes

Highlight cells with conditional formats

Format a worksheet for printing

‹#›

Visual Overview Worksheet with Formatting

‹#›

Formatting Cell Text, Part 1

You can add formatting to a workbook by choosing its fonts, styles, colors, and decorative features through the use of themes

A theme is a predefined coordinated set of colors, fonts, graphical effects, and other formats that can be applied to workbooks to give them a consistent, professional look

‹#›

Formatting Cell Text, Part 2

To choose the font typeface, select the cell or range. On the Home tab, in the Font group, click the Font arrow, and then select a font name

To set the font size, select the cell or range. On the Home tab, in the Font group, click the Font Size arrow, and then select a size

To set the font style, select the cell or range. On the Home tab, in the Font group, click the Bold, Italic, or Underline button; or press CTRL+B, CTRL+I, or CTRL+U

‹#›

To set the font color, select the cell or range. On the Home tab, in the Font group, click the Font Color arrow, and then select a color

To format a text selection, double-click the cell to enter Edit mode, select the text to format, change the font, size, style, or color, and then press ENTER

Formatting Cell Text, Part 3

‹#›

Applying Fonts and Font Styles, Part 1

Theme font

Associated with a particular theme

Used for headings and body text

Change automatically when you change the theme applied to the workbook

Text formatted with a non-theme font retains its appearance no matter what theme is used with the workbook

‹#›

Applying Fonts and Font Styles, Part 2

Character styles

Serif fonts have extra strokes at the end of each character

Sans serif fonts do not have extra strokes

Every font can be further formatted with:

A font style such as italic, bold, or bold italic

Underline

Special effects such as strikethrough and color

Can increase or decrease the font size

‹#›

Applying Fonts and Font Styles, Part 3

‹#›

Applying a Font Color

Theme colors are the 12 colors that belong to the workbook’s theme: 4 for text and backgrounds, 6 for accents and highlights, and 2 for hyperlinks

Standard colors are always available

Can also create custom colors

‹#›

Formatting Text Selections Within a Cell

The Mini toolbar contains buttons for common formatting options used for the selection

‹#›

Working with Fill Colors and Backgrounds, Part 1

Filling a cell’s background with color, also known as a fill color, can be helpful for highlighting data, differentiating parts of a worksheet, or adding visual interest to a report

The same selection of colors used to change the color of cell text can be used to change the cell background

‹#›

Working with Fill Colors and Backgrounds, Part 2

‹#›

Adding a Background Image

Another way to add visual interest to worksheets is with a background image

Many background images are based on textures such as granite, wood, or fibered paper

The image does not need to match the size of the worksheet. Instead, a smaller image can be repeated until it fills the entire sheet

Background images do not affect any cell’s format or content. Fill colors added to cells appear on top of the image, covering that portion of the image

‹#›

Using Functions and Formulas with Sales Data, Part 1

In Excel, you can collet sales data such as:

Gross Sales

Cost of Sales

Operating Expenses

Net Profit/Loss

Items Sold

Sales data can be used to calculate sales statistics for an entire company or specific stores and products

‹#›

Using Functions and Formulas with Sales Data, Part 2

‹#›

Applying Number Formats, Part 1

General format displays numbers exactly as they are typed

Some numbers may require formatting to make interpretation easier; you might need to:

Change the number of digits displayed to the right of the decimal point

Add commas to separate thousands in large numbers

Include currency symbols to numbers to identify the monetary unit being used

Identify percentages using the % symbol

‹#›

Applying Number Formats, Part 2

Excel supports two monetary formats

Currency format

Places a currency symbol left of the first digit

Displays negative numbers with a negative sign

Accounting format

Fixes a currency symbol at the left edge of the column

Displays negative numbers within parentheses

Displays zero values with a dash

Slightly indents values from the right edge

‹#›

Applying Number Formats, Part 3

‹#›

Formatting Dates and Times

Dates and times are stored as numbers, not as text, so you can apply different formats without affecting values

Short Date format: mm/dd/yyyy

Long Date format displays the day of the week and the full month name in addition to the day of the month and the year

Other built-in formats include formats for time in 12- or 24-hour formats

‹#›

Formatting Worksheet Cells

Format appearance of individual cells by:

Modifying alignment of text within the cell

Indenting cell text

Adding borders of different styles and colors

‹#›

Aligning Cell Content

Default:

Text aligned with left bottom borders

Numbers aligned with right bottom border

Buttons to set alignment options are in Alignment group on Home tab

‹#›

Indenting Cell Content and Adding Borders to Cells

Indenting is useful for creating subsections

In common accounting practices:

A single black border appears above a subtotal

A single bottom border is added below a calculated number

A double black bottom border appears below the total

‹#›

Merging Cells, Part 1

Retains only content (and cell reference) from upper-left cell in the range

Merge options:

Merge & Center—merges the range into one cell and horizontally centers the content

Merge Across—merges each row in the selected range across the columns in the range

Merge Cells—merges the range into a single cell, but does not horizontally center the cell content

Unmerge Cells—reverses a merge, returning the merged cell to a range of individual cells

‹#›

Merging Cells, Part 2

‹#›

Rotating Cell Contents

Text and numbers are displayed horizontally within cells. However, you can rotate cell text to any angle to save space or to provide visual interest to a worksheet

The state names at the bottom of the merged cells would look better and take up less room if they were rotated vertically within their cells

‹#›

Exploring the Format Cells Dialog Box

Buttons on the Home tab provide quick access to commonly used formatting

The Format Cells dialog box provides more options for formatting selected cells; six tabs, each focusing on different options:

Number

Alignment

Font

Border

Fill

Protection

‹#›

Visual Overview Worksheet Formatted for Printing

‹#›

Calculating Averages

The AVERAGE function calculates the average value from a collection of numbers

The syntax of the Average function is:

AVERAGE (number1, number2, number3, …)

‹#›

Applying Cell Styles, Part 1

Use styles to ensure that cells displaying same type of data use the same format

A cell style is a collection of formatting options that include a specified font, font size, font styles, font color, fill color, and borders

All cell styles are listed in the Cell Styles gallery, which you access on the Home tab in the Styles group

‹#›

Applying Cell Styles, Part 2

‹#›

Copying and Pasting Formats

Copying formats with the Format Painter

Fast and efficient way of maintaining a consistent look and feel throughout a workbook

Copies formatting without copying data

Use Paste Options button to paste formatting from a copied range along with its contents

Use Paste Special to control exactly how to paste the copied range

‹#›

Finding and Replacing Text and Formats, Part 1

The Find and Replace commands let you make content and design changes to a worksheet or the entire workbook quickly

The Find command searches through the current worksheet or workbook for the content or formatting you want to locate

The Replace command then substitutes it with the new content or formatting you specify

‹#›

Finding and Replacing Text and Formats, Part 2

You can choose to:

Find each occurrence of the search text one at a time and decide whether to replace it

Highlight all occurrences of the search text in the worksheet

Replace all occurrences at once without reviewing

‹#›

Working with Themes, Part 1

The Office theme is the default theme applied to workbooks

When you switch to a different theme, the theme-related fonts, colors, and effects change throughout the workbook to reflect the new theme

‹#›

Working with Themes, Part 2

‹#›

Highlighting Cells with Conditional Formats, Part 1

Excel has four conditional formats:

Data bars

Highlighting

Color scales

Icon sets

‹#›

Highlighting Cells with Conditional Formats, Part 2

Highlighting Cells with Conditional Formatting

Select the range in which you want to highlight cells.

On the Home tab, in the Styles group, click the Conditional Formatting button, point to Highlight Cells Rules or Top/Bottom Rules, and then click the appropriate rule.

Select the appropriate options in the dialog box.

Click OK

The Quick Analysis tool appears whenever you select a range of cells, provides access to the most common tools for data analysis and formatting of the selected range

‹#›

You can modify any conditional formatting rule to change what is being formatted, as well as change what formatting is applied

You can remove a conditional format at any time without affecting the underlying data

When you use conditional formatting to highlight cells in a worksheet, the purpose of the formatting is not always immediately apparent. To ensure that everyone knows why certain cells are highlighted, you should document the meaning of the format

Highlighting Cells with Conditional Formats, Part 3

‹#›

Highlighting Cells with Conditional Formats, Part 4 Figure 2-34 Highlight cells rules

Rule	Highlights Cell Values
Greater Than	Greater than a specified number
Less Than	Less than a specified number
Between	Between two specified numbers
Equal To	Equal to a specified number
Text that Contains	That contain specified text
A Date Occurring	That contain a specified date
Duplicate Values	That contain duplicate or unique values

‹#›

Formatting a Worksheet for Printing, Part 1

Print settings can be applied to an entire workbook or to individual sheets

Page Break Preview shows only those parts of the active sheet that will print and how the content will be split across pages

By default, all cells in a worksheet containing text, formulas, or values are printed

‹#›

Formatting a Worksheet for Printing, Part 2

‹#›

Formatting a Worksheet for Printing, Part 3

To insert a page break:

Click the first cell below the row where you want to insert a page break, click a column heading, or click a row heading

On the Page Layout tab, in the Page Setup group, click the Breaks button, and then click Insert Page Break

To remove a page break:

Select any cell below or to the right of the page break you want to remove

On the Page Layout tab, in the Page Setup group, click the Breaks button, and then click Remove Page Break

Or In Page Break Preview, drag the page break line out of the print area

‹#›

Formatting a Worksheet for Printing, Part 4

Add print titles (descriptive information) on each page of a printout in case pages become separated

Create page headers and footers to include text not usually found within the worksheet

‹#›

Formatting a Worksheet for Printing, Part 5

Headers and footers have three sections: left, center, right

A margin is the space between the page content and the edges of the page

By default, Excel sets the page margins to

0.7 inch on the left and right sides

0.75 inch on the top and bottom

0.3-inch margins around the header and footer

You can reduce or increase these margins as needed by selecting predefined margin sizes or setting your own

‹#›

Excel | Module 2 Formatting Workbook Text and DataEX 2–2

Session 2.1 Visual Overview:

The Font group has buttons for setting the font, font size, font color, and font style, which indicates how characters are emphasized, such as bold, italic, or underline.

You can format text strings within a cell in Edit mode.

The Accounting format places the currency symbol on the left edge of the cell, adds a thousands separator, shows values to two decimals places, and displays negative values inparentheses.

A font defines the appearance and shape of letters, numbers, and special characters.

You can merge, or combine, several cells into one cell. This content is centered in the merged range A13:A21.

You can rotate content in the cell.

You can change the fill color of a sheet tab to help organize your workbook.

The Alignment group has buttons for setting horizontal and vertical alignment, orientation, and indents; wrapping text in cells; and merging cells.

25765_EX-mod2_ptg01_001-068.indd 2 23/11/18 12:14 PM

Module 2 Formatting Workbook Text and Data | Excel EX 2–3

Formatting a Worksheet

The Percent style formats numbers as percentages with the % symbol after the number. You can change how many decimal places are shown.

The Number group has buttons for applying the Accounting format, Percent style, and Comma style, as well as changing how many decimal places are displayed.

The Comma style adds a thousands separator to numbers, adds two decimal places, and lines up values within a column by their decimal points.

You can increase or decrease the number of decimal places that are displayed in cells. These values show no decimal places.

A fill color is a background color that can be added to cells. It is often used to help differentiate parts of a worksheet or highlight data.

25765_EX-mod2_ptg01_001-068.indd 3 23/11/18 12:14 PM

Module 2 Formatting Workbook Text and Data | Excel EX 2–5

Figure 2–1 Font gallery

To format the company name: ◗ 1. NP_EX_2-1.xlsx Excel2 >

Module NP_EX_2_Sales

◗ 2.

◗ 3. A1

◗ 4. Font arrow

◗ 5. Arial Black

◗ 6.

◗ 7. Calibri Light

◗ 8. Font Size arrow 28 28

◗ 9. Bold CTRL+B

◗ 10. A2

◗ 11. Font Size arrow 16

fonts in the Office theme

Live Preview of the Arial Black font

all available fonts

Arial Black font being selected

25765_EX-mod2_ptg01_001-068.indd 5 23/11/18 12:14 PM

Excel | Module 2 Formatting Workbook Text and DataEX 2–10

Setting the Worksheet Tab Color Fill colors can also be used with the sheet tabs. You can add the same tab color to sheets that share a common purpose to create a visual structure in the workbook. Stefan wants you to change the tab color of the Documentation sheet to gold.

Although you can change the sheet tab fill color, you cannot change its text color or text style.

To change the tab color of the Documentation sheet: ◗ 1. Documentation

◗ 2. Tab Color

◗ 3. Gold, Accent 4

◗ 4. Sales Report

◗ 5. Documentation

Figure 2–5 Font and fill colors added to the Documentation sheet

labels are white text on a light blue background

width of column B is 30 characters

button to wrap text within a cell

light blue text on a white background

text wrapped in the cell

◗ 5. 30

◗ 6. A4:B6

◗ 7. A7

25765_EX-mod2_ptg01_001-068.indd 10 23/11/18 12:14 PM

Module 2 Formatting Workbook Text and Data | Excel EX 2–13

◗ 3. C17 =C8/C22

◗ 4. C18 =C9/C22

◗ 5. C20 =C11/C22

◗ 6. C15:C20 D15:D20

◗ 7. B4

Figure 2–7 Overall and per-store sales

Stefan also wants to report how the company’s sales and expenses have changed from the previous year to the current year. To do this, you will calculate the net change in the sales statistics as well as the percent change. The percent change is calculated using the following formula:

percent change = current year value — previous year value

previous year value

You will calculate the net change and percentage for all of the statistics in the Sales Report worksheet.

overall sales statistics

per-store sales statistics calculated by dividing the overall statistics by the number of stores

number of stores in the current and previous years

25765_EX-mod2_ptg01_001-068.indd 13 23/11/18 12:14 PM

Excel | Module 2 Formatting Workbook Text and DataEX 2–16

When choosing between the Accounting format and the Currency format for your worksheets, you should consider accounting principles that govern how financial data should be formatted and displayed.

Formatting Numbers You can make the financial figures in your workbooks easier to read by using commas to separate the thousands values. Other formatting options include setting the number of decimal places, and using currency and percent symbols. Changing the number format does not affect the stored value, only how that value is displayed in the cell.

Applying Number Formats Cells start out formatted with the General format, which, for the most part, displays numbers exactly as they are typed. If a value is calculated from a formula or function, the General format displays as many digits after the decimal point as will fit in the cell and rounds the last digit.

The General format is fine for small numbers, but some values require additional formatting to make the numbers easier to interpret. For example, you might want to:

Change the number of digits displayed to the right of the decimal point Add commas to separate thousands in large numbers Include currency symbols to numbers to identify the monetary unit being used Identify percentages using the % symbol

Excel supports two monetary formats—Accounting and Currency. Both formats add thousands separators to the monetary values and display two digits to the right of the decimal point. The Accounting format places the currency symbol at the left edge of the column and displays negative numbers within parentheses and zero values with a dash. It also slightly indents the values from the right edge of the cell to allow room for parentheses around negative values. The Currency format places the currency symbol directly to the left of the first digit of the monetary value and displays negative numbers with a negative sign. Figure 2–10 compares the two formats.

Figure 2–10 Accounting and Currency number formats

negative values displayed with a negative sign

currency symbol placed to the left of the first digit

values are slightly indented from the right cell edge

zeros displayed with a dash

currency symbol placed on the left edge of the cell

negative values displayed in parentheses

25765_EX-mod2_ptg01_001-068.indd 16 23/11/18 12:14 PM

Excel | Module 2 Formatting Workbook Text and DataEX 2–24

The merged cells make it easier to distinguish stores in each state. Next, you will rotate the cells so that the state names are displayed vertically in the merged cells.

Rotating Cell Contents Text and numbers are displayed horizontally within cells. However, you can rotate cell text to any angle to save space or to provide visual interest to a worksheet. The state names at the bottom of the merged cells would look better and take up less room if they were rotated vertically within their cells. Stefan asks you to rotate the state names.

Figure 2–17 Merged cells

To rotate the state names: ◗ 1. A26

◗ 2. Orientation Rotate Text Up

range A35:A42 merged into a single cell, cell A35

range A43:A45 merged into a single cell, cell A43

Merge & Center button

To merge the state name cells: ◗ 1. A26:A34

◗ 2. Merge & Center

◗ 3. A35:A42 Merge & Center

◗ 4. A43:A45

25765_EX-mod2_ptg01_001-068.indd 24 23/11/18 12:14 PM

Excel | Module 2 Formatting Workbook Text and DataEX 2–30

Session 2.2 Visual Overview:

A manual page break, identied by a solid blue line, is a page break you set to indicate where a new page of the printout starts.

Print titles are rows and/or columns that are included on every page of the printout. In this case, the text in rows 1 and 2 will print on every page.

The Format Painter copies and pastes formatting from one cell or range to another without duplicating any data.

The Page Layout tab has options for setting how the worksheet will print.

25765_EX-mod2_ptg01_001-068.indd 30 23/11/18 12:14 PM

Module 2 Formatting Workbook Text and Data | Excel EX 2–31

Designing a Printout

Page Break Preview shows the location of the print area and all page breaks.

An automatic page break, identified by a dotted blue line, is a page break that Excel sets when a page of the printout is full.

The print area is the range or ranges in a worksheet that you specify to be printed. In Page Break Preview, the print area is not grayed out.

A cell style is a predesigned combination of font, font size, and font color that you can apply to a cell.

Conditional formatting applies a format to a cell when its value meets a specied condition.

The Find and Replace commands, which are available from the Find & Select button, are used to quickly make content and format changes throughout a workbook.

25765_EX-mod2_ptg01_001-068.indd 31 23/11/18 12:14 PM

Excel | Module 2 Formatting Workbook Text and DataEX 2–36

◗ 4.

◗ 5. Title

◗ 6. B5:F5

◗ 7. Cell Styles Accent4

◗ 8. A24 Title

◗ 9. A3

Figure 2–24 Cell Styles gallery

Figure 2–25 Cell styles applied to the worksheet

click to import a cell style from another workbook

click to define a custom cell style

Heading 1 cell style

Live Preview of the Heading 1 cell style

gallery of cell styles

Title cell style applied to cell B4

Accent4 cell style applied to the range B5:F5

25765_EX-mod2_ptg01_001-068.indd 36 23/11/18 12:14 PM

Module 2 Formatting Workbook Text and Data | Excel EX 2–45

To change the workbook’s theme: ◗ 1. A1

◗ 2. Page Layout

◗ 3. Themes

◗ 4.

◗ 5. Ion

Figure 2–33 Live Preview of the Ion theme

◗ 6. Ion

Changing the theme made a significant difference in the worksheet’s appearance. The most obvious changes to the worksheet are the fill colors and the fonts. Only formatting options directly tied to a theme change when you select a different theme. Any formatting options you selected that are not theme-based remain unaffected by the change. For example, a standard color or font is not affected by the theme. That is why the standard colors used in cells A1 and A2 do not change when you change the theme.

Setting Theme Colors and Fonts Businesses often use custom themes that match their company’s logo colors and fonts. To change the theme colors, click the Colors button in the Themes group on the Page Layout tab, and then select one of the color palettes. To create your own color palette, click Customize Colors to open the Create New Theme Colors dialog box. In this dialog

colors and fonts as they appear in the Ion theme

buttons to set the colors and fonts for the current theme

Themes button

Themes gallery

Ion theme in the Themes gallery

loads additional theme files

saves the current theme in a file

25765_EX-mod2_ptg01_001-068.indd 45 23/11/18 12:14 PM

Module 2 Formatting Workbook Text and Data | Excel EX 2–53

Formatting a Worksheet for Printing You should format any worksheets you plan to print so that they are easy to read and understand. You can do this using the print settings, which enable you to set the page orientation, the print area, page breaks, print titles, and headers and footers. Print settings can be applied to an entire workbook or to individual sheets. Because other people will likely see your printed worksheets, you should format the printed output as carefully as you format the electronic version.

Stefan wants you to format the printed version of the Sales Report worksheet to be distributed to the sales team at Bristol Bay.

Using Page Break Preview Page Break Preview shows only those parts of the active sheet that will print and how the content will be split across pages. A dotted blue border indicates a page break, which separates one page from another. As you format the worksheet for printing, you can use this view to control what content appears on each page.

Stefan wants to know how the Sales Report worksheet would print in portrait orientation and how many pages would be required. You will look at the worksheet in Page Break Preview to find these answers.

To view the Sales Report worksheet in Page Break Preview: ◗ 1. A1

◗ 2. Page Break Preview

◗ 3. 25%

Figure 2–41 Sales Report worksheet in Page Break Preview

Trouble?

25765_EX-mod2_ptg01_001-068.indd 53 23/11/18 12:14 PM