This week, see how to add a table of contents in a workbook, customize the right-click menu, pick a random winner, and many more tips.
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
1. Customize Right-Click Menus
When you right-click in Excel, a pop-up menu appears, with a list of commands that you can use in the current situation. If you’ve ever wondered how you can customize that menu, AlexJ shares his technique, and a sample file that you can download from my website.
You can run the macro, to add the filter commands to the right-click menu. Or, if you’re feeling adventurous, follow his instructions, and add different commands.
2. Using Goal Seek
Goal Seek is one of the What If Analysis tools in Excel, and Winston Snyder shows how to use Goal Seek, manually, or with a macro. See how much you would have to increase one number, to reach a target amount.
3. Pick a Random Winner
Last week, someone asked me how to pick a winner in a spring raffle, using Excel. I’ve had a few giveaways on my blog, and I always use the RAND function to pick a random winner. Just create a list of names or numbers, and put =RAND() in the next column. Sort by the RAND column, and the winner is the name/number at the top.
This short video shows the steps.
4. Pareto Chart Updates
If you saw Matthew Eaton’s pareto chart a couple of weeks ago, you might want to take a look at his updated version. He uses this chart in Excel, to help improve his writing.
5. Add a Table of Contents
If you have a workbook with lots of sheets, you can use Chris Newman’s macro to create a table of contents, with a link to each sheet. His sample file also has a macro that will split a long list into several columns, to make it easier to use.
6. Clustered Column Chart Alternatives
If you’re tired of seeing clustered column charts, especially the 3-D kind, you’ll get 6 new ideas from Ann K. Emery. She helped a colleague overhaul a chart, and wants to know which alternative you like the best.
7. Count Rows With At Least One Match
You can use COUNTIF or COUNTIFS to check for a set of criteria when counting. On the ExcelXOR blog, see how to count if at least one criterion is met — it’s a bit complicated!
8. Spreadsheets in Education
In the Spreadsheets in Education journal, you can read a variety of interesting articles, including “Divination: Using Excel to explore Ethnomathematics” (Vol. 8, Issue 1). Some of the articles have workbooks that you can download.
9. Don’t Use These 7 Characters
Do you know the 7 characters that you can’t use in a worksheet tab name? David Ringstrom gives you the list, and also reminds us of the word that we’re not allowed to use as a sheet name.
I use letters and underscores in sheet name, but not too many other characters — and I always avoid apostrophes, even though they are allowed. They can cause problems!
_____________________
_____________________