Tutorial 4

Enhancing a Workbook with Charts and Graphs

Objectives:

a. Create an embedded chart
b. Create and format a pie chart
c. Work with chart titles and legends
d. Cratae and format a column chart
e. Create and format a line chart
f. Modify a chart data source
g. Create and format a combined chart
h. Create a 3-D chart
i. Create and format sparklines and data bars
j. Create a chart sheet.


Important Points.
1. Each chart has a data source that must be selected.  Excel uses the first row of the selected range as the series name, the first column as the category values, and the remaining columns as the series values. If the data source is organized in rows rather that in columns the first row contains the category values, the remaining rows contain the data values for erach data series, and the fist columns of each series row contain the series names.

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. Insert Pie Chart. - Select the data to build the chart on, Click Insert tab on ribbon, in charts group click Pie button, then click 2-d pie section 

investment Category Assest
cash 7144
U.S. stocks 49250
Non-U.S. Stocks 9715
Bonds 15177
Other 3360
Total Assets 84646 mil

The above is the data being used for the chart below.
Pie chart

4. Moving the chart to a different worksheet.  Click Chart Tools, click  Design, click move chart/location button you will see:

Chart Location dialog box
Click the object in if you want to go to an existing sheet and select the one you want.  If you are going to a new sheet the click new sheet and type in the name you want for the new sheet.
5. To move the pie chart and size it.  Move the pointer over the chart until you see the screen tip Chart Area, Left click and hold and drag the chart to where you want it. Then move to one of the sizeing handles on the chart and move the cursor to expand or contract the chart.

6. To apply a different chart style to Assets Pie.  Click Chart Tools Design tab, in the chart styles click the more button to open chart style gallery and select the one you want. You will see:

Alternate Chart Styles

7. To apply chart layouts- in the chart layouts group on the chart tools design tab click the more button you will see:

Chart layouts

8. Formatting the Chart Title-click the chart title , type in the new chart title, press enter, cick the home key, click the font size arrow and select the size you want, click on the text color and choose the color you want, click on fill back ground and select the color you want.

9. Format Chart Legend.  click the chart tools layout tab, then click legend button; you will
see:

Charts legend button

Click more legends. You will see:
More legends dialog box

Select the options that you want.

10. 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.
11. Leader lines might be added to the labels to connect them to their corresponding slices.
12. An exploded pie chart moves one slice away from the pie as if someone were taking here piece out of the pie.
13. Formatting chart elements.
14. Data labels: In labels group on chart tools layout tab, click data labels button, click more data label option. select the option you want.
15. 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.
16. 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.
17.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.
18. Explode the entire pie chart - click the pie chart, drag any pie slice to explode all the slices, release mouse.
19. 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.
20. 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.
21. 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.
22. 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.
23. 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
24. 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.
25. 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.
26. 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  You will see:
Selecting axes to format
, click more primary vertical axis option, You will see:
Format Vertical axes dialog box
 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.
27. Format chart columns: click  chart tools layout tab, click chart elements arrow, select the series, click format selection,  You will see:
format data series dialog box
Select the options you want to apply to the columns from the format data series dialog box , click close.
28. Creating the fund history line chart.-used when the data consists of values drawn from categories that follow a sequential order of evernly spaced intervals. - select the data A4-D44 from fund hiistory , click insert, clinc line, click line.  You will see:
Fund History line chart

a. Insert title - Click chart tools layout, click chart title, you will see:
Chart title dialog box

Click above chart, you will see:

Chart title inserted into chart

With the chart title selected, type in the new title and change the format to what you want.

b. Modify the value axis scale: Double click the value axis.  You will see:

Format Axis dialog box

c. Format Category axis labels:  double click category axis.  You will see:
formatiing catagory axis

d. Create custom format for the four digit year. Click number on left side of format axis box.  You will see:
Format four digit year dialog box

e. Format the chart legend: - double click the legend: You will see:
format chart legend dialog box

Select the options that you want.
f. Adding gridlines:  click Chart tools layout, in axes group click gridlines, then point to vertical gridlines you will see:
Gridline dialog box

g. Add title to vertical axis: Click chart tools layug, in axes grup click gridlines, point to Primary vertical gridlines, can clickminor Gridlines: You will see:

Vertical gridline dialog box

Select the option you want and type in the title.

29. Understand the Series function:  =Series(name, categories, values, order)
a. name - is the name that apears in the chart
b. categories are the labels tat apear on the category axis.
c. values are the values that excel plot for the data series
d. order is the order in which the series appears in the chart.
30. Combination chart is a chart tht combines two or more chart types in a single graph.

a. to apply the line  chart to the libalities Data.  click the chart tools layout, click chart elements arrow, click series libality e. You will see:
Select data series

b. Click chart tools design, click change chart type, you will see:
Change Chart type dialog box

c. click line with markers, click ok,  you will see:

combination chart

31. To remove the line and edit the markers in the line chart. 
a.click the line for thelibalities series, click chart tools layout, click format selection. You will see:
format data series dialog box

b. click line color, click no color, click marker option you will see:
format data series - Marker options
c. click built-in option, click type arrow, select horizontal line, click size and set to ten,
click close: You will see:
Data markers for series liabilities

32. Creating a 3-D chart. click the Assets pie chart, click chart tools design, click Change Chart Type, click pei, click Pie in 3-D, click ok  You will see:
Assets chart in 3-d

a. to rotate andreorinet the 3-d pie chart - click chart tools layout, in background group click the 3-d rotation button.  You will see:
3-D rotation dialog box
b. In the rotation section type 90in the X box, and 20 in the Y box and click close: You will see:
Rotated 3-D pie chart

33. Crerating sparklines- asparkline is a mini chart that is displayed withing a worksheet cell. Use to convey maximum amount of graphical information within avery small space. Line,column, and win/loss arethe type of sparklines available.  Go to sectors worksheet, in e3 type 10-year history, select range E4-E15, click insert, click line button in Sparkline group, You will see:
Crate sparkline dialog box
with the insertionpoint in the datarange box, click the sector history sheet tab, select the data range B6:M45, click ok.  You will see:
Chart with line Sparklines

34. Creating data Bars - a data bar is a conditional format that adds a horizontal bar to the background of a cell containing a numberic value.  when applied to a range of cells, the data bars have the same appearance as a bar chart with each cell containing one bar. In the sectors worksheet, select the range C4:D15, clickhome, in the styles group click conditional formatting,
conditional formatting
a. point to data bars,

data bars

b. select blue data bar.  you will see:
data bars display in sheet

c. Modifying a data bar rule - select range with databars - click conditional formatting , click manage rules. You will see:
Conditional formatting manage rules.

d. Make sure current selection is selected, click Edit Rule button,  you will see:
Edit conditional formatting rules dialog box

e. In the type row, click Maximum, then click number, in value row type.40 in the maximum box, click Ok for each dialog box. You will see:
Data bars adjusted in cells
The data bars have been adjusted so that all of the values are displayed.
35. Creating a chart sheet.- Select the chart then click move chart in  location on the menu bar: You will see:

Move chart menu
a. click new sheet and type in Cover Sheet, click ok. You will see:

Cover sheet with line graph

b. Format chart in cover sheet. -click the more button in the chart styles group you will see:

More styles

c. Click style 40  You will see:
Style 40 applied to line graph

d. Click the chart tools layout tab, in label group, click legend, then cick none.
e. In the Axes group click the Axes button the set the Primary Horizontal Axis, the Promary vertical Sxis, and the Depth Axis to none.  You will see:
Chart with axes removed

f. In backgroud click 3-D rotation  Set x value to 60, y value to 20, perspective value to 80You will see:

3-D rotation button

g. Click the fill on the left side of the dialog box, click gradinetg fill, click preset colors, click daybreadk, click direction, click linear down.
fill options

h. click close.  You will see:
Cover sheet

i. Overlay and format chart title: In labels group click chart title, click centered overlay title, type new centur fund, touch enter, click home, change point size to 36 and font color to white.  You will see:
chart with formatted title