Search Contextures Sites
Goal Seek + more Excel tips
April 7, 2015
Use Excel's Goal Seek feature, pick a random number from a list, and many more tips, in this week's Excel news.
- Debra - firstname.lastname@example.org
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.
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. Video: More Than One Filter in Pivot Table
There are 3 types of pivot table filters -- Manual, Labels, and Values. If you apply one type, that filter will be automatically removed, when you try to apply an additional filter -- unless you change a pivot table setting.
Watch the short video, to see how to filter for specific months, and then filter again, to show the top 3 values for those months: Apply Multiple Filters
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!
10. No Golfing Yet
The temperature reached a balmy 17°C last week, which was a nice surprise, after the long winter. I saw this sign, while we walked through the park, warning us that golf is not allowed. There's no danger of anyone trying to break that rule for a few weeks, at least!
See more recommended Excel products.
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2017
All rights reserved.