/
Excel Tutorial  4:  Enhancing a Workbook Excel Tutorial  4:  Enhancing a Workbook

Excel Tutorial 4: Enhancing a Workbook - PowerPoint Presentation

test
test . @test
Follow
383 views
Uploaded On 2018-10-22

Excel Tutorial 4: Enhancing a Workbook - PPT Presentation

with Charts and Graphs Objectives Create an embedded chart Create and format a pie chart Work with chart titles and legends Create and format a column chart New Perspectives on Microsoft Excel 2010 ID: 693388

excel chart microsoft 2010 chart excel 2010 microsoft perspectives data charts creating series format values axis pie sparklines column

Share:

Link:

Embed:

Download Presentation from below link

Download Presentation The PPT/PDF document "Excel Tutorial 4: Enhancing a Workbook" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.


Presentation Transcript

Slide1

Excel Tutorial

4:

Enhancing a Workbook

with Charts and GraphsSlide2

Objectives

Create an embedded chart

Create and format a pie chartWork with chart titles and legendsCreate and format a column chart

New Perspectives on Microsoft Excel 2010

2Slide3

Objectives

Create and format a line chart

Modify a chart data sourceCreate and format a combined chart

Create a 3-D chartCreate and format sparklines and data barsCreate a chart sheet

New Perspectives on Microsoft Excel 2010

3Slide4

Visual Overview

New Perspectives on Microsoft Excel 2010

4Slide5

Chart Elements

New Perspectives on Microsoft Excel 2010

5Slide6

Creating an Excel Chart

Charts show trends or relationships in data that are difficult to show with numbers

Select a range to use as chart’s data source

New Perspectives on Microsoft Excel 2010

6Slide7

Creating an Excel Chart

Select chart type that best represents the data

Use one of 73 built-in charts organized into 11 categories, or…

Create custom chart types based on built-ins

New Perspectives on Microsoft Excel 2010

7Slide8

Creating an Excel Chart

Three Chart Tools tabs appear on the Ribbon: Design, Layout, and Format

New Perspectives on Microsoft Excel 2010

8Slide9

Moving a Chart to a Different Worksheet

Move Chart dialog box provides options for moving charts

New Perspectives on Microsoft Excel 2010

9Slide10

Moving and Resizing Charts

Selecting the chart displays a

selection box (used to move or resize the object)

To move the chart, drag selection box to new location in worksheetTo resize the chart, drag a sizing handle

New Perspectives on Microsoft Excel 2010

10Slide11

Designing a Pie Chart

Charts include individual elements that can be formatted (chart area, chart title, plot area, data markers, legend)

Make changes using a built-in style that formats the entire chart or by selecting and formatting an individual element

Use a chart style to apply several formats to the chart at one time

New Perspectives on Microsoft Excel 2010

11Slide12

Designing a Pie Chart

Choose location of the legend, and format it using tools on Chart Tools Layout tab

New Perspectives on Microsoft Excel 2010

12Slide13

Formatting Data Labels on a Pie Chart

New Perspectives on Microsoft Excel 2010

13Slide14

Changing Pie Slice Colors

Use distinct colors to avoid confusion, especially for adjacent slides

Format each slice rather than entire data series (each slice represents a different value in the series)

New Perspectives on Microsoft Excel 2010

14Slide15

Designing a Pie Chart

Exploded pie charts

Move one slice away from the othersUseful for emphasizing one category above all of the others

New Perspectives on Microsoft Excel 2010

15Slide16

Creating a Column Chart

Column chart

Displays values in different categories as columnsHeight of each column is based on its value

Bar chartColumn chart turned on its sideLength of each bar is based on its value

New Perspectives on Microsoft Excel 2010

16Slide17

Column/Bar Charts vs Pie Charts

Column/bar charts are superior to pie charts

For large number of categories or categories close in value

Easier to compare height or length than areaCan be applied to wider range of dataCan include several data series (pie charts usually show only one data series)

New Perspectives on Microsoft Excel 2010

17Slide18

Inserting a Column Chart

Select data source

Select type of chart to createMove and resize the chart

Change chart’s design, layout, and format by:Selecting one of the chart styles, orFormatting individual chart elements

New Perspectives on Microsoft Excel 2010

18Slide19

Inserting a Column Chart

New Perspectives on Microsoft Excel 2010

19Slide20

Editing the Axis Scale and Text

Range of values

(scale) of an axis is based on values in data sourceVertical (value) axis: range of series values

Horizontal (category) axis: category valuesPrimary and secondary axes

can use different scales and labels

Add descriptive axis titles if axis labels are not self-explanatory (default is no titles)

New Perspectives on Microsoft Excel 2010

20Slide21

Editing the Axis Scale and Text

New Perspectives on Microsoft Excel 2010

21Slide22

Editing the Axis Scale and Text

New Perspectives on Microsoft Excel 2010

22Slide23

Formatting the Chart Columns

Columns usually have a common format – distinguished by height, not color

New Perspectives on Microsoft Excel 2010

23Slide24

Communicating Effectively with Charts

Keep it simple

Focus on the messageLimit the number of data seriesUse gridlines in moderation

Choose colors carefullyLimit chart to a few text styles

New Perspectives on Microsoft Excel 2010

24Slide25

Visual Overview

New Perspectives on Microsoft Excel 2010

25Slide26

Charts, Sparklines, and Data Bars

New Perspectives on Microsoft Excel 2010

26Slide27

Creating a Line Chart

Use when data consists of values drawn from categories that follow a sequential order at evenly spaced intervals

Displays data values using a connected line rather than columns or bars

New Perspectives on Microsoft Excel 2010

27Slide28

Formatting Date Labels

Set minimum and maximum dates to use in the scale’s range

Set major and minor units as days, months, or years to use for the scale’s interval

New Perspectives on Microsoft Excel 2010

28Slide29

Formatting Date Labels

Custom date formats use combinations of “m”, “d”, and “y” for months, days, and years

Number of letters controls how Excel displays the date

New Perspectives on Microsoft Excel 2010

29Slide30

Setting Label Units

Simplify a chart’s appearance by displaying units of measure appropriate to data values

Useful when space is at a premium

Example: Display the value 20 to represent 20,000 or 20,000,000Apply custom formats to numbers, including adding text

New Perspectives on Microsoft Excel 2010

30Slide31

Overlaying a Chart Legend

Makes more space for the plot area

An overlaid chart element floats in the chart area and is not fixed to a particular position; can be dragged to a new location

New Perspectives on Microsoft Excel 2010

31Slide32

Creating a Line Chart

Adding gridlines

Adding an axis title

New Perspectives on Microsoft Excel 2010

32Slide33

Editing and Revising Chart Data

Modify the data range that the chart is based on (do not directly modify data in the chart)

If values/labels in data source are changed, chart automatically updates to show new content

New Perspectives on Microsoft Excel 2010

33Slide34

Adding a Data Series to an Existing Chart

New data series appears in the chart with a different set of data markers

New Perspectives on Microsoft Excel 2010

34Slide35

Adding a Data Series to an Existing Chart

New Perspectives on Microsoft Excel 2010

35Slide36

Adding a Data Series to an Existing Chart

Understanding the SERIES

function

Can be edited within the formula bar to make

quick changes to the chart, but the function is tied to an existing chart; it cannot be used

within a worksheet cell or referenced from another Excel formula

New Perspectives on Microsoft Excel 2010

36Slide37

Creating a Combination Chart

Select a data series in an existing chart

Apply a new chart type to that series, leaving the other data series in its original format

New Perspectives on Microsoft Excel 2010

37Slide38

Choosing the Right Chart

New Perspectives on Microsoft Excel 2010

38

Chart

When to Use

Pie charts

Small number of categories; easy to distinguish relative sizes of slices

Column or bar chart

Several categories

Line charts

Categories follow a sequential order

XY scatter charts

To plot two numeric values against one another

Custom chart

Available charts don’t meet your needsSlide39

Creating a 3-D Chart

Provide illusion of depth and distance

Adds three spatial dimensions:The

x-axis (length)The y-axis (height)

The

z-axis

(depth)

Chart’s

perspective

controls how fast the chart appears to recede

New Perspectives on Microsoft Excel 2010

39Slide40

Creating a 3-D Chart

New Perspectives on Microsoft Excel 2010

40Slide41

Adding Sparklines and Data Bars

Both convey graphical information about worksheet data without occupying a lot of space

New Perspectives on Microsoft Excel 2010

41Slide42

Creating Sparklines

A mini chart displayed within a worksheet cell

Compact in size; doesn’t include chart elementsGoal is to convey maximum amount of graphical information in a very small space

Can be grouped or ungroupedGrouped sparklines share a common formatUngrouped sparklines can be formatted individually

New Perspectives on Microsoft Excel 2010

42Slide43

Types of Sparklines

Line sparkline

Highlights trendsColumn sparklineFor column charts

Win/Loss sparklineHighlights positive and negative values

New Perspectives on Microsoft Excel 2010

43Slide44

Creating Sparklines

Select a data range containing data to graph

Select a location range where you want sparklines to appear

New Perspectives on Microsoft Excel 2010

44Slide45

Creating Sparklines

New Perspectives on Microsoft Excel 2010

45Slide46

Adding and Formatting Sparkline Markers

Can specify only line color and marker color

Can create line markers for highest value, lowest value, all negative values, first value, and last value

Can create markers for all data points regardless of value or position in data sourceCan add an axis to a sparkline – horizontal line that separates positive and negative values

New Perspectives on Microsoft Excel 2010

46Slide47

Adding and Formatting Sparkline Markers

New Perspectives on Microsoft Excel 2010

47Slide48

Creating Data Bars

Conditional format that adds a horizontal bar to background of a cell containing a numeric value

Length based on value of each cell in selected range

Dynamic

Lengths of data bars automatically update if cell’s value changes

New Perspectives on Microsoft Excel 2010

48Slide49

Modifying a Data Bar Rule

Alter rules of the conditional format

New Perspectives on Microsoft Excel 2010

49Slide50

Modifying a Data Bar Rule

New Perspectives on Microsoft Excel 2010

50Slide51

Creating a Chart Sheet

For detailed charts that need more space to be seen clearly or to show a chart without any worksheet text or data

Do not contain worksheet cells for calculating numeric values

New Perspectives on Microsoft Excel 2010

51Slide52

Creating a Chart Sheet

New Perspectives on Microsoft Excel 2010

52