Excel Advanced
Agenda
1. Introduction
2. Advanced Formulas and Formatting a. Array Formulas
b. Conditional Formatting 3. Lookup Formulas
a. LOOKUP and VLOOKUP
b. Index-Match and Offset-Match 4. PivotTables
Mid York Library System’s BTOP program is grant-funded. In order to keep this program running in the future, we must demonstrate its positive impact on our community. We would be extremely grateful if you would share with us the experiences you have had attending our training sessions and how our program has impacted your life. Please send your responses via e-mail or regular mail. Responses may be used to promote Mid York Library System and the BTOP program, and as part of grant reporting.
E-mail: [email protected]
Advanced Formulas and Formatting
Review
What is a formula?
Tip! A formula is a mathematical expression that “operates,” or performs a calculation, on the contents of a cell. Formulas can contain numbers, cell references, operators, and functions. The result of a formula is displayed in the cell into which it is entered.
Formula Operators
Operator What does it do?
+ Addition - Subtraction * Multiplication / Division ^ Exponentiation & Concatenation
= Equal to (Logical test)
> Greater than (Logical test)
< Less than(Logical test)
>= Greater than or equal to (Logical test)
>= Less than or equal to (Logical test)
<> Not equal to (Logical test) What is a function?
Tip! A function is a sometimes complex calculation that is pre-programmed into Excel so that you don’t have to create it on your own.
Cell References
Type of Reference What does it look like? What does it do?
Relative A2 Row and column references can change when you copy formula to another cell. This is the default for Excel formulas
Absolute $A$2 Row and column references do not change when you copy the formula.
Mixed $A2 or A$2 Either the row or column reference is relative, and the other is absolute.
Another worksheet Sheet1!A2 References a cell in a different worksheet
Array Formulas
What is an array?An array is simply a collection of items. In Excel, it can be one dimensional (a single row or column) or two dimensional (rectangular range of cells).
Why use arrays and array formulas?
o To ensure that all formulas in a range are identical.
o To avoid tampering or accidental overwriting – you can’t edit one cell in an array formula. o Eliminate unnecessary calculations, eliminate the need to autofill
o Filter out error values in calculations.
Tip! When entering an array formula always hit Ctrl + Shift + Enter! Practice
Task 1: Solve for total revenue by multiplying Quantity*Price, then find the sum. Next, use an array formula to eliminate a step.
o Hint! You can multiply and sum in a single formula! o Try: {=SUM(A4:A8*B4:B8)}
o Explanation: when multiplying two arrays, Excel creates a new array in its memory of the products. The
sum function then adds up these values.
Task 2: Find the average using the AutoSum function. Then, find the average using an array formula to exclude zeros.
o Hint! Remember the IF function: IF(logical test, value_if_true, value_if_false) o Try: {=AVERAGE(IF(F4:F8<>0, F4:F8))}
o Explanation: Like the example above, we are creating a new array in Excel’s memory. This array includes
only the non-zero values from which the average is found.
Task 3: Try to find the sum of a range that includes errors. Then, create an array formula to do the same.
o Hint! New formula: IFERROR(value, value_if_error) o Try: {=SUM(IFERROR(K4:K9, “ “))}
Conditional Formatting
Automatically apply formatting to your spreadsheet, based on cell contents. Use conditional formatting to quickly identify errors, values, or particular cell types.
Like all formatting options, you must first select (click and drag) the cells that you would like to format differently.
Examples:
1. Mark cells that are above or below average 2. Highlight alternate rows
3. Highlight duplicate values 4. Add data bars or icons
Task 1: Using the built-in conditional formatting rules, highlight grades that are above average.
o Hint! Under the conditional formatting options, try Top/Bottom Rules.
Task 2 & 3: Use conditional formatting formulas to highlight entries that appear in both List #1 and List #2, and unique entries in Lists #3 and #4
o Hint! COUNTIF(Absolute Range, Relative Criteria)
o The “Absolute Range” when formatting list #1 is our second list.
o The “Relative Criteria” is the first item in list #1 – relative because then it will check item 2, item 3… o Hint! Check the relative/absolute references chart.
o Hint! COUNTIF returns a value of one if the criteria (value) appears in the range (match) and a zero if it does not
appear (unique).
o Try: COUNTIF ($F$5:$F$12, D5)>0 for the first list.
Task 4: Use conditional formatting and formula to create a search bar and highlight a match.
o Hint! This is a variation of COUNTIF: our criteria is absolute – always in the search bar, while our range is relative. o Try: COUNTIF(M6, $M$4)>0
LOOKUP Formulas
Just like using a phone book to find a telephone number by looking up a name, lookup formulas return a value from your table by looking up a related value.
For simple lookups and decision-making formulas, IF statements can be used. However, nested if statements can quickly become very complicated:
o IF(B2>=65, “Pass”, “Fail”) compared to IF(B2>=90, “Excellent”, IF(B2>=80, “Very Good… o IF statements like the one above “lookup” one value and assigns a new value to a column. The lookup options below are usually a much better option:
Function
Arguments
VLOOOKUP VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
HLOOKUP HLLOKUP(lookup_value, table_array, row_index_num, [range_lookup])
LOOKUP LOOKUP(lookup_value, lookup_vector, [result_vector])
MATCH MATCH(lookup_value, lookup_array, [match_type])
INDEX INDEX(array, row_num, [col_num])
OFFSET OFFEST(reference, rows, cols, [height], [width])
VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP(what, where, return_what, [sorted])
VLOOKUP searches for a value (lookup_value) in the first column of the specified table (table_array). It returns the
corresponding value from the specified table column (col_index_num). The optional fourth argument determines whether or not an approximate (TRUE) or exact (FALSE) match is returned. If true, your data in the lookup column must be sorted in ascending order. VLOOKUP can only return a value to the right of the lookup column.
o Pros: Easy to use, straightforward formula
o Cons: Can only lookup value in first (left-most) column
Task: Write a lookup formula that returns the address of a given library.
LOOKUP
=LOOKUP(lookup_value, lookup_vector, [result_vector])
=LOOKUP(what, where, return_what)
LOOKUP searches for a value (lookup_value) in the specified range (lookup_vector). It returns the corresponding value
from the specified range (result_vector). Unlike VLOOKUPS, the LOOKUP function can search for and return values from any column. When looking up values, they must be sorted in ascending order.
INDEX-MATCH
=INDEX(array, row_num, [col_num])
=INDEX(where, what_row, what_column)
=MATCH(lookup_value, lookup_array, [match_type])
=MATCH(what, where, approximate_match])
INDEX returns the corresponding value for the row and column coordinates given. When used with the MATCH
formula, we can “feed” the index formula the position of a value. MATCH works by looking up a value in a specified column and returning the row position.
o Pros: Speed, flexibility
o Cons: More complicated to write than a VLOOKUP
Task: Write a lookup formula that returns the name of a library for a given phone number.
OFFSET-MATCH
=OFFSET(reference, rows, cols, [height], [width])
=OFFSET(from, move_this_many_rows, columns, [return_this_many_rows], [columns)
As we saw in the previous example, MATCH returns the position of a value in a column. Unlike other lookup formulas,
OFFSET can return a range of values.
o Pros: Flexibility, can return a range of values o Cons: More complicated to write than a VLOOKUP
Task: Write a lookup formula that returns the county, address, and phone number for a library. Tips for Lookup Formulas
Used named ranges: “Address” is easier to understand than “C3:C48” o Easier to remember
o Fewer errors
o Formulas are simpler and easier to understand Even better, use tables
Keep in mind match type
o Is your range sorted? Are you looking for an approximate or exact match? Wildcards
o Use ? to match any single character o Use * to match any number of characters
PivotTables
A PivotTable is a quick and interactive way of summarizing and analyzing large amounts of data. While most of the information and answers can be found using formulas and other calculation, PivotTables are faster and allow you adjust, filter, expand, and collapse while viewing the results instantly.
Why use PivotTables?
Summarize and analyze large amounts of data
Find totals and subtotals by category and subcategories
Expand and collapse data to view only what is most important/interesting Switch rows and columns (“pivot”)
Filter, sort, slice, and group Create interactive PivotCharts
Task: Using the data on “Kevin’s Ice Cream Shops” create a PivotTable, adjusting fields, filters, and values to answer the following questions:
1. How do drive-thru shops compare across all three counties?
2. How well does frozen yogurt sell at different types of stores? In different counties?
3. Show subtotal data for each county. Within each county, show data for each type of store. 4. Which location had the highest sales for each county?
Task: Create a PivotChart for a scenario above.
Tip! After inserting a PivotTable, use the check boxes or click and drag to add fields as rows, columns, values or filters. Tip! Want multiple filters? Try adding a slicer!
Tip! Want to show a count or average of values rather than a sum? Click the drop-down menu and choose “Value field settings…” for more options.
Tips and Tricks
Drill Down: double-click on a value to open a new sheet that shows all records that add up to it Trial and Error: switch fields from row to column, add and remove categories to find what works Show subtotals and grand totals using the “Design” tab options
Calculated fields: You don’t need to add another column to your table - create a calculated field to show things like Profit/Sales
