Excel 2013 Charts And Graphs

C o n t e n t sMrExcelLIBRARYExcel 2013Charts and GraphsBill JelenQue Publishing800 East 96th Street,Indianapolis, Indiana 46240 USA1234567891011121314Aa taG l a n c eIntroduction: Using Excel 2013 to Create Charts .1Introducing Charts in Excel 2013.7Customizing Charts .35Creating Charts That Show Trends .77Creating Charts That Show Differences .113Creating Charts That Show Relationships .147Creating Stock Analysis Charts .171Advanced Chart Techniques .197Creating Pivot Charts and Power View Dashboards .223Using Sparklines, Data Visualizations, and OtherNonchart Methods .249Presenting Excel Data on a Map .275Using SmartArt Diagrams and Shapes .287Exporting Charts for Use Outside of Excel.317Using Excel VBA to Create Charts .333Knowing When Someone Is Lying to You with a Chart .401Charting References .411Index .417

Excel 2013 Charts and GraphsCopyright 2013 by Que PublishingAll rights reserved. No part of this book shall be reproduced, stored in aretrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from thepublisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in thepreparation of this book, the publisher and author assume no responsibilityfor errors or omissions, nor is any liability assumed for damages resultingfrom the use of the information contained herein.Associate PublisherGreg WiegandExecutive EditorLoretta YatesManaging EditorSandra SchroederDevelopment EditorCharlotte KughenProject EditorSeth KerneyISBN-13: 978-0-7897-4862-1ISBN-10: 0-7897-4862-2Copy EditorLibrary of Congress Cataloging-in-Publication data is on file.IndexerBarbara HachaKen JohnsonPrinted in the United States of AmericaFirst Printing: February 2013ProofreaderTrademarksTechnical EditorAll terms mentioned in this book that are known to be trademarks or servicemarks have been appropriately capitalized. Que Publishing cannot attest tothe accuracy of this information. Use of a term in this book should not beregarded as affecting the validity of any trademark or service mark.Warning and DisclaimerEvery effort has been made to make this book as complete and as accurate aspossible, but no warranty or fitness is implied. The information provided is onan “as is” basis. The author and the publisher shall have neither liability norresponsibility to any person or entity with respect to any loss or damages arising from the information contained in this book or from the use of the CD orprograms accompanying it.Bulk SalesQue Publishing offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales. For more information, please contactU.S. Corporate and Government or sales outside of the U.S., please contactInternational Salesinternational@pearsoned.comKathy RuizBob UmlasPublishing CoordinatorCindy TeetersMultimedia DeveloperDan ScherfInterior DesignerAnne JonesCover DesignerAnne JonesPage LayoutJake McFarland

ContentsIntroduction: Using Excel 2013 to Create Charts . 1Choosing the Right Chart Type .1Using Excel as Your Charting Canvas.2Topics Covered in This Book .3This Book’s Objectives .4Versions of Excel .4Conventions Used in This Book .4Special Elements in This Book.4Next Steps.51 Introducing Charts in Excel 2013 . 7What’s New in Excel 2013 Charts .7Choosing Among the Three Ways to Create a Chart.10Creating a Chart from the Quick Analysis Icon .10Inserting a Recommended Chart .11Creating a Chart Using the Other Icons on the Insert Tab .14Creating a Chart Using Alt F1.15Changing the Chart Title .16Editing a Title in the Formula Bar .16Why Can’t Excel Pick Up the Title from the Worksheet? .17Assigning a Title from a Worksheet Cell .18Handling Special Situations .19Charting Noncontiguous Data .19Charting Nonsummarized Data .22Charting Differing Orders of Magnitude Using a Custom Combo Chart.23Reversing the Series and Categories in a Chart .24Changing the Data Sequence by Using Select Data .25Using the Charting Tools.26Introducing the Three Helper Icons .26Introducing the Format Task Pane.27Using Commands on the Design Tab .30Micromanaging Formatting Using the Format Tab .31Using Commands on the Home Tab .31Changing the Theme on the Page Layout tab .32Moving Charts.32Moving a Chart Within the Current Worksheet.33Moving a Chart to a Different Worksheet .34Next Steps.34

ivExcel 2013 Charts and Graphs2 Customizing Charts .35Accessing Element Formatting Tools .35Identifying Chart Elements .37Recognizing Chart Labels and Axes .37Recognizing Analysis Elements.39Identifying Special Elements in a 3D Chart .40Formatting Chart Elements.41Moving the Legend .41Changing the Arrangement of a Legend .42Formatting Individual Legend Entries .43Adding Data Labels to a Chart .43Adding a Data Table to a Chart .46Formatting Axes .47Displaying and Formatting Gridlines .55Formatting the Plot Area and Chart Area .61Controlling 3D Rotation in a 3D Chart .65Forecasting with Trendlines .66Adding Drop Lines to a Line or Area Chart .69Adding Up/Down Bars to a Line Chart .70Showing Acceptable Tolerances by Using Error Bars.71Formatting a Series.72Formatting a Single Data Point .73Replacing Data Markers with Shapes.73Replacing Data Markers with a Picture .73Changing the Theme Colors on the Page Layout Tab.74Storing Your Favorite Settings in a Chart Template .75Next Steps.753 Creating Charts That Show Trends .77Choosing a Chart Type .77Column Charts for Up to 12 Time Periods .77Line Charts for Time Series Beyond 12 Periods .77Area Charts to Highlight One Portion of the Line.79High-Low-Close Charts for Stock Market Data .79Bar Charts for Series with Long Category Labels.79Pie Charts Make Horrible Time Comparisons .80100 Percent Stacked Bar Chart Instead of Pie Charts .80Understanding Date-Based Axis Versus Category-Based Axis in Trend Charts .80Converting Text Dates to Dates.83Plotting Data by Numeric Year .88Using Dates Before 1900 .89Rolling Daily Dates to Months Using a Pivot Chart.91Using a Workaround to Display a Time-Scale Axis .93Communicate Effectively with Charts .96

ContentsvUsing a Long, Meaningful Title to Explain Your Point .96Highlighting One Column .100Replacing Columns with Arrows .101Highlighting a Section of a Chart by Adding a Second Series .102Changing Line Type Midstream .103Adding an Automatic Trendline to a Chart.105Showing a Trend of Monthly Sales and Year-to-Date Sales .106Understanding the Shortcomings of Stacked Column Charts.108Shortcomings of Showing Many Trends on a Single Chart.110Next Steps.1114 Creating Charts That Show Differences .113Comparing Entities .113Using Bar Charts to Illustrate Item Comparisons.113Adding a Second Series to Show a Time Comparison .115Subdividing a Bar to Emphasize One Component .116Showing Component Comparisons .117Using Pie Charts.120Switching to a 100 Percent Stacked Column Chart .126Using a Doughnut Chart to Compare Two Pies .127Dealing with Data Representation Problems in a Pie Chart .129Using a Waterfall Chart to Tell the Story of Component Decomposition .136Creating a Stacked and Clustered Chart .138Next Steps.1455 Creating Charts That Show Relationships .147Using Scatter Charts to Plot Pairs of Data Points.148Creating a Scatter Chart .149Adding Labels to a Scatter Chart in Excel 2013 .149Showing Scatter Chart Labels in Excel 2010 .150Adding a Second Series to a Scatter Chart .151Joining the Points in a Scatter Chart with Lines.153Using a Scatter Chart with Lines to Replace a Line Chart .154Drawing with a Scatter Chart .155Testing Correlation Using a Scatter Chart .157Adding a Third Dimension with a Bubble Chart .159Using Charts to Show Relationships.161Using Paired Bars to Show Relationships.161Using a Frequency Distribution to Categorize Thousands of Points .163Using Radar Charts to Create Performance Reviews .166Using Surface Charts to Show Contrast .167Using the Depth Axis .168Controlling a Surface Chart Through 3D Rotation .168Next Steps.169

viExcel 2013 Charts and Graphs6 Creating Stock Analysis Charts .171Overview of Stock Charts .171Line Charts.171OHLC Charts .172Candlestick Charts .173Obtaining Stock Data to Chart .173Rearranging Columns in the Downloaded Data .174Dealing with Splits Using the Adjusted Close Column .175Creating a Line Chart to Show Closing Prices .177Adding Volume as a Column Chart to the Line Chart .180Creating OHLC Charts .182Producing a High-Low-Close Chart .182Customizing a High-Low-Close Chart .183Creating an OHLC Chart .184Adding Volume to a High-Low-Close Chart .186Creating Candlestick Charts .191Changing Colors in a Candlestick Chart .191Understanding High-Low Lines and Up-Down Bars .192Next Steps.1967 Advanced Chart Techniques .197Mixing Two Chart Types on a Single Chart .197Moving Charts from One Worksheet to Another .200Making Columns or Bars Float .200Using a Rogue XY Series for Arbitrary Gridlines.202Showing Several Charts on One Chart by Using a Rogue XY Series .207Creating Bullet Charts in Excel 2013 .212Creating a Thermometer Chart .217Creating a Benchmark Chart .219Creating a Delta Chart.220Next Steps.2218 Creating Pivot Charts and Power View Dashboards .223Creating a PivotChart Using Recommended Charts .223Changing the Fields in the Pivot Chart .225Sorting the Pivot Chart .226Grouping Daily Dates in the Pivot Chart.

vi Excel 2013 Charts and Graphs 6 Creating Stock Analysis Charts .171 Overview of Stock Charts .171