Advanced Excel

Created by David Moore, PhD

Key Concepts

1. Pivot Tables and Sort/Filter
2. Conditional Formatting
3. Charts
4. Concatenate and Indirect
5. TEXT functions
6. Goal

Pivot Table

• Efficient in making calculations by group
• Useful for segmented data. Ex. Car Sales by Month, Year, Region, Make, Model.
• Must refresh to update if source data changes

Sort and Filter

• Once formatted as table can use dropdown menu on column headers
• ALT H T to format as table (ALT JT G to return table to range)

Conditional Formatting

• Common uses
• Highlight cells that are greater/less than a value
• Display icons based on cell values

Using Charts

• Scatter is almost always preferred to a line chart
• Use a combo if graphing more than one series and scales are different
• Always label graph properly
• Should be clear to reader what graph is depicting

Concatenate and INDIRECT

• Can use the CONCATENATE function or the & symbol to "glue" text or references together
• Per usual text inputs require ""
• INDIRECT is used to grab a cell reference when the input is text based.

TEXT functions

• =LEN(text): returns length of referenced text string
• =LEFT(text,num_chars): returns the specified number of characters (num_chars) from the left of beginning of text string specified by text.
• =RIGHT(text,num_chars):returns the specified number of characters (num_chars) from the end of text string specified by text.
• =MID(text,start_num,num_chars): returns the text beginning at the n'th character(start_num) and ending n characters later (num_chars) given the text string specified by text.

TEXT functions Cont'd

• PROPER(text): Capitalizes the first character of each word and lower case for all other characters.
• UPPER(text): Changes all characters to upper case.
• LOWER(text): Changes all characters to lower case.
• SEARCH(find_text,within_text,[start_num]):Searches the specified text string (within_text) for the text (find_text) and returns the location where that text begins. Optional command start_num tells Excel to start searching for that text in the text string beginning n characters into the string. Allows wildcard characters and case insensitive.

TEXT functions Cont'd

• FIND(find_text,within_text,[start_num]): Exact same as SEARCH but does not allow wildcard characters and is case-sensitive.
• SUBSTITUTE(text,old_text,new_text): Replaces old_text with new_text in given text string.
• REPLACE(old_text,start_num,num_chars,new_text): Given a text string (old_text) replaces the specified number of characters beginning at a given position(start_num) with new_text.
• Text-to-column (ALT A E): Given a column of data will split each text string into multiple columns based on specified delimiter or fixed width.

Goal Seek

• Set Cell: Cell that contains a formula function that you want to set to a certain value
• To value: Value you want set cell to change to
• By changing cell: this cell must be referenced in the set cell formula.

Next time