Report

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 =SUM(A5:A8) Other Auto Sum options Average / Count / Min / Max =AVERAGE(A5:A8) =COUNT(A5:A8) =MIN(A5:A8) =MAX(A5:A8) * Cell Referencing * (Tutorial 3) Relative Absolute Mixed When copy; changes to relative position of cells A3 When copy; stays constant; always same cell $A$3 When copy; portion stays constant and portion changes relative $A3 or A$3 * Pivot Tables * (Tutorial 5) Create Pivot Tables Select fields to include Row Labels / Column Labels / Sum Values / Report Filter Format Pivot Table Styles / Layout Options Field Settings to format Sort Group Items Pivot Chart Sum Values Create Filter Change to other options…count, average, etc Report Filter – to filter Slicer – to filter Create Slicer Format Slicer Filter With Slicer 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 * Advanced IF Functions * IF(OR and IF(AND (Tutorial 7) Components IF(OR Function Nested IF/OR Function IF(AND Function Nested IF/AND Function Logical Decision (D) Value if True (T) Value if False (F) =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) * Advanced IF Functions * Nested IF (Tutorial 7) Components Nested IF Function Nested IF Function Logical Decision (D) Value if True (T) Value if False (F) One IF statement with an IF statement as either the Value if True or Value if False (CAN HAVE MORE THAN 2 IFs Nested) =IF(D1, T1, IF(D2, T2,F2)) =IF(G1=0,500,IF(G1=1,1000,0)) * Advanced Functions * VLOOKUP (Tutorial 7) Components Lookup Value (LV) Table Array (T) Column Index Number (#) Range Lookup (R) 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 * Advanced Functions * COUNTIF (Tutorial 7 & 9) Components Range (R) Criteria (C) COUNTIF =COUNTIF(R,C) =COUNTIF(F2:F101,B4) NOTES: RANGE- Data where we are looking for the matches to count CRITERIA – Cell that contains the information that match that you are looking for * Advanced Functions * SUMIF & AVERAGEIF (Tutorial 7 & 9) Components SUMIF AVERAGEIF NOTES: Range (R) Criteria (C) Sum Range (S) or Average Range (A) =SUMIF(R,C,S) =SUMIF(F2:F101,B4,G2:G101) =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 * Grouping Worksheets * (Tutorial 6) Click first worksheet, hold Group to Format or shift, Click last worksheet, release – says [Group] after Print file name Ungroup 3-D Reference formula Click on worksheet NOT in the group – no longer says [Group] after file name =SUM(Sheet1:Sheet3!B4) Note: (Sheet1:Sheet3 – Group of Worksheets) * Macros * (Tutorial 8) Data Validation Turn on Developer Tab on Rules Ribbon 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 * What-IF Analysis * (Tutorial 10) Solver Objective to what? Changing Cells Constraints 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)} * 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)} Excel Basics Fill a Series Using Step Value Using Trend Conditional Formatting Highlight Cells Duplicate Values Enter Review Comments Hyperlinks Working with Multiple Workbooks (Tutorial 6) Linking in formulas Switch Windows Manage Links View Multiple workbooks with Arrange All Workspace Templates Financial Functions (PMT) (Tutorial 3 & 9) Components Format RATE, NPER, PV, FV, Type =PMT(RATE,NPER,PV,FV,Type) RATE = interest rate per payment period ( annual rate / periods in a year) NOTES: 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 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 =IFERROR(equation,”error message”) 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