Excel tips for students, Study Guides, Projects, Research of Mathematics

Download Excel tips for students and more Study Guides, Projects, Research Mathematics in PDF only on Docsity!

Microsoft

Office Excel

2010 - Level 2

Reference Material

Please Do Not Copy

Microsoft

Office Excel

Level 2

Reference Material

Please Do Not Copy

M ICROSOFT

®

OFFICE EXCEL

®

L EVEL 2

L ESSON 1 - C ALCULATING D ATA WITH A DVANCED F ORMULAS

A. Apply Cell and Range Names.................................... 2

Range Names............................................... 2

B. Calculate Data Across Worksheets................................ 10

Three-Dimensional Cell References.............................. 10

C. Use Specialized Functions....................................... 13

Function Categories.......................................... 13

Function Syntax.............................................. 14

Function Entry Dialog Boxes.................................... 14

Improvements to Excel Functions............................... 15

D. Analyze Data with Logical and Lookup Functions.................... 20

Logical Functions............................................. 20

How the Lookup and Reference Functions Work................... 23

VLOOKUP and HLOOKUP Functions.............................. 23

L ESSON 2 - O RGANIZING W ORKSHEET AND T ABLE D ATA

A. Create and Modify Tables........................................ 32

Tables...................................................... 32

Table Components........................................... 33

The Create Table Dialog Box.................................... 34

Styles and Quick Style Sets..................................... 34

The Table Tools Design Contextual Tab........................... 35

Table Modification Options..................................... 35

CONTENTS

Contents iii

Reference Material

Please Do Not Copy

LESSON 3 - P RESENTING D ATA U SING C HARTS

LESSON 4 - A NALYZING DATA USING P IVOTT ABLES, S LICERS, AND

Reference Material

Reference Material

• B. Format Tables
• C. Sort or Filter Data
  • Sorting
  • Filters
  • The Advanced Filter Dialog Box
  • Filter Operators
• D. Use Functions to Calculate Data
  • Summary Functions in Tables
  • Database Functions
  • AND and OR Conditions
  • Subtotals
• A. Create a Chart
  • Charts
  • Types of Charts
  • Chart Insertion Methods
• B. Modify Charts
  • Chart Elements
  • Chart Tools Contextual Tabs
• C. Format Charts
  • Chart Styles and Formatting Options
  • Chart Layouts
• A. Create a PivotTable Report P IVOTC HARTS
  • PivotTable
  • The PivotTable Field List Pane
  • The Value Field Settings Dialog Box
  • PivotTable Functions
  • PowerPivot
• iv Microsoft ® Office Excel ® 2010 - Level CONTENTS
• B. Filter Data Using Slicers
  • Slicers
  • The Insert Slicers Dialog Box
• C. Analyze Data Using PivotCharts
  • PivotCharts
• A. Insert and Modify Pictures and ClipArt L ESSON 5 - I NSERTING G RAPHIC OBJECTS
  • Graphic Objects
  • Pictures and Clip Art
  • The Screen Capping Tool
  • Picture Formatting Options
  • The Artistic Effects Feature
• B. Draw and Modify Shapes
  • Shapes
  • Types of Shapes
• C. Illustrate Workflow Using SmartArt Graphics
  • SmartArt Graphics
  • The Choose a SmartArt Graphic Dialog Box
• D. Layer and Group Graphic Objects
  • Layering
  • Grouping
• A. Customize the Excel Environment L ESSON 6 - C USTOMIZING AND E NHANCING THE E XCEL E NVIRONMENT
  • General Options in Excel
  • Color Schemes
• B. Customize Workbooks
  • Comments
  • Hyperlinks
  • Watermarks in Excel
  • Background Pictures
  • General Workbook Customization Options
    • C. Manage Themes
      • Themes
      • Theme Modification Options
    • D. Create and Use Templates
      • Templates
      • Types of Templates
  • LESSON LABS
  • S OLUTIONS
  • I NDEX
• vi Microsoft ® Office Excel ® 2010 - Level CONTENTS

A BOUT THIS C OURSE

In the first course, Microsoft®^ Offıce Excel ®^ 2010 - Level 1, you learned the basic skills needed to create and format spreadsheets, perform simple calculations, modify spreadsheet ele- ments, and print workbooks. To increase your proficiency, you need to know how to perform advanced calculations, analyze and interpret data, and display spreadsheet data in a visually effective manner. In this course, you will use advanced formulas and tools to streamline and analyze spreadsheet data. You will also enhance the look and appeal of your workbooks by adding charts and other graphical objects.

Excel is a powerful data analysis application used for manipulating large amounts of numerical data. Knowing the advanced features of this software will aid you in processing complex data and enhancing spreadsheet reports and charts.

Course Description

Target Student

This course is meant for those desiring to gain advanced skill sets necessary for calculating data using functions and formulas, sorting and filtering data, using PivotTables and PivotCharts for analyzing data, and customizing workbooks.

Course Prerequisites

Before starting this course, students are recommended to take the following New Horizons course or have equivalent knowledge: Microsoft ®^ Offıce Excel ®^ 2010 - Level 1.

How to Use This Book

As a Learning Guide

This book is divided into lessons and topics, covering a subject or a set of related subjects. In most cases, lessons are arranged in order of increasing proficiency.

I NTRODUCTION

Introduction vii

Reference Material

Please Do Not Copy

Calculating Data with

Advanced Formulas

In this lesson, you will use advanced formulas.

You will:

• Apply cell and range names.
• Calculate data across worksheets.
• Use specialized functions.
• Analyze data with logical and lookup functions.

Lesson Time

L ESSON 1 1 hour(s), 30 minutes

LESSON 1

Lesson 1: Calculating Data with Advanced Formulas 1

Reference Material

Please Do Not Copy

Introduction

As you continue working with Microsoft®^ Office Excel ®^ , you have probably learned new skills and explored new functions, for example, to generate new data from an existing set of data using basic formulas in a worksheet. Performing sophisticated calculations in a worksheet or across multiple worksheets is the hallmark of a more advanced user of Excel. In this lesson, you will create advanced formulas in a workbook.

Ever tried figuring out how much you can afford to pay for a new house and what your loan payments will be, say, over a period of ten years? It can be a frustrating experience trying to determine each of your payments for the said period. Excel offers built-in functions for per- forming calculations in your workbook. All you need to do is input data and select a function to perform the calculation and display the result.

T OPIC A

Apply Cell and Range Names

In this lesson, you will calculate data using advanced formulas in your workbook. A straight- forward way of constructing complex formulas lies in using names instead of cell addresses to refer to cell ranges. In this topic, you will apply cell and range names.

The more complex a formula is, the harder it can be to determine exactly what data is used in which part of the formula. When you use a range name, you can easily determine which data range is referenced in the formula.

Range Names

A range name is a descriptive label assigned to one or more cells for referring to them in a formula. The cells may be located anywhere in a workbook; in case of multiple cell ranges, they may be adjacent or nonadjacent. When you select a range of cells that have been assigned a name, the range name is displayed in the Name box. A range name must start with a letter, cannot contain spaces, and can be up to 255 characters long. It is good practice to use range names that are short, easy to remember, and descriptive enough to allow you to identify their function.

Excel allows you to limit the scope of a range name to either a worksheet or workbook. If the scope is set for a worksheet, then the range name cannot be used to refer to more than one range in a worksheet. It can, however, be used on another worksheet. If the scope is set for a workbook, the range name cannot be reused anywhere on the workbook.

LESSON 1

2 Microsoft ®^ Office Excel ®^ 2010 - Level 2

Reference Material

Please Do Not Copy

• Select Workbook to declare the scope throughout the entire workbook.
• Or, select a worksheet name to declare the scope for a particular worksheet.

By declaring the scope for a specific worksheet, it is possible to use the same name multiple times in a workbook.

5. If desired, type comments to describe the range name.
6. In the^ Refers To^ text box, select what the range refers to.
   • A cell reference, that is, one cell or a group of cells.
   • A constant: (=[ number or text ]).
   • Or, a formula: (=[ formula ]).
7. Click OK.
8. If necessary, close the^ Name Manager^ dialog box.

Procedure Reference: Name Cell Ranges Using Worksheet Data To use worksheet data to create range names:

1. Select the range.
2. On the Formulas tab, in the Defined Names group, click Create from Selection.
3. In the Create Names from Selection dialog box, in the Create names from val- ues in the section, check the desired check boxes to use the contents of the top or bottom row or the right or left column as range names.
4. Click^ OK.

Procedure Reference: Edit a Named Range To edit a named range:

1. Select the named range to verify that it is the range whose name you want to change.
2. On the^ Formulas^ tab, in the^ Defined Names^ group, click^ Name Manager.
3. In the Name Manager dialog box, from the list of named ranges, select the range name that you want to change and click Edit.
4. In the Edit Name dialog box, type the new range name and add any new com- ments, if necessary.
5. To change the cell references for the range, type the new references in the^ Refers To text box, or select the text box and then select the new range on the worksheet and click OK.

For example, you might need to edit the cell references for the range if you have inserted a row or column that runs through the range.

6. Click Close to close the Name Manager dialog box.
7. Select the range name from the^ Name^ box drop-down list to verify the new name and cell range.

LESSON 1

4 Microsoft ®^ Office Excel ®^ 2010 - Level 2

Reference Material

Please Do Not Copy

Procedure Reference: Delete a Range Name

To delete a range name:

1. On the Formulas tab, in the Defined Names group, click Name Manager.
2. In the^ Name Manager^ dialog box, select the range name or names that you want to delete.

You can use Shift + Click or Ctrl + Click to select multiple names.

3. Click^ Delete^ or press^ Delete^ on the keyboard and click^ OK.
4. Close the Name Manager dialog box.

Procedure Reference: Include Range Names in Formulas

To include range names in formulas:

1. Select the cell where you want to enter the formula.
2. Start entering the formula in the formula bar or in the selected cell.
3. At the point in the formula where you need to insert a cell or range address, insert the range name.
   • Type the range name, or type multiple range names, separated by commas.
   • Or, in the Defined Names group, click Use In Formula and select the range name from the drop-down list.
4. Complete the entry of the formula.

Range names refer to absolute cell addresses. When formulas that include range names are cop- ied, they do not copy relative to their new location.

A CTIVITY 1-

Managing Range Names in Workbooks

Data Files:

• Last Fiscal Sales.xlsx

Scenario:

You are working in the sales department of Our Global Company (OGC) Bookstores. The company’s board has asked you to submit a report on the total and average sales in different regions in different quarters for the last fiscal year. You entered the data in an Excel sheet and now want to apply range names for cell ranges so that it is easier to refer to them in the for- mulas.

LESSON 1

Lesson 1: Calculating Data with Advanced Formulas 5

Reference Material

Please Do Not Copy

c. In the Create Names from Selection dia- log box, verify that Top row is checked by default and click OK to create the range name from the cell in the top row.

d. From the Name Box drop-down list, select Quarter2.

e. Observe that the range C5:C8 is selected. Select the range A5:E5.

f. On the Formulas tab, in the Defined Names group, click Create from Selec- tion.

g. In the Create Names from Selection dia- log box, verify that Left column is checked and click OK.

h. From the Name Box drop-down list, select Northeast and observe that the range B5:E5 is selected.

Students can name the ranges B6:E6, B7:E7, and B8:E8 by selecting the range A6:E8.

i. Repeat the steps from selecting the ranges through using the Create Names from Selection dialog box to name the ranges B6:E6 , B7:E7 , and B8:E8 as South- east, Northwest, and Southwest, respectively.

j. Select the range B5:E6.

k. To the left of the formula bar, click inside the Name Box.

l. In the Name Box , enter the name of the range as East

LESSON 1

Lesson 1: Calculating Data with Advanced Formulas 7

Reference Material

Please Do Not Copy

m. Similarly, name the range B7:E8 as West

4. Use Name Manager to rename the sales groups.

a. On the Formulas tab, in the Defined Names group, click Name Manager.

b. In the Name Manager dialog box, select Northeast and click Edit.

c. In the Edit Name dialog box, in the Name text box, type NE and click OK.

d. Similarly, rename Northwest, Southeast, and Southwest to NW, SE, and SW , respectively.

e. Close the Name Manager dialog box.

f. In the Name Box drop-down, observe the modified names.

5. Calculate the total and average sales for the East Coast and the West Coast.

a. Select cell B13.

LESSON 1

8 Microsoft ®^ Office Excel ®^ 2010 - Level 2

Reference Material

Please Do Not Copy

T OPIC B

Calculate Data Across Worksheets

In the previous topic, you made complex formulas simpler to read by using range names. Another way you can increase the complexity of your formulas is by using formulas that pull data from several worksheets. In this topic, you will calculate data across worksheets.

You may find that you have data located in several worksheets. You could cut and paste the data into a single worksheet for calculation purposes, but then you run the risk of original data changing. Instead, you can make your calculation span worksheets and not be restricted to one. This way your calculation will be accurate, regardless of where the data is found.

Three-Dimensional Cell References

Definition: A three-dimensional (3-D) cell reference is a cell reference located in the same place on multiple worksheets. It can be used only in a workbook with multiple worksheets. Each 3-D reference contains a start point (the first worksheet), an end point (the last worksheet), and a cell reference. 3-D references can be used in some functions and certain formulas.

Example:

Figure 1-2: Cells located on multiple worksheets can be referred using three- dimensional references.

How to Calculate Data Across Worksheets

Procedure Reference: Enter Cells and Ranges from Multiple Worksheets into a Formula To enter cells and ranges from multiple worksheets into a formula:

1. Select the cell where you want to enter the formula.

LESSON 1

10 Microsoft ®^ Office Excel ®^ 2010 - Level 2

Reference Material

Please Do Not Copy

2. Start entering the formula in the formula bar or directly in the selected cell.
3. At the point in the formula where you need to insert a cell or range address, insert the appropriate multiple-sheet reference.
   • To refer to cells from a different worksheet, select the worksheet and then select the cell or range, or type the cell or range reference including the worksheet prefix.
   • Or, to insert a three-dimensional cell reference, select the first tab, Shift-click the tab for the last worksheet, and then select the cell or range on the first worksheet.
4. Complete entering the formula.

A CTIVITY 1-

Creating Formulas Across Multiple Worksheets

Data Files:

• Department Wise Revenue.xlsx

Scenario:

Your manager has asked you to create a summary page for a workbook that contains quarterly sales results for the last fiscal for different departments. After inserting the summary as the first page, you are about to enter the formulas on the Summary sheet. The formulas will refer to data from multiple worksheets.

What You Do How You Do It

1. Calculate the Northeast sales team’s contribution to consulting for the entire fiscal.

a. From the C:\084577Data\Calculating Data with Advanced Formulas folder, open the Department Wise Revenue.xlsx file.

b. On the Summary worksheet, select cell B5.

c. Type =SUM(Qtr1!B5, Qtr2!B5, Qtr3!B5, Qtr4!B5) and press Enter.

LESSON 1

Lesson 1: Calculating Data with Advanced Formulas 11

Reference Material

Please Do Not Copy