Author: Bill Jelen
Publisher: Que, 2010
Aimed at: Intermediate level Excel users
Pros: Outstanding quantity and quality
Cons: Not suitable for complete beginners or casual users
Reviewed by: Janet Swift
This book's goal, stated in its Introduction is
"to make you more efficient and effective in creating visual displays of information in Excel 2010".
The use of the word "more" suggests that author Bill Jelen expects that the reader already be able to use both Excel and its charting capabilities. In view of this he dives straight in without any of the slow-paced introductory stuff you would expect if you were a complete beginner.
He does however expect you to be new to Excel 2010 and so its new features are the topic of Chapter 1 which is a well illustrated overview of charting in Excel 2010 the first few pages of which will aid any legacy users (i.e. those moving from Excel 2003 or earlier). The second half of the chapter gets on to some detailed aspects of chart placement and data selection and it concludes by looking at the Chart Layouts gallery and how to create a custom theme and even share it with others.
Chapter 2: Customizing Charts looks in more detail at how to control various elements in a chart and covers formatting chart elements using the Layout tab, Format tab and the even more powerful Format dialog box, culminating in an example in which the Format Data Series dialog is used to replace data markers with clip art.
Choosing the correct chart for different situation is both an art and a science - and it is something that many users are unaware of and is also often ignored in books. It is refreshing therefore that Jelen covers this aspect pretty thoroughly in the next three chapters that look at creating charts to show trends, show differences and show relationships.
Along the way he covers all the basic chart types - column, line, pie, bar and XY scatter - discussing when to use them and also when to avoid them. He also introduces lesser known charts such as High-Low-Close, 100 percent stacked bar, Doughnut and Waterfall charts and techniques such as adding a second series. Chapter 6 is devoted to Stock Analysis charts and introduces Candlestick charts to complement Open- High-Low-Close and Line charts. This chapter concludes with creating a live chart using a web connection and making charts small for use in dashboards.
In Chapter 7: Advanced Chart Techniques we go beyond the commonplace and if you like hints and tips there's plenty here, starting with mixing two chart types on a single chart, something that is no longer available via the Chart Type dialog. Then there's a section on dynamic charts which covers the functions needed to create the worksheet underlying such a chart and the chapter concludes with four examples of charts created with VBA, two of which were competition entries to MrExcel.com, the site run by this book's author, where more such charts can be seen.
We are in Excel's advanced territory in Chapter 8: Creating and Using Pivot Charts. It is a fairly short chapter and assumes that you know what Pivot tables are about. The chapter concludes:
Pivot charts are about as high-tech as you can get. In some cases you do not need a chart to present your data. Chapter 9, "Using Spartklines, Data Visualizations, and Other Nonchart Methods", goes low-tech, showing you how to use the new sparkline feature to buildgraphic displays of information rght in your spreadsheet cells.
If you want to know what Sparklines are, how to present them and how to control them the next 10 pages are everything you need and they are followed by an equally useful section on Data Bars. Chapter 9 concludes with two ingenious methods for creating visual displays without Excel's charting facilities, one using conditional formatting, the other using the REPT function.
As it's title "Presenting Excel Data on a Map Using MapPoint" suggest, you'll need additional software for Chapter 10 but it's worth flipping through to see how poweful a combination Excel and MapPoint can be.
SmartArt, which is the successor to the six types of business diagrams in Excel 2003, has been expanded in Excel 2010 and now offers 129 types and has been improved. In particular each shape now has an associated text editor with two levels - headlines and body copy - and 30 styles allow an image to be included. As well as the basic of using SmartArt, Chapter 11 gives examples of organization charts, Venn diagrams and layouts using arrows, gears, funnels and discusses choosing a suitable layout for your message. The end of the chapter looks at WordArt and at Shapes, known previously as AutoShapes. The idea of assigning a formula to a shape is given as an example and there's a link to a video in the MrExcel gallery on You Tube - similar links are in most chapters of the book.
Chapter 12 has the topic "Exporting Charts for Use Outside of Excel" - specifically into PowePoint, Word, onto the Web or converting them to XPS or PDF and Chapter 13 looks at "Using Excel VBA to create Charts" - starting with the advice "You shouldn't be intimidated by VBA". then covering enabling VBA in Excel, the VBA editor, using the macro recording facility and then getting down to the 73 chart types in VBA - listed in a table. There's quite a lot of code in this chapter including a complete listing in the Case Study: Printing a Chart for Each Customer.
I really enjoyed the final chapter - Chapter 14 Knowing When Someone is Lying to You with a Chart. It covers the misuse of perspective and scale, size of charts and of chart elements, declaring that pyramid and cone charts should be banned on the grounds of their blatant misrepresentation. It also advises you to "avoid stacked surface charts" and to "chart something else when numbers are too bad.
So what about the book as a whole?
It starts at a fast pace and the pace never seems to slacken. It will appeal to the Excel user who has used charts and wanted to do more - either in terms of their information value or in terms of creativity/individuality. However, it would overwhelm the complete beginner and frustrate anyone who only wants to produce a few bar charts - but it has found a permanent place on my bookshelf.