Block 6

Unit II – Working with Tables and Charts

2.0 Introduction
2.1 Objectives
2.2 Introduction to tables
2.3 Creating and Deleting Tables
2.4 Format Tables
2.5 Changes Table Formats
2.6 Introduction to Charts
2.7 Creating and Inserting a Charts
2.8 Modify Charts
2.9 PIE, Line Charts etc…
2.10 Summary
2.11 Solutions / Answers
2.12 Further Readings

2.0 INTRODUCTION
Excel is especially used for accounting purpose. But at some point while using Excel, it’s likely
that you’ll come across information represented in charts, graphs and tables. Excel users should
learn to know how to interpret this information.

Data Tables are a range of cells that are used for testing and analyzing outcomes on a large scale.

A chart often makes it easier to understand the data in a worksheet because users can easily pick
out patterns and trends illustrated in the chart that are otherwise difficult to see.

In this Unit, let us study the tables and charts of MS Excel.

2.1 OBJECTIVES
After going through this Unit, you should be able to:
• Describe the significant use of the tables and charts in Excel
• Create, Insert, format and delete tables
• Make Changes in The Table Formats
• Use the Chart wizard
• Create, Insert and modify Charts
• Work with various types of charts in Excel

1
2.2 INTRODUTION TO TABLES

A Data Table will show you how by changing certain values in your formulas you can affect the
result of your formula. Data Tables can store the results of many different scenarios for you in
one table, so that you can analyze them to select which scenario is your best option. The results
are then written into a table form in your Workbook in a location specified by you. Data Tables
are written as array formulas, which therefore allow them to perform multiple calculations in a
single location.

There are two types of Data Tables, One-variable data tables and two-variable data tables.

One-variable data tables

The first thing you must do is to create a base or test model and tell your Data Table which
formulas from your base model you want to test. This is easily done from inside the Data Table
by placing a formula to reference the formula in the base model.

Two-variable Data Tables

You can use a two-variable Data Table to gauge the effect on one formula by changing the value
of two input cells within the one table. With this type of table, you can nominate two series of
data that can be placed back into the original model into two different input cells.

Let us see how to create, insert and delete a table in the next section.

2.3 CREATING AND DELETING TABLES

To make managing and analyzing a group of related data easier, you can turn a range of cells into
a Microsoft Office Excel table (previously known as an Excel list). A table typically contains
related data in a series of worksheet rows and columns that have been formatted as a table. By
using the table features, you can then manage the data in the table rows and columns
independently from the data in other rows and columns on the worksheet.

Create Tables
You can create a table from a data range that is set up with a header row. If your range does not
include a header row, you should add one.

Steps to create a table from a range

I. Select any cell in the range.

II. Click the Insert tab on the Ribbon and then click the Table button (or press Ctrl+L).
Excel displays the Create Table dialog box and selects the entire contiguous range that
includes the active cell pointer.

Diagram 1 explains the procedure.

2
 Diagram 1

List Tool Bar

III. Click OK. Excel creates the table, applies a default style, and adds a sort and filter arrow
to the cells in the header row. Excel also displays the Table Tools Design tab on the
Ribbon.

To Delete a Table

3
 I. Select the table
II. Right Click on the selected table
III. Choose clear contents

OR
I. On a worksheet, select a table.
II. Press DELETE

Converting a Table to a Range

If you no longer need to work with a table, you can convert the table to a normal range. Right-
click in the table and choose Table> Convert to Range from the contextual menu or click
anywhere in the table and then click Convert to Range in the Table Tools Design tab.

Organize Data In to Excel Tables

We can organize data in excel table with the help of filters. To see the details of pass students
only we have to follow the procedure as shown below

I. Click on the result filter

II. Choose pass
Diagram 2 explains the procedure.

4
 Diagram 2

Inserting New Rows or Columns

Steps to Insert a New Column to The End of Table

I. Click on a cell in the column after the last Column of table (in our example, Column G) i.e
click on any cell from cell G1 To cell G9
II. Start entering the data and press enter key.
III. Excel automatically extends the table horizontally.

Diagram 3 explains the procedure.

Diagram 3

5
 Steps to Insert a New Row to The End of Table

I. Similarly, if you enter data in the row below the last row of the table (In our example Row 10)
II. Excel extends the table vertically to include the new row.

Diagram 4 explains the procedure.

Diagram 4

Note: An exception to automatically extending tables is when the table is displaying

a Total Row. If you enter data below the Total Row, the table will not be extended.

Steps to Insert a New Column With in the Table

I. To add a column to the left of a column, Right-click on the column and choose Insert
II. Click on column option
III. A Column will be inserted

Diagram 5 explains the procedure

Diagram 5
6
Steps to Insert a New Row With in the Table

I. Select a row to add new row above the selected row

II. Right click and choose Insert Row

Diagram 6 explains the procedure.

Diagram 6

Print Excel Table

If the data that you want to print is in a Microsoft Office Excel table, you can print just the Excel
table.

Steps to print excel table

I. Click on any cell in the table

II. Click on print icon on the list tool bar
III. Enter file name in Save as dialog box and Click Save

Diagram 7 explains the procedure.

7
 Diagram 7

To Select a Row or Column in Excel Table

To Select Column(s) in Excel Table

I. Take the mouse pointer to the column header
II. Click on the column name when mouse pointer changes its shape and becomes a thick
arrow pointing downwards
III. The column will be selected

To select multiple non adjacent columns, press CTRL key and click on multiple columns
one by one. For adjacent columns click on first column, press SHIFT key and press the
last column of the range of columns to be selected.

8
Diagram 8 explains the procedure.

Diagram 8

To Select Row(s) in Excel Table

I. Take the mouse pointer to the first cell of the row to select
II. Press left click when mouse pointer changes its shape and becomes a thick arrow pointing
towards right
III. The row will be selected

To select multiple non adjacent rows, press CTRL key and click on multiple rows one by
one. For adjacent rows click on first row, press SHIFT key and press the last row of the
range of rows to be selected. Diagram 9 explains the procedure.

Diagram 9
9
2.4 FORMAT TABLES
STEPS TO APPLY AUTOFORMAT
I. Select the cells that contain the source data (data you want to format)
II. Choose FORMAT, Auto format. The auto format dialog box pops.
III. Scroll through the table format samples
IV. Select the format to apply
V. Click OK.
The procedure is explained in the following four figures.

Figure 1

Figure 2 Figure 3

10
Figure 4
Check Your Progress 1

1) A Data Table will show you how by changing certain values in your _____ you can affect the
result of your formula.
2) _______ can store the results of many different scenarios for you in one table, so that you can
analyze them to select which scenario is your best option.
3) Data Tables are written as array formulas, which therefore allow them to perform multiple
calculations in a ______ location.
4) There are _____ types of Data Tables, One-variable data tables and two-variable data tables.

5) Describe table with respect to Excel application software.

2.5 CHANGES TABLE FORMATS

Once a format is applied on a range of cells, we can also make changes in the table
formats. Changes can be to change the format or to remove the already applied format

To change the format

I. Select the Table (range of cells)

II. Choose FORMAT, Auto format. The auto format dialog box pops.
III. Scroll through the table format samples
IV. Select the new format to apply
V. Click OK.

To remove the format

I. Select the Table (range of cells)

II. Choose FORMAT, Auto format. The auto format dialog box pops.
III. Scroll to the bottom of the format sample palette
IV. Choose none to remove the format
V. Click OK.

Figure 5 11
2.6 INTRODUCTION TO CHARTS

Charts are used to analyze data graphically. Good created and formatted charts can help people
and businesses make decisions based on the impact that their images provide to the users.
Microsoft Excel is equipped with the Chart Wizard that allows you to create and format a chart to
suit almost any scenario or need.

To create a chart, you must first decide where data is coming from. This is mostly done with
information available on a worksheet. Formatted charts come in various flavors for diverse goals,
ranging from columns to pies, from lines to surfaces, etc.

A chart is usually created on a two-dimension basis, which relates to data on columns and rows
on a worksheet. Overall, once you have decided on the data source, you have a great deal of
freedom on the design process. This means that you control what pieces of information will be
available on the chart, how the chart will be displayed, whether to display the chart on the
originating worksheet or a separate one.

A chart is made of different sections. The main area allows users to view the graphical display of
data, the legend explains the meaning of various colors on the chart, the title indicates what the
chart is used for. When designing one, the charts in Microsoft Excel are organized by types
including columns, bars, lines, pie, etc.

When creating a chart, you will mainly be influenced by the kind of information you intend to
analyze. Data that deals with scaled numbers such as monthly sales or student grades are suitable
for column or bar charts. If your data is about percentage or fractions, you should use pie or
doughnut charts. Microsoft Office ships with Microsoft Map, a special software product that
allows you to create a mapped chart used to analyze data geographically, depending on the
country or a region of the world.

2.7 CREATING AND INSERTING A CHART

“A picture is worth a1000 words.” It is more convenient to see information than to read
it. Visual information is easier to understand patterns, to analyze trends in data and to see
comparisons. For example, it is easier to know the sales trends to make decisions quickly,
than to analyze several columns of data to find the conclusions.

When creating a chart, first find out what the chart will be used for, this allows you to get
necessary data available. Once the originating data is available on a worksheet, select Microsoft
Excel can recognize data format and act appropriately. To create a chart, you will primarily use
the Chart Wizard which will take you step by step in creating the object. To start, select data
intended to be analyzed, then on the Formatting toolbar, click the Chart Wizard button, and
proceed. Charts come in different types for various purposes.

12
About Chart Wizard

The Excel Chart Wizard makes the process of creating charts or graphs a very simple
procedure. There are four steps to the Excel Chart Wizard. These steps contain a number of
options that allow you to pick a chart type, choose your data, and add titles and formatting to
produce a finished chart.

A chart is based on a selection or range of related data. A chart often makes it easier to
understand the data in a worksheet because users can easily pick out patterns and trends
illustrated in the chart that are otherwise difficult to see.

The Four Steps of the Chart Wizard

Chart Type
Choose the type of chart to create such as a pie chart, bar chart, or line chart.

Chart Source Data

Selecting or verifying the data that will be used to create the chart.

Chart Options
Adding titles to the chart and choosing various chart options such as adding labels and a
legend.

Chart Location
Deciding whether to put the chart on the same page as the data or on a separate sheet.

Steps to create a chart

I.Enter data in the sheet

II.Select data range
III.Go to Insert Menu, Click on chart
IV. Select the type of chart
V. Select Data Range
VI. Select the Title of the chart, X-Axis and Y-Axis captions
VII. Select the option to place chart as an object on a new sheet or on any of the
existing sheets.
VIII. Finally click the finish button

13
 Figure 6 Figure 7

Figure 9 Figure 8

14
 Figure 10

Figure 11

Another Method is click on Chart Wizard on Standard Toolbar, as shown below.

Figure 12

15
Check Your Progress 2
Q.NO 1 Why do we use charts?
Q.NO 2 Describe Data Series, Legend and Gridline chart elements.

2.8 MODIFY CHARTS

Once we have made a chart, there are situations when we have to make changes in the
charts. The change may be in data values or in the type and appearance of the chart. The
effect of chart source change is automatically reflected in the chart.

Steps to change the appearance of the titles

I. Click on the chart title, Salary Details

II. As per the need on Click on pattern, font or alignment tabs
III. Select the formatting parameters like border, line style, color etc
IV. Select the font style, font size from font tab
V. Select the alignment details from the alignment tab
VI. Finally click on OK, button.

Figure 13

16
Figure 14

Figure 15

17
Figure 16

Figure 17

18
Another method to format the title of the chart is simply right click on the title and click
on the Format Chart Title.

Figure 18

Steps to Modifying Chart Area

I. Right click on chart area

II. Click on Format Chart Area
III. Select options from pattern, font or properties
IV. Click OK

Figure 19

19
Figure 20

Figure 21

20
Figure 22
Steps to Modifying Plot Area

I. Right click on Plot Area

II. Click on Format Plot Area
III. Select options from patterns tab
IV. Click OK

Figure 23

Figure 24

21
 Figure 25

Steps to Modifying Format Legend

I. Right click on Legend Area

II. Click on Format legend
III. Select options from patterns tab
IV. Click OK

Figure 26

22
 Figure 27

Figure 28

Axis titles can also be modified by, right click on the title and then choose the appropriate
options from the available one.

23
Chart Tool Bar

Chart can be formatted using chart tool bar also. To activate chart tool bar, Right click on
the empty space on the right side of menu bar and choose chart as shown in the figure
below. Individual component highlighted with action associate with icon. See Combined
Figure 29.

Chart Legend By Row By

Objects Column

Angle Counter
Clock Wise

Format
Chart Area Chart Data Angle
Type Table Clock Wise

Figure 29

24
Move and resize chart items with the mouse
Microsoft Excel automatically sizes titles to accommodate their text. You can move titles
with the mouse but you cannot resize them.

I. Click the chart area (chart area: The entire chart and all its elements.), the plot
area (plot area: In a 2-D chart, the area bounded by the axes, including all data
series. In a 3-D chart, the area bounded by the axes, including the data series,
category names, tick-mark labels, and axis titles.), or the legend (legend: A box
that identifies the patterns or colors that are assigned to the data series or
categories in a chart.) you want to move or resize.

II. To move an item, point to the item, and then drag it to another location.

To resize an item, point to a sizing handle. When the pointer changes to a double-
headed arrow, drag the sizing handle until the item is the size you want. By this method
you can also change the size of a chart it self.

How to delete a chart

I. Select the chart

II. Right click on chart area
III. Choose Clear

Second Method

I. Select/Click on chart area

II. Press delete key

2.9 TYPES OF CHARTS

There are different types of charts available in Microsoft Excel.

Column Chart: This type of chart is used to compare values across categories. A column chart
can render a very effective result when analyzing data of the same category on a defined scale.
The column chart emphasizes high and low values. It helps to compare items.’

The classic column chart is made of flat bars that simply illustrate maximal, minimal, and in-
between values. One of the options allows you to create a 3 dimensional look of the chart and
further accentuate the colors and/or other graphic effects. To enhance an effective analysis, you
can create a real 3-D chart that shows data and graphics in perspective.

25
 Figure 30

Line Chart: A Line chart is used to analyze ups and downs of a tendency in a range of values.
You can define it with one series of values where you will judge the evolution of an item over a
period. When used with more than one series, this chart can be very helpful in comparing values
of the same category over the same period. Fortunately, the Line chart can also be used to analyze
values that don't share the same periodic variable. For example, you can use it to compare library
attendance with regard to the real population number (which could be in hundreds of thousands or
millions) with the number of people attending the library. In the latter situation, if the same axis
are used to analyze, one category will almost disappear from the chart; the alternative is to
separate their axis on the same chart.

Figure 31

Pie and Doughnut Charts: This type of chart is used to compare values across categories. The
Pie chart is used to show percentage and/or fraction values. You should choose it whenever your
analysis involves values that altogether evaluate to a 100%, a 1, a 10, a 100, or a 1000 scale.

An alternative to the Pie is the Doughnut chart. Sometimes this one can be used as a better
alternative because it can include more than one series.

26
 Figure 32

Stock : This type of chart is used to compare values across categories

Figure 33

Check Your Progress 3

Q.NO 1 Describe pie and line charts.
Q.NO 2 How to select a different chart type?

27
2.10 SUMMARY
Apart form tables this unit has explained the powerful charting capabilities of Excel. The unit
have covered the different types of professional chart making exercises. The main focus of this
unit is to help the learner understand the use of tables and charts. A learner will learn about the
terminology used with respect to tables and charts to help in modify the same with respect to
appearance and data. After studying this unit, students will be well versed with techniques and
procedures to make and use tables and charts for professional and educational purpose.

2.11 SOLUTIONS / ANSWERS

CHECK YOUR PROGRESS 1
1) Formulas
2) Data Tables
3) Single
4) Two

Answer 5 To make managing and analyzing a group of related data easier, you can turn a range
of cells into a Microsoft Office Excel table (previously known as an Excel list). A table typically
contains related data in a series of worksheet rows and columns that have been formatted as a
table. By using the table features, you can then manage the data in the table rows and columns
independently from the data in other rows and columns on the worksheet.

CHECK YOUR PROGRESS 2

Answer 1:
Rather than using a worksheet to represent data, you can create a chart to represent the same data.
For example, you might want to crate a chart and print the chart and worksheet together for a
presentation. That way your audience can easily see trends ina series of values.

Answer 2:
Data Series:
The bars, pie wedges, or other elements that represent plotted values in a chart. Often, the data
series corresponds to rows of data in your worksheet.
Legend: The elements that designates the separate categories of a chart. For example, the legend
for a column chart shows what each column of the chart represents.
Gridline: The lines that depict the X-axis and y-axis scale of the data series. For example, major
gridlines for the y-axis help you follow a point form the x-or y- axis to identify a data point’s
exact value.

CHECK YOUR PROGRESS 3

Answer 1:

Pie : It plots only one category of data, but each wedge of the pie represents a different data
series. Use this chart to show the relationship among parts of a whole.

28
Line: this chart is used to emphasize trends and the change of values over time, showing how one
or more items have changed over time. Lines emphasize the change, not the comparison of one
item to another. Also, useful for plotting numerous categories of data for multiple data series.

Answer 2:

Excel offers a myriad of chart types for presenting your data. You’ll find that certain chart types
are best for certain situations. To change to a different chart type, select your chart and click the
Chart Type down arrow on the Chart toolbar. A palette of chart types appears. Click any chart
type. Excel transforms your chart into that chart type. Experiment with chart types until you get
the chart that best suits your needs.

2.12 FURTHER READINGS

1. Excel 2000, No Experience Required, Gene Weisskopf, BPB Publication
2. Microsoft Excel 2000 in 24 hours, SAMS, Techmedia
3. A first cours I computrs 2003 Edition, Vikas publications, Sanjay Saxsena
4. Introduction to Computers with MS-Office 2000 by Tata McGraw-Hill Publishing
Company Limited Authors: Alexis Leon and Mathews Leon
5. http://spreadsheets.about.com
6. www.excelself.com
7. http://office.microsoft.com

29