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)

Relative Absolute Mixed

* Cell Referencing *

(Tutorial 3) 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 Create Filter Sum Values 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 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 Function Nested IF/AND Function =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 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) Nested IF Function =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 NOTES: =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

* 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(R,C,A) AVERAGEIF =AVERAGEIF(F2:F101,B4,G2:G101) RANGE- Data where we are looking for the matches to count NOTES: (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) Group to Format or Print Click first worksheet, hold shift, Click last worksheet, release – says [Group] after file name 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)

Data Validation

* Macros *

(Tutorial 8) Rules Lists Turn on Developer Tab on Ribbon 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

Solver Data Table (1 Variable)

* What-IF Analysis *

(Tutorial 10) 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) 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)}

Data Table (2 Variable)

* What-IF Analysis *

(Tutorial 10) 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 RATE, NPER, PV, FV, Type Format NOTES: =PMT(RATE,NPER,PV,FV,Type) 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

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

Scenario Manager

What-IF Analysis

(Tutorial 10) 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