MICROSOFT EXCEL 2000

INTRODUCTION

Microsoft Excel is a member of the Microsoft Office group of applications. Using

this powerful application software, you may create spreadsheets consisting of many kinds of
inbuilt formulae as well as logic functions.

Starting Excel. Once installed, MS Excel can be started in a number of ways. You
may select the Microsoft Excel shortcut from the Programs Menu, Click on its icon in the
Microsoft Office Shortcut Bar or Double click on an existing MS Excel Workbook in the
Windows Explorer.

TIP You may place a shortcut of MS Excel in the Startup folder in the Programs menu to
start MS Excel automatically every time when you start Windows.

MS EXCEL WINDOW

Cell Name Box Title Bar Menu Bar Tool Bar Formula Bar Control Buttons

Row Selectors Status Bar Work Area Scroll Bars

 -2-

Title Bar :- The title bar is placed at the top of the program window and indicates
the names of the program and file in use. It also changes its fill colour to the windows
default settings when activated or deactivated. By the help of the Title bar, you can
identify the active window as well as the program name.

Menu Bar. Menu bar contains the inbuilt menus of Microsoft Excel. The common
available menus are,

 File
This menu contains commands, which will directly apply to the
workbook. This means that the commands available in File menu are
not for a cell, column or rows in a worksheet. Keep it in mind that
when you going to perform an action to the entire workbook, you may
refer to the File menu for that command.

TIP To activate File menu, click on its name or press Alt+F

 Edit
Edit menu is to perform the Edit actions in a workbook. Most of the
edit commands are available in this menu. You may take assistance of
this menu, when you perform editing in a workbook.

TIP To activate Edit menu, click on its name or press Alt+E

 View
View menu is to hold the commands related to display of workbooks.
Many of the screen contents like toolbars, status bar, formula bar etc
can be hide or display using this menu. However, view menu will only
change the display to your convenient level. The settings will not
extend to the existing data or information.

TIP To activate View menu, click on its name or press Alt+V

 Insert
When you are required to insert some object in to your workbook, you
may take assistance of this menu. Most of the insert commands are
available in Insert Menu.

TIP To activate Insert menu, click on its name or press Alt+I

 Format
Most of the formatting commands are available in Format menu. Using
this menu, you could format a column, row or cell in a workbook.

TIP To activate Format menu, click on its name or press Alt+O

 -3-
 Tools
You may customize your workbook as well as the Excel by using the
commands in the Tools menu. Commands like Protect workbook,
Auto correct, macros, customize, options etc are available in Tools
menu.

TIP To activate Tools menu, click on its name or press Alt+T

 Data
When you think to use your workbook as a data bank or data source,
you may find the suitable commands in the Data menu. Commands
like Sort, Filter, Form etc are in this menu.

TIP To activate Data menu, click on its name or press Alt+D

 Window

This menu assists you to deal with the Workbook window. You can
switch between opened workbook windows using this menu. The
commands like Freeze, Split windows etc are available in this menu.

TIP To activate Window menu, click on its name or press Alt+W

 Help

MS Excel has inbuilt help feature. When you face some problems with
MS Excel, you may go for help using the Help menu. The office
assistant can be found in this menu. You may take assistance of this
feature to use the help feature perfectly.

TIP To activate Help menu, click on its name or press Alt+H

TIP When you start to create or edit a chart, the Data Menu will be replaced with Chart
Menu. By the help of Menu bar, you can open each menu and view its commands.
Each Menu will have an underlined character, which will let you to drop down that
menu by pressing the underlined character with ALT key.

Toolbars. Toolbars will normally appear just below the Menu Bar. Buttons on the
toolbars are graphical representation to a Menu Command. You may access and perform a
quick menu command using the equivalent button in the toolbar. The common used toolbars
are Standard and Formatting toolbars.

You may view or hide toolbars using the Toolbar command in the View menu of the
program. To view a toolbar, activate the View menu and select the toolbar under Toolbars
submenu. The selected toolbar will be displayed in the Window. In the same way you can
click on the name of a toolbar to hide it from the screen.
 -4-

The toolbars can be set anywhere in the Application’s window. However, the default
location of the toolbars is Top of the window. If you wish to move a toolbar to any other
location, simply Click and Drag it at its extreme left corner to the new location. The
appearance of the toolbar may change accordingly.

TIP You may right click on any displayed toolbar or menu bar and can select the name of
a toolbar from the context menu to hide or unhide it from the screen.

More Buttons Feature. There is a new feature available in MS Excel 2000 that
allows you to quickly add or remove buttons in a toolbar. This ‘more buttons’ button
will be available at the right edge of an opened toolbar. Clicking on it will display the
available buttons list. You may select or deselect one according to your requirement.

Formula Bar. The formula bar is located just below the Toolbars. This will display the value
or formula you entered in the active cell. The left side of the formula bar will display the
address of the active cell. If you enter a constant value in the active cell, the value will be
displayed in the formula bar. If you used a formula, the formula will be displayed. The
underlying value (result of the formula) will not be shown in the formula bar.

Scroll bars. Obviously, the spreadsheets you created in MS Excel may quite larger in size
and may not be possible to view the entire sheet on the screen. The scroll bars will help you
to scroll the work sheet from left to right, top to bottom and vice versa. To perform these
scroll operations, there are two scroll bars named, Vertical and Horizontal are available in
MS Excel.

The Vertical scroll bar is visible at the right hand side of the window. This scroll bar
will let you to scroll the work sheet from top to bottom or vice versa. There are two scroll
buttons; two browse buttons, one elevator and a browse object selector button available in
this scroll bar to assist you. The scroll buttons are to scroll the work sheet row by row. The
elevator will let you to scroll the worksheet faster and reach to a desired location. To do this,
click and drag the elevator up or down as required. The browse buttons are to browse
(search) for a page, graphic, section etc according to the browse object selected using the
browse object selector.

The horizontal scroll bar consists only the scroll buttons and elevator. This scroll
buttons will let you to scroll the work sheet from left to right and vice versa. To scroll the
sheet to right, click on the right scroll button or drag the elevator right.

TIP Clicking on the ‘select browse object’ button in the vertical scroll bar and selecting
an object type will let you to scroll for a particular object like graphics, page, foot
note, field, table etc.

EXIT MS EXCEL. Every MS Office Applications has same method to close the
application. You may use either the Exit command from the File menu or the shortcut key
command ALT + F4 to exit the application. When you close the application, it will prompt
you to save your work if you leave some work unsaved.
 -5-
WORK BOOK

The work done using MS Excel will save as a workbook. This is just like the
documents you created in MS Word and presentations in MS PowerPoint. The new
workbook will be forced to use a default workbook template named Normal and will also
provide a default name to the workbook as book1, book2 etc. This file name will be used to
save the work book unless you specified a file name as your own.

Work Sheet. The Workbook contains work sheets in it. The work sheet is a grid of columns
and rows. Refer to figure above to have a look to the worksheet as how it will look. Each
work sheet can have up to 255 columns and 65536 rows. The columns are named from A to
IV and rows are from 1 to 65536. You may use any number of columns and rows as per your
requirements. It is not necessary to use all the columns and rows of a work sheet. The
above-mentioned figures are the max limit only.

Cells. The intersection point of each column and row will form a cell. This is the basic
component of a workbook. The cells are named according to its location in the worksheet,
i.e. which column and row is used to create it. A cell formed by the intersection of the
Column A and Row 1 will be A1. If a cell is formed using the D Column and 10th Row, its
name will be D10.

You have seen in the MS Word and PowerPoint that the text typed will only treated as
text and in no way it will count as data. But in MS Excel, each and every thing you entered
in a Cell is data. The word data used in computer field is to mention some information
supplied or stored in computer’s memory. The data can be manipulate and get useful
information to the user. But the text you type in a document or a presentation can’t be
manipulated.

Columns. A column is a vertical group of cells identified with a letter from A through
IV. You may select an entire column by clicking on the letter at the top of the column. If
there is more than one sheet, each worksheet will have 256 columns in it.

Rows. A row refers to a horizontal group of cells identified by a number from 1 through
65536. Clicking on the row selector at the extreme left of a row will select the entire row.

Range. A range is a rectangular group of cells. Each range is referred to by its opposite
corners i.e. the left top cell and the right bottom cell, separated by a colon. If a range starts
from the B4 cell and ends in E9 cell, you may refer it as B4:E9. To select a range of cells,
simply click and drag from the first cell to the last cell.

Active Cell. Active cell is the current cell in which you are working. This cell will be
highlighted with a thick border. When you enter some data or formula, it will be appeared in
the active cell.

TIP When you focus a cell in the workbook, the cell address will be displayed in the Name
box and the Row number and Column name will be highlighted. This will help you to ensure
that you have focused the correct cell.
 -6-
Create a Workbook. To create a
new workbook, simply pull down
the File menu and click on New.
This will display the New dialog
box where you can select the
default template under the General
tab or a preset template in the
spreadsheet solutions tab.

When you finish, click on

OK to close the dialog box and
return to the MS Excel working
window. If you wish to cancel the
New Workbook command, you
can use the Cancel button.

TIP The key combination Ctrl+N will open a new workbook immediately and will bypass
the New dialog box.

Save a Workbook. It
is important that you
should save your work
to retrieve it in a later
occasion. To save a
workbook initially, you
may select the Save
command from the File
menu or press Ctrl+S
in the keyboard. When
you save a workbook
initially, it will prompt
you to supply some
information to complete
the Save command
perfectly. This inputs
can be given in the Save As dialog box, which will be displayed when you execute the Save
command initially or Save As command.

The Save As dialog box will let you to specify the location (drive/folder) where you
want to save your work, a custom file name and the file format. When you finish with these
settings, click on Save button to complete the save operation. The Cancel button will allow
you to cancel the command.

CELL REFERENCES

The cell is the basic element of a worksheet and is used to hold a value, formula or
object. It can be formatted, calculated, moved, named and referenced. Each cell is identified
by a unique cell reference made up of the column name followed by the row number. Each
cell can also be given a unique name and can then be referenced by that name.
 -7-
Cell references are used in formulae to refer to the cells in the worksheet. You can
refer to cells on the same worksheet, other sheets in the same workbook, and other
workbooks. Cell references to other workbooks are called External References. References
to data in other applications are called Remote References.

TIP To select a cell you can simply click it, or enter the cell reference in the Name box or
enter the cell reference in the Go To box by selecting it from the Edit menu.

Cell reference is used to refer to the contents of a cell or range. Cell reference can
refer to an absolute location on a particular worksheet, like Sheet1!$B$3, or a position
relative to a cell. Absolute cell references are always preceded by a dollar ($) sign.
Relative references have no dollar sign. A third case, Mixed references, have both absolute
and relative references combined, such as Sheet1!B$3. In this case the column is relative and
row is fixed. Sheet1!$B3 is also a mixed reference as the column is fixed and row is relative.

Mixed references are one of the basic features that give spreadsheets their power and
flexibility. When used correctly, mixed references can be copied down columns and across
rows, eliminating the need to manually edit formulas. Instead, they automatically refer to the
correct locations.

A few examples will help to explain the differences between absolute, relative and
mixed cell references. If you enter the absolute reference formula =$B$5 into a cell on a
worksheet, it will always be equal to the cell B5 on the same worksheet. If you copy the
formula to any cell on the worksheet, it will continue to be equal to the value of B5.

In relative reference, if you entered the above cell reference without the dollar signs
i.e. B5 in cell C6 and copy it to C7, the reference in the C7 will become B6 instead of B5.
This is because, when you enter cell reference B5 in C6, the Excel will note the location as
one column to left and one row up. If you copy this formula to cell C24, the cell reference
will be changed to B23, as it is the cell one column to left and one row up to C24. It means
that the relative reference will be changed according to the new location.

Mixed References are a little more complex. In the last example, we referred to one
cell to the left, one row up with the formula =B5 in C6. If you copy this formula to D7, the
reference will be changed as =C6. But in some cases you may need to fix the column and
leave the row relative. In such situations, you may enter =$B5 instead of =B5 in C6. This
will fix the column reference to B only. If you copy this reference to D7, the reference will
be as =$B6. These kinds of references are called as mixed references. Either the column or
row can be fixed. When you copy such references to new locations, only the relative
reference will change.

In the above example, if you put the dollar sign before the row number as =B$5 in C6,
the row will be fixed to 5 and the column will remain relative. When you copy this formula
to E10, the reference will be changed to =D$5. The column has been changed according to
the new location and the row remains unchanged.

When you deal with references between sheets, you can enter the sheet name first and
separate the cell reference with an Exclamation (!) sign. This will focus to the cell reference
 -8-

in the sheet name entered. If you want to enter the reference B5 of sheet5 to A5 of
sheet1, you may enter ‘=Sheet5!B5
This means that the cell is in column B and row 5 of sheet5. If you need to make it
fixed reference, you put dollar signs before B and 5. It will look like =Sheet5!$B$5. You
may use mixed reference by changing it to =Sheet5!$B5 or =Sheet5!B$5.

TIP You should be aware to use the appropriate reference in formulas to avoid errors.

FORMATTING

Many times you may require changing the format of the contents of a cell.
Formatting means apply colours, bold, underline, Italics effects, alignments, borders etc to
the data. You have used the Font and Paragraph dialog boxes in MS Word to format text as
well as paragraphs. In MS Excel you have learnt that the basic component of a workbook is
cell and it holds data (not text). Hence the formatting option in MS Excel differs from other
applications.

When you apply formatting, the effects will be directly applied to the contents of the
active cell. If you select a range, the formatting will be applied to the cells in that range.
Now the question is coming that how can you format a cell or a range. Go through the
following few paragraphs to be familiar yourself with various formatting techniques.

Format Cells

The MS Excel is equipped with

a powerful dialog box called Format
Cells, which includes most of the
formatting options. You may access
this dialog box from the Format menu
by selecting the Cells command. The
alternate option is right clicking on the
cell and selects Format cells command
from the popup menu. When you
execute any of the above commands, the
Format cells dialog box will be
appeared in the Excel window.
The Format cells dialog box has
six tabular sections. They are Number,
Alignment, Font, Border, Patterns and Protection. Each tab consists the relevant
formatting options.

Number Tab. The number tab will let you to select a data type and format, which
will automatically apply to the data entered in to the cell. If you select General from
the category list, the cell will support both the numbers and alphabets. You may
select the Number option if you wish to display the numbers in some specific format.

The currency option will let you to change the numbers entered in to the cell
to currency format. If you want to format the contents of a cell to currency format,
 -9-

simply right click on the cell and select the Format cells command. Select the
Currency option in the Number tab and select the currency symbol, decimal places,
negative number format and click on OK. The value in the cell will be changed
accordingly.

When you wish to line up the Currency symbol and decimal places in a
column, the Accounting option will assist you better. When you select this option, it
will place the currency symbol to the left of the column and value to the right. This
will give an enhanced look to the cell contents.

The Date and Time options are to define the date/time format you want to use.
Select one option and select the format in the type column and click OK. The data
you entered in the cell will be automatically changed to that format.

Selecting the Percentage option will multiply the value entered in to a cell
with 100 and display the value with a percentage (%) sign.

If you wish to display fractions like ½ in the cell instead of decimal places,
you can choose the Fraction option. There are some options available to choose to
use when the conversion takes place.

The Scientific options can be used to convert values in to scientific forms,

which will let you to reduce the cell size. In scientific format the figure 1 will be
displayed as ‘1.00E+00’. The figure 10000 will be displayed as ‘1.00E+04’.

The Text option can be used to enter even numbers as text. Select this option
and click on OK. This option can be used when you wish to enter a number
preceding with a zero (e.g. 00123). If you enter a number like 00123 in a column
formatted to General or Number option, MS Excel will omit the zeros at the
beginning and display as 123. In this case, you can choose the Text option and store
that data.

Some special and custom options are also available. You may use these
options to store the data in a customized format.

Alignment Tab. The second tab

is Alignment tab, which will let you
set the alignment options (Vertical and
Horizontal). You may select the
horizontal alignment options to align
the contents horizontally in a cell. To
better align the data, you can also use
the vertical alignment options. These
options will align the data vertically in
the cell.

In some cases you may need to

set indent inside a cell. You can use
 -10-
the indent settings with the Left (Indent) option in the Horizontal column.
You may use the orientation to change the angle and direction of the contents
in the cell. It will let you to set data up to 90 degrees of angle.

The Text control options can be used to better arrange the data. You may
check mark the Wrap text option if you want to break long sentences in to multiple
lines in a cell. When you reach the end of the first line, the next word will
automatically shifted to the second line.
Some times you may require fitting the contents of a cell in the available cell
width. You can check mark the Shrink to Fit option to reduce the font size of the
data to fit inside the cell.
The Merge cells option will work with multiple cells only. You may select
two or more cells and check mark this option to merge the selected cells to create a
single cell. If you wish to remove the merge option later, you can again select the
merged cell and deselect the merge cells option in the Format cells dialog box.

Font Tab. A font is

equivalent to a type style. The
default font used by MS Excel is
Times New Roman. The
formatting of font can be carried
out in Font tab. Activate the Font
tab by clicking on it.

Here you can select the

font, font style, size, underline,
color and effects.

The procedure is same as

in MS Word and you don’t need
much explanation in this section.

TIP A preview will be

displayed in the preview section, which will let you to know how will the text
appear after the changes have applied.

Border Tab. Many times you may need

to apply border to cells. This will
definitely give a better look to your
workbook. Click on the border tab and
select the preset border to apply or remove
border. Selecting the None option will
remove the border from the cells. The
outside option adds an outside border to a
cell or the selected range of cells. The
Inside option will only be active if you
have selected more than one cells. It will
insert borders to each cells in the selected
range.
 -11-

You may also select a line style and colour to apply to the border. It is also
possible to define a border according to your requirement. To do this, click on the
appropriate buttons in the border section.

TIP If you wish to apply colour or line styles, select these options before selecting a
border style.

Patterns Tab. These settings can be used to format the cell with shaded patterns or
colours. Click on this tab and select a colour. Click on OK to view the changes. The
cell will be filled with the colour you selected in the dialog box.

You may select a pattern in the Pattern box and a colour in the Colour box.
MS Excel will create the selected pattern by merging the selected colour with black
colour and display the pattern sample in the sample box. Click on OK to apply the
new pattern to the cell.
Protection Tab. The last option in the Format Cells dialog box is Protection.
These settings will let you to protect a cell or selected cells. If you protect a cell, it
will not allow you to alter its contents.

TIP You must protect your work sheet before protecting a cell. Otherwise the cell
protection will not function.

PROTECT WORKBOOK

It is possible to protect your workbook that

secures the structure and/or windows of the workbook
and can be implemented with or without password. This
level of protection is useful when you are creating data
entry forms or worksheet applications that require the
users to interact with the workbook in a preset fashion.
However the data-forms and worksheet applications are
out of scope of this book.

To protect a workbook, open it in the MS Excel

window and select Protect Workbook command from
the Protection sub menu of Tools menu. This will
display the Protect Workbook dialog box.

You may protect the Structure of your workbook by marking the Structure option.
Selecting the Windows option will protect the window settings like size, position of the
window from other users.

The password is optional. If you don’t want to allow other users to unprotect the
workbook, you may protect the workbook with a secure password. If you don’t supply a
password, other users can access the unprotect workbook command from the Tools>
Protection sub menu and unprotect the workbook.
 -12-

PROTECT WORKSHEET

There is a number of ways to prohibit users

from viewing or changing the data in your worksheets.
Choose Tools > Protection > Protect sheet to access
the Protect sheet dialog box. In this you can protect
the contents, objects and scenarios of a worksheet.

An optional password box is also available to

restrict other users from unprotect the work sheet.

TIP When you protect charts or chart sheets, you

are protecting the chart object or elements
from being manually revised. The chart will still update to reflect any changes in the
source data.

ENTERING INFORMATION

You are now familiar with the basics of MS Excel. You are able to create a new
workbook, save, close and reopen it again. Now you required entering information (raw
data) in to the workbook. A workbook contains many work sheets like pages in a document
and slides in a presentation. The basic component of a worksheet is Cell. It means that any
entry you made will directly go to a cell. You have learnt about cells and cell references in
the previous section.

Data is entered in Excel by simply selecting a cell and then typing into it. As you
type, the data will fill the formula bar and at the active cell at the same time. In addition two
small buttons will appear to the left of the formula bar, one containing an X and other a check
mark.

The X is cancel button and can be used to cancel the entry of the data. However you
must cancel the entry before pressing the Enter key. Once you pressed the Enter key, the
entry will be stored in to the active cell. Clicking on the Check mark is equivalent to press
the Enter key.

TIP When you entering data in to a cell, moving the cursor by using the mouse or
keyboard will automatically cause the data to be entered in to the cell.

When you enter data into a cell, Excel will interpret the data either as text or number.
If the entered data is text, Excel will align it to the left of the cell and numbers to right.
Numeric data can hold numbers from 0 to 9 and characters like +, – , E, e, (, ), $, % and /. If
you enter any other character, Excel assumes it as text. However, the date and time data will
not be treated as text.

TIP Excel will allow you to enter up to 15 digits numbers in a cell. The numbers that have
more than 15 digits will be converted in to Scientific Notations.
 -13-
DATA DISPLAY

Excel displays data according to their format. However, in some occasions default
displays will come into their role and the data will be displayed accordingly. When you click
on a cell that holds a formula, the formula will be displayed in the formula bar only. The cell
will display the formula result.

In some cases, if the cell width is less and the value (number, date or time only) in the
cell is longer, the cell will display some pound signs ‘#####’ instead of the value. In such
situations, you may increase the cell width by dragging the column heading’s right boundary
outwards or apply the shrink to fit option to that cell from the Format cells dialog box.

Excel also handles text in a unique way. Text that is too wide to fit in a cell will be
displayed across a number of cells. However, if there is data in the cells to the right, the text
will be truncated at the end of the cell. The text is still stored in the original cell, but is not
displayed. Some important points which you can apply with the text that is too wide for a
particular column are under:-

 You can widen the column to display all of the text.

 You can simply wrap the text within the cell.
 You can shrink the font so that it fits in the cell.
 You might choose to merge the cells so that the text displays over a number of
columns, if the cells to the right are empty.
 You can change the orientation of the text so that it displays on an angle.

DATA ENTRY TECHNIQUES

There are a number of techniques that can help you speed up the data entry and ensure
its accuracy when working with worksheets.

Data Fill. You may often find it necessary to enter the same data and formulae across a
number of columns or rows. Here is the data fill comes in its role. Data fill will copy the
contents and format of the original cell but not the cell comments. Cell comments are some
notes stored with a cell to give some instant information. You may fill across down or right
to fill the contents of the active cell. However, you may drag the fill handle up or left to fill
across that direction.

To fill the contents and format of one cell to any direction, click
the target cell and drag the fill handle located at the bottom right corner
of the cell to that direction. The contents and format will be copied to
those cells. To do this action using menu command, click on the cell and
select Edit > Fill and select the direction to fill the remaining cells.

Data Series. Data series is a series of numbered items that follow a

natural progression. The numbers are either related to each other in
someway, as in 2, 4, 8, 16, 32. To fill the data series in a range of cells,
select the range and choose Edit > Fill > Series. The Series dialog box
will be appeared. Do the settings and click on OK. For more details on
Data series, ref to BPB publication’s Mastering MS Excel chapter 5.
 -14-

Many times you may notice that click the right button or left button instructions in
applications related publications. The left button always performs the action equivalent to the
Enter key. Wherever you want to execute a command, you can click or double click (as
applicable) the left button.

The right button is to display a shortcut menu, which displays some immediate
commands applicable to the location at where you have right clicked. In MS Excel, you may
use the right button to perform some additional tasks. You may select a cell and drag its fill
handle using the right button, the range will be selected and a short cut menu will be
displayed when you release the mouse button. You may use this menu commands to speed
up the fill action.

The options are,

 Copy Cells will Auto fill copies of the cell rather than creating a trend or series.
 Fill series gives the same results as using Auto Fill to fill a series.
 Fill Formats is similar to Edit > Paste Special > Formats. However, it also copies
the formats in the same sequence as the original range so that formats like stripes
or alternating borders are maintained.
 Fill values copies the contents of the source cell ore extends the series, but it does
not extend the cell formatting.
 Fill Days, Weekdays, Months, and Years is only applicable if the data in the range
is date based.
 Linear Trend, Growth Trend, and series are also available. The latter will bring
up the Series dialog box.
Custom Fill

The Auto fill feature can

automatically extend a range of cells
that contain days of the week,
incrementing each of the days
accordingly. For example, if you
place the word Monday in a cell and
then drag the fill handle across the
row, the adjoining cells will contain
the remaining days of the week –
Tuesday, Wednesday, Thursday,
and so on. This is a handy feature
for entering frequently used lists
such as the days of the week.

Excel provides a feature that allows you to create your own custom list of names or
text. This list is then available in conjunction with AutoFill so that you can enter the first
name on the list and simply drag the remaining names or text in to your worksheet. To create
a custom list, follow these steps:
 -15-

 Enter the list of names in a column in your worksheet. Be sure to enter the data in
the desired order.
 Select the range of cells containing the list and choose Tools > Options and select
the Custom Lists tab.
 Be sure that the first item in the List box, NEW LIST, is highlighted.
 You will notice that the selected range appears in the box labeled Import list
from cells.
 Press the Import button and the list will be added to the List Entries box. You
will also see the list appear under the Custom Lists box.
 Choose OK to close the Options dialog box.

TIP Entering any one of the content in the custom list to a cell and fill it across or down
can fill the data in the list.
Auto Correct.
Excels AutoCorrect
feature checks the spelling as you
enter the data and automatically
corrects commonly misspelled
words. Frequently misspelled
words can be added to the
AutoCorrect dictionary in two
ways. The first is through Excel’s
Spelling Checker. When a
misspelled word is displayed, you
are given the option to add the
word to the AutoCorrect
dictionary. Launch the spelling
checker dialog box from the
Tools menu to check a misspelled
word. The Excel will suggest the
correct word. Select it and click
on AutoCorrect button. This
will change the word to its correct
form in the worksheet as well as
added it to the AutoCorrect
dictionary. In the figure, you may
see that and is misspelled as nad
and Excel’s spelling checker
suggests it as and. When you
click on the AutoCorrect button,
the nad and its right form i.e. and
will be added to the AutoCorrect
dictionary. Next time when you
enter the word nad, Excel will
AutoCorrect it as and.
-16-
 In the other method, you can directly enter the misspelled word as well as the correct
word in the AutoCorrect dictionary. To do this,
 Select Tools > AutoCorrect. The AutoCorrect dialog box will be displayed.
 Enter the misspelled word in the Replace box and Correct word in the With box.
 Click on Add button to add the entry to the dictionary. You may add as many entries
to the AutoCorrect Dictionary using this dialog box.
 When you finish, click on OK.
TIP AutoCorrect can be used to enter full forms of abbreviations, correct misspelled
words etc.

EDITING DATA

Once you have entered a value or formula into a cell, you may again require to change
it. To do this, first select the cell by clicking on it. The value or formula will appear in the
formula bar. Click on the formula bar to edit the data. When you start editing, the status bar
will display the word Edit that will indicate that you are currently in edit mode. A cursor
will also appear in the formula bar to allow you to move through the data and edit it. If the
cell contains a formula or cell reference, the referenced cells will be outlined and colour
coded.

The Options command in the Tools menu allows you to change the default settings of
MS Excel. You have seen that when you wish to edit some data, you should go to the
formula bar to edit it. It is normally not possible to edit the data inside the cell. If you do
some changes in the Options dialog box, it will be possible for you. Launch the Options
dialog box by choosing the Tools > Options command and select the Edit tab. Select the
option Edit directly in Cell and click on OK.

-17-
Now see that the data is editing in the cell. The cursor will be in the cell, not in the formula
bar. To edit a cell, double click on it. It will allow you to change the data.

TIP If you select a cell and start typing, the existing data will be over written. You must
double click the cell or click in the formula bar to edit it.

Editing a cell is very similar to editing data in any program and much like using a
simple word processor. You can use the arrow keys or mouse to move through the cell; the
backspace and delete keys can be used to remove characters; and you can highlight text and
over write it. The Home and End keys can be used to move to the beginning or end of the
cell data. When you have finished editing the data, you can press Enter or Click the Enter
button in the Formula bar.

UNDO & REDO

Like most other Windows programs, Excel provides an Undo command on the Edit
menu. The undo command on the menu usually lists exactly what action will be undone if
you choose it. Excel supports multiple levels of undo. The last actions will be undone first,
and so on. When you have undone some action, the Redo command will be enabled, which
lets you to cancel the undo action. If you have undone some action and go back to the
previous state, you may use the redo command to recall the action.

MOVE DATA

Cutting, copying and pasting data are probably the most frequently performed
worksheet commands. As a result, Excel provides numerous ways to carry out these actions.
Regardless of the method chosen, you should keep in mind that pasting the data will
overwrite whatever is currently in the cell. Excel will warn you of this if you use drag and
drop, but it will simply overwrite the data with no warning if any of the other methods are
used.

TIP To receive a warning when cells are about to be overwritten, choose Edit > Options
command and select Edit tab, check mark the Alert before overwriting option.

DRAG & DROP

One of the easiest ways to move data in Excel is to drag the data from one location to
another using the mouse. To do this,

 Select the cell or range to move.

 Click & depress the left mouse button on the border of selected cell or range.
 Drag the mouse to the new location.
 Release the left button.

-18-
 You can copy data the same way by simply holding down the Ctrl key before you drop
data. When you press the Ctrl key, a small plus sign that appears above the mouse
pointer to indicate that you are copying the data rather than cutting it.

Cell border

Fill handle

There are keyboard short cuts available to perform the Cut, Copy and Paste actions.
They are,

Ctrl + C - Copy The Cut, Copy and Paste commands can also be access
Ctrl + X - Cut from the Edit Menu.
Ctrl + V - Paste

The Standard Toolbar also provides the Cut, Copy and Paste command tools,
which can be easily access through a simple mouse click. The buttons are below:

Cut Copy Paste

It is important to know that when and where should you apply the Cut and Copy
commands. The data you have placed in the clipboard using cut command can be used in any
section of your worksheet, one work sheet to another, one workbook to another workbook.
When you cut a cell or range of cells, a moving dotted line surrounds the range, indicating
that a copy of the data has been placed in the clipboard. But, Excel does not actually cut the
data from its old location until you have pasted it to a new location by using the Paste
command.

You must aware that, when you perform cut and paste commands, the data will
retains all of the same cell references. If you cut the formula =E8*D8 from cell F6 to H12, it
still refers to cells E8 and D8.

TIP When selecting where to paste a range of data, select a single cell at the left corner
rather than the entire range. Excel will automatically select the correct number of columns
and rows.

When you copy a cell or range of cells, a moving dotted line surrounds the range to
indicate that a copy of the data has been placed in the clipboard. You can then select a range
on the worksheet where you want to paste the data. After the data has been pasted, you will
notice that the moving dotted border still surrounds the original cells. This gives you
opportunity to paste another copy of the data to a new location. Simply select the new range
and paste another copy of the data to your worksheet.

You must aware that, when you copy a formula that includes relative and mixed cell
references, Excel will rewrite the references according to its new location. For more details
you may refer back to the Cell References section.
-19-
DELETE DATA

You have two choices for removing data from the worksheet. You can clear the data
in the cells, which is equivalent to erasing data by selecting it and apply the Clear command
from Edit menu. In another way you can delete the actual cells, which removes the entire
cell and shifts the cells underneath.
To remove only the data, do any of the following:

 Edit the cell and delete the actual values using the delete or backspace key.
 Use the menu option Edit > Clear > All.
 Right – click with the mouse and choose clear contents.
 Drag the fill handle up and to the left so that it covers the cell.
 Select the cell and press the Delete key.
 Select the cell, press Backspace key and press Enter.

Delete Cells/Row/Column.

Deleting cells should be done very carefully. It may affect your worksheet adversely.
When you clear a cell, only the contents will be cleared. The blank cell will remain there.
Deleting a cell will alter all of the cells below and to the right of the selected cell by shifting
the data. Deleting a cell may vary the relative and mixed cell references in the formulas of
the worksheet. You may delete an entire row or column easily. It will not change the cell
references in formulas.

To delete a column or row,

 Select it by clicking on the Column Letter or Row Number.

 Choose Edit > Delete.
 The selected row or column will be deleted.

INSERT DATA

The procedures of insert command are matches with the Delete command procedures.
You can insert entire columns and rows.

To insert an entire row or column,

 Select the row or column by clicking its letter or number.

 Choose Edit > Insert.
 A column or row will be inserted left or above to the selection.

-20-
Insert Cells.
 Just like when you delete a cell or range, when you
insert cells you are prompted to shift the data on your worksheet.
Select the cells or range of cells to insert and choose Edit >
Insert. A dialog box will ask whether you want to shift cells
right or down to make room for the newly inserted range.

Selecting the Shift cells right will insert the selected cell
or range and move the existing cells in that location to right. If
you select Entire row or Entire column the cell range will
inserted and shift row or column in that location entirely.
FIND AND REPLACE

In some times you may require replacing an existing text or value with another one.
The command Find and Replace are come in their role here. You can find almost any text,
whether it is located in a cell, formula, cell reference, or range name. Once it has find, you
have the option to replace it.
To search for data,
 Click on any cell to search the entire worksheet or select the required range.
 Choose Edit > Find.
 Enter the text string to find under Find What.
 Click on Find Next to find the search string.
To replace data,
 Carryout the above actions.
 Click on Replace button.
 Enter the new data in the Replace with.
 Click on Replace button to replace the search item one by one.
 Click on Replace All button to replace search item in all locations.

SPELLING CHECKER
The MS Excel has an
inbuilt spell checker, which
will let you to compare the
entered text with a dictionary
that holds most of the
meaningful words. To do
this,

-21-

 Select a single cell to

  check the whole worksheet or select the range to limit the area to check.
 Press F7 or choose Tools > Spelling.
 The Spelling dialog box will be displayed.
 Follow the commands in dialog box and click on Cancel button to finish the job.

SAMPLE WORKBOOK

You have been familiarized with the basic workbook concepts and now it is possible
to create a workbook, enter/edit/delete/insert data and format it yourself. Here is a sample
workbook to practice the commands and options discussed above.

Active Row Cell Address Cell’s contents Active Column Active Cell

This is a simple workbook, which holds the details of an examination mark sheet. To
enter these data, simply click on each cell and type the data. Use the Align commands to
align the data as per your requirements.

The active cell is highlighted with a dark border. When you type something, it will
directly enter into the active cell. You can view the Active cell address in the Name box. To
identify the cell address yourself, look for the highlighted column letter and row number.
The cells contents will be displayed in the Formula bar. You may edit the cell contents by
clicking on it in the Formula bar or double clicking in the cell. You can also press F2 and
edit the data inside the cell.

The data range has been applied a border style to give a look to the worksheet. To do
this, simply select the range by click and move the mouse over it and apply a border style
using the Outside border icon in the Formatting toolbar or from the Border tab of Format
cells dialog box.

-22-
Once you have made the workbook, you may edit it. Various edit methods you have learnt in
the previous sections. If you wish to add a heading to the above worksheet, you should insert
a row at the top of the worksheet. To do this simply right click any cell in the row 1 and
select insert command. Now select entire row in the Insert dialog box and click OK. You
may also do this command by choosing Insert > Row.

Type the heading as MARK SHEET : SQD (A) 010203 in the first cell. The text will
be displayed in the first cell across the succeeding cells. But it is not centered to the
worksheet contents. You may select the cell range A1:G1 and merge the cells using the
Alignment tab of Format Cells dialog box. The cells will be merged and become a single
cell. Now set the alignments horizontally center and vertically top using the Format cells
dialog box. Now your worksheet will look like as under:

FUNCTIONS & FORMULAS

In MS Excel, formulas always begin with an equal (=) sign. This tells Excel that the
numbers and characters following the equal sign must be calculated. If you leave off the
equal sign, the formula will be interpreted as text and displayed as entered. For example, if
you entered =3+2 in a cell, the cell will calculate the formula and will display the result 5 in
the cell. If you enter 3+2 in a cell, the cell will assume it as text and will display 3+2 in it.
Formulas consist of numbers, cell references, defined names, operators, and/or
parentheses. The numbers can hold figures from 0 to 9. The operators are as under:

Operator Character Operator Character

Addition + Greater than >
Subtraction - Less than <
Multiplication * Greater than or equal to >=
Division / Less than or equal to <=
Exponential ^ Not equal to <>
Equal to = Concatenation &
-23-
 When you type a simple formula like =1+3, Excel will calculate the formula and
display the result. But if you want to change the values, you should edit the formula. This is
not recommended because a simple mistake in the formula will make it defunct. In such
situations, you may enter the values in worksheet cells and can enter those cell references in
place of numbers in formula. If you enter the above values, 1 in cell A1 and 3 in B1 and
enter the formula =A1+B1 in cell C1, it will display the result 4. When you change the
figures in A1 or B1, the result will automatically change. This will reduce time and
unwanted editing of formula. See the figure below to give a look to the above formula:

Formula

Result

Values

Operators define the calculation to be performed by the formula. You may familiar
with the standard mathematical operators like +, –, / and * which are represented to Addition,
Subtraction, Division and Multiplication respectively. You may use these operators directly
to perform those types of calculations in Excel. But it is important that, you must start the
formula with an equal to (=) sign.

Formulas are calculated from left to right. Operators within parentheses (bracket) will
calculate first. Exponential calculations will perform secondly. The multiplication and
division calculations will be perform lastly. You must aware of this sequence of calculations
while creating formulas. The items those required to be calculated first should be in
parentheses.

Look at the following two formulas. In these formulas you may find the power of
parentheses.

Formula Result Sequence of calculation

=(2+6)/2 4 Excel will calculate 2+6 first and then divide
by 2
=2+(6/2) 5 Excel divide 6 by 2 first and then add to 2

In addition to the mathematical operators and text operators, Excel also uses three
reference operators especially with cell ranges. They are Colon, Comma and Space.
-24-
 A colon is used to specify a range like A1:A5 refers to A1, A2, A3, A4 and A5.
A comma is used to separate two ranges like A1:A5, B1:B5 etc.
A space is used as an intersection operator between two ranges. It refers to the cell at
the intersection point of the two ranges and evaluates to #NULL! if the two ranges do not
intersect. e.g. C1:C5 B3:G3 would be the value in cell C3.

Usually you will use the cell references in formulas. But many times it may difficult
to understand the formulas. You can enter a formula in cell H2 to add the values in E3, F3
and G3. The formula will look like =E3+F3+G3. While looking into the formula you may
not get idea about the actual representations of each cell. If you can enter the formula as
=WP1+WP2+WP3 it well understood.

To do this, you may use the naming feature. Cells as well as Ranges can be named
individually. These names can be used in formulas also.

To name a cell or range,

 Select the cell or range.
 Type the name in the Name Box in the left of the formula bar.
 Press Enter.
 The cell or range will be named accordingly and the name will appear in the
Name Box drop down list.

TIP When you use text in formulas, the text string should be in double inverted commas
(Apostrophe). You may use the concatenation (& sign) to combine text strings.

CIRCULAR REFERENCES

A circular reference is a reference that refers back upon itself. If you enter the
formula =A1+B1 in cell B1, it will become a circular reference. It can’t return a result as the
value to be calculated is referred to the same cell. When you enter functions, you must aware
of circular references.

Now you go back to the sample workbook discussed earlier. You have learnt to use
formulas to use manipulate data. In the above workbook, you have created a sample mark
sheet, which has marks of three papers of some students. Now you add one column at the
right of the WP 3 as Total Marks. Here you can use an addition formula to add the marks of
three papers.
 Simply click on cell H3 and enter the formula =E3+F3+G3 and press Enter.
 See that the sum of the three cells referred in the formula has displayed in H3.
 Click on H3 again and drag the fill handle to H7.
 The formula will copy to the selected range and they will display the result
accordingly.
 The modified sheet is displayed below.

-25-
The following modifications have been made in the above workbook:
 Added the Total Mks lable in the cell H2.
 Added a formula in cell H3 as displayed in formula bar above.
 Selected the cell H3 and drag its fill handle to H7.
 Merged the cell H1 with the merged cell A1 to adjust the heading in the center.
 Center aligned the range H3:H7 to align the data in the center.
 Applied border style to the above range.

FUNCTIONS

A function is a built-in formula that performs a calculation or a series of calculations

based on predetermined rules. Functions usually require input arguments and always return
a result. MS Excel is equipped with hundreds of functions to perform common tasks such as
SUM, AVERAGE, MAX etc.

Almost all functions require input parameters or arguments. In the above formula you
have supplied the cell addresses to create the formula. In functions there are some words that
are the name of the function, which asks you to fill in a reference or value to return a result.
In the above formula you have used three cell references to return a result. You may use the
SUM function instead of the formula. SUM function can be used to add values in a range of
cells. To use the SUM function, you must type =SUM and enter the cell range in
parentheses.

=SUM(E3:G3) is equivalent to the above formula. It will calculate all the values in
cell range E3 to G3. The value entered in the parentheses is called as function argument.
 -26-
Paste Function

Built in functions are easily

accessible through a dialog box named as
Paste Function. You may open this dialog
box by selecting Insert > Function. You
may also use the button in the Standard
toolbar to launch the dialog box.

When you select a function category

in the box, the related functions will be
displayed in the Function Name box. When
you select a function, its name, purpose and
required arguments will be displayed below the Function category box. When you click on
OK another dialog box will be opened, which will let you to enter the required arguments.

 Click on the collapse button (red arrow mark) to select a range or cell in the
workbook. The dialog box will be minimized and let you select the value from a range
or cell.

 Select the range or cell and again click on the collapse button to restore the dialog
box. The selected value range will be displayed in the first argument box and the values
will be shown in the right side of the box.

 Repeat the procedure to complete all arguments and press OK.

TIP When you enter formula, simply click on a target cell to select its cell address or
select the range to get its reference.

Common functions

 MAX. The MAX function can be used to find the highest number in a range of
cells.

=MAX (Cell Range)

Use comma to mention more ranges.

-27-
  MIN. The MIN function can be used to find the lowest number in a range of
cells.

=MIN (Cell Range)

Use comma to mention more ranges.

 AVERAGE. The AVERAGE function can be used to find the average

number in a range of cells.

=AVERAGE (Cell Range)

Use comma to mention more ranges or cells.

 COUNT. The COUNT function is used to count the total number of values
entered in a range. You can enter up to 30 arguments (ranges or cell references)
separated by comma.

=COUNT (Cell Range)

The count function only looks for numbers, dates and times. Text or blank
columns will not be counted. You can use the COUNTA function to count all
values, including numbers, text, error values, logical values etc. The argument is
same as count function.

LOGICAL FUNCTIONS

Logical functions use TRUE and FALSE statements to evaluate a condition or return
information. One of the most common logical functions is IF, which will perform one
operation if a condition is true and a separate operation if the condition is false. This is one
of the most commonly used functions in MS Excel because it allows compare data.

The other logical functions used in MS Excel is AND and OR operators, which
combine two or more tests in one formula and return a TRUE or FALSE result. The NOT
function is also used for logical testing.

IF Function

The IF function is used to evaluate a condition and to perform one of two operations,
depending on whether the condition evaluates to TRUE or FALSE. The structure of ID
function is as under:

IF (logical_test,value_if_true,value_if_false)

The function has three arguments. The first argument is the condition to be tested.
The next argument is the formula to be used to return a result, if the testing condition
returns a TRUE value. The third argument will considered for calculation, if the test
result is FALSE.
-28-
 Look back to our sample workbook. You have added a total marks column in that
workbook. Now we can use the IF function to test the students are Pass or Fail.

 Go to the workbook and add a column heading as Result at the right of the
Total Marks column.
 Enter the following IF function in the cell I3.
 =IF(H3>150,“PASS”,“FAIL”)
 Ensure that you have entered all the parentheses, commas and cell references
correctly. If okay, press Enter.
 Click on I3 and Drag the Formula up to I7.
 The result will be displayed accordingly.
 Give a look to the figure below:

The example above is a simple IF function. You may use additional IF functions to
the Value_IF_False argument section to test more. Add a new column heading in the above
workbook to the right of the Result heading as Grading. Here you will experience the power
of IF function. Click in J3 and enter the following formula:

=IF(I3="FAIL","FAIL",IF(H3>=210,"A",IF(H3>=180,"B","C")))

In this formula, Excel will test for the first condition i.e. I3=“FAIL” and will return a
value “FAIL” if the value in cell I3 is “FAIL”. If the test result is not true i.e. the I3 holds the
value as “PASS”, it will go the False Value section of the IF function, which is another IF
function.

Now the second IF function will be tested. The Excel will search the cell value in H3
and will return the value “A”, if the value is greater than or equal to 210. If it is not, it will
focus to the False Value section of the second IF function. Here the third IF function will
become functional.
 -29-

Excel again go for a search to the cell H3 and will return the value “B”, if the cell value is
greater than or equal to 180. If it is not it will go to the False Value section of the third IF
function i.e. “C”.

You must aware about the parentheses and commas; otherwise these kind of
formulas will put you in puzzle.

Combinations of IF Function

 SUMIF. The SUMIF function can be used to SUM a range or referenced

cells with some logical testing.

The structure is =SUMIF(Range,Criteria,Sum_range)

Range is the range of cells you want evaluated.

Criteria is the criteria in the form of a number, expression, or text that defines which cells
will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".

Sum_range are the actual cells to Sum. The cells in sum_range are summed only if their
corresponding cells in range match the criteria. If sum_range is omitted, the cells in range
are summed.

Example
Suppose A1:A4 contain the following property values for four homes: Rs10,000,
Rs20,000, Rs30,000, Rs40,000, respectively. B1:B4 contain the following sales commissions
on each of the corresponding property values: Rs700, Rs1,400, Rs2100, Rs2800.
 -30-

SUMIF(A1:A4,">15000",B1:B4) Result will be Rs6,300

It considered only A2:A4 because the criteria amount “>15000” is not true for cell
A1 as it is Rs10,000. Hence it summed the values in range B2:B4.

 COUNTIF. The COUNTIF function can be used to COUNT a range or referenced

cells with logical testing.

The structure is =COUNTIF(Range,Criteria)

 Go back to your sample workbook and click on cell B9 and enter Total Attended.
 Click on Cell B10 and enter Passed.
 Click on Cell B11 and enter Failed.
 Click on Cell B12 and enter Pass %
 Now click on cell D9 and enter the formula, =COUNTA(J3:J7)
 Click on cell D10 and enter the formula, =COUNTIF(I3:I7,"=PASS").
 Click on cell D11 and enter the formula, =D9–D 10.
 Click on D12 and enter the formula, =D10/D9*100.

 Format the workbook as shown in figure above to give a nice look. These changes will create
a summary of the mark sheet. Notice that how the COUNTIF function works.

 When you enter the formula =COUNTA(J3:J7) in cell D9, Excel has counted the total number
of entries in that range and return the result in D9.
-31-
  The COUNTIF formula in D10 look for the criteria and count the matching cells, that holds
the “PASS” value and return the value in D10.

 Now you have add a simple formula to find the difference between the cell D9 and D10 to
find out the total number failed in D11.

 The formula entered in D12 has divided the Total Pass by Total Attended and multiply with
100 to find out the percentage.

AND & OR FUNCTIONS

The AND and OR functions evaluate multiple conditions and return a TRUE or
FALSE based upon the results of each of those conditions. The structure (syntax) of these
functions is

AND(logical1, logical2,….)

OR(logical1, logical2,….)

When you place the AND function and give conditions inside the parentheses, MS
Excel will evaluate all the conditions in the parentheses and will return a TRUE or FALSE
condition. You can use these logical functions with the IF function to increase the test levels.

Example

Look at the sample workbook given below. It calculates the result of a BPET
according to the individual’s performance in each item. A combination of the IF and AND
functions is used to return this result.

The formula used in cell G3 is given below.

-32-
 =IF(AND(C3<>"F",D3="Q",E3="Q",F3="Q"),C3,"F")

In this formula, MS Excel have performed four tests and return a result according to
the test value.
 MS Excel tests the C3 and confirms that it is not holding the value “F”.
 It again looks in to D3 and ensures that it holds a value equal to “Q”.
 The test is then focuses to E3 and ensures it is holds the value “Q”.
 Now the last condition i.e. F3=”Q” is tests.
 Now Excel will merge the test report and create a final result, as all the conditions are
TRUE.
 If it is TRUE it will go the True Value part of the IF function. In this equation the
True Value is a cell reference. Hence Excel will display the value in the referenced
cell as the formula result.
 If any or all of the conditions are FALSE, then Excel will look at the False Value
argument of the IF function. Here it is a text string i.e. “F”.

You can also test the above conditions using the OR function. The formula is given below:

=IF(OR(C4="F",D4="F",E4="F",F4="F"),"F",C4)

Notice the differences in the conditions used to test for a TRUE or FALSE state.

DATE AND TIME FUNCTIONS

Almost every application, regardless of its ultimate purpose, will eventually

incorporate dates and times. Dates can be used as a date stamp or be manipulated for
analysis. In MS Excel the dates and times are stored in memory as numeric values. Since it
can be easily add and subtract.

Excel provides two functions enabling you to return the current date and time: NOW
and TODAY. These functions check your system clock and return a current setting that can
be used to timestamp a workbook. Stamping a worksheet with the current date and time
allows you to indicate when an Excel worksheet was printed.

NOW and TODAY Functions

The NOW function can be used to fill a cell with both the date and time at a particular
moment. The syntax for the NOW function is =NOW(). When you enter this function in a
cell, it will look at the system date and clock. Then it will display the date with the time in
the cell. You may format the cell to change the date format.

The TODAY function is very similar to the NOW function. However, the TODAY
function will only return the date value in a cell. It will not display the time. The syntax of
TODAY function is =TODAY(). Both these functions have no arguments section. It will
take the date and time value from the system directly.
-33-
Goal Seek
Sometimes you may know the desired result of a single formula. But you are not sure
about the input values that the formula needs to determine the result. In such cases you can
use the Goal Seek feature. When goal seeking, Microsoft Excel varies the value in one
specific cell until a formula that's dependent on that cell returns the result you want.

Solver

You can also determine resulting values when you need to change more than one cell
used in a formula and have multiple constraints for those values. Solver adjusts the values in
the cells you specify to produce the result you want from the formula.

CHARTING

Chart is a graphical representation of data in a worksheet. It is so effective and

conveying information rapidly. MS Excel containing powerful charting features.

Instant Charting

It is very easy to create charts in MS Excel. The easiest way to create an instant chart
is,
 Select the data range.
 Press the F11 key.
 MS Excel will create a chart based on the default chart type.
Go back to the Mark Sheet workbook above, and select the range D2:G7 and press
F11. The chart shown below will be created instantly.

-34-
 MS Excel has created a chart based on the data and labels in the selected data range
and pasted it as a Chart sheet in the workbook. You may rename, move, copy and delete chart
sheets by right clicking on it and select the appropriate command from the short cut menu.
 -35-
Chart Wizard
It is also possible to create charts
in a worksheet. These charts are called
as embedded charts. To create an
embedded chart, follow the steps below:

 Select the data range that you

wish to chart in the worksheet.
 Click the Chart Button in
the Standard toolbar or select it
from the Insert Menu.
 The chart wizard will be started.

The chart wizard will collect

inputs from you in four steps and
will create a chart. These steps
are explained below:

Chart Wizard Step 1. When you click on the Chart button in the Standard Toolbar,
the dialog box as shown at the right of this text will be displayed. This is the first step
of Chart creation using chart wizard.

In this dialog box, you may select a suitable chart type to display your data.
To do this,
 Select a category in the Chart type list box. When you click one, a number of
sub layouts will be displayed in the Chart sub-type list box.
 Click on a sub type. The visual function of the chart will be displayed in the
box below.
 Press and hold the Press and Hold to View Sample button to give a look to
chart that will create for you on completion of the wizard.
 When you finish, click on Next to continue.

Chart Wizard Step 2. The

second step will be displayed
when you click the Next button in
step 1.

The data range you have

selected will be shown in Data
Range box. If you require
modifying it, click on the
Collapse button at the right of
the box. Do the modifications
and click on the collapse button
again to continue the settings.
You may specify the data
series in this wizard. This means
-36-
that if you select the Rows radio button, the chart will assess the values in the
rows to create series. Selecting the Columns option will put the values in
columns as the series.

The second tab of this step will let you to specify advanced series settings.
When your worksheet is simple, it is not required. But if you are trying to create a
chart with data located in various locations, you must do these advanced settings
to improve charting.

Each change you have made to the data range will reflect in the preview of
chart shown at the top of the dialog box. Click on Next to continue.

Chart Wizard Step 3.

Using the 1st two

steps of chart
wizard you have
finished the data
settings of a chart.
But the job is not
finished yet.

In step 3, you
are required to do
some settings to
improve the look of
the chart. Chart is a
graphical display of
data stored in a
worksheet. Hence it is important that the chart itself should be understandable.

In step 3, you will see six tabs. They are:

 Title. In this tab, you can enter a suitable Chart Title, X-axis and Y
axis titles. Enter the titles in boxes given. The changes can be viewed
in the Preview.

 Axes. This tab is to set the X-axis and Y-axis labels. You may
display or hide the labels as well as values shown in the X and or Y
axes. If you select the automatic or category option under the X-axis,
the label will be displayed. Selecting the time-scale will display the
time in the X-axis. Normally the default settings are sufficient. You
may not require to makie any changes to this tab.

 Gridlines. The lines shown horizontally and vertically in the Plot

Area are called as Grid Lines. You may use grid lines to divide the
plot area in to small sections, which will let you to find a value easily.
 -37-

 It is also possible to hide grid lines. These options you can set up in
the Gridlines tab.

TIP You may add Major & Minor grid lines in both X and Y axis.

 Legend. Legend is useful to identify series. This is a sample of

each series displayed in the chart. You may display, hide and change
location of the legend in a chart. The Legend tab will assist you to
complete this job.

TIP Hiding the legend will increase the size of the Plot Area.
.
 Data Labels. Data labels tab is to set the option that the data label or
value should be displayed above each series or not.

 Data Table. This is the last tab and will let you to display or hide the
data table with the chart. Selecting the option will display the data
range you selected in the worksheet below the chart.

TIP When you are placing a chart as an independent sheet, it is

better to choose the Data Table option.

Do the settings and click on Next to continue.

Chart Wizard Step 4.

The last step will ask you to select the option that where you wish to insert the
chart. You may insert the chart as an object in the same sheet or as a new sheet.
Click on the appropriate radio button and click on Finish to complete the action.

The last three steps in the Chart wizard will let you to go back to the previous step, if
you wish to change the settings. When you click the Finish button in any one step, the chart
as per your request will be created. If you left some of the steps, MS Excel will assign the
default settings for those steps.
 -38-
BASIC CHART OBJECTS

Y-Axis X-axis Chart Area Plot Area Series Grid Lines Legend

The key to mastering the use of charts in Excel is to have a thorough understanding of
each chart object, its modification and formatting. Some of the important chart objects are:-

(a) Chart Area. This is the total area covered by the

chart object. The other entire chart objects and elements are
part of the Chart Area. This is just like a canvas used to draw
a picture. Chart is the picture and Chart Area is the canvas.

Chart area can be used to modify the whole chart.

Right click any blank area of the chart area and select
appropriate command from the context menu to perform the
modification.

Using this context menu, you can format the chart area.
It means that you may change the background colour and font of the chart area. It is
also possible to display all of the four dialog boxes of chart wizard to do some
modifications by selecting its command from the context menu.
 -39-

(b) Plot Area. This is the actual area covered by the chart. This area includes
the axes, grid lines and data series.

(i) Axes. These are the Y-axis and X-axis also called as the Value axis
and Category axis respectively.

(ii) Data Series. A chart consists of one or more independent series of

data. A data series is made up of values and a name or label indicating what
the data series represents.

(iii) Grid Lines. Gridlines help the viewer to assess the relative
variability in each data series and provide a means to quickly obtain with
reasonable accuracy the value of a given data point.

(c) Legend. The legend provides a key to what the data series represents. It
visually maps the name of the series to the colour or pattern used to represent the
series in the plot area.

TIP To format any individual object of a chart object, select the chart, simply right click
on the object and select Format command from the context menu.

PAGE SETUP & PRINT

Before printing your worksheets, it’s good idea to think about the size and orientation
of print media. The printed document may look considerably different than it appears on the
screen, especially if you printing to a black and white printer. Most of the options to control
the printing of a worksheet can be found in the Page Setup command on the File Menu.
Before printing a worksheet you should do the settings in the page setup dialog box. Keep in
mind that whatever changes you make in Page Setup will affect the active worksheet only.

Print Area. The first step in printing a document is to decide on the area of the
worksheet to print. The print area can be the entire worksheet, or just a specific range
within the worksheet. If you do not set a print area, Excel will assume the entire data
area of the worksheet as the default option.

To define a print area,

 Select the range of cells you want to print.

 Choose File > Print Area > Set Print Area command.
 The selected range will be marked as the print area.

OR

 Click on File > Page Setup command.

 Click on the collapse button of Print Area box under the Sheet tab.
 Select the range of cells with the mouse.
 -40-

TIP To redefine a print area, simply select the new range and set it as the print area. The
earlier print area will be redefined.

Page Break Preview. The Page Break Preview command in the View menu will
change the view settings of a worksheet and will display the defined print area only.
The page break preview is better to view the page breaks in your worksheet and you
may adjust the page breaks by click and drag them with mouse. This will let you to
include or exclude columns or rows in a single page.

It is important that, when you drag the page breaks to include additional
columns or rows, the page contents will be resized to fit in a single page. It means
that, as many columns or rows you included in to a single page, the size of the
contents will be reduced from 100% to less. The page break preview of the sample
worksheet we have done earlier is shown below:

Page breaks Page No

Page Setup Dialog Box

All the settings pertaining to Page setup can be done in the Page Setup dialog box. To
view the Page Setup dialog box, select File > Page Setup. The Page Setup dialog has four
tabs. These are Page, Margins, Header/Footer and Sheet. Each tab will let you setup the
options related to its name.

Page Tab This tab will allow the settings like Orientation (Portrait or
Landscape), Scaling of contents to fit in one page, Paper size and Print quality.
 -41-

The orientation means the layout of the page to print the contents. It may be in
vertical or horizontal. If you wish to print the contents vertically in a paper, select
the Portrait radio button. Selecting the Landscape radio button will print the
contents horizontally. It is important that you don’t need to change the paper loading
procedure in the printer to print in landscape. Load the paper normally and the printer
will automatically print according to your selection.

The scaling option

will let you to reduce or
increase the contents to fit in a
single page. Adjust the
percentage by clicking the
increase or decrease buttons.
Click on the Print Preview
button to view how will the
changes affect in your
printout. You may also select
the Fit to option to fit the
entire print area in selected
number of pages. Select the
Fit to radio button and set the
number of pages in both wide
and tall options to one. View the changes in Print Preview. The entire print area
contents will be adjusted in a single page.

Header & Footer. Use this tab to set the Header and Footer that should be
printed in each page. The Header and Footer boxes will let you to select a preset
one. Click on the drop down box and select one to display it as your header or footer
in each page.

Use the Custom Header and Custom Footer buttons to define a custom text
or expression. When you click on the Custom Header or Custom Footer button, the
following dialog box will be appeared. There are three sections available to enter the
Header or Footer. Click on a section and type the text or select a tool from the
displayed tools to include page numbers, time date etc.
 -42-

Margins. This tab

is to set up the margin
size. The Top, Bottom,
Left, Right, Header
and Footer margins can
be set up using this tab.
The Center on Page
option will let you to
adjust the contents on
the page to align
vertically or
horizontally or both.
Click on appropriate
check box and see the
changes.

Sheet Tab. Sheet

tab is the most
important one. This
will let you to define
the Print Area,
Print Titles (Top
and Left of each
page), Print options
and Page Order. To
define the print area,
simply click on the
Print Area collapse
-42-
 -43-

button and select the range. The range you selected will be displayed in the Page
Setup box. Click again the collapse button to restore the Page setup dialog box.
The Print Titles option will let you to define some rows, which should repeat
in each page. If you are printing a large sheet divided in to multiple pages, the
column headings may require in each page. In such situations you can click the Rows
to repeat at top collapse button and select the range. Contents of these rows will be
repeated in each page. You may also select specific column(s) to repeat at left side of
each page. To do this, click the Columns to repeat at left collapse button and select
the range.
The Print options can be used to enhance the look of the printed material.
Select the Gridlines option to print gridlines. Select the Black and white option to
print the sheet in black and white mode. Select the Draft quality option to save the
ink/toner and print a draft page.
Select the Row and Column headings to include them with the page. This
option will print the column heading like A, B, C, D etc at the top of each column and
row numbers like 1, 2, 3, 4 etc at the left of each row.
If you wish to print the comments that you have made to cells in your
worksheet, select the print option in the Comments box.
The last option is the page order. This is useful when the worksheet is wider
than a single page. Selecting the Down, then over will print the pages from top to
bottom and start from top again. The Over, then down option will print the pages
horizontally and start from left again.

Down, then over Over, then down

Page 1 Page 4 Page 7 Page 1 Page 2 Page 3

Page 2 Page 5 Page 4 Page 5

Page 3 Page 6 Page 6 Page 7

Printing
The print command is
just like in MS Word. Select
the Print command from the
File menu. The Print dialog
box will be displayed.

The first step is to

decide which printer should
be used to print the sheet.
Select the appropriate printer
name in the Name drop down
box. When you select a
printer the status, type,
 -44-

location and comments of that printer will be displayed. Ensure that your printer status is
Idle. Idle means that printer is currently not printing. The Where indication will let you
know that the printer is local or network one.

Click on the Properties button to setup the printer. The printer’s properties will be
displayed. Set the paper size, resolution settings, Print settings and click on OK to return to
the Print dialog box.

Now set the Print Range that you wish to print. The All option will print the entire
pages in the current sheet. You may specify a particular page range in the Page(s) option.

Set the Number of copies according to your requirement. Use the collate check box
to print a complete set of each copy. This option will only be active if your print area
contains more than one page. Deselecting the Collate option will print each individual page
to the number of times you specified in the copy box.

The Print What option is to select what to print. It may be a selection, Entire
workbook or the Active sheet. Select the appropriate radio button and click on OK to start
printing.

SORT DATA

MS Excel uses specific sort orders to arrange data according to the value, not the
format of the data. The sorting is possible in two ways; they are Ascending (A-Z) and
Descending (Z-A). In an ascending sort, Excel uses the following order:

Numbers Numbers are sorted from the smallest negative number to the largest
positive number.

Alphanumeric sort When you sort alphanumeric text, Excel sorts left to right,
character by character. For example, if a cell contains the text A100, Excel places the
cell after a cell that contains the entry A1 and before a cell that contains the entry
A11.

Logical values In logical values, FALSE is placed before TRUE.

Error values All error values are equal.

Blanks Blanks are always placed last.

Sort the Contents Based on a Single Column

If you previously sorted a list on the same worksheet, Microsoft Excel uses the
same sorting options unless you change them. To sort the contents of a column:
 Click a cell in the column you would like to sort by.

 Click on Sort Ascending button on the Standard Toolbar.

 The contents will be re-arranged accordingly.
 -45-

TIP Selecting the Sort Descending button in the Standard Toolbar will re-arrange the
contents of the active column from Z – A.

Sort the Contents Based on Two or More Columns

It is possible to sort the contents of two or more columns in MS Excel. To do this, you will be
assisted with the Sort dialog box. To sort the contents of a sheet based on two or more columns, do
the following:-

 Click a cell in the list you want to sort.

 On the Data menu, click Sort.
 The Sort dialog box will be displayed.
 Select the name of the columns you want
to sort in the Sort by and Then by boxes.
 Select the sort sequence at the right of the
boxes.
 Click on OK to apply the sort order.
 The list will be sorted according to your
settings.

TIP When you sort the list based on two or more

columns, the list will be sorted according to
first selection, then sorted again according to the Then by column. It means that the
contents of the second column will only be considered for filtering if there is duplicate
data in the first column selected for sorting.

Look at the picture below to get an idea about the sort operation. The sample
workbook you have created earlier has been sorted to the Descending order of the Total
Marks column.
-
46-
Total Marks column has been sorted
FILTER DATA

Filters are useful to see the data that matching specific criteria. MS Excel supporting
the filter operations. Applying the filter option to a column will display a subset of rows in a
list. It is important that you can apply filters to only one list on a worksheet at a time.

Apply Filter

 Click a cell in the list you want to filter.

 On the Data menu, point to Filter, and then click AutoFilter.

 Drop down arrow buttons will be added to each column header.

 Click the drop down arrow in the column and select an item from the drop down list.

 The rows those contains the selected value only will be displayed.

 To apply an additional condition based on a value in another column, repeat steps 4 in the
other column.

TIP The custom option in the drop down list will let you to define some advanced criteria,
like filter the list by two values in the same column, or to apply comparison operators
other than Equals etc.

Remove filter

 To remove a filter from one column in a list, click the arrow next to the column, and then
click All.

 To remove filters applied to all columns in the list, on the Data menu point to Filter, and
then click Show All.

 To remove the filter arrows from a list, on the Data menu point to Filter, and then click
AutoFilter.