CHAPTER 3 BASIC APPLICATION SOFTWARE 2 Lecture Objectives

CHAPTER 3 BASIC APPLICATION SOFTWARE

2 Lecture Objectives Formula Copying Details in Spreadsheets Formatting Techniques in Spreadsheets Available Number Formatting Schemes in Excel Advanced Formatting Techniques Auto Format Conditional Format Internet Features in Spreadsheets Converting sheet to a Web Document Format Getting sheet data from the web (Web Query) © 2005 The Mc. Graw-Hill Companies, Inc. All Rights Reserved.

3 Formula Copying in Spreadsheets © 2005 The Mc. Graw-Hill Companies, Inc. All Rights

4 Formula Copying Formula copying results in formula reuse without retyping. Requirements in Formula Copying 1. Cell addresses in the copied formula should be picked from the same relative positions as they were in the actual formula. Actual Formula © 2005 Copied The Mc. Graw-Hill Companies, Inc. All Rights Reserved. Formulas

5 . . Formula Copying Requirements in Formula Copying 2. Cell addresses in the copied formula should be picked from an absolute position. Absolute Position: C 2 Copied Formulas © 2005 The Mc. Graw-Hill Companies, Inc. All Rights Reserved.

6 . . Formula Copying Formula Requirement Implementation in Spreadsheet Programs Req#1: Use of Relative Cell Names (like B 2, C 2 etc. )– for changing cell locations. Req#2: Use of Absolute Cell Names (like $C$2 etc. )– for fixed cell locations. Relative Cell Name: B 5 Absolute Cell Name: $C$2 Copied Formulas © 2005 The Mc. Graw-Hill Companies, Inc. All Rights Reserved.

7 Formula Copying in Microsoft Excel For formula having relative cell names : 1. If copy is made downward (in the same column), Cell’s Row# changes only (by row displacement). 2. If copy is made rightward (in the same row), the Cell’s column letter changes only (by column displacement). Example: Relative Reference (C 2) © 2005 The Mc. Graw-Hill Companies, Inc. All Rights Reserved.

8 . . Formula Copying in Microsoft Excel For formula having cell names with $ symbols The cell dimension (column or row) having a $ symbol will not be affected by copying process. Examples $C$2 - Use for an Absolute Cell Reference Downward Copying: No change Rightward Copying: No change $C 2 – Use for a Mixed Cell Reference Downward Copying: Row# changes Rightward Copying: No change C$2 – Use for a Mixed Cell Reference Downward Copying: No change Rightward Copying: Column letter changes © 2005 The Mc. Graw-Hill Companies, Inc. All Rights Reserved.

9 Formatting Spreadsheets Formatting a sheet results in Improved sheet outlooks. More understandable sheets. Categorized into two classes. Formatting a sheet look/ appearance Row height / Column widths Foreground/ background colors (for emphasis) Selecting Fonts Borders Text Control in Cells (Wrap text, angular text label etc. ) © 2005 The Mc. Graw-Hill Companies, Inc. All Rights Reserved.

10 Number Formatting in Spreadsheets …. Two Formatting Classes Formatting sheet data values. Currency values with currency symbols/ separator etc. Desired number of decimals/ negative number representations. Display decimal values as Fractions. Display decimal values as percentages. Important: Changing the format, doesn’t change the actual value of a cell, but only the displayed value. © 2005 The Mc. Graw-Hill Companies, Inc. All Rights Reserved.

11 Number Formatting in Spreadsheets To make a worksheet data (values/ formula results) more readable. Add useful symbols and/ or separators in the display of a number. Available Number Format Options in Microsoft Excel are as follows: General Number Currency Accounting Scientific Date Time Percentage Fraction Custom © 2005 The Mc. Graw-Hill Companies, Inc. All Rights Reserved.

12 …Details of Format types in Excel © 2005 The Mc. Graw-Hill Companies, Inc.

13 …Details of Format types in Excel © 2005 The Mc. Graw-Hill Companies, Inc.

14 …Details of Format types in Excel © 2005 The Mc. Graw-Hill Companies, Inc.

15 Date/Time Arithmetic in Spreadsheets Date Arithmetic allows the user to do date/ time calculations Spreadsheet software treats date/time as numeric values (behind the scenes) Examples: Number of days between two date values What will be the date after 20 days from now? © 2005 The Mc. Graw-Hill Companies, Inc. All Rights Reserved.

16 …Details of Format types in Excel © 2005 The Mc. Graw-Hill Companies, Inc.

17 …Details of Format types in Excel © 2005 The Mc. Graw-Hill Companies, Inc.

18 Advanced Formatting Techniques in Excel Auto Format Allows the user to quickly format a spreadsheet table. Every auto-format scheme includes a bunch of Microsoft Excel, Formats| Auto. Format… format techniques. Users can collectively apply a selection of these formats by Selecting a table area on a worksheet. Apply an auto format scheme from the given list. Select the formats to be included from that scheme. © 2005 The Mc. Graw-Hill Companies, Inc. All Rights Reserved.

19 …. Advanced Formatting Techniques in Excel Conditional Formatting Content based formatting on spreadsheet cells. Allows to monitor large data tables efficiently. Needs user to enter criteria expressions for cell formattings. Spreadsheet program applies the criteria on each cell dynamically and does the formatting accordingly. © 2005 The Mc. Graw-Hill Companies, Inc. All Rights Reserved.

20 Using Internet Features Spreadsheet software allows user to Create documents to be placed on Internet (HTML version) Communicate with automated sites to receive relevant data (Use of Web Query in Excel) Example: – In a spreadsheet using stock price details, the current data can be retrieved using a Web Query. © 2005 The Mc. Graw-Hill Companies, Inc. All Rights Reserved.