Lab Manual: Practical 5 – Creating and Formatting
Charts
Course: Advanced Excel with
Visualization | Course Code: CAD41VSP201
Practical Number: 5
Topic: Creating Various Chart Types and Applying
Comprehensive Formatting.
Duration: 2 Hours
1. Objective
The primary
objective of this practical is to enable students to effectively create and
format various types of charts in Microsoft Excel to visualize data. Students
will learn to:
- Choose
appropriate chart types for different data sets and analytical goals.
- Create
basic column, bar, pie, and line charts.
- Add and
customize essential chart elements such as titles, axis labels, legends,
and data labels.
- Apply
various formatting options to chart elements, data series, and the chart
area for improved readability and aesthetic appeal.
- Understand
how to effectively present data visually to convey insights.
2. Introduction
Data
visualization is a critical component of data analysis, allowing for quick
comprehension of large datasets. Charts in Excel provide a powerful way to
represent data graphically, making it easier to identify patterns, trends, and
outliers. This practical will guide you through the process of selecting the
right chart type and applying extensive formatting to create professional and
insightful visualizations. We will build upon the marksheet
created in Practical 3, using its data for some of our charting exercises.
3. Pre-requisites
- Successful
completion of Practical 3.
- Basic
understanding of Microsoft Excel interface.
- Ability
to open, save, and navigate Excel worksheets.
4. Software
Required
- Microsoft
Excel
5. Procedure:
Practical Steps with Examples
Ensure you
have your completed marksheet from Practical 3 open.
We will use this table as our dataset for creating charts.
Step 5.1: Preparing Data for Charting
For this
practical, we will use the Marksheet data from Practical 3. Open your workbook and
ensure you have the Marksheet tab selected.
|
Student Name |
Subject 1 |
Subject 2 |
Subject 3 |
Subject 4 |
Subject 5 |
Total Marks |
Percentage |
Grade |
Result |
|
Alice |
85 |
72 |
90 |
68 |
75 |
390 |
78.0% |
A |
Pass |
|
Bob |
55 |
60 |
48 |
50 |
62 |
275 |
55.0% |
B |
Pass |
|
Charlie |
92 |
88 |
95 |
80 |
89 |
444 |
88.8% |
A |
Pass |
|
Diana |
30 |
45 |
38 |
25 |
40 |
178 |
35.6% |
Fail |
Fail |
|
Eva |
70 |
65 |
72 |
78 |
63 |
348 |
69.6% |
B |
Pass |
Step 5.2: Creating a Basic Column Chart
A column
chart is excellent for comparing values across different categories.
1.
Select Data: Select the Student
Name column
(e.g., A4:A8) and the Total Marks column (e.g., G4:G8). To select non-adjacent columns, select the first
column, then hold down the Ctrl key while selecting the second column.
2.
Insert Chart: Go to the Insert tab on the Excel ribbon.
3.
Choose Chart Type: In the Charts group, click on Insert Column or Bar Chart and select the Clustered Column (2-D Column) option.
o
Observation: A column chart will appear on your worksheet, showing each student’s
total marks.
4.
Move Chart: Click on the chart, then go to the Chart Design tab > Location group > Move Chart. Choose New sheet and name it Total Marks Chart, then click OK. This places the chart on its own sheet for better focus.
Step 5.3: Creating a Pie Chart
A pie chart
is suitable for showing the proportion of parts to a whole. For this, we’ll
need a count of each grade.
1.
Prepare Data:
o
On your Marksheet tab, find an empty area (e.g., cells L3:M6).
o
In L3, type Grade. In M3, type Count.
o
In L4, type A. In L5, type B. In L6, type C.
o
In M4, enter the formula =COUNTIF(I:I,"A") to count ‘A’ grades.
o
In M5, enter =COUNTIF(I:I,"B").
o
In M6, enter =COUNTIF(I:I,"C"). (Adjust I:I to your actual Grade column).
2.
Select Data: Select the Grade categories and their Counts (e.g., L4:M6).
3.
Insert Chart: Go to the Insert tab.
4.
Choose Chart Type: In the Charts group, click on Insert Pie or Doughnut Chart and select 2-D Pie.
o
Observation: A pie chart showing the distribution of grades will appear.
5.
Move Chart: Move the chart to a New sheet named Grade Distribution.
Step 5.4: Creating a Line Chart
A line chart
is ideal for showing trends over sequential data, such as a student’s performance
across different subjects.
1.
Select Data: Select a single student’s subject marks (e.g., B4:F4 for Alice).
2.
Insert Chart: Go to the Insert tab.
3.
Choose Chart Type: In the Charts group, click on Insert Line or Area Chart and select 2-D Line.
o
Observation: A line chart will appear showing Alice’s scores across subjects.
4.
Edit Horizontal Axis Labels: Right-click on the chart, select Select Data…. In the Select Data Source dialog, under Horizontal Axis Labels, click Edit. Select the range B3:F3 (Subject 1 to Subject 5 headers) and click OK twice.
o
Observation: The x-axis labels will now correctly show "Subject 1",
"Subject 2", etc.
5.
Move Chart: Move the chart to a New sheet named Alice Performance.
Step 5.5: Formatting Chart Elements
Now, let’s
learn how to customize the appearance of our charts. We’ll use the Total Marks Chart for these examples.
1.
Activate Chart: Go to the Total Marks
Chart sheet.
Click anywhere on the chart to activate the Chart Design and Format tabs.
2.
Chart Title:
o
Click on the existing "Chart Title" textbox.
o
Type: Student Total Marks Performance.
o
Format Title: Select the title, go to the Format tab, use WordArt Styles or Font group on Home tab to change font, size, color.
3.
Axis Titles:
o
Click the + button on the right side of the
chart.
o
Check Axis Titles.
o
Click on the Primary
Horizontal axis title,
type: Student Name.
o
Click on the Primary
Vertical axis title,
type: Total Marks.
o
Format Axis Titles: Select an axis title, then use the Format tab or right-click > Format Axis Title… to change font, color, alignment.
4.
Legend:
o
Click the + button.
o
Check Legend. You might only have "Total
Marks" as a series.
o
Position Legend: Click the arrow next to Legend and choose Bottom or Top.
o
Format Legend: Select the legend box, right-click > Format Legend… to change fill, border, font.
5.
Data Labels:
o
Click the + button.
o
Check Data Labels.
o
Position Labels: Click the arrow next to Data Labels and choose Outside End.
o
Format Labels: Select one data label, then right-click > Format Data Labels…. You can choose what to include
and change font, color.
6.
Data Series Formatting (Columns/Bars):
o
Click on one of the columns in the chart. This selects the entire data
series.
o
Right-click > Format
Data Series…. A Format Data Series pane will appear on the right.
o
Fill & Line: Under the Fill tab (paint bucket icon), change
the Color of the bars, or choose a Gradient fill or Picture or texture fill.
o
Border: Add a solid
line border to the bars with a specific color.
o
Series Options: Under Series Options (bar chart icon), adjust Gap
Width to make
bars wider or narrower.
7.
Chart Area & Plot Area Formatting:
o
Chart Area: Click on the blank area outside the plot area but within the chart
boundary. Right-click > Format
Chart Area…. Change Fill color or add a Border.
o
Plot Area: Click on the area where the columns are displayed (inside the axes).
Right-click > Format Plot
Area…. Change Fill color (e.g., light grey)
to make the data stand out.
8.
Gridlines:
o
Click the + button.
o
Uncheck Gridlines to remove them, or click the
arrow next to Gridlines to select specific gridlines and
format them individually (e.g., change line style or color).
Step 5.6: Changing Chart Type and Quick Layouts
1.
Change Chart Type:
o
With the chart selected, go to the Chart Design tab.
o
In the Type group, click Change Chart Type.
o
Select a different chart type (e.g., Bar instead of Column) and click OK.
o
Observation: The chart will transform, keeping most of its formatting.
2.
Quick Layout:
o
On the Chart Design tab, in the Chart Layouts group, click Quick Layout.
o
Hover over different layouts to see how they arrange chart elements
(titles, legend, data labels) automatically. Select one you like.
3.
Change Colors/Styles:
o
On the Chart Design tab, use the Change Colors dropdown to apply different color palettes.
o
Use Chart Styles to apply pre-defined visual
styles (backgrounds, effects).
6. Exercises /
Tasks
Apply the
concepts learned to complete the following tasks:
1.
Student Percentage Comparison: Create a Bar Chart comparing the Percentage of all students from the Marksheet. Ensure the chart has a clear
title, axis titles, and data labels displaying the percentages.
2.
Subject-wise Average Marks:
o
First, calculate the average marks for each Subject (Subject 1, Subject 2, etc.) in your Marksheet (e.g., using =AVERAGE(B4:B8) for Subject 1).
o
Create a Column Chart to display these average marks.
Format the columns with a unique color.
3.
Pass/Fail Ratio:
o
Count the number of Pass and Fail results from your Result column using COUNTIF.
o
Create a Pie Chart showing the proportion of Pass vs. Fail. Add data labels showing both the count and
percentage for each slice.
4.
Individual Performance Line Chart:
o
Create a Line Chart for a different student (e.g.,
Bob) showing their performance across the five subjects.
o
Customize the line color and add markers to
each data point.
o
Add a subtle background fill to the Plot Area.
5.
Practice Quick Layouts and Styles: For any of your created charts, experiment with at
least three different Quick
Layouts and two
different Chart Styles from the Chart Design tab.
7. Assessment /
Deliverables
- Submit
the completed Excel workbook (.xlsx file)
containing the Marksheet and new sheets for each chart created (Total Marks
Chart, Grade Distribution, Alice Performance, and charts from the Exercises/Tasks).
- Ensure
all charts are clearly titled, have appropriate axis labels (where
applicable), legends, and data labels.
- Verify
that various formatting options (colors, fills,
borders, fonts) have been applied to enhance the visual presentation of
the charts.
- Be prepared
to explain your choice of chart type and formatting decisions for each visualization.
