|
|
 You can use data validation to create drop down lists in Excel. With a bit of Excel magic, you can create dependent drop down lists, so the selection in one drop down controls what appears in the next drop down.
We'll take a quick look at how a basic dependent drop down works, then add flexibility by changing the data validation formula.
Select Countries and Cities
For example, in the worksheet shown below, there's a drop down to select a country in column A. If you select USA as the country, cities from the USA appear in the Column B drop down.
This is created by using the INDIRECT function in the data validation settings for column B:
=INDIRECT(A2)
There are city lists in the workbook, and each list is named to match its country name. So, if you select USA in cell A2, the INDIRECT function references the range named USA.
Change the Formula
Currently, the drop down list in column B doesn't work, unless you first select a country in column A. We'd like to give users the option to select a world city, if they haven't selected a country in column A.
On the Lists worksheet, there's a range named World, highlighted in the screen shot below.
Just as you can use the IF function on the worksheet, you can use it in a data validation formula. For the data validation cells in column B, we'll change the formula to the following:
=IF(A2="",World,INDIRECT(A2))
If cell A2 is blank (equal to an empty string ""), then show the World list. Otherwise, show the list for the country selected in cell A2.
After this change to the data validation formula, if you click on a drop down arrow in column B, and no country is selected in column A, the list of world cities appears.
Watch the Video
To see the steps for creating a basic dependent drop down list, then adding an IF function, please watch this Dependent Data Validation video tutorial.
________________
 August seems to be a very popular birthday month among my Excel friends. I won't mention any names here, because most of them are quite elderly, and the shock might upset them.  Anyway, to all of them, and you, if you're celebrating this month -- happy birthday!
The Birthday List
If you're in charge of an employee list, and have to send birthday greetings, or hiring date anniversary congratulations, you can use Excel to help you keep track.
In the worksheet shown below, there are fictitious employee names and birthdates. In a separate column you could use the MONTH function to check which birthdates are in the current month.
This formula compares the month of the date in column C, to the month of today's date. If the months are the same, the result is TRUE.
=MONTH(C2)=MONTH(TODAY())
Highlight Birthdays with Conditional Formatting
Instead of adding another column with formulas, you could use conditional formatting to highlight the current month's dates in column C.
To highlight the dates:
- Click OK to apply the conditional formatting.
Spot the Birthdays
On the worksheet, all the birthdays from the current month are highlighted with the formatting that you selected. When you open the file at the start of each month, you'll quickly spot all the people who are celebrating birthdays.
__________________
 It's Shark Week on the Discovery Channel, so here are a couple of handy Excel functions you can use in case of a shark attack. Of course, if you stay home and watch television, you should be safe. The infamous land shark rarely attacks.
That Shark is Large
Sharks are pretty big, with the shark types in this list ranging from 2 to 5 metres.
You're being attacked by the second longest shark. How long is it? To find out, you can use the LARGE function. It takes two arguments -- the array or range of numbers, and the position in that range.
In this example, the range is B2:B12, and the position is 2.
=LARGE(B2:B12,2)
If you type the position number in cell D1, you can refer to that cell in the LARGE function.
=LARGE(B2:B12,D1)
Sharks Eat Off the Floor
Yes, some sharks eat fish, and other snacks, that they find on the ocean floor. Unfortunately, a few sharks have also dined on people, including about 1000 in the USA, over the past 300 years.
From a list of shark attacks per state, you could use the FLOOR function to round down the average number of attacks, to a specific multiple. For example, you could round the average attacks (in cell E4) to a multiple of 10, with this formula:
=FLOOR(E4,10)
But if you're going to be attacked by a shark, you should make sure it happens in Georgia, where your odds of survival look pretty good.
Excel Tweet of the Day
Funny, there weren't any shark related Excel tweets, but this one made me laugh:
Dahlia and I will be working on baby's first spreadsheet today. Gotta learn Excel at some point.
For more entertaining and enlightening Excel tweets, culled from the hundreds posted every day, see Excel Theatre Blog.
You're Gonna Need a Bigger Boat
If you saw Jaws in the 70s, you probably don't need me to warn you -- don't go in the water! If you do go, take a really big boat.
_________
 Someone emailed me last week about problems with a range named Database. For reasons known only to the hamsters that operate my brain wheels, that reminded me of Excel's Data Form. It's a built-in data entry tool, that lists all the fields in a table, with entry boxes for some fields, and the formula results showing. You can scroll through the records, or find specific records, based on criteria.
It's not a feature that I use too often, and when I looked for the Data Form command in the Excel 2007 Ribbon, it was missing! It looks like I'm not the only one who doesn't use the Excel Data Form too often.
Access the Excel Data Form
Even though the Data Form command is missing from the Excel 2007 Ribbon, you can still use it. A quick way to open the Data Form is the keyboard shortcut:
- Press Alt + D, then type O
If you use the Data Form frequently, you can add its icon to the Quick Access Toolbar (QAT). There are instructions here for customizing the Excel QAT. You'll find the Form command in the "Commands Not in the Ribbon" category.
Open the Excel Data Form
To open the Data Form, select a cell in a table on the worksheet, then use the keyboard shortcut (Alt + D, O) or the icon on the QAT. It doesn't matter which cell in the table is selected -- the Data Form opens at the first record.
Use the Excel Data Form
To open the Data Form, select a cell in a table on the worksheet, then use the keyboard shortcut (Alt + D, O) or the icon on the QAT. It doesn't matter which cell in the table is selected -- the Data Form opens at the first record.
- To add a new record, click New, and enter the data
- To scroll through the records, use the Up arrow and Down arrow keys, or use the scroll bar on the Data Form.
- To edit a record, scroll to that record, and change the data in the entry boxes. While editing a record, you can click the Restore button, to restore the current values. To complete the edit, click Close, or move to the next record (scroll bar or arrow keys).
- To delete a record, scroll to that record, then click the Delete button
- To find a specific records, or the first record that meets your criteria, click the Criteria button, and enter the criteria, then click Find Prev or Find Next.
Data Form Quirks and Mysteries
Here are a few things about the database that might surprise you. If you know of any other quirks, please share them in the comments below.
- If there is a range named Database on the worksheet, it will open in the Data Form, even you have selected a cell in a different table. Ah, that's what reminded me of Data Forms, when I got the Database question.
- The width of the boxes in the Data Form is connected to the widest column in the underlying table. If you widen that column, the Data Form will adjust automatically.
- If numeric columns are too narrow, and some cells show number signs, those number signs are shown in the Data Form, even if its data entry boxes are wide enough to show the number. And unlike the worksheet, where you can point to a cell with number signs, and see a popup with the value, there's no such feature in the Data Form.
- But even the number sign quirk has a quirk! Sometimes the numbers are shown in the Data Form, even if the numbers signs show on the worksheet. In the screen shot below, the Cost column is slightly wider, and now the numbers can be seen in the Data Form.
- Data Validation lists, and other data validation settings, are ignored. For example, there's a drop down list in the Product column on the worksheet, but not in the Data Form. You can type any value in the Product cell, despite the restrictions on the worksheet.
- After editing a record, if you click Find Prev or Find Next, the changes are not saved.
Enhanced Data Form
If you like the built-in Excel Data Form, you'll like John Walkenbach's Enhanced Data Form even better. It's a free download for the full version (either Excel 2007 and later, or Excel 2003 and earlier). If you want to see the code behind it, you can buy the VBA password for the bargain basement price of $15.
- The J-Walk Enhanced Data Form automatically creates a drop down list, if the data validation list is based on a range with the same name as the field. In this example, the data validation list in column B is based on the Product named range.
- Another benefit of the J-Walk Enhanced Data Form is that it opens at the record that's currently selected.
- Also, you can drag the bottom right corner, to adjust the size of the form, and the data entry boxes.
Do You Use the Data Form?
What about you -- do you use the Excel Data Form? Did you know that it existed? Are there any other Data Form quirks that you know about?
___________
 Good news, if you're spelling challenged -- or too lazy to type long words. You can combine cell values in Excel, without using the CONCATENATE function. Keep reading, to learn the easy way to combine cells, and add some fancy formatting to the dates and numbers.
Use the & Operator
Yes, instead of using CONCATENATE, you can use the ampersand operator -- & -- to combine cell values in Excel. That's a savings of 10 characters! And in the hot weather we're having this summer, it's important to conserve your energy for more important things.
Even more good news -- you don't need to remember how to spell "ampersand", and you can even use one of its less technical names -- "Fancy And" or "Shift-7".
Combine Two Cells
In the cell where you'd like to see the combined values from two other cells:
- Type an = sign, to start the formula
- Click on the first cell that you want to combine
- Type an &
- Click on the second cell that you want to combine.
In the screenshot below, the product name and amount are being combined, and the formula is:
=B2&E2
- Press Enter, to complete the formula
The values from the two cells are combined into one continuous text string, showing the product name and price.
Add a Space Between Combined Text
Your formula to combine the product name and price cells worked as advertised, but the results would look better with a space between the product name and price. To create a space, you can include a text string in the formula.
- Select the cell with the formula
- Click after the first cell reference
- Type the & operator
- Type " " (double quote, space, double quote)
- Type the & operator
- Press Enter to complete the revision
The revised formula is:
=B2&" "&E2
The product name and price now have a space between them.
Format Numbers in Combined Cells
When you combine text with a date or number, you can format the result by using the TEXT function. The TEXT function has two arguments -- the cell reference, and the formatting. In this example, you can format the number as currency, with two decimal places.
TEXT(E2,"$#,##0.00")
- Select the cell with the formula
- Change the second cell reference, to include the TEXT function
- Press Enter to complete the revision
The revised formula is:
=B2&" "&TEXT(E2,"$#,##0.00")
The product name and price now have a space between them, and currency formatting on the number.
Help With Number Formats
If you need help with setting up the Number Format argument in the TEXT function, there are a few more examples on the Combine Cells in Excel page.
You can also format a sample cell in Excel, using the Number Format commands. Then, to see its formatting code:
- Select the formatted cell
- Press Ctrl+1, to open the Format Cells dialog box.
- On the Numbers tab, click the Custom category
- Copy the formatting from the Type box.
- Close the dialog box, and paste the formatting into the TEXT formula
Watch the Combine Cells in Excel Video
To see the steps for creating a formula to combine and format cells, please watch this short Excel video tutorial.
_____________
 How many students got a grade between 50 and 70? How many golfers scored between 70 and 80? How many orders were for a quantity between 5 and 10?
In Excel 2007 and higher, you can use the COUNTIF and COUNTIFS functions to calculate the answers to those questions. In earlier versions, COUNTIFS is not available, and you can use COUNTIF.
Count Numbers in a Range with COUNTIF
In the worksheet shown below, there's a list of items ordered, and the quantity of each item. The challenge is to count the orders where the quantity is between 5 and 10.
Unfortunately, Excel doesn't have a Between function, so you'll have to find another solution.
With two separate COUNTIF formulas, you can calculate the number of orders where the quantity is 5 or greater, and the number of orders where the quantity is greater than 10.
Then, you can combine those two COUNTIF formulas, to find the answer. The first formula shows that there are 9 orders with a quantity of 5 or higher. From that amount, you'll subtract the number of orders with a quantity greater than 10 (3 orders). Those shouldn't be included in the final count, because they're over the maximum.
=COUNTIF($B$2:$B$10,">=" & E4) - COUNTIF($B$2:$B$10,">" & G4)
The combined formulas show a result of 6 -- the number of orders with a quantity between 5 and 10.
In Excel 2007, and later versions, you can use the COUNTIFS function, and the formula is a bit simpler. Instead of using two COUNTIF functions, and subtracting one result from the other, you'll list all the ranges and criteria within one COUNTIFS formula. The result will be a count of the orders that meet all the criteria.
In this example you're counting the orders with a quantity between 5 and 10. Using the Excel operators, you want quantities that are >=5 AND<=10.
The COUNTIFS formula uses pairs of ranges and criteria, with a limit of 127 pairs. You'll use two -- the first to check for quantity >=5 and the second to test for quantity <=10.
=COUNTIFS($B$2:$B$10,">=" & E4,$B$2:$B$10,"<=" & G4)
The COUNTIFS formula shows a result of 6 -- the same as the COUNTIF formula that you created earlier.
Add More Criteria to COUNTIFS
It's easy to add more criteria to the COUNTIFS function. Currently, the COUNTIFS formula shows that 6 of the orders have a quantity between 5 and 10. You can add another range and criteria, to find only the Pen orders.
=COUNTIFS($B$2:$B$10,">=" & E4,$B$2:$B$10,"<=" & G4,$A$2:$A$10,"Pen")
You can also use wildcards in the criteria, so the following formula would count any orders where the item name starts with "Pen".
=COUNTIFS($B$2:$B$10,">=" & E4,$B$2:$B$10,"<=" & G4,$A$2:$A$10,"Pen*")
Watch the Count Numbers in a Range Video
To see the steps for creating a COUNTIF and a COUNTIFS formula, please watch these 2 short Excel video tutorials.
Yeah, I've felt like this guy too:
I think I just installed 100TB of microsoft products in order to be able to spend a couple of hours reviewing a spreadsheet.
For more entertaining and enlightening Excel tweets, culled from the thousands posted every day, see
Excel Theatre Blog.
_________________
In Excel 2003, you could create a custom toolbar, and attach it to a specific workbook. I didn't use that feature, but occasionally I receive a workbook where someone has attached a toolbar.
Custom Toolbars in Excel 2007
The Ribbon was introduced in Excel 2007, to replace the Excel toolbars. It has an Add-Ins tab, where custom toolbars and menu commands will appear.
If you open an Excel 2003 workbook that has an attached custom toolbar, that custom toolbar's tools will appear on the Add-Ins tab in the Ribbon. Things can get pretty messy, especially if those custom toolbars had more than a few buttons.
Here's just a small section of my Add-Ins tab, after opening a client's workbook.
Delete a Custom Toolbar from the Ribbon
Unfortunately, those custom toolbars don't automatically disappear when you close the workbook to which they're attached. Nope, they hang around, uninvited, cluttering up the place.
The good news is that you can delete the custom toolbars from the Ribbon, with an easy mouse shortcut.
To delete a custom toolbar from the Ribbon:
- Right-click on one of the custom toolbar's commands
- Click Delete Custom Toolbar
- When the warning message appears, click Yes, to confirm that you want to delete the custom toolbar.
And don't worry -- you're not deleting the toolbar from the workbook. It will turn up again, whether you want it or not, the next time you open that workbook.
Watch the Remove Custom Toolbars Video
This Excel video tutorial will show you how to remove any custom toolbars from the Ribbon, by using a mouse shortcut.
_______________
 Mike Alexander, Microsoft Excel MVP, and madcap owner of DataPig Technologies, is interviewing Excel people this summer, and posting the interviews on his Bacon Bits blog. Lots of fun!
Here's the only picture that I have of Mike, and apparently John Walkenbach has a photo of me.
It's funny, that picture reminds me of something, but I can't quite remember what it is.
The Interview Questions
Mike sent each of us a list of thought-provoking questions, then published our answers, along with his astute comments and career-enhancing photos. This week it was my turn, and you can read the results here: DataPig's Interview with Debra Dalgleish
The first picture in Mike's article was from a Microsoft conference that I attended in 2008. As you can see below, I've already given the poor guy beside me a headache.
I'm surprised that my lightning-fast throat chop was captured on film. Or, it might not have been a throat chop. I might have been telling someone how much frosty beverage to put in my glass.
Who Plays You in the Excel Movie?
One of Mike's interview questions was "Who plays your role in a movie about Excel?" You'll have to read Mike's article to see my answer.
And what about you? When they make "Excel: The Movie", who will play your role?
____________
If you're doing Excel training, or writing blog posts, or creating sample files, you might need to find some public data to use in your Excel file. You don't want to publish confidential client data, even if it's a bit disguised, or reveal your household finances to the world.
Instead of creating sample data on your own, you can find datasets online. Once source is Google Public Data Explorer (part of Google Labs), where you can access several large datasets, and use those in your Excel example files.
When you follow the links to the dataset pages, you'll find interactive charts and maps, and links to the source data websites.
Source Data Pages
Of course, each source data website is set up differently, but on some of the source data websites, you'll find more charts and interactive tables, and files that you can download.
Sample Excel Data for Download
For example, you can download Excel files with economic, environmental and social statistics from the OECD Factbook 2010, published by the Organization for Economic Co-operation and Development.
To see how it worked, I downloaded an Employment rates file with several years of data, for three age groupings. That would give you enough data to use for your own sample charts or training exercises.
Other Data Sources
Are there other good sources of public data that you use? Please share them in the comments. Thanks!
______
 Automatic or manual makes me think of cars, and choosing a transmission type, but an Excel workbook can also be on Automatic or Manual calculation. And just like a car, it's safest if you know what type of Excel calculation mode you're driving, before you head for the information highway.
In a car, you can tell if it's a manual transmission, if there's a clutch pedal, to the left of the brake pedal. In Excel, it might not be obvious whether the calculation mode is set for Automatic or Manual.
Add a Calculation Indicator
To help avoid problems, you can add Automatic and Manual to the Quick Access Toolbar (QAT) in Excel. This has two benefits:
- you can quickly see the current calculation mode,
- and you can easily change the current calculation mode.
Those check boxes are really helpful when working with big, complex files. I can easily turn calculations to Manual while making changes to the file, and then back to Automatic to make sure everything is working correctly.
Why Does Calculation Change to Manual?
Occasionally, a client will call about problems with an Excel file, that was working perfectly the day before. Sometimes the problem can be traced to the calculation setting. Somehow, it switched to Manual, and the client doesn't know how or why.
Even if you never change your calculation mode, it can be changed without your knowledge. The calculation mode setting is affected by the first workbook that's opened during an Excel session.
So, if a co-worker sends you a workbook that was saved in Manual calculation mode, and you open that workbook first thing in the morning, it could affect the rest of your Excel files. With the Automatic and Manual settings visible on your QAT, you'll have a better chance of noticing the problem.
Watch the Video
To see the effects of opening Excel workbooks with different calculation mode settings, watch this short Excel tutorial video.
___________
|
|
will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
Recent Comments