Welcome to Excel 2007 Charts. This book is intended for spreadsheet users who want to get the most out of Excel's charting and features. I approached this project with one goal in mind: to write the ultimate Excel charting book that would appeal to users of all levels.
As you probably know, most bookstores offer dozens of Excel books. The vast majority of these books are general-purpose user guides that explain how to use the features available in Excel (often by simply rewording the text in the help files). Most of these books include a chapter or two that cover charts and graphics. None, however, provides the level of detail that you'll find in this book.
I've used Excel for almost 15 years, and I've been creating charts for more than 30 years. Back in the precomputer days, I often spent hours creating a publicationquality chart by hand, using rulers, graph paper, and rub-off lettering. Today, creating such a chart with Excel would require only a few minutes — and would probably look much better.
Many Excel users tend to overlook the powerful charting features available. For many, creating anything but the simplest chart often seems like a daunting task. This book starts with the basics and covers every aspect of charting, including macros. If I've done my job, working through this book will give you some new insights and perhaps a greater appreciation for Excel.
This is not a book for beginning Excel users. If you have absolutely no experience with Excel, this may not be the best book for you. To get the most out of this book, you should have some background using Excel. Specifically, I assume that you know how to:
Create workbooks, enter data, insert sheets, save files, and perform other basic tasks.
Navigate through a workbook.
Use the new Excel 2007 Ribbon interface.
Work with dialog boxes.
Create basic formulas.
Use common Windows features, such as file-management and copy-and-paste techniques.
Later chapters cover VBA programming, and the main focus there is on creating and controlling charts using VBA. These chapters assume a basic knowledge of VBA, and those who have some experience with VBA will benefit most from these programming chapters. They'll be able to customize the examples and make them even more powerful. These chapters may inspire nonprogrammers to spend some time understanding VBA.
Note
Most of the material in this book is also relevant to those who need to create charts in Microsoft Word 2007 or Microsoft PowerPoint 2007. Choose Insert
To use this book, you need to have a copy of Microsoft Excel 2007 for Windows. No exceptions. Excel 2007 is so different from previous versions that this book doesn't even make an attempt at backward compatibility. If you use a previous version of Excel, locate a copy of the initial edition of this book (Excel Charts, published in 2003 by Wiley).
To use the examples on the companion CD-ROM, you'll need a CD-ROM drive. The examples on the CD-ROM are discussed further in Appendix A, "What's on the CD-ROM?"
Hardware requirements? The faster the better. And, of course, the more memory in your system, the happier you'll be. I strongly recommend using a high-resolution video mode: at least 1024 × 768 and preferably higher. When working with charts, it's very convenient to be able to see lots of information without scrolling. My normal setup is a dual-monitor system, with two 1600 × 1200 displays.
Take a minute to skim the following sections and discover some of the typographic conventions used throughout this book.
Excel 2007 features a brand new "menuless" user interface. In place of a menu system, Excel uses a context-sensitive Ribbon system. The words along the top (such as Home, Insert, Page Layout, and so on) are known as tabs. Click a tab, and the Ribbon displays the commands for the selected tab. Each command has a name that is (usually) displayed next to or below the icon. The commands are arranged in groups, and the group name appears at the bottom of the Ribbon.
The convention I use is to indicate the tab name, followed by the group name, followed by the command name. So, the command used to toggle word wrap within a cell is indicated as follows:
HomeAlignmentWrap Text
When a chart is selected, Excel displays additional "contextual" tabs. So, for example, to add a title to the selected chart, I indicate the command as follows:
Chart ToolsLayoutLabelsChart TitleAbove Chart
Formulas usually appear on a separate line in monospace font. For example, I may list the following formula:
=VLOOKUP(StockNumber, PriceList,2, False)
This book also contains examples of VBA code. Each listing appears in a monospace font; each line of code occupies a separate line. To make the code easier to read, I typically use one or more spaces to create indentations. Indentation is optional, but it does help to delineate statements that go together.
If a line of code doesn't fit on a single line in this book, I use the standard VBA line continuation sequence: a space followed by an underscore character. This indicates that the line of code extends to the next line. For example, the following two lines comprise a single VBA statement:
If ActiveChart Is Nothing Then _ MsgBox "Please select a chart or activate a chart sheet."
You can enter this code either exactly as shown on two lines, or on a single line without the trailing underscore character.
Names of keys on the keyboard appear in normal type — for example, Alt, Home, PgDn, and Ctrl. When you should press two keys simultaneously, the keys are connected by a plus sign: "Press Ctrl+G to display the Go To dialog box."
Excel's worksheet functions appear in all uppercase characters, like so: "Use the SUM function to add the values in column A."
Unless you're dealing with text inside quotation marks, Excel is not sensitive to case. In other words, both of the following formulas produce the same result:
=SUM(A1:A50) =sum(a1:a50)
Excel, however, will convert the characters in the second formula to uppercase.
In Part III, the VBA chapters, terms such as names of objects, properties, and methods that appear in code listings show up in monospace type in regular paragraphs as well: "In this case, Application.ActiveChart is an object, and HasTitle is a property of the object." Macro and procedure names appear in normal type: "Execute the UpdateChart procedure." I often use mixed uppercase and lowercase letters to make these names easier to read. Named ranges appear in italic: "Select the WeeklySales range."
The mouse terminology in this book is all standard fare: pointing, clicking, right-clicking, dragging, and so on. You know the drill.
Throughout the book, icons appear next to some text to call your attention to points that are particularly important.
Note
I use Note icons to tell you that something is important — perhaps a concept that may help you master the task at hand or something fundamental for understanding subsequent material.
Tip
Tip icons indicate a more efficient way of doing something, or a technique that may not be obvious. These will often impress your officemates.
Note
These icons indicate that an example file is on the companion CD-ROM. (See Appendix A for more details about the CD-ROM.)
Note
I use the Cross-Reference icon to refer you to other chapters that have more to say on a particular topic.
Warning
I use Warning icons when the operation that I'm describing can cause problems if you're not careful.
I had many ways to organize this material, but I settled on a scheme that divides the book into three main parts.
Part I: Chart Basics
This Part is introductory in nature and consists of Chapters 1 through 6. Chapter 1 presents an overview of Excel's charting features. Chapter 2 presents some terminology and introduces the types of charts Excel supports. In Chapter 3, I discuss various ways to work with chart data series. Chart formatting and customizations are covered in Chapter 4. Chapter 5 discusses chart analytical features, such as trendlines and error bars. The Part concludes with Chapter 6, a discussion of other types of graphics supported by Excel.
Part II: Mastering Charts
Part II consists of seven chapters that cover intermediate to advanced material. Chapter 7 covers interactive charts — charts that can be modified easily by an end user. Chapter 8 contains a wide variety of common and not-so-common charts, including ways to generate quite a few "nonstandard" charts. Chapter 9 discusses several ways to display data directly in cells, including small "sparkline" charts. Chapter 10 covers mathematical and statistical charting techniques. In Chapter 11, I cover pivot charts (charts generated from a pivot table). Chapter 12 offers suggestions to help you avoid common mistakes and make your charts more visually appealing. The final chapter in this Part is Chapter 13, which is devoted to nonserious charting applications, yet contains lots of useful information.
Part III: Using VBA with Charts
The three chapters in Part III deal with VBA. Chapter 14 presents an overview of VBA as well as some basic VBA charting examples, and Chapter 15 shows more advanced examples. Chapter 16 discusses Excel's color system for VBA programmers.
Appendixes
You can use this book any way you please. If you choose to read it from cover to cover while lounging on a sunny beach in Kauai, that's fine with me. More likely, you'll want to keep it within arm's reach while you toil away in your dimly lit cubicle.
Owing to the nature of the subject matter, the chapter order is often immaterial. Most readers will probably skip around, picking up useful tidbits here and there. If you're faced with a challenging task, you may want to check the index first to see whether the book specifically addresses your problem.
At the back of the book, you'll find a coupon that you can redeem for a discounted copy of my award-winning Power Utility Pak — a collection of useful Excel utilities plus many new worksheet functions. I developed this package using VBA exclusively.
You can also use this coupon to purchase the complete VBA source code for a nominal fee. Studying the code is an excellent way to pick up some useful programming techniques.
You can take the product for a test drive by installing the trial version from the companion CD-ROM.
I'm always interested in getting feedback on my books. The best way to provide this feedback is via e-mail. Send your comments and suggestions to:
| [email protected] |
Unfortunately, I'm not able to reply to specific questions. Posting your question to one of the Excel newsgroups is, by far, the best way to get such assistance. For more information about newsgroups, see Appendix B, "Other Charting Resources."
Also, when you're out surfing the Web, don't overlook my Web site ("The Spreadsheet Page"):
http://www.j-walk.com/ss/ |
Now, without further ado, it's time to turn the page and expand your charting horizons.