Contextures News 20150407

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.

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!

weekly photo

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

Debra Dalgleish

Debra Dalgleish

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.


Last updated: January 25, 2020 4:19 PM