Advanced Excel Techniques
OAIR Conference 2007
Columbus, OH
Ashutosh Nandeshwar
ii
Contents
1 Shortcuts 1
1.1 Take Control of the “Ctrl” Key . . . . . . . . . . . . . . . . . . . . . . . 1
1.1.1 Cut-Copy-Paste . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.1.2 Move Around . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.1.3 Misc . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.2 General . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.3 Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2 Formulae 5
2.1 IF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2.2 VLOOKUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
2.3 COUNTIF and SUMIF . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
2.4 MATCH, INDEX, and OFFSET . . . . . . . . . . . . . . . . . . . . . . . 8
2.5 SUMPRODUCT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
3 Magic of Array Formulae 15
3.1 Count Unique Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3.2 Count Duplicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
3.3 Average . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
3.3.1 Average Excluding Zeros . . . . . . . . . . . . . . . . . . . . . . . 16
3.3.2 Average of n Large or Small Numbers . . . . . . . . . . . . . . . . 17
4 Visual Basic for Applications (VBA) 19
4.1 Where to Put the Code? . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
4.2 Macro Recorder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
4.3 Hello World! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
4.4 Autofit Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
4.5 Manual Calculation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
4.6 Footer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
4.7 Print Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
4.8 User-Defined Function (UDF): Reverse String . . . . . . . . . . . . . . . 23
4.9 Concatenate Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
4.10 Flip a Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
4.11 Fill Blank Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
5 Resources 29
Bibliography 31
iv CONTENTS
A Keyboard Shortcuts from 33
List of Figures
1.1 Fixed Number of Zeros using TEXT and REPT Function . . . . . . . . . | 3 |
1.2 Quick tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . | 3 |
1.3 Creating Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . | 4 |
2.1 Simple IF formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . | 5 |
2.2 IF with AND and nested IF . . . . . . . . . . . . . . . . . . . . . . . . . | 6 |
2.3 IF formula with AND and OR . . . . . . . . . . . . . . . . . . . . . . . . | 6 |
2.4 Nested IFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . | 6 |
2.5 VLOOKUP Formula with ISNA . . . . . . . . . . . . . . . . . . . . . . . | 7 |
2.6 Vlookup Formula with Range . . . . . . . . . . . . . . . . . . . . . . . . | 8 |
2.7 Examples of COUNTIF Function . . . . . . . . . . . . . . . . . . . . . . | 9 |
2.8 Examples of SUMIF Function . . . . . . . . . . . . . . . . . . . . . . . . | 9 |
2.9 Example of MATCH Function . . . . . . . . . . . . . . . . . . . . . . . . | 10 |
2.10 Example of INDEX Function . . . . . . . . . . . . . . . . . . . . . . . . | 10 |
2.11 Using MAX, MATCH, and INDEX Functions . . . . . . . . . . . . . . . | 11 |
2.12 Example of OFFSET Function . . . . . . . . . . . . . . . . . . . . . . . . | 12 |
2.13 Example of SUMPRODUCT and SUM as an Array Formula . . . . . . . | 12 |
2.14 Colors of Range Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . | 13 |
2.15 Example of SUMPRODUCT and SUM for Multiple Conditions . . . . . | 13 |
2.16 Array Coercion Example . . . . . . . . . . . . . . . . . . . . . . . . . . . | 14 |
3.1 Example of AVERAGE Function Excluding Zeros . . . . . . . . . . . . . | 16 |
3.2 AVERAGE and SUM of Top Three and Bottom Three . . . . . . . . . . | 18 |
4.1 Steps to Create Custom Toolbar Buttons for Macros [1] . . . . . . . . . . | 21 |
4.2 Example of Reverse String UDF . . . . . . . . . . . . . . . . . . . . . . . | 23 |
4.3 Example of Concatenate Range Function . . . . . . . . . . . . . . . . . . | 24 |
4.4 Example of Flip Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . | 25 |
4.5 Example of Fill Blanks Procedure . . . . . . . . . . . . . . . . . . . . . . | 27 |
vi LIST OF FIGURES
Listings
4.1 Hello World Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . | 20 |
4.2 Autofit Column and Unwrap text . . . . . . . . . . . . . . . . . . . . . . | 20 |
4.3 Manual Calculation Macro . . . . . . . . . . . . . . . . . . . . . . . . . . | 22 |
4.4 Procedure to Set Footer . . . . . . . . . . . . . . . . . . . . . . . . . . . | 22 |
4.5 Print Formula in Adjacent Cells . . . . . . . . . . . . . . . . . . . . . . . | 22 |
4.6 Reverse String UDF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . | 23 |
4.7 Palindrome Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . | 24 |
4.8 Concatenate Range Function . . . . . . . . . . . . . . . . . . . . . . . . . | 24 |
4.9 Procedure to Flip a Range . . . . . . . . . . . . . . . . . . . . . . . . . . | 26 |
4.10 Procedure to Fill the Blanks . . . . . . . . . . . . . . . . . . . . . . . . . | 28 |
viii LISTINGS
Chapter 1
To paste special (values): for 2003 and lower, follow this sequence- Ctrl + C, Ctrl + V,
Alt + E, V, Enter
Note: To select data between current position and position you want to go to, hold the
Shift key
To go to cell A1- Hold Ctrl and Press Home
To go to the last cell in the data- Hold Ctrl and Press End
To go to the last non-blank cell in a column from top- Hold Ctrl and Press Down Arrow
To go to the last non-blank cell in a column from bottom- Hold Ctrl and Press Up Arrow
To go to the next worksheet- Hold Ctrl and Press Page Down
To go to the next worksheet- Hold Ctrl and Press Page Up
To select the current row- Hold Shift and Press Spacebar
To select the current row- Hold Ctrl and Press Spacebar
To format the current selection- Hold Ctrl and Press 1
To zoom in or out in the worksheet- Hold Ctrl and Scroll mouse wheel up or down
1
CHAPTER 1. SHORTCUTS
To view the formulae instead of their values- Hold Ctrl and press tilde (?)
To force a new line in the text- Hold Alt and press Enter
To change the reference (relative to absolute) of a range in a formula- Select the range in the formula and Hit F4 to cycle to through the references
To fill the data or formula down from the top cell to all the cells below- Double-click
the thick plus() symbol at the right-bottom corner of the cell Note: filling-down will stop whenever a blank cell on the left-side is encountered
To fill the data or formula, when double-clicking does not work, follow these steps:
1. Go to the first cell that you would like to copy or fill in other cells
2. Press F2 to edit the formula in that cell
3. In the name box , type in the address of the last cell of the range, where you want to fill data or formula, then Hit Enter while Holding the Shift key
To convert “textnumbers” to numbers use any of these methods:
1. Type 0 in a blank cell, copy that cell, and Paste Special > Add over the range, which has “textnumbers”
2. Select the column that has “textnumbers”, and use Text to Columns on this range (without providing any delimiters)
3. Use SmartTags
To get fixed number of zeros in front of a number use this formula
1.2. GENERAL 3
=TEXT(value,REPT("0",n))
where value is the number that you want to format and n is the number of zeros you want in front of the number. See Figure 1.1.
Figure 1.1: Fixed Number of Zeros using TEXT and REPT Function
To get the unique records from the data follow these steps:
2. On the Data menu, point to Filter, and then click Advanced Filter
3. Do one of the following:
(a) To filter the range or list in place, similar to using AutoFilter, click Filter the list, in-place
(b) To copy the results of the filter to another location, click Copy to another location. Then, in the Copy To box, enter a cell reference
4. Select the Unique records only check box
Quick Tool provide stats, such as Average, Count, Count Nums, Max, Min, and Sum of the data from a selected range without entering any formulae. To change the calculation, right click on the bottom toolbar and choose the desired statistic, see Figure 1.2
(a) Quick tool in 2007 (b) Quick tool in 2003
To view statistics, such as Sum, Average, Max, and Min on the data with AutoFilter on, use the SUBTOTAL function. For example, =SUBTOTAL(9,A1:A20) will return the sum of the cells from the range A1 to A20 that are filtered and visible.
CHAPTER 1. SHORTCUTS
Names are helpful, as they are easy to remember, and rather than providing a reference to a range, we can directly refer to them by using names. We can assign names to formulae and range. Names are specially useful when you have big data table, and you would like to do counts and sums, or use other formulae. One quick way to define names is using the “Create Name” feature (see Figure 1.3(a)). To use this follow these steps:
1. Select your data (shortcut: Ctrl + A, or go to the first cell (Ctrl + Home) and press Ctrl + End)
2. Click on the Insert Menu, then click on the Name option, and then click on Create, or Ctrl+Shift+F3
3. Check the Top row box to give names to the columns of data
(a) Create Names Box
(b) Name in the NameBox
After the names are created, if you select the complete data range, it would show the name of the range in the name box, see Figure 1.3(b).
Chapter 2
The syntax of an IF formula is:
IF(logical_test,value_if_true,value_if_false)
For example, if we create an indicator for non-residents, and the condition is that if the permanent state is not equal to OH, then non-resident indicator is equal to Y, else N. We can write this formula two ways (See Figure 2.1):
=IF(B2<>"OH","Y","N")
=IF(B3="OH","N","Y")
If we want to add some more conditions to IF formula, we can use AND, OR, or nested IFs (up to seven for 2003^{1} and lower, and 64 for 2007^{2}.) For example, we want scholarship indicator to be Y if the GPA is greater than equal to 2.5 and need greater than equal to 500. This can be achieved two ways (see Figure 2.2):
1. Using AND
=IF(AND(B12>=2.5,C12>=500),"Y","N")
2. Two nested IFs
=IF(B15>=2.5,IF(C15>=500,"Y","N"),"N")
5
We can build any condition for the first argument of the IF formula that results in a TRUE or FALSE. For example, if we create an “at-risk” indicator, based on two conditions:
1. If the current GPA is less than 2 AND current enrolled hours are less than 10, OR
2. Number of courses with F is greater than 1
We can build a formula using IF, AND, and OR to achieve this (see Figure 2.3):
=IF(OR(AND(B21<2,C21<10),D21>1),"Y","N")
Of course, we can create a lot more complicated IF formulae by nesting multiple IFs. For example, if we want to convert college codes to college description, we can use nested IFs to achieve that (see Figure 2.4); however, a better way to do that is using VLOOKUP.
=IF(A29="A","College of Arts",IF(A29="B","College of Business",IF(A29="T",
"College of Technology",IF(A29="E","College of Engineering","NA"))))
1
2
2.2. VLOOKUP
VLOOKUP formula searches for a value in the first column of a table array and returns a value in the same row from another column in the table array. The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find. The syntax of VLOOKUP formula is:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
In the above example, our lookup value is the college code and table array is a table with the codes and its descriptions. The column index is the column that we want to return after matching a code, and the range lookup is an argument to search for exact matches (1 or TRUE) or approximate matches (0 or FALSE); however, if the search is for an approximate match, then the values in the first column need to be sorted in an ascending order. If the exact match argument is set to true, and if no match is found, this formula would return a #NA error. To overcome this error we can nest an IF formula with ISNA function to check if the VLOOKUP formula causes an error. See Figure 2.5 and notice the shaded area for ISNA function.
=IF(ISNA(VLOOKUP(B50,$A$37:$B$40,2,0)),"Not found",
VLOOKUP(B50,$A$37:$B$40,2,0))
In Excel 2007, we can use function IFERROR to check if a formula is generating an error, and this function will evaluate the first argument, if it generates an error, the function will return the value supplied in the second argument.
=IFERROR(VLOOKUP(B49,$B$37:$C$40,2,0),"Not found")
We can use the approximate match argument when we want to return ranges. For example, if we want to return the range on GPA based on the following conditions:
1. if GPA is >= 0 and < 2, the range is poor,
2. if GPA is >= 2 and < 2.5, the range is OK,
3. if GPA is >= 2.5 and < 3, the range is moderate,
4. if GPA is >= 3 and < 3.5, the range is good,
5. if GPA is >= 3.5, the range is very good
We can construct a lookup table as given in Figure 2.6(a), and use it in a VLOOKUP formula as given in Figure 2.6(b).
(a) (b) Vlookup Formula
Lookup Table
Figure 2.6: Vlookup Formula with Range
COUNTIF function is useful when you quickly want to know a count based on a criterion (for multiple criteria use Pivot tables, use array formulae, or use COUNTIFS in 2007.) See Figure 2.7 for examples; the syntax of COUNTIF is:
COUNTIF(range,criteria)
SUMIF function is similar to the COUNTIF function; however, it offers two rangesone to test the criterion, and the other to sum based on the criterion. IF sum range is omitted this function will sum the criterion range. See Figure 2.8 for some examples; the syntax of SUMIF is:
SUMIF(range,criteria,sum_range)
MATCH by itself is nothing special; it returns the position of an item in an array; however, using some creativity and other functions wonderful formulae can be written. The syntax of MATCH is:
MATCH(lookup_value,lookup_array,match_type)
2.4. MATCH, INDEX, AND OFFSET
Figure 2.7: Examples of COUNTIF Function
Figure 2.8: Examples of SUMIF Function
where lookup value is the value we want to find in the lookup array, and match type determines the match type between exact and approximate (greater or smaller) match. See Figure 2.9 for an example.
INDEX function in an array form returns an item from an array when given the row and the column number. See Figure 2.10 for an example. The syntax of INDEX is:
INDEX(array,row_num,column_num)
As you can see, MATCH and INDEX by themselves are not very useful;however, when we combine we can produce great results. For example, let’s look at the survey, where we asked people to rate themselves (categories: Don’t know, Newbie, Savvy, Expert) on Excel (concepts: Excel program itself,Conditional formatting,Spreadsheet formatting and working,Simple formulae,Advanced formulae,Filters, Advanced Filters,VBA (macros) Pivot tables,Data analysis), and I wanted to find the category that maximum people rated themselves, for each concept (see Figure 2.11 ). To achieve this I used this formula:
=INDEX($B$1:$E$1,0,MATCH(MAX(B2:E2),B2:E2))
where the range B1:E1 has the categories, and the range B2:E2 had the ratings. For the concept “Excel program itself”, maximum number (14) of people rated themselves as “Savvy”, but to find this using a formula is a three step process:
1. Find out the maximum number of ratings for each concept, and this is done using the MAX function, which returns 14 for the range B2:E2.
=MAX(B2:E2)
2. Find out the position of this maximum value in the range B2:E2, and this is done using the MATCH function, which returns 3.
2.5. SUMPRODUCT
=MATCH(14,B2:E2))
3. Find out the category name for this column position from the range B1:E1, and this is done using the INDEX function, which returns “Savvy.”
=INDEX($B$1:$E$1,0,3)
OFFSET returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. In a sense, this function is similar to the INDEX function; however, rather than returning a value from an array, OFFSET returns a cell or range reference. The syntax of OFFSET function is:
OFFSET(reference,rows,cols,height,width)
When I am using the values from a PivotTable to move to a nicer format, I have found particular use of the OFFSET function. For example, consider the table obtained in Figure 2.12(a), and we would like to report the yield percentages in a nicer format as shown in Figure 2.12(b). This is achieved by using this OFFSET formula, and dragging it across and down:
=OFFSET($B15,0,B$21)
SUMPRODUCT function multiplies the input arrays, and then adds them. The syntax of SUMPRODUCT is:
SUMPRODUCT(arr1,arr2, ,arr30) for Excel 2003
SUMPRODUCT(arr1,arr2, ,arr255) for Excel 2007
(a) Pivot Table Data
(b) Using OFFSET Function
Figure 2.13 is an example of SUMPRODUCT function, and SUM formula used as an array formula. Array formulae are entered by pressing SHIFT, CONTROL, and ENTER at the same time. Results of both formulae are achieved in two-steps:
1. Multiply each item of the arrays:
5 × 6 = 30
7 × 8 = 56
6 × 9 = 54
9 × 5 = 45
2. Sum these values:
The real magic of array formulae is in counting and summing based on multiple conditions. For example, if we want to count number of students based on some criteria, we can use either SUMPRODUCT, or SUM as an array formula. We will use the same data ( 1.3(a)) and the names created in Section 1.3. Let’s create counts for following conditions:
2.5. SUMPRODUCT
1. State is OH AND GPA is greater than 2.4 AND Gender is M. We can use these formulae to find this count:
=SUMPRODUCT(--(State="OH"),--(GPA>2.4),--(Gender="M"))
{=SUM((State="OH")*(GPA>2.4)*(Gender="M"))}
2. Enrolled hours are greater than 10 AND Gender is F AND Race is W. We can use these formulae to find this count:
=SUMPRODUCT(--(EnrCrHrs>10),--(Gender="F"),--(Race="W"))
{=SUM((EnrCrHrs>10)*(Gender="F")*(Race="W"))}
You can see in Figure 2.14 that as soon as you type the range name, Excel colors the range, which is stored in a name. See Figure 2.15 for above two formulae. When Excel
Figure 2.15: Example of SUMPRODUCT and SUM for Multiple Conditions
sees an array argument, such as State=“OH”, it evaluates each cell in that range with that condition, and returns an array with TRUE or FALSE values. The double dashes or minus signs (??) are used for coercion to convert the logical values to numbers. For example, if put one minus sign in front of a logical value TRUE, then Excel returns -1, and if put one more minus sign in front of -1, then Excel will return positive 1 (see Figure 2.16.) In our criterion of State=”OH”, Excel returns this array with logical values: {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE}
When we coerce it with one minus sign, Excel returns this array:
{0; 0; -1; -1; 0; -1}
After coercing it with two minus signs, Excel returns this array:
{0; 0; 1; 1; 0; 1}
In our example of State=“OH”, GPA > 2.4, and Gender=“M”, Excel creates three arrays:
{FALSE; FALSE; TRUE; TRUE; FALSE; TRUE} {TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}
{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE}
When using double minus sign in SUMPRODUCT function, Excel first multiplies the arrays with numbers, and then adds them, resulting in a count. In this case, the calculation was like this:
1. Multiply each item in the arrays 0 × 1 × 1 = 0
0 × 0 × 1 = 0
1 × 1 × 0 = 0
1 × 1 × 1 = 1
0 × 0 × 0 = 0
1 × 1 × 0 = 0
2. Add the result of multiplication 0 + 0 + 0 + 1 + 0 + 0 = 1
The array formula using SUM will do exactly the same thing, but there is no need of coercing the arrays, as it automatically converts logical values to numbers. If we wish we can enter a formula like this:
=SUMPRODUCT((State="OH")*(GPA>2.4)*(Gender="M"))
This formula will also return the exact same results; however, it is not proper form providing arguments to this function, and results in slower performance.
Chapter 3
Let’s see some magical things an array formula can do. As mentioned earlier, while writing formulae for counting, range names are very useful. Following examples are with range names. In addition, array formulae should be entered using Ctrl + Shift + Enter. The curly braces surrounding the formula will automatically appear; do not enter formula with the braces. Some of the array formulae are taken from Andrew’s Tips web-site with permission. I would encourage readers to go to his web-site to find more interesting and useful ways of array formulae.
In our range of states, if we want to find out the number of unique values, we can write a formula like this:
{=SUM(1/COUNTIF(State,State))}
The way this formula works is very intriguing; it works in three parts:
1. COUNTIF function counts the number of states matching the criterion. In this case, we are supplying full range as criteria, as a result of that Excel returns an array with the count of the appearance of each row of the range, therefore, we can get this array:
{1;1;3;3;1;3}
For the states: WV, PA, and KY, COUNTIF returns 1, as they appear only once; however, for OH, COUNTIF returns 3.
2. Calculate the division of 1 by the counts. This results in this array:
{1;1;0.3333;0.3333;1;0.3333}
3. Sum this array, which results in 4.
If we had 2 rows of KY, the division would be 1/2 + 1/2, again resulting in one. By dividing 1 by the counts, it creates equal fractions and by summing these fractions we get one for each duplicated row.
15
CHAPTER 3. MAGIC OF ARRAY FORMULAE
To count duplicates we can use a formula like this:
{=SUM(IF(COUNTIF(State,State)>1,1,0))}
In this case, the COUNTIF function will return how many times a state has appeared, but the IF function will return one for counts more than one, else will return zero, therefore, if there are unique rows, the IF function will return zero, else the duplicate rows are counted and summed.
If you would like to find out average of a range excluding zeros, such a formula can be written:
{=AVERAGE(IF(B63:B67>0,B63:B67,""))}
This formula works in three parts:
1. The IF function tests the condition, and creates an array of logical values TRUE or FALSE.
2. If it (the IF function) finds cells with values greater than zero, it returns that cell, else it returns a blank string “”, which the AVERAGE function ignores while calculating.
3. Find an average of the values in that array
For example, consider that data given in Figure 3.1. Let’s assume that these are the number of applicants for each college by year, and we would like to find out the average of applicants for each year, excluding zero applicants for a college.
Note: This logic can be used to construct formulae for finding average (or sum, max) based on different criteria(for Excel 2003 and below, as Excel 2007 has SUMIFS, AVERAGEIF, and AVERAGEIFS.) For example, to find average of GPA of the students, who are not from OH, we can construct a formula like this:
3.3. AVERAGE 17
{=AVERAGE(IF(State<>"OH",GPA,""))}
To find maximum GPA of students, who are not from OH, we can construct a formula like this:
{=MAX(IF(State<>"OH",GPA,0))}
We can add multiple criteria to this formula. For example, if we want to find maximum GPA of students, who are from OH, and are males, we can construct a formula like this:
{=MAX((State="OH")*(Gender="M")*GPA)}
If we want to find minimum GPA of students, who are from OH, and are males, we can construct a formula like this:
{=MIN(IF(State="OH",IF(Gender="F",GPA,""),""))}
If we would like to ignore the errors from a range and find average of that range, we can construct a formula like this:
{=AVERAGE(IF(ISERROR(GPA),"",GPA))}
Suppose we have a list of application sources and number of applicants from each source (see Figure 3.2) and we want to find out average number of applicants from top three sources. To achieve this we can use following formula, where the range of applicants is named as “Applicants”:
{=AVERAGE(LARGE(Applicants,{1,2,3}))}
This formula works in two parts:
1. The LARGE function finds out the n^{th} largest number from a given range. In this case, we have provided an array {1,2,3} to find out the largest, the second largest, and the third largest number from the range. The LARGE function will return an array with top three large numbers. In this example, these numbers are 49,47, and
33.
2. Average function finds out the average of this array, which is 43 in this example.
Let’s say that you want to find average for top eight sources, it would not be preferable to write an array like this {1,2,3,4,5,6,7,8}, therefore, we can use the ROW function to generate an array of continuous numbers. For example, to generate an array of numbers from one to eight this formula can be used:
=ROW(1:8)
However, this formula would not work if you add or delete rows between row number one and eight, therefore, to make it nonvolatile use the INDIRECT function, such as:
=ROW(INDIRECT("1:8"))
CHAPTER 3. MAGIC OF ARRAY FORMULAE
The INDIRECT function translates the text argument to Excel ranges. For example, if cell A1 has the text “Sheet2”, then the formula =INDIRECT(A1 & ‘‘!A1’’) will return the value of the cell A1 from Sheet2. The modified formula for finding average of eight largest sources:
{=AVERAGE(LARGE(Applicants,ROW(INDIRECT("1:8"))))}
Using the same logic, formulae for finding average of n small numbers, or sum of n small or large numbers can be constructed.
Average applicants from bottom three sources:
{=AVERAGE(SMALL(Applicants,ROW(INDIRECT("1:3"))))} Sum of applicants from top three sources:
{=SUM(LARGE(Applicants,ROW(INDIRECT("1:3"))))} Sum of applicants from bottom three sources:
{=SUM(SMALL(Applicants,ROW(INDIRECT("1:3"))))}
Chapter 4
Once a person asked me, “why to use VBA?” I stumbled to give a reply, as VBA is such a neat solution for me to customize Excel, it was ingrained in me, and I never developed a justification for it; however, in retrospect, these are some of the reasons why one should use VBA:
When you have repetitive tasks, such as formatting, file creation, data manipulation
Formulae are too complex to remain explainable. If you write some formulae that you cannot explain to yourself after some time, then it is better that you code them
Customizing Excel for your work environment to increase productivity, such as creating add-ins, creating user-defined functions (UDFs), creating utilities
When it is simply not possible to achieve what you want using Excel’s built-in functions and utilities
A few pages on VBA from the book Excel 2007 Power Programming with VBA by John Walkenbach, pp 135-138 [2].
There are numerous places to put the code, such as modules, worksheets, Thisworkbook, and personal workbook. When you record a macro, Excel asks the location of the macro, whether it is a new workbook, current workbook, or personal workbook. By default, Excel inserts a module in the active workbook and puts the code in that module.
Personal workbook is a common and a hidden workbook for all other workbooks. It is created when you record a macro for the first time. If there are numerous macros that you use regularly, then it is a good idea to store them in the personal workbook. If you store different macros in different workbooks, then accessing them can be an issue; however, if you store them in personal workbook, then the macros can be accessed from any workbook.
Macro recorder is a very good way to learn the objects and its properties, methods, and actions. Although Excel’s macro recorder creates a lot of junk in the code, it is a very
good start to learn about VBA. When you start recording, it records each of your action to VBA equivalent; however, it can only record the actions that are doable using Excel’s interface. For example, if you would like to create a table of contents of your sheets in a workbook, you cannot record such a macro, and you would need to write code for it.
A simple “hello world!” procedure to produce a message box is given in Listing 4.1. Write or copy this program in a worksheet’s VBE. To open the Visual Basic Editor (VBE) press Alt + F11. You can run this procedure three ways:
1. From macro list (press Alt + F8 to bring up the list)
2. From VBE by selecting the procedure and clicking on the play button (or Press F5)
3. Hitting the shortcut key, if one was assigned
Sub HelloWorld ()
MsgBox ”Hello world” , vbOKOnly, ”My First Program”
End Sub
The keyword Sub tells VBA that a procedure is starting, and the keyword End Sub tells VBA that procedure has ended. A function is a procedure, but unlike regular procedures it returns a value, and to start a function, Function keyword is used. The word that follows after Sub or Function keywords is that name of that procedure or function. You can insert a new procedure or a function by using the insert menu from VBE.
MsgBox is VBA’s in-built function, which returns a message box with the input values of prompt, button type, and title. There are few more input arguments of the MsgBox function; however, often these arguments are sufficient. The syntax of MsgBox function is:
MsgBox(prompt[, buttons] [, title] [, helpfile, context])
Often when I receive an Excel file, the columns width is too small to see everything from a cell or the text is wrapped, that’s why I created a small macro or procedure to autofit columns and remove wrapping of text. Listing 4.2 is the code for it.
Sub AutofitColumn () ’Run autofit method for a l l columns ’Columns A to IV for Excel 2003 and below ’Columns A to XFD For 2007 Columns(”A:IV” ). EntireColumn . AutoFit ’ Set the property of wrapping text to false | for | a l l | columns |
4.5. MANUAL CALCULATION
Columns(”A:IV” ). WrapText = False End Sub |
In this example, the procedure name is AutofitColumn. Comments are included in the code to improve readability and explanation; in VBA, comments start with a single quote. EntireColumn is a property of the range, in this case columns, and Autofit is a method of a range, which is specified by row(s) or column(s). Wraptext is also a property of the range object. A range can contain cell(s), row(s), and column(s).
I even created a macro button for this by following the steps listed in Figure 4.1, given in Excel 2002 Power Programming with VBA by John Walkenback, pp. 231 [1].
Figure 4.1: Steps to Create Custom Toolbar Buttons for Macros [1]
If you have many formulae and the calculation is set to automatic, you are bound to face performance issues. With every change in the workbook, Excel will calculate all the formulae. One simple solution would be to set the calculation to manual (Tools ? Options ? Calculation ? Check the manual radio button), and then perform calculations only on the selected range using the code given in Listing 4.3. In Listing 4.3, Calculate is a method for the selected range. This is particularly useful when you are developing big spreadsheet models, and want to test your models. To be really effective you should assign a shortcut key to this macro, to do assign a shortcut key follow these steps:
1. Press Alt + F8 to bring the list of macros
2. Select the macro and Hit Options
3. Assign the shortcut key
Sub ManCalc() Selection . Calculate End Sub |
I like to have footers on every worksheet that I send out with my initials, date, and page number. I have the procedure given in Listing 4.4 to achieve that.
Sub Footer () With ActiveSheet . PageSetup . LeftFooter = ”From the Office of RPIE, ” & Chr(13) & ”&D” . PageSetup . RightFooter = ”ARN” & Chr(13) & ”Page &P of &N” End With End Sub |
With and End With keywords are used to work with an object, so that we do not have to explicitly write that object’s full reference inside the With block.
Chr function is VBA’s in-built function, which returns the character for a given ASCII code. In this example, I have used ASCII character 13 to insert a line.
Activesheet is a property of the active workbook, which represents the active sheet in the active workbook.
PageSetup object holds all the information on page setup attributes of a worksheet.
LeftFooter and RightFooter are properties of PageSetup object, which can either return or set text on the left side or the right side of the worksheet.
For this handout I have used the procedure given in Listing 4.5 to print formulae of the selection to adjacent cells as text, so that we can see values on the left hand side and the formula on the right hand side.
Sub Conv2Text() Dim c As Range For Each c In Selection c . Offset (0 , 1) = Chr(39) & c . Formula Next c End Sub |
1
2
3
4
5
6
In Listing 4.5, line number 2 defines a variable c as a range. To define a variable we use the keyword Dim. Line number 3 starts a For Each loop to iterate through all the cells from the selected range. For Each loop is a special type of loop, which loops through the Collections. The common for loop looks like this:
4.8. USER-DEFINED FUNCTION (UDF): REVERSE STRING
For i = 1 to 10
lines of code .
.
Next i
The left hand side part on line number 4 is the location adjacent to a cell, which is found using the OFFSET function (remember Section 2.4 on page number 11). Here we are telling Excel to set a value of a cell that is one column to the right off the current cell.
The right hand side part of line number 4 is setting the value of the offset cell with the formula of the current cell and adding an apostrophe (Chr(39)=‘) to make it a text value.
This example will show you how to create a user-defined function (UDF). Let’s say that we want to reverse a given string, so if the input is “abcd”, the output would be “dcba.” This function is given in Listing 4.6.
Function ReverseString ( sInputString As String) As String ReverseString = StrReverse( sInputString )
End Function
As mentioned in Section 4.3 on page number 20, a function is a procedure that returns a value. In this example, it is taking an input and returning the output as a string. A function’s input argument are specified in the parenthesis after the function name. In this example, we have used VBA’s in-built function StrReverse, which reverses the input string. Functions that are completely based, such as this one, on other functions are known as “wrappers.”
User-defined functions are used exactly the same as Excel’s in-built functions; however, you need to specify the path, where these functions are created. For example, if your UDFs are stored in the personal workbook, to use them in other workbooks, you would refer them with “!” or “!”, depending on Excel’s version, as a prefix. An example is shown in Figure 4.2.
By the way, Saippuakivikauppias is a Finish word for “soap-stone vendor”, and it is claimed to be the world’s longest palindrome. That reminds me that I have a function given in Listing 4.7, which checks if a word is a palindrome or not.
Function IsPalindrome ( sInput As String) As Boolean
If LCase( sInput ) = StrReverse(LCase( sInput )) Then
IsPalindrome = True
End If
End Function
I use this function almost everyday, as it lets me concatenate a range without inserting & signs or putting a comma in the CONCATENATE function. One repetitive use that I have found is to create OR/AND conditions for Access queries. I copy-paste the field values of a column from Access, do some filtering and my conditions are ready. Then I use this CONCATFUNC to create a string to use in my Access query. Let’s say I want to limit the data from semesters summer 2005, fall 2005, and spring 2006 in Access. To do that I will have to write this ‘‘2005M’’ OR ‘‘2005F’’ OR ‘‘2006S’’ in the criteria part under the semester field in Access. If I have a long list of restrictions, then typing this manually will be very time consuming, and that’s where this function, given in Listing 4.8, is useful. An example is given in Figure 4.3.
Figure 4.3: Example of Concatenate Range Function
Function ConCatFunc(rngRng2bCated As Range , Optional sChar2bAdded As String = ” ,”) As String Dim sOutput As String , c As Range On Error GoTo ConCatFunc Error For Each c In rngRng2bCated sOutput = sOutput & c . Value & sChar2bAdded Next c sOutput = Left(sOutput , Len(sOutput) ? Len(sChar2bAdded )) ConCatFunc = sOutput On Error GoTo 0 Exit Function ConCatFunc Error : ConCatFunc = ”#Error#” |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
4.10. FLIP A RANGE
End Function |
19
This function takes two inputs: the range to be concatenated and the character to be inserted in between each string. Notice on line number 1 of the Listing 4.8 the keyword Optional, which means the user doesn’t need to supply this argument, and by default it will insert a comma between each string.
This function features some minimal error handling. Whenever VBA comes across an error, it raises a flag and generates an error message. A good programming practice is to provide “error handlers”, so that the user is not left clueless. The statement
On Error GoTo ConCatFunc Error tells VBA to go to the label ConCatFunc Error if an error is raised. A label in VBA can be specified by typing the label name and putting a colon immediately after it.
Line number 7 removes the extra character at the end of the output string by using the VBA functions Left and Len. The function Left takes out user-specified number of characters from the input string starting from the left side. Len function counts the number of characters in the input string.
Often, I need to flip a range, as shown in the Figure 4.4, and for that I have a procedure, which is given in Listing 4.9.
(a) Before Flipping (b) After Flipping
Listing 4.9: Procedure to Flip a Range
Sub f l i p () Dim Arr As Variant Dim myrange As Range Dim arRetArr () As Variant , lArrBnd As Long , i As Long On Error GoTo flip Error Set myrange = Range( Selection . Address) Arr = myrange ’ store the selected values in an array ’ check i f there is only one column or not If myrange . Columns . Count = 1 Then lArrBnd = UBound(Arr , 1) ReDim arRetArr (lArrBnd , 0) For i = 0 To lArrBnd ? 1 ’ f l i p the array arRetArr ( i , 0) = Arr(lArrBnd ? i , 1) Next i Range( Selection . Address ) = arRetArr ’ check i f there is only one row or not ElseIf myrange .Rows. Count = 1 Then lArrBnd = UBound(Arr , 2) ReDim arRetArr (0 , lArrBnd) For i = 0 To lArrBnd ? 1 ’ f l i p the array arRetArr (0 , i ) = Arr(1 , lArrBnd ? i ) Next i Range( Selection . Address ) = arRetArr Else MsgBox ”Your selection contains multiple rows or columns . ” & vbCrLf & ”This macro will only work on either one column or one row” , vbCritical , ”Flip Error” End If On Error GoTo 0 SmoothExit flip : Exit Sub flip Error : MsgBox ”Error ” & Err.Number & ” (” & Err. Description & ”) in procedure f l i p ” Resume SmoothExit flip End Sub |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
4.11. FILL BLANK CELLS
This procedure is very helpful when we have blank cells in a range and we would like to fill the blank cells with the values from the non-blank cells above it. I use it when I copy and paste data from Pivot Tables. The procedure listed in Listing 4.10 is from , another good place for Excel and VBA tips and tricks. An example of this procedure is shown in Figure 4.5.
(a) Before Filling the Blanks (b) After Filling the Blanks
Listing 4.10: Procedure to Fill the Blanks
’ taken from http ://www. ozgrid .com/Excel/excel?f i l l ?blank?c e l l s . htmSub FillBlanks () Dim rRange1 As Range , rRange2 As Range Dim iReply As Integer If Selection . Cells . Count = 1 Then MsgBox ”You must select your l i s t and include the blank cells ” , vbInformation , ”OzGrid .com” Exit Sub ElseIf Selection . Columns . Count > 1 Then MsgBox ”You must select only one column” , vbInformation , ”OzGrid .com” Exit Sub End If Set rRange1 = Range( Selection . Cells (1 , 1) , Cells (65536 , Selection .Column ).End(xlUp )) On Error Resume Next Set rRange2 = rRange1 . SpecialCells ( xlCellTypeBlanks ) On Error GoTo 0 If rRange2 Is Nothing Then MsgBox ”No blank cells Found” , vbInformation , ”OzGrid .com” Exit Sub End If rRange2 . FormulaR1C1 = ”=R[?1]C” iReply = MsgBox(”Convert to Values” , vbYesNo + vbQuestion , ”OzGrid .com”) If iReply = vbYes Then rRange1 = rRange1 . Value End Sub |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Chapter 5
1. My blog:
2. ASAP Utilities (A must have!):
3. Discussion board at Mr. : php
4. :
5. Chip Pearson’s Excel page:
6. Daily dose of Excel:
7. John Walkenbach’s spreadsheet page:
8. Tushar Mehta’s Excel page:
9. Books list:
30 CHAPTER 5. RESOURCES
[1] J. Walkenbach. Excel 2002 power programming with VBA. M&T Books, 2001.
[2] J. Walkenbach. Excel 2007 Power Programming with VBA. Wiley; John Wiley distributor, 2007.
32 BIBLIOGRAPHY
Appendix A
Excel Keyboard Shortcuts from: | |||||
Key Alone Shift Ctrl Alt Shift Ctrl | |||||
F1 | Help | What's This Help | Insert Chart Sheet | ||
F2 | Edit Mode | Edit Comment | Save As | ||
F3 | Paste Name Formula | Paste Function | Define Name | Names From Labels | |
F4 | Repeat Action | Find Again | Close Window | Quit Excel | |
F5 | Goto | Find | Restore Window Size | ||
F6 | Next Pane | Prev Pane | Next Workbook | Switch To VBA | Prev Workbook |
F7 | Spell Check | Move Window | |||
F8 | Extend Selection | Add To Selection | Resize Window | Macro List | |
F9 | Calculate All | Calculate Worksheet | Minimize Workbook | ||
F10 | Activate Menu | Context Menu | Restore Workbook | ||
F11 | New Chart | New Worksheet | New Macro Sheet | VB Editor | |
F12 | Save As | Save | Open | | |
A | Select All | Formula Arguments | |||
B | Bold | ||||
C | Copy | ||||
D | Fill Down | Data Menu | |||
E | Edit Menu | ||||
F | Find | File Menu | Font Name | ||
G | Goto | ||||
H | Replace | Help Menu | |||
I | Italics | Insert Menu | |||
J | |||||
K | Insert Hyperlink | ||||
L | |||||
M | |||||
N | New Workbook | ||||
O | Open Workbook | Format Menu | Select Comments | ||
P | | Font Size | |||
Q | |||||
R | Fill Right | ||||
S | Save | ||||
T | Tools Menu | ||||
U | Underline | ||||
V | Paste | ||||
W | Close Workbook | Window Menu |
Excel Keyboard Shortcuts from: | |||||
Key Alone Shift Ctrl Alt Shift Ctrl | |||||
X | Cut | ||||
Y | Repeat Active | ||||
Z | Undo | ||||
` (~) | Toggle Formula View | General Format | |||
1 (!) | Cell Format | Number Format | |||
2 (@) | Toggle Bold | Time Format | |||
3 (#) | Toggle Italics | Date Format | |||
4 ($) | Toggle Underline | Currency Format | |||
5 (%) | Toggle Strikethru | Percent Format | |||
6 (^) | a | Exponent Format | |||
7 (&) | a | Apply Border | |||
8 (*) | Outline | Select Region | |||
9 (() | Hide Rows | Unhide Rows | |||
0 ()) | Hide Columns | Unhide Columns | |||
- | Delete Selection | Control Menu | No Border | ||
= (+) | Formula | Auto Sum | Insert dialog | ||
[ | Direct Dependents | Direct Precedents | |||
] | All Dependents | All Precedents | |||
; (semicolon) | Insert Date | Select Visible Cells | Insert Time | ||
' (apostrophe) | Style | Copy Cell Value Above | |||
: (colon) | Insert Time | ||||
/ | Select Array | Select Array | |||
\ | Select Differences | Select Unequal Cells | |||
Insert | Insert Mode | Copy | |||
Delete | Clear | Delete To End Of Line | |||
Home | Begin Row | Start Of Worksheet | |||
End | End Row | End Of Worksheet | |||
Page Up | Page Up | Previous Worksheet | Left 1 screen | ||
Page Down | Page Down | Next Worksheet | Right 1 screen | ||
Left Arrow | Move Left | Select Left | Move Left Area | ||
Right Arrow | Move Right | Select Right | Move Right Area | ||
Up Arrow | Move Up | Select Up | Move Up Area | ||
Down Arrow | Move Down | Select Down | Move Down Area | Drop down list | |
Space Bar | Space | Select Row | Select Column | Control Box | Select All |
Tab | Move Right | Move Left | Next Window | Next Application | Previous Window |
BackSpace | Goto Active Cell |