Excel tips and tutorials
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.
___________
An Excel scroll bar can be used for practical (and sometimes boring) things, like testing the effect of price changes, or adjusting a chart’s date range.
But this is the festive season, so let’s use a scroll bar for something more, well, festive!
In this example, instead of accounting and finance, you’ll see how to use an Excel scroll bar to decorate a Christmas tree, without macros.
Unfortunately, this Excel file can’t make hot chocolate or eggnog, so you’ll need to provide your own.
It’s not just for the holiday season though — the sample file has useful features that you can adapt to other workbooks too:
To see how the Excel Christmas tree trimming scroll bar works, you can watch this short Excel video.
For instructions on creating the Excel scroll bar file, and to download the sample file, go to my Contextures website: Excel Scroll Bar Christmas Tree Example.
Someone emailed me this week, and asked how he could improve his Excel skills.
Here’s what I suggested:
Books: Read Excel books – there’s a list of my favourites on my Contextures site, and you can browse your local bookstore, or search in Amazon, to see what’s new
Blogs: Follow a few Excel blogs, to see what topics people are writing about. You might learn about new Excel features, or see helpful tips for familial features
Websites: Visit some of the Excel expert websites, for Excel tips, tricks, videos, and sample files. I highly recommend the Contextures website, but I might be biased. 😉
Videos: Another great way to improve your Excel skills is by watching videos. There are lots of videos on my Contextures site, and on my Contextures YouTube channel.
Experiment: And remember keep trying new things in your own Excel files! That’s my favourite way to learn. Just be sure to do your tests on a backup copy of your files, just in case things go horribly wrong!
What would you add to that list of ways to improve your Excel skills?
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.
____________
