Excel tips and tutorials
In Excel 2003, and earlier versions, an AutoFilter allows only two criteria for each column. In Excel 2007 and later, you can select multiple criteria from each column in the table. See how to apply an Excel AutoFilter with multiple criteria in a range on the worksheet.
Update: Get the latest version of this workbook on my Contextures site: Filter Criteria List Macro.
Continue reading “Excel AutoFilter With Criteria in a Range”
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.
I’ve posted a few versions of the Excel Worksheet Data Entry Form, starting with the original version that Dave Peterson created. Thanks for your comments and suggestions, which give me ideas for enhancing it.
Continue reading “How to Customize the Excel Data Entry Form”
Over on the Contextures website, I’ve updated the AutoFilter Intro page, so it now covers the basics for Excel 2007 AutoFilters.
However, many people are still using an older version of Excel, so I’ve moved the original material to the Excel 2003 AutoFilter Basics page.
AutoFilters are easier to use in Excel 2007 and Excel 2010, and the filter and sort options are automatically added in the top row, if you format your list as an Excel Table.
Among the new AutoFilter features that were introduced in Excel 2007 are dynamic date ranges.
A Dynamic Date Range is one that changes automatically, as time moves forward.
For example, you could select Yesterday, which will represent a different date, every day that you open the Excel file.
Unfortunately, the dynamic dates are only semi-dynamic, and they don’t magically change when you open the workbook at a later date. You’ll need to update the filter to see the current information.
You can update the Excel 2007 AutoFilter manually, by clicking Reapply on the Excel Ribbon. Or, you could add a bit of code to the Workbook_Open event, to reapply the filters automatically.
If you’re not familiar with the new features in Excel 2007 and Excel 2010 AutoFilters, you can learn more at Excel 2007 AutoFilter Basics.
_____________
The problem with putting a lovely red geranium on your table is that you can end up remodelling the kitchen! (Do you remember that magic geranium fable?)
Anyway, a while ago, I posted my Excel Weekly Meal Planner, which let you select meals for each weeknight, and print a grocery shopping list.
All was well in my kitchen, as I happily planned my meals with that useful workbook.
Then, JP from Code for Excel and Outlook, sent me a “geranium” — a copy of my Excel Christmas Planner, with a fancy new Excel Recipe Selector worksheet.
JP’s workbook has code that looks up recipes via web API, and returns the result to Excel.
For example, if you’re tired of steamed carrots, you can enter “Carrots” in the Recipes worksheet, and find more exotic recipes online.
Thanks JP! Your enhancement works great, and now it will be easy to spice up those holiday meals.
Yes, JP’s Recipe Selector makes the Holiday Dinner Planner better, but it made my Excel Weekly Meal Planner look shabby!
It needed a recipe selector too. In his blog post, JP showed the code for using the API, and had a link to another function that’s required.
So, I copied the Recipes sheet from JP’s workbook, and inserted it into my weekly meal planner file. It worked great in its new location, and I just had to change a couple of references in the code.
But…now the rest of the weekly meal planner looked a bit run down, so I decide to remodel it. After a couple of coats of paint, a new backsplash and a shiny new pivot table, it’s ready for the open house.
You can tour the remodelled version on the Excel Weekly Meal Planner page on the Contextures website, and download a copy to help plan your meals.
To see a quick overview of how the Excel Weekly Meal Planner works, you can watch this short video.
___________
In some workbooks, you want users to select one or more from a list of options.
To make it easier for people to enter data, you can add a List Box, with check boxes, to an Excel worksheet.
You wouldn’t want to have too many List Boxes on a worksheet, but it would be handy in a data entry form, like this one.
On the Contextures website, there are instructions for setting up a List Box, and a sample workbook.
You can enter records, one at a time, on the data entry sheet, and run the macro to store the saved records another sheet.
The List Box macro code pulls the selected items from the List Box, and stores them in separate cells.
To see the steps for creating a List Box on a worksheet, you can watch this short Excel video tutorial.
____________
Last December, I posted an Excel Advent Calendar, and it was surprisingly popular, throughout the year.
Today, working on my Excel file was like riding a lazy snail through molasses in January — but slower!
Usually an Excel Advanced Filter is a speedy way to extract data from a table, but things weren’t working right in a sample file that I got last week.
And despite what my high school English teachers might think, you can’t mix too many similes, when trying to describe excruciating slowness.
The sample file had code that ran an Advanced Filter in Excel 2007.
The code ran quickly in Excel 2003, but screeched to a near halt in Excel 2007. What was the problem?
In the sections below, I detailed all the things I tried, while troubleshooting the slow macro problem.
There’s a video at the end of this post too, where I show the problem and the solution.
When the code ran in Excel 2007, it looked like the extracted rows were being pasted in the second worksheet, one row at a time.
Aha! I should turn off the screen updating — a simple solution. You’d think.
Nope! Even with the screen updating turned off, the code barely crawled along.
It took almost 3 minutes to extract 1500 rows — maybe a millisecond faster than it ran with screen updating turned on. Who has that kind of time?
In the next round of solution guessing, I got rid of the few formulas in the worksheet and criteria range.
There wasn’t anything too complex, but maybe that was slowing things down.
I also changed calculation to manual at the start of the code, then set it to automatic at the end of the code.
Neither of those changes had any effect on the code’s speed.
In round 12 of testing (I’ve lost track of the test count), I copied the data, and pasted it as values into a new workbook.
The code ran like lightning. In July. With jet engines. Hmmm.
Maybe it was the formatting and styles in the original file that were slowing things down.
To test that theory, I formatted the original table with Normal style, which removed all the borders and fill colour.
That didn’t improve things, but when I removed the red fill from the heading cells, I noticed a red comment marker in one of the cells.
Could a comment be the problem? That didn’t seem likely, but:
When I tried to create a sample file to demonstrate this problem, things got even stranger.
I created a table with a comment in the heading, and ran the code, expecting it to be slow. It ran quickly, in several tests.
Next, I added a shape to the worksheet, and assigned a macro, to make it easier to run the code.
The code slowed down again!
Next, I deleted the shape, and the code was still slow, so I had to delete the comment to speed it up again.
If your Advanced Filters are running slowly in Excel 2007, try removing any comments in the table heading cells.
You could delete them at the start of a VBA procedure, run the filter, then add the comments at the end of the code.
The problem seems to occur if there are heading comments, and a shape is added later, as you can see in the short video demonstration below.
Fortunately, this problem appears to be fixed in Excel 2010, so if you upgrade, you should be able to have comments and shapes, without slowing down the Advanced Filters.
Update: In the comments, PDLobster suggests the following solution, to speed up the filters — thanks!
To see the steps for reproducing and solving the Advanced Filter speed problem, you can watch this short Excel video.
____________
Copy and paste. It’s one of the first things you learn to do in Excel, and something you do every day.
Without copy and paste, your Excel work would take much longer, and you’d be exhausted by the end of the day, from all that typing!
Here are some Excel copy and paste tips and trouble shooting suggestions.
Continue reading “Excel Copy and Paste Tips and Trouble”
Last year, I posted a link to my Excel Christmas planner, that includes a scheduler for holiday meals.
Despite the rude comments from my Excel buddies (OCD? Torturing guests with pivot tables?), I still use that planner to stay on schedule.
Maybe those doubters go out for their holiday meals, so they don’t have to worry about planning!
Even if you don’t have to plan the holiday dinner, you might want to plan your Black Friday shopping trip, or compare gift prices at different stores.
In the latest version of the Excel Christmas Planner there’s a Black Friday worksheet.
Enter Store Names at the top of the table, then enter product info and prices in the rows below.
The worksheet calculates which store has the best price for each item, and which store has the most deals.
Note: If prices are the same at multiple stores, the first store will be shown in the “Best Price” column.
With this handy Excel Holiday Shopping worksheet, you’ll know where to start your shopping blitz, so you get the most for your money.
And that’s what the holidays are all about, right? 😉
Happy Thanksgiving!
__________
