Excel 2013 Charts and Graphs

( 1 )

Overview

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 ...

See more details below
Paperback
$26.26
BN.com price
(Save 34%)$39.99 List Price

Pick Up In Store

Reserve and pick up in 60 minutes at your local store

Other sellers (Paperback)
  • All (10) from $22.02   
  • New (9) from $22.02   
  • Used (1) from $26.25   
Excel 2013 Charts and Graphs

Available on NOOK devices and apps  
  • NOOK Devices
  • Samsung Galaxy Tab 4 NOOK 7.0
  • Samsung Galaxy Tab 4 NOOK 10.1
  • NOOK HD Tablet
  • NOOK HD+ Tablet
  • NOOK eReaders
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac

Want a NOOK? Explore Now

NOOK Book (eBook)
$17.99
BN.com price
(Save 43%)$31.99 List Price

Overview

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

Read More Show Less

Product Details

  • ISBN-13: 9780789748621
  • Publisher: Que
  • Publication date: 2/28/2013
  • Series: MrExcel Library Series
  • Edition number: 1
  • Pages: 440
  • Sales rank: 375,481
  • Product dimensions: 6.90 (w) x 9.00 (h) x 1.20 (d)

Meet the Author

Bill Jelen, Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. Bill was a regular guest on Call for Help with Leo Laporte and has produced more than 1,500 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 39 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine. His Excel tips appear regularly in CFO Excel Pro Newsletter and CFO Magazine . Before founding MrExcel.com, Bill Jelen spent 12 years in the trenches--working as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives near Akron, Ohio, with his wife, Mary Ellen.

Read More Show Less

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

Read More Show Less

Customer Reviews

Average Rating 2
( 1 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(1)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Anonymous

    Posted November 17, 2014

    Garretts list of things NOT to do when starting to rp at a new place

    1: do NOT ask to join. Just walk in <p> 2: get a good reputation. Dont make people dislike you the first day <p> 3: dont get in a new "relationship" right away. <p> 4: make good friends quickly. <p> 5: rp there regulary

    Was this review helpful? Yes  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)