Excel2007 Formatting-Computer Networks-Lecture Handouts, Lecture notes of Computer Networks

Download Excel2007 Formatting-Computer Networks-Lecture Handouts and more Lecture notes Computer Networks in PDF only on Docsity!

Excel 2007

Formatting

• WORKSHOP DESCRIPTION ................................................................
  • Overview
  • Prerequisites
  • Objectives
• CONDITIONAL FORMATTING .............................................................
  • Format All Cells by Using a Two-Color Scale
  • Format All Cells by Using a Three-Color Scale
  • Format All Cells by Using Data Bars
  • Format all cells by using an icon set
  • Text, Number, or Date or Time Values
  • Format Only Top or Bottom Ranked Values
  • Format Only Values That Are Above or Below Average
  • Format Only Unique or Duplicate Values
  • Use a Formula to Determine Which Cells to Format
  • Clear Conditional Formats
    • Worksheet
    • A range of cells, table, or PivotTable
• TEMPLATES .......................................................................................... - Creating a New Template - EXERCISE
• FORMULAS............................................................................................
  • Precedence of Operators
  • Cell Referencing
    • Understanding Relative, Absolute and Mixed References
    • References to Worksheets in the Same Workbook
    • References to worksheets in other workbooks
• FUNCTIONS ...........................................................................................
  • Entering functions manually
  • Entering functions using the Function Wizard
    • EXERCISE
• CHARTS .................................................................................................
• CHANGE THE CHART TYPE OF AN EXISTING CHART ................... - EXERCISE
• MAIL-MERGE DATA TO WORD...........................................................
• WHAT HAPPENED TO EXCEL LISTS?...............................................
• LISTS / TABLES ....................................................................................
  • Sorting
  • Entering Data Into a List / Table
    • Using the Quick Access Toolbar
    • Entries
  • Auto Filter
    • EXERCISE
• RESOURCES .........................................................................................
  • Faculty / Staff Resource Center
  • Getting Help
  • Campus Resources

WORKSHOP DESCRIPTION

Overview

The first leg of the Excel Workshop series was designed primarily for those who had little experience with Excel. Participants were able to flavor the basics of a spreadsheet, create and modify worksheets, format and enhance worksheets, manage workbooks and setup their work in a presentable fashion.

This is the second leg of this series and designed to cover topics such as conditional formatting, creating templates, formulas and functions, charting techniques, mail merge and creating lists.

The conclusion of this series is designed primarily for Pivot Tables and Macros. To start off we will cover a general know how on Pivot Tables, followed by creation of a Pivot Table and a Pivot Chart. Later on the workshop comprises of recording macros, macro menus and global macros.

Prerequisites

Excel 2007 Basics (optional)

Objectives

Participants attending this workshop will:

Apply Conditional formatting to worksheets.
Learn how to create a template, apply the necessary formatting and then save it for future use.
Understand use of Formula’s and Functions
Learn how to create Charts
Work with Mail Merge &
Create meaningful lists.

Format All Cells by Using a Two-Color Scale

Color scales are visual guides that help you understand data distribution and variation. A two-color scale helps you compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a green and red color scale, you can specify that higher value cells have a more green color and lower value cells have a more red color.

step 1. Select one or more cells in a range, table, or PivotTable report. step 2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales. step 3. Select a two-color scale.

Format All Cells by Using a Three-Color Scale

Color scales are visual guides that help you understand data distribution and variation. A three-color scale helps you compare a range of cells by using a gradation of three colors. The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have a green color, middle value cells have a yellow color, and lower value cells have a red color.

step 1. Select one or more cells in a range, table, or PivotTable report. step 2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales. step 3. Select a three-color scale. The top color represents higher values, the center color represents middle values, and the bottom color represents lower values.

Format All Cells by Using Data Bars

A data bar helps you see the value of a cell relative to other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower numbers, especially with large amounts of data, such as top selling and bottom selling toys in a holiday sales report.

step 1. Select one or more cells in a range, table, or PivotTable report. step 2. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Data Bars, and then select a data bar icon.

Format all cells by using an icon set

Use an icon set to annotate and classify data into three to five categories separated by a threshold value. Each icon represents a range of values. For example, in the 3 Arrows icon set, the green up arrow represents higher values, the yellow sideways arrow represents middle values, and the red down arrow represents lower values.

step 1. Select one or more cells in a range, table, or PivotTable report. step 2. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Icon Set, and then select an icon set.

Format Only Values That Are Above or Below Average

You can find values above or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an annual performance review or you can locate manufactured materials that fall below two standard deviations in a quality rating.

step 1. Select one or more cells in a range, table, or PivotTable report. step 2. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Top/Bottom Rules. step 3. Select the command that you want, such as Above Average or Below Average. step 4. Enter the values that you want to use, and then select a format.

Format Only Unique or Duplicate Values

step 1. Select one or more cells in a range, table, or PivotTable report. step 2. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules. step 3. Select Duplicate Values. step 4. Enter the values that you want to use, and then select a format.

Use a Formula to Determine Which Cells to Format

If your conditional formatting needs are more complex, you can use a logical formula to specify the formatting criteria. For example, you may want to compare values to a result returned by a function or evaluate data in cells outside the selected range.

You can choose more than one format. The formats that you select are displayed in the Preview box.

step 1. Select one or more cells.

step 2. On the Home tab, in the Styles group, click the arrow next

to Conditional Formatting, and then click Manage Rules.

step 3. Do one of the following:

a. To add a conditional format, click New Rule.

b. To change a conditional format, do the following:

Make sure that the appropriate worksheet, table,

or PivotTable report is selected in the Show formatting rules for list box.

Optionally, change the range of cells by clicking

Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog.

c. Select the number, font, border, or fill format that you want to apply when the

cell value meets the condition, and then click OK.

Clear Conditional Formats

Worksheet

step 1. On the Home tab, in the Styles group,

click the arrow next to Conditional Formatting , and then click Clear Rules.

step 2. Click Entire Sheet.

A range of cells, table, or PivotTable

step 1. Select the range of cells, table, or

PivotTable for which you want to clear conditional formats.

step 2. On the Home tab, in the Styles group,

click the arrow next to Conditional Formatting , and then click Clear Rules.

step 3. Depending on what you have selected,

click Selected Cells, This Table, or This PivotTable.

TEMPLATES

A template file is a model that can serve as the basis for new worksheets. A template can include both data and formatting information. Template files are great time-savers. They’re also an ideal way to ensure a consistent look among reports, invoices, and other documents you tend to create repeatedly.

The advantages to using templates are standardization and protection. It is difficult to save over the original accidentally, because when you save a new template-based workbook for the first time, you must supply a new file name for it. This way you can repeatedly create new workbooks with the same appearance without worrying about overwriting the original.

Creating a New Template

step 1. Open the workbook you want to use as a template.

step 2. Click Office Button, Save As, and supply a file name.

step 3. Choose Excel Template from the Save As Type list, and click Save.

When you choose the Template format in the Save As dialog box, Excel switches to the Template folder so that your new template will be saved there. This is the location that ensures that your template will always be available when you click Office Button, New, and click on My Templates.

Precedence of Operators

Operators are symbols that represent specific mathematical operations, including the plus sign (+), minus sign (-), division sign (/), and the multiplication sign (*). When performing these operations in a formula, Excel follows certain rules of precedence:

Expressions within parentheses are processed first.
Multiplication and division are performed before addition and subtraction.
Consecutive operators with the same level of precedence are calculated from left to right.

First ( )

Second * /

Third + -

Cell Referencing

A cell reference identifies a cell or group of cells in a workbook.

When you include cell references in a formula, the formula is linked to the referenced cells. The resulting value of the formula is dependent on the values in the referenced cells and changes automatically when the values in the referenced cells change.

Cell references are especially helpful when one creates complex formulas.

Understanding Relative, Absolute and Mixed References

Relative references: Refers to cells by their position in relation to the cell that contains the formula. Example : =A
Absolute references: Refers to cells by their fixed position in the worksheet. Example: =$A$
Mixed references: Contains a relative reference and an absolute reference. Example: =$A1 or A$1. If the dollar sign precedes only the letter (A, for example), the column coordinate is absolute and the row is relative. If the dollar sign precedes only the number (1, for example), the column coordinate is relative and the row is absolute.

Absolute and mixed references are important when you begin copying formulas from one location to another in your worksheet. When you copy and paste, relative references adjust automatically, while absolute references do not.

References to Worksheets in the Same Workbook

You can refer to cells in other worksheets within the same workbook just as easily as you refer to cells in the same worksheet.

For example, to enter a reference to cell A9 in Sheet2 into cell B10 in Sheet1, do this:

step 1. Select cell B10 in Sheet1, and type an equal sign.

step 2. Click the Sheet2 tab.

step 3. Click cell A9, and then press Enter.

After you press Enter, Sheet1 is made active. Select cell B10, and you will see that it contains the formula =Sheet2!A9.

The worksheet portion of the reference is separated from the cell portion by an exclamation point. Note also that the cell reference is relative, which is the default when you select cells to create references to other worksheets.

References to worksheets in other workbooks

You can refer to cells in worksheets in separate workbooks in the same way that you refer to cells in other worksheets within the same workbook. These references are called external references.

For example, to enter a reference to Book2 into Book1, follow these steps:

Create a new workbook—Book2—by clicking the New button on the Quick Access toolbar.
Choose View Tab > Window Group > Arrange All.
Select cell A1 in Sheet1 of Book1, and type an equal sign.
Click anywhere in the Book2 window to make the workbook active.
Click the Sheet2 tab at the bottom of the Book2 window.
Click cell A2.
Press Enter to lock in the reference.

EXERCI SE 2

FORMULAS, FUNCTIONS AND CELL REFERENCING

1. Using Formulas, Functions and cell referencing

2. From the workshop folder named Workshop Series 2 on your desktop, open

the file named excercise2.xls in Excel.

3. Get the sum of the Second Quarter in cell C7. Use the formula approach by

typing in =C2+C3+C4+C

4. Get the sum of the Third Quarter in cell D7. Use the function approach by

typing in =SUM(D2:D5)

5. Get the sum of the Fourth Quarter in cell E7. Use the drag approach by

selecting the cell D7 and dragging it over to E7.

6. Calculate the value for Average (hint: function Average), Highest Sales (hint:

function Max) and Lowest Sales (hint: function Min).

7. Save this file.

8. Create a new worksheet, and rename it Summary. Using cell referencing create

a summary to indicate the Average, Highest Sales and Lowest Sales.

CHARTS

Microsoft Excel includes a powerful and versatile charting engine. Over the years, Microsoft has continued to add both more analytic features and more presentation capabilities to this component of the program. You can now choose from an extensive variety of common business and technical chart types, and you can enhance the appearance of your charts with pictures, clip art, WordArt text, lines, arrows, and many other devices that help make a stronger visual impact…

Charts often help you see trends and patterns that may be harder to spot by just looking at numbers.

Creating a chart in Microsoft Office Excel is quick and easy. Excel provides a variety of chart types that you can choose from when you create a chart. For more information about the chart types that you can use, see Available chart types.

For most charts, such as column and bar charts, you can plot the data that you arrange in rows or columns on a worksheet in a chart. Some chart types, however, such as pie and bubble charts, require a specific data arrangement.

step 1. On the worksheet, arrange the data that you want to plot in a chart.

step 2. Select the cells that contain the data that you want to use for the chart.

TIP : If you select only one cell, Excel automatically plots all cells containing data that directly surround that cell into a chart. If the cells that you want to plot in a chart are not in a continuous range, you can select nonadjacent cells or ranges as long as the selection forms a rectangle. You can also hide the rows or columns that you don't want to plot in the chart.

step 3. On the Insert tab, in the Charts group, do one of the following:

a. Click the chart type, and then click a chart subtype that you want to use.

b. To see all available chart types, click a chart type, and then click All Chart Types

to display the Insert Chart dialog box, click the arrows to scroll through all available chart types and chart subtypes, and then click the ones that you want to use.