Contextures News

Never do this in Excel!

Nov 7, 2017

Something you should never do in Excel, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Drop Down Symbols

You can use different fonts on an Excel worksheet, including picture fonts like Wingdings and Webdings. However, if you make a drop down list with data validation, the list will only show the Tahoma font, no matter what font you used on the worksheet.

Tahoma does have a few symbols you can use though -- press the Alt key, then type on the number keypad (NOT the numbers at the top of the keyboard). For example, to make a drop down list with up, down, and sideways arrows, use the numbers 29, 30, and 31.

See the steps, and more symbols, on my Data Validation Tips page.

Never Do This

Last week, someone sent me a question about calculating recipe costs. They had hundreds of recipes to calculate, and planned to make a separate worksheet for each recipe, with combo boxes to select ingredients. Yikes! I said that many sheets with combo boxes would make Excel explode!

Never make a separate sheet for each recipe, or region, or anything else you're keeping track of. Put all the info on one sheet, in a named Excel table, with a column that has the recipe name, or region, or whatever. Then, with everything in one list, you can filter, or sort, or create a pivot table to summarize the data.

To see an example, download my weekly meal planner. You can use the same techniques in other Excel projects. Keep it simple -- you'll be happy that you did!

Weave a Tartan

As you might have guessed Dalgleish is a Scottish name, and last week I used Excel for a truly Scottish project -- "weaving" a tartan pattern on a worksheet. John Marshall, who is a Visio MVP from Ottawa, has been studying colours in Excel, and was using tartan colours for testing. He sent me his file, and I polished up some of the macros, and added a new tab on the Excel Ribbon, so the workbook is easier to use.

Even if you're not too interested in tartans, download the tartan workbook to see how it works. You'll get ideas for setting up your own files, and making it easier for people to go through a series of steps.

And if you are interested in tartans, read more about the workbook, and tartans, on my blog.

Fun Fact: Contexture means "act of weaving parts into a whole" and that's what my logo represents.

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

Excel Macros - According to a StackOverflow survey, Excel's programming language, VBA, is the 3rd most disliked language among developers. But, as Simon Murphy points out, that doesn't mean VBA is dying. (Level - Int/Advanced)

Pivot Tables - On YouTube, the game review channel, Many a True Nerd, just reached 300K subscribers. To celebrate, Jon did a survey, and shows how to analyze the results in Excel. He's very enthusiastic about pivot tables, but hates the GetPivotData function! I skipped through the video, and it cracked me up. (Level - All)

Dashboards - The Excel TV guys are hosting a free webinar tonight (Nov. 7th), at 7 PM Eastern time. The topic is "How to Build 3 Dashboards & Reports in Under 90 Minutes", and you'll get all the files, plus a link to watch the webinar video again later, if you want to review parts of it. (Level - Int/Advanced)

Also see: My Excel Products || Excel Events || Previous Issues || Excel Humour

A Watched Pot

Most of the time, I manage to keep Excel from exploding, but wasn't so lucky while making chocolate pudding recently. Don't believe that old saying, "A watched pot never boils." This pot definitely boiled, and started to overflow. Oh well, there wasn't too much to clean up, and the pudding was delicious!

Do you remember this old-style Corning Ware, with the blue flower? The same company makes the Gorilla Glass that's used on iPhones. We visited the Corning Museum of Glass, long ago, and it's awesome.

That's it for this week! If you have any comments or questions, send me an email.

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: https://www.contextures.com/newsletter/excelnews2017/20171107ctx.html

Debra Dalgleish
ddalgleish @ contextures.com

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.