And that reminded me of a new shortcut I discovered last week:

• Ctrl+D makes a copy of an embedded chart (Excel 2007 and Excel 2010 only).

To help you remember, think "D for Duplicate." I couldn't find it in the Help system, and a cursory check of the Web leads me to suspect that I'm the first person in the world to discover this.

Before Excel 2007, the easiest way to copy a chart is to press Ctrl while you drag the chart with your mouse. That action was removed in Excel 2007, but Microsoft provided an even easier method -- and didn't even tell anyone about it!

Just a quick note to tell you how impressed I have been, for a very long time, with your Excel books. I bought one several years ago (maybe Excel Power Programming With VBA?) and, at the time, it put me ahead of all of my colleagues in terms of practical, ready to use knowledge about MS Excel.

Skip forward at least a decade and I had a current need to update my ss skills and went to Barnes & Noble to get some up-to-date resource technical materials. As I was scanning the available materials, my wife said "how can you ever manage to figure out which Excel book to pick?" At that moment in my scanning, I saw just "Walkenbach" and I pulled it out and replied "Right here, this guy is an Excel genius. I recall reading one of his earlier Excel books and it was just incredible." I think she thought I was kidding.

Well, I took Excel 2007 Bible home over the weekend and cracked it open this morning to get up to speed on charts. I wrapped up the 'Getting Started Making Charts' intro on charts and am just part way into 'Learning Advanced Charting' and I am very impressed! Your exceptionally clear writing style, focus on how to do the task at hand, easy transition from introductory to advanced materials are all just top-of-the-line.

So, I thank you, man, for putting out such a truly great product and I wish you the very best. I can't say enough good stuff!

Wishing you the best,
Greg S., NY

I'm glad you like it, Greg.

Excel has a macro facility, known as Excel 4 macros (XLM for short) that was the primary macro language prior to the introduction of VBA in Excel 5.0. Most people have long since migrated their Excel 4 macros to VBA; however, some Excel 4 macro capabilities were missing from VBA, which made this migration difficult.

In Excel 2010, one of our goals was to remove any remaining barriers that people had to complete the migration of Excel 4 macros to VBA.

The article lists quite a few things that, in the past, required an XLM macro. The most important new feature is the ability to provide argument descriptions for user-defined functions. These are the descriptions that appear in the Function Arguments dialog box.

I posted a new tip that demonstrates how to do this: User-Defined Function Argument Descriptions In Excel 2010.

Here's how it looked in 1997:

Click to enlarge, or try navigating it at archive.org.

I'd say my site design skills have improved over the years.

Mr. Walkenbach,

My name is Joe P. I'm a 22 year old college senior. I've been working on a co-op for about a year with a local company in Quality Assurance. When I started, the demands of the job went beyond my capabilities: they needed someone who could write Excel macros, and a lot of them. Determined to fulfill the role, I went to Barnes and Noble and picked up "Excel VBA Programming for Dummies."

That was about 9 months ago. Since then, I've learned the amazing things Excel can do thanks to your easy to follow guide. Not only has it expanded my capacity at work, but I've taken my Excel VBA skills and like to make fun spreadsheets in my spare time. I spent about 4 or 5 months writing a program that plays and analyzes the game of Craps in a little over 1 MB.

My time on co-op is almost up. When I leave, I plan to start up a consulting firm. I'm going to contract my services at building spreadsheets to small businesses in the area. Excel programming has become one of my favorite things to do, and I'm hoping I can find a market for my skills... I can't imagine a better way to make a living.

So I just wanted to send you a sincere thank you for writing your book, and wanted to let you know, I've put the information to great use, both professionally and personally.

Thanks for the kind words, Joe. When I started reading your email, I just assumed that the book you used was Excel Power Programming. Glad to hear that the For Dummies book also works.

It uses no macros. Unprotect the worksheet and try to figure out how it work. Some hints:

• Dynamic Charting with No VBA
• Combination of XY (Scatter) and Stacked Column chart types
• Marker Fill with picture files.
• Conditional Chart Formatting for chessboard style
• No IF() functions. Not even one. REPT() and boolean logic as alternative to IF() INDEX/MATCH

In my case, I used Excel about two hours ago to double-check a file that will be included on the CD that comes with my Excel 2010 Formulas book. The tech editor, Niek Otten, noticed a discrepancy between the text and a figure. So I had to check it. Niek's really good at tech editing books. I'm often amazed at the stuff he points out. Too bad it's such a low-paying job.

Before that, I received a PUP v7 upgrade request, and checked my PUP sales workbook to ensure that the person was eligible for an upgrade. She was. Yes, those upgrade requests are checked manually. Low-tech, but that's the best I can do.

So what's the last thing you did with Excel?

He suggested that "Jay" is short for J-Walk, and the cartoon is actually about me. It's unlikely because I have a different hairstyle, I don't have a briefcase, and I don't make house calls.

I wasn't familiar with Working Daze. I read some more, and Jay is a regular character in the strip, which has been around since at least 2001.

Assume that you have the VB Editor window open. You click Excel' minimize button to minimize the app to get it out of the way. Then you see the VB Editor window, and realize that there's no need for it to be open. So you close it -- and Excel's window pops back up!

Yeah, as far as annoyances go, it's a minor thing. But it's been going on since Excel 5. With every new version, I keep hoping it will be fixed, but it never is. I can't think of a single reason why closing the VB Editor window should restore Excel's window.

What little Excel quirks do you find annoying?

I also provide an OPML file that you can download.

What's an OPML file?

It's an XML file that contains feed details, and can be imported by most RSS aggregators. The net effect is that you can subscribe to all of these Excel blogs with just a few mouse clicks.

What's a RSS aggregator?

It's software that makes it very easy to keep up with dozens (or hundreds) of blogs. If you don't use an RSS aggregator, you really should. My favorite is Google Reader, a web-based aggregator (requires an account with Google).

To import these 44 Excel blog feeds (plus 20 additional feeds for the blogs that provide a comments feed) into Google Reader, first download the OPML file. Then navigate to Google Reader choose Manage Your Subscriptions, and then Import/Export. Browse for the OPML file, and the subscriptions will be added. Other RSS aggregators provide the same functionality.

This month's Office 2010 retail pricing announcement and ongoing discounts for Office 2007 Home and Student are Microsoft's tacit acknowledgment that the productivity suite isn't as valuable as it once was. Office is tracking a course of unplanned obsolescence and the inevitable end shared by oh-so many other products: Commoditization.

Or, maybe lower prices can be explained by competition.

I'll ask upfront: Do you really need Microsoft Office on a daily basis? Is Office vital to your work day? Do you use it at home? If you use it at work, how often? If you use it at home or for college, how often? Please respond in comments.

My answers are easy. I don't use Office at all.

Well then, I guess we can conclude that nobody uses it.

And then, he asks another stupid question:

Word processing reached commodity status years ago, as more applications incorporated the basic formatting features most people use more than 90 percent of the time. No external wordprocessing program is required to blog, e-mail, instant message, tweet or post to social networks like Facebook. Be honest, how much of the writing you regularly do requires a dedicated wordprocessor?

That's right, Joe. Everybody's writing is limited to 140-character tweets.

And the he moves on to Excel:

What is Excel or any spreadsheet really necessary for? Sure, lots of business people use spreadsheets for data analysis, but what is the need for consumers or even small business owners? Many financial products or services, like Quicken or Quickbooks, put a friendly face on spreadsheets;

And people actually take this guy seriously?]]> 2010-01-27T16:19:36+00:00 http://spreadsheetpage.com/index.php/blog/excel_version_poll/ http://spreadsheetpage.com/index.php/blog/excel_version_poll/ General This is mainly a test to see if PollDaddy polls work with my blog software.

Today I ran an Amazon report for the year 2009. It shows every sale made with my affiliate ID, with all of the backup data (except information about the buyer). It occurred to me that the report is a perfect candidate for a pivot table. So I imported it into Excel and create a few pivot tables. I made it available as a download, for people who want to learn pivot tables, but don't have access to a good set of data to play around with. See: Pivot Table Demo Workbook.

If you're curious, my total Amazon affiliate income for last year was $2,524.84. That's almost $7 per day! Some people make a living from it, but I just view it as free money that pays my Web hosting bills. Here are the top items sold via my links:

=SUM(DAI:LYD,OS:EOF,EX:CEL)

=SUM(CON:TEX,TU:RES,BL:OG)

=SUM(BA:CON,BI:TS)

=SUM(NEW:TON,EX:CEL,BA:CH)

=SUM(PT:SB,L:OG)

=SUM(S:PR,E:AD,SH:EET,P:AGE,BL:OG)

Challenge: Write a macro that accepts a text string, and creates formulas like these automatically.]]> 2010-01-24T15:45:44+00:00 http://spreadsheetpage.com/index.php/blog/attention_64_bit_excel_2010_users/ http://spreadsheetpage.com/index.php/blog/attention_64_bit_excel_2010_users/ PUP I've been working on PUP v7.1, and it's almost ready to go. I've tested it on Excl 2007, 32-bit Excel 2010 Beta (running on Vista), and 64-bit Excel 2010 Beta (running on Windows 7).

Before I release it to the world, I'd like a few other 64-bit Excel users to try it out. If you're interested, send me an email at walkenbach (at) gmail.com.

In Office 2007, the command is available in the Resources tab of the Excel Options dialog box.

Apparently, this useful tool has been removed from Office 2010. I could not find a command or button to diagnose problems. But I did find this in the Help system:

Maybe it's just a joke, added by a technical writer.

Today I spent some time and updated it for Excel 2007 and 2010. I added a few new features, including a Ribbon Interface.

If you'd like to take a look, download it here: King James Bible.

Here's an overview of the year's stats:

The average visitor spends 2 minutes and 18 seconds here, and views 2.82 pages.

The top countries are: U.S., United Kingdom, Canada, India, Australia, Germany, Netherlands, France, and Singapore.

In terms of page views, here are the top-10 individual documents at this site for 2009:

1. Spreadsheet Protection FAQ
2. Referring To Ranges In Your VBA Code
3. Cell Counting Techniques
4. Employee Time Sheet
5. A Formulas To Calculate A Ratio
6. Creating A "Megaformula"
7. Alternatives To Nested IF Functions
8. Comparing Two Lists With Conditional Formatting
9. Protecting Cells, Sheets, Workbooks, and Files
10. Excel Easter Eggs

Here's a chart of the number of page views, by day:

The cyclical dips indicate weekends. That big spike was on August 11-13. Page views were about double during that period, and the extra traffic came to view that Megaformula page. Oddly, there was no major referrer for those three days, so it's possible that the link was in a printed article.

That low point was on April 9. Either my site was down most of the day, or Google Analytics was down.

Here's a chart that shows traffic by source:

Surprisingly, Internet Explorer is still the most popular browser (64%). It's followed by Firefox (28%) and Chrome (4%).

I indicated that it would be fairly easy to update the code. That was based on some preliminary tests I did using Excel 2007, Excel 2010 and 64-bit Excel 2010. But I was wrong. Updating the code will require more work than I had thought.

Today I learned something about VBA: When you install Office 2010, you get VBA 7. And VBA 7 replaces your previous VBA version. I have three versions of Excel installed on my main system (Excel 2003, Excel 2007, and Excel 2010 Beta). So now, all three of them are running VBA 7 (even though the Help - About dialog tells me it's VBA 6.5 in Excel 2003 and 2007).

What this means is that my tests led me astray. I assumed that I could simply add "PtrSafe" to my API function declarations. That solution works (most of the time) only for VBA 7. Earlier versions of VBA don't recognize the PtrSafe keyword. So, in order to keep PUP v7 compatible with Excel 2007 (running VBA 6.5), I'll need to use the vba7 and win64 compiler directives, and provide two sets of API function declarations. It's still not incredibly difficult, but it's a lot more work than I thought it would be.

One of the things I'll be doing in PUP v7.1 is to add this fix to all of the UserForms.