More
on MS Excel 2010
Excel is used
to manage, edit and format data in rows and columns. Apart from that, it
assists the user with calculations and data analysis.
In the previous
lesson, you learnt about the popular spreadsheet software called Microsoft
Excel or MS Excel. It is part of the Microsoft Office package. Each file of MS
Excel is called a workbook. A workbook contains three worksheets by default.
However, more worksheets can be added. Each worksheet is divided into many rows
and columns. The intersection of a row and a column is called a cell.
SELECTION IN A
WORKSHEET
Boot-Click-Enter
In order to
edit or format the contents of a cell in a worksheet, you will have to select
the cell. There are different ways of selecting an individual cell, a range of
cells, a column, a row or an entire worksheet.
>Selecting a
Cell
To select a
single cell in the worksheet, click on the required cell. A thick border will
appear around it. A single selected cell is also called an active cell.
➤ Selecting Adjacent Cells
To select a
range of adjacent cells, follow either of the steps given below:
File
Home
Intert
Page Layout
Formula
Click on the upper-leftmost
cell in the range, hold down the left mouse button and drag to the
lower-rightmost cell in the range. For example, if you have to select a range
from the cell A1 to D6 (A1:D6), click on cell A1 and then holding the left
mouse button, drag and select till cell D6 (Fig. 1).
OR
Click on the
first cell in the range, press the Shift key on the keyboard and click on the
last cell in the range.
Selecting
Non-Adjacent Cells
To select a
number of non-adjacent cells, follow the steps given below:
Step Select the
first cell in the required range of cells.
Step Press Ctrl
key on the keyboard and click on the other cells that are to be selected.
➤ Selecting an Entire Column or Row Click on
the column header or row number to select a single column or row, respectively.
For example, click on letter D in the column header to select column D (Fig.
2). Similarly, click on number 2 in the row header to select row 2.
select multiple
columns or rows, click on the first column header or row number. Press the
Shift key and click the last column header or row number to be selected. For
example, to select columns A to G, first click on the column header A, then
press the Shift key on the keyboard and click on the column header G.
Similarly, to select rows 2 to 8, first click on row number 2, then press the
Shift key and click on row number 8. Cut Cat
Home
Page Layon
La Copy
Jormat Panter
Font
D
Capboard
A1
A
➤ Selecting the Entire Worksheet
To select the
entire worksheet, click on the Select all button at the top left corner of the
worksheet (Fig. 3).
EDITING A WORKSHEET
Editing a
worksheet means modifying, altering or deleting its content. This includes
changing the cell contents, and inserting and deleting cells, rows and columns.
You can also use cut, copy, and paste commands to move or copy the cell
contents
Editing Cell Contents
Boot-Click-Enter
5
Follow the
steps given below to edit or modify the contents of a cell:
Step If you
want to 're-type' or overwrite the contents of the cell, just click on the cell
and type afresh. But, if you want to modify the contents of the cell, press the
F2 key or double-click on the cell and type the changes (Fig. 4).
OR
When you select
a cell, its contents are displayed in the Formula bar. You can then easily edit
the cell contents, whether re-typing or modifying, in the Formula bar,
Use the left
and right arrow keys on the keyboard and edit the contents of the cell.
Step Press the
Enter key or click anywhere in the worksheet to complete the editing in the
active cell. In case you want to cancel the changes, press the Esc key.
Fig. 4: Editing
cell contents
➤ Deleting Contents of a Cell
Select the
cell(s) in the worksheet whose content is to be deleted and follow the steps
given below:
Step Click on
the Home tab.
Step In the
Editing group, click on the drop down arrow next to the Clear button (2 Clear
and select the option Clear Contents.
OR
Right-click on
the area selected and click Clear Contents from the shortcut menu that appears
(Fig. 5).
OR
Select the cell
and simply press the Delete key on the keyboard.
CUT, COPY AND
PASTE COMMANDS
While working
on a worksheet, the contents can be moved or copied from a cell or a range of
cells to some other part of the same worksheet or to another worksheet.
➤ Cut
If you wish to
remove the text from a worksheet and move it within the same worksheet or to
another worksheet, you need to apply the CUT command. Follow the steps given
below to
cut the data
from a cell or a range of cells:
Step Select the
cell or the range of the cells to be cut.
Step Click on
the Home tab.
Step In the
Clipboard group, click on the Cut command (Fig. 6).
➤ Copy
If you wish to
duplicate the cell contents in the same worksheet or in another worksheet, you
need to apply the COPY command. Follow the steps given below to copy the data
in a cell or a range of cells:
Step Select the
cell or the range of cells to be copied.
Step Click on
the Home tab.
Step In the
Clipboard group, click on the Copy command a (Fig. 7).
Do You Know?
UN
You
Un
For
by
com
File
Home
Insert
The
Calibri
the
Paste
BU
Clipboard
Fig. 6: The Cut
command in Clipboard group
File
Home
Insert
Calibri
Paste
BIU
Clipboard
Fig. 7: The Copy
command in Clipboard group
When you choose
Cut or Copy command, the cell(s) are surrounded by a marquee (a flashing dotted
line). The text that has been 'Cut' or 'Copied', should then be posted at its
destination. The cell contents are removed from the original page or copied
when the data gets pasted in the destination cell(s).
INS
To
Ste
Ste
Ste
➤ Paste
Follow the
steps given below to paste the data in a cell or a range of cells:
File
Home
Insert
Step Select the
cell or the range of cells where you want to paste the cut or copied data.
Calibri
Paste
Step Click on
the Home tab.
BIU
Step (Fig. 8).
In the Clipboard group, click on the Paste command
UNDO AND REDO
COMMANDS
Porrf-Click-Enter
You must have
used this command while working in MS Word. As the name indicates, the Undo
feature allows you to cancel the last steps you performed in the worksheet.
For example,
while entering the names of the students, if the record of a student is deleted
by mistake, the deletion can be undone by using the Undo feature. Just click on
the Undo command on the Quick Access Toolbar.
The opposite of
Undo is Redo. To redo the last action, click on the Redo command on the Quick
Access Toolbar.
INSERTING CELLS, ROWS AND COLUMNS
To insert
cells, rows and columns in a worksheet, follow the steps given below:
Step Select the
cell.
Step Click on
the Home tab.
Step In the
Cells group, click on the Insert arrow and select Insert Cells (Fig. 10(a)}.
The Insert dialog box appears, showing four options (Fig. 10(b)).
Insert
OPTION
DESCRIPTION
Shift cells
right
Moves the
selected cell to the right and inserts a new cell
Shift cells
down
Moves down the
selected cell and inserts a new cell
Entire row
Moves down the
entire row containing the selected cell and inserts a new roW
Entire column
Moves the
entire column containing the selected cell to the right and inserts a new
column
Step Select one
of the four options depending on your requirement.
Step Click on
OK to insert cells, rows or columns.
Practice Time
Try to insert a
row and a column in a worksheet.
DELETING CELLS,
ROWS AND COLUMNS
To delete
cells, rows and columns, follow the steps given below:
Step Select the
cell.
Step Click on
the Home tab.
Step In the
Cells group, click on the Delete arrow and then select Delete Cells
OPTION
DESCRIPTION
Shift cells
left
Deletes the
selected cell and moves the cells on the right of the selected cell, towards
left
Shift cells up
Deletes the
selected cell and moves the cells below the selected cell, upwards
Entire row
Deletes the
entire row containing the selected cell
Entire column
Deletes the
entire column containing the selected cell
Step Select one
of the four options depending on your requirement.
Step Click on
OK to delete cells, rows or columns.
RESIZING ROWS
AND COLUMNS
The default
height of a row is 15.00 and default width of a column is 8.43. If the data you
are entering requires a larger size of the cell, its height and width can be
adjusted in Excel as per the requirement.
Do You Know?
Column width is
measured in number of characters and row height is measured in points.
➤ Resizing a Row
Follow the
steps given below to adjust the height of a row:
Step Select any
cell in the row whose height is to be adjusted.
Step Click on
the Home tab.
Step In the
Cells group, click on the Format button. A drop down menu appears (Fig. 12(a)).
Step Click on
Row Height from the Cell Size section. The Row Height dialog box will open
{Fig. 12(b)). Enter the required height value in it
OR
Place the mouse
on the edge of the row divider. The mouse pointer will change automatically to
a double-headed arrow (+). You can drag it to adjust the height of the row
manually (Fig. 12(c)).
➤ Resizing a Column Follow the steps given
below to adjust the width of a column:
Step Select any
cell in the column whose width is to be changed.
Step Click on
the Home tab.
Step In the
Cells group, click on the Format button. A drop down menu appears (Fig. 12(a)}.
Step Click on
Column Width from the Cell Size section. The Column Width dialog box will open
(Fig. 13(a)). Enter the required width value in it.
Step Click on
OK.
OR
Place the mouse
on the edge of the column divider.. The mouse pointer will change automatically
into a double-headed arrow (+). You can drag it to adjust the column width
manually (Fig. 13(b)).
Do You Know?
If the contents
of a cell go beyond the width of the column, you can also double click in
between the two column headings to automatically increase the width. This is
called auto fit column width.
FORMATTING DATA
IN A WORKSHEET
Formatting
implies changing the appearance of text. For example, changing the font, font
size and colour. You will now learn the various ways to format the data in a
worksheet.
➤ Changing the font and its size
Boot-Click-Erter
5
Fonts are
different styles of writing. Follow the steps given below to change the font
and its size:
Step Select the
cell or the range of cells where you want to change the font and its size.
Step Click on
the Home tab.
Step In the
Font group, click on Font. A drop down list of fonts appears (Fig. 14(a)).
Select a font from the list displayed.
Step Click on
the Font size arrow in the Font group. A drop down list of font sizes appears
(Fig. 14(b)). Select the font size from the list displayed.
Calibri
Theme Foom
Cambria
Hendr
Calibri
(Body)
All Fonts
Aachen Stul
Bold
calem En gaed
LET
Ad Lib
+AdLib
Adobe Arabis
أبجد هوز
Fig. 14(a): The
Font drop down list
Fig. 14(b): The
Font size drop down list
Changing the
Font Style
To change the
text to bold, italic or underlined, follow the steps given below:
Step Select the
cell or the range of cells where you wish to change the style.
Step Click on
the Bold, Italic or Underline option in the Font group on the Home tab.
BOLD
B
ITALIC
UNDERLINE
Fig. 15: The
Bold, Italic and Underline options
➤ Changing Alignment
The placement
of the cell contents can be changed with respect to the cell margins. This is
called changing alignment. Excel allows you to change the horizontal and
vertical alignment of the cell contents.
Follow the
steps given below to align the contents of a cell:
Step Select the
cell or the range of cells whose content you want to align.
Step Click on
the Home tab.
Step In the
Alignment group, for horizontal alignment, you can click on any of the left,
centre or right alignment buttons to align your text left, centre or right of
the cell, respectively (Fig. 16).
OR
FOR VERTICAL
ALIGNMENT
FOR HORIZONTAL
ALIGNMENT
Fig. 16: The
Alignment buttons
For vertical
alignment, you can click on any of the top, middle or bottom alignment buttons
to align your text to the top, middle or bottom of the cell, respectively (Fig.
16).
➤ Merge and Center
Two or more
adjacent cells can be merged to create a single cell using the Merge &
Center option. Follow the steps given below to merge and centre-align the data:
Step Select the
range of cells you wish to merge and centre-align.
Step Click on
the Home tab.
Step In the
Alignment group, click on the Merge & Center option (Fig. 17).
Merge &
Center
Home
Insert
Page Layout
Formulas
Data
Review
Calibri
11AA
BU
Font
Alignment
➤ Applying Border
Fig. 17: The
Merge and Center option
Follow the
steps given below to apply a border to a cell or a range of cells:
Step Select the
cell or the range of cells you wish to apply a border to.
Step Click on
the Home tab.
Step In the
Font group, click on the Borders button arrow {Fig. 18(a)}. The Borders list
appears.
Step Click on
the border you wish to apply.
Follow the steps given below to apply a background colour:
Step Select the
cell or the range of cells where you want to add a background colour.
Step Click on
the Home tab.
Step In the
Font group, click on the Fill Color button arrow
(Fig. 19(a))
and
select the
required colour.
Home
Insert
Page Layout
A
Fig. 19(a): The
Fill Color button
➤ AutoFormat
Fig. 19(b): An
example of background colour application
There is a
built-in collection of pre-defined layouts in Excel. These layouts have
coloured backgrounds, borders and attractive text formatting, and can be easily
applied to a range of data. Follow the steps given below to display AutoFormat
button on the Quick Access Toolbar:
Step Click on
the Customize Quick Access Toolbar button on the Quick Access Toolbar. Click on
More Commands option from the list that appears (Fig. 20(a)). The Excel Options
dialog box will open (Fig. 20(b)).
Step Click on the Choose commands from list box arrow and then click on All
Commands from the list that appears (Fig. 20(c)}.
Step Select
AutoFormat option (from the list box on the left side) and click on the Add
button to move AutoFormat option in the list box on the right side (Fig.
20(d)).
Fig. 20(c):
AutoFormat option under All Commands
Fig. 20(d):
AutoFormat option added in the list box
Step Click on
OK. The AutoFormat button will be added on the Quick Access Toolbar.
Follow the
steps given below to use AutoFormat option:
Step Select the
cell or the range of cells where you wish to apply AutoFormat.
Step Click on
the AutoFormat button on the Quick Access Toolbar. The AutoFormat window will
open, displaying a variety of options (Fig. 20(e)).
Step Select the
desired option from the available patterns.
Step Click on
OK to apply the settings.
FORMATTING NUMBERS
Number
formatting implies changing the appearance of numbers without changing their
value such as adding percent symbol (%), comma (,), decimal place and dollar
sign ($). Let us take an example. To display a number 10567 in a cell, say B2
as 10,567.00,
follow the steps
given below:
Select the cell
B2. Step
Format Callis
Sort A
Cell Size
Gas Row Height
AutoPy Count
Defaut wetth
Organics Sheets
nama thest
ore on Cope
theel
Protection
Cer
Protect Sheet
Step Click on
the Home tab.
Step In the
Cells group, click on the Format button and then on Format Cells from the list
that appears (Fig. 21(a)}.
(a)
(b)
The Format
Cells dialog box will appear (Fig. 21(b)).
Step Select the
option Number in the Number tab.
Step Enter 2 in
the Decimal places box. It will display the number with two decimal places.
Step Click in
Use 1000 Separator checkbox to separate numbers in thousands by a comma.
Step Click on
OK to apply settings.
You can see
that the value 10,567.00 is displayed in the cell B2. However, the Formula Bar
still displays the plain number 10567 (Fig. 21(c)). This is because the change
is only in the way the number is represented in the cell, and not in its actual
value. Number formatting can be applied to a cell, a column or a row, a range
of cells or to the entire worksheet.
CREATING SERIES
(AUTOFILL)
If the values
that you enter in one or two adjacent cells match an existing entry or follow a
pattern, Excel will automatically enter the remaining characters of the series
for you. This feature of Excel is called AutoFill.
For example, to
insert the list of months in a year, you can follow the steps given below:
Step Enter
January in cell A1.
Step Place the
mouse pointer on bottom right corner of the cell. It will change to a plus sign
(+).
Step Click and
drag the mouse to cell A12.
Step Release
the mouse when you reach cell A12. The months February to December will be
displayed in the cells (Fig. 22).
➤ Using AutoSum Feature
You can use the
AutoSum button in Excel to quickly sum the values in a row or a column in an
Excel worksheet. The AutoSum button is located in the Editing group of the Home
tab (Fig. 23(a)).
Let us learn
how to use it with the help of an example:
Step Create a
worksheet as shown in the first table of figure 23(b).
Step Click in
the cell in which the sum is to be placed (B8 in this case).
Step Click on
the AutoSum button.
Step Press the
Enter key and see the result Do You Know?
Fig. 23(b): An
example of AutoSum application
You can also
use AutoFill tool to calculate columnwise sum.
The Right Way
Do not use too
many font styles in a single worksheet. It may distract you from reading the
contents. Use colours wherever necessary. Take a coloured printout of your
worksheet only if it is required otherwise it will be a waste of paper as well
as the cartridge/ink of your printer.
Let Us Recall
Editing a
worksheet means modifying, altering or deleting its content.
Formatting
gives a more professional look to the document and makes it more presentable.
The contents of
a worksheet can be copied and moved in the same worksheet or to some other
worksheet.
AutoFormat is a
built-in collection of pre-defined layouts.
Number
formatting implies changing the representation of a number such as in the form
of decimals, currency and so on.
AutoSum tool is
used to quickly sum the values of a row or a column.
Exercises
A. Tick (✔)
the correct option.
|
1.
Which command reverses the last action performed? |
(a) Undo |
|
2.
Which shortcut key will you use to select the whole worksheet? |
(c) Ctrl + A |
|
3.
Which command will you use to bring the copied cells to another location? |
(b) Paste |
|
4.
Which feature formats a worksheet in a preset format? |
(b) AutoFormat |
|
5.
To edit a cell, which key would you press from the keyboard? |
(b) F2 |
B. Fill in the blanks.
1. When the
mouse is placed over the row header,____ double-headed arrow
______ appears.
2. Undo is used
to____reverse the last action performed._______
3. is the
button_________.
4. The
series-based data is entered in the worksheet with the help of____AutoFill._____.
C. Tick (✔)
the correct statements and cross (X) the incorrect ones.
I.
✔ A row or a column can be resized in
Excel.
II.
❌ The height of a row and the width of a
column can be changed with the help of a mouse only. (They can also be changed
using the Format Cells dialog box.)
III.
✔ Cut and Paste commands are used to move
entries from one worksheet to the other.
IV.
✔ One can complete a series in Excel if
its first few members are given.
V.
✔ Click the first and last row number
along with Shift key to select multiple rows.
D. Answer the following questions.
1. Write the steps involved in using the
cut, copy and paste commands in Excel.
Steps involved in using the Cut, Copy, and Paste commands:
Cut:
Select the cell(s) or range of cells you want to cut.
Click the "Cut" button on the Home tab in the Clipboard group.
Copy:
Select the cell(s) or range of cells you want to copy.
Click the "Copy" button on the Home tab in the Clipboard
group.
Paste:
Select the destination cell(s) where you want to paste the cut or copied
data.
Click the "Paste" button on the Home tab in the Clipboard
group.
2. Why do we need the Undo and Redo
features while working on a worksheet?
The Undo and Redo features are essential for error correction and
efficient workflow in Excel.
Undo: This feature
allows you to reverse the last action you performed. If you accidentally delete
a cell, format a cell incorrectly, or make any other mistake, you can simply
undo it.
Redo: This feature
allows you to repeat the last undone action. If you accidentally undo a
necessary change, you can redo it.
3. Describe the role of formatting a
worksheet and the steps involved in the following:
Role
of formatting a worksheet and steps involved:
Formatting a worksheet enhances its readability, visual appeal, and
overall professional look. Here are the steps for some common formatting tasks:
a) Changing the Font and its Size:
Select the cells you want to format.
Click the desired font name and font size from the Font group on the
Home tab.
b) Changing the Font Style:
Select the cells you want to format.
Click the Bold, Italic, or Underline buttons in the Font group on the
Home tab to apply the desired style.
c) Shift cells up:
Select the cell(s) you want to delete.
Click the "Delete" button on the Home tab in the Cells group.
Choose "Shift cells up" from the options.
d) Delete a row:
Select the entire row you want to delete.
Click the "Delete" button on the Home tab in the Cells group.
Choose "Entire row" from the options.
4. How will you select a
number of non-adjacent cells?
· Select the first cell or range of cells.
· Hold down the Ctrl key and select the other
cells or ranges of cells you want to include.
5. What is the purpose of the AutoFill
feature available in Excel?
The AutoFill feature in
Excel allows you to quickly fill a range of cells with data based on patterns
or trends. This is useful for:
Completing
number series (e.g., 1, 2, 3, ...)
Filling
dates or days of the week
Copying
formulas to other cells
Automating
repetitive tasks
Comments
Post a Comment