Goal Seek + more Excel tips
April 07, 2015
Use Excel's Goal Seek feature, pick a random number from a list, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
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.
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.
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.
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.
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.
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.
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!
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
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!
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!
That's it for this week! If you have any comments or questions, send me an email.
[email protected] contextures.com
Last updated: January 25, 2020 4:19 PM