Excel 2007, working with charts and graphs

 

1. Create an embedded chart
2. Work with chart titles and legends.
3. Create and format a pie chart.
4. Work with 3D charts.
5. Create and format a column chart.
6. Create and format a line chart.
7. Use custom formatting with chart axes.
8. Work with tick marks and scale values.
9. Create and format a combined chart.
10.Insert and format a graphic shape.
11. Create a chart sheet

Important Points:

1. Chart Wizard - select data, chick chart wizard, select chart type, next, make any modification to chart data source, next, make modifications to chart appearance, next, specify location for chart, finish.

Dialog box Task Options
Chart Type Select from list of available chart types and corresponding sub-types, or choose to customize a chart type
Chart Source Data Specify the cells that contain the data on which the chart will be based and the cells that contain the labels that will appear in the chart
Chart Options Change the appearance of the chart by selecting the options that affect titles, axes, gridlines, legends, data labels, and data tables
Chart Location specify where the chart will be placed: embedded as on object in the worksheet containing the data or on a separate worksheet, also called a chart sheet.

2. Chart Type:

Chart Type Description
Column compares values from different categories. Values are indicated by height of column
Bar compares values from different categories. Values are indicated by the length of the bars.
Line compares values from different categories. Values are indicated by the height of the line. Often used to show trends.
Pie Compares relative values of different categories to the whole. value are indicated by the size of the pie slice.
XY(scatter) shows the patterns or relationship between two or more sets of numeric values. Often used in scientific studies and statistical analyses.
Area Similar to the line chart, except that areas under the lines are filled with colors indicating the different categories.
Doughnut similar to the pie chart, except the it can display multiple sets of data
Radar compares a collection of values from several different data sets.
Surface compares threes sets of values in a three-dimensional chart.
Bubble similar to the XY (scatter) chart, except the size of the data marker is determined by a third numeric value.
Stock Displays stock market data, including the high, low, opening and closing prices of a stock
3. Data source is the range that contain the data you want to display in the char.
4. data series consist of data values - plotted on vertical y-axis
5. Series name identifies the data series.
6. Series values are the actual data displayed in he chart.
7 category values are plotted on horizontal x-axis.
8. Plot area is rectangular area contain graphical representation of values.
9. Data marker represent data
10. Scale is the range of values that spans an axis.
11. Tic marks are like division lines on a ruler.
12. Gridlines extend tick marks Major and minor.
13. legend identifies the format of the data marker used for each series.
14. chart title is used to describe the content of the plot area and data series.
15. chart area is the entire chart.
16. Insert Titles into chart- click titles tag in Chart Wizard, Click Chart title, type in title, tab, type in x axis title, tab, type in Y axis title.
17. Insert Gridlines - click gridlines tab in chart wizard, click Major gridlines and/or Minor Gridlines.
18. Chart Location - embedded chart is a chart that is displayed within the worksheet- chart sheet is a new sheet that is automatically inserted into the workbook.
19. The embedded chart is placed in the worksheet next to its data source.
20. Designing effective charts
Keep it simple
Limit the number of data series used in the chart
Use gridlines in moderation
Choose colors carefully
Limit the use of different text styles to no more than two.
Analyze whether you need a chart in the first place
Pie charts should be used only when the number of categories is small and the relative sizes of the different slices can be easily determined.
Use a column or bar chart when you have several categories
Line charts are best for categories that follow a sequential order. note: time intervals must be a constant length if used in a line chart.
XY scatter charts show the patterns or relationships between two or more sets of values. Good for data recorded at irregular time intervals
Custom charts are created when none of the default chart will work. Also can buy third party vendors chart software that allow excel to create charts not built into the software.

Include data labels with all 3D charts


21. Pie Chart - select range of cells, click the insert tab on the Ribbon, in the charts group, click Pie in the chart type list box,
Name Pie chart with
Layout 1 Chart title, percentages, and legend
Layout 2 Chart title, percentages, and legend above the pie
Layout 3 Legend below the pie
Layout 4 Labels in pie slices
Layout 5 Chart title and labels in pie slices
Layout 6 Chart title, percentages, and legend to the right of the pie
Layout 7 Legend to the right of the pie

22. Format chart title: click the chart title, type in the new title, enter. Click home tab on ribbon, in front group click font size, select size.
23. Format Chart legend click chart tools layout tab on ribbon, in labels group, click the legend button, click show legend at left or right or bottom or top. In labels group click the legend button, click more legend options, select the option you want.
24. Format pie data labels- click chart, in labels group on chart tools layout tab, click data labels button, click, more data label options, select the option you want. click numbers in the list on the left side of box, click the option you want.
25. Leader lines might be added to the labels to connect them to their corresponding slices.
26. An exploded pie chart moves one slice away from the pie as if someone were taking here piece out of the pie.
27. Formatting chart elements.
28. Data labels: In labels group on chart tools layout tab, click data labels button, click more data label option. select the option you want.
29. Pie Slice Colors - click the pie, click one of the slices, chick home tab on ribbon, in font group click fill color button arrow, select the color you want.
30. Rotating a pie chart - double click the pie chart to open format data series box, double click the first slice of the pie chart, click in angle of first slice and put in the degrees desired, OK.
31.Explode a slice of a pie chart. - click the pie chart, click the pie slice you want to explode, drag the selected pie slice away from the rest, release mouse.
32. Explode the entire pie chart - click the pie chart, drag any pie slice to explode all the slices, release mouse.
33. Editing data source of a chart- go to the cell(s) that contain the data that the chart uses and change the data where appropriate.
34. Changing chart type- click the chart, click chart tools design tab on the ribbon., in the type group click change chart type, select the chart type you want.
35. A column chart displays values in different categories as columns with the height of the column being the value of the data. A Bar chart is a column chart turned on it side.
36. Creating a column chart - select data, click insert tab on the ribbon, in charts group click column, select the type of column chart you want, if you want the chart placed in another sheet, in the location group on the chart tools design tab, click move chart button, click object in arrow and select the sheet you want the chart to be place in.
37. Elements of a column chart
Vertical, or value axis displays the values associated with the heights of each column
Primary value axis
Secondary value axis
Scale is a range of values that spans the vertical axis.
Major tic mark act line the lines on a ruler making it easier to read the scale
Minor tick marks to further divide the space between the major tick marks.
Gridlines are use to extend the tick marks across the plot area
Horizontal, or category axis displays the categories associated with each data value
38. Format legend - click chart tools layout tab on the ribbon, in the labels group click the legend button and click the option of your choice.
39. Format chart title - click the chart title, click home tab on the ribbon, in the font group, click the font size arrow, click the font size you desire, enter.
40. Format chart axes - click the axis you want, in the font group on the home tab, click font size arrow, select the font size, click the chart tools layout tab on the ribbon, in the axes group click axes button, point to primary vertical axis , click more primary vertical axis option, click major unit fixed button, press tab, key in the sixw of major tick mark, click number, type in the number of decimal places, click close.
41. Format chart columns: click any column, in current selection group on the chart tools layout tab, click format selection, select the options you want to apply to the columns from the format data series dialog box , click close.
42. Creating a line chart which compares values from several categories with a sequential order. select data, click insert tab on the ribbon, in charts group click line button, select the line chart you want,
43. Shapes -excel has a gallery of 160 different shapes - Select cell, click insert tab on ribbon, in illustrations group click shapes button , select the shape you want from the gallery, drag the pointer over the area you want to insert the shape.
44. When a shape is selected the format tab appears on the ribbon with a label identifying it as drawing tools.
45. Resize the shape- in the size group on the drawing tools format tab, click shape height down arrow and select the desired height., in the size group click shape width down arrow and select the width you want.
46. To copy and paste shape - select the shape, click the home tab on the ribbon, in clipboard group click copy button, click paste.
47. Aligning and grouping shapes - click the first shape, hold down the shift key and click each of the remaining shapes, click the drawing tools format tab on the ribbon, in the arrange group click align button, click align middle, in the arrange group click align button then click distribute horizontally.
48. Group several shapes into single unit - with shapes selected, in the arrange group on the drawing tools format tab, click group button, then click group.
49. Create chart sheet - once chart has been built - in the locations group on the chart tools design tab, click move chart button, click new sheet option button type in name of sheet, click OK.
50. Format chart sheet. click the chart sheet, in chart styles group on the chart tools design tab click more select a style from the gallery,