Lab 6 – To work with the MS Excel environment – II SSUET/QR/114

LAB 6
6.1. OBJECTIVE
To work with the MS Excel environment – II

6.2. THEORY
Microsoft Excel is a spreadsheet editor developed by Microsoft. It features calculation or
computation capabilities, graphing tools, pivot tables, and a macro programming language
called Visual Basic for Applications. It allows users to store, organize, and analyze information.
Excel forms part of the Microsoft 365 suite of software.

6.2.1. Working with Data

Excel workbooks are designed to store a lot of information. Whether working with 20 cells or
20,000, Excel has several features to help users organize data and find what is needed.
Summarizing Data
The Subtotal command allows users to quickly summarize
data. It automatically creates groups and enables users to use
common functions like SUM, COUNT, and AVERAGE. Note
that the data must be correctly sorted before using the
Subtotal command.
To use the Subtotal command on a sorted worksheet, select
the Data tab, then click the Subtotal command. In the
Subtotal dialog box, click the drop-down arrow for the At
each change in: field to select the column to subtotal. From
the drop-down list in the Use function field, select the
function to use. In the Add subtotal to: field, select the
column where the calculated subtotal will appear. The
worksheet will be outlined into groups, and the subtotal will
be listed below each group.

CET-103L Computer Fundamentals 29

Lab 6 – To work with the MS Excel environment – II SSUET/QR/114

Conditional Formatting
Conditional formatting allows users to automatically
apply cell formatting such as colors, icons, and data
bars, to one or more cells based on the cell value. It
provides a way to visualize data and make worksheets
easier to understand.
To apply conditional formatting, a conditional
formatting rule must be created. Select the desired cells
for the conditional formatting rule. From the Home
tab, click the Conditional Formatting command. On
the drop-down menu, hover the mouse over the desired
conditional formatting type, then select the desired rule
from the menu that appears. In the dialog box, enter the
desired value(s) in the blank field then select a formatting style from the drop-down menu.

The conditional formatting rule will be applied to the selected cells.

Multiple conditional formatting rules can be applied to a cell range or worksheet. Moreover,
several predefined styles (or presets) can be used to quickly apply conditional formatting to the
data. These fall under three categories.
Data Bars Horizontal bars are added to each cell, much like a bar graph.
Color Scales The color of each cell is changed based on its value. Each color scale uses a
two- or three-color gradient.
Icon Sets Adds a specific icon to each cell based on its value.
Data Validation
Data validation allows the author to control exactly what a user can enter into a cell. To make
things easier, drop-down list of the possible options can be inserted into the cell. This allows
the author to build a powerful, fool-proof spreadsheet. Since users will not have to type in data
manually, the spreadsheet will be faster to use, and there's a much lower chance that someone
can introduce an error.
To apply data validation on a cell, select the cell then click Data Validation on the Data tab.
In the dialog box, set the validation criteria, an input message (to show a tooltip when the cell
is selected), and an error message to show if invalid data is entered by the user. Select OK.

6.2.2. Tables
Data entered in the worksheet can also be formatted as a table. Tables help organize the content
and make the data easier to use. They can improve the look and feel of the workbook. Excel
includes several tools and predefined table styles, allowing quick and easy creation of tables.

CET-103L Computer Fundamentals 30

Lab 6 – To work with the MS Excel environment – II SSUET/QR/114

To format the data as a table, select the cells to be included in the table. Click on Format as
Table on the Home tab. Select a table style from the drop-down menu. A dialog box will
appear, confirming the selected cell range for the table. If the table has headers, check the box
next to My table has headers, then click OK.

Tables include filtering by default. Users can filter the data at any time using the drop-down
arrows in the header cells.
Adding new content into any adjacent row or column will include the row/column in the table.
Table size can also be increased by clicking and dragging the bottom-right corner of the table.

6.2.3. Charts
Charts allow users to illustrate their workbook data graphically, which makes it easy to
visualize comparisons and trends. Excel has several types of charts, allowing users to choose
the one that best fits the data.
Column Use vertical bars to represent data.
Charts Can work with many different types of data but are frequently used for
comparing information.
Line Charts Ideal for showing trends.
The data points are connected with lines making it easy to see whether values
are increasing or decreasing over time.
Pie Charts Useful for comparing proportions.
Each value is shown as a slice of the pie which makes it easy to see which
values make up the percentage of a whole.
Bar Charts Use horizontal bars to represent data.
Work like column charts.
Area Charts Similar to line charts, except that area under the lines are filled in.
Surface Display data across a 3D landscape.
Charts Work best with large sets of data showing a variety of information.

CET-103L Computer Fundamentals 31

Lab 6 – To work with the MS Excel environment – II SSUET/QR/114

Charts contain several elements, or parts, that can help interpret data.
Horizontal Axis Also known as the x axis, it is the horizontal part of the chart that
identifies the categories in the chart.
Vertical Axis Also known as the y axis, it is the vertical part of the chart that measures
the value of the columns.
Data Series It consists of the related data points in a chart.
Legend The legend identifies which data series each color on the chart represents.
Chart Title The title should clearly describe what the chart is illustrating.
To insert a chart, select the cells to
chart, including the column titles
and row labels. These cells will be
the source data for the chart. From
the Insert tab, click the desired
Chart command. Choose the desired chart type from the drop-down menu. The selected chart
will be inserted into the worksheet. Also, if uncertain of the type of chart to use, the
Recommended Charts command will suggest several charts based on the source data.
By default, when more data is added to the spreadsheet, the chart may not include the new data.
To fix this, the data range must be adjusted. Click the chart, and it will highlight the data range
in the spreadsheet. Then click and drag the handle in the lower-right corner to change the data
range.

6.2.4. PivotTables
PivotTables can help make worksheets more
manageable by summarizing data and allowing users
to manipulate it in different ways. They are generally
used for analysis of large amounts of data. A
PivotTable can instantly calculate and summarize the
data in a way that will make it much easier to read.
Creating a PivotTable
To create a PivotTable, select the table or cells
(including column headers) to be included in the
PivotTable. From the Insert tab, click the
PivotTable command. The Create PivotTable dialog
box will appear. Choose the settings, then click OK.
A blank PivotTable and Field List will appear in the selected location. Each field is simply a
column header from the source data. In the PivotTable Fields list, check the box for each field
to include in the PivotTable. The selected fields will be added to one of the four areas below.
The PivotTable will calculate and summarize the selected fields.
The data in the PivotTable can be sorted using the Sort & Filter command on the Home tab.
Moreover, any number formatting may also be on the PivotTable.
Note that any changes in the data in the source worksheet will not automatically update the
PivotTable. To manually update it, select the PivotTable and then go to Analyze > Refresh.

CET-103L Computer Fundamentals 32

Lab 6 – To work with the MS Excel environment – II SSUET/QR/114

Pivoting Data
PivotTables allow for quick pivoting (reorganization) of data, allowing users to examine the
worksheet in several ways. Pivoting data can help answer different questions and even
experiment with data to discover new trends and patterns.
The PivotTable can include multiple rows and columns. To increase/decrease the number of
rows/columns, drag a field from the Fields List into/out of the respective area (rows/columns).

Filters
Filters can be used to narrow down the data in the PivotTable to
show only the information needed. To apply a filter, drag a field
from the Field List to the Filters area. The filter will appear above
the PivotTable. Click the drop-down arrow, then check/uncheck the
box next to any item to include/exclude it from the table. The
PivotTable will adjust to reflect the changes.
Slicers
Slicers are just like filters but are easier and faster to use. Slicers are
used when frequent filtering of the PivotTable is required.
To add a slicer, select any cell in the PivotTable. From the Analyze
tab, click the Insert Slicer command. A dialog box will appear.
Check the box next to the desired field, then click OK. The slicer
will appear next to the PivotTable. Each selected item will be
highlighted in blue.
Just like filters, only selected items are used in the PivotTable. When
an item is selected or deselected, the PivotTable will instantly reflect
the change. Pressing and holding the Ctrl key on the keyboard
allows the user to select multiple items at once.

CET-103L Computer Fundamentals 33

Lab 6 – To work with the MS Excel environment – II SSUET/QR/114

PivotCharts
PivotCharts are like regular charts, except they display data from a PivotTable. Just like regular
charts, users can select a chart type, layout, and style that will best represent the data.
Select any cell in the PivotTable. From the Insert tab, click the PivotChart command. The
Insert Chart dialog box will appear. Select the desired chart type and layout, then click OK.
The PivotChart will appear.
Filters or slicers can be used to narrow down the data in the PivotChart. To view different
subsets of information, change the columns or rows in the PivotTable.

6.2.5. VLOOKUP and HLOOKUP

VLOOKUP and HLOOKUP are functions in Excel that allow users to search a table of data
based on what the user has supplied and give appropriate information from that table.
VLOOKUP allows searching in a table that is set up vertically. All of the data should be set up
in columns and each column is responsible for one kind of data. HLOOKUP is the exact same
function but looks up data that has been formatted by rows instead of columns.
VLOOKUP/HLOOKUP have three arguments in order.
lookup_value User input. This is the value that the function uses to search on.
table_array The area of cells in which the table is located. This includes the entire
range/table from which the data will be extracted. All columns of the data
must be included.
col_index_num The column of data that contains the answer. For HLOOKUP, this will be
row_index_num.
range_lookup A TRUE or FALSE value. When set to TRUE, the lookup function gives
the closest match to the lookup_value without going over the
lookup_value. When set to FALSE, an exact match must be found to the
lookup_value or the function will return #N/A. Note, this requires that the
column containing the lookup_value be formatted in ascending order.
If the leftmost column (top-most row) of the table contains duplicates, the
VLOOKUP/HLOOKUP function matches the first instance only. Also, it performs case-
insensitive lookups.

CET-103L Computer Fundamentals 34

Lab 6 – To work with the MS Excel environment – II SSUET/QR/114

6.3. EXERCISES
6.3.1. Task 01
Perform the following operations in the file creates in Lab 5. Print and attach your result.
1. Find the maximum and minimum marks of English. Then use the fill handle to find the
maximum and minimum marks of all the subjects.
2. Use a formula to automatically assign grades based on the following criteria: A: 90-100%,
B: 80-90%, C: 70-80%, F: < 70%. (Use logical IF).
3. Apply the following conditional formatting to the data: A: Green, B: Blue, C: Yellow, D:
Red.
4. Build a chart with grades on the x-axis and number of students on the y-axis. Chart must
contain title, axis title, and legend.
5. Perform VLOOKUP on your data. Set the roll number as the lookup field. The data returned
should be the percentage.

CET-103L Computer Fundamentals 35