آموزش کامل Excel 2010-EN

[A M I R]

[h=2]Working with Charts

[h=3]Title

The title should clearly describe what the chart is illustrating.


​

---

[h=3]Data Series

The data series consists of the related data points in a chart. If there are multiple data series in the

chart, each one will have a different color or style. Pie charts can only have one data series.


​

 

[A M I R]

[h=2]Working with Charts

[h=3]Vertical Axis

The vertical axis (also known as the y axis) is the vertical part of the chart.

In this example (a column chart), the vertical axis measures the height, or value of the columns, so it

is also called the value axis. However, in a bar chart, the horizontal axis would be the value axis.


​

---

[h=3]Legend

The legend identifies which data series each color on the chart represents. For many charts it is

crucial, but for some charts it may not be necessary and can be deleted.


In this example, the legend allows the viewer to identify the different book genres in the chart.


​

 

[A M I R]

[h=2]Working with Charts

[h=3]Horizontal Axis

The horizontal axis (also known as the x axis) is the horizontal part of the chart.


In this example, the horizontal axis identifies the categories in the chart, so it is also called the

category axis. However, in a bar chart, the vertical axis would be the category axis.


​

​

 

[A M I R]

[h=2]Working with Charts

[h=4]To Create a Chart:

Select the cells that you want to chart, including the column titles and the row labels. These cells will be the source data for the chart.


Click the Insert tab.

In the Charts group, select the desired chart category (Column, for example).


Select the desired chart type from the drop-down menu (Clustered Column, for example).


The chart will appear in the worksheet.


​

 

[A M I R]

[h=2]Working with Charts

[h=3]Chart Tools

Once you insert a chart, a set of Chart Tools, arranged into three tabs, will appear on the

Ribbon. These are only visible when the chart is selected. You can use these three tabs to modify your chart.


​

 

[A M I R]

[h=2]Working with Charts


[h=4]To Change the Chart Type:

From the Design tab, click the Change Chart Type command. A dialog box appears.


Select the desired chart type and click OK.


​

 

[A M I R]

[h=2]Working with Charts

[h=4]To Switch Row and Column Data:
Sometimes when you create a chart, the data may not be grouped the way you want

it to be. In the clustered column chart below, the Book Sales statistics are grouped by Fiction/Non-Fiction, with

a column for each year. However, you can also switch the row and column data so that the chart will group the

statistics by year, with columns for Fiction and Non-Fiction. In both cases, the chart contains the same data; it's just organized differently.


Select the chart.

From the Design tab, select the Switch Row/Column command.


The chart will then readjust.

​

 

[A M I R]

[h=2]Working with Charts

[h=4]To Change the Chart Layout:
Select the Design tab.

Click the More drop-down arrow in the Chart Layouts group to see all of the available layouts.


Select the desired layout.


The chart will update to reflect the new layout.


Some layouts include chart titles, axes, or legend labels. To change them, just

place the insertion point in the text and begin typing.


​

 

[A M I R]

[h=2]Working with Charts

[h=4]To Change the Chart Style:
Select the Design tab.

Click the More drop-down arrow in the Chart Styles group to see all of the available styles.


Select the desired style.


The chart will update to reflect the new style.


​

 

[A M I R]

[h=2]Working with Charts

[h=4]To Move the Chart to a Different Worksheet:

Select the Design tab.

Click the Move Chart command. A dialog box appears. The current location of the chart is selected.


Select the desired location for the chart (i.e., choose an existing worksheet, or select New Sheet and name it).


Click OK. The chart will appear in the new location.
​

 

[A M I R]

[h=2]Working with Charts

[h=3]Challenge!
Open an existing Excel workbook. If you want, you can use this example.

Use worksheet data to create a chart.

Change the chart layout.

Apply a chart style.

Move the chart to a different worksheet.

​

 

[A M I R]

[h=2]Working with Sparklines

[h=3]Introduction

Sparklines are miniature charts that fit into a single cell. Since they're so compact, you can place a large number of

them in your worksheets. For example, you could place one sparkline on each row to show trends within that row. In this

lesson, you will learn how to insert sparklines and change their type and appearance.


​

 

[A M I R]

[h=2]Working with Sparklines

[h=3]Creating Sparklines

Sparklines were introduced in Excel 2010 to be a convenient alternative to charts. Unlike a

traditional chart, a sparkline is placed inside a cell, allowing you to easily create a large number of sparklines (for example, one on each row).

​

 

[A M I R]

[h=2]Working with Sparklines

[h=4]Types of Sparklines
There are three different types of sparklines: Line, Column, and Win/Loss. Line and Column work

the same as line and column charts. Win/Loss is similar to Column, except it only shows whether each

value is positive or negative, instead of how high or low the values are. All three types can display

markers at important points, such as the highest and lowest points, to make them easier to read.





​

 

[A M I R]

[h=2]Working with Sparklines

[h=4]Why Use Sparklines?

Sparklines are basically charts, so why would you want to use sparklines instead of charts? Sparklines have

certain advantages that make them more convenient in many cases. Imagine you have 1000 rows of data. If

you place a sparkline on each row, it will be right next to its source data, making it easy to see the relationships

between the numbers and the sparkline. If you used a traditional chart, it would need to have 1000 data series in

order to represent all of the rows, and you would probably need to do a lot of scrolling to find the relevant data in the worksheet.




Sparklines are ideal for situations where you just want to make the data clearer and more eye-catching, and

where you don't need all of the features of a full chart. On the other hand, charts are ideal for situations where

you want to represent the data in greater detail, and they are often better for comparing different data series.

​

 

[A M I R]

[h=2]Working with Sparklines

[h=4]To Create Sparklines:
Generally, you will have one sparkline for each row, but you can create as many as you want in any

location you want. Just like with formulas it's usually easiest to create a single sparkline and then

use the fill handle to automatically create the sparklines for the remaining rows.


Select the cells that you will need for the first sparkline. In this example, we are creating a sparkline for Kathy Albertson, so we will select her sales data.


Click the Insert tab.


In the Sparklines group, select Line. A dialog box will appear


Make sure the insertion point is next to Location Range.

Click the cell where you want the sparkline to be. In this example, we'll select the cell to the right of the selected cells.


Click OK. The sparkline will appear in the document.

Click and drag the fill handle downward.


Sparklines will be created for the remaining rows.

​

 

[A M I R]

[h=2]Working with Sparklines

[h=3]Changing the Appearance of Your Sparklines [h=4]To Show Points on the Sparkline:
Certain points on the sparkline can be emphasized with markers, or dots, making the sparkline more readable. For

example, in a line with a lot of ups and downs, it may be difficult to tell which ones are the highest and lowest points, but

if you show the High Point and Low Point, it will be easy to identify them.


Select the sparklines that you want to change. If they are grouped, you only need to select one of them.

Locate the Show group in the Design tab.

Hover over the different checkboxes to see a description of each one.


Check each option that you want to show. The sparklines will update to show the selected options.


​

 

[A M I R]

[h=2]Working with Sparklines

[h=4]To Change the Style:

Select the sparklines that you want to change.

Locate the Style group in the Design tab.

Click the More drop-down arrow to show all of the available styles.


Select the desired style.


The sparklines will update to show the selected style.


​

 

[A M I R]

[h=2]Working with Sparklines

[h=4]To Change the Sparkline Type:
Select the sparklines that you want to change.

Locate the Type group in the Design tab.

Select the desired type (Column, for example).


The sparkline will update to reflect the new type.


Some sparkline types will be better or worse for certain types of data. For example, Win/Loss is best suited

for data where there may be positive and negative values (such as net earnings).

​

 

[A M I R]

[h=2]Working with Sparklines

[h=3]Changing the Display Range

By default, each sparkline is scaled to fit the maximum and minimum values of its own data. This allows it

to fill the entire cell no matter how high or low the values are. However, it has a downside: if you are trying

to compare several sparklines, you won't be able to tell at a glance which ones have higher or lower values. The

solution is to make the display range the same for all of the sparklines.

​