Charts and graphs for Microsoft Office Excel 2007

Format:

Book

Author/Creator:

Jelen, Bill, Author.

Series:

Business solutions Charts and graphs for Microsoft Office Excel 2007

Language:

English

Subjects (All):

Business--Computer programs.

Business.

Electronic spreadsheets--Computer programs.

Electronic spreadsheets.

Charts, diagrams, etc.

Microsoft Excel (Computer file).

Physical Description:

1 online resource (480 pages)

Edition:

1st ed.

Other Title:

Charts and Graphs for Microsoft Office Excel 2007

Place of Publication:

[Place of publication not identified] Que 2007

Language Note:

English

Summary:

It is easy to create a bad looking chart in Excel. This book teaches you how to unlock the beautiful formatting options available to make incredible looking charts. The first section will talk about how to decide which chart type to use. Subsequent chapters will walk through each chart type, how to create them, how to utilize them, and special options available for each chart. The book discusses themes, colors, creating metallic charts, shadows, transparency, etc. The book also handles anything graphical in Excel. It will show the new In-Cell Data Bar charts available in Excel 2007. A section will talk about creating business flowcharts with IGX Graphics and how to display product pictures in Excel, and a section on VBA will cover creating 100's of charts using the macro language. Implementing 1-Click Charting Incorporating Drag & Drop and Dynamic Charts Creating Amazing Effects Using Charting Templates and Macros Mastering Glow, Shadow, Sparklines, Dashboards, and More Eliminating Chart Junk Structuring Spreadsheets with Business Diagrams, SmartArt Graphics, and Pivot Charts Develop your Charting expertise instantly with proven techniques After 15 years with no updates to the Excel charting engine, Microsoft has provided a complete rewrite of the chart rendering engine in Excel 2007. However, no amount of soft glow or glass bevel effects will help you communicate your point if you use the wrong chart type. This book helps you choose the right charting type and shows you how to make it look great. This book shows you how to coax Excel to create many charts you might not have believed were possible. You’ll learn techniques that allow you to ditch the Microsoft defaults and actually create charts that communicate your point. You’ll learn why the Excel stock charts are so

restrictive and how you can easily turn any line chart into a stock chart–without any limitations. You’ll also learn how to add invisible series to make columns float in midair. Learn how to create charts right in Excel cells using the new Excel 2007 data bars–or even the decades-old REPT function! In no time, this book will have you creating charts that wow your audience and effectively communicate your message. Master effective visual display of data Choose the right chart type to convey your message Learn time-saving workarounds Create charts that most people think you can’t create with Excel Understand what a Radar chart is and when you might use it Summarize a million rows of data in a single pivot table chart Present data graphically without charts Employ SmartArt graphics to show process or relationship charts Utilize VBA to create charts Put your data on a map Export your charts to the web or PowerPoint Detect chart lies.

Contents:

Cover

Contents

Introduction

Choosing the Right Chart Type

Using Excel as Your Charting Canvas

This Book's Objectives

A Note About Bugs

Special Elements in This Book

Next Steps

1 Introducing Charts in Excel 2007

What's New in Excel 2007 Charts

New Charting Tools and Menus

Using the Insert Tab to Select a Chart Type

Using the Expand Icon to Access a Gallery of All Chart Types

Understanding the Chart Thumbnail Icons

Using Gallery Controls

Creating a Chart

Selecting Contiguous Data to Chart

Selecting Noncontiguous Data to Chart

Creating a Chart by Using the Insert Ribbon Icons

Creating a Chart with One Keystroke

Working with Charts

Moving a Chart Within the Current Worksheet

Locating a Chart at the Top of Your Dataset

Reversing the Series and Categories of a Chart

Changing the Data Sequence by Using Select Data

Leaving the Top-Left Cell Blank

Moving a Chart to a Different Sheet

Customizing a Chart by Using the Design Ribbon

Choosing a Chart Layout

Choosing a Color Scheme

Modifying a Color Scheme by Changing the Theme

Creating Your Own Theme

Choosing Effects for a Custom Theme from an Existing Theme

Understanding RGB Color Codes

Converting from Hexadecimal to RGB

Finding Complementary Colors

Specifying a Theme's Colors

Specifying a Theme's Fonts

Saving a Custom Theme

Using a Custom Theme on a New Document

Sharing a Theme with Others

2 Customizing Charts

Accessing Element Formatting Tools

Identifying Chart Elements

Chart Labels and Axis

Special Elements in a 3-D Chart

Analysis Elements

Formatting Chart Elements

Formatting a Chart Title

Formatting an Axis Title

Formatting a Legend

Adding Data Labels to a Chart

Adding a Data Table to a Chart

Formatting Axes.

Using a Date-Based Axis to Represent Time

Displaying and Formatting Gridlines

Formatting the Plot Area

Creating a Custom Gradient

Formatting the Chart Walls and Floor of a 3-D Chart

Controlling 3-D Rotation in a 3-D Chart

Forecasting with Trendlines

Adding Drop Lines to a Line or Area Chart

Adding Up/Down Bars to a Line Chart

Showing Acceptable Tolerances by Using Error Bars

Formatting a Series

Formatting a Single Data Point

Using the Format Ribbon

Converting Text to WordArt

Using the Shape Styles Gallery

Using the Shape Fill and Shape Effects

Using Preset Shape Effects

Replacing Data Markers with Clip Art or Shapes

Using Clip Art as a Data Marker

Using a Shape in Place of a Data Marker

Creating a Chart Template

3 Creating Charts That Show Trends

Choosing a Chart Type

Understanding a Date-Based Axis Versus a Category-Based Axis

Accurately Representing Data Using a Time-Based Axis

Converting Text Dates to Dates

Comparing Date Systems

Dates Not Recognized as Dates: Numeric Years

Dates Not Recognized as Dates: Dates Before 1900

Using a Workaround to Display a Time-Scale Axis

Converting Dates to Text to Add a Decorative Chart Element

Kyle Fletcher: Using a Decorative Element in a Chart

Using a Chart to Communicate Effectively

Using a Long, Meaningful Title to Explain Your Point

Highlighting One Column

Replacing Columns with Arrows

Highlighting a Section of Chart by Adding a Second Series

Changing Line Type Midstream

Adding an Automatic Trendline to a Chart

Showing a Trend of Monthly Sales and Year-to-Date Sales

Understanding the Shortcomings of Stacked Column Charts

Using a Stacked Column Chart to Compare Current Sales to Prior-Year Sales

Shortcomings of Showing Many Trends on a Single Chart.

Using a Scatter Plot to Show a Trend

4 Creating Charts That Show Differences

Comparing Entities

Using Bar Charts to Illustrate Item Comparisons

Adding a Second Series to Show a Time Comparison

Subdividing a Bar to Emphasize One Component

Showing Component Comparisons

Using Pie Charts

Switching to a 100% Stacked Column Chart

Using a Doughnut Chart to Compare Two Pies

Dealing with Data Representation Problems in a Pie Chart

Creating a Pie of Pie Chart

Using a Waterfall Chart to Tell the Story of Component Decomposition

Creating a Waterfall Chart

5 Creating Charts That Show Relationships

Comparing Two Variables on a Chart

Using XY Scatter Charts to Plot Pairs of Data Points

Adding a Trendline to a Scatter Chart

Adding Labels to a Scatter Chart

Joining the Points in a Scatter Chart with Lines

Adding a Second Series to an XY Chart

Drawing with a Scatter Chart

Using Charts to Show Relationships

Testing Correlation Using a Scatter Chart

Using Paired Bars to Show Relationships

Comparing the Relationship Between Discount and Sales

Kathy Villella: Comparing Three Variables with a Paired Bar Chart

Using Paired Matching Charts

MAD Magazine: Creating a Paired Comparison Chart

Adding a Third Dimension with a Bubble Chart

Using a Frequency Distribution to Categorize Thousands of Points

Using Radar Charts to Create Performance Reviews

Manoj Sharma: Radar Charts

A Chart from Gene Zelazny

Gene Zelazny: Zelazny Chart

Using Surface Charts to Show Contrast

Using the Depth Axis

Controlling a Surface Chart Through 3-D Rotation

6 Creating Stock Analysis Charts

Overview of Stock Charts

Line Charts

OHLC Charts

Candlestick Charts

Obtaining Stock Data to Chart

Rearranging Columns in the Downloaded Data.

Dealing with Splits Using the Adjusted Close Column

Creating a Line Chart to Show Closing Prices

Adding Volume as a Column Chart to the Line Chart

Creating OHLC Charts

Producing a High-Low-Close Chart

Creating an OHLC Chart

Adding Volume to a High-Low-Close Chart

Creating Candlestick Charts

Changing Colors in a Candlestick Chart

Adding Volume to a Candlestick Chart

Manually Creating a Candlestick Chart with Volume

Creating a Candlestick Stock Chart Showing Volume and a Competitor

Creating a Live Chart by Using a Web Connection

Making Charts Small for Use in Dashboards

7 Advanced Chart Techniques

A Tool Chest of Advanced Charting Techniques

Mixing Two Chart Types on a Single Chart

Moving Charts from One Worksheet to Another

Using Shapes to Annotate a Chart

Making Columns or Bars Float

Using a Rogue XY Series to Label the Vertical Axis

Converting a Series to Gridlines

Showing Several Charts on One Chart by Using a Rogue XY Series

Using Multiple XY Series to Create a Trellis Chart

Creating Dynamic Charts

Using the OFFSET Function to Specify a Range

Using VLOOKUP or MATCH to Find a Value in a Table

Combining INDEX and MATCH

Using Validation Drop-Downs to Create a Dynamic Chart

Using Dynamic Ranges in a Chart

Creating a Scrolling Chart

Modifying the Scrollbar Example to Show the Last 12 Months

Creating Advanced Charts

Thermometer Chart

Benchmark Chart

Delta Chart

Amazing Things People Do with Excel Charts

8 Creating and Using Pivot Charts

Creating Your First Pivot Chart

What's New in Excel 2007 Pivot Tables

Deciding Which Comes First: The Table or the Chart

Rules for Preparing Underlying Pivot Data

Changing the Chart Type and Formatting the Chart.

Adding Additional Series to a Pivot Chart

Returning to a Pivot Table for Advanced Operations

Filtering a Pivot Table

Filtering Using a Report Filter Field

Using the Excel 2007 Filters for Axis and Legend Fields

Creating a Chart for Every Customer

Stratifying Invoice Amounts

9 Presenting Data Visually Without Charts

Creating Charts in the Worksheet Cells

Using Data Bars to Create In-Cell Bar Charts

Customizing Data Bars

Controlling the Size of the Smallest/Largest Bar

Showing Data Bars for a Subset of Cells

Using Color Scales to Highlight Extremes

Converting to Monochromatic Data Bars

Troubleshooting Color Scales

Using Icon Sets to Segregate Data

Setting Up an Icon Set

Moving Numbers Closer to Icons

Reversing the Sequence of Icons

Creating a Chart Using Conditional Formatting in Worksheet Cells

Creating a Chart Using the REPT Function

Creating a Chart Using Scrollbar Controls

Creating Stem-and-Leaf Plots

Creating a Stem-and-Leaf Plot with X's as the Leaves

Creating a Stem-and-Leaf Plot with Digits as the Leaves Using a Long Formula

Creating a Stem-and-Leaf Plot with Digits as the Leaves Using Sorting and Formulas

10 Presenting Your Excel Data on a Map Using Microsoft MapPoint

Plotting Data Geographically

Building a Map in Excel

Using a Chart on a Map

Using Other Map Styles to Illustrate Data

Mapping Your Customers

11 Using SmartArt Graphics and Shapes

Understanding SmartArt Graphics and Shapes

Using SmartArt

Elements Common Across Most SmartArt

A Tour of the SmartArt Categories

Inserting SmartArt

Micromanaging SmartArt Elements

Controlling SmartArt Shapes from the Text Pane

Adding Images to SmartArt

Special Considerations for Organization Charts

Using Limited SmartArt.

Choosing the Right Layout for Your Message.

Notes:

Bibliographic Level Mode of Issuance: Monograph

Description based on publisher supplied metadata and other sources.

ISBN:

1-282-64922-1

9786612649226

0-7686-7484-0

OCLC:

1027172023