Contextures

Contextures News

Excel games and troubleshooting

Nov 14, 2017

How to do the mouse wiggle 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!

Mouse Wiggle

One of my favourite Excel shortcuts is the "mouse wiggle" -- I use it to quickly change formulas to values. It's handy if you're filling blank cells in a column, based on the value above.

Here's how the wiggle works:

  1. Select a block of cells that contain formulas
  2. Point to the border of the selected range - pointer will change to 4-headed arrow
  3. Press the RIGHT mouse button, drag a bit to the right, then back to the selected cells (that's the wiggle)
  4. Let go of the mouse button, and in the popup menu, click "Copy Here as Values Only"

To see the steps, watch the short video on my Contextures blog, and there are more shortcuts on my Data Entry Tips page.

mouse wiggle

Troubleshooting

What's on your worksheet? Last week, I had to do some troubleshooting, to figure out why someone's workbook kept crashing. To get a quick summary of what was in the file, I ran the "List All Sheets" macro, that you can download from my site. It adds a sheet to the workbook, with all the sheet names, and other information, such as how many cells are used, and how many cells have data validation.

The workbook that I checked had a list that looked similar to the screenshot below. Yes, there were millions of cells with data validation! Entire columns had been set up with drop down list, instead of just the cells in the data entry range. I cleared all the unnecessary data validation cells, and that fixed the problem.

troubleshooting with sheet info list

If you have a different type of data validation problem, there are more troubleshooting steps on my site. And if you have a copy of my Excel Tools add-in, it has a Sheet Info command, and other troubleshooting tools.

Excel Articles

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

Subtotals - With Excel's Subtotals feature, you can quickly group data, and show one or more levels with subtotals. Bill Jelen discovered that you can group by months, without adding any extra columns to the data. That technique starts about the 2:00 minute mark of the video. My website has more info on the Subtotals feature. (Level - All)

Excel Games - If you enjoy games, take a look at this Excel Sudoku Solver. All the instructions and macro code are on the page, so you don't have to download anything. It's virus-free too, including the flu virus that the author had! Also, Andy Pope has a few Excel games on his website, and lots of other great stuff, like this Chart Exporter. (Level - Int/Advanced)

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

It's For Science

This year, summer weather seemed to last forever, then last Friday, it was suddenly -40C (approx), and snowing. Good thing I got a new winter coat, and had the snow tires put on the car, a few days earlier.

To help keep us warm, I did a baking experiment -- comparing homemade bran muffins to a muffin mix. The ones on the left are from the mix (2 minutes to prepare), and tasted okay. The ones on the right are from this recipe, and took a lot longer to make, but tasted better. The things we do for science!

weekly photo

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: http://www.contextures.com/newsletter/excelnews2017/20171114ctx.html

Debra Dalgleish
ddalgleish @ contextures.com

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.

 

Search Contextures Sites

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: November 11, 2017 12:54 PM