Use Excel 2013’s radically revamped charting and graphing tools to communicate more clearly, powerfully, and quickly… so you drive your message home, and get the decisions and actions you’re looking for!   This book reveals data visualization techniques you won’t find anywhere else and shows you how to use Excel 2013 to create designer-quality charts and graphs that stand out from the crowd. It will help you make the most of new features ranging from Power View to Recommended Charts, and instantly share your insights with anyone, anywhere–even on the Web and social networks.   Learning advanced Excel techniques has never been easier. You’ll find simple, step-by-step instructions, real-world examples and case studies, and more than a dozen YouTube videos, straight from MrExcel!   •      Create stunning data visualizations instantly with Excel 2013’s new Recommended Charts •      Use charts to instantly reveal trends, differences, and relationships •      Map your data with Excel 2013, MapPoint, and the new GeoFlow add-in •      Quickly generate combo charts that once required complex, frustrating procedures •      Use sparklines to imbue worksheets with more context and insight •      Highlight and clarify the meaning of data with DataBars, color scales, icon sets, and other conditional formatting tools •      Post charts to Facebook, Twitter, or LinkedIn, directly from Excel •      Build stock charts that help you make smarter investments •      Solve “non-standard” problems such as noncontiguous data or custom data sequences •      Generate new charts automatically with Excel VBA •      Uncover visual tricks that people use to lie with Excel   About MrExcel Library:  Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will:   •      Dramatically increase your productivity–saving you 50 hours a year or more •      Present proven, creative strategies for solving real-world problems •      Show you how to get great results, no matter how much data you have •      Help you avoid critical mistakes that even experienced users make

Table of Contents:
Introduction: Using Excel 2013 to Create Charts .................. 1 Choosing the Right Chart Type ...........................................1 Using Excel as Your Charting Canvas ...............................2 Topics Covered in This Book ........................................3 This Book’s Objectives ...............................4     Versions of Excel ...................................................4     Conventions Used in This Book ......................................4     Special Elements in This Book .................................................4 Next Steps.................................................5   1 Introducing Charts in Excel 2013 ............................... 7 What’s New in Excel 2013 Charts ........................................................7 Choosing Among the Three Ways to Create a Chart ........................10     Creating a Chart from the Quick Analysis Icon .........................................10     Inserting a Recommended Chart .................................................................11     Creating a Chart Using the Other Icons on the Insert Tab ......................14     Creating a Chart Using Alt+F1 ............................................................15 Changing the Chart Title ................................................................16     Editing a Title in the Formula Bar ...............................................16     Why Can’t Excel Pick Up the Title from the Worksheet? ...............17     Assigning a Title from a Worksheet Cell ......................................18 Handling Special Situations ....................................................19     Charting Noncontiguous Data .........................................................19     Charting Nonsummarized Data ............................................22     Charting Differing Orders of Magnitude Using a Custom Combo Chart .............23     Reversing the Series and Categories in a Chart ...............................24     Changing the Data Sequence by Using Select Data ........................25 Using the Charting Tools .................................................................26     Introducing the Three Helper Icons ........................................26     Introducing the Format Task Pane ..............................................27     Using Commands on the Design Tab .....................................30     Micromanaging Formatting Using the Format Tab .......................31     Using Commands on the Home Tab ......................................31     Changing the Theme on the Page Layout Tab ..............................32 Moving Charts .......................................32     Moving a Chart Within the Current Worksheet .......................33     Moving a Chart to a Different Worksheet ..............................34 Next Steps................................................................34   2 Customizing Charts .................................35 Accessing Element Formatting Tools ...................................35 Identifying Chart Elements ...........................................37     Recognizing Chart Labels and Axes ...................................37     Recognizing Analysis Elements ...........................................39     Identifying Special Elements in a 3D Chart ........................40 Formatting Chart Elements ...................................................41     Moving the Legend .......................................................41     Changing the Arrangement of a Legend ............................42     Formatting Individual Legend Entries .........................................43     Adding Data Labels to a Chart .......................................................43     Adding a Data Table to a Chart .........................................................46     Formatting Axes ..........................................................47     Displaying and Formatting Gridlines .............................................55     Formatting the Plot Area and Chart Area .............................................61     Controlling 3D Rotation in a 3D Chart ..............................................65     Forecasting with Trendlines .......................................................66     Adding Drop Lines to a Line or Area Chart ..............................................69     Adding Up/Down Bars to a Line Chart ............................................................70     Showing Acceptable Tolerances by Using Error Bars .................................71 Formatting a Series ................................................72     Formatting a Single Data Point ........................................................73     Replacing Data Markers with Shapes............................................................73     Replacing Data Markers with a Picture ......................................................73 Changing the Theme Colors on the Page Layout Tab ....................................74 Storing Your Favorite Settings in a Chart Template ..................................75 Next Steps.......................................................................75   3 Creating Charts That Show Trends .......................77 Choosing a Chart Type ............................................................77     Column Charts for Up to 12 Time Periods ........................................77     Line Charts for Time Series Beyond 12 Periods ............................77     Area Charts to Highlight One Portion of the Line ..................................79     High-Low-Close Charts for Stock Market Data .................................79     Bar Charts for Series with Long Category Labels .......................................79     Pie Charts Make Horrible Time Comparisons .............................................80     100 Percent Stacked Bar Chart Instead of Pie Charts ............................80 Understanding Date-Based Axis Versus Category-Based Axis in Trend Charts ...........80     Converting Text Dates to Dates..........................83     Plotting Data by Numeric Year .................................88     Using Dates Before 1900 .......................................................89     Rolling Daily Dates to Months Using a Pivot Chart ..................................91     Using a Workaround to Display a Time-Scale Axis .......................................93 Communicate Effectively with Charts ...................................................................96     Using a Long, Meaningful Title to Explain Your Point ..................................96     Highlighting One Column .......................................................100     Replacing Columns with Arrows .....................................................101     Highlighting a Section of a Chart by Adding a Second Series .........................102     Changing Line Type Midstream .......................................................103 Adding an Automatic Trendline to a Chart.........................................................105 Showing a Trend of Monthly Sales and Year-to-Date Sales ..............................106 Understanding the Shortcomings of Stacked Column Charts ......................................108 Shortcomings of Showing Many Trends on a Single Chart ...........................................110 Next Steps.............................................................111   4 Creating Charts That Show Differences .........................113 Comparing Entities ....................................113 Using Bar Charts to Illustrate Item Comparisons .......................................113     Adding a Second Series to Show a Time Comparison ........................................115     Subdividing a Bar to Emphasize One Component ..........................116 Showing Component Comparisons ......................................................117     Using Pie Charts ..........................................................................120     Switching to a 100 Percent Stacked Column Chart ..............................126     Using a Doughnut Chart to Compare Two Pies ....................................127     Dealing with Data Representation Problems in a Pie Chart ..............................129 Using a Waterfall Chart to Tell the Story of Component Decomposition ...........................136     Creating a Stacked and Clustered Chart ..................................138 Next Steps............................................................................145   5 Creating Charts That Show Relationships .......................147 Using Scatter Charts to Plot Pairs of Data Points ..................................148     Creating a Scatter Chart ...........................................................149     Adding Labels to a Scatter Chart in Excel 2013 .....................................................149     Showing Scatter Chart Labels in Excel 2010 ..................................................150     Adding a Second Series to a Scatter Chart ..............................................151     Joining the Points in a Scatter Chart with Lines...............................................153     Using a Scatter Chart with Lines to Replace a Line Chart ..................................154     Drawing with a Scatter Chart ..........................................................155     Testing Correlation Using a Scatter Chart .....................................................157     Adding a Third Dimension with a Bubble Chart ..................................................159 Using Charts to Show Relationships ...................................................161     Using Paired Bars to Show Relationships .........................................161     Using a Frequency Distribution to Categorize Thousands of Points .....................163     Using Radar Charts to Create Performance Reviews ..........................................166 Using Surface Charts to Show Contrast .................................................................167     Using the Depth Axis ..........................................................................168     Controlling a Surface Chart Through 3D Rotation ................................168 Next Steps.................................................................................169   6 Creating Stock Analysis Charts ...............................171 Overview of Stock Charts ...................................................................171     Line Charts ...............................................................................171     OHLC Charts ....................................................................................172     Candlestick Charts .......................................................................173 Obtaining Stock Data to Chart .....................................................................173     Rearranging Columns in the Downloaded Data ................................174     Dealing with Splits Using the Adjusted Close Column ......................................175 Creating a Line Chart to Show Closing Prices ..................................................177     Adding Volume as a Column Chart to the Line Chart ...............................180 Creating OHLC Charts .............................................................182     Producing a High-Low-Close Chart .................................................182     Customizing a High-Low-Close Chart ............................................183     Creating an OHLC Chart .........................................................................184     Adding Volume to a High-Low-Close Chart .................................................186 Creating Candlestick Charts .......................................................191     Changing Colors in a Candlestick Chart ...............................................................191     Understanding High-Low Lines and Up-Down Bars .....................................192 Next Steps.................................................................196   7 Advanced Chart Techniques .........................................197 Mixing Two Chart Types on a Single Chart .........................................197 Moving Charts from One Worksheet to Another .........................................200 Making Columns or Bars Float .........................................................................200 Using a Rogue XY Series for Arbitrary Gridlines..............................................202 Showing Several Charts on One Chart by Using a Rogue XY Series ....................207 Creating Bullet Charts in Excel 2013 ...........................................................212 Creating a Thermometer Chart ..........................................................217 Creating a Benchmark Chart ...........................................................219 Creating a Delta Chart ...........................................................................220 Next Steps.............................................................................................221   8 Creating Pivot Charts and Power View Dashboards ..................223 Creating a PivotChart Using Recommended Charts ..........................................223     Changing the Fields in the Pivot Chart .............................................................225     Sorting the Pivot Chart ..........................................................226     Grouping Daily Dates in the Pivot Chart .......................................................228     Filtering Pivot Charts Using the Filter Fly-out Menu ............................230     Filtering Pivot Charts Using Slicers .................................................230     Connecting Multiple Pivot Charts to One Slicer ...................................231 Using PowerPivot and Power View .......................................................232     Enabling PowerPivot and Power View...........................................................233     Loading Your Excel Data to PowerPivot ..........................................................233     Adding a Date Lookup Table .........................................................234     Format Your Data in PowerPivot .............................................................235     VLOOKUPs? Replacing VLOOKUPs with Relationships ...............................236     Creating a Power View Worksheet ...................................................237     Every New Dashboard Element Starts as a Table ......................................238     Converting the Table to a Chart ...............................................238     Creating a New Element by Dragging ..........................................................240     Every Chart Point Is a Slicer for Every Other Element ..........................................240     Adding a Real Slicer ................................................................................241     The Filter Pane Can Be Confusing .................................................................242     Use Tile Boxes to Filter One or a Group of Charts ..........................................................243     Replicating Charts Using Multiples .............................................................244     Animating a Scatter Chart Over Time ....................................................................245     Some Closing Tips on Power View .........................................................................246 Next Steps.................................................................................247   9 Using Sparklines, Data Visualizations, and Other Nonchart Methods .............249 Fitting a Chart into the Size of a Cell with Sparklines...............................................250     Creating a Group of Sparklines ....................................................................251     Built-in Choices for Customizing Sparklines ..........................................................253     Controlling Axis Values for Sparklines ............................................................254     Setting Up Win/Loss Sparklines .....................................................................256     Showing Detail by Enlarging the Sparkline.......................................................256     Labeling a Sparkline ........................................................................257 Using Data Bars to Create In-Cell Bar Charts ...................................................259     Creating Data Bars ..............................................................................260     Customizing Data Bars ....................................................................................261     Showing Data Bars for a Subset of Cells .......................................................262 Using Color Scales to Highlight Extremes..................................................................263     Customizing Color Scales .............................................................................264 Using Icon Sets to Segregate Data ............................................................................265     Setting Up an Icon Set ............................................................................265     Moving Numbers Closer to Icons ........................................................................266     Showing an Icon for Only the Best Cells ....................................................................268     Creating a 10-Icon Set Using a Formula ....................................................................269 Creating a Chart Using Conditional Formatting in Worksheet Cells ...........................271 Creating a Chart Using the REPT Function ................................................273 Next Steps........................................................................................274   10 Presenting Excel Data on a Map ............................275 Plotting Data Geographically .......................................................275 Importing Data to MapPoint ......................................................................275 Creating a Map in Power View ..........................................................................279 Creating a Map in GeoFlow ..............................................................284 Next Steps..............................................................................................286   11 Using SmartArt Diagrams and Shapes ...............................287 Using SmartArt .................................................................................288     Elements Common Across Most SmartArt ............................................289     A Tour of the SmartArt Categories ..........................................................289     Inserting SmartArt .....................................................................................291     Micromanaging SmartArt Elements ........................................................294     Changing Text Formatting in One Element ..............................................294     Controlling SmartArt Shapes from the Text Pane ..........................................296     Adding Images to SmartArt .............................................................................298     Special Considerations for Organization Charts ..............................................299     Using Limited SmartArt ...............................................................................301 Choosing the Right Layout for Your Message .................................................302 Exploring Business Charts That Use SmartArt Graphics .......................................303     Illustrating a Pro/Con Decision by Using a Balance Chart ................................................304     Illustrating Growth by Using an Upward Arrow ...................................................304     Showing an Iterative Process by Using a Basic Cycle Layout ............................................305     Showing a Company’s Relationship to External Entities by Using a Diverging Radial Diagram .........305     Illustrating Departments Within a Company by Using a Table List Diagram .....................306     Adjusting Venn Diagrams to Show Relationships .............................................306     Understanding Labeled Hierarchy Charts ...................................................307     Using Other SmartArt Layouts ...................................................................308 Using Shapes to Display Cell Contents ...............................................................309     Working with Shapes ......................................................................................311     Using the Freeform Shape to Create a Custom Shape .....................................311 Using WordArt for Interesting Titles and Headlines .............................................312 Next Steps............................................................................................315   12 Exporting Charts for Use Outside of Excel .....................................317 Presenting Excel Charts in PowerPoint or Word .........................................317     Copying a Document from Excel and Pasting to PowerPoint Sets Up an As-Needed Link........319     Copying and Pasting While Keeping Original Formatting .....................................................323     Pasting as Link to Capture Future Excel Formatting Changes ...........................................324     Embedding the Chart and Workbook in PowerPoint ........................................................325     Copying a Chart as a Picture .......................................................................................325     Creating a Chart in PowerPoint with Data Pasted from Excel ....................................327 Presenting Charts on the Web ...............................................................................328 Exporting Charts to Graphics Using VBA .....................................................................331 Converting to XPS or PDF .............................................................................................332 Next Steps....................................................................................................................332   13 Using Excel VBA to Create Charts .......................................333 Introducing VBA ......................................................................................................333     Enabling VBA in Your Copy of Excel .................................................333     Enabling the Developer Tab .....................................................................334     Visual Basic Tools ...........................................................................................335     The Macro Recorder .....................................................................................336     Understanding Object-Oriented Code .............................................................336 Learning Tricks of the VBA Trade ...........................................................................337     Writing Code to Handle a Data Range of Any Size ...........................................337     Using Super-Variables: Object Variables ..............................................339     Using With and End With When Referring to an Object ....................................340     Continuing a Line of Code ......................................................................................340     Adding Comments to Code .....................................................................................341 Understanding Backward Compatibility ...........................................................................341 Referencing Charts and Chart Objects in VBA Code ....................................................342 Understanding the Global Settings .............................................................................342     Specifying a Built-in Chart Type ..........................................................................342     Specifying Location and Size of the Chart .....................................................345     Referring to a Specific Chart ...........................................................................345 Creating a Chart in Various Excel Versions ........................................................346     Using the .AddChart2 Method in Excel 2013 ................................................346     Creating Charts in Excel 2007–2013 ...................................................348     Creating Charts in Excel 2003–2013 ............................349 Customizing a Chart .............................................350     Specifying a Chart Title ............................................350     Quickly Formatting a Chart Using New Excel 2013 Features ........................351 Using SetElement to Emulate Changes from the Plus Icon ..................................358     Using the Format Method to Micromanage Formatting Options ..............................363 Formatting a Data Series .........................................................367     Controlling Gap Width and Series Separation in Column and Bar Charts ...............368     Spinning and Exploding Round Charts.......................................................369     Controlling the Bar of Pie and Pie of Pie Charts ...................................................371     Setting the Bubble Size ................................................................................375     Controlling Radar and Surface Charts ...........................................................377 Creating Advanced Charts ...............................................................................381     Creating True Open-High-Low-Close Stock Charts ............................................382     Creating Bins for a Frequency Chart ..............................................................383     Creating a Stacked Area Chart ..............................................................................386 Exporting a Chart as a Graphic ..........................................................................389 Creating Pivot Charts ............................................................................................390 Creating Data Bars with VBA .....................................................................392 Creating Sparklines with VBA ..............................................................................395 Next Steps........................................................................................................399   14 Knowing When Someone Is Lying to You with a Chart ..............401 Lying with Perspective ......................................................................401 Lying with Shrinking Charts ..........................................................................402 Lying with Scale .....................................................................................................403 Lying Because Excel Will Not Cooperate ............................................................405 Avoiding Stacked Surface Charts ..............................................................................406 Asserting a Trend from Two Data Points ...........................................................407 Deliberately Using Charts to Lie ...........................................................................408 Charting Something Else When Numbers Are Too Bad ...........................................409 Stretching Pictographs .............................................................................................409 Next Steps..........................................................................................................410   Appendix Charting References ...............411   Index .....................417