excel 201 macros & controls
Post on 20-Apr-2015
147 Views
Preview:
TRANSCRIPT
Excel 201
Advanced Skills
This presentation is solely for the use of ZS Associates personnel. No part of it may be circulated, quoted or reproduced for distribution outside of ZS Associates without prior written approval of ZS Associates.
ZS Associates609.419.3800 Tel
www.zsassociates.com
2 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Please ensure you have the Excel 201 training materials on your computer
• Training materials for this session may have already been loaded on your laptops
– If not, download all files from: [ENTER PATH HERE]
• For all exercises, refer to “Excel 201 Exercises v1.0.xls”– Solutions can be found in “Excel 201 - Solution v1.0.xls”
Required Materials
3 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Agenda
• Introduction
• Concepts to Improve Workbook Usability
• Advanced Functions and Formulae
• Advanced Defined Names
• Controls
• Array Formulae
Introduction
• 5 min
• 45 min
• 45 min
• 30 min
• 30 min
• 30 min
4 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Excel 201 is designed to enhance and expand your Excel skills
• Excel 201 is designed to enhance your Excel skills through powerful formulae and functionality
• At the end of this course, you will be able to:
– Work more efficiently in Excel by using advanced functionalities
– Perform powerful analysis in Excel by understanding a wider range of options available to you
– Develop more dynamic Excel tools through the use of advanced formulae and functions
– Create user-friendly Excel workbooks by incorporating dynamic ways to interact with data
Introduction
5 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Agenda
• Introduction
• Concepts to Improve Workbook Usability
• Advanced Functions and Formulae
• Advanced Defined Names
• Controls
• Array Formulae
Introduction
• 5 min
• 45 min
• 45 min
• 30 min
• 30 min
• 30 min
6 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Conditional Formats change the appearance of a cell to reflect the value it contains
• When dealing with large amounts of data, it can be helpful to highlight records that meet certain criteria
• Cell formats can be specified using your customized toolbars or the cell properties menu
• Can be used to highlight values that meet certain criteria (e.g. outliers)
Example: Highlight Sales greater than $300
Workbook Concepts
7 20090416 - Excel 201 -v1.0© 2009 ZS Associates
• A formula can be used instead of the cell value to take advantage of relative references or worksheet functions
• Multiple conditions can be used to create different conditional formats
Advanced uses of conditional formats allow for multiple formats and multiple conditions
Workbook Concepts
If the formula evaluates to TRUE, the formatting will be applied
8 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Data Validation restricts the possible values of a cell to a given list or range
• The list option of Data Validation can be used as a simple version of a drop down box.
Criteria
Type of Criteria
Customized Alerts
Workbook Concepts
e.g. Can be useful to ensure client enters appropriate data
9 20090416 - Excel 201 -v1.0© 2009 ZS Associates
• Both a worksheet and the individual cells must have protection enabled (“locked”) to prevent changes from being made
• To lock a worksheet intended for client use:– Step 1: Unlock cells that users should be
able to edit– Step 2: Lock the worksheet
Passwords can be required to unlock the worksheet
Worksheet passwords are not secure!
Worksheet protection can be used to prevent changes to all or part of a worksheet
Workbook Concepts
10 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Workbook protection can be used to limit access to the workbook or changes to its structure
• Workbook protection can be used to require a password to open a workbook or save changes to a workbook
• Other workbook protection options can be found in Tools > Protection ([Alt]+T, P)
Workbook Concepts
Password for “write” access
Password to open
11 20090416 - Excel 201 -v1.0© 2009 ZS Associates
ZS has been hired by ABC Pharma to set product sales goals for each geography in their sales force
Workbook Concepts
Situation
•ABC Pharma has a single product with access to accurate and reliable account level product and market sales data
•They would like to use a goal based plan for their sales force’s IC plan
Complication
ABC pharma’s product is very mature and they would like their goals to be based 90% on the previous year’s product sales and 10% on each geography’s untapped potential
QuestionHow can we best design and create an excel workbook to calculate their goals?
• In a goal based plan, the field gets a bonus proportional to their goal attainment
• Attainment is calculated by dividing sales by the goal
12 20090416 - Excel 201 -v1.0© 2009 ZS Associates
After completing these exercises, you will understand the inputs to ABC Pharma’s goal setting process
• Review the inputs on the “Inputs” worksheet and review the raw account level sales data on the “Raw – Account Sales Data” worksheet
• Complete the tasks 1 – 5 listed on the “Exercises” worksheet
After completing the exercises in this section, you should understand data validation, formula auditing, named ranges and conditional formatting
Workbook Concepts
13 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Agenda
• Introduction
• Concepts to Improve Workbook Usability
• Advanced Functions and Formulae
• Advanced Defined Names
• Controls
• Array Formulae
Functions and Formulae
• 5 min
• 45 min
• 45 min
• 30 min
• 30 min
• 30 min
14 20090416 - Excel 201 -v1.0© 2009 ZS Associates
We will discuss a few advanced features that provide functionality beyond what was covered in Excel 101
Function Syntax Comments
VLOOKUP VLOOKUP(Lookup value, Lookup table, Offset, Type) Searches for a value in the leftmost column of a table, and then returns a value in the same row from the column specified
HLOOKUP HLOOKUP(Lookup value, Lookup table, Offset, Type) Searches for a value in the topmost row of a table, and then returns a value in the same column from the row specified
MATCH MATCH (Lookup value, Lookup range, Type) Returns the relative position of an item in an array that matches a specified value in a specified order
INDEX INDEX (Index range, Row, Column) Returns a value or the reference to a value from within a table or range
OFFSET OFFSET (Reference, Rows, Columns, Height, Width) Returns a range of specified Height and Width, which is located a specific number of Rows and Columns from the stated reference
INDIRECT INDIRECT(Reference) Returns the value in the cell which is specified by the stated reference
ADDRESS ADDRESS(Row_num, Col_num) Returns the address of the cells whose row and column numbers are used as parameters
ROW ROW(Reference) Returns the row number of a reference
COLUMN COLUMN(Reference) Returns the column number of a reference
SUMIF SUMIF(range, criteria, sum_range) Adds the cells specified by a given criteria
COUNTIF COUNTIF(range, criteria) Counts the number of cells within a range that meet the given criteria
RANK Rank(Number, Ref, [Order]) Returns the rank of a value within a specified range of values
SUMPRODUCT SUMPRODUCT(array1,array2,array3, ...) Multiplies corresponding components in the given arrays, and returns the sum of those products
TRANSPOSE TRANSPOSE(Source Range) Returns a vertical range of cells as horizontal, or vice versa
FREQUENCY FREQUENCY(data_array, bins_array) Calculates how often values occur within a range of values, and then returns a vertical array of numbers
TABLE TABLE(Input1,Input2) Used to perform sensitivity analysis on one and two variable tables
Functions and Formulae
15 20090416 - Excel 201 -v1.0© 2009 ZS Associates
SUMIF() and COUNTIF() functions can be used to aggregate data to a desired level
• Aggregate functions helps allow the same dataset to be used for different analyses
• These functions are preferred over Pivot tables as they eliminate the refresh task
Functions and Formulae
16 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Use the rank function to determine a value’s size relative to others in a list
• Returns the rank of a number in a list of numbers
• Syntax:– RANK(number,ref,order)
Number is the number whose rank you want to find. Ref is an array of, or a reference to, a list of numbers Order is a number specifying how to rank number.
– Use 0 (zero) for descending order and 1 (or any nonzero value) for ascending order
• After using the rank function, use other functions such as index and match to quickly produce tables for a given attribute listing the top or bottom items in a list
A B C D E1
2 Territory Sales ($MM)Sales Rank
3 A 2.1 =RANK(C3,C$3:C$6,0) 24 B 1.7 =RANK(C4,C$3:C$6,0) 45 C 3.0 =RANK(C5,C$3:C$6,0) 16 D 1.8 =RANK(C6,C$3:C$6,0) 37
Rank formula to use in column D
A B C D1112 Sales Rank Territory13 1 =INDEX(B$3:B$6,MATCH(B13,$D$3:$D$6,0)) C14 2 =INDEX(B$3:B$6,MATCH(B14,$D$3:$D$6,0)) A15 3 =INDEX(B$3:B$6,MATCH(B15,$D$3:$D$6,0)) D16 4 =INDEX(B$3:B$6,MATCH(B16,$D$3:$D$6,0)) B17
Index-Match formula to use in column C
Functions and Formulae
17 20090416 - Excel 201 -v1.0© 2009 ZS Associates
The OFFSET function is used to return a value or range of values based on location inputs
• OFFSET (Reference, Rows, Columns, Height, Width)– Returns a range of specified Height and Width, which is located a
specific number of Rows and Columns from the stated reference
If the Reference is a range of cells, the offset is counted from the upper left corner of the Reference range.
Functions and Formulae
18 20090416 - Excel 201 -v1.0© 2009 ZS Associates
TRANSPOSE can be used to normalize or denormalize data
• TRANSPOSE(Source Range)– Returns a vertical range of cells as a horizontal range, or vice
versa
• Example:
Functions and Formulae
TRANSPOSE() is different from pasting values; like any other function, it dynamically updates values when the underlying data changes
19 20090416 - Excel 201 -v1.0© 2009 ZS Associates
FREQUENCY can be used to quickly create histograms
• FREQUENCY(data_array, bins_array)– Highlight entire column when entering formula
data_array: bins_array: Frequency:69 68 34 52 10 1026 1 34 79 20 814 67 91 6 30 749 55 53 79 40 629 70 84 6 50 519 29 20 21 60 1276 14 91 74 70 89 43 37 52 80 12
64 79 80 48 90 818 55 36 58 100 451 84 21 3528 95 72 5512 58 63 797 64 52 46
12 23 3 8178 89 79 959 42 19 707 40 86 52
84 86 74 180 52 89 6
Functions and Formulae
20 20090416 - Excel 201 -v1.0© 2009 ZS Associates
One and two-variable data tables can be used to easily perform sensitivity analysis on a complicated model
• Example:
• To enter the formula– select the entire table (C12:G17)– On the Data menu, click Table– In the Row input cell box, enter the reference to the input cell for the input values in the row (C4) – In the Column input cell box, enter the reference to the input cell for the input values in the column (C2)– Click OK
A B C D E F G H I J K L M
1
2 mROI 25%
3 Optimal Size
4 Forecast ($MM) 550 144
5
6 Cost / PDE 110
7
“Complicated” Sizing Model
A B C D E F G H
10 Optimal Size
11 Forecast ($MM)
12 =L4 525 550 575 600
13
mR
OI
0%
{=Table(C4,C2)}
14 25%
15 50%
16 75%
17 100%
18
A B C D E F G H
10 Optimal Size
11 Forecast ($MM)
12 144 525 550 575 600
13
mR
OI
0% 147 154 162 171
14 25% 138 144 151 159
15 50% 129 134 140 147
16 75% 120 124 129 135
17 100% 111 114 118 123
18
Note: this formula is not an array formula (even though it will appear with brackets). It must be entered using the data table prompt
Functions and Formulae
21 20090416 - Excel 201 -v1.0© 2009 ZS Associates
We will also discuss how IS functions can be used to check results, and improve the appearance of workbooks
• IS functions check the type of the parameters and return TRUE or FALSE
• The IS functions are useful in formulae for testing the outcome of a calculationFunction Syntax * Tests for
ISBLANK ISBLANK(value) An empty cell.
ISERR ISERR(value) Any error value except #N/A.
ISERROR ISERROR(value) Any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ISLOGICAL ISLOGICAL(value) A logical value.
ISNA ISNA(value) The #N/A (value not available) error value.
ISNONTEXT ISNONTEXT(value) Any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)
ISNUMBER ISNUMBER(value) A number.
ISREF ISREF(value) A reference.
ISTEXT ISTEXT(value) Text.
Functions and Formulae
22 20090416 - Excel 201 -v1.0© 2009 ZS Associates
ISERROR can be used to make cleaner workbooks
• ISERROR(Value)– Value is the value you want tested
• Example:
Referencing blanks in a formula returns an error.
To avoid error messages we can use IF(ISERROR(Sales/Reps),“ ”,Sales/Reps).
Functions and Formulae
This is an example of on IS function. Other functions can be used in different situations
23 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Let’s use functions and formulae to calculate each territory’s goal
• Complete the exercises 6 – 13 listed on the “Exercises” worksheet
– Calculate each territory’s previous year product sales and market sales
– Calculate each territory’s goal by adding the historical portion and untapped potential portion of each territory’s goal
– Create ranks for territories based on their total goal and also their required growth rate
A territory’s required growth rate is their goal divided by their previous year product sales minus 1
After completing the exercises in this section, you will have successfully set the goals for each of ABC Pharma’s territories
Functions and Formulae
Required Growth Rate = - 1Goal
Previous Year Product Sales
24 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Agenda
• Introduction
• Concepts to Improve Workbook Usability
• Advanced Functions and Formulae
• Advanced Defined Names
• Controls
• Array Formulae
Advanced Defined Names
• 5 min
• 45 min
• 45 min
• 30 min
• 30 min
• 30 min
25 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Defined Names are more powerful than cell references
• Simplify formulae
• Create more robust analyses
• Design tools that are not dependent on a fixed amount of data
• Build graphs that automatically resize themselves to cover all of (and no more than) your data
Advanced Defined Names
26 20090416 - Excel 201 -v1.0© 2009 ZS Associates
We can use OFFSET() for resizing named ranges
• To create self-resizing named ranges we can combine COUNTA() and OFFSET() in “Define Name” window
Dynamic named ranges update when the length of an array changes.
Use offset to define the named range!
Advanced Defined Names
27 20090416 - Excel 201 -v1.0© 2009 ZS Associates
• Addressing functions take advantage of Excel’s tabular structure– Cells can have an address (A1:C1) or a name (myData)– Worksheets also have names (Sheet1)
• ADDRESS() will help identify the location name used by Excel
• INDIRECT() will help you use names that have been defined
• ROW() / COLUMN() return the number of a row or column
Addressing functions can create “dynamic” ranges that resize themselves
Sum(Sheet1!$A$1:$A$4)
Worksheet Name ! (Separator) Range Name
Advanced Defined Names
28 20090416 - Excel 201 -v1.0© 2009 ZS Associates
ADDRESS returns the location of a cell
• ADDRESS(Row_num, Col_num) is a powerful addressing function in Excel that lets us use our knowledge of how a spreadsheet is designed
• Example: ADDRESS(1,2) returns $B$1
= ADDRESS(1,2)
1st Row
2nd Column
Advanced Defined Names
29 20090416 - Excel 201 -v1.0© 2009 ZS Associates
INDIRECT uses a location or named range to return a stored value
• We can use INDIRECT() when we want to change the reference to a cell within a formula without changing the formula itself.
• Example:
= SUM(INDIRECT(C1))
Named Range “MDs” Named Range “Details”
= SUM(INDIRECT(B1))
Advanced Defined Names
30 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Formulae can provide added functionality when used as a defined name
• The cell reference and dimensions of a dynamic named range are functions of the value of some other cell(s) in the workbook
Enter a formula instead of a reference.
While it is important to be aware of this functionality in case you encounter it, we recommend that you do NOT use it; it makes workbooks very difficult to understand
Advanced Defined Names
31 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Use relative references with defined names to create “custom” functions
• Example:
We can define a named range called MySum that sums the range given in “Refers to:”
= MySum
The same function can be used to calculate the total number details.
Advanced Defined Names
While it is important to be aware of this functionality in case you encounter it, we recommend that you do NOT use it; it makes workbooks very difficult to understand
32 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Let’s make our workbook more dynamic by replacing some of the static named ranges with dynamic ones
• Complete exercise 14 listed on the “Exercises” worksheet of the goal setting workbook
– Delete the named ranges on the worksheet “Raw – Account sales Data”
– Replace them with dynamically defined named ranges Use the offset and counta functions in the named range definition
In future goal setting cycles, if the number of accounts changes, the goal setting workbook will now calculated territory level sales correctly!
Advanced Defined Names
33 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Agenda
• Introduction
• Concepts to Improve Workbook Usability
• Advanced Functions and Formulae
• Advanced Defined Names
• Controls
• Array Formulae
Controls
• 5 min
• 45 min
• 45 min
• 30 min
• 30 min
• 30 min
34 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Controls are great for building Excel tools
• What are controls?– Controls are elements on a worksheet that the
user can interact with
• There are 2 types of controls– Forms– Control Toolbox (ActiveX controls)
• To access Forms– Select View > Toolbars > Forms to bring up the
controls toolbar – Control formatting options are available by right-
clicking on the control
ActiveX controls provide many more options than Forms. But, sufficient care must be taken as ActiveX controls are sometimes unstable.
Controls
35 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Excel provides the user with various types of Controls
Drop Down Box List Box Group Boxes
Option Button
Check Box
Scroll BarSpinnerButton Text Box or Label
Controls
36 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Controls interact with the spreadsheet via “cell links”
2
2. Based on the user’s selection, the control outputs a result to the cell link
The cell link is designated under the “Control” tab in the Format Control dialog box
3. Use INDEX() to find the matching data
3
3
named ranges
1. The drop-down control takes an input range, from which the user can select one item
1The input range is defined under the “Control” tab of the Format Control dialog box
Controls
37 20090416 - Excel 201 -v1.0© 2009 ZS Associates
The Drop Down/Combo Box and List Box allow the user to select from a pre-defined list• Functionality: Drop Down/Combo and List Boxes
– The item selected is highlighted and displayed in the text box – The control returns a value representing the position of the selected
item
• When to use which ?– Drop Down/Combo Boxes requires less space on the worksheet – The List Box provides faster access
• Control Properties– INPUT RANGE– CELL LINK– DROP DOWN LINES– SELECTION TYPE
Drop Down Box List Box
Controls
38 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Option Buttons and Check Boxes are used to select multiple or a single option within a group
• Functionality
• Control Properties – VALUE– CELL LINK
Group Box
Option Buttons
Check Box
Option Button Check BoxBoth allow for a choice to be turned off/on
Single Selection: Selecting one button will deselect any others
Mutliple Selections: Boxes are independent
Return a value related to the choice selected
The # of the button checked Value for each box
Group Boxes Tell Excel which buttons work together
Format options for the user
Format options for the user
Multiple sets of Option Buttons on the same worksheet be in a Group Box or
they will ALL be linked together
Controls
39 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Scroll Bars and Spinners are linked to a cell or a text box to manipulate the values inside
• Functionality: Scroll Bar and Spinner– Adjust the value of a cell (cell link) from a
range of integer values
• Control Properties– CURRENT VALUE– MINIMUM VALUE– MAXIMUM VALUE– INCREMENTAL CHANGE– PAGE CHANGE– CELL LINK
Scroll BarSpinner
20
Controls
40 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Buttons, Labels, Text Boxes, and AutoShapes can initiate the execution of VBA code and can display cell contents
• Functionality– An advantage of displaying data in a control versus a cell is the
ability to freeze the size, position and format of the control regardless of what happens on the rest of the worksheet.
– They do not pass a value back to a cell– They do not have a “Control” tab on the “Format Control” dialog box.
Button Label Text Box
Show Graph
AutoShape
Controls
41 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Capabilities of Controls can be further enhanced by assigning Macros
• A Macro can be executed from the Macro dialog box
• Macros assigned to controls will execute every time the control is used
• Macro’s can also be recorded using the record Macro functionality
Controls
42 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Improve the user interface of a workbook by replacing some of the inputs cells with forms and/or controls
• Complete exercise 15 listed on the “Exercises” worksheet of the goal setting workbook
– Replace the input cells for maximum achievable share and the % of goal based on history with horizontal scroll bar forms
– Set appropriate minimum, maximum, and increment values for the scroll bars
Forms and controls can make it easy for users to refine workbook inputs
Controls
43 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Agenda
• Introduction
• Concepts to Improve Workbook Usability
• Advanced Functions and Formulae
• Advanced Defined Names
• Controls
• Array Formulae
Array Formulae
• 5 min
• 45 min
• 45 min
• 30 min
• 30 min
• 30 min
44 20090416 - Excel 201 -v1.0© 2009 ZS Associates
• An array is a collection of cells or values that is operated on as a group
• Array functions – Perform the same task on each cell of an input array– Return multiple values
• We use array formulae all the time– SUM and COUNT functions summarize data in ranges
Array formulae are a powerful feature of Excel that can be used to summarize lists of data by operating on each cell in an array
Array Formulae: Introduction
Array formulae allow for more complex calculations
45 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Array formulae are very powerful formulae that also provide flexibility in designing Excel tools
• The values get updated automatically when data is refreshed – convenient when data refresh happens often
• There is only one table to deal with – easy to expand to accommodate additional columns
• A combo box is utilized to display different region choices
• The offset worksheet function is used with array formulae to show territories in the selected region
Array Formulae: Introduction
46 20090416 - Excel 201 -v1.0© 2009 ZS Associates
An array formula can perform multiple calculations
Array Formulae: Concepts
• Entering an array formula– Type the array formula in the formula box– Press CTRL + SHIFT + ENTER
• When using multi-cell array formulae, all single-cell references are treated as absolutes
– In the example below, a multi-cell array formula is entered while selecting cells A2 to B2.
– The same value will result in all cells, because the reference to A1 is treated as an absolute
= { A1 + 10 }
A B C1 10 25 822 20 20 2034 20 35 925
= { A1:C1 + 10 }
47 20090416 - Excel 201 -v1.0© 2009 ZS Associates
We can use the binary logic shortcuts instead of writing out the IF() function
• Comparison operators like < , > result in a TRUE or FALSE (Boolean) reply
• To convert Boolean (True/False) values to Binary (1/0) values that can be used in other calculations, multiply the result by 1
– True * 1 = 1– False * 1 = 0
A B = (A>B) = (A<B) = (A=B)5 10 FALSE TRUE FALSE
A B = (A>B)*1 = (A<B)*1 = (A=B)*15 10 0 1 0
Array Formulae: Binary Logic
48 20090416 - Excel 201 -v1.0© 2009 ZS Associates
ORX Y X+Y0 0 00 1 11 0 11 1 2
Using binary logic with array formulae allows you to create advanced formulae with multiple criteria
• The AND operator can be simulated with multiplication ( * )
X Y X AND Y0 0 FALSE0 1 FALSE1 0 FALSE1 1 TRUE
AND
X Y X OR Y0 0 FALSE0 1 TRUE1 0 TRUE1 1 TRUE
OR
• The OR operator can be simulated with addition ( + )
• Note: There is no direct arithmetic operator that is equivalent to the NOT operator
Array Formulae: Binary Logic
X Y X*Y0 0 00 1 01 0 01 1 1
AND
49 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Array formulae are appropriate in many, but not all situations
• Can increase complexity of work / Black-Box effect
• Calculation time can increase dramatically
• Easily broken
• Requirements of array formula components to be the same size
• Limitations of multi-cell array editing
Array Formulae: Appropriate Use
50 20090416 - Excel 201 -v1.0© 2009 ZS Associates
ZS will also set district and regional goals and provide several territory goal summaries for ABC Pharma
• Complete exercises 16 - 24 listed on the “Exercises” worksheet of the goal setting workbook
– Calculate district and region historical product and market sales– Use advanced conditional formatting to highlight territories with
the highest goals or growth– Use the frequency function to create a histogram– Use index, match, and indirect to fill in values inside summary
tables
Advanced Functions and Formulae
Using advanced functions and formulae gives your workbooks tremendous power and flexibility!
Appendix A
Keyboard Shortcuts
Appendix
52 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Keyboard shortcuts
Alone Shift+ Ctrl+ Ctrl+Shift+ Alt+F1 Help Context Help F11F2 Formula Bar Edit a Cell Note Show Info Window F12F3 Paste Function Wizard Define Name Create NamesF4 Refer/Repeat Find Next Close Find Previous ExitF5 Goto Find RestoreF6 Next Pane Previous Pane Next Window Prev WindowF7 Check Spelling MoveF8 Extend Toggle Add Mode Toggle SizeF9 Calculate Calculate Sheet Minimize WorkbookF10 Menu Bar Shortcut Menu Maximize WorkbookF11 New Chart New Worksheet Excel 4 Macro SheetF12 Save As Save Open Print
Appendix
53 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Keyboard shortcuts (cont.)
Editing Highlight Section Formula BarCtrl C Copy Ctrl A Select All Ctrl ;Ctrl Ins Copy Ctrl Space Select Column Ctrl :Ctrl X Cut Shift Space Select Row Alt =Shift Del Cut Ctrl Shift Space Select Worksheet Alt +Ctrl V Paste Ctrl / Select Current Array Ctrl 'Delete Clear Ctrl * Select Current Region Ctrl "Ctrl + Insert Ctrl } Select All Dependents Alt EnterCtrl - Delete Ctrl ] Select Direct Dependents Ctrl Alt Tab
Ctrl { Select All Precedents Ctrl DelWorkbook Ctrl [ Select Direct Precedents Ctrl Shift DelCtrl N New Ctrl ? Select NotesCtrl O Open Ctrl \ Select Row Differences OtherCtrl P Print Ctrl | Select Column Differences Ctrl FCtrl S Save Alt ; Select Visible Cells Ctrl HCtrl W Close Ctrl GCtrl Pg Up Next Sheet in Workbook Formatting Ctrl ZCtrl Pg Down Prev Sheet in Workbook Ctrl 1 Format Cells Alt Bkspace
Ctrl 2, Ctrl B Bold Alt EnterDisplay Ctrl 3, Ctrl I Italic Ctrl YCtrl 7 Show/Hide Std Toolbar Ctrl 4, Ctrl U Underline Ctrl RCtrl 8 Show/Hide Outline Symbols Ctrl 5 Strike through Ctrl DCtrl 9 Hide Selected Rows Alt ' Style Box Ctrl TabCtrl 0 Hide Selected Columns Ctrl & Add Outline Border Ctrl Shift TabCtrl ( Display Selected Rows Ctrl _ Remove All BordersCtrl ) Display Selected ColumnsCtrl ` Display formulas/values
Appendix
Appendix B
Appendix
Adding drop-down menus to charts
55 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Adding Drop Down/Combo and List Boxes can be time intensive, but creates a dramatic effect
• Graphs with Drop Downs ease the presentation of multiple graphs:
– Present different scenarios in an easily accessible, uncluttered worksheet
– User can focus on what he/she wants– Eliminates time necessary to scroll through multiple graphs– When used effectively, creates a more appealing user interface– Refer to an entire data range
Appendix
56 20090416 - Excel 201 -v1.0© 2009 ZS Associates
To add Drop Down boxes to your graphs, prepare the underlying data first
• Prepare the raw data table– Create a table with columns for each of the pull-down boxes and the x- and y-axis
values
– Separately, create a list of unique values for each control E.g. if one control will allow the user to select from a list of products – create a unique list of
products to use as the INPUT RANGE for the control
– Prepare a final table to show the data for the graph – we’ll use INDEX/MATCH functions to fill in the table dependant on the user’s selections
• Create the pull-down boxes– Set the INPUT RANGEs for each control– Set the Cell Link to be used
Unique Key Product Ventile Time Period P1E Market Share # DoctorsA _1_1_0 A 1 1 0 8.61% 291A _1_1_1 A 1 1 1 13.20% 352A _1_1_2 A 1 1 2 17.46% 176A _1_1_3 A 1 1 3 18.15% 122A _1_1_4 A 1 1 4 15.36% 62A _1_1_5 A 1 1 5 26.19% 41
Add a unique key by concatenating the pull-down box columns with
the x-axis values
X–axis values
Y–axis values
Pull-down box values
Appendix
57 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Create a graph based on a final, fixed dimension data table
• The final data table will use lookup formulae to assemble the data
– Create a unique list of the possible x-axis values– Create an associated list of y-axis values in the next column
Use a lookup function to find the values associated with the x-values based on the values chosen from the pull-down boxes
The unique key will serve as an index to pull information from the raw data table
• Create the graph based on this final data table– Changing the drop down boxes will update the data table – and
your graphsP1E Market Share # doctors Product
0 8.61% 291 11 13.20% 352 A2 17.46% 1763 18.15% 1224 15.36% 625 26.19% 41
Cell designated as "Cell Link"Cell referenced to variable chosen with pull-down box
X–axis values
Y–axis values
Appendix
Appendix C
Appendix
Using dynamic ranges with graphs
59 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Graphs and charts can use Dynamic Ranges to ensure the correct scale is used to present data
• Dynamic named ranges can be used to limit the cells in a named range only to nonempty cells
• Useful as inputs to graphs– Ensures that the data range consists of only nonempty cells– Changes the scale of the graph to exactly fit the data points
* For continuous data
Appendix
60 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Special settings are needed to use Dynamic Ranges in Charts
Rather than entering cell references, enter ‘Workbookname.xls’!Rangename
When the named range changes size, the data on which the graph is based also changes size.
Select “Chart | Source Data”
Appendix
Appendix D
Appendix
Formula Auditing
62 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Highlight a section of a formula in the formula bar…
…and hit “F9” to see that portion of the formula evaluated
Use Formula Auditing features and “F9” to troubleshoot complex formulae
Use precedents and dependents to trace data through a sheet
Use “Evaluate Formula” to step through the parts of a formula
Appendix
Appendix E
Background Information for Exercise
Appendix
64 20090416 - Excel 201 -v1.0© 2009 ZS Associates
Goal setting review how we will set goals for ABC Pharma
Workbook Concepts
Analysis Approach
The sum of every territory goal should equal the national forecast
Goal1 + Goal2 + Goal3 + … + Goaln = ABC’s national forecast
90% of the national forecast will be allocated to every territory as the “history portion of their goal” based on each territory’s proportion of the nation’s previous year product sales
Goal1(historical component) + Goal2(historical component) + Goal3(historical component) + … + Goaln(historical component) = 90% * national forecast
For example, if in the previous year territory 1 accounted for 3.2% of national sales, then:
Goal1(historical component) = 90% * national forecast * 3.2%
10% of the national forecast will be allocated to every territory as the “untapped potential (U.P.) portion of their goal” based on each territory’s proportion of the nation’s untapped potential
Goal1(U.P. component) + Goal2(U.P. component) + Goal3(U.P. component) + … + Goaln(U.P. component) = 10% * national forecast
Untapped potential in a given territory is found by subtracting the current product sales from the maximum achievable sales (M.A.S.)
U.P. = M.A.S. – Current Product Sales
For example, if in the previous year territory 1 had $1.2 MM in sales and has M.A.S. of $1.5 MM, then territory 1’s untapped potential is $300K
The maximum achievable sales (M.A.S.) are the market sales multiplied by the maximum achievable share
For example, market sales in territory 1 are $3.0 MM and the maximum acheivable share is 50%, so the maximum achievable sales are:
$3.0 MM * 50% = $1.5 MM
1
2
3
top related