Excel tips and tutorials
Welcome to the Contextures 30 Excel Functions in 30 Days (30XL30D) challenge. Thanks for voting for your favourite functions, and we will cover the top 30 Excel functions (based on total votes), from the following categories:
Continue reading “30 Excel Functions in 30 Days: 01 – EXACT”
Do vampires prefer a specific blood type? Type A? Type B? Type AB? Are you positive? During the holidays, they might drink glögg, or Cosmopolitans!
Anyway, Marsha probably isn’t a vampire, but she wants to choose A or AB when doing a VLOOKUP. Here’s how you can combine values for Excel VLOOKUP formulas.
Your great suggestions in the Improve Your Excel Skills comments got me thinking. What skills would I like to improve in 2011?
It’s hard to pick just one thing, but I’ll start with Excel functions. Even the functions that you use every day can have hidden talents, and pitfalls that you aren’t aware of. And there are so many functions, that you probably only use a fraction of them.
So, in January, let’s explore 30 Excel functions in 30 days. Yes, you’re right — there are 31 days in January. However, I’m being kind, and will give you the first day off, to recover from your hangover, and/or post-holiday exhaustion. 😉
For this challenge, we’ll stick to functions in the Text, Information and Lookup and Reference functions, listed below.
There are about 60 functions in the list, and we can only cover 30, so please vote for your favourites. If you can’t see the list below, click here to go to the form.
Deadline for voting is Wednesday, December 29, 2010, at 5 PM (Toronto time).
We’ll start the challenge on January 2nd, and go till January 31st. Please check the blog every day during the challenge, and add your tips and comments, or even a HYPERLINK. There’s no SUBSTITUTE for team work, to ensure we ADDRESS all AREAS of each function.
My mind ISBLANK now, and I can’t FIND any more puns, so I’ll sign off now, and let you CHOOSE your functions. Thanks!
Update: Thanks for voting before the December 29th deadline — votes are no longer being accepted.
___________
Last week on the Bacon Bits blog, Mike Alexander showed how to send an email with the HYPERLINK function in Excel, complete with subject line and message.
Mike’s article showed how versatile the HYPERLINK function can be, and you also learned about Mike’s unique talent for poetry.
In the steps below, I’ll show you how to get the URL from an Excel Hyperlink.
Sometimes, working with an Excel data import can be a rocky horror (text) show. This month, I’ve been working with a client who is pulling together data from several accounting systems.
The project is extra exciting because each system stores the data in a different format, and we have to assemble it into a common file.
I’m sure you’ve had to deal with a similar challenge, and used your mad scientist Excel skills to clean up the mess.
In one of the import files that my client uses, the date is stored in a YYYYMMDD format.
From that number, we have to calculate the transaction date, so Excel can understand it.
You can use a few Excel functions to extract the year, month and day, and turn that time warp into a valid date.
To quote the “Time Warp” song lyrics:
In the screen shot below, the imported date is in column A. The year is at the left, in the first four characters.
Use the Excel LEFT function to pull those 4 digits into column C, to show the year.
The transaction day is shown in the two characters at the right of the date in column A.
Use the Excel RIGHT function to pull those two digits into column E, to show the day.
The final step is to use the Excel MID function to pull a specific number of characters from the middle of the string in cell A2.
The month number starts at the 5th character, and is 2 characters long.
To see a few more Excel text functions, and a sample workbook, you can visit the Split Address With Formulas page on my Contextures website
Also, see the see the Date Format Troubleshooting Tips page, for date troubleshooting tips.
To see the steps for using the LEFT, RIGHT and MID functions in Excel, to get a valid data from a date string, you can watch this short Excel video tutorial.
____________
Over at Chandoo’s Excel blog, he’s celebrating VLOOKUP week, with helpful posts like VLOOKUP Formulas Go Wild. Who knew an Excel formula could go wild?
I’ve seen many workbooks where things have run amok, but fortunately, Chandoo’s examples are much better behaved.
You don’t need any special equipment or fancy telescopes to do a lookup in Excel — you just need a simple formula.
In my videos below, see how to use the VLOOKUP function, and overcome its few shortcomings with other functions, like INDEX and MATCH.
I’m a big fan of VLOOKUP too, and have made several Excel VLOOKUP videos that show you how to use the function in different scenarios.
First, here’s a video that shows a basic VLOOKUP formula to get the correct price from a product price lookup table.
___
Next, here’s a video that shows you how to use the Excel MATCH function within a VLOOKUP formula, to make it more flexible.
The next video show you how to use VLOOKUP to change a student’s numeric score into a letter grade, by using the Approximate Match option.
You can head over to my Contextures website, and see more VLOOKUP function examples and videos.
That page also has tips for troubleshooting VLOOKUP problems, like the formulas shown in the screen shot below.
After the list was sorted, the VLOOKUP formulas ended up in the wrong order, and show the wrong product prices!
With all this information on VLOOKUP to read and watch, you won’t have to worry about planning any other weekend activities.
Chandoo’s VLOOKUP Week might end today, but you can continue celebrating, by creating your own VLOOKUP Weekend. Have fun!
____________
To fill blank cells, or delete rows with blanks cells, you can use Excel’s Go To Special feature.
For example, in the worksheet shown below, you might want to fill in all the blanks in column B, by copying the value from the row above.
There are instructions on the Contextures website to fill blank cells, by using Go To Special to select the blanks.
You can do this manually, and there’s sample code to make the job easier.
This technique works very well, unless you’re trying to fill blank cells in a long list. In that case, you might see the error message, “Selection is too large.”
This happens in Excel 2007, and earlier versions, because there is a limit of 8192 separate areas that the special cells feature can handle. (This problem has been fixed in Excel 2010.)
There are details on Ron de Bruin’s website: SpecialCells Limit Problem.
If you run into this error, you can work with smaller chunks of data instead.
On the Contextures website, Fill Blank Cells Macro – Example 3 checks for the number of areas, using Ron’s sample code, and uses a loop if necessary.
The code is shown below, and it shows a message box if the range is over the special cells limit. You can remove that line — it’s just there for information.
Sub FillColBlanks()
'https://www.contextures.com/xlDataEntry02.html
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
'2010-10-12 incorporated Ron de Bruin's test for special cells limit
'https://www.rondebruin.nl/specialcells.htm
Dim wks As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim LastRow As Long
Dim col As Long
Dim lRows As Long
Dim lLimit As Long
Dim lCount As Long
On Error Resume Next
lRows = 2 'starting row
lLimit = 8000
Set wks = ActiveSheet
With wks
col = ActiveCell.Column
'try to reset the lastcell
Set rng = .UsedRange
LastRow = .Cells. _
SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
lCount = .Columns(col) _
.SpecialCells(xlCellTypeBlanks) _
.Areas(1).Cells.Count
If lCount = 0 Then
MsgBox "No blanks found in selected column"
Exit Sub
ElseIf lCount = .Columns(col).Cells.Count Then
'this line can be deleted
MsgBox "Over the Special Cells Limit"
Do While lRows < LastRow
Set rng = .Range(.Cells(lRows, col), _
.Cells(lRows + lLimit, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
rng.FormulaR1C1 = "=R[-1]C"
lRows = lRows + lLimit
Loop
Else
Set rng = .Range(.Cells(2, col), _
.Cells(LastRow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
rng.FormulaR1C1 = "=R[-1]C"
End If
'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End With
End With
End Sub
_______________
Earlier this week, you read about the Top 100 Canadian Singles, and saw the pivot table that summarized the top songs by decade.
In the comments, Martin mentioned the FLOOR function, that I used to calculate each song’s decade, based on its release year.
Martin also pointed out that the files weren’t downloading, and I finally managed to fix that — sorry about the inconvenience.
Take my advice, and don’t work on your blog while travelling, if you can avoid it! Things that work perfectly at home, refuse to cooperate when you’re on the road.
The Excel FLOOR function rounds numbers down, toward zero, based on the multiple of significance that you specify. In the Canadian Music file, the decade is being calculated, so 10 is used as the multiple.
In column B, you can see the result of the FLOOR function, rounding down the year for each song, to show the song’s decade.
In the FLOOR function, if the number and multiple have different signs, the result is the #NUM! error. The FLOOR function works well in the music example, because the song’s year is always a positive number.
If you’re working with a list that contains both positive and negative numbers, you could use the SIGN function to calculate the number’s sign, and change the multiple to match it.
=FLOOR(A2,SIGN(A2)*10)
The Excel SIGN function result is 1 for positive number, -1 for negative numbers, and 0 for zero.
And finally, for your Friday listening pleasure, here is the second song on the Top 100 Canadian Singles list — Neil Young playing Heart of Gold.
____________
You can use the SUBTOTAL function to count visible items in a filtered list. In today’s example, AlexJ shows how to count the unique visible items in a filtered list. So, if an item appears more than once in the filtered results, it would only be counted once. Thanks, AlexJ!
Continue reading “Count Unique Items in Excel Filtered List”
On the Consumerist website last week, they posted Lauren’s Excel budget template, so I downloaded it, to take a look.
I’d call it an Expense Tracker, rather than a “Budgeter”, because it’s used to record income and expenses. (Do you know the origin of the word “budget”? I had to look it up.)
Here’s what it looks like, with part of the formula for the Total cell showing in the formula bar.
The grey fill colour is added with conditional formatting.
Shown below is the full formula for the Total.
You can see that Lauren has named the date headings (_8_10d) and hidden total row (_8_10) for each month.
Wow! It makes me tired just looking at that. Lauren created a lot of named ranges, to set up the file, and she’ll need to do more work to add more months.
Because there’s a separate section for each month, her formula needs a SUMIF formula for each range.
She might have to upgrade from Excel 2003, or she’ll pass the character limit for that formula.
I don’t know who Lauren is, but she should be commended for setting this up, and keeping track of her income and expenses.
Sure, there are many ways to improve her Budgeter, but it seems to work okay, even if it is a bit convoluted. At least she knows where her money is going!
But, there must be better ways to keep track of income and expenses. How would you set up an Excel workbook to do this?
I’d probably create a simple list, with columns for Date, Item, Location, Category and Amount, like the table in the screen shot below.
The last column calculates the year and month, so it’s easy to summarize by month.
You could even get fancy, and add data validation to the Category column, with a drop down list of valid categories.
Next, enter all your budget items, then create a pivot table to summarize your spending.
____________
