Advanced Excel Workshop Spring 2011

1. Advanced Excel Workshop Spring 2011 William F. Mee Instructor ISOM wmee@suffolk.edu February 12, 2011 From www.suffolk.us/wmee/ under Excel Workshop Link => select files, then Save to desktop: Employee, Stocks, ExcelConcepts and ProForma Save to flash drive: AppliedExcelConceptsCompleted

2. Advanced Excel Agenda • Goal: Build dynamic – powerful and flexible - tools • Review of Excel Observations • Applying Excel to common business problems

3. Excel Observations • Calculations / Functions all begin with an “=” • Use cell references in calculations • Cell references can be of three types: • Relative – change when copied • Absolute –do not change when copied • Mixed – containing both relative and absolute values • Cell references change relative to the direction they are copied • Excel processes calculations or formulas using the rules of Algebra: • Please Excuse My Dear Aunt Sally - PEMDAS • Parenthesis, Exponents, Multiplication and Division, Addition and Subtraction.

4. Excel Observations • Keep your formulas simple: Do not overuse the SUM function • Functions: Insert the function and complete the Function Arguments dialogue • Design before you build: When developing a complex calculation / formula (etc.) sketch out what you want to do before you build it • Formatting not only makes a spreadsheet more readable but also allows the creator to highlight or focus the readers’ attention to certain locations

5. Excel Observations • Dynamic Spreadsheets: • Use cell references in your calculations • Isolate conditions (variables) from analysis: • Conditions – top left or on own page • Analysis – below conditions • Always refer to conditions as absolute references in the analysis • Cell naming creates an absolute reference • Cell naming helps document the analysis