Excel tips and tutorials
In Day 4 of the 30XL30D challenge, we got details about the operating environment, with the INFO function — things like the Excel version and recalculation mode.
For day 11 in the challenge, we’ll examine the CELL function, which gives you information about cell formatting, contents and location. CELL is similar to the INFO function, with a list of types that you can enter, but has 2 arguments, instead of only one.
Continue reading “30 Excel Functions in 30 Days: 11 – CELL”
For day 10 in the 30XL30D challenge, we’ll examine the HLOOKUP function. Not too surprisingly, this Microsoft Excel function is very similar to VLOOKUP, and works with items that are in a Horizontal list.
Poor HLOOKUP isn’t as popular as its sibling, VLOOKUP, though, because most tables are set up with the lookup values listed vertically.
When was the last time that you wanted to do a horizontal lookup?
Do you ever need to search for a value horizontally, across a row, and then return a value from that column, in a specific row below?
Anyway, let’s give HLOOKUP its moment in the spotlight, and take a look at its information and examples.
Remember, if you have other tips or examples, please share them in the comments.
The HLOOKUP function looks for a value horizontally, in the first row of a table, and then it returns another value from a different row, in the same column, in that table.
The HLOOKUP function can either find exact matches in the lookup row, or it can find the closest match. For example, an HLOOKUP formula can:
The HLOOKUP function has the following syntax (sequence of arguments):
Like VLOOKUP, the HLOOKUP function can be slow, especially if you are 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, instead of an exact match.
Tip: You can use MATCH or COUNTIF to check for the value first, to make sure it is in the table’s first row. This can make the calculation much faster in some situations.
Other Excel functions, such as INDEX and MATCH, can be used to return values from a table, instead of HLOOKUP, and those functions are more efficient.
We will look at the INDEX and MATCH functions later in the 30 Day challenge, and you will see examples of how flexible and powerful those functions are.
Tip: See examples for INDEX and MATCH on my Contextures site: INDEX and MATCH Functions
Also, see these pages on my site, for examples of related lookup functions:
The HLOOKUP function always looks for a value in the top row of the lookup table.
In this example, we will find the sales total for a selected region — East, West, North or South. Those region names are in the first row of the lookup range, and they are NOT sorted in ascending order.
For our financial report, it is important to get the correct sales amount as our formula result, so the following settings the HLOOKUP function will find an exact match.
Here are the details on the Excel worksheet setup for this example. You can see the worksheet in the screen shot below:
The formula in cell C7 is:
Sometimes, you might get an error resulet with an HLOOKUP formula, as you can see in the screen shot below.
In this case, the region name, “Central”, was not found in the first row of the lookup table, so the HLOOKUP formula result is an error value —Â #N/A
In most cases, an exact match is required when using the HLOOKUP function, but sometimes an approximate match works better.
For example, if rates change at the start of each quarter, only those quarterly dates are entered as column headings. It would not be practical to enter every single date in the heading row!
Instead of trying to find an exact match for the date, with HLOOKUP and an approximate match, you can find the rate that went into effect on the closest date.
Here are the details on the worksheet setup for Example 2. You can see the worksheet in the screen shot below:
In this example:
The formula in cell D5 is:
Here is how the approximate match works, in this example:
For more HLOOKUP examples, and for tips on fixing HLOOKUP formula problems, go to this page on my Contextures site:
How to Use Excel HLOOKUP Function and Fix Problems
On that page, you’ll see how to:
To see both of the formulas used in today’s 30 Functions in 30 Days challenge examples, you can download the HLOOKUP function sample workbook.
The file is zipped, and is in Excel’s xlsx file format. The workbook does not contain any macros.
Tip: Use this workbook to follow along with the HLOOKUP video, in the next section.
To see a demonstration of the examples in the HLOOKUP function sample workbook, you can watch this short Excel video tutorial.
_______________________________
For day 9 in the 30XL30D challenge, we’ll examine the VLOOKUP function. As you can guess by its name, this is one of the Lookup functions, and works with items that are in a Vertical list.
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.
So, let’s take a look at the VLOOKUP information and examples, and if you have other tips or examples, please share them in the comments. And remember to guard your secrets!
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.
The VLOOKUP function can find exact matches in the lookup column, or the closest match, so it can:
The VLOOKUP function has the following syntax:
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. We’ll look at those functions later in the challenge, and see how flexible and powerful they are.
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 settings are used:
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
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:
The formula in cell D9 is:
=VLOOKUP(C9,C3:D7,2,TRUE)
If the 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.
The lookup value in this example is 77. That value is not in the percentage column, so the value for 75 (B) is returned.
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 formula in cell C7 is:
=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 VLOOKUP formula result is 0.
To see the formulas used in today’s examples, you can download the VLOOKUP function sample workbook. The file is zipped, and is in Excel 2007 file format.
To see a demonstration of the examples in the VLOOKUP function sample workbook, you can watch these 2 short Excel video tutorials.
_____________
Yesterday, in the 30XL30D challenge, we found character code numbers with the CODE function, and used it to reveal hidden characters.
For day 8 in the challenge, we’ll examine the CODE function’s evil twin — the CHAR function.
Continue reading “30 Excel Functions in 30 Days: 08 – CHAR”
Congratulations! You’ve made it to the first weekend in the 30XL30D challenge, including yesterday’s investigation of the FIXED function.
We’ll take it easy today, and look at a function that doesn’t have too many examples — the CODE function. It can work with other functions, in long, complicated formulas, but today we’ll focus on what it can do on its own, and in simple formulas.
So, let’s take a look at the CODE information and examples, and if you have other tips or examples, please share them in the comments. Wear your secret deCODEr ring, if you have one.
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.
The CODE function can help unravel mysteries in your data, such as:
The CODE function has the following syntax:
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 O
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.
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(RIGHT(C3,1)))
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 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.
To see the formulas used in today’s examples, you can download the CODE function sample workbook. The file is zipped, and is in Excel 2007 file format.
To see a demonstration of the examples in the CODE function sample workbook, you can watch this short Excel video tutorial.
_____________
Yesterday, in the 30XL30D challenge, we picked an item from a list with the CHOOSE function, and learned that other functions might be a better MATCH when doing a LOOKUP.
For day 6 in the challenge, we’ll examine the FIXED function, which formats a number with decimals and commas, and returns the result as text.
Continue reading “30 Excel Functions in 30 Days: 06 – FIXED”
Yesterday, in the 30XL30D challenge, we got details on our operating environment, with the INFO function, and learned that it can no longer help with our memory issues. (Neither ours, nor Excel’s!)
For day 5 in the challenge, we’ll examine the CHOOSE function.
Continue reading “30 Excel Functions in 30 Days: 05 – CHOOSE”
Yesterday, in the 30XL30D challenge, we cleaned things up with the TRIM function, and learned that it’s no SUBSTITUTE for calorie counting.
For day 4 in the challenge, we’ll examine the INFO function. Excel Help warns us to be careful with this function, or we could reveal private information to other users.
So, let’s take a look at the INFO information and examples, and if you have other tips or examples, please share them in the comments. And remember to guard your secrets!
The INFO function shows information about the current operating environment.
The INFO function can show information about the Excel application, such as:
In previous versions of Excel you could also get memory information (“memavail”, “memused”, and “totmem”), but those type_text items are no longer supported.
The INFO function has the following syntax:
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!
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.
=IF(C2+0<14,”Time to upgrade”,”Latest version”)
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, an add-in is running, and it has two worksheets, and the visible workbook has five worksheets. The total sheets returned by the INFO function is seven.
=INFO(“numfile”)
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 data validation drop down list in cell B3, and the INFO function refers to it.
=INFO(B3)
When “recalc” is selected, the result shows that the current recalculation mode is Automatic.
To see the formulas used in today’s examples, you can download the INFO function sample workbook. The file is zipped, and is in Excel 2007 file format.
To see a demonstration of the examples in the INFO function sample workbook, you can watch this short Excel video tutorial.
_____________
Yesterday, in the 30XL30D challenge, we took a poke at the lazy brother-in-law function — AREAS. It’s not used much in the working world, but you saw how the 3 reference operators work, so I hope that was useful!
For day 3 in the challenge, we’ll examine the TRIM function. In January, some people are trying to TRIM a few pounds, and my Excel Calorie Counter and Excel Weight Loss Tracker workbooks are popular.
Unfortunately, the TRIM function won’t help with the pounds, but can remove extra spaces from a text string.
So, let’s take a look at the TRIM information and examples, and if you have other tips or examples, please share them in the comments. And good luck with the calorie counting!
The TRIM function removes all the spaces in a text string, except for single spaces between words.
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:
The TRIM function has the following syntax:
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.
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 TRIM function in cell C7 removes those 4 spaces.
=TRIM(C5)
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.
The TRIM function in cell C7 removes those extra spaces, as well as the 2 spaces at the start and 2 spaces at the end of the text string.
=TRIM(C5)
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, and that is not trimmed.
=TRIM(C5)
You can manually delete the non-breaking space character, or use the SUBSTITUTE function or a macro. You’ll see other ways to clean up your data during the 30 Excel Functions in 30 Days challenge.
To see the formulas used in today’s examples, you can download the TRIM function sample workbook. The file is zipped, and is in Excel 2007 file format.
To see a demonstration of the examples in the TRIM function sample workbook, you can watch this short Excel video tutorial.
_____________
Yesterday we started the 30XL30D challenge with the action-packed, fun-filled, EXACT function. It had several examples and ways to apply it in your own workbooks.
Today we’ll investigate the AREAS function, and it’s a bit lighter in the usefulness department (to put it politely). It’s like the lazy brother-in-law, in a bad sitcom, who lies on your couch, and drinks your beer.
But, even a layabout can have a purpose. That lazy brother-in-law can serve as an example for your children, of how NOT to behave. As for the AREAS function, we can use it to see how the 3 different reference operators work, and how they affect the formula results.
So, let’s take a look at the AREAS information and examples, and if you have other examples, please share them in the comments. But don’t send me your brother-in-law!
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.
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:
The AREAS function has the following syntax:
When entering references, you can use any of the 3 reference operators:
| : | 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 |
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.
You can use the AREAS function with a simple range reference, and the count will be 1.
=AREAS(G2:H2)
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 counted as separate areas, so the formula result is 2.
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))
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.
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.
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 non-contiguous 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.
To see the formulas used in today’s demo, you can download the AREAS function sample workbook. The file is zipped, and is in Excel 2007 file format.
Try to use each function in your own workbooks. Then, 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 remember it better.
To see a demonstration of the examples in the AREAS function sample workbook, you can watch this Excel video tutorial.
_____________
