1 / 18

Excel to the Max

Excel to the Max. Gini Courter Annette Marquis TRIAD Consulting. Today’s Session. Overview (brief) Understanding conditions Using conditional functions Conditionally formatting data Using names Working with validation Creating templates Recording and editing macros

nida
Download Presentation

Excel to the Max

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. Excel to the Max Gini Courter Annette Marquis TRIAD Consulting

  2. Today’s Session • Overview (brief) • Understanding conditions • Using conditional functions • Conditionally formatting data • Using names • Working with validation • Creating templates • Recording and editing macros • Creating the user interface • What’s New in Excel 2003?

  3. Overview Goals: • to demonstrate Excel features and discuss reasons you might use them • to answer your questions • to learn how you use Excel • to give you new skills and materials to share your skills with colleagues • to have a nice morning together

  4. Structure • Questions • Content and clarifying questions as they occur • Other questions at two points: before first break and before end of session • Use microphone so others can hear • Logistics • Breaks, etc. • Cell phones and pagers

  5. Condition • a fork in the road • a rule • a decision point • the test used to determine which of 2 possible actions should be taken I Just Stopped In…To See What Condition My Condition Was In

  6. Examples of Decisions • Pay time-and-a-half for hours over 40 • Variable or product line-based commissions • List of employees who haven’t turned in X • Every third row in a list

  7. Conditions – A Core Technology • IF function • SUMIF, COUNTIF functions • Conditional formatting • Any decision structure in VBA macros

  8. Using IF • Syntax: =IF(test,if_true,if_false) • If no action for if_false, FALSE will appear in cell • Use “” or 0 to suppress FALSE

  9. Using SUMIF, COUNTIF • SUMIF =SUMIF(range, criteria, sum_range) • COUNTIF =COUNTIF(range, criteria) • To average, use SUMIF/COUNTIF

  10. Conditional Formatting • Based on: • Cell contents – formats one cell • Formula – format a cell or range based on the value of • Another cell • A calculation • Formulas must refer to a cell or range in the current workbook • Named ranges are the exception

  11. Other Cool Functions • ISBLANK • ISNA • ISERROR

  12. Ask For It By Name • Names can refer to: • A cell • A range of cells • A range of cells that span sheets in a book • A formula • A constant • Name creation methods • Enter in name box • Insert > Name > Define • Insert > Names > Create

  13. Quick and Dirty Drop Downs • Pick from List • Validation • Data > Validation • Must refer to a range on this sheet • Named ranges in current workbook are an exception: =name

  14. Creating Templates • Template issues • Protection • Ease of use • Validation and feedback

  15. Creating Macros • Methods: • Record using the Macro Recorder • Edit or enter using the Visual Basic IDE • Types: • Global – in personal macro workbook • Local – in workbook where macro is used • “Remote” – in another workbook, called from VB code

  16. User Interface for Macros • Global • Menu • Standard or Formatting toolbar • Local • Command buttons in worksheet • Toolbar displayed with VB code

  17. What’s New in Excel 2003 • List functionality • XML support • Smart documents • Document workspaces (SharePoint) • Compare workbooks • IRM (information rights management)

  18. Optional Topics • Pivot tables • Camera

More Related