Excel tips and tutorials
Thanks for participating in the 30XL30D challenge, that ended yesterday.
My favourite Excel function in the series was INDEX — I learned a few useful tricks while researching that one! How about you?
Continue reading “30 Excel Functions in 30 Days: Conclusion”
Congratulations! You made it to the final day in the 30XL30D challenge. It’s been an long, and interesting, journey, and I hope you learned a few useful things about Excel functions along the way.
Tomorrow, I’ll do a wrap-up article, and let you know how the functions ranked in the pre-challenge voting, last month.
For day 30, we’ll examine the INDIRECT function, which returns the reference specified by a text string.
This is 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.
So, let’s take a look at the INDIRECT information and examples, and if you have other tips or examples, please share them in the comments.
The INDIRECT function returns the reference specified by a text string.
The INDIRECT function returns the reference specified by a text string, so you can use it to:
The INDIRECT function has the following syntax:
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:C7)
In cell E8, the INDIRECT function creates a reference to the starting cell, E2:
=SUM(INDIRECT(“E2”):E7)
If a row is inserted at the top of the lists, and January amounts are entered, the total in column C doesn’t change. The formula changed, adjusting to the inserted row:
=SUM(C3:C8)
However, the INDIRECT function locked the starting cell to E2, so the January amount is automatically included in the column E total. The ending cell changed, but the starting cell wasn’t affected.
=SUM(INDIRECT(“E2”):E8)
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 the SUM function, as you can see in cells E3 and E4
=SUM(NumList) or =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))
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.
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 — ‘MyLinks’!R2C2
=INDIRECT(“‘” & B3 & “‘!R” & C3 & “C” & D3,FALSE)
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. The numbers could be typed in the formula, as they are in cell D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
If you need a bigger array of numbers, you probably wouldn’t want to type all of them. Another option is to use the ROW function, as in the array-entered formula in cell D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
A third option is to use the ROW function with INDIRECT, as in the formula in cell D6, 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 second formula returns an incorrect result, because the rows are adjusted. Now, instead of the average of the top 3 numbers, it shows the average of the 3rd, 4th and 5th largest numbers.
With the INDIRECT function, the third formula keeps the correct row reference, and continues to show the correct result.
To see the formulas used in today’s examples, you can download the INDIRECT function sample workbook.
The file is zipped, and is in Excel 2007 file format.
To see a demonstration of the examples in the INDIRECT function sample workbook, you can watch this short Excel video tutorial.
_____________
Yesterday, in the 30XL30D challenge, we jumped around a workbook, and opened Excel files and websites, by using the HYPERLINK function.
For day 29 in the challenge, we’ll examine the CLEAN function. Sometimes the data you get from a website, or in a download file, has some unwanted characters, and the CLEAN function can help you fix it.
It doesn’t do much heavy lifting though, and refuses to help with the mess that the kids make. This will be perfect for a lazy Sunday!
So, let’s take a look at the CLEAN information and examples, and if you have other tips or examples, please share them in the comments.
The CLEAN function shows removes some non-printing characters from text — characters 0 to 31, 129, 141, 143, 144, and 157.
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:
The CLEAN function has the following syntax:
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 non-breaking space character 160, you can use SUBSTITUTE to replace them with space characters, or empty strings.
The CLEAN function works to remove some non-printing characters, such as those in the 0-30 range of the ASCII character set. In this example, I added characters 9 and 13 to the original text string from C3.
=CHAR(9) & C3 & CHAR(13)
The LEN function 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)
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),””)
To see the formulas used in today’s examples, you can download the CLEAN function sample workbook. The file is zipped, and is in Excel 2007 file format.
To see a demonstration of the examples in the CLEAN function sample workbook, you can watch this short Excel video tutorial.
_____________
Yesterday, in the 30XL30D challenge, we replaced text with the SUBSTITUTE function, and used it to create flexible reports.
For day 28 in the challenge, we’ll examine the HYPERLINK function. Instead of manually creating hyperlinks, with the command on the Excel Ribbon, you can use this function.
So, let’s take a look at the HYPERLINK information and examples, and if you have other tips or examples, please share them in the comments.
The HYPERLINK function creates a shortcut that opens a document stored on a computer, network server, intranet, or Internet.
The HYPERLINK function can open a document, or jump to a specific location, so you can:
The HYPERLINK function has the following syntax:
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, then recreate that in your link_location argument.
There are several different ways to create the text string for the 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 pound sign (#) at the start of the address indicates that the location is within the current file.
=HYPERLINK(“#”&ADDRESS(1,1,,,B3),D3)
You could also use the & operator to construct the link location. Here, the sheet name is in cell B5 and the cell is in C5.
=HYPERLINK(“#”&”‘” & B5 & “‘!” & C5,D5)
For a link to a named range in the same workbook, just use the range name as the link location.
=HYPERLINK(“#”&D7,D7)
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)
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)
To see the formulas used in today’s examples, you can download the HYPERLINK function sample workbook. The file is zipped, and is in Excel 2007 file format.
To see a demonstration of the examples in the HYPERLINK function sample workbook, you can watch this short Excel video tutorial.
_____________
Yesterday, in the 30XL30D challenge, we used the OFFSET function to return a reference, and saw that it is similar to the non-volatile INDEX function.
For day 27 in the challenge, we’ll examine the SUBSTITUTE function. Like the REPLACE function, it replaces old text with new text, in a text string, but can replace multiple instances of the same text.
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.
So, let’s take a look at the SUBSTITUTE information and examples, and if you have other tips or examples, please share them in the comments.
The SUBSTITUTE function replaces old text with new text, in a text string. It will replace all instances of the old text, unless a specific occurrence is selected, and SUBSTITUTE is case sensitive.
The SUBSTITUTE function replaces old text with new text, in a text string, so you could use it to:
The SUBSTITUTE function has the following syntax:
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” in the title text will be replaced with the region name, selected in cell D13.
=SUBSTITUTE(RptTitle,”yyy”,D13)
When you copy data from a website, there might be hidden, non-printing space characters in the text.
If you try to remove 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 with a normal space character. Then, use TRIM to remove all the extra spaces.
=TRIM(SUBSTITUTE(B3,CHAR(160),” “))
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 LEN function calculates the number of characters in cell B3. The SUBSTITUTE function replaces all the space characters with empty strings, and the second LEN function finds the length of the revised string. The length is 2 characters shorter, so there are two spaces.
=LEN(B3)-LEN(SUBSTITUTE(B3,” “,””))
In cell D3, the SUBSTITUTE function replaces only the 2nd space character with the new text – ” | ”
=SUBSTITUTE(B3,” “,” | “,C3)
Instead of using two columns for this formula, you could combine them into one long formula.
=SUBSTITUTE(B3,” “,” | “,LEN(B3)-LEN(SUBSTITUTE(B3,” “,””)))
To see the formulas used in today’s examples, you can download the SUBSTITUTE function sample workbook. The file is zipped, and is in Excel 2007 file format.
To see a demonstration of the examples in the SUBSTITUTE function sample workbook, you can watch this short Excel video tutorial.
_____________
Yesterday, in the 30XL30D challenge, we changed text strings with the REPLACE function, and learned that it can insert characters too.
For day 26 in the challenge, we’ll examine the OFFSET function. From a starting reference, it returns another reference, of a specified size, and offset by a set number of rows and columns.
So, let’s take a look at the OFFSET information and examples, and if you have other tips or examples, please share them in the comments.
The OFFSET function returns a reference offset from a given reference.
The OFFSET function can return a reference to a range, and be combined with other functions. Use it to:
The OFFSET function has the following syntax:
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.
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:
=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.
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 selected month number is 3, so the result is the total of the March sales.
=SUM(OFFSET(A3:A6,0,B10))
You can also use the OFFSET function to create a dynamic range. In this example, I’ve created a name, MonthsList, with this formula:
=OFFSET(‘Ex03’!$C$1,0,0,COUNTA(‘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 MonthsList as its source.
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.
=SUM(OFFSET(C2,COUNT(C:C)-E3+1,0,E3,1))
To see the formulas used in today’s examples, you can download the OFFSET function sample workbook. The file is zipped, and is in Excel 2007 file format.
To see a demonstration of the examples in the OFFSET function sample workbook, you can watch this short Excel video tutorial.
_____________
Yesterday, in the 30XL30D challenge, we used the INDEX function to return a value or reference, based on a row and/or column number.
For day 25 in the challenge, we’ll examine the REPLACE function, which is in the Text category. It replaces a specified number of characters in a text string, with new text.
Continue reading “30 Excel Functions in 30 Days: 25 – REPLACE”
Yesterday, in the 30XL30D challenge, we found text strings with the FIND function, and learned that it is case sensitive, unlike the SEARCH function.
Continue reading “30 Excel Functions in 30 Days: 24 – INDEX”
Yesterday, in the 30XL30D challenge, we had a light day, with the N function, and learned that can return a number, based on a value.
For day 23 in the challenge, we’ll examine the FIND function. It’s similar to the SEARCH function, which we saw on Day 18, but the FIND function is case sensitive.
So, let’s take a look at the FIND information and examples, and if you have other tips or examples, please share them in the comments.
Continue reading “30 Excel Functions in 30 Days: 23 – FIND”
Yesterday, in the 30XL30D challenge, we identified cell contents with the TYPE function, and used it to check for a number, before multiplying.
For day 22 in the challenge, we’ll examine the N function. It’s similar to the T function that we saw earlier, but checks for numbers instead of text.
