You might not love the Ribbon in Excel 2007, but the user interface does have new features that are an improvement over Excel 2003. The new features are useful when you’re working with large formulas or long names.

Change the Formula Bar Height

In Excel 2003, if you click on a cell that contains a long formula, it can spill onto the worksheet, hiding the column headings. It’s nice to see the formula, if you want to edit it, but annoying if you’re trying to do something else.

In Excel 2007, you can adjust the height of the formula bar, and the long formulas don’t cover the worksheet. To adjust the height:

1. Point to the bar at the bottom of the formula bar.
2. When the pointer changes to a two headed arrow, drag up or down, to change the formula bar height

After you’ve adjusted the height, you can quickly restore it to its previous height, by clicking the Collapse Formula Bar button.

When the formula is collapsed, the button changes to Expand Formula Bar. Click that to return to the previous height setting.

Also, if the formula is too long to show in the formula bar at its current height, scroll buttons appear, as you can see in the screen shot above. You can click the scroll buttons to view the formula, if you don’t want to change the formula bar height.

Widen the Name Box

Another new feature that I find really helpful is the ability to widen the Name Box. Instead of just seeing the start of a long name, and three dots, you can widen the Name Box to see the entire name.

To adjust the Name Box width, point to the dividing line at the right edge of the Name Box, and drag to the right.

To return to the default Name Box width, double-click the dividing line. (At least I think it’s the default width – I’ve changed my settings too many times to remember exactly.)

Excel’s Help says, “The maximum width of the name box is half of the width of the worksheet.” However, I’m able to widen the Name Box to the full width of the worksheet, and narrow it to nothing. Maybe that restriction applied in the Beta version, and the Help wasn’t changed.

Do You Use These Features?

I’ve used Excel for so many years without these features, that I often forget they’re available. Long formulas can be hidden in the formula bar when condensed, because they don’t spill onto the worksheet, the way they used to. If a formula is cut off in a logical place, you might not even realize that part of it is hidden. Those scroll buttons are very subtle, and are the only clue that a formula doesn’t fit.

Do you remember to use the new sizing features? Do you prefer them to the Excel static settings?

Watch the Video

Here’s a short video that shows the new features for adjusting the formula bar height and the Name Box width. It also shows how to temporarily collapse the Ribbon, so only the tabs are visible. That gives you an extra inch of space, if you need it when working on a large worksheet.

_______________

Are you too old to compete in the Olympics? Maybe you’re not as bendy as those 16-year-old figure skaters, but there might be other sports with athletes about your age.

Athlete bios are posted on the Vancouver 2010 Winter Games website, and I compiled that data, then created a few Excel pivot tables, to analyze the athletes’ ages.

• Which Winter Olympic sports have the oldest athletes?
• Which countries send the youngest participants?
• Do similar age groups compete in different sports?
• Who wears the wildest pants?

With our Excel pivot tables, and some pivot table grouping, we can find the answers to those pressing questions. Well, maybe not the pants issue, but let’s look at the age questions.

Show Maximum Values in a Pivot Table

Using the Olympic athlete biographical data, I created an Excel pivot table with Sport in the Row Labels area, and Age in the Values area. The default is to show the Sum of Age, which isn’t too helpful.

Instead of a sum, I’d like to see the maximum age for athletes in each sport.

• To change the summary function in an Excel pivot table Values field, right-click on one of the values in that field.
• Click Summarize Data By, then click a different summary function. I’ll click Max, to see the highest age in each sport.

Then, I can sort the list in descending order by Age, to highlight the sports with the oldest competitors.

Alpine skiing is a surprising winner, and as I expected, figure skating and short track have a much lower maximum age.

Show a Count of Athletes

Maybe there’s only one Alpine skiier, and s/he’s really old. To compare the number of athletes in each sport, I’ll add the athlete’s name field to the Values area, and it will appear as Count of Name.

Except for the last two items, there’s a good number of athletes in each sport, with Alpine Skiing as the second largest group.

See Athlete Age by Country

If we replace Sport with Nationality in the Row Labels area, we can see the maximum age and athlete count for each country.

That 51-year-old alpine skier is from Mexico, and is the only athlete from that country. Coincidentally, Great Britain sent 51 athletes, but the oldest is 45.

To see the average age per country, you can change the summary function to Average, then sort the ages in ascending order. The lowest average ages are from countries with a small number of athletes.

To see the average ages for the larger contingents, we can filter the countries by the Count of Name value. Click the drop down arrow for the Nationality field, click Value Filters, then click Top 10. I selected to see the Top 10 items by Count of Name.

The pivot table now shows only the countries with the largest number of participants, sort by average age. There’s not much difference in the average ages among countries in the top 10 list.

That’s not too encouraging! If I want to compete in the next Winter Olympics, I should move to Mexico, and take up alpine skiing.

Age Range in Selected Sports

Finally, let’s see the age range in a few of the ice sports.  I removed Nationality from the Row Labels, and added Sports. Then, I filtered the list, to show only four of the sports – curling, figure skating, ice hockey and speed skating.

Figure skating has the narrowest age range, and curling has the widest. Maybe I can stay in Canada, and learn how to curl.

Instead of showing the individual ages on the chart, I can group the ages into 5 year bands. Right-click on an Age, and click Group. Then enter 5 in the By box, and click OK.

The chart looks less like the Rocky Mountains, and it’s easier to see the age ranges for each sport.

Download the Data

I’ve saved the athlete bio data in a zipped text file that you can download, and use it to create your own pivot table. Let me know if you make any surprising discoveries.

Download the athlete data

Excel Pivot Table Tutorials

___________

You have two Excel lists, and you’re trying to find the items that are in both lists. You know there are matching items, but your VLOOKUP formulas can’t find them. In this screenshot, the tiptech.html page is in both lists, but the VLOOKUP formula in cell C2 can’t find it.

Spot the Differences

Working with Excel data can be like one of those “Spot the Difference” puzzles. What’s different between list A and list B? If you’re lucky, the differences are obvious, like the forward slash in column B, and no leading slash in column E. Other times, it’s tougher to find the differences.

A common problem is items with leading or trailing spaces. You can’t see them on the screen, but after you’ve encountered them a few times, you learn to check for them. The LEN function is a great help if you suspect there are hidden space characters.

If you use the LEN function to compare the length of the text in cell B2 and E4, you’d see that there are 2 additional characters in cell B2. One character is the forward slash, and the other character is a trailing space.

Remove Leading and Trailing Spaces

If you want to use a VLOOKUP or MATCH to find column B items, in column E, you’ll have to get rid of any extra characters. First, you can deal with the spaces, by using the TRIM function.

To return the text from cell B2, without any leading or trailing characters, you’d use this formula:

=TRIM(B2)

If you use a formula in cell B12 to check the length of the trimmed text, it’s now 13 characters, instead of 14. The trailing space has been removed.

Remove a Specific Character

Next, you can use the SUBSTITUTE function to remove the forward slash from the text in cell B2.

=SUBSTITUTE(B2,”/”,””)

• The first argument, B2, is the cell that contains the text value.
• The second argument, “/”, is the old text, that you want to replace.
• The third argument, “”, is the new text, that replaces the old text. If you want to remove the old text, without inserting new text, use “” as an empty string, as we did here.

Combine the Functions

The TRIM and SUBSTITUTE functions work well separately, and you can combine them, to remove both the spaces and the forward slash. The order doesn’t matter, so you can use either:

=TRIM(SUBSTITUTE(B2,"/",""))

or:

=SUBSTITUTE(TRIM(B2),"/","")

Add to the VLOOKUP

Now that you know the TRIM and SUBSTITUTE functions will clean up the text in column B, you can add those functions to the VLOOKUP formula.

Instead of using B2 in the VLOOKUP:

=VLOOKUP(B2,$E$2:$F$8,2,FALSE)

use the TRIM and SUBSTITUTE functions:

=VLOOKUP(TRIM(SUBSTITUTE(B2,"/","")),$E$2:$F$8,2,FALSE)

A match for the cleaned up text is found in column E, and the Update column is filled in with the correct date.

Troubleshooting a VLOOKUP Formula

If TRIM and SUBSTITUTE don’t solve your VLOOKUP problems, there are a few more suggestions on the Contextures website: Troubleshoot the VLOOKUP formula. You’ll also find examples of using the IF function or IFERROR function to deal with VLOOKUP function errors.

And if that doesn’t work, you could try some Mr. Clean; according to this commercial from the 1950s, it works on everything, including the dog!

_____________

Yesterday, Seth Godin suggested that we should invent a holiday to celebrate the things that we love. For example, today is Family Day here in Ontario, Canada, a holiday that has only existed for a couple of years. It’s nice to have the day off, and that gave me the chance to think about other holidays.

How about a Spreadsheet Day, to honour Excel, and the other spreadsheets that people use? There are other spreadsheets, aren’t there? I searched Google, and nothing relevant came up for the keyword phrase, “Spreadsheet Day”. That’s shocking!

Next, I checked a couple of sites that list all the obscure holidays and special days, but found nothing similar listed there either. There are plenty of obscure holidays, including Pi Day on March 14th, and there’s even a Pickle Day, on November 14th. But no Spreadsheet Day.

What Day Would Work Best?

We should select a suitable day to celebrate spreadsheets, without conflicting with any of the other important holidays. The first cell in an Excel worksheet is A1, so that could guide the holiday date selection. The first “A” month is April, but April 1st is April Fools’ Day, and it would be best to avoid that. Also, April is tax month in Canada and the USA, so Excel users might not be in the mood to celebrate.

How about August 1st? There’s not much happening in August, and no other holidays on that date, that I can see. That would be a good date for Spreadsheet Day. Or maybe you have a better suggestion.

How Could We Celebrate?

What activities could we plan for Spreadsheet Day?

• The 50 yard dashboard?
• Rowing competitions?
• All day cell-ebrations?
• Sitting in a Lotus position?
• Pie Chart eating contests?

Do you have any other ideas?

Spreadsheet Fun

While researching (yes, any search in Google or YouTube is classified as research, for tax purposes), I found a couple of PC vs Mac commercials that mentioned spreadsheets and fun. I’d only seen the US versions of these ads, and was surprised to find different ads for the UK. In this case, at least, the UK version is much funnier.

PC vs Mac – US Version

PC vs Mac – UK Version

Spreadsheet Day 2010

There’s still time to organize a Spreadsheet Day for 2010, if we get started now. Let the hijinks, shenanigans and monkey business begin!

____________

If you’ve wanted to try Tableau data visualization software, now’s your chance! They’ve just launched Tableau Public, where you can upload your data, and use the free Tableau tools to create amazing interactive charts, maps and dashboards.

This example shows Economic Indicators & Stock Market Returns, and you can select from a drop down list of market metrics to update the chart.

As the product name implies, your saved data will be public, so it’s not the place to work with your top secret financial data. It’s a great opportunity to experiment with the Tableau software though, using dummy data, or data that you’re willing to share with the public.

With Tableau Public, you can connect to Excel, Access, and text files, with a limit of 100,000 rows of data per connection. You can save up to 50 Mb of content to the Tableau Public web servers.

Tableau Articles

There are other blogs where you can see dashboard examples, and see how people are using the software.

• Seattle Bubble’s Interactive Map of Washington Banks and Credit Unions
• Where the Top VCs Invest
• The Magic of Data Visualization

There’s also a gallery with dashboard examples, such as the Fantasy Football 2009 Running Backs and Student Loan Default Rates.

Use Tableau Public

To get started, go to the Tableau Public page, and click the Download Tableau Public button. Then, enter your email address, and click Submit. To help you understand the software, you can watch the brief Tableau Public Preview video and the Tableau Public training videos.

After you install Tableau Public, open it, and connect to your Excel, Access or text data file. It’s quick and easy to create a graph, and Tableau will help by suggesting chart types for your data.

Your work in Tableau Public desktop will be saved to the Tableau Public web servers, not on your computer. On the web servers, your data will be accessible by anyone on the internet, so don’t use Tableau Public for confidential or sensitive data.

Share Your Results

After you save your work, you can share it, by embedding it on your blog or website, or by sharing a link to your data. If you create a dashboard, you can post the link in the comments here, so other people can go and take a look.

___________

Related Links:

Last fall I wrote a couple of articles about Tableau, and uploaded a short video:

• Adrift in a Sea of Numbers
• Create a Chart from Excel Data in Tableau

I used a trial version of Tableau for a couple of weeks, which has all the features of the paid version. I was really impressed with what the software can do, and got in touch with the Tableau people, to see if I could participate in the Tableau Public beta. The free version wasn’t available yet, so they provided me with a license for the paid version, so I could keep experimenting, and post my work in their public servers.

_______________

You added some data validation drop down lists to your Excel worksheet, to make it easier to enter the data. Later, you opened that workbook, and the data validation arrows were missing! What happened, and how do you get those arrows back?

Select the Cell

The data validation dropdown arrow only shows when you click on the cell. So, you might have 100 cells with data validation drop down lists, but you’ll only see the arrow in the active cell.

If you want to make it easy to find the cells that have data validation lists, you can colour the cells, or add a cell comment.

If you want drop down arrows that are always visible, you can use combo boxes instead of data validation.

Objects Are Hidden

Maybe there were some other shapes on the worksheet, like logos, or text boxes, and you changed a setting to hide them. Because they’re objects too, the dropdown arrows will also be hidden.

To make the objects visible again, follow these steps:

1. Click the Office Button, then click Excel Options.
2. Click the Advanced Category
3. Scroll down to the ‘Display Options for This Workbook’ section
4. Under ‘For Objects, Show’, select All, then click OK

Dropdown Option

Even though the default setting for a data validation list is to show the arrow, it’s possible to change that setting. In the Data Validation dialog box, you can turn off the option for a dropdown list. To turn it back on:

1. Select the cell that contains a data validation list
2. On the Ribbon, click the Data tab
3. In the Data Tools group, click Data Validation
4. On the Settings tab, add a check mark to In-cell dropdown
5. Click OK

Workbook Corruption

If you try all of the above solutions, and the drop down arrows are still missing, the worksheet may be corrupted. Try copying the data to a new worksheet or workbook, and the data validation dropdown arrows may reappear.

Or, try to repair the Excel file as you open it:

   1. In Excel, click the Office button, and then click Open
   2. Select the file with the missing data validation arrows
   3. On the Open button at the bottom right, click the drop down arrow
   4. Click Open and Repair
   5. When the confirmation message appears, click Repair.

Drop Down Arrows In Excel 2003 and Earlier

There are instructions on the Contextures website for fixing missing data validation arrows in Excel 2003 and earlier versions. Check those instructions if you’re not using Excel 2007 or later version.

Watch the Missing Data Validation Arrows Video

To see the steps for fixing missing data validation arrows, watch this short video tutorial.

____________

You can create order forms and price lists in Excel, and automatically show a price when a product is selected in the order form. But what happens if you want to give some customers special pricing, or offer sales pricing occasionally?

Using the VLOOKUP Function

With a simple VLOOKUP function in Excel, you can pull a product price from a lookup table. For example, if a customer orders a jacket, the price is 25, based on this lookup table. The lookup table is a named range, ProductLookup.

To show the price after a product is selected in an order form, use a VLOOKUP formula to find that product in the lookup table. For example:

 =IF(B11="","",VLOOKUP(B11,ProductLookup,2,FALSE))

If cell B11 is empty (no product has been selected), the formula result is an empty string, and cell C11 will appear empty.

If a product has been selected in cell B11, the VLOOKUP formula finds the price in column 2 of the ProductLookup table.

Select a Variable Column

In the simple example shown above, the price will always come from the second column of the lookup table. To use variable pricing, you could create a lookup table with two or more columns of prices, instead of just one. In this example, the ProductLookup table has four columns, instead of two.

On the order form, add a drop down list where you can select one of the pricing types – Price, Promo or Sale.

Then, in the VLOOKUP formula, replace the column number with a MATCH formula.

 =IF(B11="","",VLOOKUP(B11,ProductLookup,MATCH($E$4,Pricing,0)+1,FALSE))

The MATCH formula finds the pricing type selected in cell E4, in a range named Pricing (cells B1:D1 in the lookup table).

If Promo is selected, the MATCH formula returns a 2, because Promo is the second item in the Pricing range.

You’ll add 1 to that number, because the ProductLookup table has one column to the left of the pricing columns.

So, 2 + 1 = 3, and the promo pricing will come from the 3rd column of the ProductLookup table.

Watch the VLOOKUP / MATCH Video

To see another example of using MATCH with VLOOKUP, watch this short video.

____________

After you update the source data for a pivot table, and refresh the table, some of the old data might still appear in the pivot table drop downs. For example, you changed a product name from Whole Wheat to Whole Grain, and now both names show up in the pivot table’s Product drop down.

Prevent Old Items in Excel 2007

You can prevent old items from being retained in an Excel 2007 pivot table, by changing on of the pivot table options

1. Right-click a cell in the pivot table
2. In the pop-up menu, click PivotTable options
3. Click the Data tab
4. In the Retain Items section, select None from the drop down list.
5. Click OK, then refresh the pivot table.

The old items will disappear from the pivot table drop downs, and won’t appear again.

Clear Old Items in Excel 2003

To prevent old items in Excel 2003 pivot tables, you can use programming to change the MissingItemsLimit setting.

Or, you can manually clear the old items, by following these steps:

1. If you manually created groups that include the old items, ungroup those items.
2. Drag the pivot field that contains old items out of the pivot table. Also remove it from any other pivot tables that use the same pivot cache.
3. Refresh the pivot table.
4. Drag the pivot field back to the pivot table.

This will clear the existing old items, but won’t prevent more from appearing later.

Watch the Video

To see the steps to change the retain items setting in Excel 2007, watch this short video.

______________

For more information on Pivot Tables, see the Pivot Table Tutorials on the Contextures Website.

______________

At a client’s office last week, I was selecting a pricing option from a data validation drop down list. The worksheet was zoomed to 75%, so we could see more of the data, and the person watching over my shoulder asked, “How can you even read that?”

Good question. Sometimes the font in a data validation drop down is so small that you can barely read the list.

Unfortunately, there’s no way to make the font size bigger, which is one of the drawbacks of a data validation drop down list. There are other ways to create a drop down list though, and one of those options might work better in your worksheet.

We’ll compare these types of Excel drop down lists:

• Data Validation List
• Form Control Combo Box
• ActiveX Control Combo Box

Data Validation List

Data validation is a great feature, and you can use creative formulas to create flexible lists, such as dependent drop down lists. However, the font size can’t be changed, only 8 rows are visible at a time, and only the active cell shows a drop down arrow.

You can’t change those settings, but you can colour the data entry cells, to make them obvious to the worksheet users.

Form Control Combo Box

Instead of using data validation, you could use a Form Control Combo Box. It gives you a bit more control over the appearance of the drop down list.

You can adjust the number of drop down lines in the Form Control Combo box, so you can show all 12 months, without a scroll bar.

The drop down arrow is always visible, to the Form Control combo box is easy for users to find on the worksheet. The font size can’t be changed though, so the list would be hard to read on a zoomed worksheet.

If you select an item from the Form Control drop down list, the item number is entered in the linked cell. In the screen shot below, February was selected, and a 2 is entered in cell D5.

To show the item name, instead of the item number, you can use an INDEX function in another cell. In this example, the INDEX function returns a month name from the MonthList named range, from the row number (2) for the selected month (February).

ActiveX Combo Box

Another drop down list option is to use an ActiveX combo box. This is similar to the Forms Control combo box, but has more properties that you can adjust.

After you insert a combo box, right-click the combo box, and click Properties.

You can set the properties, such as font, font size, number of rows, and even include multiple columns.

If you know that a worksheet will be zoomed most of the time, you can use a larger font, to make the list easier to read.

Combine the Drop Down Lists

To make the most of the data validation features, while allowing font changes, autocomplete and more rows, I sometimes combine data validation with a combo box. There’s only one combo box, hidden at the top of the worksheet, and bit of programming makes the combo box appear when needed.

When a user double-clicks on a data validation cell, the combo box appears, and displays the data validation list for the selected cell. If users don’t want to use the combo box, they can simply click on the arrow in the cell, and use the data validation list.

Watch the Drop Down Lists Video

To see a demonstration of the 3 types of drop down lists, you can watch this Drop Down Lists video.

____________

Can you remember how old you are? Or are you like me, and have to ask, “What year is it?” and then subtract your birth year?

Fortunately, Excel can help you with that calculation, if you use the handy, but poorly documented, DATEDIF function. You won’t find DATEDIF in Excel’s help, and the Microsoft website suggests a different method of calculating date differences. If anyone knows why, I’d love to hear about it.

Update: There is a bug in the DATEDIF function for some intervals. For details, see the article ktDATEDIF Function, which suggests a user defined function as an alternative. (Thanks to Sam and Rick for their comments.)

Create a List of Dates

It was my son’s birthday last weekend, and he was complaining about getting old. Geez, if he’s old, what does that make me? (Please don’t answer!) Yes, that’s him in the picture, wearing his Junior Whale Trainer shirt. And he still likes cookies, but those cheeks have disappeared.

To keep track of family birthdays and ages, you can create a list in Excel. In one column, enter everyone’s birth date. Here’s an example, with birth dates created using the RANDBETWEEN function.

=RANDBETWEEN($F$2,$G$2)

Using the DATEDIF Function

To calculate each person’s current age, you can use the DATEDIF function, to compare their birth date with today’s date. The DATEDIF has 3 arguments:

=DATEDIF(Date1, Date2, Interval)

Date1 must be earlier than Date2, or the result will be a #NUM! error.

Interval is the unit of time that you want to show the results in, such as:

• y for years
• m for months
• yd for calendar days between dates, ignoring the years

For example, to find the current age, in years, for the birth date in cell B2, enter this formula in cell C2:

=DATEDIF(B2,TODAY(),”y”)

Change the Interval

To see a person’s age in months, you can use “m” as the interval, instead of “y”.

=DATEDIF(B3,TODAY(),”m”)

Problems With DATEDIF

Excel 2007 and Excel 2003 may give incorrect results in some situations, especially for the “yd” and “md” intervals. You should use the more reliable user defined ktDATEDIF Function to calculate these date differences, or use Microsoft’s suggested formula for calculating date differences.

More DATEDIF Information

Chip Pearson has more information on the DATEDIF function, which he describes as “treated as the drunk cousin of the Formula family.” He shows several examples of using DATEDIF, and explains how to use it with leap years.

Microsoft has DATEDIF details for SharePoint, which looks the same as the missing DATEDIF info for Excel.

DATEDIFF Function

And finally, don’t confuse the Excel worksheet function, DATEDIF, with the VBA function DATEDIFF, or the Access DATEDIFF function. Although the functions have similar results, the DATEDIFF functions have different arguments, and use different interval settings. For example, “yyyy” is the setting for year, instead of “y”.

________________