Excel tips and tutorials
Yesterday, in the 30XL30D challenge, we counted columns with the COLUMNS function, and now it’s time for something a bit more demanding.
For day 13 in the challenge, we’ll examine the TRANSPOSE function.
Continue reading “30 Excel Functions in 30 Days: 13 – TRANSPOSE”
Yesterday, in the 30XL30D challenge, we got cell details with the CELL function, and learned that it’s useful for a few things, like extracting a worksheet name.
COLUMNS Function
For day 12 in the challenge, we’ll examine the COLUMNS function. Will this function be as useful? Or is it just another lazy function, like AREAS? Well, it does count the columns, as promised, and plays well with others, but nothing too exotic or powerful here.
So, let’s take a look at the COLUMNS information and examples, and if you have other tips or examples, please share them in the comments.
The COLUMNS function returns the number of columns in an array or reference.
The COLUMNS function can show the size of a table or named range:
The COLUMNS function has the following syntax:
If you’re using a range reference, it must be a contiguous range.
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,
and the COLUMNS function counts the number of columns in that table.
=COLUMNS(RegionSales)
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 range is named MyRange,
and this formula sums the last column in the named range.
=SUM(INDEX(MyRange,,COLUMNS(MyRange)))
To see the formulas used in today’s examples, you can download the COLUMNS function sample workbook. The file is zipped, and is in Excel 2007 file format.
To see a demonstration of the examples in the COLUMNS function sample workbook, you can watch this short Excel video tutorial.
_____________
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.
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.
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.
_____________
