OFFICE AUTOMATION – I EXCEL - BASICS 29. CREATING CHARTS AND GRAPHS In this lesson you will learn to make Charts and Graphs using MS Excel. This will equip you to make charts and graphs to represent your set of data in a workbook.
29.0 Objectives On completion of this lesson you will be able to: • •
Create and edit charts. E-mail charts.
29.1 Introduction In the last lesson you have learnt to apply functions and formulas in a worksheet. This lesson will equip you to create Charts and Graphs in MS Excel.
29.2 Charts Charts are used as a medium to make comparisons, study patterns, and trends in a data set.
29.3 Types of Charts Charts are of various types, some of the types of Charts are as shown below.
29.4 Define a Data Source Data source is a table which consists of headings at the first row or first column of the data range.Defining Data Source is the main part of creating a chart, the decision of selection of type of chart is mainly based on the data.
29.5 Creating Charts and Graphs Make sure that the data on your worksheet is properly arranged for the type of chart you want to create. Select the data range for which you want to create a chart.
From the Insert menu, click on “Charts”… or click on the Charts icon on the Standard Tool bar. “Chart Wizard: Step 1 of 4 Chart Type” dialog box appears. Select the type of chart and press Next > button.
Chart Wizard Step 2 of 4 appears Check if the data range is appearing correctly and press Next> button
“Chart Wizard Step 3 of 4” appears with “Titles” Tab activated. Type the titles for chart, X axis and Y axis, and click on Axes tab.
“Chart Wizard Step 3 of 4” appears with “Axes” Tab activated. Select the category for axis. Generally it is Automatic, based on the selected data range. Do not change the axes, keep them as Automatic. Click on “Gridlines” tab
“Chart Wizard Step 3 of 4” appears with Gridlines Tab activated. These options also appear automatically based on the selected data range. If required check minor gridlines.
Click on “Legend” tab. “Chart Wizard Step 3 of 4” appears with Legend Tab activated. Select the placement of legend as per the requirement.
Click on “Data Labels” tab. “Chart Wizard Step 3 of 4” appears with Data Labels Tab activated. Check if you want values to appear on the graph with bars, else leave them blank.
Click on “Data Table” tab. “Chart Wizard Step 3 of 4” appears with “Data Table” Tab activated. Check if you want values to appear below the chart. Click on Next> button.
Chart Wizard Step 4 of 4 appears Chart Location.
Select “As New Sheet:” If you want your chart to appear on a new sheet. Select “As object in:” If you want your chart to appear on the same sheet.
Press “Finish”. You will see your chart as shown below.
29.6 Copy and Paste Charts To copy and paste charts from one file to another, right click on the chart and select “Cut” or “Copy”. Right click on the place where you want to paste the chart selected and select “Paste”.
29.7 Editing of Charts The figure describes the different parts of the chart.
Once the chart is ready, it can be edited if required. The type of chart can be changed and viewed before finalizing it.
29.7.1 Change Type of Chart Right click on the chart and click on “Chart type” option from “Options” menu.
“Chart type” dialog box appears. You can change the type of chart here. Click on the “Press and hold to View Sample” button to see how the chart will look when you choose this type of the chart.
Select the “Chart title� to change font type and/or size as shown in the figure.
Select the font type as shown in the figure.
Select the font size as shown in the figure.
Click on B for Bold, I for Italic, U for underline Change the color of the text by selecting this font color icon.
Select Chart area by clicking on chart, as shown in the figure.
Fill Color in the chart area by selecting “Fill Color” icon.
The chart will appear as shown in the figure. Select Chart area by clicking on it, this will appear as shown in the figure. Fill Color in the chart area by selecting “Fill Color” icon.
The chart will appear as shown in the figure. Click on the bars/line of the chart to select, as shown in the figure.
Double click on the bar/line to view “Format data Series� dialog box.
29.7.2 Borders You can change the borders for the bars of your chart.
29.7.3 Area You can fill the color in the bars of your chart. Click on “Fill Effects”… button.
29.7.4 Fill Effects: Gradient Tab Fill Effects dialog box appears with “Gradient” tab activated.
Select one color as shown in the figure. Click on left or right arrows to make this color Dark or Light.
You can also select “Two Colors” option as shown in the figure. Select First Color from Color 1, and select the second color from Color 2.
You can also Select Preset colors from Preset drop down list.
Variants appear on the screen based on the selected colors for shading styles.
If “Horizontal” Shading Style is selected, the variant will appear as shown.
If “Vertical” Shading Style is selected, the variant will appear as shown.
If “Diagonal up” shading Style is selected, the variant will appear as shown.
If “Diagonal down” shading Style is selected, the variant will appear as shown.
If “From Corner” shading Style is selected, the variant will appear as shown.
If “From center” Shading Style is selected, the variant will appear as shown.
29.7.5 Fill Effects: Texture Tab Click on “Texture” tab, to fill textures in the graph.
Selected texture will appear in Sample. Click on “OK” to fill. 29.7.6 Fill Effects: Pattern Tab Click on “Pattern” Tab, to fill patterns in the graph.
Selected pattern will appear in Sample with selected Foreground and Background colors. Click on “OK” to fill.
29.7.7 Fill Effects: Picture Tab Click on “Picture” Tab, to fill picture in the graph.
Select picture by clicking on the “Select Picture” button. Selected picture will appear in Sample. Click “OK” to fill.
Self-Check Questions Fill in the Blanks 1. To change the background color of the chart you will double click on ______________________ 2. ______________________ option is used from options menu to change the type of the chart. 3. ______________________ option is used to change the source of data from options menu for the chart. Answer True or False 4. If you change data for the chart, the chart will not change automatically 5. You cannot change the font of the title of the chart 6. You can change the type of chart
29.8 Summing Up In this lesson you have familiarized yourself with the following: • • • • • •
Charts Define a data source Creating Charts and Graphs Copy and Paste Charts Editing in Charts E-mailing a chart
29.9 Answers to Self-Questions 1. 2. 3. 4. 5. 6.
Chart Area Chart Type Source Data F F T
29.10 Terminal Questions 1. 2. 3. 4. 5.
What is the use of a Chart? Name any three types of charts? What is a Data Source? How do you copy and paste a chart? How do you fill pattern in the chart?
29.11 Glossary •
Charts: A picture based on some numbers and it changes when numbers are changed.