200 likes | 207 Vues
3. CHAPTER. BASIC APPLICATION SOFTWARE. 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
E N D
3 CHAPTER BASIC APPLICATION SOFTWARE
Lecture Objectives • Formula Copying Details in Spreadsheets • FormattingTechniques in Spreadsheets • Available Number Formatting Schemes in Excel • Advanced Formatting Techniques • Auto Format • ConditionalFormat • Internet Features in Spreadsheets • Converting sheet to a Web Document Format • Getting sheet data from the web (Web Query)
Actual Formula Copied Formulas 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.
Absolute Position: C2 Copied Formulas ..Formula Copying • Requirements in Formula Copying 2. Cell addresses in the copied formula should be picked from an absolute position.
Relative Cell Name: B5 Absolute Cell Name: $C$2 Copied Formulas ..Formula Copying • Formula Requirement Implementation in Spreadsheet Programs Req#1: Use of Relative Cell Names (like B2, C2 etc.)– for changing cell locations. Req#2: Use of Absolute Cell Names (like $C$2 etc.)– for fixed cell locations.
Example: Relative Reference (C2) 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 letterchanges only (by column displacement).
..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 $C2 – 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
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.)
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.
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:
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?
Advanced Formatting Techniques in Excel • Auto Format • Allows the user to quickly format a spreadsheet table. • Every auto-format scheme includes a bunch of 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. Microsoft Excel, Formats| AutoFormat…
….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.
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.