Excel tips and tutorials
It’s the middle of the week, and you’re buried under a pile of Excel workbooks. The weekend seems to be miles away.
To get you over the midweek hump, here are three examples of spreadsheet fun and games.
Continue reading “Take a Break With Excel Games”
Another humdrum day in the world of spreadsheets? Hardly!
Thanks to John Walkenbach (Mr. Spreadsheet) and Mike Alexander (DataPig), things are getting exciting.
They’ve just announced a Show Us Your Spreadsheets contest, with Excel books and gift cards going to the winners. The contest is open to residents of the USA, UK, and Canada (except Quebec).
To enter, send in a photo of yourself with one of John’s or Mike’s books, or with a fabulous Excel workbook that you created.
As an example of creative book holding, here’s John hiding behind a notebook, imitating the poses of his buddies Bill and Steve.
Be sure to read and follow the official rules, and submit your photos (up to 3) by December 3rd.
You probably have some Excel books by John and/or Mike, but if not, head to Amazon or your local bookstore, and pick one up (or a bunch!)
The best photos will be selected by John and Mike, and prizes awarded based on their decision. These multi-talented gentlemen have previous experience in judging creative events, as you can see in the photo below.
Dick is constructing an amazing tower, and you can see John (second from left) and Mike (right), judging the event. This shows that they have a keen eye for design, and are calm in the face of danger.
They seem to like beer and hockey pucks too, so that bodes well for the Canadian entries!
____________
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.
____________
Would you prefer a bigger font size for items in a data validation drop down list?
Could you save typing time, if the words were completed automatically, as you started typing them?
Last week, someone tweeted a wish for a new Excel Chart type:
Continue reading “Excel Line Chart and Scatter Chart Compared”
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!
____________
Can the sales staff and accounting staff ever work in peace? One group wants to see product descriptions, when entering orders. The other group thinks the descriptions clutter up the worksheet — they just want the product codes.
Try this data validation trick, and you might be nominated for next year’s Nobel Peace Prize. (Results not guaranteed.)
See the workbook details below, and there’s a video with step-by-step instructions at the end of the page.
To make it easier for users to enter data in an Excel workbook, you can create drop down lists in the cells, by using Excel data validation.
In this example the product list is in an Excel Table, and the ProductShow column is a named range — ProdList.
The ProdList range is used as the source for the drop down lists on the order entry sheet.
After the product is selected from the drop down list, the full description is automatically replaced by the product code. How does it happen?
It’s the magic of Excel VBA — event code that runs when the worksheet is changed.
The Excel VBA code uses the Match worksheet function to find the row number in the lookup list. It replaces the selected product description with the matching Product Code from that row in the lookup list.
Peace at last! Your co-workers will be happy that they don’t have to memorize the product codes, and the accounting department will be grateful that they get the data in the format they need.
To see the Excel VBA code that changes the product name to a product code, go to the Contextures website, and download the sample file: DV004: Data Validation Change.
The example used here is the Excel 2007 version, and there is also an Excel 2003 version of the sample file.
You can watch this video to see the steps for creating an Excel Table, naming a column in that table, then using that name when creating the data validation drop down list.
___________
People are lazy! Shocking, I know, but who wants to click twice in Excel, if you can do the same thing by only clicking once?
Peterson, champion of weary Excel users, created this sample Excel VBA sort code, that adds invisible rectangles at the top of each column in a table.
A macro is automatically assigned to each rectangle, and it sorts the table by that column, when you click it.
Here are two benefits of using Dave’s code:
There are two macros in Dave’s sample file.
Before you run the SetupOneTime macro, you should edit both macros, to adjust them for your workbook
In the SetupOneTime macro, change the iCol variable to match the number of columns in your table. If your table doesn’t start in cell A1, change that reference.
Next, change the variables in the SortTable macro, to suit your table settings. You can adjust:
If you want to see the rectangle outlines, change the Line.Visible setting to True.
After you’ve edited the macros, you can run the setup macro:
Now, click a heading in the table, to sort by that column.
When I was getting this blog post ready, I discovered that Dave’s original code needed a tweak before it would work correctly in Excel 2007 and Excel 2010.
In the original code, written for Excel 2003, there was one line of code that made the rectangular shape invisible:
.Fill.Visible = False
In the newer versions of Excel, only the borders of the invisible shapes were clickable, so I had to change the code to these two lines:
.Fill.Solid .Fill.Transparency = 1#
The revised code worked for me in Excel 2003, 2007 and 2010, creating transparent shapes that were clickable.
To see the full code for the SetupOneTime and SortTable macros, and download the sample workbook, visit the Sort Data With Excel Macros page on the Contextures website.
And for more Excel advanced sorting tips, go to the Excel Add-in Advanced Sorting page on my Contextures site.
To see the steps for editing the code, adding the rectangles, and clicking the hidden shapes, you can watch this short Excel tutorial video.
_________________
Slasher movies are a scary Halloween tradition, and you can fight back against these horror films, by using Excel Slicers to slash through piles of data.
The Guardian recently posted a list of Greatest Films of All Time. Let’s see how we can use Excel Slicers for Halloween horror films from that list.
Continue reading “Excel Slicers for Easy Pivot Table Filtering”
To make it easier to people to enter data in your Excel workbook, you can create drop down lists in the cells, by using Excel data validation. These lists will also help prevent invalid entries in your worksheets.
Continue reading “Shortcuts to Create Drop Down List in Excel”
