1 / 19

Excel Review – Part 2 (Tutorials 6-10) Some information contained from Tutorials 1-5 also

Excel Review – Part 2 (Tutorials 6-10) Some information contained from Tutorials 1-5 also. What have you learned? * On Case Exam study guide! *. *Simple Formulas / Functions * (Tutorials 2 & 3). Use Cell References NOT numbers if possible!. Simple Formula. =C2*C3 =C2/5. Auto Sum.

lacey
Download Presentation

Excel Review – Part 2 (Tutorials 6-10) Some information contained from Tutorials 1-5 also

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 Review – Part 2 (Tutorials 6-10) Some information contained from Tutorials 1-5 also What have you learned? * On Case Exam study guide! *

  2. *Simple Formulas / Functions * (Tutorials 2 & 3) Use Cell References NOT numbersif possible! Simple Formula =C2*C3 =C2/5 Auto Sum =SUM(A5:A8) =AVERAGE(A5:A8) =COUNT(A5:A8) =MIN(A5:A8) =MAX(A5:A8) Other Auto Sum options Average / Count / Min / Max

  3. * Cell Referencing * (Tutorial 3) Relative When copy; changes to relative position of cells A3 Absolute When copy; stays constant; always same cell $A$3 Mixed When copy; portion stays constant and portion changes relative $A3 or A$3

  4. * Pivot Tables * (Tutorial 5) Row Labels / Column Labels / Sum Values / Report Filter Create Pivot Tables Select fields to include Sum Values Change to other options…count, average, etc Format Pivot Table Styles / Layout Options Field Settings to format Sort Group Items Report Filter – to filter Slicer – to filter Create Slicer Format Slicer Filter With Slicer Pivot Chart Create Filter

  5. IF statements (Basic) (Tutorial 3) Components Logical Decision (D), Value if True(T), Value if False (F) Format =IF(D,T,F) =IF(A5>=1000,A5,0) NOTES: D must be an equation; T & F maybe a value, function, or equation

  6. * Advanced IF Functions * IF(OR and IF(AND (Tutorial 7) Components Logical Decision (D) Value if True (T) Value if False (F) IF(OR Function Nested IF/OR Function =IF(OR(D1,D2,…),T,F) =IF(OR(G1=0,F1=0),H1,0) =IF(AND(D1, D2,…),T,F) =IF(AND(G1=0,F1=0),H1,0) IF(AND Function Nested IF/AND Function

  7. * Advanced IF Functions * Nested IF (Tutorial 7) Components Logical Decision (D) Value if True (T) Value if False (F) Nested IF Function One IF statement with an IF statement as either the Value if True or Value if False (CAN HAVE MORE THAN 2 IFs Nested) Nested IF Function =IF(D1, T1, IF(D2, T2,F2)) =IF(G1=0,500,IF(G1=1,1000,0))

  8. * Advanced Functions * VLOOKUP (Tutorial 7) Lookup Value (LV) Table Array (T) Column Index Number (#) Range Lookup (R) Components VLOOKUP =VLOOKUP(LV,T,#,R) NOTES: LV – Where is the value you are looking up (NOT in the table that is being used to lookup the information!) T – The entire table that you are looking up the information in. # - What column number contains your answer (1,2,…) R – FALSE = Exact Match; TRUE = Approximate Match

  9. * Advanced Functions * COUNTIF (Tutorial 7 & 9) Components Range (R) Criteria (C) COUNTIF =COUNTIF(R,C) =COUNTIF(F2:F101,B4) RANGE- Data where we are looking for the matches to count CRITERIA – Cell that contains the information that match that you are looking for NOTES:

  10. * Advanced Functions * SUMIF & AVERAGEIF (Tutorial 7 & 9) Components Range (R) Criteria (C) Sum Range (S) or Average Range (A) SUMIF =SUMIF(R,C,S) =SUMIF(F2:F101,B4,G2:G101) AVERAGEIF =AVERAGEIF(R,C,A) =AVERAGEIF(F2:F101,B4,G2:G101) RANGE- Data where we are looking for the matches to count (Same as COUNTIF) CRITERIA – Cell that contains the information that match that you are looking for (Same as COUNTIF) SUM RANGE or AVERAGE RANGE – Data you want to add or average NOTES:

  11. * Grouping Worksheets * (Tutorial 6) Click first worksheet, hold shift, Click last worksheet, release – says [Group] after file name Group to Format or Print Ungroup Click on worksheet NOT in the group – no longer says [Group] after file name 3-D Reference formula =SUM(Sheet1:Sheet3!B4) Note: (Sheet1:Sheet3 – Group of Worksheets)

  12. * Macros * (Tutorial 8) Turn on Developer Tab on Ribbon Data Validation Rules Lists Input & Error Messages Create Macro by recording Run Macro using Ribbon Run Macro using combination key (Ctrl) Save As Macro Enabled (.xlsm) Create / Name / Use Macro Button

  13. * What-IF Analysis * (Tutorial 10) Objective to what? Changing Cells Constraints Solver Solve – Keep Solution – Answer Report Load / Save Solver Setup Turn on Solver on Data tab of Ribbon (Add-in) Data Table (1 Variable) Setup: Solution cells across top row; Values to substitute in down left column Data Table Dialog Box: Column input cell: cell you are substituting values for {=TABLE(,B4)}

  14. * What-IF Analysis * (Tutorial 10) Data Table (2 Variable) Setup: Solution cell in top-left corner (outside all changing values); One set of values to substitute in down left column; A second set of values to substitute in across the top row Data Table Dialog Box: Row input cell: cell you are substituting values for in the top row Column input cell: cell you are substituting values for in the left column {=TABLE(B3,B4)}

  15. Excel Basics Enter Review Comments Fill a Series Using Step Value Using Trend Conditional Formatting Highlight Cells - Duplicate Values Hyperlinks

  16. Working with Multiple Workbooks (Tutorial 6) Switch Windows Linking in formulas Manage Links View Multiple workbooks with Arrange All Workspace Templates

  17. Financial Functions (PMT) (Tutorial 3 & 9) Components RATE, NPER, PV, FV, Type Format =PMT(RATE,NPER,PV,FV,Type) NOTES: RATE = interest rate per payment period ( annual rate / periods in a year) NPER = number of payments (number of years * periods in a year) PV: Loan is amount borrowed; Investment is Initial Investment FV: Loan is zero; Investment is goal TYPE = 0 at end of month; 1 beginning of month

  18. Other Financial Functions & Other Advanced Functions (Tutorial 7 & 9) Working with Investments or Loans PMT, RATE, NPER, PV, FV, PPMT, IPMT Depreciation SLN, DB Investment Analysis NPV, IRR =IFERROR(equation,”error message”) IFERROR

  19. What-IF Analysis (Tutorial 10) Scenario Manager Scenario Name Changing Cells – Set once and do not change for all scenarios Second screen enter values for changing cells Scenario Summary Report Scenario Pivot Table Report Goal Seek Set cell…To Value…By Changing Cell

More Related