Office Excel 2010 Lab 3 Managing and Analyzing

Office Excel 2010 Lab 3 Managing and Analyzing a Workbook Mc. Graw-Hill Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

3 -2 Objectives 1. 2. 3. 4. 5. 6. Mc. Graw-Hill Use absolute references Copy, move, name, and delete sheets Use Auto. Fill Reference multiple sheets Use Find and Replace Zoom the worksheet Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

3 -3 Objectives 7. 8. 9. 10. 11. 12. Mc. Graw-Hill Split windows and freeze panes Use what-if analysis and Goal Seek Create Sparklines Control page breaks Add custom headers and footers Print selected sheets and areas Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

3 -4 Concept Preview 1. 2. 3. 4. 5. Mc. Graw-Hill Absolute Reference Sheet Name Auto. Fill Sheet and 3 -D References Find and Replace Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

Concept Preview 6. 7. 8. 9. 10. Mc. Graw-Hill Split Window Freeze Panes What-If Analysis Goal Seek Conditional Formatting Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

Concept 1 : Absolute Reference 3 -6 Dollar sign symbols change references to absolute Mc. Graw-Hill Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

3 -7 Concept 2: Sheet Name Each sheet in a workbook can be assigned a descriptive sheet name Mc. Graw-Hill Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

3 -8 Concept 3: Auto. Fill handle Examples of how Auto. Fill extends a series Mc. Graw-Hill Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

Concept 4 : Sheet and 3 -D References 3 -9 • Formulas can reference cells in other sheets of a workbook – Sheet Reference – 3 -D References Mc. Graw-Hill Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

Concept 5 : Find and Replace Mc. Graw-Hill 3 -10 Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

3 -11 Concept 6: Split Windows • Allows you to divide a worksheet into sections, called panes • Each pane can be scrolled independently Mc. Graw-Hill Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

3 -12 Concept Mc. Graw-Hill 7: Freeze Panes Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

3 -13 Concept 8: What-If-Analysis Values in linked cells of Year sheet updated Reducing Payroll expenses increases Profit Margin Mc. Graw-Hill Chart shows change in profit margin Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

3 -14 Concept 9: Goal Seek Enter location of cell containing formula to be solved Enter desired result Specify cell that can be adjusted Mc. Graw-Hill Goal Seek found solution to achieve 15% profit margin Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.

Concept : Conditional Formatting 3 -15 10 Sets or clears conditional formatting rules Mc. Graw-Hill Copyright © 2011 The Mc. Graw-Hill Companies, Inc. All rights reserved.