Advanced Excel Part 2 - Core-CT

1 1 | P a g e Advanced Excel part 2 Instructor Notes September 16, 2016 1) INDEX and MATCH .. 1 2) Dynamic Tables - Introduction .. 3 3) SUMIFS using a Table .. 5 4) Running Totals (Run_Tot) .. 6 5) Dashboard / 7 6) INDEX/MATCH: Dynamic Table .. 10 7) SUMIF .. 11 8) Array Formula .. 12 9) Using INDEX/MATCH as an Array .. 13 10) SUMPRODUCT .. 14 11) HLOOKUP .. 15 12) Miscellaneous .. 16 13) FRP312 Formula .. 16 1) INDEX and MATCH a. INDEX takes a defined block of data (usually a table) and returns a value to a cell based on coordinates 1. Syntax: =INDEX(Range,RowNumber,[ColumnNumber]) (1) [ ] indicates optional. 2. For example, if you have a 10 x 10 range and the function was =INDEX(range,1,1) then INDEX would return the top left cell contents. =INDEX(range,10,10) would return the bottom right cell contents. 3. When setting up a range, do not include row or column headers.

2 A range is just the data. (1) A range can be a Named Range or a cell reference (ex. A2:F11) 4. INDEX is used most often in conjunction with MATCH b. MATCH looks for a criteria in a list and returns a number indicating the location in the list. 1. Syntax: MATCH(LookUp,Range,[-1,0,1]) (1) Where 0 = Exact Match (the same as FALSE in VLOOKUP) 2 | P a g e (2) Where 1 = Exact or next lowest value in the list (the same as TRUE in VLOOKUP). The list is in descending order. (3) Where -1 = Exact or next highest value in a list (where the list is in ascending (reverse) order ~ the opposite use of TRUE) (4) If the third argument is omitted, it will default to 1. 2. For example, you have a list (in order) of names {Tom, Dick, Harry}. The MATCH function is =MATCH( Dick ,list,0) and will return the number 2 indicating the position in the list of Dick.

3 3. The lookup can be hard coded into the function (ex., Dick ~ must be in quotes) or it can be a reference to another cell (ex., A1). If the lookup is a number, it does not need to be in quotes. 4. Because MATCH returns a number, We can combine MATCH with INDEX c. Syntax 1. =INDEX(Range,MATCH(Lookup,Range,0),MATCH (Lookup2,Range2,0)) Row Number Column Number 2. INDEX/MATCH can substitute for VLOOKUP (1) INDEX/MATCH provides the ability to have the lookup column anywhere in the data. You are not tied to a left-most column scenario as you are with VLOOKUP. (2) For example, you have an employee table where the employee number is column 1 and the employee LastName,FirstName is column 2. You want to find the employee number from the name. Your function would be: =INDEX(Column1:Column2,MATCH(LastName,Fi rstName,Column2,0),1).

4 This function reads: Index the two columns and find the name in the second column and return the related value in the first column. d. Example from Class 1. The first example [=INDEX(Range,1,1)] shows how INDEX works in its most basic form 2. The second example [=INDEX(Range,B25,D25)] places the criteria outside of the formula. Having your criteria outside of the formula is considered a best practice. 3. The third example introduces the MATCH function, and is meant to show that MATCH returns the position number of the list being reviewed. When you change the contents of cells B26 and D26, the INDEX formula in C30 becomes #VALUE!. This is normal. Rewriting the INDEX/MATCH formula will resolve the error. (1) In cell C31: =MATCH(B26,DeptID,0) In cell C32: =MATCH(D26,Month,0) (2) And finally, we combine INDEX and MATCH to use one formula: In cell C30: =INDEX(Range,MATCH(B26,DeptID,0),MATCH(D 26,Month,0)) 4.

5 See the Miscellaneous section for instructions on (1) Creating worksheet specific named ranges 3 | P a g e (2) Creating drop down menus 2) Dynamic Tables - Introduction a. Dynamic tables are a collection of rows and columns that the user wants to manage independently from other data. You can have multiple tables on the same worksheet. b. There are three ways to create a dynamic table. Highlight the data and headers: 1. Ctl-T 2. Ctl-L 3. Navigation: Insert (Ribbon) > Table > Create Table (dialog box) > OK 4. The default style that is created includes a blue header row and alternating light blue and white stripes for the data. This style can be changed. c. Tables, by default, are named Table1, Table2 etc. This can be changed by navigating to the Design ribbon, highlighting the table name and renaming. Press Enter to complete the name change.

6 1. The naming rules are the same as for named ranges (1) No spaces (2) Letters, numbers, underscores, dots (3) Upper and lower case allowed but not necessary 2. Dynamic tables are listed in the Name Box drop down 3. Dynamic tables are included in workbook s function library and are distinguished from other functions by the use of the symbol. 4. Dynamic tables are listed in the Name Manager: Formulas (ribbon) > Name Manager 5. Dynamic tables are not named ranges and cannot be found by pressing F3 d. The header row is always visible. As you scroll down, the header labels replace the familiar column names (A,B,etc) 1. The active cell must be inside the table for this to happen. If the active cell is outside of the table then the column labels will remain as normal. e. Adding rows or columns automatically expand the table. f. Columns can be selected by mousing over the header until you see a downward pointing arrow and left clicking 1.

7 The header row can be selected by mousing over the left-most cell of the table until you see a right-pointing arrow and left clicking. g. From the Design tab you can create a pivot table from the data 1. Navigation: Design (ribbon) > Summarize with PivotTable 2. You can also create a pivot table from: Insert (ribbon) > PivotTable 3. When you add a column or row the table automatically expands. This means that the pivot table is not restricted to inserting columns or rows inside the table. You can add them to the ends and the pivot table will recognize them h. Calculations within a table use table nomenclature rather than cell nomenclature 4 | P a g e 1. They are called Calculated Columns 2. Cell nomenclature: =J9-K9 Calculated Column nomenclature: =[@[Gross This is called a structured reference (1) The @ symbol indicates this row (2) The other references refer to the column header name.]]

8 (3) The formula will be the same for every row 3. As soon as a formula is entered it is copied to the other cells in the column (1) If the column has the wrong format then the column must be highlighted before applying the format (formatting one cell will not copy to all of the other cells) 4. If you are referencing a constant outside of the table you must either: (1) Lock the cell reference (F4) (2) Use a named cell (automatically locked reference) (3) A non-locked cell reference is a relative reference and when the calculated column copies down, it will copy the relative reference appropriately. 5. As rows are added to the table the calculated column will automatically include the new information. i. When a table is created it automatically includes an auto-filter. This auto-filter works in exactly the same as the standard Excel auto-filter.

9 1. The auto-filter can be turned off: Data (ribbon) > Filter j. From the Design tab you can insert a Total Row (checkbox) 1. The default value is Sum 2. Each cell in the Total Row has a drop down menu associated with it and the choices presented are the same as for =SUBTOTAL (1) Average (2) Count (3) Count Numbers (4) Max (5) Min (6) Sum (7) StdDev (Standard Deviation) (8) Var (Varience) (9) More Functions (This allows the user to create a custom formula) k. The first and last column can be formatted differently from the other table columns. l. If you no longer want a dynamic table, you can convert it back to just a data range 1. Navigation: Design (ribbon) > Convert to Range >Convert to Normal Range (dialog Yes/No) 2. A table converted to a range retains the formatting it had when it was a table. 5 | P a g e (1) Use Design > Table Styles > None to convert the format to a range style before you convert the table to a range.

10 M. Drawbacks 1. No absolute references in structured formulas (this can be huge) 2. No formulas in headers. When converting a range to a table, these are renamed. 3. @ThisRow, but no #PreviousRow 4. Only one formula per column 5. Formulas can be difficult to read (Microsoft claims structured formulas are easier to read). 3) SUMIFS using a Table a. The objective of this lesson is to show how a range can be locked in a dynamic table environment. The lesson asks you to find a number of totals based on values in four different columns. Usually SUMIFS has a single column as a RangeToSum. To fill in the matrix as shown without using a dynamic table would require four separate SUMIF formulas. With dynamic tables you can write one formula and copy it across and down. b. Background 1. The syntax for SUMIFS is =SUMIFS(RangeToSum,Rng1,Crit1,Rng2,Crit2 ) (1) There are a minimum of 5 required arguments 2.