Excel Data Validation Update

I’ve finally updated my Data Validation intro video, so it shows the steps for creating a drop down list in Excel 2010, instead of Excel 2003.

Note: These instructions apply to Excel 2007 too, in case you’re using that version.

Data Validation Drop-Down List
Data Validation Drop-Down List

Data Validation Articles

In honour of this momentous occasion, here are links to a few of my previous Excel Data Validation articles and posts.

You probably know all the basics, but maybe you’ve missed a few of these tips and tricks.

Show or Hide User Tips In Excel – AlexJ shows how to let users turn data validation messages on or off, by choosing TRUE or FALSE from a drop down list.

Dependent Data Validation From a Sorted List – Select an item in the first drop down list, and related items are shown in the second drop down list.

Limit the Total Amount Entered in Excel – use Data Validation to limit the total amount that users enter in a group of cells.

Select Multiple Items from Excel Data Validation List – instead of selecting just one item from a data validation drop down, you can select two or more.

Plan Your Party Seating with Excel – too late for Christmas dinner, but this might help with your New Year’s festivities.

Note: Remember to vote for the Excel functions that you’d like to learn more about during the 30 Excel Functions in 30 Days challenge, starting January 2nd.

Watch the Excel Drop Down List Video

The shiny new video is below, in case you’d like to see the steps for making a drop down list in an Excel worksheet.

________________

Combine Values for Excel VLOOKUP

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.

Continue reading “Combine Values for Excel VLOOKUP”

2011 Challenge: 30 Excel Functions in 30 Days

Icon30Day 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. 😉

CHOOSE Your Functions

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).

Share Your INFO

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.
___________

Use Excel Scroll Bar to Trim Christmas Tree

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!

Trim the Tree

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.

Useful Excel Features

It’s not just for the holiday season though — the sample file has useful features that you can adapt to other workbooks too:

  • Scroll bar lets users change a number quickly and easily
  • A text box that displays a changing message based on VLOOKUP formula
  • conditional formatting shows hidden cells when target number is reached
  • named ranges make it easy to work with specific cells
Use Excel Scroll Bar to Trim Christmas Tree
Use Excel Scroll Bar to Trim Christmas Tree

Watch the Video

To see how the Excel Christmas tree trimming scroll bar works, you can watch this short Excel video.

Excel Scroll Bar Sample File and Instructions

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.

Improve Your Microsoft Excel Skills

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!

Your Suggestions

What would you add to that list of ways to improve your Excel skills?

Excel AutoFilter With Criteria in a Range

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”

Get the URL from an Excel Hyperlink

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.

Continue reading “Get the URL from an Excel Hyperlink”

Excel 2007 AutoFilter Dynamic Dates

icondynamic 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.

Improvements in AutoFilters

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.

Filter for Dynamic Date Ranges

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.

AutoFilter Dynamic Date Range settings
AutoFilter Dynamic Date Range settings

Update Filters

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.

autofilter2007_16

Learn More About Excel 2007 AutoFilters

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.
_____________

Excel Weekly Meal Planner With Recipe Selector

imageThe 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?)

Excel Weekly Meal Planner

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.

Find a Recipe

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.

Find recipes for specific ingredient
Find recipes for specific ingredient

Remodelling the Excel Weekly Meal Planner

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.

Add a Recipes Sheet

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.

Get the Update Version

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.

excelmealplan03

Watch the Excel Weekly Meal Planner Video

To see a quick overview of how the Excel Weekly Meal Planner works, you can watch this short video.

___________