Preface to the First Edition
This is a story about the collaboration since 2003 between two mechanical engineers, one from Berlin with extensive experience in software development and the other from Kreuzlingen, Switzerland, who has worked as a management consultant, a businessman, and a professor of business administration at two universities of applied sciences. Several thousand hours of both independent and joint efforts have yielded a wealth of tips and tricks and as practical examples to show how to create good charts with Excel – without programming. Here we consider good or professional charts to be those that adhere to the HICHERT®SUCCESS Standards of Design.
Since two separate paths led us to this topic, we would also like to present this preface in two different parts.
From Rolf Hichert
I think the beginnings of this book can be traced back to a McKinsey seminar for new employees in New York in 1979. Speakers included Gene Zelazny who introduced the subject of visualizing business data. In my time as a consultant at McKinsey in Düsseldorf, I was constantly fascinated by the ability of some colleagues to transform complex business issues into understandable exhibits.
I took up this idea again in 1984 while I was still a professor at the University of Applied Sciences in Constance. I founded MIK together with Michael Moritz. The company objective was to develop management information systems for the PC, something completely new at the time, focused on the automatic creation of meaningful charts. With our products TZ-Info and later MIK-Info, we achieved a certain degree of recognition, and not just in German-speaking countries. BMW Motorcycles, Würth, and Credit Suisse were among our first major clients.
In 2001, as managing director of MIS Switzerland (subsequently part of the Infor Group), I revisited the subject of a standardized design concept for business analyses. In 2004, I (along with 500 other participants) had an opportunity to get acquainted with Edward Tufte at one of his events in Washington D.C. I then decided to give it one more try on my own. I became self-employed again and held seminars, gave talks, and organized customer projects on the ‘Professional Design of Reports and Presentations.’ In the early days there were seldom more than four or five (paying) participants.
The breakthrough then came when we received invitations from MIS AG to speak at seminars in Vienna and Berlin and from the Internationaler Controllerverein in Baden and in Munich. After that, I presented for the first time to companies such as ABB, Lufthansa, Telekom Austria, and Nestlé. Under the acronym SUCCESS, this has grown into several hundred in-house seminars for companies in nearly every industry. Altogether, several thousand participants have attended my own seminars and those of cooperating partners such as Vereon, Mazepoint, Controller Akademie, ZfU, OECI, and Bissantz.
The concept of creating good business charts gained new momentum when I met Holger Gerths in 2003. Holger was immediately taken by the idea of programming charts with a difference – different from those he was familiar with as a software developer at MIS AG. And after only two weeks, he and I presented a VBA solution that could create unique Excel charts using just a few parameters. But this did not become our chosen the path. Our main objective remains the creation of sophisticated charts using only standard Excel functions. We present here what we have achieved so far on this journey.
From Holger Gerths
And now I would like to add my part of the story. When I met Rolf Hichert in December of 2003, I was busy working on a BI project for a major pharmaceutical company, which was slowly coming to a close. Working in a project team, we had deployed the back- and front end of an extremely complex data warehouse using MS-SQL server, VB.NET, and MIS software (today known as Infor). Although the front end involved visualization issues, no one had given any thought to rules of design as it simply wasn’t an issue in this field at the time. But probably everyone at one time or another had wondered in private whether our reports were being understood and, if not, we often assumed it was due to incompetent recipients.
I first learned from my then new business partner and later good friend that a report is hard to understand when there are no standards of design. Rolf was the first to show me that, in addition to a clear title, a good exhibit should also have a clear message, which is properly highlighted. These enhancements in the form of arrows or ellipses would occupy us for many months later.
I would also like to take this opportunity to briefly explain how we succeeded in getting Excel to produce virtually every conceivable variation of chart, starting with simple tips and then ‘tricking’ Excel into generating totally abstract and clever results.
We began with writing totals above stacked column charts, a familiar trick today. In this case, we simply placed transparent columns over visible ones and then gave them labels. Our first trick was complete.
Just to clarify: we certainly weren’t the first to figure this out. But it was the beginning of what today has grown into a very extensive collection of chart tricks developed in many different ways, by searching the Internet, through our own discoveries, by fortunate coincidence, or through the kind assistance of many resourceful seminar participants, who sometimes knew how to do something better than we did.
The need to create multicolored axes stems from a requirement in the HICHERT®SUCCESS1 concept that elements with different meanings should also appear differently. Conversely there is a requirement that elements with the same meaning should also look the same, so the axis line should be used to distinguish between data categories, such as previous year and plan, by using different colors, shapes, and patterns. The secret to creating axis units of different colors is to use a separate data series for each color. Since every data series is based on a cell range for the source data, formula logic can be used to assign the desired color and thickness (width) to every-axis unit. This was the first time we began to view chart data series like clay that we could mold as needed.
We have a long history of developing the movable arrow in Excel charts – typical of all objects that move in a chart. We were really proud to be able to attach a picture – in this case an arrow – to a point in a chart, indirectly using a column chart. Later a seminar participant showed us how to do it without even bothering with a column chart. Before the multi-point trick to rotate or change the color of vectors in Excel shapes even existed, there had been numerous attempts to calculate arrow contours and to plot a scatter or X-Y chart using trigonometry functions and polar coordinates.
The trick with the time axis was our first approach to moving charts in the direction of the category-axis and scaling them. This included the option of plotting columns and bars with different widths. The advent of Excel 2007 unfortunately rendered this elegant trick useless and began the ‘phase of the offset function.’
It was the dawn of a new era for us when we succeeded in arranging any number of labels in a chart using points (from a scatterplot). A point can position a label in the chart area so that its text can then be linked to any cell. Showing and hiding the labels of small numbers became possible, as did the selective labeling of line charts, for example.
Even wavy contours on columns or bars were no longer an impossible task because columns – just like arrows - can be created and placed anywhere in a chart. A simple clever trick to exactly position texts, such as title information, messages, and footnotes, came in the form of transparent charts, behind which the labels were visible in cells.
What came next? Displaying pie segments in portfolio visualizations, managing scales using automatic axis scaling and fixed scaling, the 960x720-pixel trick to link with PowerPoint exhibits, etc., all contributed to an extensive catalogue of ‘tips and tricks,’ which is covered in the Appendix to this document.
Stacking several transparent charts on top of one another (positioned using the ALT trick) was our solution to incorporate any number of data series. After they are grouped, they can be copied and moved just like an individual chart. This has led to the emergence of the newest generation of Excel master templates that meet virtually every design need. The alternative is to plot them manually in PowerPoint to achieve the same effect.
In conclusion, I would like to thank everyone who has been willing to share one trick or another with me or who has placed trust in me to find solutions in the course of numerous projects. Bernd Held and Hartmut Erb provided us with support in the first version of our manuscript. Moreover, Jörg Knuth, Björn Rick, and Markus Wolff were always willing to discuss critical issues with us.
Preface to the Second Edition
The first edition sold out much faster than planned. However, this also presented us with an opportunity not only to correct minor errors and inconsistencies, but also to delve deeper into the special features of Excel 2010 – for example, incorporating screenshots. There have been no major changes in Excel, other than the new trick linking to PowerPoint. The current design standards and examples from SUCCESS are shown in the Appendix including the new dimensioning concept for charts, which is based on font size.
To avoid a potential misunderstanding, we would like to say at the outset that this book does not show you how to ‘quickly come up with a chart’ without taking specific design standards into account. You could use the wizard provided in Excel for that. Instead, we want to help you to develop chart templates based on a uniform concept of design and meaning which we call HICHERT®SUCCESS. If you frequently need a small number of similar charts, it is well worth investing some time and effort to develop them applying these standards. To avoid another misunderstanding, we would like to stress that we do not want to show you how to analyze a specific dataset in different ways. Instead, we want to explain our main design principles using one single example file. Once you have worked through this complex example file, you will then understand the principles behind these unusual tips and tricks and so be able to develop chart templates for chart types that are completely different from the stacked column chart shown here. To assist you, we have provided training materials and guidelines under www.hichert.com and www.hi-chart.com with various chart types and chart templates available free for download.
Holger Gerths and Rolf Hichert
STRUCTURE OF THE BOOK
This book shows you how to create charts in entirely different ways from those offered by the Excel chart wizard. Armed with the knowledge of new tips and tricks you can create virtually any kind of visualization (for example, new chart types) and any number of details (for example, highlighting and custom labels).
Chapter 1 provides an overview of the design concept of HICHERT®SUCCESS when applied to business charts.
Chapter 2 shows you how to create a practical column chart in 30 steps.
Chapter Appendix presents a few special topics, such as the professional way to link with PowerPoint or Word, and practical shortcuts with which you can put what you have learned into practice. In addition, we also present the current design recommendations from HICHERT®SUCCESS.
When you have worked through this book with the help of the practice file, which you can download from www.excel-book.hichert.com, you will have gained a new understanding of working with Excel charts. You will be able to use the tips and tricks shown here not only for the stacked column chart, but hopefully for other chart types such as bar, line, scatter, and bubble charts. Although most of the tips and tricks will work for all chart types in Excel, others may work differently. So we offer additional practical examples on our websites www.hichert.com and www.hi-chart.com.
We have put a lot of effort into verifying the information presented in this book and making sure that the information is presented as intended. That said, we are very interested in any errors you find, or information you find outdated, etc.
Known errors will be collected and presented at errors-ExcelBook.hichert.com, and we will update the book. To inform us about your findings, please send an email to errors-ExcelBook@hichert.com. For any correct notification that is not listed on the before mentioned page, you will receive an Amazon gift voucher.
1 Business Charts
1.1 Classification of Business Charts
1.2 Designing Professional Business Charts
1.3 Examples of Professional Business Charts
2 Creating a stacked Column Chart in Excel 2007 and 2010
2.1 Using the Excel Files
2.2 Source Data
2.3 Inserting a Column Chart
2.4 Moving the Chart in the Grid
2.5 Removing Unnecessary Elements
2.6 Maximizing the Plot Area
2.7 Correct use of Colors
2.7.1 Create a New Theme Color
2.7.2 Finding The Location of Theme Colors in Excel 2007 and 2010
2.8 Determining Cell Styles
2.9 Working with Excel Templates
2.10 Labeling Columns
2.11 Adjusting the Column Width
2.12 Changing the Vertical Order of the Column Series
2.13 Positioning the X-axis Line
2.14 Showing Sums Above Columns
2.15 Making Space to the Left and the Right of the Chart
2.16 Enable Freely Controllable Y-Scaling
2.17 Controlling the Y-axis
2.18 Free Positioning of Your Own X-axis Label
2.19 Create a Legend at the Right Chart Edge
2.20 Automatic Alignment of the Legend at the Last Filled Month
2.21 Switch the Legend From Left to Right
2.22 Insert a Vertical Line
2.23 Creating a Data-Directed Color Change in the X-axis
2.24 Add Freely Positionable Arrows as Graphics
2.25 Add Freely Positionable Arrows as Scatter Chart With Connection Line
2.26 Hide Labels of Small Chart Values
2.27 Insert Freely Positionable Column Labels
2.28 Integrate Chart Title
2.29 Create Page Grid
2.29.2 Link (Excel Camera)
3.1 Navigate in Charts
3.1.1 Navigation in Charts With Excel up to Version 2003
3.1.2 Navigation in Charts With Excel From Version 2007
3.2 Link Excel to PowerPoint
3.3.1 Shortcuts in Excel
3.3.2 Shortcuts in Windows
3.4 Tips and Tricks
3.5 Frequently Asked Questions (FAQ)
3.6 Font Size for Determination of Chart Dimensions
3.7 Standardized Colors, Shapes, and Symbols
3.8 Fourth Generation Charts
1 Business Charts
The term business chart means a chart that visualizes multiple series of numbers relating to business content. Today we come across business charts in PowerPoint presentations, internal management reports, external annual reports, and in the daily press or business magazines. Usually a business chart visualizes sales, earnings, market share, number of employees, or other business indicators, using entertaining pie or doughnut charts, cheerful line graphs, and good old column charts. They are usually a little too colorful and a bit too large, but serve the purpose of livening up hard-to-read texts and drab tables with a dash of creativity.
When we speak of ‘professional’ business charts, refer to charts that are ideally suited to providing interesting insights into business data resulting from analysis rather than decoration. In reports or presentations these charts convey important facts relating to business content as coherently as possible. Sophisticated business charts convey content, insights and messages that could not be communicated as effectively if they were just presented verbally. In Section 1.3, we present several practical examples of what we consider to be ‘professional,’ well-constructed business charts. The objective of Chapter 2 is to show you how to create such visualizations in practice using standard Excel functions.
The reason why most of today’s business publications contain charts of little significance with low information density (fewer elements, fewer dimensions) is also due to the limited number of standard Excel functions– particularly when working with Excel’s Chart Wizard. Excel is by far the most popular program currently used for creating charts for consultants and teachers, by qualified experts in companies, and by students. The aim of this book is to put as many interested users as possible in a position to create ‘professional’ charts in Excel using a number of tips and some basic tricks. Until now this could only be accomplished with special software and involving significant manual effort in PowerPoint.
Many components are used when designing business communication materials that include reports, statistical summaries, presentations, and dashboards. We need to distinguish here between charts and other components like:
Tables: Arrangements of numbers and words in columns and rows
Texts: Descriptions, explanations and comments
Pictures: Graphs such as flow charts, decision trees, organograms, photographs, screenshots or maps.
The terms charts and pictures are not always commonly interpreted.
Pictures are classified as visualizations that do not solely represent series of numbers. When a schedule analysis also includes allocations of capacity, milestones, and timeline constraints, we would call it a picture
Charts are classified as representations of figures or statistics designed to tell a story or explain the current situation. If a cost analysis only compares numbers, then we call it a chart.
The line between charts and pictures blurs, for example, when you look at bar visualizations of project cost analysis and compare them to bar visualizations for project s