1 / 20

Advanced Formatting Techniques in Spreadsheets

Learn how to use advanced formatting techniques in spreadsheets, including formula copying, number formatting, date/time arithmetic, and internet features. Improve the appearance of your sheets and make data more readable.

maryamj
Download Presentation

Advanced Formatting Techniques in Spreadsheets

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 3 CHAPTER BASIC APPLICATION SOFTWARE

  2. 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)

  3. Formula Copying in Spreadsheets

  4. 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.

  5. 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.

  6. 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.

  7. 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).

  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 $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

  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.)

  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.

  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:

  12. …Details of Format types in Excel

  13. …Details of Format types in Excel

  14. …Details of Format types in Excel

  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?

  16. …Details of Format types in Excel

  17. …Details of Format types in Excel

  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 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…

  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.

  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.

More Related