Unit

Microsoft Excel

Introduction
Introduction to Excel
Excel Spreadsheet Creation and Formulas
Working with Columns and Rows

1
1. INTRODUCTION TO EXCEL
Microsoft Office is a family of Client software , server software , and services developed by Microsoft
. It was first announced by Bill Gates on August 1, 1988, at COMDEX (Computer Dealers’ Exhibition)
in Las Vegas. The first version of Office contained Microsoft Word, Microsoft Excel, and Microsoft
PowerPoint. It’s written by C++ Language Program and available in 102 Languages.

Figure 1 – Bill Gates

Microsoft Excel is an Office Package application that can be used a spreadsheet for creation of small
databases, data management, or for chart creation.
The electronic spreadsheet portion of Excel allows the users to perform sophisticated calculations
and the creation of formulas that automatically calculate answers.
Data management capability allows the manipulation of lists of information such as names, address-
es, inventory items, prices, etc. The information created in an Excel spreadsheet or database can be
used to create Excel charts.

1.1. Launching Microsoft Excel

To start Microsoft Excel from a computer terminal, complete the following:
1) Click the Start button at the bottom left corner of the screen
2) Select the All Apps
3) Click the Excel icon

Figure 1.1 - Launching Microsoft Excel via Windows 10

2
1.2. Microsoft Excel Interface
Excel launches with Start Screen that display Blank workbook and thumbnails of popular templates
as seen in Figure 1.2.

Figure 1.2 - Start Screen Figure 1.3 - Blank Workbook

The Title Bar displays the name of the current workbook and the name of application. By default, a
new blank workbook is called “Book1”, which create with one default worksheet “Sheet 1” (See Figure
1.3). And can create up 255 worksheets in one workbook. A workbook is a collection of individual
worksheets. And a worksheet is a grid composed of 16,384 columns and 1,048,576 rows.

The intersection of a row and column is called a cell. Cells are used to store data entries. Each cell is
referred to by its cell address consisting of the column letter and the row number. For example, the
address of the cell in the first column and the first row of a worksheet is “A1.” The active cell is the
currently selected cell where data can be entering and edited. The active cell has a thick Green border
around it, and its address appears in the Cell Name Box. Only one cell can be active at a time. Refer
to Figure 1.4 for a brief description of each item.

Item Descriptions
Title Bar Contains the title of the workbook and the application
File Menu Grouping of commands to save, open, print and perform other commands common to
all Office applications.
Quick Access Collection of buttons to quickly access regularly used features of the application.
Toolbar
Ribbon Contains many features formerly found in menu structures.
Scroll Bars Move around a worksheet, up or down, left or right.
View Options Excel provides several different ways to view a workbook.
Dialog Launcher A control for accessing more features contained within a group on the Ribbon.
Worksheet Tab Indicates the worksheet name and which worksheet is active.
Zoom Control Controls the magnification of the worksheet.
Cell Name Box Indicates the active cell or the upper, left cell in a range of cells.
Formula Bar Indicates the data value in a cell, or a formula (if present).

Figure 1.4 - Excel Interface and Components

3
 File Menu
Tabs Tell me Tell me
Quick Access Toolbar

Ribbon
Dialog Box
Launcher

Name Box

Formula Bar
Active Cell
Scroll Bar

Worksheet Tabs
Quick Access
Status Bar
Toolbar View Options Zoom Controller

Figure 1.5 - Microsoft Excel Interface

1.2.1. Using the File Menu

Excel provides a collection of commands common to all Office applications that are grouped under
the File Menu. The File Menu replaces the Office Button that was found in previous versions of Mic-
rosoft Office. Clicking the File Menu will open a Menu displaying the commands available. In addition,
Excel can be customized by clicking the Options button at the bottom of the Menu and selecting or
deselecting the options available.

File
Figure 1.6 - File Menu

Figure 1.7 - Commands within the File Menu

1.2.2. Using the Quick Access Toolbar

The Quick Access Toolbar is located above the Ribbon (see Figure 1.5) and contains common, fre-
quently used commands. The commands available from the Quick Access Toolbar are always visible,
which eliminates searching through the Ribbon or tabs.

4
1.2.3. Minimizing and Maximizing the Ribbon
Users can minimize the Ribbon, which will allow more of the document to be visible in the active
window. When minimized, only the tabs will be visible. All the commands within the groups of each
tab on the Ribbon will still be available.

To minimize the Ribbon

1) Click the Ribbon Display Options button drop-down arrow in

Title Bar. The Ribbon Display Option menu will open
2) Select the Show Tab> The Ribbon will be concealed

Figure 1.8 - Ribbon Display Options

To use commands on the Ribbon while minimized:
1) Click the Tab where the command is located. The Ribbon will re-appear
2) Click the tab again and the Ribbon will be concealed
To maximize the Ribbon:
1) Click the Ribbon Display Option drop-down arrow. The Ribbon Display Option menu will open
2) Click the Show Tabs and Commands menu item. The Ribbon will re-appear

1.3. Creating and Saving Workbooks

Once a workbook is opened or created, data can be entered by clicking inside a cell and using the
keyboard. Pressing {Enter} automatically move the insertion point to the beginning of the cell in the
next row, but in the same column.

To create a new workbook while an existing workbook is open:

1) Click the File Menu then click the New button in the available options. The New Workbook avail-
able options open (see Figure 1.9)
2) Click the Blank Workbook button in the Templates section of the dialog box. A new blank work-
book will open in a Microsoft Excel window
3) independent of the existing workbooks. The dialog box will close

To save a new
Figure 1.9 - New Workbook Dialog Box

5
1) Click the Save button on the Quick Access Toolbar
2) Select the Location E.g. Browse, The Save As dialog box opens (See Figure 1.10)
3) Enter a name for the Workbook in the File Name text box
4) Click the Save button to save the file. The dialog box will close

Figure 1.10 - Save As Dialog Box

Using Save As
The Save As option allows users to save a new or existing workbook in a different format other than
the default Excel format.
1) Click the File Menu to display the commands
2) Click Save As and Select the Location E.g. Browse
3) If a different format is desired (such as an Excel 97-2003 Workbook, template or web page for-
mat) then click the Save As Type drop-down arrow (See Figure 1.11) and select the appropriate for-
mat, E.g. Office 97-2003 Documents

Figure 1.11 - Excel Save As Option

1.4. Opening an Existing Workbook
After creating and saving a workbook, the file resides on a disk. To view or edit an existing workbook,
it must first be opened from the disk
To open an existing workbook

1) Click the File Menu

2) Click the Open button in the list of available options.
3)Select location of file to open, E.g. Browse, The Open dialog box
6
 Figure 1.12 - File Menu with Open Option Figure 1.13 - Excel Open Dialog Box

4) Double Click on the document name and, if necessary, click the Open button.

2. Excel Spreadsheet Creation and Formulas

Worksheets consist of rows and columns. To navigate a worksheet, standard navigation keys on the
keyboard can be used.

2.1. Working with Columns and Rows

Columns and rows are used to store information in Excel. Usually columns represent the field com-
mon for each individual entry and rows represent the list of the entries. For example, a column can
contain grades of students on a final exam while rows contain the list of students in the class.

2.1.1. Selecting a Column and Row

Users must select columns and rows to perform functions such as formatting rows or columns,
changing the width of several columns at one time, changing the height of several rows at one time
or inserting and deleting columns or rows. To select a single column, click the desired column header.
To select a single row, click the desired row indicator.

To select both a column and a row as seen in Figure 2.1:

1) Click the desired Column Header
2) Hold the {Ctrl} key and click the desired Row Indicator

Column Header

Row Indicator

Figure 2.1 - Selecting Rows and Columns

7
2.1.2. Inserting a Column
A newly inserted column will appear to the left of the active (selected) column.
1) Right click a cell in the column next to where the column will be inserted
2) Select the Insert command from the menu. The Insert dialog box opens
3) Select the Entire Column option button. (See Figure 2.2)
4) Click the OK button. A new column will be inserted
OR
1) Right click the mouse button on the column header
2) Select the Insert Option

2.1.3. Inserting a Row Figure 2.2 - Insert Option

A newly inserted row will appear above the row that is active (selected).
To insert a row:
1) Right click a cell in the row above where the new row will be inserted
2) Select the Insert command from the menu. The Insert dialog will open. (See Figure 2.2)
3) Select the Entire Row option button
4) Click the OK button. A new row will be inserted
OR
1) Right click the mouse button on the row indicator
2) Select the Insert option

2.1.4. Deleting Columns and Rows

To delete a column:
1) Right click a cell in the column
2) Select the Delete command. The Delete dialog box will open (See Figure 2.3)
3) Select the Entire Column command
4) Click the OK button
OR
1) Right click the mouse button on the column header. A shortcut
menu appears
2) Select the Delete command

To delete a row:
1) Right click a cell in the row Figure 2.3 - Delete Option
2) Select the Delete command. The Delete dialog box will open (See Figure 2.3)
3) Select the Entire Row command
4) Click the OK button
OR
1) Right click the mouse button on the row indicator. A shortcut menu appears
2) Select the Delete command

8
2.1.5. Navigating the Worksheet
Users can use the keyboard to navigate between cells in a worksheet. Commands are listed in Figure
2.4
Item Descriptions
One Cell At A Time Use the {Arrow} keys on the keyboard to move up, down, left, or right. The {Enter} key
can be used to move down, the {Shift+Enter} combination can be used to move up, the
{Tab} key can be used to move to the right, and the {Shift+Tab} combination can be used
to move left.
One Screen at A Time Press the {Page Up} and {Page Down} keys to move up or down one screen at a time. The
(Up and Down) active cell will move up or down as indicated.
One Screen At A Time Press the {Alt+Page Down} keys to move one screen to the right; and press the {Alt+Page
(Left and Right) Up} keys to move one screen to the left. The active cell will move right or left as
indicated.
Figure 2.4 - Navigating the Worksheet

Using the Name Box

The Name Box can be used to navigate to a specific cell. Clicking inside the Name Box will highlight
the contents. Typing in a specific cell address and pressing the {Enter} key will move the active cell
to that address.

Figure 2.5 - Name Box

2.1.6. Entering Data

Data can be text or numeric. Text is defined as any combination of numbers and letters. Numeric
entries are limited to numbers. Numbers can exist as independent values or as values derived from
a formula.
Entering Text
Text will automatically align to the left in a cell. If the length of the text is greater than the width of the
column, it will appear as if it is occupying adjacent cells. When the text is entered into the adjacent
cell, the long text entry appears as if characters have been deleted. The characters have not been
deleted and will appear if the width of the column is adjusted to accommodate the entry.
To enter text:
1) Click in the cell in where the text will be entered
2) Type the text into the cell. The text will also appear in the Formula bar
3) Press {Enter} or the {Tab} key to exit the cell

9
 Entering Text
Text will automatically align to the left in a cell. If the length of the text is greater than the width of the
column, it will appear as if it is occupying adjacent cells. When the text is entered into the adjacent
cell, the long text entry appears as if characters have been deleted. The characters have not been
deleted and will appear if the width of the column is adjusted to accommodate the entry.
To enter text:
1) Click in the cell in where the text will be entered
2) Type the text into the cell. The text will also appear in the Formula bar
3) Press {Enter} or the {Tab} key to exit the cell

Entering Numbers
Numbers are automatically aligned to the right in a cell. To enter a negative value, type a minus (-)
sign before the number or enclose the number in parenthesis. Decimals are indicated by typing a
period (.) and typing numeric values after. Additionally, dates are considered numeric values that can
be manipulated through mathematical calculations.
To enter number:
1) Click in the cell where the data will be entered
2) Type the number into the cell. The number will also appear in the Formula bar.
3) Press {Enter} or the {Tab} key to exit the cell

Entering a Line Break Inside a Cell

Users may want to add several lines of data into a cell. It is possible to control the line breaks for mul-
tiple-line headings or labels in Excel, similar to Microsoft Word.

Figure 2.6 - Cell with Line Break

To enter a line break inside a cell:
1) Click the cell where the label or heading will be added
2) Type the first line of information
3) Press the {ALT+Enter} key combination and Type the second line
4) Repeat the steps 3 and 4 if there are additional lines that need to be entered
5) Press the {Enter} key when finished

Cell Ranges
A group of selected cells is call a range. The range is identified by the address of the first and last cell.
To select a range:

1)Click on the first cell in the range to be selected

2) Drag the pointer across the range of cells to select
3) Release the mouse button. The range of cells will be highlighted

10
OR
1) Click on the first cell in the range to be selected
2) Hold down the {Shift} key and click in the last cell of the range

Figure 2.7 - Adjacent Range of Cells Figure 2.8 - Nonadjacent Range of Cells

It is also possible to use the combination of keyboard and mouse to select nonadjacent ranges. To
select nonadjacent cells, select the first group of cells, hold the {Ctrl} key down on the keyboard and
select the nonadjacent group of cells.

Entering Data in a Range

Select the range in which data will be entered by following the previous steps. The range of cells
must be highlighted. In an adjacent range of cells, the data being entered will automatically begin at
the first cell in the range. To begin entering data into a cell other than the first in the range, press the
{Tab} key to move to the right; press the {Shift+Tab} keys to move to the left; press the {Enter} key
to move down or press the {Shift+Enter} keys to move up.

Editing Cell Data

Once data has been entered into a cell, the contents of the cell can be modified by editing, deleting,
copying or pasting. If data is entered into a cell that already has an entry, the new entry will replace
the old one. Users do not have to be in edit mode.
To edit cell entries:
1)Double-click a cell. Excel will shift the cell into edit mode with the content appearing in the
Formula bar
2) Cell can be edited in the cell itself or in the Formula bar

Deleting Cell Data

To delete cell data:

1) Select the cell or cell range to delete

2) Press the {Delete} key on the keyboard

Copying and Moving Cell Data

In addition to typing data into cells, users can also copy or move data from a cell or range of cells to
another. Copying and pasting cell data leaves the data in the original cell(s) and duplicates it into the
target cell(s). Cutting and pasting cell data moves the data from the original cell(s) and places it into
the target cell(s).

To copy and paste:

11
1) Select the cell or the cell range to copy
2) Select the Home tab on the Ribbon
3) Click the Copy button in the Clipboard group. A blinking marquee appears around the selected
cell or range
4) Select the target cell or range into which to paste the copied cell content
5) Click the Paste button in the Clipboard group
6) Press the {Esc} key to remove the blinking marquee

Figure 2.9 - Cell with Marquee Figure 2.10 - Past Button

To cut and paste:

1) Select the cell or the cell range to cut

2) Click the Cut button in Clipboard group. A blinking marquee appears around the selected cell
or range
3) Select the target cell or cell range into which to paste the cut cell contents
4) Click the Paste button in the Clipboard group
Undo and Redo
If the user makes a mistake or desires to cancel an action just performed, it is possible to undo or redo
it. The Undo feature allows undoing the results of a previous command or action.
To undo an action:
1) Click the Undo button in the Clipboard group. The user may click the Undo button as many
times as needed in succession
Once the Undo feature is used, the Redo feature becomes available. It restores the results of actions
that were undone with the Undo feature.
To redo an action:
1) Click the Redo button in the Clipboard group

2.2. Formatting a Worksheet

Formatting the characters in a worksheet can enhance the appearance of the worksheet and provide
a more professional appearance to the entire worksheet.

2.2.1. Live Preview

In the case of character formatting, Live Preview applies to the Font typeface, Font size, and Font col-
or. Live Preview allows the user to visualize how a different typeface, size, or color will appear within
the document. Live Preview temporarily displays any changes to a selected object or selected text in
the document without actually changing it

12
2.2.2. The Mini Toolbar
Like Microsoft Word, Excel has the Mini Toolbar. The Mini Toolbar contains frequently used formatting
commands and appears in a semi-transparent mode whenever text is selected for formatting. Mov-
ing the mouse over the toolbar activates it and makes the options available for use.

Figure 2.11 - The Mini Toolbar

2.2.3. Character Formatting

Character formatting enhances the appearance of text, and includes font typeface, font size, font
style, and font color. Character formatting is applies using the features on the Home tab of the Rib-
bon in the Font group
Font Typeface Font Size

Font Style Font Color

Figure 2.12 - Font Group

Changing Font Typeface

A font typeface is defined as a group of characters sharing similar type attributes. The default font
typeface for new documents in Excel is Calibri
To change the font typeface for selected text:
1) Select the text to change
2)Click the Font Typeface drop-down arrow in the Font group. A list of available fonts will appear
3) Select the desired font name to change the font typeface

Changing Font Size

Font size refers to the height of printed text on a page and is measured in units called points. There
are 72 points in one inch. The higher the number entered for the font size, the larger the text will be.
The default font size for new documents in Excel is 11 points.
To change the font size for selected text:

1) Select the text to change

2) Select the Home tab on the Ribbon
3) Click the Font Size drop down arrow in the Font group. A list of available font sizes will appear
4) Select the desired font size

Font sizes are not listed in increments of one point. If a desired font size is not listed in the Font Size
drop-down list, click the number instead of the drop-down arrow, manually enter the desired font
size and press the {Enter} key to apply the new font size.

13
Changing Font Style
Font style refers to the font type enhancement. Figure 76 provides examples of available font styles
Button Font Style Example
B Bold Example of a Bold text
I Italic Example of Italicized text
U Underline Example of Underlined text

Figure 2.13 - Font Styles

Changing Font Color
Two aspects of font color can be styled. The background color of the text (also known as the Text
Highlight Color) can be changed from the default color of white and the color of the font itself can be
changed from the default color of black.
To change the Text Highlight Color for selected text:

1) Select the text to change

2) Click the Text Fill Color drop-down arrow on the Text Fill Color button in
the Font group. The available highlight colors will appear as shown in Figure 2.14
3) Select the desired color
Figure 2.14 - Fill Color

To change the font color for selected text:

1) Select the text to change
2) Click the Font Color drop-down arrow on the Font Color button in the Font
group. The available font colors will appear as shown in Figure 2.15
3) Select the desired color

2.2.4. Resizing and Formatting Cells Figure 2.15 - Font Color

The user can change the size of the columns and rows to make them more appropriate. Formatting
cells can improve the appearance of the worksheet.
Changing Column Width
A cell entry contains up to 32,767 characters. By default, column width is 8.43 characters. If the user
enters a value that does not fit within the column width, characters will “spill over” into the next
column cells and will appear to occupy the cell(s) to the right. A number that does not fit within a
column is displayed as a series of pound signs (######). To accommodate the length of data in a
cell, change the width of the column.

To change a column width:

1)Position the mouse pointer between column headers. The pointer will change to a two-direction
arrow with a vertical line through it
2) Hold down the left mouse button and drag the line to the right or left
To set the width of the column to fit the widest cell:
1)Move the pointer between the column header of the column to adjust and the column header im-
mediately on the right
2)Double-click the left mouse button

14
Changing Row Height
Row height is automatically adjusted to accommodate larger font sizes, but users may also adjust
them manually.

To adjust a row height:

1) Position the mouse pointer between row headers. The pointer will change to a two-direction
arrow with a horizontal line through it
2) Hold down the left mouse button and drag the line up or down
2.2.5. Changing Alignment

To change the alignment of the text in the cell(s)

1) Select the cell(s) to change
2) Click the Left , Center , or Right align buttons in the Alignment group

2.2.6. Creating Cell Borders

Adding a border to selected cells or cell ranges is a convenient way to dis-

tinguish those cells from others.

To select cell borders:

1) Select the cell or cell range around which to place a border.

2) Click the Borders drop-down arrow in the Font group.
3) Select the desired border. Figure 2.16 provides an example of the Border
drop-down

Figure 2.16 - Border Drop Down Menu

2.2.7. Formatting Numbers
Cells can be formatted to change the appearance of a number in a cell.
Formatting modifies the appearance of the worksheet. Using formatting, the user can add features
such as dollar signs ($), percent symbols (%), commas (,), and the fixed number of decimal places
that will be displayed.

To format a currency value:

1)Select the cell or range of cells to format.

Figure 2.17 - Accounting Number Format

15
2) Click the Accounting Number Format button in the Number group

If currencies other than US dollars are needed, click the Accounting Number Format drop-down ar-
row and select a currency from the list of options as seen in Figure 2.17.
To format a cell in a percent style:

1) Select the cell or range of cells to format

2) Click the Percent Style button ٪ in the Number group

To format a cell in a comma style that denotes units of thousands:

1) Select the cell or range of cells to format

2) Click the Comma Style button , in the Number group

To increase the number of decimal places in a number:

1) Select the cell or range of cells to format

2) Click the Increase Decimal button 000 in the Number group

To decrease the number of decimal places in a number:

1) Select the cell or range of cells to format

2) Click the Decrease Decimal button 000 in the Number group

2.3. Using Simple Formulas

Excel allows users to perform sophisticated calculations and create formulas. The advantage of using
formulas is that when data in the worksheet changes, all the formulas are recalculated and the re-
sults displayed automatically. This feature assists in developing budgets, forecasting models, creating
sales plans, estimating marketing projections, calculating invoices, generating banking statements,
or any other applications that may involve formulas and functions. Formulas begin with an equal sign
(=) because they contain cell addresses. The equal sign prevents Excel from interpreting the formula
as text, since text addresses begin with letters.
Using AutoSum
Excel has built-in functions that are shortcuts for formulas. The most commonly used function is the
Sum function, which calculates the total of the values in a range of cells. The AutoSum button in the
Editing group on the Home tab of the Ribbon is a shortcut to enter the formula in the active cell. Au-
toSum is an easy way to sum values in a row or column of a worksheet.
When the user clicks the AutoSum button, a suggested range of cells for the function is selected with
a blinking colored border around it, and the Sum formula appears in the active cell similar to Figure
2.18.
Depending on the position of the active cell, the suggested range of val-
ues can be either a row or column. If the active cell is at the bottom of a
column, the column of values above the active cell is selected; if the active
cell is at the end of a row, the row of values to the left of the active cell is
selected. If necessary, the selected range can be changed by dragging the
selection borders.
Figure 2.18 - Cell Selected by Auto Sum Function
16
To use the AutoSum function:
1) Click the cell where the total of values needs to be entered
2) Click the AutoSum button in the Editing group
3) To accept the range of cells and perform the calculation, click the AutoSum button again, or press
the [Enter] key on the keyboard. The total of the selected values appears in the active cell and the
formula Appears in the Formula Bar
For the example shown in Figure 2.18, the Formula Bar would display =SUM (A1:A6 when the cell
containing the formula is selected
Entering Formulas
To enter a formula, it is necessary to click the cell where the calculated value will appear.
To enter formulas manually:
1) Select the cell where the formula will be entered
2) Type [=SUM (first cell address in the range: last cell address in the range)]
3) Press the [Enter] key on the keyboard to perform the calculation
The two cell addresses are separated by a colon. Example: [=SUM (A1:A6)]
Using Basic Functions
Clicking the AutoSum drop-down arrow will display the most commonly
used functions. To display all functions built into Excel, click the menu item.
The Insert Function dialog box will open and all functions will be accessible
to the user. Using either of these options will enter any function. The user
need only select the cells that will be used by the function to perform the
calculation
Figure 2.19 - Commonly Used Function
3. Spreadsheet Review and Printing
3.1. Using Page Setup Features
The features on the Layout tab of the Ribbon in the Page Setup group can be used to manage work-
sheet attributes such as the orientation of the worksheet, the size of the margins, and whether or not
gridlines appear when the worksheet is printed.

Figure 3.1 - Page Setup Group

3.1.1. Changing Page Orientation
The user can change the orientation of a worksheet so that it prints either vertically (portrait orien-
tation) or horizontally (landscape orientation) on a page. In portrait orientation (the default), the
shorter edge of the paper is at the top.

1)To change the page orientation

2) Select the Layout tab on the Ribbon
3) Click the Orientation drop-down arrow in the Page Setup group
4) 4. Select either the Portrait or the Landscape option

17
OR

1) Click the Page Setup dialog box launcher. The Page Setup dialog box opens
2) If necessary, click the Page tab
3) In the Orientation section, select either the Portrait or Landscape option button
4) Click the OK
3.1.2. Changing Margins
The margins define the printed area on the page. They control the distance between the edge of the
paper and the data on the page. Larger margins reduce the printed area. If the worksheet is smaller
than the print areas on the page, the Center on Page section feature can be used as described below
to center the worksheet between horizontal and vertical margins.

Figure 3.5 - Page Setup Dialog Box

Figure 3.4 - Margin Drop Down Menu

To change margins:
1) Click the Margins drop-down arrow in the Page Setup group
2) Select a set of margins from the options available
3) If the desired margins are not available, click the Custom Margins option. The Page Setup dialog
box will open
OR
1) Click the Page Setup dialog box launcher. The Page Setup dialog box opens
2) If necessary, click the Margins tab
3) Click inside the spin boxes and type in the margins manually, or use the spin box arrows to set the
margins
4) Click the OK button

3.1.3. Printing with Gridlines

By default, Excel will not print the gridlines on the worksheet. Printing the gridlines may make the
spreadsheet easier to read because they visibly separate rows and columns. Excel gives the option to
print a worksheet with or without gridlines.
To print with gridlines
1) Click the Print check box in the Gridlines section in the Sheet Options group (See Figure 3.6)

18
OR
1) Click the Sheet Options dialog box launcher. The Page Setup dialog box opens
2) If necessary, Click the Sheet tab
3) Check the Gridlines check box in the Print section
4) Click the OK button

Figure 3.6 - Print Check Box

Figure 3.7 - Page Setup Dialog Box

3.2. Printing Worksheets

After a worksheet is completed, it can be previewed before being printed. Printing options are ac-
cessed using the File Menu and Print button.
3.2.1. Printing the Current Worksheet
To print one copy of the worksheet that is displayed:
1) Click the File Menu
2) Select Print button
3) Click the menu item. The worksheet will be sent to the printer and printed. Or you can define the
specific page number in Page’s section
3.2.2. Printing Options
Multiple printing options are available when using the Print dialog box. Using the Print dialog box
allows users to print a range of cells, specific pages or a range of pages, and print multiple copies and
etc…

To print a selected range of cells:

1) Select the range of cells to be printed

2) Click the File Menu
3) Click the Print button. The Print Option open
4) Click the Selection option under the settings
5) Click the OK button

Figure 3.8 - Print Selected Parts

To print a specific pages or a range of pages:

1) Click the File Menu
2) Click the Print button. The Print Option open
3) Enter the starting page number of the page range in the From: spin box section and Enter the
ending page number in the To: spin box under Setting section
4) Click the OK button

19
To print multiple copies:
1) Click the Print button as described above
2) Enter the number of copies to print in the Copies: spin box in the Copies section.
3) Click the OK button

Selecting Printers:
A printer can be selected by clicking the Printer drop down arrow in the Printer section of the Print
options and selecting a printer from the list from which you want to print.

Figure 3.9 - Print Option

3.3. Help Option/ Closing a Workbook

Excel Help Function
Excel has an extensive Help function to answer any questions the user may have regarding the com-
mands and functions of Excel.
The Help function can be activated from the Excel user interface by clicking the Microsoft Excel Help
button located in the upper right corner of the File Menu. It can also be activated from any open
dialog box by clicking the Help button which is located in the upper right corner of all dialog boxes.

Closing A Workbook

When finished working on a workbook, the user can close it or exit the Excel program.
The Close button located on the right side of the Title bar will close the Excel application.
The Close button located on the right side of the File Menu will close only the file.

Close the Excel

Close the Current File

Figure 3.11 - Close Buttons

20
4. Advanced Microsoft Excel
4.1. Formatting Cells in a Worksheet
Formatting can be used to enhance the appearance of a worksheet.
A worksheet can be formatted by changing the appearance of the
text, numbers, and cells; either one at a time or simultaneously. Ap-
plying the appropriate formatting conveys a professional image.

Figure 3.8 - Print Selected Parts

4.1.1. Formatting with Special Effect
Formatting with special effects includes rotating, wrapping, shrinking, merging, and also changing
the indentation of text. Besides special effects, there are predefined and conditional formatting op-
tions for worksheet data
Rotating Text in a Cell
Text can be displayed in horizontal or vertical orientation. To change the orientation of text, specify
a display angle. This is often used to draw attention to text. Text can be rotated in one-degree incre-
ments up to a 90-degree angle.

To change the orientation of text in a cell:

1) Select the cell or range of cells to rotate

2) Click Home tab
3) Click Orientation from Alignment tab
4) Under the Orientation section, Select the angle of rotation
Figure 4.2 - Orientation & Text Control
OR

1) Click Alignment dialog box launcher from Home tab

2) Under the Orientation section, enter the angle of rotation in the degree spin box

Figure 4.3 - Format Cells Dialog Box

Wrapping Text in a Cell
When text exceeds the length of the cell, it may display in the adjacent cell. In this case, Excel can
wrap the text into one cell without adjusting the column width
To wrap a text in a cell:
1) Select the cell or range of cells to wrap
2) Select the Home tab on the Ribbon
3) Click the Alignment dialog box launcher. The Format Cells dialog box opens (see Figure 4.3)
4) Select the Alignment tab
5) Under the Text Control section, Check the Wrap text check box
6) Click the OK button
21
Shrinking Text in a Cell
Besides wrapping, Excel can decrease the size of the text allowing it to fit into a cell without spilling
over to the next cell.
To shrink text to fit in a cell:
1) Select the cell or range of cells to shrink the text
2) Select the Home tab on the Ribbon
3) Click the Alignment dialog box launcher. The Format Cells dialog box will open
4) Under the Text Control section, check the Shrink to fit check box
5) Click the OK button

Merging Multiple Cells into One Cell

Often a heading or title in Excel appears to occupy several cells, when in fact it is only occupying one.
Without having to alter the width of a cell, cells can be combined or merged with adjacent cells to
allocate a larger area in order to accommodate the titles.

To merge a range of cells into one cell:

1) Drag to select the cells to merge
2) Select the Home tab on the Ribbon Figure 4.4 - Merge & Center
3) Click the Alignment dialog box launcher. The Format Cells dialog box will open
4) Under the Text Control section, Check the Merge cells check box
5) Click the OK button
Using Cell Styles
Formatting data using predefined styles provides a consistent look to the worksheet and can be use-
ful for visually organizing data. Cell styles provide different colors, shadings, and font effects to the
cells where the style is applied.

To apply a Cell Style:

1) Drag to select the range to format
2) Select the Home tab on the Ribbon
3) Click the Cell Styles drop-down arrow from Styles group
4) Select the style from the drop down menu

Figure 4.5 - Cell Styles Drop Down Menu

Applying Conditional Formatting

There may be times when it is necessary to format cells in a particular style if the cell content meets
certain conditions. For example, users may want to bold and italicize all values greater than 100 in
order to emphasize them. Excel can format the numbers to the user’s specification based on the
contents of the cells.

22
To apply Conditional Formatting:
1) Drag to select the range to format
2) Select the Home tab on the Ribbon
3) Click the Conditional Formatting drop-down arrow from Styles
group
4) Select the type of condition desired. Each type of condition will re-
quire the user to enter different parameters to specify the condition and

Figure 4.6 - Conditional Formatting

Figure 4.7 - Example of Conditional Formatting

The Highlight Cells Rules option applies logical math operators (>, <, =) as well as text and date search
criteria to the range of cells selected by the user. The Top/Bottom Rules option applies criteria to the
selected cells based on the relative rankings of the data in each cell. Even though the menu specifies
“100 Items” or “100%” for the options available, the user is able to customize the actual percent or
number of items to be considered when defining the condition. See Figure 4.8.

Figure 4.8 - Example of Highlight Cells Figure 4.9 - Example of Data Bar Option

Data Bars are the first of the three new conditional formats incorporated into Excel. Data Bars ex-
amine the range of values in the selected cells and apply a visual format in each cell based on the
relative weight of the cell compared to the maximum value in the cells selected. Figure 4.10 shows the
options available when the Data Bars option is selected. Figure 4.9 shows a group of cells where the
Green Data Bar has been applied

Figure 4.10 - Data Bar Options Figure 4.10 - Data Bar Options

The third of the new conditional format options are the Icon Sets. Icon Sets associate sets of icons
with data values in the selected range of cells. Each value has its own symbol. Figure 4.11 shows the
options available when the Icon Sets option is selected. Figure 4.12 shows a group of cells where the
5 Ratings Icon Set has been applied.

Figure 4.12 - Cells with 5 Ratings Icon Set Applied

23
4.2. Managing Worksheets
Worksheets in previous versions of Excel such as Excel 2003 contained 65,536 rows and 256 col-
umns, with a single workbook containing up to 1,024 worksheets. Excel 2016 and beyond greatly
expand the amount of information that can be stored in a workbook. Each workbook is still limited
to 1,024 worksheets, but each worksheet is now capable of containing 16,384 columns and 1,048,576
rows. Consequently, navigating within a worksheet and between various worksheets can be quite
difficult. To simplify this process, Excel provides freeze panes, scroll bars, and navigation buttons.
[Link] Large Worksheets
Occasionally a worksheet is so large that it is difficult to view the column and row headings and all the
data at the same time because the row and column headings scroll out of view. To solve this problem,
Excel allows freezing worksheet titles in panes. Freezing panes prevents the row and column head-
ings from scrolling out of view while navigating the worksheet.
To freeze panes:
1) Select the View tab on the Ribbon
2) Click the Freeze Panes drop-down arrow
3) Select appropriate option from the menu

To unfreeze panes:
1) Select the View tab on the Ribbon
2) Click the Freeze Panes drop-down arrow Figure 4.13 - Freeze Panes

3) Select the Unfreeze Panes option

Figure 4.14 - Unfreezing Panes

4.2.2. Working with Multiple Worksheets

Excel provides various features that allow easy access to and management of multiple worksheets.
These features allow navigating to view the content of each worksheet and also modify its contents.
Inserting Worksheets
By default, Excel provides One worksheets (Sheet 1) in a single workbook. Users may create addition-
al worksheets to accommodate large volumes of data. New worksheets are inserted by clicking the
New Sheet on the right side of the last worksheet in the workbook.
New Sheet

Figure 4.15 - Inserting New Sheet

Renaming Worksheets
If a workbook contains several sheets, the sheet names (on the sheet tabs) are important for iden-
tification purposes. When renaming worksheets, it is best to assign a name that is relevant to the
information it holds.

To rename a worksheet:
1) Double-click the Sheet Tab to rename. The sheet name will be highlighted
2) Enter the new name, and press the {Enter} key
OR
1) Right-click the Sheet Tab Figure 4.16 - Renaming Sheet

2) Click Rename
3) Enter the new name and press the {Enter} key

24
Navigating Between Worksheets
The active worksheet is the worksheet that is currently displayed. A worksheet can be displayed by
clicking its tab; however, if there are too many worksheets, the user may not be able to view every tab
at once. For example, in a workbook that contains worksheets for every month of a year, the tabs for
the last few months of the year may be hidden.
Display the Next Sheet Sheet Tabs

Figure 4.12 - Cells with 5 Ratings Icon Set Applied

Display the Previous Sheet Insert New Sheet

Excel provides the worksheet navigation buttons listed in Figure 4.17 to navigate between the work-
sheets tabs.
Selecting Worksheets
There is always an active worksheet in any given workbook. For instance, in Figure 4.17 the “January”
worksheet is the active sheet. However, users can activate or select more than one sheet in a single
workbook. Activating several worksheets is useful when it is necessary to format or print several
worksheets simultaneously rather than formatting or printing them individually. Excel offers two op-
tions to select both single and multiple worksheets. Depending on the preference, users can select
adjacent or non-adjacent worksheets.
Single worksheets are activated by clicking the worksheet name tab. The active sheet will appear
white rather than gray.

To activate the shortcut menu:

Sheet Navigation Buttons

Figure 4.18 - Sheet Navigation Buttons

Figure 4.19 - Selection through Shortcuts

1) Right-click in the area where the worksheet navigation buttons are located. A list of all work-
sheets will appear
2) Select the worksheet to activate. The list will disappear and the chosen worksheet will activate

The key combinations for navigating through different worksheets are:

1) {Ctrl + Page UP}: activates the previous sheet (if there is one)
2) {Ctrl + Page Down}: activates the next sheet (if there is one)
To select multiple adjacent worksheets:

1. Click the tab of the first worksheet to select. The worksheet will appear, and its tab is selected
2. Hold down the {Shift} key and click the tab of the last adjacent worksheet to select. All of the
worksheets in between are now selected
3. Click on any unselected tab to deselect the worksheets

To select multiple non-adjacent worksheets:

25
 1. Click the tab of the first worksheet to select. The worksheet will appear, and its tab is selected
2. Hold down the {Ctrl} key and click the tab of the next worksheet to select
3. Click another non-adjacent tab to select while still holding down the {Ctrl} key. The non-adjacent
tabs are now selected and active
4. Release the {Ctrl} key and click on any unselected tab to deselect the worksheets
4.2.3. Working Between Worksheets
It is important that the workbook demonstrate order, relevance, and a proper logical flow for others
to easily understand and navigate between the worksheets. To achieve this purpose, use methods
such as copying and moving worksheets within the workbook.
Copying a Worksheet
When working with data (monthly expenses or income for example), the user may find a series of
information that is constant for all the worksheets. Excel allows copying a worksheet to eliminate
retyping. When a worksheet is copied, all the data, formulas, attributes, and page layouts are also
copied.

To copy a worksheet:

1. Select the tab of the worksheet to copy.

2. Hold down the {Ctrl} key and click and hold the mouse pointer on the tab of the sheet to be cop-
ied. A paper symbol with a “+” sign on it and a small black triangle pointing down appears. The “+”
indicates that the sheet is copying. The black triangle indicates the location of where the worksheet
will be copied to.

Figure 4.20 - Copying a Worksheet

3. Drag the worksheet to the desired location and release the mouse.

Moving a Worksheet
It is also possible to rearrange the order of worksheets by moving the sheet to the desired position.
Unlike copying, moving does not replicate the sheet but simply moves the sheet from one location
into another

To move a worksheet:

1. Select the tab of the worksheet to move

2. Click and hold down the mouse button on the worksheet tab. A small black triangle appears indi
cating the position where worksheet will be moved if the mouse is released.
3. Drag the selected worksheet tab to the desired location.
Copy Data between Worksheets
When working with Excel data, the user may copy selected information from one worksheet and
paste it into another. When making a copy of data, Excel copies not only the data but also its accom-
panying formulas and formatting attributes.

26
To copy data:

1. Highlight the data to copy. The highlighted area will be shaded

2. Select the Home tab on the Ribbon
3. Click the Copy button in the Clipboard group. A blinking marquee sur-
rounding the range of cells will appear
4. Select the destined worksheet to which the information will be copied
5. Click the first cell in the range where the copied data should be pasted
6. Click the Paste drop-down arrow from Clipboard Group
7. Select Paste from the menu
[Link] the original worksheet tab and press the {Esc} key. The blinking
marquee will be removed
Figure 4.21 - Past Drop Down Menu

4.3. Creating and Modifying Charts

Creating charts is one of the most powerful features in Excel. A chart uses values in a worksheet to
create a graphical representation of their relationship. With Excel charts, the user can summarize,
highlight, or reveal trends in the data that might not be obvious when simply looking at the numbers.
4.3.1. To create a chart:
1) Highlight the data that will be charted. The highlighted area will be shaded. (See Figure 4.22)
2) Select the Insert tab on the Ribbon
3) Click the chart category drop-down arrow for the appropriate chart sub-type in the Charts
group. (See Figure 4.23)
4) Select the chart sub-type from the drop-down menu (See Figure 24). The chart will be created
and embedded in the active worksheet (See Figure 4.25)

Figure 4.23 - Chart Group Figure 4.22 - Simple Data Used to Create Chart

Figure 4.24 - Column Drop Down Menu

Chart Title

Point Area

Value (Y) Axis Gridlines

Category (X) Axis

Legend

Figure 4.25 - Chart Created from Data in Figure 4.22

27
4.3.2. Identifying Chart Objects
Excel charts contain several elements called objects. Refer to Figure 4.26 for chart object descrip-
tions.
Chart Object Descriptions
Chart Area The entire area within the chart borders including the chart and all related elements.
Plot Area The area in which Excel plots data.
Category Axis The axis that contains the categories being plotted. It is usually the horizontal axis.
(x-axis)
Value Axis The axis that contains the values being plotted. It is usually the vertical axis.
(y-axis)
Chart Title Text describing the chart that is automatically centered and placed at the top of
the char.
Legend Describes the data series being plotted.
Gridlines Lines that extend from an axis across the plot area to help guide the eye from the
data point to its corresponding value.
Figure 4.26 - Identifying Chart Objects
4.3.3. Chart Tools Contextual Tabs
Users can modify a chart any time after it is created. The chart must be activated by clicking or se-
lecting it before attempting modifications. The two Chart Tools contextual tabs contain the tools nec-
essary to modify and enhance the chart. Contextual tabs are not visible or activated until the chart is
activated. Figure 4.27, Figure 4.28 identify all of the groups in each of the contextual tabs.

Figure 4.27 - Design Contextual Tab Groups

Figure 4.28 - Format Contextual Tab Groups

Changing Chart Type

Users can change the chart type any time after a chart has been created without having to recreate

To change the chart type:

1) Click anywhere on the chart to activate it

2) Select the Design contextual tab on the Ribbon
3) Click the Change Chart Type button in the Type group.
The Change Chart Type dialog box opens
4) Find and select the new chart type.
5) Click the OK button to change the chart type

Figure 4.29 - Chart Types

28
Adding a Chart Title
By default, charts are not created with titles. The title object must be added after the chart is created.
To add a title to the chart:

1) Click anywhere on the chart to activate it.

2) Select the Design contextual tab on the Ribbon.
3) Click the Add Chart Element drop-down arrow in the Chart Lay-
out group.
4) Select the appropriate option from Chart Title drop-down menu.
5) Type the chart title into the text box that appears on the chart.

Figure 4.30 - Adding Chart Title

Moving a Chart
When charts are created, they are automatically embedded in the worksheet where the chart data is
located. Charts can be moved to their own worksheet or to a different worksheet.
To move a chart:

1) Click anywhere on the chart to activate it.

2) Select the Design contextual tab on the Ribbon.
3) Click the Move Chart button in the Location group. The Move
Chart dialog box will open. Figure 4.31 - Move Chart Dialog Box

4) Select the desired location for the chart and click the OK button.
Adding and Removing Gridlines
Major horizontal gridlines are automatically incorporated into a chart when it is created. Both hori-
zontal and vertical gridlines (major and minor) can be added, removed, or modified at any time.
To modify Vertical gridlines:

1) Click anywhere on the chart to activate it.

2) Select the Layout contextual tab on the Ribbon.
3) Click the Add Chart Element drop-down arrow in the Chart Layout group
4) Select the appropriate option from Gridlines drop-down menu

Figure 4.32 - Gridlines Option

Modifying the Chart Legend

When a chart is initially created, the chart legend is automatically placed on the right side of the
chart. Users are given the capability of relocating the legend to a different position and formatting it.
To modify the chart legend:

1) Click anywhere on the chart to activate it.

2) Select the Layout contextual tab on the Ribbon.
3) Click the Add Chart Element drop-down arrow in the Chart Layout group
4) Select the appropriate option from Legend drop-down menu

Figure 4.33 - Legend Option

29
4.4. Using Formulas and Functions
Formulas are used to perform calculations on values entered into the cells of a worksheet. A formu-
la consists of values or text, arithmetic operators, cell addresses, and worksheet functions used to
calculate a value in a cell. The major advantage of Excel lies in the capability of the application to
automatically adjust and recalculate a formula when the value of a cell used in the formula changes.
Components Descriptions
Operators Tells Excel which math operation or comparison to perform.
Cell Individual cells (A1, A2) and cell ranges (A1:D10).
Reference/
address
Values or text The content of the cells.
Worksheet SUM, AVERAGE, COUNT, MAX, etc.
functions
Figure 4.34 - Formula Components
Excel provides a variety of operators and worksheet functions for creating simple formulas. In addi-
tion to the operators listed in Figure 4.35, Excel has several built-in functions that enable the user to
perform more operations.
Operator Name
+ Add
- Subtract
* Multiply
/ Divide
() Controls the order of operation. Calculations within parentheses are performed first

% Converts the number into a percentage. For example, when typing 0.10, Excel read the value at %10

= Logical comparison (equal to)

> Logical comparison (greater than

< Logical comparison (less than)

>= Logical comparison (greater than or equal to)

<= Logical comparison (less than or equal to)

<> Logical comparison (not equal to)

Figure 4.35 - Mathematical and Logical Operators

30
No Function Name Function Explanation Example Output

1 SUM/TOTAL The SUM function adds values. =SUM(2,5,3,7)

17
You can add individual values,
cell references or ranges or a mix
of all three.
2 MINIMUM MIN will return the minimum =MIN(2,5,3,7)
2
value in a given list of arguments
3 MAXIMUM MAX will return the maximum =MAX(2,5,3,7)
7
value in a given list of arguments
4 AVERAGE Returns the average of its =AVERAGE(10,20,30)
20
arguments, including numbers,
text, and logical values
5 COUNT Counts how many numbers are =COUNT(10,20,30)
3
in the list of arguments
6 COUNTBLANK Counts the number of blank cells =COUNTBLANK(A1:A10)
2
within a range
7 MEDIAN Returns the median of the given =MEDIAN(2,5,3,7)
4
numbers
8 NOW Returns the serial number of the =NOW() 7/18/2024 3:44

current date and time

9 TIME Return the sequential serial =TIME(14, 30, 0)
2:30 PM
number that represents a
particular time.
10 SECOND Converts a serial number to a =SECOND(A10)
36
second
11 MINUTE Converts a serial number to a =MINUTE(A10) 20

minute
12 HOUR Converts a serial number to an =HOUR(“14:30:00”)
2:30 PM
hour
13 MONTH Converts a serial number to a =MONTH(A10)
04
month
14 DATE Use this function to return the =DATE(2024,04,24) 2024.04.24
sequential serial number that
represents a particular date.
15 REPLACE Replaces characters within text =REPLACE(“TEACHING”,6,3,”ER”) TEACHER

16 UPPER Converts text to uppercase =UPPER(“mazar”)

MAZAR

17 LOWER Converts text to lowercase =LOWER(“MAZAR”)

mazar

18 ROW Returns the row number of a =ROW(A10)

10
reference
19 COLUMN Returns the column number of a =COLUMN(A10)
A
reference

31
 20 LEN Returns the number of characters in a =LEN(“Ahmad”) 5
text string
21 ODD Rounds a number up to the nearest odd =ODD(10) 11
integer
22 EVEN Rounds a number up to the nearest =EVEN(11) 12
even integer
23 ISBLACK Returns TRUE if the value is blank =ISBLACK(A10) TRUE

24 ISNUMBER Returns TRUE if the value is a number =ISNUMBERA10) FALSE

25 ISTEXT Returns TRUE if the value is text =ISTEXT(A10) TURE

26 POWER Returns the result of a number raised to =POWER(100,4) 10000

a power
27 SQRT Returns a positive square root. =SQRT(225) 15

28 EXACT Compare two text strings (TRUE or =EXACT(“ALI”,”AHMAD”) FALSE

FALSE)
29 TRIM Removes all spaces from text except for =TRIM(“AB CDE”) AB CDE
single spaces between words
30 ROMAN Converts a number to a Roman numeral =ROMAN(542) DXLII
as text.
31 PRODUCT Returns the product of numbers =PRODUCT(10,100) 1000
provided as arguments (Multiplies its
arguments).
32 CONCATENATE Joins several text items into one text =CONCATENATE(“A”,”B”,”C”) ABC
item
33 PROPER The Excel PROPER function capitalizes =PROPER(“afghan”) Afghan
words given text string. Numbers and
punctuation are not affected.
34 MID MID function extracts a given number of =MID(“AFGHANISTAN”,3,5) GHANI
characters from the middle of a supplied
text string.
35 WIDTH Returns the width of characters in a text =CELL(A10) 33
string
36 RIGHT Returns the last character or characters =RIGHT(“AFGHANISTAN”,6) NISTAN
in a text string, based on the number of
characters you specify.
37 LEFT Returns the First character or =LEFT(“AFGHANISTAN”,7) AFGHANI
characters in a text string, based on the
number of characters you specify.
38 OSVERSION Returns information about the current =INFO(“OSVERSION”) Win-
operating environment dows(64
bit) NT
39 SYSTEM Info returns information about the =INFO(“SYSTEM”) pcdos
current operating environment
40 DAYS Returns the number of days =DAYS(“2024-12-31”,”2024- 365
01-01”)
between two dates

32
 41 DAY360 Calculates the number of days between =DAY360(“2024-01-
two dates based on a 360-day year 01”,”2024-12-31”)
42 FALSE Returns the logical value FALSE =IF(15>16, TRUE, FALSE
FALSE)
43 TRUE Returns the logical value TRUE =IF(16>15, TRUE, TRUE
FALSE)
44 PI Returns the value of pi. =PI() 3.141592654

45 IF Use this function to return one value if =IF(PER>=70,”PASS”,”- PASS

a condition is true and another value if FAIL”)
it›s false.

4.4.1. Creating Formulas

Formulas can be as simple as using the AutoSum function or complex using multiple operators.
When using multiple operators, Excel uses the standard mathematical order of precedence to deter-
mine which operations are carried out first.
The order of precedence is as follows: parentheses, exponents, multiplication and division, then, addi-
tion and subtraction. For example, the result of “=(8*5)+10” is 50 and the result of “=8*(5+10)” is 120.
Entering Formulas
Formulas are entered in the cell where the result will appear. Cell addresses can either be typed or the
mouse can be used to select the cells used in the formula (this allows Excel to enter the cell addresses
into the formula automatically). Users must still manually enter the operator type appropriate to the
formula used.
To enter a formula:
1) Type the {=} sign inside the cell where the result should appear
2) Select the cell holding the value to be evaluated by the formula. A marquee will surround the
selected cell; and the cell address is automatically inserted in the target cell
3) Use the appropriate operator (+, -, *, /…)
4) With the mouse pointer, select the second cell to be evaluated by the formula. A marquee will
surround the selected cell
5) Press the {Enter} key. The formula result is placed inside the cell

Figure 4.36 - Entering Formula

4.4.2. Insert Function

When working with complex formulas and functions, the user may not be sure of the proper syntax
of the function. Excel provides a feature that ensures the correct entry of a function and its argu-
ments. The Insert Function dialog box contains several arguments that are grouped by functions in
order to narrow the selection. Use the Insert Function feature to ensure that the function is spelled
correctly and has the arguments in the correct order.

33
To insert a formula using the Insert Function feature:
1) Select the cell where the result will appear
2) Select the Formulas tab on the Ribbon
3) Click the Insert Function button. The Insert Function dialog box will open
4) Select an option from the or Select a category: list box. The list box provides the broad cate-
gories of functions available in Excel
5) Select a function in the Select a function: section
6) Click the OK button. The Formula Arguments dialog box will open

Figure 4.37 - Average Calculation Function

7) Specify a range of arguments for the function by clicking the Collapse Dialog button. The Formu-
la Arguments dialog box is temporarily collapsed to a thin box
8) Select a range of cells in the worksheet containing the values to be used in the function. A blink-
ing marquee surrounds the range of cells and their cell addresses appears in the collapsed box
9) Click the Collapse Dialog button to redisplay the Formula Arguments window
10) Click the OK button. The result of the operation is inserted in the target cell
4.4.3. Using Advanced Functions
Another key advantage of using Excel functions are their decision-making capabilities. Logical func-
tions make decisions based on criteria. If the evaluated criteria are true, then one action is taken. If
the evaluated criteria are false, then a different action is taken.
Using the IF Function
One of the most important and frequently used functions available in Excel is the IF function. The IF
function returns one value if a condition is true and another value if a condition is false. In the above
example, if the amount of the order is greater than a set value, then a true value would be returned.
If the order amount were less than the set value, then a false value would be returned.
The syntax of an IF Function is: =IF (logical test, value if true, value if false)
The components of the formula are listed in the following Figure:

If Function Result
The test condition can contain cell references, text in
quotes, cell names, and numbers. The items are
compared using the following comparison operators:
= equal to
Logical test <> not equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to

Value if true The result produced if the logical test is true.

Value if false The result produced if the logical test is false.

34
Using IF Function
In this example we can see the result of student by using the if function, (See Figure 4.39)
= if (G3>=60,” Passed”, “Failed”)

Figure 4.39 - IF Function Example

4.4.4. Using the AND Function with the IF Function

The AND function returns a logical value (true or false) depending on the value of its arguments. If all
its arguments return true, the AND function returns true. If at least one of its arguments returns false,
the AND function returns false.
For example, Widgets R Us gives monthly bonuses for sales representative whose monthly sales
amount is greater than $10,000 and have 3 or more years of experience. If the sales representatives
fulfill both criteria, they will receive a bonus worth $5,000. Otherwise, they will get no bonus. Given
this scenario, the function will look like this:

Figure 4.40 - AND Function

By looking in cell E3 of Figure 4.30 the user will see that the first sales representative, Riaz Ahmad.
has fulfilled the criteria and thus received the $5,000 bonus. When the formula is copied from cell
E4 to 76 by dragging the Fill Handle, the function is evaluated for all of the sales representatives. All
except Mumtaz. are awarded a bonus.
4.4.5. Using the OR Function with the IF Function
The OR function is similar to the AND function, but it returns a true value if at least one of its argu-
ments is true; otherwise, it returns a false value. In Figure 4.41 below, by using the OR function, Mum-
taz. will receive the bonus of $5,000.

Figure 4.41 - OR Function

Copy the formula to the cell range E4:E6. The user will observe that Mumtaz., even though he did not
meet the sales target of $10,000, still qualifies for the $5,000 bonus on grounds that the minimum
years of experience needed was exceeded.

35