1 / 149

EXCEL 2007

EXCEL 2007. INTERMEDIATE. Nolan Tomboulian. Tomboulian@Yahoo.com. May 24, 2012. Intermediate Course Objectives.

oakes
Download Presentation

EXCEL 2007

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 2007 INTERMEDIATE Nolan Tomboulian Tomboulian@Yahoo.com May 24, 2012

  2. Intermediate Course Objectives • This class is for students who have some experience using and creating Excel workbooks, understand cell and worksheet formatting, worksheet navigation, basic mathematical operands (+, -, *, /, ^) , relative and absolute cells and Functions. • Quick Skill Review of: • Ribbon / Tabs / Groups / Commands / Quick Access / Customization • Workbook navigation and keyboard commands • Copy / Paste / Paste Special Options • AutoFill Options • Cell Formatting and selecting ranges • Formulas: Relative and Absolute cell reference • Conditional Formatting • Data Validation • Using DATE and TIME Functions • Logical functions (IF, AND, OR, NOT) • Lookup Tables and Functions • Data & Tables: Filtering / Sorting / and custom sort lists • Sub-Totals and groupings • Intermediate formulas and functions (Round, INT, MODE, MEDIAN, RANK) • Conditional Data Summaries (COUNTIF, SUMIF, AVERAGEIF, COUNTA) • Intermediate charts and graphics • Introduction and creation of Pivot Tables

  3. RULES INTRODUCTIONS LOGISTICS AGENDA FILE INFORMATION CONTACT INFORMATION Housekeeping

  4. Logistics • Parking • Bathrooms • Student Facilities • Smoking • Fire Alarm / Code Red • Lunch & Breaks

  5. This class will not be… Not too many people know that the word “LECTURE”is actually an acronym. It stands for Lengthy Endless Continuous Torture with Unending Repetition of Explanations. A LECTURE Class participation is welcomed and beneficial to you (and others)

  6. Code Of Conduct • Respect each other (Talking) • Food and Drink • Participate • Patience (with me and yourself) • Ask questions • Have FUN • Have your own projects or ideas? • Turn in the Evaluation Survey!

  7. About Me • New Mexico State – Business Systems • Wake Forest – MBA • Carteret Community College MIS Director and Instructor • First Union Training and Technology, Legacy reporting, System interfaces • Edge Institute Team Leader for China exchange / teaching programs.

  8. About You • Introductions • Your name • What department/area do you work in? • What is your job? (Give a short description) • What is your skill level with Excel? • What do you want to take away from this class?

  9. Class Flow • General Flow • Overview of Topic • Step-by-Step Exercises • Independent Practice • Questions • Collaboration

  10. Technical Issues • The Exercise Data Files could be: • 1) On your Desktop • 2) On the T: Drive • 3) On the CD • @FRC • _Excel_Workbooks • Most of the files should be in a READ ONLY mode to keep you from changing them. • Create a File Folder on the T: drive, Desktop or flash drive where you plan to save your work. • Do a SAVE AS to add files to YOUR File Folder. • The SAVE AS does not remove the Read Only status. • Press the Office Button and then Prepare – Mark as Final

  11. Nolan Tomboulian Tomboulian@yahoo.com 252-675-0176 END OF SECTION Housekeeping

  12. Where to Begin?

  13. Where to Start?

  14. The Ribbon does appear overwhelming! There are some options you may never use! There are many tutorials and resources available How do you eat an elephant?

  15. Introducing Excel • Microsoft Office Excel 2007 (or Excel) is a computer program used to enter, analyze, and present quantitativedata • Desktop Icon or • Start / All Programs / Microsoft Office • (Right Click and copy the program to the desktop for quick access) • A spreadsheet is usually a collection of text and numbers laid out in a rectangular grid. (Columns and Rows) • Often used for budgeting, inventory management, and decision making • Think of Excel as a big table or chart to fill in with data. • You can also insert other objects such as: pictures, word-art, smart-art, drawings/shapes and other OBJECTS (files).

  16. Basic Worksheet Review • Review of: • Ribbon / Tabs / Groups / Commands • Quick Access Toolbar / Customization • Workbook navigation • Selecting Cell Ranges • Keyboard commands (Alt or <F10>) • Copy / Paste / Paste Special Options • AutoFill Options • Cell Formatting Options • Formulas – Relative and Absolute

  17. Conditional Formatting Create special color coded and icon “views” of data based on various criteria and “Rules”

  18. Conditional Formatting • Why use conditional formatting? • Emphasize data • Call attention to errors • Easy data correlation • Can use built-in conditional formatting rules, or create your own • Can “Filter” data based on Cell Format color • Built in rules can usually be modified: • (ie: Top 10 can be any Number: Top 2, Lowest 3, Top 15%) • Can reference a cell, use a “Constant” value, or even a formula: $A$17 70 =$A5>$B5 .

  19. Conditional Formatting

  20. Conditional Formatting: Data Bars and Icons You can “hide” or “display” the Data Value with the graphic.. Be careful with the use of colors for color blindness and printing While there may be 3 to 5 Icons, you can define the rules Icon Sets Data Bars & Icons

  21. Custom Conditional Formats • You can have multiple conditions that apply to the same range of data. • Each time you create a custom conditional format, you are defining a conditional formatting Rule. • A Rulespecifies the type of condition (such as formatting cells greater than a specified value) and the type of formatting to apply when that condition occurs. • When you have multiple rules, you should test your conditions to make sure the logic is applied in the correct or desired sequence. • Be careful with colors, because some people don’t see them! It also takes more ink!

  22. Exercise • Highlight “Duplicate” records • Create/Edit Conditional Formatting Rules • Highlight an entire ROW of data based on a Formula

  23. Average Analysis using Conditional Formatting #1

  24. Using Conditional Formatting – Example This example is a more complex use of the Conditional Formatting Option to highlight an entire ROW of data based on a condition. The worksheet is: Student_Data . Highlight the Data Range (A2:E20) [Home] {Conditional Formatting} Manage Rules New Rule Use a Formula to determine which cells to format Enter the Formula: =$ColumnRow =value String data needs to be in “Quotes” Use conditions: =, >, <, >=, <=, <> Example: = $C2=“W” =$D2>25 (You need to use the ABSOLUTE Column Cell Reference Select the FORMAT for the Rule. Verify the APPLIES TO: range $A$2:$E$20 http://www.free-training-tutorial.com/animations/conditional-formatting-row.html

  25. Conditional Formatting Use the {Find and Select } option on the [HOME} tab to identify cells that may contain conditional formatting

  26. Nolan Tomboulian Tomboulian@yahoo.com 252-675-0176 END OF SECTION Conditional Formatting

  27. G. I. G. O. Data Validation Garbage In – Garbage Out Checking values at the time of Data Entry Or Apply validation rules before processing a workbook you have been given from someone else!

  28. When designing a workbook, a good design should consider who is entering the data and how important it is that the data being entered conforms to certain established rules. Data Validation What is the impact of invalid data? For Example: • SEX might be defined to only allow for “M” or “F” (it is case specific!) • RACE might be limited to “B”, “W”, “H”, “A”, “O” • Zip-Code might be defined to be a 5 digit number (US standard) • Department might be limited by a select LIST of values • (Accounting, HR, Production, Audit) • Numeric Data might be defined to only be “Whole Numbers” or to be within a certain range or be bounded by limits • State or Name may be limited to a certain number of characters • Check input for a valid date or date range value

  29. DATA VALIDATION Data Validation is used to “trap” errors at the time of Data Entry. The IF and other Logic Functions can also be used to find “errors” Select the Range of data for the Data Validation rule RIBBON [Data] tab Data Validation There are three tabs 1) Data Validation for new rules 2) Circle Errors to identify errors that were entered BEFORE the validation rule was applied 3) Clear Circles to remove the indication of data validation exceptions. Conditional Formatting could also be applied to provide a visual for incorrect data

  30. Data Validation • There are 3 tabs to a Data Validation Dialog: • {Settings} {Input Message} {Error Alert} 1) Allow a) What kind of data do you want to check? b) What do you want to do with “Blank” data? c) Do you want the data entry to be via the keyboard or from a “Selection Box” 2) Input Message I suggest leaving this blank since it will “repeat” for every cell. You could define two rules, 1 for the 1st cell with a PROMPT and then another rule for the additional cells. 3) Error Processing STOP Warning Informational

  31. Exercise Apply Data Validation Rules

  32. Nolan Tomboulian Tomboulian@yahoo.com 252-675-0176 END OF SECTION Data Validation G.I.G.O.

  33. IF AND OR NOT Using Logical Functions

  34. Objectives • Using IF to evaluate a single condition • Using the AND function for multiple conditions • Using the OR function for multiple conditions • Using the NOT function to take the inverse result of an AND or OR function • Nested IF Functions to be used to calculate 3 or more different outcomes • Consider using a Lookup Table if the number of conditions to be tested is large.

  35. Logical Functions (And / Or / Not) • These functions are not computational: • They return a “True” or “False” value based on the conditional test. • They are used to test for conditions to be used for further analysis. • When combined with other functions, they can be used to perform actions on data, based on returned value of the logical function. • They can be used in a Cell or nested within an =IF function.

  36. Working with the Logical =IF Function • The IF function is a logical function that returns a TRUE value if the logical condition is true and a FALSE value if the logical conditions are false. • The TRUE and FALSE values can be flags that can be used in additional formulas, actual values or operations, or even other formulas • IF Function syntax: • IF(logical_test, Do_if_true, [Do_if_false])

  37. IF Function String values must be in Quotes It IS NOT case sensitive.

  38. Game Bracket Copy A2:C4 and paste into A6 to create a 2nd bracket of Games =IF(B6>B8,A6,A8)

  39. Game Bracket – Round 2 =IF(B6>B8,A6,A8) Add values in D3 and D7 and create a formula in E5 to show the winner =IF(D3>D7,C3,C7) Can you copy the cells from A2:E8 into A10, and add the logic for ROUND 3? Now copy the cells for the 8 teams and add the logic for ROUND 4 You now have the template for a 16 team Regional Tournament

  40. Nested If What happens now if a TIE score is entered in B2 and B4? WHY? =IF(B2=B4,”TIE”,IF(B2>B4,A2,A4)) This example demonstrates a Nested IF. While in “real life” we assume there is only one “winner” and “Loser” what if we allowed for the possibility of a TIE. =IF(B2=B4,”TIE”,IF(B2>B4,A2,A4))

  41. Working with =AND Function • The AND function is a logical function that returns a TRUE value if allof the logical conditions are true and a FALSE value if any of the logical conditions are false. • Can test up to 255 logical conditions • =AND(cond1, Cond2, Cond3….)

  42. AND Function In E2: =AND(C2=“FT”,D2>=1) In F2: =IF(E2,B2*.03,0)

  43. Working with Logical Functions NESTED IF

  44. Working with Logical Functions • A nested IF function is when one IF function is placed inside another IF function to test for multiple outcomes • Can allow for three or more outcomes, instead of just two • =IF([Pay Grade]=1,2500,IF([Pay Grade]=2,5000, IF([Pay Grade]=3, 7500,"Invalid pay grade")))

  45. Working with Logical Functions=OR • The OR function is a logical function that returns • TRUEvalue if any of the logical conditions are true • FALSEvalue if all the logical conditions are false • =OR(logical_test_1, logical_test_2,…)

  46. Working with Logical Functions=NOT • Takes the Opposite of a returned condition • =NOT(logical_test)

  47. Working with Logical Functions If Years is Less than or equal to 1 or Salary > 100,000, there is no Bonus Otherwise, check the Pay Grade code to assign a constant value • =IF(OR([Years Service]<=1,[Annual Salary]>100000),0, IF([Pay Grade]=1,$T$1,IF([Pay Grade]=2,$T$2, IF([Pay Grade]=3,$T$3,"Invalid pay grade"))))

  48. Checking Formulas • Check your parentheses • All functions have an opening and closing parentheses • Correct number • Correct position • Excel uses color coding to help you keep track of items in a formula • Don’t use the “=“ inside of Nested functions.

  49. Example Electoral College Every four years, the United States has an election to pick the President. A system is used to assign “VOTES” based on the population of the State. And the number of representatives the state has. It is a “Winner Take All” – Whoever wins a majority of the votes in the state gets ALL the Electoral Votes This example uses an =IF function to assign the votes to a Republican 1=(RED) or Democrat 2=(BLUE) Maybe a Data Validation should be assigned to the input Cells for the Data Entry in the E and H columns There is also a condition in Column J to compare the Predicted Result with the Actual Result..

  50. Exercise – Payroll Record • Use the IF function to calculate Regular hours worked (<=40) • Use the IF statement to calculate if there are any Over-Time Hours • Use the If statement to calculate Overtime Rate If there was Overtime Hours.

More Related