Peltier Tech Update December 2015

Hello patient followers. I haven’t posted for a while, not because I can’t think of anything to write about, but because I’ve been rather busy. I’ll talk about each of these things separately, but I think I need a quick post to say what’s going on.

Peltier Tech Blog

There are plenty of topics that I want to cover. New chart types in Excel 2016. Charting and programming examples. Dozens and dozens of old articles that were written for Excel 97 and need to be updated.

An article that I started in October and still haven’t gotten around to finishing is an Excel-based solution to xkcd’s velociraptor problem, inspired by an article in Wired magazine. I have a formula-based approach and a VBA approach, plus plenty of cool images from a Google search.

Microsoft Excel 2016 for Windows and Mac

I’ve written about how Microsoft has released Excel 2016 for Mac and Excel 2016 for Windows.

The Mac version finally looks enough like the Windows version that I don’t feel totally hobbled while using it. In fact, the look and feel is very much like the Windows equivalent. Of course, it was released prematurely, and a lot of things were not really working yet, particularly on the VBA side. But each month Microsoft releases an update (the latest is 15.17), which fixes a bunch of stuff, hasn’t yet fixed a bunch of other stuff, and into which a few new bugs have crept in. The latest big improvements are that the ribbon can now be customized by add-ins like my own charting utilities, though there is not yet a capability for users to modify the ribbon themselves.

The Windows version is pretty good; I’m using it most of the time now, except for testing. There are a few things I don’t like, for example the way they handle pinned files and folders in the File-Open and File-Save functions. But all in all, it’s working well. The Windows version has monthly updates too, if you’re using an Office 365 subscription.

The coolest thing Microsoft has done is started up a User Voice section on Excel, which allows us, as regular users, to make suggestions for features that would make Excel even better. If you see an idea you like, you can vote on it, and the items with the most votes get attention from Microsoft. Here are some suggestions I’ve made, voted for, or commented on.

Give us a proper NULL() worksheet function – This would let you use NULL() in a formula, for example, and a chart would treat the formula as if the cell were totally blank, and leave a gap in the line. You know what’s cool? Within a couple weeks of posting this idea, someone from Microsoft called me to discuss this function, and now it’s actually being implemented.

Recent Files pane in Excel 2013 was pretty good, but Excel 2016 broke it – This is about how Excel 2016 messed up how pinned files and folders are displayed, which I complained about above. It’s only got 61 votes, so follow the link and add your vote.

Sensible date formatting on X-axis of XY-scatter charts – This would give you a nicer date format for XY charts, without having to use the less-flexible line chart. Only three votes, so I don’t know about this one.

Chart series formatting – UI overhaul – I think the intent of this one is to provide a single dialog to format all of a chart’s series, the way Excel 2013 introduced one dialog to manipulate chart type and axis for all series. 36 votes, so people, share the love.

Go to the User Voice site, read the ideas people have posted, and vote for your favorites. Microsoft is reading these, and commenting on even the ideas without too many votes.

Peltier Tech Charts for Excel

I announced the release of Peltier Tech Charts for Excel 3.0, the latest major upgrade to my popular and awesome Excel charting utilities. This major upgrade makes it compatible with Excel 2016, such that one add-in works on both Windows and Mac computers; no need to buy two licenses if you can’t decide on an operating system. Because Mac Excel 2016 is evolving monthly, I have been spending a lot of time making sure that my software takes advantage of the fixes Microsoft makes (and removing workarounds). I have also been addressing a lot of dumb little problems, and I’ve started outlining some new features. The documentation is admittedly pretty lame, so I have started outlining that work as well.

If you are a licensee of my earlier utilities, email me and I’ll set you up with a discount coupon so you can upgrade on the cheap.

MVP Summit

In November, Microsoft hosted the annual Microsoft Global MVP Summit. I met up with a few dozen Excel MVP colleagues, and we got caught up, had a beer or three, and talked Excel with the Excel Product Group at Microsoft. These folks are working on some cool things, not just the Windows and Mac stuff, but also Excel on all kinds of platforms: I let them talk me into installing Excel on my Android phone, and I was amazed that I could actually do a little work on it. They’re working on improved simultaneous co-authoring of documents, and on more new chart types, and this new chart engine they’ve been developing will make charting faster and more flexible.

Australia and New Zealand 2016

In March 2016 I and several of my Excel MVP colleagues will be traveling to the Southern Hemisphere to present a series of conferences on Excel. We will have two-day sessions in Auckland, Sydney, and Melbourne. Follow the link to read more about Excel Summit South 2016. There will be presentations by the experts, including MVPs, industry leaders, and Microsoft. We’ll have panel discussions and Q&A sessions, and lots of time for off-line chats.

Amsterdam 2016

On May 26, 2016, MVPs Tony de Jonker and Jan Karel Pieterse will host the third annual Amsterdam Excel Summit. I missed the 2014 session, but I was there last year, presented a couple of sessions, and met a lot of people that I’d only known through email or blogs. I’ll be there again in 2016, so come by and visit; I’m really friendly, not as nerdy as my blog would make you think.

On May 27, 2016, I will lead the Excel Charting And Dashboard Masterclass along with Tony de Jonker and David Hoppe. We’ll be teaching about charting, visualization, and dashboards.

Microsoft is Listening

Microsoft has changed a lot, especially in the past couple of years. They’ve really opened up about what they’re working on, and they’re listening much more closely to what others are telling them. Of course, Microsoft pays a lot of attention to us MVPs, both at the Summit and in the mailing lists they host to discuss things with us. But they also are interested in what their regular users are saying. I mentioned User Voice above, where Microsoft program managers are reading the suggestions that users are posting. They also pay attention to the Send-a-Smile/Frown feedback; I always include my email in the things I send in, and I’ve gotten responses on at least a couple of the items I’ve submitted.

This is not the same Microsoft we’ve known all these years.

 

Peltier Tech Chart Utility

New Survey: What Spreadsheet Programs Do You Use?

Please participate an improved, small, non-scientific survey about spreadsheet version usage.

My previous survey allowed only one version of Excel to be selected for work and for home. I knew that this was limiting, but the survey seemed easier to set up this way. But a few comments corrected my thinking, and a few responses helpfully had multiple versions entered as “Other”, so I’m going to set that one aside, and offer the following survey. As it turns out, it was not really any harder to set it up. Live and learn. You can select any and all spreadsheet versions that you use.

WordPress sometimes does funny stuff with embedded content like this survey. If the survey does not appear when the page is first loaded, wait a few seconds, then refresh the page.

[field name=iframe]

Peltier Tech Chart Utility

Survey: What Version of Excel Do You Use?

Update: Survey closed, Results posted

This quick and dirty survey has been closed. Thanks to all who participated. Results will be posted in the near future. In addition, a newer and better version of the survey will be/has been posted at New Survey: What Spreadsheet Programs Do You Use?

The original survey (shown below) allowed one response each for version of Excel used at work and home. This ignores those who use multiple versions, for example, developers who need too make sure their spreadsheets will work on any version of Excel.

Excel Version Usage Survey

My main intention in this survey was to get an idea of how many Excel users have upgraded to “New” versions (2007 and 2010), and how many are still using the “Classic” versions (1997 through 2003). The following chart shows my findings.

Breakdown of Excel versions in use

So about 86% of users have upgraded, while 14% are staying pat. The percentage was almost identical for usage at work and at home. I’m not sure if I’m surprised.

A handful of respondents entered multiple versions into the “Other” boxes in the survey; if these responses included both Classic and New, I didn’t count them. The follow-up survey will try to capture this usage more accurately.

Peltier Tech Chart Utility

Getting Answers For Your Excel Questions

NOTE: Please do not reply to this post with a question. Read this post to see where and how to ask your question.

You’ve exhausted the built-in and online help provided by Microsoft, and don’t have what you need. So where do you go to get help in Excel?

There are a large number of resources available to you. Search engines, online forums, and a number of useful general Excel topic web sites, and a huge selection of Excel books.

What Have You Tried?

In What Have You Tried?, Matt Gemmell bemoans the questions from people who have not done their homework. Here are some preliminary suggestions from Matt to ensure that you are ready to be helped:

  • Have you broken the question or problem down sufficiently to really ask something concrete?
  • Is your problem the sort of standard question for which there’s definitely already some sample code and documentation available? Skim the documentation, or do a quick search.
  • Try searching the web. If you’re having trouble getting a decent result, you need to narrow things down. Someone else has probably asked your question – or maybe a hundred someones.

Direct Inquiries

It’s tempting to send an email directly to me or to another expert whose previous website or forum post has been helpful. I get a dozen or more unsolicited emails a week asking for general Excel help. I welcome questions and clarifications regarding topics posted on my web site, but I don’t often have the time (or motivation) to address emails out of the blue. It’s more effective to post a question on a forum with a broader audience (see below), because many more people will see the question, and several people will respond to a public post before a single busy individual even notices a stray email. I’d rather answer a public question, because it becomes part of the body of public knowledge, more people will see it, and Google will have a chance to pick it up.

Posting a question in a comment to an unrelated blog post is also not very effective. You’ll either have your comment deleted, or if you’re lucky you might get a link to a more relevant post. If you’re on someone’s blog, use their search box to find a more relevant post yourself.

Search Engines

Go to the source, Google. Search for a few related keywords. If the results aren’t what you need, they may at least give you ideas for better keywords. I even use Google to search Microsoft’s site. Use site:microsoft.com as one of your keywords to focus the search on microsoft.com.

Forums

There are a large number of forums devoted to Excel. For some reason, new forums keep popping up, even though a new forum lacks the core of experts and depth of archived information of an established forum. The established forums include Mr Excel, OzGrid, Excel Help Forum, Experts Exchange. I visit various forums from time to time to see if I can answer a few questions.

Choosing a Forum

Qualities of a good forum include:

  • Lots of traffic: dozens or hundreds of new threads each day.
  • A long history: archives extending back five years or more.
  • Recognized experts: members with designations indicating expertise (but watch out for too much game-like clutter, like badges and medals and point counts).
  • Relatively few unanswered threads.

How to Ask a Forum Question

  • Take a couple minutes to try Google first. Many questions have been asked and answered a thousand times before.
  • Spend a few minutes searching the forum’s archives.
  • Use a descriptive subject line. I skip posts with subjects like Help!! or Excel Question.
  • Write a concise and clear problem statement. State what you are trying to do, what steps you took, and what happened. (Sometimes framing a question well is enough to clarify the problem in your own mind, and you figure it out yourself.) If you get an error message, include the description in its entirety and not just the error number. Sure it’s an effort to retype it, but the error number may correspond to several different descriptions. Error number 1004 means a procedure halted during execution: very informative.
  • Include the Excel version somewhere in your problem statement.
  • Write clearly, DON’T TYPE IN ALL CAPITALS, don’t use text message shorthand LOL, proofread your post.
  • Don’t look for a button to upload your workbook. If you can’t describe your problem without attachments, most people won’t bother trying to answer. If someone wants more information, they will ask for a workbook.
  • Be courteous and patient. If your question isn’t answered within several hours or a day, reread the question.
  • Don’t bump your own post to bring it to the top of the list. Not everyone is in your time zone or takes breaks when you do, so let your question mellow until someone finds it. Also, adding a post to the thread, even just yourself bumping it, makes the thread look answered, so someone who is looking for unanswered questions will skip yours.

Why isn’t the Forum Answering My Question?

  • Is the subject line vague, as in I need excel help!!!?
  • Is it so simple that a Google search would have uncovered the answer in 60 seconds?
  • Is it written so poorly that nobody wants to try to decipher it?
  • Does it look too much like you want someone to do your homework?
  • Does it look like you’re asking for someone to do a whole project for free?
  • Is it written discourteously?

A Few Forums

Mr Excel is undoubtedly the best of the forums. Mr Excel receives more traffic than the rest, hosts a huge archive of solved issues, and has a large number of knowledgeable users willing to answer questions.

OzGrid used to be focused more on revenue than on the content of the forum, but this has improved in recent years. OzGrid has decent traffic, a good archive, and a number of experts who respond to posts.

While Mr Excel and OzGrid receive a meaningful amount of traffic, most other forums don’t seem to have critical mass.

StackOverflow and SuperUser, which are respectively developer- and power-user-targeted partner forums, have moderate amounts of Excel traffic, and I like the flow of their layout. They are relatively new, but their Excel traffic and archive are expanding.

Microsoft has a number of relevant forums, such as Microsoft Office ExcelExcel for Developers, and VBA. Microsoft’s used to host very good newsgroups, but they ditched the newsgroups in favor of their own forums. The early versions of the forums were unusable. (MS suffers from an incredible case of Not Invented Here, so they are continuously reinventing the wheel, and as we know, Wheel 1.0 is often square.)

The Microsoft forums have improved, and they get a huge amount of traffic, redirected no doubt from the helpless online help, but they lost most of their knowledgeable members when the newsgroups were liquidated. Instead, numerous helpdesk-style contractors respond semi-intelligibly on Microsoft’s behalf.

Excel Help Forum is somewhat active, but you can scroll through a few days’ new posts in just a couple minutes.

Experts Exchange is a good quality forum, but it’s a paid membership service, and its structure is very constraining. Plus it’s overly concerned about scoring answers, to the extent that once a responder got upset that I added to his answer, because he didn’t want to share points. Sheesh, if I’m trying to answer someone’s question, I care if the answer helped. Why do I need points to prove how smart I am?

VBA Express and XL Guru are of good quality, but unfortunately their traffic is too low. Tek-Tips has Office and VBA forums which are intermediate in quality and traffic.

LinkedIn has recently appeared on the scene with its forums. So far most of these these forums have had no value. Most members have no apparent experience with forums, have not developed forum etiquette, and ask dumb questions with answers found easily elsewhere. Then someone will give a lame answer as if it’s actually helpful, and seventeen others will respond with “Me, too!”

I once suggested that a member of a LinkedIn forum visit Mr Excel, and was told by several forum members that the personal interaction on the LinkedIn forum was a great feature. Uh, what? You get the same interaction on Mr Excel and the other forums, only it’s with smart people who can actually help. I no longer spend any time on the LinkedIn forums.

Specific Web Sites

There are a large number of useful Excel web sites and blogs. You’ll find most of them if you use Google well, and if you take note of who is answering questions on the forums. But here are a few noteworthy sites.

Chip Pearson’s Excel Source has hundreds of pages covering a wide variety of topics using worksheet formulas and VBA.

The Spreadsheet Page is John Walkenbach (Mr Spreadsheet)’s site filled with free tips, downloads, and other stuff, plus information about his books and other Excel products.

ExcelUser is Charley Kyd’s site directed toward business users of Microsoft Excel. ExcelUser has written about Excel dashboards, and offered dashboard-related products for Excel, since authors of other dashboard web sites were still in grade school.

Contextures is Debra Dalgleish’s site of Excel Tips and Tutorials. If you have a question about pivot tables, autofilters, conditional formatting, data validation, you’ll find the answer here.

Chandoo.org has a number of free and paid Excel learning resources by everyone’s favorite, Chandoo.

Excel Books

There are innumerable books on all aspects of working with Excel. I won’t list them here; instead I’ll direct you to my web page that lists the Excel Books that I own and actually use. (I own others that I don’t find useful, and I’ve left them off the list.) I update this book list every so often, when I’ve gotten a couple new books or when a new version of Excel has been released. Disclosure: the book page contains Amazon Affiliate links, as does the sidebar of this page, so if enough of you buy enough books, I’ll be able to start posting from exotic vacation destinations.

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

Create Excel dashboards quickly with Plug-N-Play reports.