Excel2010

7.2 Chart Tools: Design Tab

After you prepare your initial chart and select it, 3 new tabs will appear in the ribbon. These are called Contextual tabs. The first of these tabs is the Chart Design Tools tab which contains the tools for: Chart Type, Chart Data, Chart Layouts, Chart Styles, and the Location. The tools, different than the previous Excel versions, are so efficient and rich that they have many ready to use formats prepared by professionals. You just need to be a little patient to decide for a good color and design.

Figure 7.5: Chart Tools → Design Tab buttons

7.2.1 Chart Layouts

The Chart Layouts group contains possible positioning layout options for: Chart Title, X and Y category axes title positioning, Legend positioning, etc. options.

Legend is a box that identifies the patterns or colors that are assigned to the data series or categories in a chart.

Figure 7.6: Chart Layouts

7.2.2 Chart Types

If the selected chart type didn’t fit your needs, you don’t have to start over. Just, click the Change Chart Type button in the Chart Design Tools tab. It will open the dialog box containing many options. You can choose the new chart type from this dialog box.

Figure 7.7: Changing Chart Type

Charts

121

Figure 7.9: Pie Chart

Figure 7.10: Scatter Charts

7.2.2.1 Bar Charts

You may use different types of charts for different purposes. For example, for annual sales, because it’s easy to see the growth/shrink, you can select Column, Cylinder or Line.

Figure 7.8: Column and Line Charts

7.2.2.2 Pie Charts

On the other hand, for market shares or for the results of an election, you could prefer a Pie chart, etc. A pie chart consists of a circle split into segments. The segments represent individual parts which make up the total.

7.2.2.3 Scatter Charts

Scatter charts are used when you have two or more variables and you want to search the relation between them. (See Chapter 5.5.4.8 Correlation) In the left figure, as you see, there is a strong negative relation between the number of absences and final grade.

7.2.2.4 Other Charts

A surface chart is useful when you want to find optimum combinations between two sets of data. As in a topographic map, colors and patterns indicate areas that are in the same range of values. Like a pie chart, a doughnut chart shows the relationship of parts to a whole, but it can contain more than one data series.

7.2.3 Chart Styles

Chart styles offer many professional color and design sets. All you need to do is to select the one that suits your needs. You are not limited with these predefined style sets. You can also select a different color theme and have fun with hundreds of different style sets. Moreover, you are able make your own changes as you wish.

122	Microsoft Excel

7.3 Chart Tools: Layout Tab

Figure 7.12: Chart Layout Tools Tab

Most of the tools in the Layout were -in a wayhidden in the previous Excel versions. We didn’t have them right away on the toolbars or in the menus. You needed to have a little bit of experience to reach them. But now, they are in front of us and waiting for more tuning.

For example, for the previous example, we selected Layout6 from the Chart Design Chart Layouts. By default, it has Y-axis title but it doesn’t have X-axis title. You can change these by clicking Axis Titles

button from the Chart Layout Tools tab. To complete

Figure 7.13: Axis title positioning

our example, I added X-axis title as in Figure 7.13. After adding the title -as you move or process other Excel objectsyou can move or format this title, too. Or, if you want you can show more options and make further changes.

7.3.1 Layout In Brief

1.Chart Title: Descriptive text that briefly explains the purpose of the chart. It’s usually located at the top of the chart.

2.Axes: Charts typically have two axes (axis: A line bordering the chart plot area used as a frame of reference for measurement. The Y-axis is usually the vertical axis and contains data values. The X-axis is usually the horizontal axis and contains categories or items) used to categorize and measure data: a category (X) axis and a value (Y) axis. 3-D charts have a third (Z) axis. Pie and doughnut charts do not have any axis.

3.Legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart. You can choose the legend location by selecting one of the Bottom, Corner, Top, Right, or Left buttons.

4.Gridlines: Lines you can add to a chart that make it easier to view and evaluate data. Gridlines extend from the tick marks on an axis across the plot area. Choose proper options, either you want to display the major and/or minor gridlines for the X and Y axis. Usually, minor gridlines are not displayed.

Charts

123

5. Data Labels: A label that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell. Choose to display and/or format data labels.

You can place Legend keys with the assigned format and color, next to the labels in the chart.

Series name displays data on the selected axis as the default category (x) axis, even if data is date formatted.

Category name displays the category name assigned to all data points. For scatter and bubble charts, the X value is displayed.

Value displays the value represented for all data points.

Percentage displays the percentage of the whole for all data points in pie and doughnut charts.

Separator allows you to choose how the contents of the data label are separated. Choose from the options available or type in a custom separator.

6. Data Table: A grid that contains the numeric data used to create the chart. The data table usually is attached to the category axis of the chart and replaces the tick-mark labels on the category axis. Choose to display the chart data in a data table.

The Show data table option displays the value for each data series in a grid below the chart. This option is not available for pie, XY (scatter), doughnut, bubble, radar, or surface charts.

Click the Show legend keys option to display legend keys, with the assigned format and color for each plotted series, next to series label in the data table.

7.4 Chart Tools: Format Tab

Chart Tools Format tab contain the standard shape formatting tools that has been explained in the MS. Word book of this series. Briefly, we have Shape Style tools, WordArt tools, and shape arrangement tools here.

Figure 7.14: Chart Tools: Format tab

124	Microsoft Excel

Example 7.2:

Your math teacher heard about Charts and he wants to prepare a chart for his lesson. He has two exams and an average column. Now, help him prepare this chart.

Students	Exam1	Exam2	Average
Kevin Schmidt	100	60	85
Todd Rafferty	91	100	97
David Shadovitz	80	40	64
Pete Thomas	86	70	81
Pablo Varando	55	45	52
Average	82	63	76

Solution:

Select the range and click Insert Charts Column Clustered Cylinder.

Figure 7.15: Inserting Chart

Then, insert the Chart Title using: Chart Tools Layout Labels Chart Title

Above Chart. Also change Primary Horizontal Axis style using: Chart Tools

Layout Axes Primary Horizontal Axis More Options Alignment Custom Angle=0.

Figure 7.16: Adding Title

Charts

125

To open the Format Chart Area dialog box

Select an object from the chart area

Click the “Format Selection” button in the “Current Selection” group.

You can use your imagination and productivity to prepare nice looking charts using fill effects. Just show the Format Chart dialog box (Figure 7.17c) from Chart Tools Layout CurrentSelection Chart Elements combo box.When you click on any chart object, the Format Chart dialog box allows you to edit the properties of that object.

Figure 7.17: Changing the format using the Current Selection tools in Chart Layout Tools tab.

This dialog box has many different options for changing the appearance of the chart. You can place one of your pictures as a background; use gradient fill effects; etc. The Shadow and Round corners options will definitely give a nice look to your charts. Finally, using Chart Tools Format WordArt Styles, you can modify the title text you used in your chart.

Now, you have finished modifying your chart. Here is the final look of our chart. But, I believe you can prepare better charts with your good imagination.

Figure 7.18: 10A Math Semester 1 Results Chart

126	Microsoft Excel

Questions

1.If you want to show the percentages of a whole in a chart, what type of chart would you prefer?

a. Line chart

b. Bar chart

6.An doughnut chart is useful for .... .

a.Showing optimum combinations between the two sets of data.

b.Comparing individual items.

c. Pie chart	d. Bubble chart
	c. Showing the relationship of parts to a whole
	with more data series.

2.A bar chart is useful for ……………. .

(Choose all that apply)

a.Showing the amount of change in data over time.

b.Showing the relationship of parts to a whole.

c.When searchingthe relation between two or more variables

d.Comparing individual items.

d.Showing changes to data at regular intervals.

7.Which of the following tabs contains the Chart command?

a. Home	b. Page Layout
c. Formulas	d. Insert

3.From where can you change the positions of: Titles, Legend and Data Labels?

a. Chart Options	b. Source Data
c. Chart Layouts	d. Chart Type

4.Which chart type is used to show the optimum combinations between the two sets of data?

a. Column	b. Scatter
c. Pie	d. Surface

5.Can you add more data series to a pie chart?

a. Yes	b. Sometimes
c. No	d. It depends

8.Charts typically have two axes used to categorize and measure data: a category

(x)axis and a value (y) axis. Pie and doughnut charts do not have any axes.

TRUE

FALSE

9.Which chart type(s) cannot be used for the table below?

I.Doughnut II. Column

III. Pie

IV. Cone

a. III

b. I-III

NAME	GRADE
John S.	80
Jack W.	65
Adreea T.	92
Bob C.	76

c. III-IV

d. I-III-IV

Charts

127

Project

1. What type of chart would work best to represent the information in this worksheet?

	A	B	C		D	E
1		Surat Co. Stores
2		Quarterly Sales Report
3		Quarter 1	Quarter 2		Quarter 3	Quarter 4
4	TV	4510	5000		12000	35000
5	Computer	5620	6000		4000	8000
6	Washing Machines	7840	8500		4700	10000
7	Walkman	800	2000		2500	3000

2.Use the Chart Wizard to create a chart that plots the cell range A4:E7. Title the Chart “Quarterly Sales” and place the chart in a separate sheet.

3.Click the legend to select it and change the font size used in the legend to 12 pt.

4.Make the legend taller by about 0.5” taller, and drag it to the bottom right of the chart.

5.Change the chart type to a 3-D Bar chart.

6.Change the color of the Walkman series to light green.

7.Use the drawing toolbar to add an arrow that points to the largest number in the chart and add a textbox at the other end of the arrow that says “Super!”

8.Change the chart data source so that only the totals for each component (cell range F4:F7) are plotted in the chart.

128	Microsoft Excel

Practice

1. Make the following chart according to the Market Shares for automobiles in your country.

2. Make the following chart for the given data.

	129
Charts

Word Search Puzzle

Solve the clues and find each word in the puzzle.

R

W

Z

E

M

L

E

Z

U

S

D

T

U

N

N

A

I

X

E

L

İ

X

A

R

J

I

J

E

I

R

Z

G

T

X

T

A

L

O

R

O

N

I

M

B

E

I

A

H

R

V

L

E

I

P

A

P

K

N

T

C

Q

D

A

C

K

R

U

B

H

Z

D

H

C

N

B

J

O

E

K

C

O

G

U

L

Z

O

E

I

O

N

S

L

K

I

T

R

C

O

L

J

V

S

E

I

R

E

S

B

A

X

E

S

I

L

H

G

G

D

Y

K

Words

Clues

AXES

Plural form of a straight line around which an object rotates.

A chart type.

Visual display of information.

A chart type.

Information

Brief description which appears on a computerized note.

Used to identify each bar in a 3-D column chart.

A chart type.

The secondary gridlines on the background of a chart.

General or descriptive heading for a chart.

A chart type used especially on limited data.

The main gridlines on the background of a chart.

130	Microsoft Excel