Excel Tips and Tricks Presented by Tracee Baker
Excel Tips and Tricks Presented by: Tracee Baker, Business Analyst, TD Ameritrade July 21, 2015 This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 1
A Little Bit About Me • Approximately eight years of experience in the Business Analysis roll • Insurance Industry • Financial Industry • Approximately eleven years of business experience (Product Development) • Analytic and Business Analysis background used during this time This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 2
Housekeeping Items • Have a tendency to talk fast, please let me know if I need to slow down • Very casual presentation, so feel free to ask questions along the way • I may not have all the answers to your questions – but I am pretty good at eventually figuring things out This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 3
What We Will Review Today Sharing tools that I find useful in excel. • VLOOKUP/HLOOKUP functions • Different ways to identify data matches • Pivot tables • Inserting existing documents or pictures to an excel sheet This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 4
VLOOKUP and HLOOKUP Functions This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 5
VLOOKUP Function/Formula VLOOKUP is a VERTICAL (column) search for a value. Using VLOOKUP is similar to looking up a person’s name in a telephone book to get a telephone number. • VLOOKUP finds a value in one column, • and returns its corresponding value on the same row in another column. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 6
HLOOKUP Function/Formula HLOOKUP is a HORIZONTAL (row) search for a value. • HLOOKUP finds a value in one ROW • and returns its corresponding value on the same column in another row. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 7
VLOOKUP Function/Formula – Exact Match VLOOKUP has 4 parameters 1. What are you looking up? (lookup_value) =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) =VLOOKUP(A 2, ‘Kit. Data!$A$2: $C$16 , 3 , FALSE) 1 2. Where are you looking it up? (table_array) 2 3 3. What column has the result? (col_index_num) 4. Close or exact match? (range_lookup) Exact match = FALSE Close match = TRUE or omitted This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 8
VLOOKUP/HLOOKUP Function/Formula KEY THINGS TO KNOW • If FALSE, formula will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned. Example: True =vlookup(lookup_value, table_array, col_index_num, false) • If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. Example: True =vlookup(lookup_value, table_array, col_index_num, true) Example: Omitted =vlookup(lookup_value, table_array, col_index_num) NOTE: The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 9
VLOOKUP/HLOOKUP Function/Formula KEY THINGS TO KNOW • Use “$” in table array portion (F 4) of formula to keep table data stagnant when you copy formula. • These will automatically be included when your array is located in a different spreadsheet (aka workbook) =VLOOKUP(A 2, ‘Kit. Data!$A$2: $C$16 , 3 , FALSE) • The first column of your table_array must be the column where you are looking for your lookup_value. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 10
VLOOKUP/HLOOKUP Function/Formula Let’s walk through a couple of live examples. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 11
Finding Data Matches This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 12
Different Ways to Identify Data Matches • MATCH Formula/Function • Conditional Formatting This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 13
MATCH Function/Formula MATCH has 3 parameters 1. What are you looking up? (lookup_value) =MATCH(lookup_value, lookup_array, range_lookup) =MATCH(A 2, $C$2: $C$16, 0) 2. Where are you looking it up? (lookup_array) 3. Close or exact match? (match_type) Less than <= 1 Exact match = 0 Greater than >= -1 Value returned is the relative position of that item in the target range. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 14
Conditional Formatting With your data set highlighted, from the ‘HOME’ ribbon simply click: • Conditional Formatting • Highlight Cell Rules • Duplicate Values This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 15
Conditional Formatting Duplicate Values Dialog Box • Can find either the Duplicate or Unique values • Select or set the formatting to highlight the similarities/differences This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 16
Different Ways to Identify Data Matches Let’s walk through a couple of live examples. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 17
Pivot Tables This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 18
Pivot Tables There are not as painful as you may think…. . https: //www. youtube. com/watch? v=n 67 RYI_0 sc 0 This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 19
Pivot Tables Pivot tables allow you to quickly summarize and analyze large amounts of data in lists and tables -- independent of the original data layout in your spreadsheet -- by dragging and dropping columns to different rows, columns, or summary positions. Among other functions, a pivot-table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet, without having to write a single formula. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 20
Pivot Tables 1. Select the data range that you wish to be included in your Pivot Table 2. From your ‘INSERT’ ribbon click Pivot. Table. 3. Within Create Pivot. Table Dialog Box - Select to place pivot table in either: 1. New Worksheet 2. Existing Worksheet (this will prompt you to select the location of the pivot table NOTE: All columns must have a header. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 21
Pivot Tables 1. Select the data range that you with to be included in your Pivot Table 2. From your ‘INSERT’ ribbon click Pivot. Table. 3. Within Crate Pivot. Table Dialog Box - Select to place pivot table in either: 1. New Worksheet 2. Existing Worksheet (this will prompt you to select the location of the pivot table 4. Drag and drop the fields to the Row Labels and Values boxes This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 22
Pivot Tables Quickly and easily you have a table that illustrates the sum of sales by product. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 23
Pivot Tables To format your sales numbers simply: 1. Right click within the column you wish to format 2. Select Number Format 3. Format Cells Dialog Box will allow you to set your preferred number format This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 24
Pivot Tables Let’s walk through a couple of live examples. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 25
Inserting Existing Documents Into Excel This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 26
Inserting Existing Documents Into Excel 1. From your ‘INSERT’ ribbon click Object. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 27
Inserting Existing Documents Into Excel 2. Within Object Dialog Box - Select Create From File. 3. Select Browse… to choose the folder in which the document you wish to insert is located. 4. Check the Display as Icon box 5. Select OK This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 28
Inserting Existing Documents Into Excel Let’s walk through a live example. This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 29
Useful Links Keyboard Shortcuts Excel 2007 Excel 2010 Excel 2013 This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 30
Additional Questions ? This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 31
THANK YOU! This document contains confidential information for use by TD Ameritrade Holding Corporation and its subsidiaries. 2/24/2021 32
- Slides: 32