I YEAR - ICT - UNIT 5
Unit V: Excel
Overview of Excel features – Creating a new worksheet, Selecting cells, Entering and
editing Text, Numbers, Formulae, Referencing cells – Inserting Rows/Columns – Changing column widths and row heights, auto format, changing font sizes, colors,
shading,
Charts –Mathematical,Statistical,and String
Functions
Q)
What do you mean by Excel?
Explain the features of Excel.
ANS: MS-Excel
is a Windows based application package. It is quite useful in entering,
editing, analysis and storing of data. Arithmetic operations with numerical
data such as addition, subtraction, multiplication and division can also be
done with Excel. You can sort the numbers/characters according to some given criteria
(like ascending, descending etc.)and solve simple financial, mathematical and
statistical formulas.
There are a number of features that are
available in Excel to make your task easier. Some of the main features are:
- AutoFormat -
lets you to choose many preset table formatting options.
- AutoSum -
helps you to add the contents of a cluster of adjacent cells.
- List AutoFill
- automatically extends cell formatting when a new item is added to the
end of a list.
- AutoFill
- feature allows you to quickly fill cells with repetitive or sequential
data such as chronological dates or numbers, and repeated text. AutoFill
can also be used to copy functions. You can also alter text and numbers
with this feature.
- AutoShapes
toolbar will allow you to draw a number of geometrical shapes, arrows,
flowchart elements, stars and more. With these shapes you can draw your
own graphs.
- Wizard
- guides you to work effectively while you work by displaying various
helpful tips and techniques based on what you are doing.
- Drag and Drop
- feature will help you to reposition the data and text by simply dragging
the data with the help of mouse.
- Charts
- features will help you in presenting a graphical representation of your
data in the form of Pie, Bar, Line charts and more.
- PivotTable
- flips and sums data in seconds and allows you to perform data analysis
and generating reports like periodic financial statements, statistical
reports, etc. You can also analyse complex data relationships graphically.
- Shortcut Menus
- commands that are appropriate to the task that you are doing appear by
clicking the right mouse button.
Q)
Describe the terms a) Microsoft Excel b) Worksheet c) Sheet d) Cell e) Active Cell
f) Cell Range
a) Microsoft Excel:
Microsoft Excel is an electronic spreadsheet program which is used to
enter, calculate, analyze and represent data in different format. It has basic
and some advance features of spreadsheet for manipulation of data such as
arithmetic and logical operation. It comes with large number of functions for
finding answer to statistical, mathematics and financial questions. Besides, it
has the ability to display data in the form of graphs, charts and
histograms.
b) Worksheet:
Microsoft Excel document is known as workbook; by default a workbook
contains three sheets. But can insert as many sheets as the computer memory
become full.
c) Sheet:
A sheet is just like stack of pages. Actually, sheet is a grid of rows
and columns. The horizontal lines are known as rows while vertical lines are
called columns. The row starts with letter A and column starts with number 1.
d) Cell:
A cell is where a row and a
column meet. Each cell has a particular address depending on the row and column
number. The cell’s address is shown in the name box and can be used in any
function.
e) Active cell:
The active cell is the cell which is currently selected. An active
cell is indicated by a thick border around it. The contents of the active cell
are displayed in the formula bar.
f) Cell range:
A group of selected cells is called a cell range. For example A1:B4 is
a cell range which consists all cells ranging from A1 to B4. A cell range is usually used in a function.
Q)
Describe the parts of MS- Excel Window. (OR) Explain the interface of Microsoft
Excel.
1. Quick
Access Bar: It allows us to access common command quickly. By default it
has Save, Undo, Redo/Repeat command; however one can add more commands by
customizing it.
2. Ribbon:
The ribbon is center for all commands, which we require to accomplish
different tasks in the MS-Excel. The ribbon is divided into different tabs;
there are seven tabs in Microsoft Excel 2007. Each tab is further categorized
into groups of related commands.
3. Group: A group contains a number of related commands
for example Font group in Home tab contains all commands about font.
4. Name
Box: The Name Box displays the current cell’s address or cell range.
5. Scroll
Bar: Basically there are two scrollbars i.e. vertical scrollbar for top and
bottom scrolling and horizontal scroll bar for left and right scrolling.
6. Zoom
Control: Zoom Control is used to zoom in or zoom out sheet.
Q) What
are the various kinds of data we can enter in excel.
You
can enter various kinds of data in a cell.
1. Numbers: Your
numbers can be from the entire range of numeric values: whole numbers (example,
25), decimals (example, 25.67) and scientific notation (example, 0.2567E+2).
Excel displays scientific notation automatically if you enter a number that is
too long to be viewed in its entirety in a cell. You may also see number signs
(# # # # # #) when a cell entry is too long.
2. Text: First select the cell
in which data has to be entered and type the text. Press ENTER key to finish
your text entry. The text will be displayed in the active cell as well as in
the Formula bar. If you have numbers to be treated as text use an apostrophe (‘)
as the first character. You cannot do calculations with these kind of data
entry.
3. Date and Time: When
you enter dates and times, Excel converts these entries into serial numbers and
kept as background information. However, the dates and times will be displayed
to you on the worksheet in a format opted by you.
4. Data in Series: You
can fill a range of cells either with the same value or with a series of values
with the help of AutoFill.
Q) Write a procedure to edit the data in worksheet.
Editing
your Excel worksheet data is very easy. You can edit your data by any of the
following ways:
1. Select
the cell containing data to be edited. Press F2. Use Backspace key and erase
the wrong entry. Retype the correct entry.
2. Select
the cell and simply retype the correct entry.
3. If
you want only to clear the contents of the cell, select the cell and press
Delete key.
4. To
bring back the previous entry, either click on Undo button on standard Toolbar
or select Edit→ Undo command or use keyboard
shortcuts CTRL+Z.
Q) Explain the number
formatting in Excel.
Formatting Numbers: Excel
provides preset number formats to help you standardize how numbers will appear
in your worksheet.
Formatting numbers can be done in the following ways. These are
a)
Formatting
Numbers: Toolbar Option
Formatting
Numbers: Toolbar Option
b)
Formatting
Numbers: Dialog Box Option
c)
Clearing
Number Formatting
When you want to format numbers quickly, Excel
allows you to do so from the Ribbon.
- Select
the cell(s) you want to format
- From
the Home command
tab, in the Number group,
click the desired toolbar option
|
Name
|
Image
|
Description
|
|
Number Format
|
 |
Displays the formatting style of the selected cell
|
|
Accounting Number Format
|
 |
Changes the formatting to Accounting
|
|
Percentage Style
|
 |
Changes the formatting to Percentage
|
|
Comma Style
|
 |
Changes the formatting to include commas and two decimal places
|
|
Increase Decimal
|
 |
Adds one decimal place to the selected cell
|
|
Decrease Decimal
|
 |
Removes one decimal place from the selected cell
|
|
Format Cells: Number
|
 |
Accesses the Format Cells dialog
box
|
The Format
Cells dialog
box can help you customize your number formatting.
- Select
the cell(s) you want to format
- In
the Home command
tab, in the Number group,
click FORMAT
CELLS: NUMBER
The Format Cells dialog box appears with the Number tab displayed. - From
the Category list,
select the desired number format
HINT:You can preview the formatting in the Sample section.
EXAMPLE: Select Currency. - If
the format offers additional options, select the preferred options
EXAMPLE: Format the number of decimal places, the desired symbol, and negative numbers.
- Click OK.
The selected cells are formatted.
The General number format is the
default selection. Changing the formatting to General will remove all other
number formatting for the selected cells.
- Select
the cell(s) you want to format
- From the Home command
tab, in the Number group,
click NUMBER
FORMAT
» select General.
Q)
What are the different cell references available in Excel? Explain it.
Cell Reference
To identify the location of a cell, a reference is given to a cell. It
is also referred to as Cell Address. A cell reference or cell address consists
of the column letter and row number that intersect at the cell's location.
Eg. a) The cell in the first column and first row is referred as ‘A1’
b) The cell in the 5th column and 10th row is referred as ‘E10’
c) The cell in the 27th column and 45th row is referred as ‘AA45’
Relative, Absolute and Mixed
Cell Reference
Normally, when a formula or function from one
cell is copied to another, the references given in the formula or function
automatically changes to suit the new locations. For example in the cell C1, we
have given a formula as ‘=A1+B1’ to add values in A1 and B1. If we copy the
formula to C2, the formula is converted as ‘=A2+B2 ’. This type of reference is
called Relative Reference.
Unlike relative references, absolute references do not change
when copied to another location. You can use an absolute reference to keep a
row and/or column constant. An absolute reference is designated in a formula by
the addition of a dollar sign ($). It can be assigned to the column
reference,the row reference, or both. Eg. The formula =$A$1+$B$1 will not
change its references if we copy the formula to any part of the worksheet.
If we assign the constant reference either for
column or for row, it is called Mixed Reference. Egg. The cell reference
$A1, makes the column A constant, but the row 1 changes according to the new
location. In the same way the reference A$1, makes the row constant, but the
column reference relative.
Q) What do you mean by formula? Explain how to
enter formula and edit formula in excel.
Formula: A formula is an equation
which lets you perform some calculation in worksheet. A formula always starts
with = sign. For example to multiply 4 by 8, =4*8 formula is used. A formula can be entered directly into cell or
with help of formula bar. While creating a formula we can use constant values
or cells reference.
Entering formula with help of formula bar:
1.
Click on any cell you want to enter formula.
2.
Click inside the formula bar.
3. Type
equal sign (=).
4.
Enter formula e.g. 5^2. Complete formula will look like =5^2
5.
Press Enter. The square value of 5 will be displayed in the cell.
Entering
formula directly into cell:
1.
Click on any cell you want to enter formula.
2. Type
equal sign (=).
3.
Enter formula e.g. 5*2. Complete formula will look like =5*2
4.
Press Enter. The result of 5*2 i.e. 10 will displayed in the cell.
Arithmetic
and Logical Formulas
Arithmetic formula
contains normal operators for performing basic mathematical operations. These
include plus (+), minus ( - ), multiply (*) and divide ( / ). Using these
operators we can add, subtract, multiply and divide constant values or cells.
Logical formula
contains logical operator such as >(greater than),< (less
than),=>(greater than or equal) ,=<(less than or equal) for performing logical operations i.e. to
compare two values whether one of them is greater, smaller or equal. The result
of logical formula is always True or false.
Creating an arithmetic
formula:
1.
Click on any cell you want to enter formula.
2.
Type equal sign (=).
3.
Enter formula e.g. 63+98 or type cell address.
You can also select cell with mouse or keyboard after you have typed =.
Complete formula will look like =63+98
4.
Press Enter. The result of 63+98 i.e. 161 will
displayed in the cell.
Creating
a logical formula:
1.
Click on any cell you want to enter formula.
2.
Type equal sign (=).
3.
Enter formula e.g. 342>452 or type cell
address. You can also select cell with mouse or keyboard after you have typed
=. Complete formula will look like =342>452
4.
Press Enter. The Boolean value (True or False)
of 342>452 i.e. false will displayed in the cell.
Q) What do you mean by operator? Explain
Mathematical and logical operators in excel.
Operators: An operator is a special symbol that tells a programme
what action to take on a series of numbers. There are two kinds of operators:
(a) mathematical operators and (b) comparison or logical operators.
(a)
Mathematical Operators: We use these operators to add, subtract,
multiply, and divide numbers. The following are the 5 mathematical operators.
(b)
Comparison Operators: These
operators are used to compare one value to the other. These operators are also
called logical operators because the resulted answer in the cell is always
either True or False.
The following are the comparison
operators.
Q) Write a note on Fill and
Auto fill in Excel.
Fill: Fill command allows filling data automatically
into cells. It can be used to continue a number series, dates series, time
series according to the pattern you have already applied. In Excel, there is a fill handle feature 
which
can be dragged to quickly fill many kinds of data series.
which
can be dragged to quickly fill many kinds of data series.
Fill data automatically into adjacent cells
1. Make
selection around the cells which contains the data, you want to fill into
adjacent cells.
2. Drag
the fill handle crosswise those cells which you want to fill.
3. Release
the mouse button.
Fill in a series of numbers or dates
A series is collection of numbers which follows
a specific pattern. For example 2,4,6,8,10,…, is series of numbers.
1. Enter
the starting value of the series in the first cell. e.g. Enter 2.
2. Enter
a value in the next cell to make a pattern. e.g. Enter 4
3. Select
both cells i.e. first cell and next cell.
4. Drag
the handle crosswise the cells you want to fill with series.
AutoFill
AutoFill option appears after the fill handle is
dragged; it allows choosing how the selection is filled e.g. to fill only the
cell’s formatting choose Fill Formatting Only. In similar way to fill with only
contents then choose Fill without formatting. AutoFill option can be turned ON
or OFF.
Fill Formatting Only:
1. Drag
the handle crosswise the cells you want to fill with series.
2.
Click On option which appears after you
drag the fill handle.
3.
Choose Fill Formatting Only. This will fill only formatting.
Q) Explain how to
create custom list and delete custom list.
Create a custom list: There are two ways to create a custom list. If your
custom list is short, you can type the values directly in the dialog box. If
your custom list is long, you can import it from a range of cells.
Create a
custom list by typing in values
1.
Click the Microsoft Office Button , and then click Excel Options.
2.
Click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists.
3.
In the Custom Lists box, click NEW LIST, and then type the entries in the List entries box, beginning with the first entry.
Press ENTER after each entry.
4.
When the list
is complete, click Add.
The items in the list that you selected are
added to the Custom
lists box.
5.
Click OK twice.
|
A
|
|
|
1
|
High
|
|
2
|
Medium
|
|
3
|
Low
|
Create a
custom list from a cell range
1.
In a range of
cells, enter the values that you want to sort or fill by, in the order that you
want them, from top to bottom. For example:
2.
Select the
range that you just typed. In the preceding example , you would select cells
A1:A3.
3.
Click the Microsoft Office Button, click Excel Options, click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists.
4.
In the Custom Lists dialog box, verify that the cell reference
of the list of items that you selected is displayed in the Import list from cells box, and then click Import.
5.
The items in
the list that you selected are added to the Custom Lists box.
6.
Click OK twice.
NOTE: You can only create a custom list based on a value
(text, number, and date or time). You cannot create a custom list based on a
format (cell color, font color, and icon).
1. Click the Microsoft Office Button, and then click Excel Options.
2. Click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists.
3. In the Custom Lists box, select the list that you want to delete, and then
click Delete.
Q) How to change font and font sizes.
To make a worksheet look more professional and
presentable, various attributes of Excel related to formatting can be used.
Formatting can be applied by altering the appearance of text like changing the
font, font size and font color. Formatting can be applied to any type of data
in the worksheet such as number, text, date and so on.
Changing Font and Font Size
To change the font and font size of the data,
follow these steps.
1.
Select
a cell, a range of cells, a row or a column for which the font and the font
size are to be changed.
2.
Click
the down arrow next to the Font drop-down list box ( 
) in the Font group on the Home tab. Select the required font
from the list displayed.
) in the Font group on the Home tab. Select the required font
from the list displayed.
3.
Click
the down arrow next to the Font Size drop-down list box (
) in the Font group. Select the required font size from the
list displayed.
) in the Font group. Select the required font size from the
list displayed.
Changing Font
Style
To make the selected text bold, italic or underlined, follow these
steps.
1.
Select
a cell or a range of cells in which you want to change the style.
2.
Click
the Bold
(
), Italic (
) or Underline button (
) in the Font group on the Home tab.
), Italic ( ) or Underline button ( ) in the Font group on the Home tab.
Q) How to
change alignment.
Alignment allows you to change the placement of the cell
contents with respect to the cell margins. Excel provides several types of
alignment namely, left, centre and right. The left alignment aligns the data to
the left of the cell. The centre alignment aligns the data to the centre of
the cell. The right
alignment aligns the data to the right of the cell. To align the
data, follow these steps.
1.
Select
a cell or a range of cells.
2.
Click
the Align Text
Left button (
) or Center
button (
) or Align Text Right button (
) in the Alignment group on the Home tab.
) or Center
button ( ) or Align Text Right button ( ) in the Alignment group on the Home tab.
You can also merge two or more cells by using Merge & Center option.
Merge &
Center combines two or more selected adjacent cells to create a
single cell. Also, it aligns the data to the centre of the merged cell. To
merge and centre align the data, follow these steps.
1.
Select
the range of cells that you want to merge and centre align.
2.
Click
the Merge &
Center button (
) in the Alignment group on the Home tab.
) in the Alignment group on the Home tab.
Q) How to
apply border
To apply a border in the worksheet, follow these steps.
1.
Select
a cell or a range of cells to which you want to apply a border.
2.
Click
the drop-down arrow next to the Border button (
) in the Font group and select the required type of border.
) in the Font group and select the required type of border.
Adding
Background Colour
To add a background colour to the cells, follow these steps.
1.
Select
a cell or a range of cells to which you want to add a background colour.
2.
Click
the drop-down arrow next to the Fill Color button (
) in the Font group and select the required fill colour.
) in the Font group and select the required fill colour.
Q) Write a
note on AutoFormat
AutoFormat is a built-in collection of pre-defined layouts
that can be easily applied to a range of data. These layouts do not change or
alter your data; they only apply coloured backgrounds, rearrange borders and
apply attractive text formatting. This feature of Excel increases the
readability of the data in the worksheet. The button for AutoFormat is not
available in any of the tabs on the ribbon. To display AutoFormat button on the
Quick Access Toolbar, follow these steps.
1.
Click
the Customize
Quick Access Toolbar button ( 
) on the
Quick Access Toolbar and then click More Commands
option from the list that appears. The Excel Options dialog box appears.
) on the
Quick Access Toolbar and then click More Commands
option from the list that appears. The Excel Options dialog box appears.
2.
Click
the drop-down arrow on the Choose commands from
list box and then click All commands from the list that appears.
3.
Select
AutoFormat
option from the list box on the left side and then click the Add button to move AutoFormat
option in the list box on the right side.
4.
Click
the OK
button. The AutoFormat
button will
be added in the Quick Access Toolbar.
To use AutoFormat, follow these steps.
1.
Select
the range of cells to which AutoFormat is to be applied.
2.
Click
the AutoFormat
button on the Quick Access Toolbar. The AutoFormat dialog box appears.
3.
Select
the desired option from the available patterns.
4.
Click
OK to
apply the settings.
Q) How to change the width and height of coloumn and row in
excel.
There are several methods to change the
column widths and row heights.
To change the column widths, do one of the following. You then may need to
follow another instruction given to acquire the desired look.
§
Double-click the column border. This
automatically sizes the column to fit the widest data it contains.
§
Drag the column border. When you point to the
border between two column headings, the pointer takes the shape of a two-headed
arrow. You can then drag the border to the left or right to decrease or
increase the size of the column.
§
Set a specific column size in characters. -
Excel 2003: From the Format menu choose Column, slide right to Width. Type a
size and click OK. - Excel 2007: On the Home tab, Cells group, choose Format,
choose Column Width. Type a size and click OK.
To change the row height, do one of the following. You then may need to
follow another instruction given to acquire the desired look. Row heights will
change automatically to accommodate the size of the data in a given row.
§
Double-click the row border. This
automatically sizes the column to fit the highest data it contains.
§
Drag the row border. When you point to the
border between two row headings, the pointer takes the shape of a two-headed
arrow. You can then drag the border up or down to increase or decrease the size
of the column.
§
Set a specific row size in characters. -
Excel 2003: From the Format menu choose Row, slide right to Height. Type a size
and click OK. - Excel 2007: On the Home tab, Cells group, choose Format, choose
Row Height. Type a size and click OK.
Q) Explain the parts of a function.
Meaning: A function is a predefined
formula that
performs calculations using specific values in a particular order.
The
parts of a function:
Each
function has a specific order, called syntax,
which must be strictly followed for the function to work correctly.
Syntax
order:
1.
All functions begin with the = sign.
2.
After the = sign, define the function name (e.g., Sum).
3.
Then there will be an argument.
An argument is the cell range or cell references that are enclosed by
parentheses. If there is more than one argument, separate each by a comma.
An example
of a function with one argument that adds a range of cells, A3 through A9: 
An example
of a function with more than one argument that calculates the sum of two cell
ranges: 
Q) Explain different types of functions in excel.
A function is a pre-set formula
which can be written directly into a cell, to display an outcome. Excel comes
with pre-loaded functions. The syntax always includes an initial operator and a
pair of round brackets which enclose the arguments of the function. Eg : Sum(
), Avg( ), Max( )
Excel's
different functions
There are
many different functions in Excel 2007. Some of the more common functions
include:
1. Date and Time Function
2. Mathematical Function
3. Text Manipulation Function
4. Logical Functions
5. Lookup and Reference Function
6. Financial Function
Mathematical
Function
1)
SUM( ): The SUM
function adds together a supplied set of numbers or numbers in a given range
and returns the sum of these values.
|
Syntax
|
=SUM(Number1, Number2……Number255)
|
2)
SUMIF( ): SUMIF function adds all numbers in a range
of cells, only if it meets the given criteria
|
Syntax
|
=SUMIF(range, criteria, [sum_range])
|
3)
ROUND( ): The ROUND function
rounds a number to a specified number of digits following normal rounding
rules, i.e.; round down if the decimal portion is < 5, and round up if the
decimal portion is ≥ 5.
|
Syntax
|
=Round (number, num_digits)
|
4)
COUNT( ): The COUNT function will count cells that
contain numbers or count the numbers given in the arguments separated by commas
|
Syntax
|
=Count (Value1, Value2, Value3…..)
|
Some of the mathematical functions
are as follows:
|
Functions
|
What it Does
|
|
SUM
|
Adds its arguments
|
|
SUMPRODUCT
|
The most powerful and useful
function in Excel
|
|
ROUND
|
Rounds a number to a
specified number of digits
|
|
ROUNDUP
|
Rounds a number up, away
from zero
|
|
SUBTOTAL
|
Returns a subtotal of a
filtered list or database)
|
|
TRUNC
|
Truncates a number to an
integer
|
|
INT
|
Rounds a number down to the
nearest integer)
|
|
ABS
|
Returns the absolute value
of a number
|
|
MOD
|
Returns the remainder from
division
|
|
POWER
|
Returns the result of a
number raised to a power
|
|
SQRT
|
Returns a positive square
root
|
String Function
The functions have been grouped by category, to help you to
find the function you need. Each function link provides a full description of
the function, with examples of use and common errors.
|
Converts all
characters in a supplied text string to lower case
|
|
|
Converts all
characters in a supplied text string to proper case (i.e. letters that do not
follow another letter are upper case and all other characters are lower case)
|
|
|
Converts all
characters in a supplied text string to upper case
|
|
|
Joins together
two or more text strings (New in Excel 2016 - replaces the Concatenate function)
|
|
|
Joins together
two or more text strings
|
|
|
Returns a
specified number of characters from the start of a supplied text string
|
|
|
Returns a
specified number of characters from the middle of a supplied text string
|
|
|
Returns the
length of a supplied text string
|
|
|
Returns the
character that corresponds to a supplied numeric value
|
|
|
Returns the
numeric code for the first character of a supplied string
|
|
|
Returns the
Unicode character that is referenced by the given numeric value (New in Excel
2013)
|
|
|
Returns the
number (code point) corresponding to the first character of a supplied text
string
|
|
|
Replaces all or
part of a text string with another string (from a user supplied position)
|
|
|
Substitutes all
occurrences of a search text string, within an original text string, with the
supplied replacement text
|
Statistical Functions:
Excel provides an extensive range of Statistical
Functions, that perform calculations from basic mean, median & mode to the
more complex statistical distribution and probability tests.
|
Returns the
number of numerical values in a supplied set of cells or values
|
|
|
Returns the
number of non-blanks in a supplied set of cells or values
|
|
|
Returns the
largest value from a list of supplied numbers
|
|
|
Returns the
smallest value from a list of supplied numbers
|
|
|
Returns the
statistical rank of a given value, within a supplied array of values
|
|
|
Returns the
Average of a list of supplied numbers
|
|
|
Returns the
Median (the middle value) of a list of supplied numbers
|
|
|
Returns the Mode
(the most frequently occurring value) of a list of supplied numbers
|
Comments
Post a Comment