Countless new data visualization tools are at the disposal of anyone interested in creating charts and infographics. Yet, despite this recent rise of new options, there’s little doubt about which is the most widely spread charting tool out there: Microsoft Excel. And after interviewing Jorge Camões, of Excelcharts, a couple of years ago, today it’s a pleasure to bring you another of the top experts in visualization with Excel, Jon Peltier.
A metallurgist by training, with a Doctor of Science degree from MIT, Jon has over 20 years experience in research and development, and more recently in manufacturing and production support, in the aerospace, automotive, and industrial parts industries. His background also includes Statistics, Total Quality, and Six Sigma, and started using Excel and the rest of the Microsoft Office suite as part of his job: doing research, analyzing data, and writing reports. But he soon became obsessed with all things Excel, and was awarded as a Microsoft Excel MVP in 2001.
Through his company, Peltier Technical Services Inc, Jon designs and supports a range of commercial utilities that add charting functionality to Microsoft Excel. He also offers advanced training in Microsoft Excel, in topics related to charts, dashboard reports, programming, and interactivity between Office applications. And he does custom programming in Microsoft Excel to improve user productivity and allow faster analysis and display of data.
Earlier this year we had the opportunity to ask Jon a few question about Excel and visualization, for an interview that we are happy to share with you now:
Visualoop (VL) – Jon, last year, on Excel TV, you shared the story on how you started developing Excel solutions and tools. What was the hardest part of that whole process, of going from working in engineering to establishing yourself as a developer /consultant?
Jon Peltier (JP) – My transition from one career to another began nearly 20 years ago. I was working as a research metallurgist in a corporate research lab, studying advanced aerospace materials and processes for manufacturing them. Since we all had Excel as part of the corporate software infrastructure, that’s the tool I became familiar with. I started like most people, using Excel to hold data, perform routine calculations, and create rudimentary graphics.
After a while, I became bored with the tedium of manually repeating tedious calculations (ever use SOLVER in Excel?), and decided to make the computer work for me. I spent days with the manual (yes, a printed manual!) in my lap while chugging through the ancient XLM programming language. My first victory was a long script that turned a manual 4-hour process into an automatic 1-minute process.
While changing over from XML to VBA, I discovered the Microsoft Newsgroups, which were based on the old Usenet system. These were the best Excel online forums ever, but Microsoft scrapped them partly due to their famous Not-Invented-Here syndrome (which they have greatly reduced in recent years), and built their own terrible, horrible, no good, very bad forums. But these forums were good for learning all about Excel topics, including formulas, charting, and programming, and before long I was answering more questions than I was asking. I became a Microsoft MVP because of my contributions to the Excel community.
I was doing a lot of programming now, in support of my scientific and engineering work. I learned how to fudge a box plot (and numerous other graphical feats) in Excel, and I learned how to archive my Excel charts in PowerPoint slides, automatically. I kept improving my Excel skills, so that when the next aerospace industry recession hit I had an alternative to hunting for another engineering gig.
For a long time, people from the newsgroups would email me and ask if I could do their VBA project for them, but I was busy with my engineering job. But one day I actually picked up a couple small projects, and I worked nights and weekends on them, learning the intricacies of programming for Other People. When the layoff came, I was able to ramp up a couple existing projects and take on several more.
After doing contract-style project work for several years, I took a few of the tricks I’d developed over the years, and packaged them into Excel add-ins, that I began selling on my web site. I thought these would supplement the project work, but in fact, the Peltier Tech Chart Utilities have become my main business. I have Windows and Mac versions, and I’m starting development on an add-in which will link to Excel but run directly in PowerPoint. I’m still active in the forums, especially MrExcel.com, helping other Excel users, and I do occasional training gigs.
VL – So, in a nutshell – for our readers who are less familiar with Peltier Tech -, what services and solutions are you offering, and who can benefit from them?
JP – My main services are my Chart Utilities, which provide one-click generation of charts that aren’t native to Excel, but that you can make with a lot of effort. Waterfall charts, box plots, histograms and paretos, charts that combine clustered and stacked bars, etc. The utilities also have assorted features for formatting, labeling, and exporting charts. My target audience is fairly broad: industry, finance, academia, government.
VL – After all these years, why keep focusing on Excel? Is it “just” because it’s still the most wide-spread charting tool out there?
JP – Excel is everywhere, and it’s not just charting, it’s data and analysis. All the accountants have their data in Excel and they have packages that handle a lot of their crunching, in Excel. A great many analyses done in dedicated graphics packages start by importing Excel data.
Excel is also rather easy to automate with little or no programming experience. Intermediate users with tools no more advanced than the Macro Recorder and Google can build themselves decent time-saving programs, without involving corporate IT.
In fact, in my science and engineering days, we had Minitab as well as Excel. But Minitab was a pain to work with, not as flexible as Excel, and the outputs were awful. I’d recommended that Minitab build a plug-in so that it could get data from Excel, do its stuff, and dump the outputs in Excel, using Excel charts. Much of my work at the time was getting Excel to do the same analyses that were so painful in Minitab. It’s when I started developing Excel box plots.
VL – Ok, and when should a company avoid Excel as its primary charting tool?
JP – I’m not sure a typical company should avoid Excel as its primary charting tool. If users are careful with formatting and chart types, Excel can do 90% of what most users need (and 98% of what clever users need). The biggest data need for businesses is accounting, and spreadsheet programs built for accountants. I’ll bet the bean counters at Tableau use Excel.
VL – Back when you started, besides all the programming skills, you probably had to develop your visualization and data literacy level as well. What were your initial references in this specific area of expertise, and how did they impact your whole business and market approach?
JP – Some of the programming started in graduate school, where we used BASIC to control testing and data acquisition equipment, as well as process the data and plot the results (on an old HP multi-pen plotter). I took plenty of math as an undergrad, then minored in probability and statistics as part of my doctoral program.
When visual literacy became a thing, I probably cut my teeth on Edward Tufte, then gravitated towards Stephen Few as a more practical, helpful alternative. I’ve always been interested in the human brain, so I got into Few’s teachings about human cognition. Naomi Robbins is another good, practical source. There are a lot of other people in dataviz, but for my purposes of engineering and business data presentation, these were sufficient. Dashboards with simple charts, not infographics with esoteric designs.
VL – How about today, who are the folks that you are paying close attention to, related to data visualization?
JP – I follow a lot of data and viz accounts on Twitter, many as much for personality as for technicality. A quick rundown of my Twitter feed includes tweets from @AlbertoCairo, @DataRemixed (Ben Jones), @JuiceAnalytics, @arnicas (Lynn Cherny), @VizWizBI (Andy Kriebel), @jschwabish, @krees, @jenstirrup, @visualisingdata (Andy Kirk), @Freakalytics (Stephen McDaniel), @acotgreave, @camoesjo, @moritz_stefaner, @flowingdata (Nathan Yau), @eagereyes (Robert Kosara). I follow even more people in Excel and related technologies.
VL – After the popularization of “infographics” and data visualization in general, that started roughly in 2009, have you noticed any changes in your average client’s demands and data literacy levels?
JP – I think the revolution started much sooner than that. I took Edward Tufte’s class in Boston in late 2003 or early 2004, when I was still a metallurgist, and Stephen Few published ‘Show Me The Numbers’ in 2004.
When I started my programming career (also in 2004), I assumed that most of my work would be doing graphical projects for clients. But the real money is in automation, reducing the tedious, mind-numbing tasks that people are stuck with. Charting was a small subset of that, and if anything I was usually introducing automation to existing chart-based projects.
Most of the time clients have appreciated my guidance about improving their data presentation. I don’t really think people have become more aware of charting, and really, infographics are a far cry from day-to-day business graphics. I did have one project that I had to disentangle from after weeks of work because they insisted I revert to their original scheme, which was Excel 3D column and pie charts in the default Excel 2003 color scheme.
VL – Another consequence of that “boom” is that we are seeing lots of new DIY data visualization tools, programming languages and charting libraries coming out on a regular basis – some free, some paid. Have you been looking out for these new tools, have you tried some of them, or even worked in some project with it?
JP – As I mentioned, I used to work with Minitab. This was in support of corporate work in Total Quality, Six Sigma, Lean Manufacturing, Statistical Process Control, and other buzzwords. But Minitab is awful to use, and very good Excel-based tools have sprung up, including SPC for Excel (which I’ve collaborated on) and QI Macros.
Several years ago, when my work was still predominantly on client projects, I looked into R. I thought it was very capable, and this is when Hadley Wickham was coming out with his charting package ggplot. But none of my clients had a need for this kind of analysis.
I spent a little time with Tableau when it exploded onto the scene. Again, great capabilities, but no match for my clients. And at this time I was concentrating on commercializing my Chart Utilities.
More recently, when Plotly came out with an Excel add-in, I spent some time with that. Essentially it made it easier to get Excel data into your online Plotly library so you could build Plotly graphs. I even was interested in helping with development of their add-in, using many of the Excel dialog work I’ve done over the years, but time is always too short.
Also in recent years, Microsoft has developed some very capable Power BI tools, including Power Pivot, Power Map, Power Query.
VL – Now, throughout the years Excel changed a lot. From your business’ perspective, what were the key updates/fixes or overall changes in Excel that impressed you the most?
JP – As ugly as Excel’s default chart formats have been, the charting engine underlying it has always been flexible, so that someone like me could figure out ways to get the desired chart appearance.
For Office 2007, Microsoft rebuilt this charting engine from the ground up, making some major improvements, while making a few things harder (so I had to develop some new tricks). Office 2007 came out with a somewhat better color scheme; the glaring colors in the palette were de-emphasized, the muddy gray background was retired, and in fact, the palette was now relatively friendly to color-vision-deficient users.
Office 2013 made great improvements to default formats, with lighter gray lines for axes and gridlines, and better colors (probably the influence of Tableau). The interface for chart design and formatting was also improved.
Now, if we could just get rid of all the inane 3D chart types and the gratuitous graphical effects….
Excel has made strides with data handling as well as with graphics. In Excel 2003, the “List” was born, and this evolved in Excel 2007 to the “Table”. This is basically a worksheet structure that provides database-like functionality to a worksheet range: filtering, sorting, etc. This has been a killer feature, especially when combined with already-killer pivot tables and of course charts.
In recent releases, Excel has cleaned up many of its historical problems with statistical functions.
Excel’s integration with Power Pivot and other Power BI tools has expanded Excel’s feature set into business analytics.
VL – A bit of oversimplification-daydreaming now: If you had the chance to work on a new version of Excel, even build it from scratch, what would be your first changes/fixes, and why?
Well, Microsoft already knows about all of the bugs I encounter, and all of the little things I wish were different.
I would de-emphasize all of the stylistic stuff, the excessive formatting that is all to easy to apply to an otherwise fine graphic. I’d remove most of the 3D chart types. I’d make it easier to link chart elements (labels, titles, and axis scales) to the worksheet. I’d make it easier to adjust a chart’s data (extend all series by N rows in one operation, for instance, instead of series-by-series).
I’d bring back the F4 (Repeat Last Action) functionality for all chart operations, and I’d make it easier to bulk format charts. This right now is my greatest pain point.
VL – Finally, Jon, is there something are you currently working on, that you can share with our readers? What can we expect from Peltier Tech in the next, let’s say, 6 to 12 months?
JP – Microsoft has new versions of Office coming out for both Windows and Mac, and I will be upgrading my Chart Utilities. These upgrades are not just for compatibility with the new versions, they are also a vehicle for me to introduce new chart types and expansion of other features.
Earlier I mentioned my PowerPoint-based Chart Utilities, which are barely on the drawing board. I hope to have a preliminary Windows version soon, probably in conjunction with the new Office release, and eventually there will be a Mac version.
We really appreciate the time Jon dedicated answering our questions in such depth. Don’t miss his updates on Twitter (@Jon_Peltier and @PeltierTech) and visit Peltier Tech’s blog for a constant stream of Excel charting how-to’s and tips.