Text, Information, Lookup and Reference Functions
30 Excel Functions in 30 Days
Debra Dalgleish www.contextures.com
30 Excel Functions in 30 Days: Text, Information, Lookup and Reference Copyright Š 2011 by Debra Dalgleish, Contextures Inc., www.contextures.com All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The information in this book is distributed on an "as is" basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Contextures Inc. shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. Version 1.00 – August 26, 2011
www.contextures.com
Table of Contents Table of Contents .......................................................................................................................................... 1 Introduction ................................................................................................................................................... 1 Function 01: EXACT .................................................................................................................................... 2 Function 02: AREAS .................................................................................................................................. 12 Function 03: TRIM ..................................................................................................................................... 17 Function 04: INFO ...................................................................................................................................... 21 Function 05: CHOOSE ............................................................................................................................... 26 Function 06: FIXED ................................................................................................................................... 31 Function 07: CODE .................................................................................................................................... 37 Function 08: CHAR .................................................................................................................................... 40 Function 09: VLOOKUP ............................................................................................................................ 47 Function 10: HLOOKUP ............................................................................................................................ 53 Function 11: CELL ..................................................................................................................................... 57 Function 12: COLUMNS ............................................................................................................................ 61 Function 13: TRANSPOSE ........................................................................................................................ 65 Function 14: T ............................................................................................................................................. 71 Function 15: REPT ..................................................................................................................................... 73 Function 16: LOOKUP ............................................................................................................................... 78 Function 17: ERROR.TYPE ....................................................................................................................... 82 Function 18: SEARCH ............................................................................................................................... 86 Function 19: MATCH ................................................................................................................................. 91 Function 20: ADDRESS ............................................................................................................................. 96 Function 21: TYPE ................................................................................................................................... 102 Function 22: N .......................................................................................................................................... 106 Function 23: FIND .................................................................................................................................... 110 Function 24: INDEX ................................................................................................................................. 114 Function 25: REPLACE ........................................................................................................................... 123 Function 26: OFFSET ............................................................................................................................... 126 Function 27: SUBSTITUTE ..................................................................................................................... 131 Function 28: HYPERLINK....................................................................................................................... 137 Function 29: CLEAN ................................................................................................................................ 141 Function 30: INDIRECT........................................................................................................................... 144 What Next? ............................................................................................................................................... 150 Functions Index......................................................................................................................................... 151
www.contextures.com
Introduction
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 1
This guide will help you get started with Excel functions, from the Text, Information and Lookup and Reference categories. You'll see the arguments required for each function, learn tips and pitfalls, and work through examples. To improve your skills, find an applicable situation to use each function in your own workbooks. For extra brain-sticking power, teach a friend or co-worker how to use each function. When you explain it to someone else, you'll understand it better yourself.
Links 1. If you're reading this guide on your computer, you can click the links to read more about a topic on the Contextures website. The home page is http://www.contextures.com 2. The tutorial videos are uploaded to the Contextures channel on YouTube, and you can click the video links in this guide, to view the videos online.
Sample Files 3. The zipped folder that contains this Lesson Guide also has folders with Excel files, for the lessons. You can create and save your own files as you work through this course, or use the sample files provided for each lesson.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 2
Function 01: EXACT
The EXACT function can check for an exact match between text strings, including upper and lower case. Formatting does not affect the result. If the text strings are exactly the same, the function result is TRUE; if they're not exactly the same, the result is FALSE.
How Could You Use EXACT? Besides checking two cells to see if their contents match exactly, you can use the EXACT function to do the following: • Use with data validation to block changes to a cell • Force upper case entries in a data validation cell • Check for an exact match in a list of codes • Find an exact match in a lookup table • Count exact matches in a list • Pinpoint the differences between 2 cells
EXACT Syntax The EXACT function has the following syntax: EXACT(text1,text2) • Text1 is the first text string. • Text2 is the second text string. You can enter the arguments as cell references or text strings. In Excel 2007, the maximum string length for EXACT is 32767 characters.
www.contextures.com
EXACT Traps
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 3
In Excel 2007 Help, there is the following statement in the Remarks section for the EXACT function. "You can also use the double equals (==) comparison operator instead of the EXACT function to make exact comparisons. For example, =A1==B1 returns the same value as =EXACT(A1,B1)." This is incorrect. There is no "double equals" operator in Excel, and this remark has been removed in the Excel 2010 Help.
Example 1: Test a Password You can use the EXACT function to see if someone has entered the correct password in an Excel workbook. 1. On a sheet named "AdminData", enter a secret password in cell C2, and name that cell "pwd". Use a mixture of upper and lower case in the password.
On another sheet, users will enter the password, and you'll use the EXACT function to compare their entry to the contents of the "pwd" cell. 2. On the Ex01 sheet, the user will type a password in cell C3. For this test, enter the correct password, but make the last letter lower case, instead of upper case.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 4
First, see what happens if you use the Equal Sign to compare the two cells. 3. In cell C5, enter this formula: = C3=pwd and the result will be TRUE, as long as the values are the same – it doesn't matter if the letters are upper or lower case.
Next, see what happens if you use the EXACT function to compare the two cells. 4. In cell C6, the EXACT function compares C3 to the pwd cell: =EXACT (C3,pwd) • If the contents of the two cells are identical, including upper and lower case, cell C6 will show TRUE as the result. Note: Formatting differences, such as bold font, are ignored.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 5
• If there is a difference in the contents – if even one letter is a different case, the result in C6 will be FALSE.
Finally, you could combine the EXACT function with the IF function, to show a message if the incorrect password is entered. 5. In cell C8, enter this formula: =IF(C3="","",IF(EXACT(C3,pwd),"Correct","Incorrect. Try again.")) • If cell C3 is empty ("") then no message will be shown in cell C8. • If cell C3 is an EXACT match for the pwd cell, then cell C8 will show the message, "Correct". • If cell C3 is NOT an EXACT match for the pwd cell, then cell C8 will show the message, "Incorrect. Try again."
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 6
Example 2: Allow Changes to a Cell
After the user enters the correct password, you could allow changes to specific cells in the worksheet. In this example, users can change the Daily Rate cell (C5), if they enter the correct password in cell C3. We also want to limit the values they can type in the Daily Rate cell. The rate must be greater than 0% and less than 10%. With a custom data validation formula, you can combine the password check, and the rate value limits. The AND function is used, to make sure that both conditions are met. 1. Select cell C5, and on the Ribbon's Data tab, click Data Validation 2. Under Allow, select Custom 3. Enter this formula, then click OK. =AND(EXACT(C3,pwd),C5>0,C5<0.1)
To test the data validation, enter a password, then try to type a number that is higher than 10%. You'll see an error message, and will have to Cancel or Retry.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 7
Example 3: Force Upper Case Entries
You could also use the EXACT function in data validation to ensure that all upper case letters are typed in a cell. For example, a Canadian postal code is a set format, with alternating numbers, and upper case letters, e.g., L9L 9L9. The UPPER function converts all text to upper case, so you can use it to compare the cell contents with the upper case cell contents. In cell C2, data validation has been applied, with the formula: =EXACT(C2,UPPER(C2))
If any lower case letters are entered in cell C2, an error alert will appear. This won't prevent all errors in the postal code, but will ensure that only upper case letters are used.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 8
Example 4: Find an Exact Match in a List
Instead of simply comparing one cell to another, you might need to look for an exact match in a list of values. If someone types a product code in a cell, is that exact product code in your product list? In this example, there is a product code list in cells B2:B5, and a customer can order a product, by typing its code in cell E2.
The formula in cell F2 uses the EXACT function to check the product code typed in cell E2, and see if there's an exact match in the list of product codes. The OR function checks if any one of the cells – B2 OR B3 OR B4 OR B5 – is an EXACT match for the entry in cell E2. IN cell F2, type this formula, and press Ctrl+Shift+Enter, to array-enter the formula. =OR(EXACT($B$2:$B$5,E2)) NOTE: The curly brackets will be automatically added, to show that it is an array formula – do not type the curly brackets yourself. {=OR(EXACT($B$2:$B$5,E2))}
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 9
Example 5: Pull a Name for an Exact Match in a List In a lookup table, the EXACT function can distinguish between AA1 and Aa1, and help return the correct product name for each code. Other functions, like VLOOKUP, would treat AA1 and Aa1as identical, and return the product name for the first matching code it encounters in the table, regardless of case. In this example, there is a password list in cells B2:B5, and you can find the user name, by typing a password in cell D2.
The formula in cell F4 uses 3 functions – INDEX, MATCH and EXACT: • EXACT function checks the code typed in cell E4, to see if there's an exact match in the list of passwords. Result is TRUE, if the password is found. • MATCH function returns the table row number of the TRUE result, and bG8943TO is found in the 2nd row of the range ($C$4:$C$7). • INDEX function returns the value in the 2nd row of range $B$4:$B$7 – Sam. NOTE: The formula is array entered, by pressing Ctrl+Shift+Enter {=INDEX($B$4:$B$7,MATCH(TRUE,EXACT($C$4:$C$7,E4),0))}
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 10
Example 6: Count the Exact Matches in a List
In a lookup table, the EXACT function can distinguish between AA1 and Aa1, and help return the correct count for each code. Other functions, like COUNTIF, would treat AA1 and Aa1as identical, and return the count for all variations of the code. There is an Item list in cells B3:B12, and a list of unique items in column D.
The formula in column E uses 2 functions – SUMPRODUCT and EXACT: • The EXACT function checks the item typed in column C, to see if there's an exact match in the list of items. • The SUMPRODUCT function returns the total count, based on the number of TRUE results. • The two minus signs (double unary) in front of the EXACT function convert the TRUE and FALSE values to 1 and 0 values. Enter this formula in cell E2, and copy down to cell E7: =SUMPRODUCT(--EXACT($B$3:$B$12,D3)) To see how the formula works, select the part of the formula within the SUMPRODUCT brackets, and then press the F9 key on your keyboard. The inner formula is calculated, and shows zeros and ones. NOTE: Press the Esc key to exit the formula, without saving the changes.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 11
Example 7: Check Each Character in a Cell
The EXACT function can tell you if two text strings match exactly, but you might want a quick way to see which characters are not identical. In this example, each string has 6 characters, and the numbers 1 to 6 are entered as column headings.
The formula in cell D3 combines two functions – MID and EXACT: • The MID function returns a specific character from column A or column B, based on the number in the formula's column heading. For example, in the formula shown above, for D3, the first character in each string is tested, because the number in cell D2 is 1. • The EXACT function compares the two characters extracted by the MID functions. Enter this formula in cell D3 and copy across to cell I3: =EXACT(MID($B3,D$2,1),MID($C3,D$2,1)) With the $ sign, the formula uses an absolute reference to columns $B and $C, and to row $2. If the formula is copied across or down, those references won't change. There is no $ sign on the reference to column D, so it is a relative reference, and will change when you copy the formula across to column I.
Watch the EXACT Video To see a demonstration of the EXACT function examples, you can watch this video tutorial. YouTube link: Compare Cells With Excel EXACT Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 12
Function 02: AREAS
The AREAS function returns the number of areas in a reference -- an area is a range of contiguous cells or a single cell. The cells can be empty, or contain data – that has no effect on the count.
How Could You Use AREAS? The AREAS function doesn't have many real-world uses, but it's an interesting example of how the reference operators work. You can use the AREAS function to do the following: • Count the number of areas in a range • Count the number of intersections for multiple ranges • Calculate an area number for an INDEX function
AREAS Syntax The AREAS function has the following syntax: AREAS(reference) • The reference argument can be a single cell or range, or can refer to multiple areas.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 13
Reference Operators
When entering references, you can use any of the 3 reference operators: Operator
Example
Type
Use
:
colon
A1:B4
Range
all cells between, and including, the two references
,
comma
A1, B2
Union
combine multiple references into one
space
A1 B3
Intersection
cells common to the references
AREAS Traps If you are using a comma in the AREAS function, to refer to multiple ranges or cells, add another set of parentheses. =AREAS((F2,G2:H2)) Otherwise, the comma will be interpreted as a field separator, and you'll get a "too many arguments" error.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 14
Example 1: Count the Areas in a Range
You can use the AREAS function with a simple range reference, and the count will be 1. Even though there are 2 cells in the range, the cells are contiguous, so they count as a single area. =AREAS(G2:H2)
Example 2: Count the Areas in Multiple References You can use the AREAS function with multiple references, to get a total count of areas. Because a comma is used as the union operator, you'll need to add an extra set of parentheses in the formula. =AREAS((F2,G2:H2)) The two ranges in the reference are adjacent, but are listed separately in the formulas, and counted as separate areas, so the formula result is 2.
Example 3: Count the Areas in Overlapping References Even if the references overlap, or one reference is completely within another, when using the comma as the union operator, each area will be counted separately. =AREAS((F2,F2:H2))
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 15
In this example, the two references overlap, and F2 is completely within the F2:H2 range, but they are counted as separate areas, so the formula result is 2.
Example 4: Count the Areas in Intersecting References When you use the space character to create an intersection from the references, the intersection areas will be counted. =AREAS(TESTREF01 TESTREF02) The named range TESTREF01 is coloured blue and TESTREF02 is coloured purple. These ranges intersect at three points, outlined with bold borders, so the formula result is 3.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 16
Example 5: Calculate the Area Number for INDEX The INDEX function, in Reference form, can use area number as its final argument. INDEX(reference,row_num,column_num,area_num) This example, based on an Excel newsgroup post by Leo Heuser, refers to a noncontiguous named range – TestBlock. In the INDEX formula, TestBlock is the reference, and the AREAS function calculates the number of areas in the TestBlock range. To get the value from TestBlock, row 5, column 1, last area, use this formula: =INDEX(TestBlock,5,1,AREAS(TestBlock)) The last area is Day04, and the 5th value in Day04 is H05, which is the formula result.
Watch the AREAS Video To see a demonstration of the AREAS function examples, you can watch this video tutorial. YouTube link: Count Areas With Excel AREAS Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 17
Function 03: TRIM
The TRIM function removes all the spaces in a text string, except for single spaces between words.
How Could You Use TRIM? The TRIM function can help with the cleanup of text that you've downloaded from a website, or imported from another application. The TRIM function: • Removes spaces from start and end of text string • Removes all except single spaces between words • Does NOT remove some space characters copied from websites
TRIM Syntax The TRIM function has the following syntax: TRIM(text) • text is a cell reference or text string from which you want spaces removed.
TRIM Traps The TRIM function only removes standard space characters from the text. If you copy text from a website, it might contain special non-breaking space characters, and the TRIM function will not remove those.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 18
Example 1: Removes spaces from start and end of text string You can use the TRIM function to remove all the space characters at the start and end of a text string. In the screenshot below, there are 2 extra spaces at the start and 2 at the end of the text in cell C5. The LEN function is used in column E, to calculate the number of characters in each cell. This formula in cell E3 shows that there are 13 characters in cell C3. =LEN(C3) The TRIM function in cell C7 removes those 4 spaces from the text in cell C5. =TRIM(C5)
The LEN formula in cell E7 now counts 13 characters â&#x20AC;&#x201C; the 4 space characters added in cell C5 have been trimmed.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 19
Example 2: Removes all except single spaces between words You can use the TRIM function to extra space characters between words in a text string. In the screenshot below, there are 3 extra spaces between the words in cell C5, as well as 2 spaces at the start and 2 spaces at the end of the text string. You can see the character counts in column E, calculated with the LEN function. The TRIM function in cell C7 removes those extra spaces between the words, as well as the 2 spaces at the start and 2 spaces at the end of the text string. The character count is reduced to 13. =TRIM(C5)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 20
Example 3: Does NOT remove some space characters The TRIM function does NOT remove some space characters, such as a non-breaking space copied from a website. In the screenshot below, Cell C5 contains one non-breaking space, at the start of the text string, and that character is not trimmed. =TRIM(C5)
Alternatives When the TRIM function doesn't work, you can try other methods for cleaning the data: • manually delete the non-breaking space character • use the SUBSTITUTE function • record or write a macro.
Watch the TRIM Video To see a demonstration of the TRIM function examples, you can watch this video tutorial. YouTube link: Remove Spaces With Excel TRIM Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 21
Function 04: INFO
The INFO function shows information about the current operating environment.
How Could You Use INFO? The INFO function can show information about the Excel application, such as: • Microsoft Excel version • Number of active worksheets • Current recalculation mode In previous versions of Excel you could also get memory information ("memavail", "memused", and "totmem"), but those type_text items are no longer supported.
INFO Syntax The INFO function has the following syntax: INFO(type_text) • type_text is one of the following items, which specify what information you want. o "directory" Path of current directory o "numfile" Number of active worksheets in open workbooks. o "origin" Absolute cell reference of top left visible o "osversion" Current operating system version, as text. o "recalc" Current recalculation mode; "Automatic" or "Manual". o "release" Microsoft Excel version, as text. o "system" Name of the operating environment: "pcdos" or "mac"
www.contextures.com
INFO Traps
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 22
In Excel's Help file, there is a warning that you should use the INFO function with caution, because it could reveal confidential information to other users. For example, you might not want other people to see the file path that your Excel workbook is in. If you're sending an Excel file to someone else, be sure to remove any data that you don't want to share!
Example 1: Microsoft Excel version With the "release" value, you can use the INFO function to show what version of Excel is being used. The result is text, not a number. In the screenshot below, Excel 2010 is being used, so the version number is 14.0. =INFO("release")
You could use the result to display a message, based on version number. This formula, in cell C4, uses IF, to test the result of the INFO function in cell C2 =IF(C2+0<14,"Time to upgrade","Latest version")
A zero is added to the INFO function result, to change the text value into a number. Then, that number can be tested, to see if it's less than 14 (the latest version of Excel). C2+0<14
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 23
Example 2: Number of active worksheets
With the "numfile" type_text value, the INFO function can show the number of active worksheets in all open workbooks. This number includes hidden sheets, sheets in hidden workbooks, and sheets in add-ins. In this example, the PivotPower add-in is running, and it has two worksheets, and the visible workbook has five worksheets. You can see all the open projects, by looking in the Visual Basic Explorer (press Alt + F11 to open the VBE). The total sheets returned by the INFO function is seven. =INFO("numfile")
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 24
Example 3: Current recalculation mode
Instead of typing the type_text value as a string in the INFO function, you can refer to a cell that contains one of the valid values. In this example, there is a range named TypeText, which contains a list of the type_text items for the INFO function.
In cell B3, there is a data validation drop down list, based on the TypeText named range.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 25
In cell C3, the INFO function returns the result for the type_text that is selected in B3. =INFO(B3)
When "recalc" is selected, the result shows that the current recalculation mode is Automatic.
Watch the INFO Video To see a demonstration of the INFO function examples, you can watch this video tutorial. YouTube link: Get Excel Version Number with INFO Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 26
Function 05: CHOOSE
The CHOOSE function, from the Lookup and Reference category, picks from a list of options, based on an index number. CHOOSE is one of the few functions that can return an actual reference, which makes it very useful.
How Could You Use CHOOSE? The CHOOSE function can return a specific numbered item from a list, such as: • For month number, return the fiscal quarter number • Based on starting date, calculate the next Monday • For store number, show a sum of sales
CHOOSE Syntax The CHOOSE function has the following syntax: • CHOOSE(index_num,value1,value2,…) o index_number must be between 1 and 254 (or 29 in Excel 2003 and earlier) o index_number can be typed in the function, or can be a formula or cell reference o index_number fractions will be truncated to the lowest integer, before using o value arguments can be numbers, cell references, defined names, formulas, functions, or text
CHOOSE Traps • In Excel 2003, and earlier versions, the CHOOSE function is limited to numbers between 1 and 29.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 27
• Lookup lists are usually easier to manage on a worksheet, instead of having them typed in a formula. With VLOOKUP or MATCH functions, you can refer to a worksheet list.
Example 1: Fiscal Quarter for Month Number The CHOOSE function works well with a simple list of numbers as the values. For example, if cell B2 contains a month number, a CHOOSE formula can calculate the fiscal quarter for that month. In this example, the fiscal year starts in July, so months 7, 8 and 9 are in quarter 1. In the table below, you can see the fiscal quarter below each month number, in the orange cells. This table isn't used in the CHOOSE function – it's just a visual reference for how the fiscal quarters are set up.
For the CHOOSE function, the first argument is a reference to cell C2, where you'll type a month number. Next, you'll enter the quarter number for each month, in the order that they appear in the table above. =CHOOSE(C2,3,3,3,4,4,4,1,1,1,2,2,2) Enter a month number in cell C2, and the Fiscal Quarter is calculated by the CHOOSE function, in cell C3. If the month number in cell C2 is 8 (August), then the fiscal quarter is 1.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 28
Example 2: Calculate Date of Next Monday
The CHOOSE function can be combined with other functions, like WEEKDAY, to calculate upcoming dates. For example, if you're in a club that meets every Monday night, you can find next Monday's date, based on today's date. In column F, in the table below, you can see the weekday number for each day. In column H, you can see the number of days you would add to each weekday, to get to the next Monday. For example, on Sunday, you would need to add 1 day. If it's Monday, there are 7 days till next Monday, and so on. This table is not used in the CHOOSE function – it's for visual reference only. The numbers in column H are what you'll enter as values in the CHOOSE function.
With the current date in cell C2, the formula in cell C3 uses the WEEKDAY and CHOOSE functions to calculate the next Monday. =C2+CHOOSE(WEEKDAY(C2),1,7,6,5,4,3,2)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 29
Example 3: Show Sales Total for Selected Store You can use CHOOSE in formulas with other functions too, like SUM. In this example, we can get a total sales for a specific store, by entering its number in the CHOOSE function, and list the ranges to total for each store. In this example, the store number (101, 102 or 103) is entered in cell C2. To get the index_number value, as 1, 2 or 3, instead of 101, 102, or 103, you can use a formula: C2100. The sales numbers for each store are in a separate column, as show below.
Inside the SUM function, the CHOOSE function will be evaluated first, and returns the correct range for the SUM, for the selected store. The stores are numbered in sequence, starting from 100, so we subtract 100 from the store number, to get its position in the table. =SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))
This is example of a situation where other functions, like INDEX and MATCH, would be more efficient.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 30
Watch the CHOOSE Video
To see a demonstration of the examples in the CHOOSE function sample workbook, you can watch this video tutorial. YouTube link: Select Item in List With Excel CHOOSE Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 31
Function 06: FIXED
The FIXED function, from the Text category, rounds a number to a specified number of decimals, with or without comma separators, and returns the formatted result as text. There aren't too many uses for the FIXED function, as you can see in the examples shown below.
How Could You Use FIXED? The FIXED function can change numbers to text, formatted with a set number of decimals. This could help in limited situations, such as: • Matching numbers imported as text from another application • formatting numbers in a text string
FIXED Syntax The FIXED function has the following syntax: • FIXED(number,decimals,no_commas) o Number is the number you want to round and convert to text. o Decimals is the number of digits to the right of the decimal point. o If omitted, decimals defaults to 2 o If negative, decimals round to the left of decimal point o If no_commas is FALSE or omitted, the result includes commas as usual o If no_commas is TRUE, commas are not included in the result.
FIXED Traps The FIXED function changes a number to text, so don't use it anywhere that you want to keep numbers as numbers. For example, if you want to limit the number of decimal places for a chart's data, use number formatting in the cells, or use the ROUND function instead.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 32
Example 1: Format Rounded Number
The FIXED function lets you take a number, round it to a specific number of decimals, add commas, and return the result as text. For example, with the number 32187.5689231 in cell C2, you could use the FIXED function to round it to 2 decimal places, and include a comma. =FIXED(C2,2,FALSE)
Instead of using FIXED, you could simply format the cell as Number format, with 2 decimal places, and a comma. That would leave the cell value unchanged â&#x20AC;&#x201D; only its appearance would be different.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 33
Or, you could use the ROUND function, combined with cell formatting, to return a number that is rounded to two decimal places, and has a comma separator. =ROUND(C2,2)
Finally, if you really want the result as text, use the TEXT function. Its formatting options are much more flexible, and if you need rounding to the left, you can combine it with the ROUND function. =TEXT(C2,"#,###.00″)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 34
Example 2: Round Number to the Left
With the FIXED function, you can also round to the left of the decimal point, by using a negative number for the decimal places argument. To remove commas, use TRUE in the no_commas argument. Again, the result is text. =FIXED(C2,-2,TRUE)
Or, you could use the ROUND function, to return a number that is rounded to the left. =ROUND(C2,-2)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 35
Example 3: Show Number as Thousands of Dollars The FIXED function has limited formatting options, and only lets you control the number of decimal places, and the commas. You could use FIXED to round a number to the thousands, but can't add a dollar sign, or hide the zeros. =FIXED(C2,-3)
Instead of using FIXED, you could format the cell with a Custom Number format of $#, which would leave the cell value unchanged â&#x20AC;&#x201D; only its appearance would be different.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 36
Or, for a text result, use the TEXT function, which lets you format the number as thousands of dollars. =TEXT(C3,"$#,")
Watch the FIXED Video To see a demonstration of the examples in the FIXED function sample workbook, you can watch this video tutorial. YouTube link: Change Numbers to Text With Excel FIXED Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 37
Function 07: CODE
The CODE function returns a numeric code for the first character in a text string. For Windows, the returned code is from the ANSI character set, and for Macintosh, the code is from the Macintosh character set.
How Could You Use CODE? The CODE function can help unravel mysteries in your data, such as: • What hidden character is at the end of imported text? • How can I type a special symbol in a cell? The CODE function can work with other functions, in long, complicated formulas, but we'll focus on what it can do on its own, and in simple formulas.
CODE Syntax The CODE function has the following syntax: • CODE(text) o text is the text string from which you want the first character's code
CODE Traps Results could be different if you switch to a different operating system. The codes for the ASCII character set (codes 32 to 126) are consistent, and most can be found on your keyboard. However, the characters for the higher numbers (129 to 254) may vary, as you can see in the comparison charts shown here: Differences between ANSI, ISO-8859-1 and MacRoman character sets For example, the ANSI code 189 is ½ and for the Macintosh it is Ω
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 38
Example 1: Get Hidden Character's Code
When you copy text from a website, it might include hidden characters. The CODE function can be used to identify what those hidden characters are. For example, there is a text string in cell B3, and only "test" is visible — 4 characters. In cell C3, the LEN function shows that there are 5 characters in cell B3. To identify the last character's code, you can use the RIGHT function, to return the last character. Then, use the CODE function to return the code for that character. =CODE(RIGHT(B3,1))
In cell D3, the RIGHT/CODE formula shows that the last character has the code 160, which is a non-breaking space used on websites.
Example 2: Find a Symbol's Code To insert special characters in an Excel worksheet, you can use the Symbol command on the Ribbon's Insert tab. For example, you can insert a degree symbol ° or a copyright symbol ©. After you insert a symbol, you can determine its code, by using the CODE function =IF(C3="","",CODE(C3)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 39
Use the Code to Insert a Symbol
Once you know the code, you can use the numeric keypad (not the regular numbers) to insert the symbol. The code for the copyright symbol is 169. Follow these steps to enter that symbol in a cell. On a keyboard with a numeric keypad
1. On the keyboard, press the Alt key 2. On the numeric keypad, type the code as a 4-digit number (add leading zeros if necessary): 0169 3. Press Enter, to see the copyright symbol in the cell. On a keyboard with no numeric keypad
On your laptop, you might need to press special keys to use the numeric keypad function. Check the owner's manual, for directions. Here's what works on my Dell laptop. 1. Press the Fn key, and the F4 key, to enable NumLock 2. Locate the numeric keypad within the letters on the keyboard. On my keyboard, J=1, K=2, etc. 3. Press the Alt key, and the Fn key, and using the numeric keypad, type the code as a 4-digit number (add leading zeros if necessary): 0169 4. Press Enter, to see the copyright symbol in the cell. 5. When finished, press the Fn key, and the F4 key, to disable NumLock
Watch the CODE Video To see a demonstration of the examples in the CODE function sample workbook, you can watch this video tutorial. YouTube link: Get Character Number With Excel CODE Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 40
Function 08: CHAR
The CHAR function returns a specified character, for the number entered, based on the character set used by your computer. (For Windows, the ANSI character set, and for Macintosh, the Macintosh character set.) It's the opposite of the CODE function, which returns the code for a specific character.
How Could You Use CHAR? The CHAR function can help enter special symbols or specific characters, such as: • • • •
Add a line break in a formula Find a specific letter in the alphabet List all the letters of the alphabet Create a reference table of character codes
CHAR Syntax The CHAR function has the following syntax: • CHAR(number) o a number between 1 and 255, specifying the character you want returned. The character is from the character set used by your computer
CHAR Traps Just like the CODE function, CHAR results could be different if you switch to a different operating system. The codes for the ASCII character set (codes 32 to 126) are consistent, and most can be found on your keyboard. However, the characters for the higher numbers (129 to 254) may vary, as you can see in the comparison charts shown here: Differences between ANSI, ISO-8859-1 and MacRoman character sets
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 41
Example 1: Add a Line Break
To enter a line break in a cell, you can press Alt + Enter. When combining text strings in a formula, you can use CHAR(10) — the line break character. =C2 & CHAR(10) & C3
After you create the formula, format the cell with Wrap Text, to see the line break, instead of that strange symbol between the strings.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 42
Example 2: Find a Letter in the Alphabet
Quick! What's the 19th letter of the alphabet? With the CHAR function, you can quickly figure that out, without singing the alphabet song in your head. The first step is to find the CODE for the first letter of the alphabet — A. You can type A in a cell, and refer to it, or type A as a text string in the function. =CODE("A") or =CODE(B1)
The CODE for the first letter in the alphabet is 65, which is 1 + 64. So, if you add 64 to a letter number, you'll get the CODE for that letter. Then you can use the CHAR function to find any other letter in the alphabet, by adding 64 to the requested number. =CHAR(B5+64)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 43
Example 3: List the Entire Alphabet
With Excel's AutoFill feature, you can type two numbers, and quickly create an entire series of numbers. For example, 1. Type 1 in cell A2 and type 2 in cell B2. 2. Select cells A2:B2, and point to the fill handle, at the bottom right of cell B2 3. Drag across to cell Z2, to create a series of numbers, from 1 to 26.
Unfortunately, that AutoFill technique doesn't work with letters (unless you create a Custom List, as described below.) However, you can use the CHAR function to create a list with the entire alphabet. In cell A3, enter this formula, then copy it across to cell Z3. =CHAR(A2 + 64)
www.contextures.com
Create a Custom List
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 44
If you want to enter the alphabet with the AutoFill feature, you can import your alphabet list into the Custom Lists feature. 1. Copy the cells with the alphabet list, and paste in the row below, as Values 2. Select the cells with the alphabet values (not the row with the CHAR formulas) 3. Click the Office button on the Excel Ribbon, then click Excel Options (In Excel 2010, click the File tab, then click Options) 4. In the list of Categories, click Popular (In Excel 2010, click Advanced) 5. Click the Edit Custom Lists button 6. Click the Import button, then click OK, and close Excel Options.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 45
Example 4: Create a Character Reference Chart You can create a reference chart with all the printable characters, by using the CHAR function. This chart will be a quick guide if you want to enter a symbol by typing (press Alt, and type the 4-digit code on the numeric keypad). 1. On a worksheet, type the numbers from 32 to 255 (use the AutoFill feature) 2. In the adjacent column, use the CHAR function to show the character for each code. To see the characters for a different font, like Symbol or Wingdings, change the font in the CHAR function column.
In the sample workbook, the CHAR function cells are in a range named CHAR_List. You can select that named range from the Name Box drop down list, then change the font, to see a different CHAR set.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 46
Watch the CHAR Video
To see a demonstration of the examples in the CHAR function sample workbook, you can watch this video tutorial. YouTube link: Get Characters With Excel CHAR Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 47
Function 09: VLOOKUP
The VLOOKUP function looks for a value in the first column in a table, and returns another value from the same row in that table. Other functions might do a better job of pulling data from a table (see VLOOKUP traps section below), but VLOOKUP is the lookup function that people try first. Some people get the hang of it right away, and others struggle to make it work. Yes, this function has some flaws, but once you understand how it works, you'll be ready to move on to some of the other lookup options.
How Could You Use VLOOKUP? The VLOOKUP function can find exact matches in the lookup column, or the closest match, so it can: • Find the price of a selected product • Convert student percentages to letter grades
VLOOKUP Syntax The VLOOKUP function has the following syntax: • VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) o lookup_value: the value that you want to look for — it can be a value, or a cell reference. o table_array: the lookup table — this can be a range reference or a range name, with 2 or more columns. o col_index_num: the column that has the value you want returned, based on the column number within the table. o [range_lookup]: for an exact match, use FALSE or 0; for an approximate match, use TRUE or 1, with the lookup value column sorted in ascending order..
www.contextures.com
VLOOKUP Traps
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 48
VLOOKUP can be slow, especially when doing a text string match, in an unsorted table, where an exact match is requested. Wherever possible, use a table that is sorted by the first column, in ascending order, and use an approximate match. You can use MATCH or COUNTIF to check for the value first, to make sure it is in the table's first column (see example 3 below). Other functions, such as INDEX and MATCH, can be used to return values from a table, and are more efficient.
Example 1: Find the Price for a Selected Item The VLOOKUP function looks for a value in the left column of the lookup table. In this example, we'll find the price for a selected product. It's important to get the correct price, so the following arguments are used in the formula: • • • •
a product name is entered in cell B7 the pricing lookup table is in range B3:C5 price is in column 2 of the pricing lookup table. FALSE is used in the last argument, to find an exact match for the lookup value. The formula in cell C7 is: =VLOOKUP(B7,B3:C5,2,FALSE)
If the product name is not found in the first column of the lookup table, the VLOOKUP formula result is #N/A
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 49
Example 2: Convert Percentages to Letter Grades Usually, an exact match is required when using VLOOKUP, but sometimes an approximate match works better. For example, when converting student percentages to letter grades, you wouldn't want to type every possible percentage in the lookup table. Instead, you could enter the lowest percentage for each letter grade, and then use VLOOKUP with an approximate match. In this example: • • • • •
a percentage is entered in cell C9 the percentage lookup table is in range C3:D7 the lookup table is sorted by the percentage column, in ascending order letter grade is in column 2 of the percentage lookup table. TRUE is used in the last argument, to find an approximate match for the lookup value. The formula in cell D9 is: =VLOOKUP(C9,C3:D7,2,TRUE) If the exact percentage is not found in the first column of the lookup table, the VLOOKUP formula result is the next largest value that is less than lookup_value.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 50
The lookup value in this example is 77, which is not in the percentage column. The grade for 75 (B) is returned, because 75 is the next largest value that is less than 77.
Example 3: Find Exact Price with Approximate Match The VLOOKUP function can be slow when doing an exact match for a text string. In this example, we'll find the price for a selected product, without using the Exact Match setting. To prevent incorrect results: • the lookup table is sorted by the first column, in ascending order • COUNTIF checks for the value, to prevent incorrect results • the IF function shows a zero result, if the product is not found by the COUNTIF function
www.contextures.com
The formula in cell C7 is:
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 51
=IF(COUNTIF(B3:B5,B7),VLOOKUP(B7,B3:C5,2,TRUE),0)
If the product name is not found in the first column of the lookup table, the formula result is 0.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 52
More VLOOKUP Info and Examples
• For more VLOOKUP examples, you can visit the VLOOKUP page on the Contextures website. • For suggestions on speeding up a lookup formula, see Charles Williams' page on Optimizing Lookups.
Watch the VLOOKUP Videos To see a demonstration of the examples in the VLOOKUP function sample workbook, you can watch these 2 video tutorials. YouTube link: Find Product Price with VLOOKUP Function YouTube link: Find Product Price with VLOOKUP Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 53
Function 10: HLOOKUP
The HLOOKUP function looks for a value in the first row of a table, and returns another value from the same column in that table. This function is very similar to VLOOKUP, and works with items that are in a Horizontal list, instead of vertical.
How Could You Use HLOOKUP? The HLOOKUP function can find exact matches in the lookup row, or the closest match, so it can: • Find the sales total in a selected region • Find rate in effect on selected date
HLOOKUP Syntax The HLOOKUP function has the following syntax: • HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) o lookup_value: the value that you want to look for — it can be a value, or a cell reference. o table_array: the lookup table — this can be a range reference or a range name, with 2 or more columns. o row_index_num: the row that has the value you want returned, based on the row number within the table. o [range_lookup]: for an exact match, use FALSE or 0; for an approximate match, use TRUE or 1, with the lookup value row sorted in ascending order.
www.contextures.com
HLOOKUP Traps
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 54
Like VLOOKUP, the HLOOKUP function can be slow, especially when doing a text string match, in an unsorted table, where an exact match is requested. Wherever possible, use a table that is sorted by the first row, in ascending order, and use an approximate match. You can use MATCH or COUNTIF to check for the value first, to make sure it is in the table's first row. Other functions, such as INDEX and MATCH, can be used to return values from a table, and are more efficient.
Example 1: Find the Sales for a Selected Region The HLOOKUP function looks for a value in the top row of the lookup table. In this example, we'll find the sales total for a selected region. It's important to get the correct amount, so the following settings are used: • • • •
a region name is entered in cell B7 the region lookup table is in range C2:F3 sales total is in row 2 of the table. FALSE is used in the last argument, to find an exact match for the lookup value. The formula in cell C7 is: =HLOOKUP(B7,C2:F3,2,FALSE)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 55
If the region name is not found in the first row of the lookup table, the HLOOKUP formula result is #N/A
Example 2: Find Rate for Selected Date Usually, an exact match is required when using HLOOKUP, but sometimes an approximate match works better. For example, if rates change at the start of each quarter, only those dates are entered as column headings. Then, with HLOOKUP and an approximate match, you can find the rate that was in effect for that date. In this example: • • • • •
a date is entered in cell C5 the rate lookup table is in range C2:F3 the lookup table is sorted by the Date row, in ascending order rate is in row 2 of the table. TRUE is used in the last argument, to find an approximate match for the lookup value. The formula in cell D5 is: =HLOOKUP(C5,C2:F3,2,TRUE) If the date is not found in the first row of the lookup table, the HLOOKUP formula result is the next largest value that is less than lookup_value.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 56
The lookup value in this example is March 15th. That value is not in the date row, so the value for January 1st (0.25) is returned.
Watch the HLOOKUP Video To see a demonstration of the examples in the HLOOKUP function sample workbook, you can watch this video tutorial. YouTube link: Find Table Data with HLOOKUP Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 57
Function 11: CELL
The CELL function shows information about formatting, contents and location of the referenced cell. It's similar to the INFO function, with a list of types that you can enter, but has 2 arguments, instead of only one.
How Could You Use CELL? The CELL function can show information about a cell, such as: • Cell's number format • Worksheet name • Cell alignment or column width
CELL Syntax The CELL function has the following syntax: • CELL(info_type,reference) o info_type is one of the following values o address…Reference of first cell in reference, as text. o col…Column # of cell in reference. o color…1 = color for negative values; otherwise 0 (zero). o contents…Value of upper-left cell in reference o coord…Absolute reference of first cell in reference o filename…Filename and full path o format…Cell number format o parentheses…1 = parentheses for positive or all values; otherwise 0. o prefix …"label prefix" of cell (alignment) o protect…0 = not locked, 1 = locked. o row…Row # of cell
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 58
o type…type of data in cell (empty, text, other) o width…Column width of cell
CELL Traps There are a few things to watch for when using the CELL function: • The CELL function's reference argument is optional, but if it is omitted, the result is returned for the last cell that was changed. To ensure that the result is what you expect, it's best to include a reference — you can refer to the cell that contains the CELL formula. • When using the CELL function, you may need to recalculate the worksheet to update the CELL formula result, if the cell is changed. • For the "filename" info_type, an empty string is returned if the workbook has not yet been saved.
Example 1: Cell's Number Format With the "format" value, you can use the CELL function to show the number format of a cell. For example, if cell B7 has General format, the result of this formula is "G", for General: =CELL("format",C2)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 59
Example 2: Get Worksheet Name
With the "filename" value, the CELL function shows the file path, file name, and worksheet name. =CELL("filename",B2)
From that result, you can use other functions to extract the worksheet name. In the formula below, the MID and FIND functions are used to find closing square bracket, then return the 32 characters that follow it. (A worksheet name is limited to 31 characters) =MID(CELL("filename",C3),FIND("]",CELL("filename",C3))+1,32)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 60
Example 3: Get Info_Type From Drop Down
Instead of typing the info_type value as a string in the CELL function, you can refer to a cell that contains one of the valid values. In this example, there is a data validation drop down list in cell B4, where you can select one of the info_type values. In cell C4, the CELL function uses the selected info_type, and refers to cell B2. When "type" is selected, the result is "b" if the cell is blank, "l" for a label (text constant), and "v" for anything else. In this example, cell B2 contains a formula – ="a"&2 – and the CELL function returns an "l", because the formula result is text. If the formula changed to =2+2, the CELL function would return a "v" (anything else). =CELL(B4,B2)
When "width" is selected, the result shows the column width, as an integer. The measurement unit represents the width of one character in the default font size.
Watch the CELL Video To see a demonstration of the examples in the CELL function sample workbook, you can watch this video tutorial. YouTube link: Get Excel Worksheet Name With CELL Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 61
Function 12: COLUMNS
The COLUMNS function returns the number of columns in an array or reference. It can be combined with other formulas well with other formulas, like TRANSPOSE.
How Could You Use COLUMNS? The COLUMNS function can show the size of a table or named range: • Count columns in an Excel Table • Sum last column in a named range
COLUMNS Syntax The COLUMNS function has the following syntax: • COLUMNS(array) o array is an array or array formula, or reference to a range.
COLUMNS Traps If you're using a range reference, it must be a contiguous range.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 62
Example 1: Count Columns in an Excel Table
In Excel 2007 and Excel 2010, you can create a formatted Excel table, and refer to its name in a formula. In this example, there is a table named RegionSales.
In cell C2, the COLUMNS function counts the number of columns in the RegionSales table. =COLUMNS(RegionSales)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 63
Example 2: Sum Last Column in Named Range
If you combine the COLUMNS function with SUM and INDEX, you can get the total for the last column in a named range. Here, the selected range is named MyRange.
This formula, in cell C2, sums the last column in the named range. =SUM(INDEX(MyRange,,COLUMNS(MyRange)))
The COLUMNS function counts the columns in the range, and the INDEX function uses that number to return a reference to the last column. Then, SUM calculates the total amount in that column.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 64
Watch the COLUMNS Video
To see a demonstration of the examples in the COLUMNS function sample workbook, you can watch this video tutorial. YouTube link: Count Columns With Excel COLUMNS Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 65
Function 13: TRANSPOSE
The TRANSPOSE function returns a horizontal range of cells as a vertical range, or vice versa.
How Could You Use TRANSPOSE? The TRANSPOSE function can change the orientation of data, or be used with other functions: • change horizontal data to vertical • show total salary over best consecutive years To change data orientation, without links, • use Paste Special > Transpose.
TRANSPOSE Syntax The TRANSPOSE function has the following syntax: • TRANSPOSE(array) o array is an array or a range of cells to be transposed
TRANSPOSE Traps • TRANSPOSE must be entered as an array formula, by pressing Ctrl+Shift+Enter. • The TRANSPOSE destination range must have the same number of rows and columns, respectively, as the array has columns and rows.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 66
Example 1: Change Horizontal Data to Vertical If data is arranged horizontally in a worksheet, you can use the TRANSPOSE function to list the data vertically, in a different location. For example, in a summary sheet, or dashboard, a vertical layout might be best. With the TRANSPOSE function, you could link to your original horizontal data, without changing its layout. TRANSPOSE is a bit tricky to use though, because you have to carefully select the destination cells, before you type the formula. For example, to transpose a 2 row x 4 column horizontal range to a 4 row x 2 column vertical range: 1. Select the 8 cells where you want to display the data vertically â&#x20AC;&#x201D; cells B4:C7 in this example. 2. Type this formula, then press Ctrl+Shift+Enter to enter as an array formula. =TRANSPOSE(B1:E2) Curly brackets will be automatically added at the start and end of the formula, to show that it is array entered.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 67
Instead of using TRANSPOSE, you could use another formula to display the data, such as this INDEX formula. It does not require array entry, and you don't have to select all the destination cells when creating the formula. Enter this formula in cell C4, then copy down to cell C7, to get the annual amounts from the horizontal table, in a vertical layout. =INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 68
Example 2: Change Orientation Without Links If you just want to change the orientation of your data, without keeping a link to the original data, you can use the Paste Special command: 1. 2. 3. 4.
Select the original data and copy it Select the top left cell of the destination range On the Ribbon's Home tab, click the Paste drop down arrow, then click Transpose (optional) Delete the original data.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 69
Example 3: Total Salary for Best Consecutive Years The TRANSPOSE function can be used with other functions, as in this eye-popping formula. It was posted by Harlan Grove, in the Excel newsgroups, in a discussion on calculating the total salary for the best 5 consecutive years. In this example, cell A5 is named Number, and I've entered 4, for the number of years this example. Type this formula in cell B5, and press Ctrl+Shift+Enter: =MAX(MMULT(A8:J8,–(ABS(TRANSPOSE(COLUMN(A8:J8)) -COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1)) -(Number-1)/2)<Number/2)))
The formula tests the ranges to see if there are enough consecutive COLUMNS. The results of those tests (1 or 0) are multiplied by the cell values, to get the total salaries. To verify the results, I used the INDEX function, with SUM, with the maximum value highlighted in yellow. This is a long way to accomplish what the previous formula did in one cell!
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 70
Watch the TRANSPOSE Video
To see a demonstration of the examples in the TRANSPOSE function sample workbook, you can watch this video tutorial. YouTube link: Change Horizontal Data to Vertical – Excel TRANSPOSE Function
www.contextures.com
Function 14: T
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 71
The T function returns the text from a value, or an empty string for no text.
How Could You Use T? The T function has very limited use, and could be replaced by other functions, such as ISTEXT. You can use T to: • Return text if value is text • Return an empty string if value is non-text
T Syntax The T function has the following syntax: • T(value) o value can be any value — typed in the formula, or a cell reference
T Traps The T function returns an error if the value is an error, unlike ISTEXT, which returns FALSE for errors.
Example 1: Test If a Value is Text I can't think of a situation where T would be preferable to another function, like ISTEXT. Let's compare the two functions, side by side, to see the results. If a value is text, the T function returns that text. In the screenshot below, the T function is used in cell C4, to check the value in cell B3. =T(B4)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 72
That looks like a number in cell B4, but there's an apostrophe in front of it, so it's actually text. The T function returns the "1234" string. In column D, the ISTEXT function also tests the value in cell B4, and returns the result of TRUE. =ISTEXT(B4)
If the value is a number, as in cell B3, the T function result is an empty string. If you use ISTEXT instead, the result is FALSE. For error values, the T function result is an error, and ISTEXT returns false.
Watch the Tea for Two Video With only one small example, there's not enough information to make a demo video for the T function. If you'd like a short break, here's a link to a musical interlude instead â&#x20AC;&#x201D; Anita O'Day singing Tea for Two. And as we now know, =T(42) would return an empty string! YouTube link: Tea for Two by Anita O'Day
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 73
Function 15: REPT
The REPT function repeats a text string, a specified number of times. The REPT function repeats a text string, a specified number of times. The REPT function repeats a text string, a specified number of times. Note: Writing the above paragraph would be much easier with the REPT function.
How Could You Use REPT? The REPT function can be used to fill a cell with a character, or in innovative ways, such as: • Creating an in-cell bar or dot chart • Keeping a quick tally • Finding the last text entry in a column
REPT Syntax The REPT function has the following syntax: • REPT(text,number_times) o text is the item that you want to repeat. o number_times is a positive number
REPT Traps • The limit to the text string is 32,767 characters — anything higher, and the formula will result in an error. • If number_times is a decimal, it will be truncated to an integer. • If number_times is zero, the result is an empty string.
www.contextures.com
REPT Alternative
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 74
If you simply want to fill a cell with a character, you can use cell formatting instead: 1. In a cell, type the character(s) that you want as the fill, e.g. a hyphen or period 2. With the cell selected, press Ctrl + 1, to open the Format Cells window 3. Click the Alignment tab, and from the Horizontal setting, select Fill 4. Click OK to close the dialog box. The character fills the cell, and expands or shrinks if the column width is adjusted.
Example 1: In-Cell Bar Chart If you don't have a newer version of Excel, with data bars, use the REPT function to create a simple in-cell bar chart. For example, to create a bar chart for a target of 100: 1. In cell B3, type 100 2. In cell C3, enter the following formula: =REPT("n",B3/5) 3. Format cell C3 with Wingdings font (I used font size 9), and adjust column C's width to fit the bar chart. 4. Change the number in cell B3, and the chart will change. I added conditional formatting in this example, to highlight quantities less than 60.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 75
Example 2: In-Cell Dot Chart
Instead of bar charts, you can use the REPT function to create a simple in-cell dot chart. For example, to create a dot chart for a target of 100: 1. In cell B3, type 100 2. In cell C3, enter the following formula: =REPT(" ",B3/5-1) & "o" 3. Adjust column C's width to fit the resulting bar. Change the number in cell B3, and the dot location in the chart will change.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 76
Example 3: Keep a Simple Tally
If you lost your cribbage board, or are counting the days until your next vacation, you can use a simple tally to keep track of the score, or the passing days.
To create a tally with the REPT function: 1. In cell B3, type your target amount, e.g. 25 2. In cell C3, enter the following formula: =REPT("tttt ",INT(B3/5)) & REPT("l",MOD(B3,5)) 3. Format cell C3 with Comic Sans font, or another font with a straight "t". (Finally — a use for Comic Sans!) 4. Adjust column C's width to fit the resulting bar. If the target number is high, you can increase the row height, and format for Wrap Text 5. Change the number in cell B3, and the tally will change. The formula result shows one group of t's, for every 5 items in the count — INT(B3/5). If there is a remainder, after dividing the count by 5, that amount is displayed at the end, as lower case L's — MOD(B3,5).
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 77
Example 4: Find the Last Text Item in a Column Combined with VLOOKUP, you can use the REPT function to find the last text item in a column. For example, with text items in column D, use this formula to find the last item: =VLOOKUP(REPT("z",255),D:D,1) The REPT function in the formula creates a text string at the end of the alphabet, and VLOOKUP won't be able to find that string. So, with approximate match, it returns the last text item in the list.
Watch the REPT Video To see a demonstration of the examples in the REPT function sample workbook, you can watch this video tutorial. YouTube link: Get Excel Version Number with REPT Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 78
Function 16: LOOKUP
The LOOKUP function returns a value from a one-row or one-column range or from an array. It's similar to VLOOKUP and HLOOKUP, but works in a slightly different way.
How Could You Use LOOKUP? The LOOKUP function can return a result, based on a lookup value, such as: • Find last number in a column • Find latest month with negative amount • Convert student percentages to letter grades
LOOKUP Syntax The LOOKUP function has two syntax forms — Vector and Array. With Vector form, it looks for a value in a specified column or row, and with Array form, it looks in the first row or column of an array. The Vector form has the following syntax: • LOOKUP(lookup_value,lookup_vector,result_vector) o lookup_value can be text, number, logical value, a name or a reference o lookup_vector is a range with only one row or one column o result_vector is a range with only one row or one column o lookup_vector and result_vector must be the same size
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 79
The Array form has the following syntax:
• LOOKUP(lookup_value,array) o lookup_value can be text, number, logical value, a name or a reference o searches based on the array dimensions: o if there are more columns than rows, it searches in the first row o if equal number, or more rows, it searches first column o returns value from same position in last row/column
LOOKUP Traps • The LOOKUP function doesn't have an option for Exact Match, which both VLOOKUP and HLOOKUP have. If the lookup value isn't found, it matches the largest value that is less than the lookup value. • The lookup array or vector must be sorted in ascending order, or the result might be incorrect. • If the first value in the lookup array/vector is bigger than the lookup value, the result is an #N/A error.
Example 1: Find Last Number in Column In the Array form, you can use the LOOKUP function to find the last number in a column. Excel's Help lists 9.99999999999999E+307 as the largest number allowed to be typed into a cell, so that number is entered as the lookup value. Assuming that large number won't be found, the last number in column D is returned. In this example, the numbers in column D do not have to be sorted, and there are text entries included in the column. =LOOKUP(9.99999999999999E+307,D:D)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 80
Example 2: Find Latest Month With Negative Amount This example uses LOOKUP in its Vector form, with sales amounts in column D, and month names in column E. Things didn't go well for a few months, and there are negative amounts in the sales column. To find the last month with a negative amount, this LOOKUP formula tests each sales amount to see if it's less than zero. Then, 1 is divided by that result, and returns either a 1 or a #DIV/0! error. The lookup value is 2, which won't be found (only 1 or #DIV/0!). So, the last 1 is used, to return the month name from column E. =LOOKUP(2,1/(D2:D8<0),E2:E8)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 81
Example 3: Convert student percentages to letter grades Just as you did with the VLOOKUP formula, you can use LOOKUP, in its Vector form, to find the letter grade for a student's percentage score. With LOOKUP, the percentages do not have to be in the first column of the lookup table â&#x20AC;&#x201D; you can specify any column. Here, the scores are in column D, sorted in ascending order, and letter grades are in column C, to the left of the lookup column. Enter this formula in cell D10, to see the grade for the score in cell C10: =LOOKUP(C10,D4:D8,C4:C8)
Watch the LOOKUP Video To see a demonstration of the examples in the LOOKUP function sample workbook, you can watch this video tutorial. YouTube link: Get Last Number with Excel LOOKUP Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 82
Function 17: ERROR.TYPE
The ERROR.TYPE function identifies an error type by number, or returns #N/A if no error is found. You can use that information to help with troubleshooting
How Could You Use ERROR.TYPE? With the ERROR.TYPE function, you can: • identify an error type • help users troubleshoot error results
ERROR.TYPE Syntax The ERROR.TYPE function has the following syntax: • ERROR.TYPE(error_val) o error_val is the error that you want to identify o ERROR.TYPE codes: o 1…..#NULL! o 2…..#DIV/0! o 3…..#VALUE! o 4…..#REF! o 5…..#NAME? o 6…..#NUM! o 7…..#N/A o #N/A..Other
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 83
ERROR.TYPE Traps
If the error_val is not an error, the result of the ERROR.TYPE function is an #N/A error. You can avoid this, by using ISERROR to test for an error, as shown in Example 2.
Example 1: Identify the Error Type With the ERROR.TYPE function you can check a cell, to identify which error it contains. If there isn't an error in the cell, the result is #N/A, instead of an error type code number. =ERROR.TYPE(B3)
In this example, cell B3 contains #VALUE!, so the error type is 3.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 84
Example 2: Help Users Troubleshoot Errors
By combining the ERROR.TYPE function with other functions, you can help users troubleshoot error results in a cell. In this example, numbers should be entered in cells B3 and C3. If text is entered, the result in D3 is a #VALUE! error. If a zero is entered in cell C3, the result is a #DIV/0! error. In cell D4, ISERROR checks for an error, and the ERROR.TYPE function returns a number for the error. The LOOKUP function finds the applicable troubleshooting message from a table of error type codes, and displays it. =IF(ISERROR(D3), LOOKUP(ERROR.TYPE(D3),$B$9:$B$15,$D$9:$D$15),"")
Here's the lookup table, with Error Type numbers, and messages.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 85
Watch the ERROR.TYPE Video
To see a demonstration of the examples in the ERROR.TYPE function sample workbook, you can watch this video tutorial. YouTube link: Troubleshoot Excel Errors with ERROR.TYPE Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 86
Function 18: SEARCH
The SEARCH function looks for a text string, within another text string, and returns its position, if found. SEARCH is not case sensitive.
How Could You Use SEARCH? The SEARCH function looks for a text string, within another text string, and it can: • Find a text string within another text string – not case sensitive • Use wildcards in the search • Specify a start number within search text
SEARCH Syntax The SEARCH function has the following syntax: • SEARCH(find_text,within_text,start_num) o find_text is the text that you're looking for. o within_text is the string that you're searching in. o if start_num is omitted, the search starts with the first character
SEARCH Traps The SEARCH function will return the position of the first matching string, regardless of case. If you need a case sensitive search, use the FIND function.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 87
Example 1: Find Text in a String
Use the SEARCH function to look for text within a text string. In this example, we're looking for a single character (entered in cell B5), within a text string in cell B2. =SEARCH(B5,B2) If the text is found, the SEARCH function returns the number of its starting position in the text string. If it's not found, the result is a #VALUE! error. You could use IFERROR to wrap the SEARCH function, and display a message, if the result is an error. The IFERROR is available in Excel 2007 and later versions. For earlier versions, you can use IF with ISERROR. =IFERROR(SEARCH(B5,B2),"Not Found")
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 88
Example 2: Use wildcards with SEARCH
Another way to check the SEARCH results is with the ISNUMBER function. If the string is found, the SEARCH result is a number, so the ISNUMBER result is TRUE. If the text is not found, SEARCH results in an error, and ISNUMBER returns FALSE. You can use wildcards in the find_text argument. The * (asterisk) represents any number of characters, or no characters, and the ? (question mark) represents a single character. In this example, the * wildcard is used, so central, center and centre are all found in the street addresses, when the search string, in cell E2, is cent*r. This formula is entered in cell C3, and copied down to cell C7. =ISNUMBER(SEARCH($E$2,B3))
Example 3: Specify Start Number for SEARCH By typing two minus signs (double unary) in front of the ISNUMBER function, it returns 1/0 instead of TRUE/FALSE. In this example, City and Occupation are shown in column B. We want to find all occupations with the text string entered in cell E1. The SUM function in cell E2 will total the number of records where the text string was found. Enter this formula in cell C2, and copy down to C11: =–ISNUMBER(SEARCH($E$1,B2))
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 89
The formula found the string that contain "bank", but one of those is in a City name, not the occupation:
There is a pipe character after each city name, so we can add a SEARCH for that. The pipe character position can be used as the start_number argument in the main SEARCH, so the cities will be ignored when searching. Now, with the revised formula, only the rows with "bank" in the occupation are counted. =–ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2)))
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 90
Watch the SEARCH Video
To see a demonstration of the examples in the SEARCH function sample workbook, you can watch this video tutorial. YouTube link: Find Text in String With Excel SEARCH Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 91
Function 19: MATCH
The MATCH function returns the position of a value in an array, or #N/A if not found. The array can be sorted, or unsorted, and the MATCH function is not case sensitive.
How Could You Use MATCH? The MATCH function returns the position of an item in an array, and that result can be used by other functions, such as INDEX or VLOOKUP. For example: • • • •
Find position of item in unsorted list Use with CHOOSE to get student grades Use with VLOOKUP for flexible column choice Use with INDEX for to show winner's name
MATCH Syntax The MATCH function has the following syntax: • MATCH(lookup_value,lookup_array,[match_type]) o lookup_value can be text, number or logical value o lookup_array is an array, or array reference (contiguous cells in a single row or column) o match_type can be -1, 0 or 1. If omitted, assumed to be 1
MATCH Traps MATCH function returns the position of the item found, not the value. If you need the value, combine MATCH with another function, like INDEX.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 92
Example 1: Find Item in Unsorted List
For an unsorted list, you can use 0 as the match_type argument, to find an exact match. If you're searching for text, and using 0, you can include wildcard characters in the lookup value. In this example, you can type a month name, or partial name with wildcards, in cell D2. Type this formula in cell E2, to find that month's position in the list. The lookup_array argument is an array reference â&#x20AC;&#x201C; B3:B7 =MATCH(D2,B3:B7,0)
Instead of an array reference, you can type an array as the lookup_array argument, within curly brackets. In this formula, the lookup month name is in cell D5, and three month names are typed in the MATCH function's second argument -- {"Jan","Feb","Mar"}. If a later month, such as Oct, is entered in D5, the result will be #N/A. =MATCH(D5,{"Jan","Feb","Mar"},0)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 93
Example 2: Change Student Grades to Letters
Just as you did with VLOOKUP, you can use MATCH to help convert a student's score to a letter grade. In this example, it is combined with CHOOSE, to get the letter grade. The match_type is -1, because the scores are sorted in descending order. When the match_type is -1, the result is the smallest value greater than or equal to the lookup value. Enter this formula in C9, to find the letter grade for the score in cell B9. =CHOOSE(MATCH(B9,B3:B7,-1),C3,C4,C5,C6,C7)
The lookup value is 54, and it's not in the list of scores, so MATCH returns the position for 60. Because 60 is in position 4, the 4th value in the CHOOSE options is the result â&#x20AC;&#x201D; cell C6, with a grade of D.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 94
Example 3: Create Flexible Column Choice in VLOOKUP To make a VLOOKUP formula more flexible, you can use MATCH to find a column number, instead of hard-coding it into the formula. In this example, users can select a region name in cell H1, as the lookup value for the VLOOKUP. Then, they can select a Month in cell H2, and the MATCH function returns the column for that month. Enter this formula in cell H3, to find the sales for the selected Region and Month: =VLOOKUP(H1,$B$2:$E$5,MATCH(H2,B1:E1,0),FALSE)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 95
Example 4: FIND Closest Match with INDEX
The MATCH function also works well with the INDEX function. In this example, the MATCH function is used to find the guess that is closest to the correct number. 1. The ABS function returns the absolute difference between each guess and the correct number. 2. The MIN function finds the smallest difference. 3. The MATCH function finds the smallest difference in the list of differences. If there are multiple identical differences, the first one will be returned. 4. The INDEX function returns the name in that position in the list of names. Type this formula in cell F2, then press Ctrl+Shift+Enter, to enter as an array. =INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))
Watch the MATCH Video To see a demonstration of the examples in the MATCH function sample workbook, you can watch this video tutorial. YouTube link: Find Item Position in List With Excel MATCH Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 96
Function 20: ADDRESS
The ADDRESS function returns a cell address as text, based on a row and column number. It can return an absolute or relative address, in A1 or R1C1 style. A sheet name can also be included in the result.
How Could You Use ADDRESS? The ADDRESS function can return a cell address, or combine with other functions to: • Get cell address from row and column number • Find cell value from row and column number • Return address of cell with highest value
ADDRESS Syntax The ADDRESS function has the following syntax: • ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text]) o abs_num — if 1 or omitted, Absolute ($A$1), for Relative (A1), use 4. Other options, 2=A$1, 3=$A1 o a1 — if TRUE or omitted, returns A1 style, FALSE for R1C1 style o sheet_text — sheet name can be included, for address on different sheet
ADDRESS Traps The ADDRESS function only returns the cell address as text. If you need the cell value, use the INDIRECT function around the ADDRESS function, or use one of the alternative formulas shown in Example 2.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 97
Example 1: Get cell address from row and column number With the ADDRESS function, you can get a cell address, as text, based on a row number and column number. If you enter just those two arguments, the result is an absolute address, in A1 style. Enter this formula in cell C4, to get an address based on the numbers in C2 and C3: =ADDRESS($C$2,$C$3)
By omitting the abs_num argument in the formula above, the result was the default, absolute reference -- $C$1. To see the address as a relative reference, change the formula, by adding 4 as the abs_num argument. =ADDRESS($C$2,$C$3,4)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 98
To see R1C1 style, instead of the default A1 style, you can add FALSE in the a1 argument. In this version, the formula has a 1 as the abs_ref argument, so the result is an absolute reference, in R1C1 style – R1C3. =ADDRESS($C$2,$C$3,1,FALSE)
The final argument is sheet, and you can include a sheet name, if you want it in the result. The ADDRESS function will automatically add single quotes around the sheet name, in the formula result. =ADDRESS($C$2,$C$3,1,TRUE,"Ex02")
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 99
Example 2: Find cell value from row and column number The ADDRESS function returns the cell address as text, not as an actual reference. If you want to return the cell's value, you can wrap the ADDRESS function with an INDIRECT function, to create a reference. Enter this formula in cell C4, to return the value from cell C1: =INDIRECT(ADDRESS(C2,C3))
The INDIRECT function will work without the ADDRESS function too. Here, the & operator is used to create an R1C1 style address, and the cell value is returned. =INDIRECT("R" & C2 & "C" & C3,FALSE)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 100
The INDEX function can also return a cell's value, based on a row and column number. =INDEX(1:500,C2,C3)
Example 3: Return address of cell with highest value In this example, we'll find the cell with the highest value, and use the ADDRESS function to get its address. In cell F3, the MAX function finds the highest number in column C. =MAX(C3:C8)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 101
Then, in cell F4, the ADDRESS function is combined with MATCH, which finds the row number, and COLUMN, which gets the column number. =ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
Watch the ADDRESS Video To see a demonstration of the examples in the ADDRESS function sample workbook, you can watch this video tutorial. YouTube link: Get Cell Address as With Excel ADDRESS Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 102
Function 21: TYPE
The TYPE function returns a number that identifies a value's type:
Here's the list of results, and the data types:
How Could You Use TYPE? The TYPE function can tell you what kind of value is in a cell, but the logical functions, like ISERROR, ISTEXT, etc., will also check for a specific data type. However, if you just want to know what's in a cell, the TYPE function can: • Identify cell value type by number • Test for Number type before multiplying
TYPE Syntax The TYPE function has the following syntax: • TYPE(value) o value can be text, number, error, or any other value
www.contextures.com
TYPE Traps
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 103
Unfortunately, the TYPE function cannot identify cells that contain a formula. It only shows the type for a cell's contents, or a formula's result. In a few versions of Excel, the Help files incorrectly reported that a formula would return 8 with the TYPE function. This MSKB article corrects that error. http://support.microsoft.com/kb/119148
Example 1: Identify cell value type by number The TYPE function returns a number, based on a value's type, so you can use it to see what's in a cell. For example, if you type 123 in cell B3, the result is 1 – Number. =TYPE(B3)
However, if there is an apostrophe in front of the number, the TYPE function result is 2 – Text.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 104
Example 2: Test for Number type before multiplying You could use the TYPE function with CHOOSE, to multiply valid numbers, or show a message, if something else is entered. =CHOOSE(MIN(TYPE(B3),3),B3*C3,"No text","Enter Qty") • If a number is entered in B3, the TYPE function returns 1. That's less than 3, so the MIN function returns 1, and CHOOSE returns the result of B3*C3.
• If text is entered in B3, the TYPE function returns 2. That is less than 3, so the MIN function returns 2, and CHOOSE returns the message "No text".
• If anything else is entered in B3, the TYPE function returns a 4 or higher. The MIN function result will be 3, so CHOOSE returns the message "Enter Qty".
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 105
Watch the TYPE Video
To see a demonstration of the examples in the TYPE function sample workbook, you can watch this video tutorial. YouTube link: Identify Cell Contents with Excel TYPE Function
www.contextures.com
Function 22: N
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 106
The N function returns a value converted to a number. It's similar to the T function that we saw earlier, but checks for numbers instead of text.
How Could You Use N? The N function has limited use, and can usually be replaced by a different function or operator. You can: • Return number based on a cell's value • Use N function alternatives • Add a hidden note in a cell
N Syntax The N function has the following syntax: • N(value) o value can be text, number, or any other value
N Traps If the value is an error, the N function returns the same error, instead of a number. Use IFERROR or ISNUMBER to handle the errors.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 107
Example 1: Return a Number Based on Cell Value With the N function, you can return a number, based on a cell's value. If the cell's value is a number, then that number is returned. =N(C3)
This table shows the result of other values in the N function.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 108
Example 2: N Function Alternatives
Instead of the N function, you can use ISNUMBER to test for numbers. =ISNUMBER(C3)
Or use two minus signs (double unary) to convert TRUE/FALSE or text numbers to numbers. =IFERROR(--C3,"")
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 109
Example 3: Add Hidden Note to Cell
If you want to add a hidden note in a cell, you can use the N function. Because the result of a text value in the N function is zero, adding this note won't affect the cell result. The note will only be visible in the formula bar, when the cell is selected. =SUM(B3:C3) + N("Store01 closed in June")
Watch the N Video To see a demonstration of the examples in the N function sample workbook, you can watch this video tutorial. YouTube link: Return Cell Value as Number with Excel N Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 110
Function 23: FIND
The FIND function finds a text string, within another text string, and is case sensitive. It's similar to the SEARCH function, but the FIND function is case sensitive, and SEARCH is not.
How Could You Use FIND? The FIND function can find text within another string, and is case sensitive. For example: • Find start position of text in text string • Find exact values from a list • Find street name in address
FIND Syntax The FIND function has the following syntax: • FIND(find_text,within_text,[start_num]) o find_text is the text that you're looking for. o within_text is the string that you're searching in. o if start_num is omitted, the search starts with the first character
FIND Traps • The FIND function will return the position of the first matching string, and it is case sensitive. For a search that is not case sensitive, use the SEARCH function. • You can't use wildcard characters in the find_text string. For wildcards, use the SEARCH function.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 111
Example 1: Find Text in a Text String
To find specific text in a text string, you can use the FIND function. Enter this formula in cell D5, to find text in cell B2, based on the text entered in cell B5. =FIND(B5,B2)
FIND is case sensitive, so the first two "i" characters are ignored, because they are lower case. The upper case I, at position 14, is found. To handle errors, if the text is not found, you can wrap the FIND function with IFERROR (in Excel 2003 or earlier, use IF and ISERROR). =IFERROR(FIND(B5,B2),"Not Found")
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 112
Example 2: Find exact values from a list
Because FIND is case sensitive, you can use it to find exact strings in another string. In this example, there are valid codes listed in column E. With the FIND function, we can identify the ID numbers in column B that contain one of the valid code strings. This formula must be array-entered, by pressing Ctrl + Shift + Enter =IF(OR(ISNUMBER(FIND($E$2:$E$4,B2))),"Yes","No")
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 113
Example 3: Find street name in address
In the next example, most of the addresses in column B start with a street number. With this formula in C2, the street number is removed. =IF(ISNUMBER(--LEFT(B2,1)),MID(B2,FIND(" ",B2)+1,LEN(B2)),B2)
The formula tests the first character at the LEFT, using a double unary (--) and ISNUMBER. ISNUMBER(--LEFT(B2,1)) If the first character is a number, the FIND function locates the first space character, and the MID function returns all the text from the next character, to the end. MID(B2,FIND(" ",B2)+1,LEN(B2)) If the first character is not a number, the entire text string in cell B2 is returned.
Watch the FIND Video To see a demonstration of the examples in the FIND function sample workbook, you can watch this video tutorial. YouTube link: Locate Text With Excel FIND Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 114
Function 24: INDEX
The INDEX function returns a value or reference to a value. Combine it with other functions, like MATCH, for powerful formulas.
How Could You Use INDEX? The INDEX function can return a value or reference to a value, so you can use it to: • Find sales amount for selected month • Get reference to specified row, column, area • Create a dynamic range based on count • Sort column of text in alphabetical order We've already used INDEX several times, with other functions: • • • •
with EXACT to find name for the password with exact match with AREAS to find the last area in a named range with COLUMNS to calculate sum of last column in range with MATCH to find the name for closest guess in a contest
www.contextures.com
INDEX Syntax
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 115
The INDEX function has two syntax forms — Array and Reference. With Array form, a value is returned, and with Reference form, a reference is returned. The Array form has the following syntax: • INDEX(array,row_num,column_num) o array is an array constant or range of cells o if array has only 1 row or column, corresponding row/column number argument is optional o if array has >1 row or column, and only row_num or column_num is used, array of entire row or column is returned o row_num – if omitted, column_num is required o column_num – if omitted, row_num is required o if both the row_num and column_num arguments are used, returns value in cell at intersection of row_num and column_num o if row_num or column_num are zero, returns array of values for entire column or row The Reference form has the following syntax: • INDEX(reference,row_num,column_num,area_num) o reference can refer to one or more cell ranges – enclose nonadjacent ranges in parentheses o if each area in reference has only 1 row or column, corresponding row/column number argument is optional o area_num selects range in reference from which to return row and column intersection o area_num – if omitted, area 1 is used o if row_num or column_num are zero, returns reference for entire column or row o result is a reference, and can be used by other functions
INDEX Traps If the row_num and column_num don't point to a cell within the array or reference, the INDEX function returns a #REF! error.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 116
Example 1: Find sales amount for selected month Enter a month number in cell F2, and the INDEX function in cell G2 returns the sales amount from that row in the reference. Here the month number is 4, so the April sales amount is returned. =INDEX($C$2:$C$8,F2)
To make the formula more flexible, you could use MATCH to return the row number, based on the month that was selected from a drop down list. =INDEX($C$2:$C$8,MATCH($F$2,$D$2:$D$8,0))
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 117
Example 2: Get reference to specified row, column, area In this example, there is a named range, MonthAmts, which consists of 3 non-contiguous ranges – coloured blue in the screenshot below. The MonthAmts range has 3 areas — one for each month — and there are 4 rows and 2 columns in each area.
With the INDEX function, you can return the cost or revenue amount for a specific region and month in the MonthAmts range. • reference: MonthAmts range • row_num: B10 – Region number • column_num: C10 – column number (1=Rev, 2=Cost) • area_num: D10 – Month number Enter this formula in cell E10, to find the amount for the specified region, column, and month: =INDEX(MonthAmts,B10,C10,D10)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 118
The INDEX function result can be multiplied, as in the Tax calculation in cell F10: =0.05*INDEX(MonthAmts,B10,C10,D10) or, it can return a reference for the CELL function, to show the address of the result, in cell G10. =CELL("address",INDEX(MonthAmts,B10,C10,D10))
The CELL function is also used in conditional formatting, to highlight the selected cell.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 119
Example 3: Create a dynamic range based on count You can also use the INDEX function to create a dynamic range. In this example, there is a list of month names in column C, on the INDEX_Ex03 worksheet. I've created a name, MonthsList, with this formula: =INDEX_Ex03!$C$1:INDEX(INDEX_Ex03!$C:$C,COUNTA(INDEX_Ex03!$C:$C))
If another month is added to the list in column C, it will automatically appear in the data validation drop down list in cell F2, which uses MonthsList as its source.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 120
Example 4: Sort column of text in alphabetical order In the final example, the INDEX function is combined with several other functions, to return a list of months, sorted in alphabetical order. This formula is typed in cell E4, and array-entered, by pressing Ctrl + Shift + Enter. Then, copy the formula down to cell E9. =INDEX($C$4:$C$9,MATCH(SMALL( COUNTIF($C$4:$C$9,"<"&$C$4:$C$9),ROW(E4)-ROW(E$3)), COUNTIF($C$4:$C$9,"<"&$C$4:$C$9),0))
Read the next section, to see how the formula works.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 121
How the Formula Works
To see how this formula works, we can take a look at the functions within the formula. We'll break the functions out into separate columns, to see the step-by-step results. • The COUNTIF function shows how many month names come before a specific month name. To test it, you can array-enter this formula in cell G4, and copy down to G9: =COUNTIF($C$4:$C$9,"<"&$C$4:$C$9)
From the result in cell G4, we can see that there are 2 months that come before January, alphabetically. • The ROW function calculates the row within the results list, by subtracting the formula's row from the heading cell's row: =ROW(E4)-ROW(E$3) • SMALL returns the nth smallest item in the COUNTIF list, based on the calculated ROW number. So, for ROW 1, the smallest COUNTIF result is 0. =SMALL($G$4:$G$9,H4)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 122
• MATCH returns the row number for the SMALL result. For 0, the MATCH is found in row 4 of the table. =MATCH(I4,$G$4:$G$9,0)
• INDEX returns the Month name, based on the MATCH result. =INDEX($C$4:$C$9,J4)
Watch the INDEX Video To see a demonstration of the examples in the INDEX function sample workbook, you can watch this video tutorial. YouTube link: Excel INDEX Function Gets Value from Row and Column
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 123
Function 25: REPLACE
The REPLACE function replaces characters within text, based on the number of characters, and starting position, specified.
How Could You Use REPLACE? The REPLACE function can replace characters in a text string, such as: • change area code in phone number • Replace first space with colon and space • Use nested REPLACE to insert hyphens
REPLACE Syntax The REPLACE function has the following syntax: • REPLACE(old_text,start_num,num_chars,new_text) o old_text is the text string in which characters will be replaced. o start_num is the position of the old characters o num_chars is the number of old characters that will be replaced o new_text is the text that will replace the original text
REPLACE Traps The REPLACE function replaces a specified number of characters at the indicated starting position. To replace a specific text string, anywhere in the original text, you can use the SUBSTITUTE function.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 124
Example 1: Change area code in phone number With the REPLACE function, you can change the first three digits in a phone number, when a new area code is introduced. In this example, the new area code is entered in column C, and the revised phone numbers are shown in column D. =REPLACE(B3,1,3,C3)
With the text in cell B3, starting from the first character, 3 characters are replaced, with the value in cell C3.
Example 2: Replace first space with colon and space To identify the starting position for the REPLACE function, you can use the FIND function, to locate a specific text string or character. In this example, we want to replace the first space character, with a colon and space character. =REPLACE(B3,FIND(" ",B3,1),1,": ")
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 125
Example 3: Use nested REPLACE to insert hyphens The REPLACE function can be nested, so multiple replacements are made in the old text string. In this example, the list of phone numbers needs to have hyphens inserted, after the first 3 numbers, and after the second 3 numbers. By using zero as the number of characters to replace, none of the existing numbers will be removed, and hyphens will be inserted. First, a hyphen will be inserted as the 4th character. Then, a hyphen will be inserted as the 8th character, in that revised text string. =REPLACE(REPLACE(B3,4,0,"-"),8,0,"-")
Watch the REPLACE Video To see a demonstration of the examples in the REPLACE function sample workbook, you can watch this video tutorial. YouTube link: Change Text with Excel REPLACE Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 126
Function 26: OFFSET
The OFFSET function returns a reference, of a specified size, based on a starting reference, and offset by a set number of rows and columns.
How Could You Use OFFSET? The OFFSET function can return a reference to a range, and be combined with other functions. Use it to: • • • •
Find sales amount for selected month Sum the selected month's sales Create a dynamic range based on count Sum the last n month's sales
OFFSET Syntax The OFFSET function has the following syntax: • OFFSET(reference,rows,cols,height,width) o reference is a cell or range of adjacent cells. o rows can be positive (below starting reference) or negative (above starting reference) o cols can be positive (right of starting reference) or negative (left of starting reference) o height must be positive, and is number of rows in returned reference o width must be positive, and is number of columns in returned reference o if height or width are omitted, starting reference size is used
www.contextures.com
OFFSET Traps
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 127
The OFFSET function is volatile, so it could slow down a workbook if used in too many cells. Instead, you could use another function, like INDEX, to return a reference.
Example 1: Find sales amount for selected month With the OFFSET function, you can return a reference to a range, based on a starting reference. In this example, we want the sales amount in cell G2: • starting reference is cell C1 • number of rows to offset is entered in cell F2 • sales amount is in column C, so the number of columns to offset is zero • height is 1 row • width is 1 column =OFFSET(C1,F2,0,1,1) In cell H2, there is a similar OFFSET formula, to return the month name. The only difference is the column offset — 1 instead of zero. =OFFSET(C1,F2,1,1,1) Note: The height and width arguments could be omitted, because we want a reference that is the same size as the starting reference. I used them in this example, to show how all the arguments work.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 128
Example 2: Sum the selected month's sales
In this example, the OFFSET function returns a reference to the sales amounts for the selected month, and the SUM function returns the total for that range. In cell B10, the month number is 3, so the result is the total of the March sales. • • • •
starting reference is A3:A6 rows to offset is zero (you could omit the zero, for the same result) columns to offset is entered in cell B10 height and width are omitted, because the reference should be the same size as the starting reference =SUM(OFFSET(A3:A6,0,B10))
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 129
Example 3: Create a dynamic range based on count Just as you did with the INDEX function, you can also use the OFFSET function to create a dynamic named range, that will expand automatically if items are added or removed. In this example, there is a list of month names in column C, on the OFFSET_Ex03 worksheet. I've created a name, Months_List, with this formula: =OFFSET(OFFSET_Ex03!$C$1,0,0,COUNTA(OFFSET_Ex03!$C:$C),1)
If another month is added to the list in column C, it will automatically appear in the data validation drop down list in cell F2, which uses Months_List as its source
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 130
Example 4: Sum the last n month's sales
In this final example, OFFSET is combined with SUM and COUNT, to show the total for the last n months. As new quantities are added, the formula result will automatically adjust to include the latest months. In cell E2, the number of months is 2, so the August and September amounts are summed. • starting reference is cell C2 • number of rows to offset is calculated by counting the numbers in column C, subtracting the number in cell E3, and adding 1 • quantity is in column C, and the starting reference is C2, so the number of columns to offset is zero • height is the number entered in cell E3 • width is 1 column =SUM(OFFSET(C2,COUNT(C:C)-E3+1,0,E3,1))
Watch the OFFSET Video To see a demonstration of the examples in the OFFSET function sample workbook, you can watch this video tutorial. YouTube link: Excel OFFSET Function Gets Range from Row and Column
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 131
Function 27: SUBSTITUTE
The SUBSTITUTE function, like the REPLACE function, replaces old text with new text, in a text string. It will replace all instances of the old text, unless a specific number of instances is selected, and SUBSTITUTE is case sensitive. In some situations though, it's quicker and easier to use the Find/Replace command on the Excel Ribbon, with Match Case option turned on, for case sensitive replacement.
How Could You Use SUBSTITUTE? The SUBSTITUTE function replaces old text with new text, in a text string, so you could use it to: • Change region name in report title • Remove non-printing characters • Replace last space character
SUBSTITUTE Syntax The SUBSTITUTE function has the following syntax: • SUBSTITUTE(text,old_text,new_text,instance_num) o text is the text string or cell reference, where text will be replaced. o old_text is the text that will be removed o new_text is the text that will be added o instance_number is the number of occurrence of old text that you want replaced
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 132
SUBSTITUTE Traps
• The SUBSTITUTE function can replace all instances of the old text, so use the instance_num argument if you want a specific occurrence of old text replaced. • For replacements that are not case sensitive, you can use the REPLACE function.
Example 1: Change region name in report title With the SUBSTITUTE function, you can create a report title that changes automatically, based on the region name that is selected. In this example, the report title is entered in cell C11, which is named RptTitle. The "yyy" string is where the region name should appear in the printed report title.
The Region name for the report is selected in cell C13.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 133
The printed report title is in cell C3. With this SUBSTITUTE formula, the "yyy" in the title text will be replaced with the region name, selected in cell D13. =SUBSTITUTE(RptTitle,"yyy",D13)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 134
Example 2: Remove non-printing characters
When you copy data from a website, there might be hidden, non-printing space characters in the text. If you try to remove these space characters from the text in Excel, the TRIM function can't remove them. The characters aren't normal space characters (character 32); they are non-breaking space characters (character 160). Instead, you can use the SUBSTITUTE function to replace each of the non-printing spaces, CHAR(160), with a normal space character, " ". Then, use TRIM to remove all the extra spaces. =TRIM(SUBSTITUTE(B3,CHAR(160)," "))
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 135
Example 3: Replace last space character
Instead of replacing all instances of a text string, you can use the SUBSTITUTE function's instance_number argument to select a specific instance. In this list of recipe ingredients, we want to replace the last space character only. In cell C3, the formula calculates the number of spaces in the B3 cell: • first LEN function calculates the number of characters in cell B3. • SUBSTITUTE function replaces all the space characters with empty strings • second LEN function finds the length of the revised string. With the spaces removed, the length is 2 characters shorter, so there are two spaces. =LEN(B3)-LEN(SUBSTITUTE(B3," ",""))
In D3, the SUBSTITUTE function replaces the 2nd space character with new text – " | " =SUBSTITUTE(B3," "," | ",C3) Instead of using two columns for this formula, you could combine them: =SUBSTITUTE(B3," "," | ",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 136
Watch the SUBSTITUTE Video
To see a demonstration of the examples in the SUBSTITUTE function sample workbook, you can watch this video tutorial. YouTube link: Change Text with Excel SUBSTITUTE Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 137
Function 28: HYPERLINK
The HYPERLINK function creates a shortcut that opens a document stored on a computer, network server, intranet, or Internet. . Instead of manually creating hyperlinks, with the command on the Excel Ribbon, you can use this function.
How Could You Use HYPERLINK? The HYPERLINK function can open a document, or jump to a specific location, so you can: • Link to location in same file • Link to Excel file in same folder • Link to website
HYPERLINK Syntax The HYPERLINK function has the following syntax: • HYPERLINK(link_location,friendly_name) o link_location is the text string for the location where you want to go. o friendly_name is the text you want displayed in the cell
HYPERLINK Traps If you have trouble creating the correct location string for the HYPERLINK function, manually insert a link with the Hyperlink command on Excel's Ribbon. That should show you the correct syntax, and then recreate that in your link_location argument.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 138
Example 1: Link to location in same file
There are several different ways to create the text string for the HYPERLINK function's link_location argument. In the first example, the ADDRESS function returns the address for row 1, column 1, on the sheet that is named in cell B3. The friendly name for the hyperlink is in cell D3. In the HYPERLINK formula, we add a pound sign (#) at the start of the address, to indicate that the location is within the current file. =HYPERLINK("#"&ADDRESS(1,1,,,B3),D3)
You could also use the & operator, and a cell address, to construct the link location. Here, the sheet name is in cell B5 and the cell address is in C5. The friendly name for the hyperlink is in cell D5. =HYPERLINK("#"&"'" & B5 & "'!" & C5,D5)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 139
For a link to a named range in the same workbook, use the range name as the link location. Here, the range name is in cell D7, and that is also used as the friendly name for the hyperlink. =HYPERLINK("#"&D7,D7)
Example 2: Link to Excel file in same folder To create a link to another Excel file, in the same folder, just use the file name as the link_location argument for the HYPERLINK function. For files that are up a level or more in the hierarchy, use two periods and a backslash for each level. =HYPERLINK(C3,D3)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 140
Example 3: Link to a website
You can also link to website pages with the HYPERLINK function. In this example, the URL is constructed from text strings, and the website name is used as the friendly name. =HYPERLINK("http://www." &B3 & ".com",B3)
Watch the HYPERLINK Video To see a demonstration of the examples in the HYPERLINK function sample workbook, you can watch this video tutorial. YouTube link: Open Files with Excel HYPERLINK Function
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 141
Function 29: CLEAN
The CLEAN function removes some non-printing characters from text — characters 0 to 31, 129, 141, 143, 144, and 157. Sometimes the data you get from a website, or in a download file, has unwanted characters. The CLEAN function can help you fix it, but it won't clean everything.
How Could You Use CLEAN? The CLEAN function can remove some non-printing characters from text , but not all of them. You can use CLEAN, or other functions when necessary, to: • Remove some non-printing characters • Replace non-printing characters in text
CLEAN Syntax The CLEAN function has the following syntax: • CLEAN(text) o text is any information from which you want the non-printing characters removed
CLEAN Traps The CLEAN function only removes some non-printing characters from text — characters 0 to 31, 129, 141, 143, 144, and 157. For other non-printing characters, such as the nonbreaking space character 160, you can use SUBSTITUTE to replace them with space characters, or empty strings.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 142
Example 1: Remove non-printing characters
The CLEAN function removes some non-printing characters, such as those in the 0-30 range of the ASCII character set. In cell C5, I added characters 9 and 13 to the original text string from C3. =CHAR(9) & C3 & CHAR(13) The LEN function, in E5, shows that the number of characters in cell C5 increased to 15, with those non-printing characters included.
With the CLEAN function, in cell C7, those characters are removed, and the number of characters is reduced by 2, so it's back to the original 13 characters. =CLEAN(C5)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 143
Example 2: Replace non-printing characters
For the characters that the CLEAN function can't remove, like characters 127 and 160, you can use the SUBSTITUTE function to replace them. =SUBSTITUTE(E3,CHAR(C3),"")
Watch the CLEAN Video To see a demonstration of the examples in the CLEAN function sample workbook, you can watch this video tutorial. YouTube link: Excel CLEAN Function Removes Non-Printing Characters
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 144
Function 30: INDIRECT
The INDIRECT function returns the reference specified by a text string. It's one of the ways that you can create a dependent data validation drop down list, where, for example, the selection in the Country drop down controls the choices in the City drop down.
How Could You Use INDIRECT? The INDIRECT function returns a reference specified by a text string, so you can use it to: • • • •
Create starting reference that doesn't shift Create reference to static named range Create reference from sheet, row, column info Create array of numbers that doesn't shift
INDIRECT Syntax The INDIRECT function has the following syntax: • INDIRECT(ref_text,a1) o ref_text is the text string for a reference. o a1 if TRUE or omitted, uses an A1 reference style; if FALSE, the R1C1 reference style is used
INDIRECT Traps • The INDIRECT function is volatile, so it could slow down your workbook, if used in many formulas. • If the INDIRECT function creates a reference to another workbook, that workbook must be open, or the formula will result in a #REF! error.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 145
• If the INDIRECT function creates a reference to a range outside the row and column limit, the formula will result in a #REF! error. (Excel 2007 and Excel 2010) • The INDIRECT function cannot resolve a reference to a dynamic named range
Example 1: Create starting reference that doesn't shift In the first example, there are identical numbers in columns C and E, and the totals are the same, using the SUM function. However, the formulas are slightly different. In cell C8, the formula is: =SUM(C2:C4) In cell E8, the INDIRECT function creates a reference to the starting cell, E2: =SUM(INDIRECT("E2"):E4)
Later, if a row is inserted at the top of the lists, and January amounts are entered, the column C total doesn't change. The formula adjusts to the inserted row, shifting down: =SUM(C3:C5) However, the INDIRECT function locks the starting cell to E2, so January is included in the column E total. The ending cell changed, but the starting cell wasn't affected. =SUM(INDIRECT("E2"):E5)
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 146
Example 2: Create reference to static named range The INDIRECT function can also create a reference for a named range. In this example, the blue cells are in a range named NumList. There is also a dynamic range in column B, based on the count of numbers in that column. The total for either range can be calculated, by using the range name with SUM: E2:
=SUM(NumList)
E3:
=SUM(NumListDyn)
Instead of typing the name in the SUM formula, you can refer to the range name in a worksheet cell. For example, with the name NumList in cell D7, the formula in cell E7 is: =SUM(INDIRECT(D7)) Using INDIRECT with the static range names gives the same result as typing the range name in the SUM function. Unfortunately, the INDIRECT function can't resolve a dynamic range, so when the formula is copied down to cell E8, the result is a #REF! error.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 147
Example 3: Create reference from sheet, row, column info You can easily create a reference based on row and column numbers, by using FALSE as the second argument in the INDIRECT function. This creates an R1C1 style reference, and in this example, a sheet name is also included â&#x20AC;&#x201D; 'MyLinks'!R2C2 =INDIRECT("'" & B3 & "'!R" & C3 & "C" & D3,FALSE)
The result in cell E3 shows the value from cell B2 on the MyLinks sheet.
Example 4: Create array of numbers that doesn't shift In some formulas, you need an array of numbers, as in this example, where we want the average of the 3 highest numbers in column B. Those numbers are highlighted with conditional formatting, in the list below.
By combining AVERAGE and LARGE in a formula, you can find the average for the top 3 numbers.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 148
The numbers {1,2,3} could be typed as an array in the formula, as they are in cell D2: =AVERAGE(LARGE(B1:B6,{1,2,3}))
If you need a bigger array of numbers, you wouldn't want to type all of them. Instead, you could use the ROW function, as in the array-entered formula in cell D3: =AVERAGE(LARGE(B1:B6,ROW(1:3)))
A third option is to use the ROW function with INDIRECT, as in the formula in cell D4, which is also array-entered: =AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
The results for all 3 formulas are the same. However, if rows are inserted at the top of the sheet, the INDIRECT function will keep the correct row reference, and continue to show the correct result. The simple ROW formula would adjust, and show an incorrect result.
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 149
Watch the INDIRECT Video
To see a demonstration of the examples in the INDIRECT function sample workbook, you can watch this video tutorial. YouTube link: Excel INDIRECT Function Gets Reference from Text String
www.contextures.com
What Next?
30 EXCEL FUNCTIONS IN 30 DAYS â&#x20AC;&#x201C; PAGE 150
You've now covered 30 Excel Functions in 30 Days, from the Text, Information and Lookup and Reference categories, and you're probably eager to learn more.
Learn from Contextures Visit the Contextures Blog, and the Contextures website for Excel VBA tips and tutorials.
Learn from Excel Help While you're working in Excel, check its Help files, to learn more about the Excel functions. There are tips, notes, and examples.
Learn from Books If you're looking for Excel VBA books, there's a list on the Contextures website: Excel VBA Books
Keep in Touch Also, if you haven't already, you can follow me on Twitter (@ddalgleish), and find my Contextures page and Pivot Tables page on Facebook. Thanks again, and I hope you keep learning Excel Functions! Debra
www.contextures.com
30 EXCEL FUNCTIONS IN 30 DAYS – PAGE 151
Functions Index
ABS ........................................................... 69, 95 absolute reference ......................................... 11 ADDRESS ........................................ 97, 101, 138 AND .................................................................. 6 AREAS ................................................. 14, 15, 16 AVERAGE ...................................................... 148 CELL ............................................ 58, 59, 60, 118 CHAR .......................... 41, 42, 43, 134, 142, 143 CHOOSE ................................ 27, 28, 29, 93, 104 CLEAN ........................................................... 142 CODE ........................................................ 38, 42 COLUMN ................................................ 69, 101 COLUMNS........................................... 62, 63, 69 COUNT.......................................................... 130 COUNTIF ................................................. 51, 120 double unary ............................ 10, 88, 108, 113 ERROR.TYPE ............................................. 83, 84 EXACT ................................................4, 5, 6, 7, 8 FIND........................................ 59, 111, 112, 113 FIXED .................................................. 32, 34, 35 HLOOKUP ................................................. 54, 55 HYPERLINK.................................... 138, 139, 140 IF................................5, 22, 38, 51, 84, 112, 113 IFERROR.......................................... 87, 108, 111 INDEX .... 16, 63, 67, 95, 100, 116, 117, 118, 120 INDIRECT ........................ 99, 145, 146, 147, 148 INFO ................................................... 22, 23, 25 INT .................................................................. 76 ISERROR.......................................................... 84 ISNUMBER ........................ 88, 89, 108, 112, 113 ISTEXT ............................................................. 72 LARGE ........................................................... 148
LEFT ...............................................................113 LEN ............................................ 18, 38, 113, 135 LOOKUP ........................................ 79, 80, 81, 84 MATCH .......................... 92, 93, 94, 95, 101, 120 MAX ........................................................69, 100 MID .........................................................59, 113 MIN .........................................................95, 104 MMULT ...........................................................69 MOD................................................................76 N............................................................107, 109 OFFSET .................................... 69, 127, 128, 130 Operators ........................................................13 OR .............................................................8, 112 relative reference ...........................................11 REPLACE ................................................124, 125 REPT .................................................... 74, 76, 77 RIGHT ..............................................................38 ROUND ......................................................33, 34 ROW ................................................67, 120, 148 SEARCH ............................................... 87, 88, 89 SMALL ...........................................................120 SUBSTITUTE .......................... 133, 134, 135, 143 SUM .................. 29, 63, 109, 128, 130, 145, 146 T ......................................................................71 TEXT ..........................................................33, 36 TRANSPOSE ...............................................66, 69 TRIM............................................ 18, 19, 20, 134 TYPE ......................................................103, 104 UPPER ...............................................................7 VLOOKUP ................................ 48, 49, 51, 77, 94 WEEKDAY ........................................................28
www.contextures.com