EXCEL tutorial inventory spreadsheet
UNIT ONE
AN INTRO OF EXCEL
Structure
1. introduction
2. Objectives 3. Excel Basic
a. To start MS-Excel
b. Parts of MS-Excel spreadsheet
4. Worksheet within workbook
5. Cell
6. Navigate worksheet
7. Enter and edit data
a. To make an entry in the cell
b. To edit the cell content
8. Range of cell
9. Cell references
10. Setting the column width
11. Cell formatting
12. Save, Close, New, and Open options
13. Inserting cells, columns and rows
14. Exit from Excel
15. Importance of functions 16. Functions with Examples
a. Mathematical & Statistical functions
b. Date and time function
c. Text functions
20. Operator
21. Logical function
22. Auto sum
23. Function wizard
24. Self Test
UNIT 1 An Introduction to MS-Excel
INTRODUCTION
MS Excel is a windows based spreadsheet (worksheet) package. When calculations are made on paper and certain data must be changed, then the entire work must be recalculated and re-written. If a spreadsheet package, is used then the re-calculation is automatic. The details of bank passbook, tax, inventory, purchase and sales can also be maintained using a spreadsheet package. Lotus 1-2-3, Sympathy, MS Excel etc., are spreadsheet packages
OBJECTIVES
After going through this unit, you will be able to,
Start MS Excel
Identify different parts of worksheet
Create, Save, Open and close a workbook
Work with formulas and functions
Exit from MS-Excel
EXCEL BASICS
Before working with MS-Excel, it must be started, and different parts of Excel spreadsheet must be identified.
TO START MS-EXCEL
Click on the Start button on the Taskbar. Select Programs> Microsoft Excel.
From Windows Explorer, double click on any Excel icon (Will be present in Microsoft Office folder). Excel will start and the file will be opened.
Select Start > Run, Type EXCEL and click on OK. EXCEL, .EXE is the executable filename of MS-Excel, present in Microsoft Office folder.
Double click on the Excel shortcut icon on Windows desktop (if any).
PARTS OF MS-EXCEL SPREADSHEET
When MS-Excel is loaded, the Excel window will appear on the screen. Excel window with its parts is given below.
Application Name File name Title Bar Menu Bar Tool Bar Format Bar Formula Bar
Name Box Current Cell
Columns
Rows
Sheets
(Excel Screen with parts labeled)
Title Bar: - Display the application name, file name and various window controls
Menu Bar: -Different option for selection
Tool Bar: - It display by default and allow giving common commands
Formatting Tool Bar: -It allows the user to give commands related to formatting Name Box: -It displays the address of the current cell.
Formula Bar: -It displays the cell content.
Current Cell: -The active cell is called current cell.
Row: -Horizontal line on Excel sheet is called Row. There are 65536 rows numbered as 1, 2, 3, 4, 5… 65536. To go to the last row press end and down arrow key and to return to cell in first row press end and up arrow key.
Column: -Vertical line on Excel sheet is called column. There are 256 columns numbered as A, B, C….. Z, AA, AB…AZ, BA, and BB…IV. To go to the cell in last column press end and right arrow key and to return to the cell in first column press end and left arrow key.
{The extension of Excel file is .XLS}
Scroll Bars: -Used to scroll through different parts of current sheet.
Split Boxes: -Used to split the window into 2 or 4 parts or to remove the split.
WORKSHEETS WITHIN WORKBOOK
Excel documents (files) are known as workbook. Each workbook contains 3 worksheets by default.
Adding or deleting the sheets can change the number of sheets. Each sheet is named uniquely like Sheet1, Sheet2, Sheet3 etc, which is called displayed in the sheet tab. A workbook can also contain chart sheets, which are named as Chart1, Chart2, etc. by default.
CELL
The intersection of a column and a row is called as a cell. Each cell has a name or a cell address. The cell address consists of the column letter and a row number. For instance, the first cell is in first column and first row. First column name is A and first row number is 1. Therefore, the first cell address is A1. First cell is also called as Home cell. Similarly, the last cell address is IV65536 (column IV and row 65536). Total cells in a worksheet are 256 * 65536 (Total number of columns * rows). At any time one cell can be active and the address of the active cell is displayed in the name box.
NAVIGATE WORKSHEET
To move to any cell if any worksheet of an open workbook, the mouse can be used.
To scroll though different parts of the worksheet, drag the scroll box in the scroll bars or click on the arrow marks in the scroll bars.
To go to different sheets in the workbook, click on the desired sheet name in the sheet tab.
To go to desired cell, click inside the cell, or select Edit>Go To type the desired cell address ion the Reference box (for example, D7) and click on OK or.]
Click on the name box, type the desired cell address and press Enter.
To move from one cell to another, the keyboard can also be used.
Key FUNCTION
è(or tab ) Right one cell
ç(or shift + tab) Left one cell
? Up one cell
? Down one cell
CTRL + ? Last cell in the row
CTRL + ? First cell in the row
CTRL + ? First cell in the column
CTRL + ? Last cell in the column
CTRL + HOME First cell in a worksheet (Home cell)
Pgdn Down one screen
Pgup Up one screen
Alt + Pgdn Right one screen
Alt + Pgdn Left one screen
Ctrl + Pgdn Next sheet
ENTER AND EDIT DATA Ctrl + Pgup Previous sheet
SEE IN COLUMN D
Any entry can be made in the active cell. Entries can be of 4 different type. Relative reference They are:-
Text: - Text in cell can include any combination of letters, numbers keyboard symbols. A cell can contain 32000 characters.
Number: - Number include digits from 0 to 9 and some special characters like $ % + - / (), E
etc. Data and time are started as number if 8-15 is entered in a cell, excel will display it as 15-Aug. When a notation when a formatted number does not fit in cell #### is displayed.
Logical values: - logical entries, true and false (uppercase only) can be entered in the cell.
Formulas: - Formulas and entered into the cell to perform calculation. A formula begins with an equal sign (=). After completing the formula entry, the result of formula will be displayed in the cell and the formula will be displayed in the formula bar.
TO MAKE ANY ENTRY IN THE CELL
Make the cell active (select the cell) by click on the cell or by pressing arrow keys.
Type the content of cell.
Press Enter or press the arrow keys or click on any other cell to complete the entry.
TO EDIT THE CELL CONTENT
Make the cell active (select the cell) and use any of the following method.
Press F2 function key or double click on the cell, make the correction in the cell content and press
Enter. Click on the formula bar (which displays the active cell content), make the correction and press Entreat
To delete the cell content click on the cell and press Delete.
RANGE OF CELLS
Range is group of cells (also called as a block) selected generally to make the cell formatting. To specify a range of cells using the mouse, click and drag from the middle of cell.
CELL REFERENCES
A formula represents certain cell relationship which generates a result. When typing the cell address in the formula, three types of cell references can be used, A cell reference specifies. When the formula is copied to other cell. Its columns or row number is to change or not.
Key FUNCTION
è(or tab ) Right one cell
ç(or shift + tab) Left one cell
? Up one cell
Relative reference: - If a formula with relative reference is copied. The cell reference use in the formula will automatically change in the copied cell for example, when the formula = B5*C5 in cell D5 in copied to D6.
Absolute reference: - If the formula with absolute reference is copied the cell reference use in the formula remain unchanged (no change in column or row number) To achieve this use $ symbol before the column letter and the row number, For example when the formula ==$B$5*$C$5 in cell E5 is copied to E6 the formula will be =$B$5*$C$5 itself because both column letter and row numbers are made constants. That means the result in the cell E5 and E6 will the same.
Mixed Reference: - If a formula with mixed reference is copied, the cell references used in the formula will change either the column letter or row number but not both of then. To achieve this use $ Symbols before the column letter or the row number.
SETTING THE COLUMN WIDTH
If the cell content is not displayed in full, in any cell (for example, floppy disk is displayed as floppy disc in the above example), the width of the column must be increased similarly column width can be reduced.
To increase or decrease the column width uses any one of the following methods.
Drag the column header right border ( displays double headed arrow while dragging ) Click on any cell in the column, select format > column > Width, current width will be displayed type the required width number and click OK.
To set the auto fit width (setting the width of a column based on the largest content in the cell) double click on the column width is generally 8.43
To size many columns at once using mouse select the [contiguous or non-contiguous] columns to be sized and drag the border of any one of the selected columns.
COMMANDS ON THE FORMAT < COLUMN MENU
Key FUNCTION
è(or tab ) Right one cell
ç(or shift + tab) Left one cell
? Up one cell
CELL FORMATTING ? Down one cell
CTRL + ? Last cell in the row
Cell formatting include setting the font type, font size bold, italic, underline, alignments, cell color, text color, decimals etc. The icons to perform these operations are generally available in the formatting Toolbars.
Exercise
For value = Quantity * Rate.
For discount = value * discount
For Net Value = Value – Discount
SAVE, CLOSE, NEW AND OPEN OPTIONS Save the Workbook
To save the workbook (file) uses any one of the following method.
Select file > Save (Click on it)
Press Ctrl + S
Click on Save icon in the standard Toolbar
Note: - Save the workbook with the extension code .XLS
Close the Workbook
To close the open workbook, select File > Close. The workbook will be closed, i.e., no worksheet will be displayed on the screen and the screen will be empty
Create a New workbook
To open a new workbook uses any one of the methods. Select file >New
Press Ctrl + N
Click on new icon in standard Toolbar
Open a workbook
To open an existing workbook uses any one of the following methods.
Select file >Open
Press Ctrl + O
Click on Open icon I the standard Toolbar
Open dialog will be displayed click on the required file name and click on Open button. If a file list is displayed at the end of file menu clicking on the file name in file list can also open a workbook.
INSERTING CELLS, COLUMNS AND ROWS
To insert cells, columns and rows select insert >cells….Insert dialog box will be displayed with the following option.
Shift cells right inserts a new cell and existing cell will be shifted to the right.
Shift cell down inserts a new cell and existing cells will shift down.Entire row will insert one row.
Entire column will insert one column.
Choose inset >Columns option to insert one or more columns. To insert more than one column, block the number of column to be inserted and choose insert > columns. Insert >Rows option will insert one row incurrent position. To insert many rows block the number of rows to be inserted and choose Insert >Row.
EXIT FROM EXCEL
To close MS Excel, use any one of the following methods
Click on close button X in the upper- right corner of the excel workspace.
Choose File > Exit.
Click on Excel icon in the upper-left corner of the Excel window to display the
Control Menu, then choose close (or double click on the Excel icon)
Press
IMPORTANCE OF FUNCTIONSAlt+F4.
Function shortens a formula. For example, to add the values in the cells C1 to
C10, the formula is =C1+C2+C3+C4+C5+C6+C7+C8+C9+C10. If the function is used. We need to type only =Sum (C1:C10).
Complex formulas can be created using functions and some tasks can be accomplished only by using the functions. For example, to display the current date and time use TODAY ( ) {only for FUNCTIONS WITH EXAMPLES date} and NOW ( ) functions.
Mathematical and Statistical Functions.
SUM ( )
It is amathematical function used to add the numeric value in a range of cells. A maximum of 30 arguments can be provided.
SYNTAX: = SUM (CELL RANGE) <enter >
AVERAGE ( )
Calculate and returns the average (arithmetic mean) of the numeric values in the range of cells. It is a statistical function. A maximum of 30 arguments can be provided.
SYNTAX: =AVERAGE (CELL RANGE) < enter >
MAXIMUM ( )
Returns the largest value in the range. It is a statistical function. A maximum of 30 arguments can be provided.
SYNTAX: =MAX (RANGE) < enter >
MINIMUM ( )
Return a lowest value in the range. It is a statistical function. A minimum of 30 arguments can be provided.
SYNTAX: =MIN (RANGE) < enter >
COUNT ( )
Counts the number of cell that contains number s in the specified range. It is a statistical function.
SYNTAX: =COUNT (RANGE) < enter >
COUNTA ( )
Counts the numbers of cells that are not empty in the specified range. It is a statistical function. A maximum 30 argument can be provided.
SYNTAX: =COUNTA (RANGE) < enter >
COUNTBLANK ( )
Counts empty cell in a specified range of cells. Cells with formulas that return (empty text) are also counted. Cells with zero values are not counted. It is a statistical function.
Syntax: =COUNTBLANK (RANGE) < range >
Type the following worksheet with functions. Try to understand the result generated by the functions.
DATE CTRL + ;(semi colon)
It will return the current date of the system.
TIME CTRL +SHIFT+ ;( semi colon)
It will return the current time of system
Date & Time Functions
Microsoft Excel for Windows uses the 1900 date system, in which serial numbers correspond to the dates January 1, 1900, through December 31, 9999. To insert the current time shortcut is Ctrl+: [colon]
DATE (year, month, and day) < enter >
Returns the serial number of a particular date. Year is a number from 1900 to 9999. Month is a number representing the month of the year.
If month is greater than 12, then month adds that number of months to the first month in the year specified.
For example, DATE (90, 14, 2 ) returns the serial number representing February 2, 1991 =DATE (91, 1, 1) equals 33239, the serial number corresponding to January 1, 1991.
NOW ( )
Syntax
=NOW ( ) < enter >
Returns the serial number of the current date and time. Value returned by NOW () function will change each time when worksheet is recalculated.
TEXT FUNCTIONS
In the following examples for various functions assume that the cell A7 contains MONITOR.
LEFT ( )
Returns the specified number of characters from the left side of a text string the number is not specified, it returns the first character.
Syntax: =LEFT (String, number) < enter >
Key FUNCTION
è(or tab ) Right one cell
ç(or shift + tab) Left one cell
RIGHT ( ) ? Up one cell
Returns the specified number of characters from the right side of a text string the number is not ? Down one cell specified, it returns the last character.
Syntax: =RIGHT (String, number) < Enter >
Key FUNCTION
è(or tab ) Right one cell
ç(or shift + tab) Left one cell MID ( ) ? Up one cell
Returns a specified number of characters starting from a specified position in a text string.
Syntax: =MID(String, start number, number of characters) < Enter >
Key FUNCTION
è(or tab ) Right one cell
LEN ( ) ç(or shift + tab) Left one cell
Returns the length of a text string. Spaces are counted as characters. Up one cell
Syntax: = LEN (string) < Enter >
Key FUNCTION
è(or tab ) Right one cell
ç(or shift + tab) Left one cell
Up one cell
ABS ( )
Returns the absolute value of a number. The absolute value of a number is the number without its sign.
Syntax
ABS (number) < enter >
Number is the real number of which you want the absolute value. e.g. if the number is -5 then it will return 5
FACT ( )
This function returns the factorial of a given number.
Syntax
=FACT (number) < enter >
e.g. if the number is 5 then the result will be 120
SQRT ( )
This function returns the positive square root of a positive number
Syntax
=SQRT (number) < enter >
If number is negative, then this function returns #NUM! Error value e.g.:-
If the number is 16 then output will be 4.
OPERATOR
Operator is a symbol, used to specify the type of calculation that is to be performed on the elements of a formula. Microsoft Excel includes four includes four different types of calculation operators: arithmetic, comparison, text, and reference.
Arithmetic Operators
Arithmetic operators perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results.
| Key è(or tab ) ç(or shift + tab) ? ? CTRL + ? CTRL + ? CTRL + ? CTRL + ? CTRL + HOME Comparison Operators Pgdn | FUNCTION Right one cell Left one cell Up one cell Down one cell Last cell in the row First cell in the row First cell in the column Last cell in the column First cell in a worksheet (Home cell) Down one screen |
These operators compare two values and then produce the logical value TRUE or FALSE.
| Key è(or tab ) ç(or shift + tab) ? ? CTRL + ? CTRL + ? CTRL + ? CTRL + ? CTRL + HOME Pgdn Text OperatorPgup | FUNCTION Right one cell Left one cell Up one cell Down one cell Last cell in the row First cell in the row First cell in the column Last cell in the column First cell in a worksheet (Home cell) Down one screen Up one screen |
The text operator “&” combines one or more text values to produce a single piece of text. &
(Alt + Pgdn ampersand) Connects, or concatenates, concatenates, two values to produce one continuous text Right one screen value “North” & “wind” produce” Northwind”
Reference Operators
Reference operators combine a range of cells for calculations.
| Key è(or tab ) ç(or shift + tab) ? ? CTRL + ? CTRL + ? LOGICAL FUNCTIONS CTRL + ? | FUNCTION Right one cell Left one cell Up one cell Down one cell Last cell in the row First cell in the row First cell in the column |
Logical functions are used to see whether a condition is true or false or to choose for multiple conditions.
IF ()
The function IF is used to determine whether a condition is true or false: Value is returned if the condition is true, and a different value is returned if condition is false. Syntax:-
=IF (condition, true action, false action) < enter >
Consider the following worksheet.
For example:-
If the value of cell d3 is 35 then If the value of cell d3 is 65 then
=if (d3<50,”fail”,”pass”) =if
(=d3<50,”fail”,”pass”) if ( 35<50,”fail”,”pass”) =if ( 65<50 ,”failTru”,”pass”) e false
So if the given condition is true then the out come will be true value and if the given conditions become false then the out come will be false value
SUMIF ( )
This function is used to total a range of numeric cells based on a condition.
SYNTAX:-
=SUMIF (Range to Search, Criteria, Range to Sum) < enter >
Range to check is the range of cells where the criteria is to be searched.
Criteria are in the form of a number, expression, or text that defines the cells to be added. Range to sum is a range of cells the number of which is to be added.
COUNTIF ( )
This function gives the count of the number of cells which satisfies the condition.
SYNTAX:-
= COUNTIF (Range to Search, Criteria) < Enter >
Consider the following worksheet.
AUTO SUM
Auto sum ? is used to total a range of numeric cells. This icon is available in Standard toolbar. To use this icon, block the numeric cells and click on the auto sum icon ( ? ).
FUNCTION WIZARD { fx }
We can also enter the function using the Function Wizard, instead of typing them.
To use the function wizard
Click on the cell where the function is to be included.
Click on the function wizard icon named Paste Function available in the Standard Toolbar ( or select insert > function….)
SELF TEST ONE
1. Differentiate between a Workbook and a Worksheet.
2. What is the cell?
3. What are the different types of data that can be entered in a cell?
4. What are Functions?
5. Explain three types of cell reference.
6. How to specify a range of cells?
7. What is auto fit column width?
8. Name any three statistical functions.
SELF TEST TWO
1. In column D, calculate Dearness Allowance@ 40% of Basic.
2. In column E, calculate House Rent Allowance@ 10% of Basic.
3. In column F, calculate Gross: - Basic + DA + HRA.
4. In column G, calculate Provident Fund@ 5% of Gross.
5. In column H, calculate Net: - Gross – PF.
6. Include 2 decimal to Gross, PF and Net.
7. Insert a Row at the top and give the heading lucky General Store and center it in between A to I.
8. Insert a column at the beginning (A) and give serial numbers for the different employees.
9. Increase or decrease the column width if necessary.
10. Center all columns heading in their cell.
11. Save the workbook under the name of GNIT.
SUMMARY
In this unit, you have learnt,
v To two start Excel, click on start button then program >Microsoft Excel.
v Excel file is called workbook, which is made up of worksheets.
v A formula is entered with = sign, i.e =A1*B1
v The logical function IF is used to decide and calculate result.
v File will be saved or stored in the specified folder under the extension (.xls).
UNIT TWO
CELL FORMATTING, FILL HANDLE AND RANGE NAME
INTRODUCTION
One of the most basic ways the appearance and usefulness of the worksheet can be enhanced is by formatting it. Common formatting options like font style, font size, bold, Italic, underline, alignments etc, are available in the Formatting Toolbar. Additional formatting options are explained in this unit.
OBJECTIVES
After going through this unit, you will be able to, Specify different types of cell formatting.
Selecting a proper alignment for the cell content.
Selecting an appropriate font for the cell content.
Specifying different types of patterns for cell shading.
Automatically fill a range of cells with different types of values.
Creating, using, editing and deleting Custom Lists. Creating, Using and deleting the Range Names.
CELL FORMAT CATEGORIES
There- are different categories of formats available like General, Number Currency,
Date, Text etc, to select a format,
1. Click on the cell or select a range of cells that are to be formats
2. Select Format > Cells. The Format Cells dialog box will be displayed. Her you may choose the desired format. General will be the default format.
Number format is used to specify number of decimals; thousand separator symbol and how negative numbers are to be displayed.
Currency format is used to specify the number of decimals, the currency symbol, and how the negative numbers are to be displayed.
Accounting format can be used to specify the number of decimals and a currency symbol.
Date format can be used to specify, how the date must be displayed in a cell i.e. month in words etc.,
Time format is used to specify 24 hour or 12 hour format, display seconds or not.
Percentages format multiply the cell value by 100 and display the result with a percent% symbol. Number of decimals can be specified.
Text format can be used to represent a number entered in a cell as a text entry.
For example if you type the following worksheet and apply the given formats,
Change Agent No. to Text
Change sale Date to Date (date-month in words year)
Change Comm. Rate to Percentage with 2 decimals
Change Commission to Currency with 2 decimals, symbol $.
Then the worksheet will appear as follows.
CELL CONTENT ALIGNMENT
Alignment refers to the positioning of cell contents within the cell. By default the text is left aligned and numbers are right aligned. To change the alignment of cell contain, select Format >cell, select the Alignment tab.
The cell content can be aligned horizontally or vertically and an orientation can be specified. The horizontal alignment options include General (default), Left, Center, Right, Fill (text in the cell will be repeated to fill it), Justify (if the text exceeds the width of cell, it is wrapped and multiple line text is justify aligned),
Center across selection (the text will be centered between the cells in two or more selected columns).
The vertical alignment options include Top, Center, Bottom, (default) and Justify (aligns the text top and bottom by wrapping the text to multiple lines, if the cell content exceeds the width of cell).
If the text exceeds the width of cell, its appearance can be controlled by selecting check boxes Wrap Text (to display text in multiple lines in the cell), Shrink to fit (to reduce the size of the characters to display it inside the cell itself) and Merge Cells (to combine the cells in two or more columns by selecting those cells).
Orienting Text within a cell: - The angle of text can be controlled within cells plus or minus 90 degrees either by clicking on and dragging the text Orientation indicator or by scrolling the Degrees Spinner.
APPLYING FONT FORMAT:-
Different types of font, size, underline, color, subscript, superscript, strikethrough etc., can be selected from the Format >cells.
APPLYING CELL BORDERS:-
First, select the cell, which are to be formatted.
Selected Format >cells.
Select the Border tab to display the border dialog box. Select the required type of border, border style, color, and click on OK.
Difference between Gridline and Border:-
Cell gridlines are not the same as cell border. Gridlines are global to the worksheet, and they diminish the impact of borders.
To remove gridlines select:
Tools>Option, select the View tab and deselect the Gridlines option.
FORMAT PAINTER
Format Painter ICON
Format Painter feature is a powerful in Excel and the icon is available on the standard toolbar. It enables the user to coy the formatting of a particular cell and paint it to other cells in the worksheet. It is an enormous time saver.
APPLYING BACKGROUND TO WORKSHEET
To make the worksheet attractive, background pictures can be inserted. To apply background:
Format >sheet> background.
Select the picture file required from a variety of graphic and file formats from the sheet Backgrounds.
To remove the worksheet background:
Select the worksheet for which background is to be removed. Format >sheet> Delete Background.
CONDITIONAL FORMATTING:-
MS-Excel introduces the concept of conditional formatting, where formatting is done to a cell it the cell contains a value or when the cell contains a value that falls between a range, (e.g. Between 10000 to 50000.) Formatting the cell is also possible when the cell contains a negative value.
The conditional formatting can be cone on cell Value is or Formula Is formatting criteria.
AUTO FILL
The auto fill feature of MS-Excel will save date entry by expanding series of numbers, days of week, different months, etc. from a given cell to adjacent ones. This is achieved using the Fill Handling.
CUSTOM LIST
A custom list is a collection of some commonly used words, which can be reproduced by dragging the fill handle. For example, a company operates in four different regions (North, South, east, and west), and the region has to be typed constantly onto the worksheet. A custom list can be used to save data entry time.
RANGE:-
A collection of cells form range. E.g. A1:B10.
RANGE NAME:-
A name can be given to a range instead of using the cell reference to use the range. This range name can be used in lace of cell addresses in formulas.
CREATING A RANGE NAME:-
There are three ways to name a range.
Using Name Box.
Using Define Name dialog box.
Using create Name dialog box.
DELETING A RANGE NAME
To delete the existing range,
Choose Insert > Name > Define, to display the Define name dialog box Select the name of the range to be deleted from the list.
Click on the Delete button. CHECKING RANGE NAMES
The range names already created can be checked as the given process.
Using function key F5
Press the function key F5 (the Edit > Go To dialog is appeared. The name of the range to be checked can be selected from the names in Workbook or it can be typed.
Click on OK.
The cells that are included, in the selected ranged are marked.
Using name box
Click on the down arrow key in the name box to get a list of range names Select the range name, which is to be checked.
DELETING RANGE NAMES
To delete the existing range,
Choose Insert > Name > Define, to display the Define name dialog box
Select the name of the range to be deleted from the list.
Click on the Delete button.
UNIT THREE GRAPHIC OBJECTS AND CHARTS INTRODUCTION
Excel also allows you to insert drawing objects in the worksheet. The icon to draw the drawing objects are. Available in the Drawing Toolbar, generally, displayed at the bottom of the worksheet.
OBJECTIVE:
After going through this unit, you will be able to.
Insert different types of drawing objects.
Understand and create embedded chart and chart sheet.
Select and format different part of chart.
Delete and embedded chart or char sheet.
GRAPHIC OBJECTS
Working with Drawing Toolbars
Many drawing objects can be inserted into the worksheet or char sheet from the drawing toolbar, it drawing toolbar is no visible, select View >Toolbars, drawing. Generally it is displayed below the sheet tabs.
AUTO SHAPES
Auto shapes is another drawing tool. Auto Shapes are pre-existing “templates” for commonly drawn shapes, like lines, polygons, arrows, as well as freeform lines and two dimensional shapes.
WORD ART
Word art is used to bring about colorful text effects to the worksheets; it also allows you to create text objects.
To create word art object this step.
Drawing toolbar è Word art è select a style from word art gallery dialog box and click on OK è Then Excel will displays the edit word art text dialog box è Then types your text and click on OK.
The word art object is placed on the worksheet, where the object can be moved, resized and formatted like other character or object.
CHART
The graphical representation of numerical data is called as CHART.
TYPES OF CHARTS
There are two types of charts
1. EMBEDED CHARTSè If charts are created on the same worksheets where the data exists is called Embedded Charts.
2. CHARTS SHEETè If any chart is created on the other or separate sheet then we call it chart sheet.
HOW TO CREATE CHART
To create a chart in a spreadsheet, select the data with column headings and one text column.
INSERT è CHART
Select a Type of Chart from This Dialog Box and Click on Next
Select One Of The Options ( Rows/ Columns )in this dialog box.
TITLE è Allows you to give different titles for the Chart
CHART TITLE è Main title for the Chart
Category (X) axis è Title for the X-axis, will be displayed bellow the X-axis labels. Value Y-axis è Title for the Y-axis, will be displayed at the left side of Y-axis number.
Axis è It can be used to specify whether the primary X-axis and Y-axis must be displayed in the chart or not.
Gridlines è It is used to specify whether major or minor X-axis and Y-axis gridlines are to be displayed in the Chart or not.
Data Table: it is used to display the date below the chart by default if is not displayed.
To display the data table, select Show data table check box We can also specify,
(Embedded Chart)
(Chart Sheet)
UNIT FOUR
DATABASE, PIVOT TABLE & DATE VALIDATION
INTRODUCTION
Every organization maintains the date of various kinds e.g. the details of employees. Sales details etc. the purpose of storing and maintaining data is to retrieve it later for day-to –day transactions and management planning. Sorting, searching totaling etc. using this data. This is called as data processing. The processed data becomes information.
OBJECTIVES
After going through this unit, you will be able to
Create a database.
Use the data from to view, edit, and delete the records in a database.
Filter the records in a database.
Sort a database, sub totals for each group of records.
Use D-function to perform calculation based on a condition.
To create, edit or delete the privet tables.
To enter correct values in the worksheet a to link o merge the information of different worksheets.
DATABASE
An organized collection of data arranged in rows and columns is a database. It also called an excel list. The columns are called fields. Each row in the list below the field name is a record.
TYPES OF DATABASE
There are two types of the worksheet database:
Internal: Database created in excel are called as internal database.
External: A database created using DBMS packages like FoxPro, Dbase, oracle and Access is called an external database.
CREATING A DATABASE
The following point must be remembered while creating a database.
Avoid creating more the one list on the same worksheet.
Leave at least one bland column and one blank row between the list and other data on the worksheet.
Avoid putting blank rows columns in the list.
Avoid placing important data to the left or right of the list because this data might be hidden when filter option is applied to the list.
DATA FORM
Excel provides a build in data form, to enter, display, delete the records in database, it has the following characteristics :
Displays one record at a time.
Can display maximum of 32 fields at a time.
Can be used to add new records and edit existing records.
Allows displaying records matching specified criteria.
It is used with internal database only.
FILTERING DATABASE RECORDS
Sometimes it is useful to show only those records that meet certain criteria of condition. This is accomplished filtering the database; there are two types’ filters:
Auto filter
Advanced filter
AUTO FILTER
Data >Filter >Auto filter
Auto filter helps us to display the records. Which meet a particular condition? When this option is selected, Drop-down controls are placed next to each filter name, on clicking on this drop-down control; the contents of the fi eld without repetition are displayed.
REMOVING THE FILTERS
If one or more filters are set, the DATA >filter > Auto filter command can be selected to remove this filters.
ADVANCED FILTER
Advanced filter is used to display records that match a condition. The condition is written in a separate part of the worksheet. To use advanced filter, Type of condition (field name and below it, the relational operation) in any part of worksheet (away from the database), the range, where the condition is written is called criteria range. Note: - Condition is not case sensitive.
DATA SORT
Sorting arranging the records in a database, based on one or more field (columns), Sort brings the related records together, so that the records in the database are easily accessible. Record can arrange in the ascending order or descending order. To sort records:
Click on any inside the database and select Data > Sort. The following dialog will be displayed on the screen.
Generally, a database will have column headings, i.e., the option Header Row under has My List has tab must be selected. If there are no field names, select No header row.
Sort can be done on one, two or three fields. The field. On which, the database is to be sorted first, must be given in sort by. The second preference field name can be given in fist then by. The third priority field name can be given in next then by. Among them, sort by field name is necessary. Sort order Ascending or Descending can be specified for each sort column. Click on OK to arrange the records.
DATA SUBTOTALS
To generate subtotals of numeric fields based on a controlling field, the database must have bee snorted on ascending or descending order of the controlling field, For example, consider a database with fields Name, Subject and Marks. The total or average marks of each student can be obtained through subtotals.
To generate the subtotal
For creating Subtotal, first of all you have to sort the Data Base and then use subtotal option on the sorted column.
Example
Sort the database on the controlling field (in the above example- Name) to generate the total of
marks obtained by the students, the record of each student must be kept together. Click on any cell inside the database and select Data > subtotal. The subtotal dialog is displayed.
Selected the name of field on which the database is sorted in the box of at each change in. From the drop box of Use function, select a function like Sum, Average count etc, (any one function).
Select the name of fields on which the subtotal is to be generated. in the example, Marks, To generate fresh subtotals ignoring the subtotals present if any select Replace current subtotals.
The age Break between Groups setting can be checked to insert a page break after each subtotal.
The Summary below Data setting can be checked to place the subtotal beneath the data. Otherwise, it is placed on top.
Click OK to generate the subtotals.
PIVOT TABLE
An interactive tables that summaries and analysis data from a database is a Pivot Table.
Creating a Pivot Table
A Pivot Table can be created from Excel database (internal database) or on the database created in any DBMS packages (external database).
To create a pivot table, lest us consider the following worksheet.
A report of total amount of sale of each item in the deferent rows and column of different branches in different columns with a provision to display the details of sales made my any salesman must be created.
STEPS AS FOLLOWS
1. Click on any cell inside the database or select all databases.
2. Select data and then click on pivot table a pivot chart report.
Click here.Select data > pivot Table Pivot table takes 4 steps it generate the report. Pivot Table Wizard step 1of 4 dialog box will be displayed. This dialog box allows you to specify the type of data to be used as the source for the pivot table. Microsoft Excel database is the default.
Since the source data is stored in worksheet database, select the Microsoft Excel list or database.
Click on Next to go to the second step.
1) Step 2 displays the database range.
Click on Next to go to third step or back to go to previous step’
2) Pivot table wizard is used to specify the layout of pivot table.
Pivot table layout contains mainly 4 parts.
These are …
DATA: - The actual data that will be inside the pivot table (generally, fields) at least one field must be placed in the data area. In our example INCOME,
ROW: - Field(s) used as row titles. In figure EXPENSE,
COLUMN:- Field(s) used as column titles. In figure BRANCH,
PAGE: - Used to filter the database, allowing the data to be viewed on page at a time. In
QUARTER,
REFRESHING A PIVOT TABLE.
Pivot table does not recalculate automatically when the source changes for example, if you edit the database on which the pivot table is created the pivot Table result is not changed automatically to reflect the changes made in database. To see the changes click on any cell inside the pivot table, and select Data. Refresh Data.
If any row or column is added to the database click on any cell inside the pivot table, select Data > Pivot Table Report. Pivot Table layout will be displayed on the screen. Click on Back button to display the database range used in the power table. Edit the range by including the added rows or columns, click on finish.
CHANGING THE PIVOT TABLE LAYOUT
There are two ways to change the layout of an existing Pivot Table. One by using the Pivot Table Wizard and another by direct interaction with the table on the worksheet.
To change the pivot table layout using the Pivot Table wizard, click on any cell inside the Pivot Table.
Select Data > Pivot Table > Pivot Table Report or click on Pivot Table icon in the Pivot Total toolbar.
COPYING THE PIVOT TABLE
One way of copying a pivot table is to copy the entire worksheet. Instead of copying the entire worksheet you can copy and paste the entire pivot table including any page fields. If you copy just a portion of a pivot table, the result when pasted. Will be normal cells rather than the actual pivot table
DELETING A PIVOT TABEL
There are two ways to deleting a pivot table. The entire worksheet containing the pivot table can be deleted. However, if you do not want to delete the entire worksheet, the pivot table cells can be cleared or deleted using EDIT >Clear > All. When deleting select the entire pivot table including page fields. If an error massage will be displayed, and the operation is not allowed.
CHANGE THE FUNCTION USED IN CALCULATION
By default, the function SUM is used for numeric fields and COUNT is used for non-numeric fields inserted in the data area of pivot table. To change the function used for fields. Click on any result cell inside the pivot table and click on Pivot table field icon in the pivot table toolbar (if Pivot Table Toolbar is not displayed, select >View> Pivot Table.
Pivot Table field dialog will be displayed to select the function to be used for calculation. Select the required function and click on OK.
CREATING PIVOT TABLES USING EXTERNAL DATABASE
Creating pivot tables using eternal databases involves the use of a program called Microsoft Query. The key benefit of creating a pivot table based on external data is that it is capable of analyzing far greater volumes of data than one based on a worksheet on a worksheet database.
To create a pivot table using an external database, at step 1 of the wizard, choose option External Data Source. Click on the next. Select the option Get Data. This option makes use of Microsoft Query to retrieve data from any database and it returns to step 2. The other options are the same.
DATA VALIDATION
Data validation is a process of verifying that the data is acceptable by applying certain rules.
Validating Numbers, Dates, Time and text
Applying data validation is conceptually similar to cell formatting i.e. the command acts upon the selected cell (s). Suppose validation I to be given,
Select the cells for which the validation is to apply.
Select data > validation. The tapped dialog box is displayed.
This dialog box is virtually identical for decimal, date and time validation. The tapped dialog box displays three tabs namely setting, input message and error alert
EXERCISE
Create the database given below and save it in the name PATIENT. XLS
1. Calculate the number of days.
= (Discharge date-Admission Date) + 1 < enter > 2. Calculate the room rent based on the ward.
Key FUNCTIO
è(or tab ) Right one cell
ç(or shift + tab) Left one cell
= if ( C2 = “ SPECIAL ” , F2 * 150 , F2 * 75 ) < enter > ? Up one cell 3. Calculate Amount.
= Room Rent + Fees < enter >
4. Sort the database in the ascending order of Department.
5. Create a subtotal of fees collected from each Department. Remove the subtotals.
6. Using subtotal, display the number of patient in each Department. Remove the subtotal.
7. Using Auto Filter, display the record of patients, whose name begins with A.
8. Using the Auto Filter, display any 4 records of patients who are hospitalized for minimum number of days.
9. Using Advanced Filter, display the record on the given condition.
Key FUNC
è(or tab ) Right o
10. Using Data Form, delete the record of Ajay. ç(or shift + tab) Left on
SELF TEST
1. What is database? Explain the type of Data Base.
2. What is the use of the Data Form?
3. Explain Sorting option.
4. What is a Pivot Table?
5. What are the main parts of Pivot Table layout?
6. What is Data Validation?
UNIT SIX Printing a Worksheet, File Protection, What If Analysis
INTRODUCTION
A worksheet, with the drawing objects and Excel controls can be printed on the paper. To print a worksheet, the required printer must be installed through windows and page must be setup. It is always better to Print Preview the worksheet before it is printed on paper.
PRINTING
Page setup è Excel offers number of option for setting up worksheet to print the data. Margins and fonts can be set, headers, footers and titles can be included. Multiple copies of the entire worksheet or just certain pages or selected area can be printed. Before printing on paper, the worksheet can be previewed.
Setting up Worksheets for Printing
Before, actually printing the worksheets set up the pages to be printed. To set up the page: choose file >page Setup, this displays the tabbed dialog box that provides access to most print related setting. The four tabs are – Page, Margins, Header/Footer and sheet.
PAGE OPTIONS
The options available on Page tab are:
Orientation: - Select portrait for tall printouts and landscape for wide printouts.
Scaling: - Adjust to: - Allows you to enlarge or reduce the printed worksheet without changing the size of the onscreen display. The printed worksheet can be reduced to lowest 10% to fit more of the worksheet on page or can bee enlarged up to 400% to enhance detail.
Scaling: - Fit to first a worksheet onto a specific number of pages, based on how many pages wide and how many pages in height the worksheet to be printed is.
If Fit to option is selected, then Excel ignores page breaks, if any and fits the entire worksheet or print area to the specified number of pages.
Paper size è select paper size from drop-down list.
Print Quality è select resolution (dpi) from list.
First Page Number è Begins numbering at specified page number.
MARGINS OPTIONS
Using Margins tab, from the Page setup dialog box, the margins can be set, the position of headers, footers, and print areas and be determined. The following options are available on the margin tab:
Top, Bottom, Left, Right: è sets margins (from the edge).
From Edge è sets header/footer placement (from edge) and this should be less than top/bottom margins.
Center on Page è check to center the print area vertically or horizontally between margins.
PAGE HEADER/FOOTERS
Creating and formatting of the heeders/footer is possible under the Header/Footer tab. Headers are printed at the top of every page and footers at end of every page. Headers are mostly used for company names and report titles and footers are commonly used for page numbers and printout dates/times.
Headers and footers are not actually a part of the worksheet, but they are a part of the printed page and are allotted separate space on the printed page. The space allotted is controlled on the Margins tab.
Using Built-in Headers/Footers
Select from variety of built-in headers and footers using the respective drop-down list on the Header/Footer tab. The list includes several commonly used header and footer formats such as page number, worksheet name, user name, date, and combination of these.
Creating custom Headers/Footers
Click on the Custom Header and Custom Footer buttons to customize headers/footers. A dialog box appears with three text boxes.
Left Section: Allows you to set a header/footer to the left of the page.
Center Section: Allows you to set a header/footer to the center of the page.
Right Section: Allows you to set a header/footer to the right of the page.
Click on specific section where the header/footer has to be entered.
Type text into any of these three sections (Press enter key for new line)
The buttons in the center of the dialog box are used to format the text. To insert values into the header/footer. They are as following:
Font format (select text from the section, click on button, select formatting options.)
A. Page Number
B. Number of Pages
C. Current data
D. Current time
E. Workbook name
F. Worksheet name
Note è The ampersand (&) is a code symbol for headers and footers, and does not print if ampersand (&) is to be printed in the header/footer, then type two ampersands when customizing headers/footers.
Sheet Options
The sheet tan pf the age setup dialog has options the following option:
Print è Select area of the worksheet to print, (either select or type the area reference)
Print Titles è Select or type rows/columns to print on every page.
Gridline è turns the gridlines on/off (only affects printed pages).
Black and White: è Prints all pages in black and white (no shades of gray fro colors).
Comments: è Allows printing cell comments either in place or at end of sheet.
Draft Quality: è Gridlines, most graphics and many types of cell formatting will not be printed thus reducing printing time.
Row and Column Headings è Including row and columns on the printed page. Page Order è select page order for multiple page worksheets,
To Quality Mark the Area for Printing è
First, insert the Print Area tool on any of the toolbars
Select the range to print and then choose File >Print Area > Set Print Area or click on the Print Area tool and then select Set Print Area.
To Remove the Area Set for Printing è
Click on the print Area tool and then select clear Print Area or File >Print Area > clear Print
PREVIEWING WORKSHEET BEFORE PRINTINGArea.
Chose file >Print Preview or click on the Print Preview button on the Standard toolbar, to see what the print end pages will look like before actually printing them. There are also several settings to control while in print preview mode. In print preview mode the mouse pointer becomes a magnifying glass. Click on any part of the worksheet, which is to be zoomed. The sheet will be magnified, and the pointer will change to an arrow. Click on the worksheet again to zoom back out.
PRINT PREVIEW BOTTONS
The following list explains the buttons displayed along the top of the workspace in the print preview mode. Next è Displays the next page (disabled when there is no next page).
Previous è Displays the previous page (disabled when there is no previous page).
Zoom è Switches between magnified and full-page display.
Print è Displays the Print dialog box.
Setup è Displays the Page Setup dialog box.
Margins è Switches on/off lines depicting page margins, header/footer margins and column width.
Page Break Preview è Displays pages break, and allows adjusting the breaks. Click on Print Preview button to return to that mode or select View >Normal to exit preview.
Close è closes the Preview window and returns to the worksheet.
SETTING
Excel automatically creates page breaks where needed. If automatic page breaks cause a page break to occur at undesirable place on the worksheet, manual page break can be inserted.
Setting Excel to Display Page Breaks Automatically
By default, automatic page breaks are not indicated on the worksheet until the sheet is printed (pr print previewed) the first time. To display page breaks,
Choose Tools>options.
Select the view tab, and then check the Page Breaks option,
Page breaks are indicated by broken lines, which run along the gridlines.
CUSTOMIZING PAGE BREAKS
There types of page breaks can be inserted, namely, vertical, horizontal and a combination of the vertical and horizontal.
Key FUNCTION
è(or tab ) Right one cell
ç(or shift + tab) Left one cell ? Up one cell
? Down one cell
CTRL + ? Last cell in the row
CTRL + ? First cell in the row
The page breaks can be accessed in the following ways: CTRL + ? First cell in the column
CTRL + Choose View Page Preview ? Last cell in the column Click on the Page Break Preview in the Print Preview dialog box.
Manual Page Breaks are indicated by heavier broken lines than automatic page breaks when displayed on the worksheet. Automatic page breaks automatically adjust when manual page breaks are inserted.
Removing Manual Page Break
To remove a manual page break, select a cell to the right of a vertical or immediately below a horizontal break and choose Insert Remove Page. (If the Insert men do not list the remove Page break command, there is no manual page break at the selected cell.)
WORKSHEET PRINTING OPTIONS
The File > Print command displays a dialog box that offers options for printing selected cells, specific sheets or pages, or and entire workbook, the Print button on the Standard toolbar will print the selection immediately without displaying the dialog box. The options available in the Print dialog box are described as following:
Printer è Displays selected from a drop-down list, print status, type, and the port that the computer is using to print.
Print to file check boxè selected it to create a disk file for the selected printer.
Page Range –All è Prints all pages in the worksheet Page Range-From/To è Print specified pages only.
Selection è prints selected cells only.
Active Sheet(s) è Prints the active worksheet.
Entire workbook è Prints the open workbook.
Number of Copies è Prints specified number of copies of selected pages.
Collate check box è Allows you to collate multiple copies.
Preview button è switches from the Print dialog box to the Print Preview mode.
Properties button è Displays a tabbed dialog box that controls the printing options.
To Print Sections of a Worksheet:
I. Select the range of cell to print II. Choose File > Print.
III. Under Print what, choose Selection to print the selected cells.
To Print Several Worksheets:
I. Select all the worksheets to be printed.
II. Choose File > Print.
III. From the Print dialog choose the selected sheet (s) option under Print What.
To Print a Few Pages
By default, Excel prints all the pages in the workbook. However, selected pages can be printed from a workbook.
I. Choose File >Print.
II. Under Page Range, select Page(s).
III. Type 2 in the from box and type 5 in the to box. Only 2nd Page to 5th Page will be printed.
Print Ranges from Different Worksheets:
Suppose there is data on four different worksheet, which is to be printed. Instead of printing on four different pages, this data from different worksheet can be printed on one single page. To do this…..
I. Select the range to be printed from the first worksheet.
II. Choose Edit >Copy.
III. Activate the worksheet where it is to be printed, select the cell to be pasted.
IV. Hold down the shift key and choose Edit > Paste Picture Link. (This pastes the copied cells as an object on the worksheet).
V. Repeat step 1 through 4 for each separate range of the different worksheet.
WINDOW
Windows and workbook are not synonymous. One workbook can be displayed in more that one window. One of the greatest benefits of this feature is the ability to view two or more worksheets of the same workbook simultaneously.
Displaying a Workbook in More than One Window
To open active workbooks (e.g. BOOK1) in separate windows do the following:
Choose Window>New window (This will create a second window)
Arranging Windows
There are several ways of arranging the different open windows. To do this, choose Window > Arrange. The arrange window box appears. Choose one of the arrange options from the dialog box.
Tiled: è Each window is fully made visible, and Excel decides how best to arrange them based upon the number of open windows.
Horizontal: è Each window is made fully visible and arranged horizontally.
Vertical:è Each window is overlapped, with the title of each window visible.
Arranging the Active Workbook Only
Windows of Active Workbook check box is used to limit the arranging of windows to the active workbook only.
If checked, only the windows of the active workbook are arranged. If unchecked, all windows of all open workbooks are arranged.
Moving Between Windows
There are various ways to move from one window to another window
The bottom of the window menu lists all open windows; choose the one to be made active. If the windows are arranged so that more than one is visible, click on the window to be activated.
Press Ctrl +F6 to activate the next window, or Ctrl +shift + F6 to active the previous window.
Closing Displayed Windows
When manipulating the display of multiple windows of workbook, use the control menu.
If the workbook book 1 is opened in multiple windows and the book has to be closed then choose File. Close. All the windows of the book 1, which are opened, will be closed.
If only one particular window of the Book 1 is to be closed then from the Control box select Close of the window which is to be closed or after selecting the window which is to be closed Press Ctrl + W or just double click on the window control box.
Hiding Windows
To avoid disorganization of the windows opened on the workspace, temporarily hiding a window is possible instead of closing it. To do this, Choose Window > Hide (to hide the active window).
The windows, which are hidden, can be displayed again by selecting window >Unhide and then select the window to be unhidden from the list.
Not: - The unhide option will be enabled only when at least one window is hidden.
CONTROLLING THE DISPLAY
There are different settings that control how elements on the screen are displayed, and these setting apply at different levels like - Workspace, Workbook, Worksheet and Window
Controlling the Workspace Display
Workspace level settings are global to the entire Excel workspace and are not specific to any workbook or workspace.
Hiding the Formula Bar and Status Bar
The Formula bar and Status Bar are hidden to gain additional screen space. If the bars are to be hidden then.
Select View >Formula Bar to hide or display the formula bar.
Select View >Select Bar to hide or display the status Bar.
These setting are permanent, i.e. if the bars are hidden and you exit Excel, then next time, you load Excel, the bars will be still hidden.
Displaying the Full screen
The full screen maximizes the Excel workspace by hiding the toolbars, title bar, status bar, and scroll bars and by displaying the work area across the full screen. A full screen display yields extra space and is mostly used for overhead presentations. Excel remembers these setting from session to session. To maximize the screen select View >Full Screen
To Restore Back to Normal Screen
Select View > Full screen
Controlling the Workbook or Window Display
Workbooks setting are saved with the workbook. If any changes are done to the workbook and later you save the workbook and close the workbook, and when you reopen it, the setting remains in effect. Access these setting by Choosing Tool > Option and then select the View Tab. The different workbook or window settings and Horizontal Scroll Bar/ Vertical Scroll Bar, Sheet Tabs.
Controlling the Worksheet Display
Worksheets setting are applied only to the active worksheet, and are saved only for that worksheet. Setting can be changed by selecting Tools >Option and then selecting the View Tab. Some of the options are listed below.
Row & Column Headings è if this setting is checked, row and column headings are displayed otherwise they are hidden.
Gridlines è If setting is checked, gridlines are displayed, if unchecked then gridline display is turned off for both the screen worksheet and the printer worksheet.
Page Breaks è If this setting is checked, automatic page break are inserted broken lines along the gridlines where page breaks occur. If unchecked, the page breaks will not be displayed until the page is printed. (Page break lines are only show on the screen; they do not appear on the hard copy printout)
Splitting Window Panes
Windows can be split into panes, resulting in either two or four separate scrollable regions on the window. One common reason for splitting is to create row and/or column heading that do not scroll out of view. Another reason is to view different regions of the sheet at the same time.
To split windows into panes, first select the cell(s) to represent the split point. Select an entire column to slit panes vertically.
Select the entire row to split panes horizontally.
Select a single cell to split panes, above and to the left of the cell. Chose Window >split.
To Remove the Split:-
Choose >Remove Split
To Freeze Spit Panes:-
Choose Window >Freeze Panes. This command locks the split in place (i.e. the split panes row and column titles). The following changes will occur.
The split bar or bars turn into solid lines.
The pane above Horizontal split can no longer be scrolled.
The pane to the left of a vertical can no longer be scrolled.
Freezing Window
To freeze, it is not necessary to split the window into the panes. The window freeze option can be used in the following manner.
Freeze the column heading; keep the cell pointer one column adjacent to the column to freeze.
Freeze the row heading, keep the cell pointer one row below the row to freeze.
To freeze the rows and columns together, place the cell pointer one column adjacent to the columns, and one row below the rows to freeze.
Then select Window >Freeze Panes.
To Remove the Freeze
Select Window >Unfreeze
Zooming In and Out:--
To reduce or increase the magnification of the window display, select a magnification level from the pull-down list on the Standard Toolbar or Choose View > Zoom to display the Zoom dialog box. Zooming out gives a big picture of the fit the worksheet model. From the dialog
box displayed select one of the several setting shown or give a Custom setting. If Selection option is selected, the selected range is zoomed to fill the window up to a maximum of 400%. Not àsplitting panes, freezing panes, and zooming do not affect the printed documents.
FILE PROTECTION
PROTECTION è There are several of protection that can be applied to a workbook. The topmost level of protection is set at the file level. The other levels of protecting the workbook are only after opening the workbook.
Protecting Data Within the Workbook.
Essentially, there are four levels of security. They are File Protection, Workbook Level Protection, Worksheet Level and Cell Protection.
File Protection
Passwords can be assigned to a file by selecting the Options button from the File save as dialog box.
Type the password/s and click on OK. In future, Workbook can be opened only if you know the open password. To modify the workbook modify password is necessary.
WORKBOOK PROTECTION
To apply protection to a workbook,
Select Tools > Protection > Protect Workbook to display the Protect Workbook, dialog Box
These are the options in the Protect Workbook dialog box.
Structure è If this option is selected, it prevents changes to worksheet structure i.e. deleting, inserting, renaming, copying, moving, hiding, or un hiding sheets is no possible.
Windows è When this option is selected, it prevents changes to workbooks window, the window control button becomes hidden and most window functions (move, size, restore, minimize, new, close, split and freeze panes) are disabled.
Password (optional) è If required you can type a password which can be up to 255 characters, it can include special characters and is case sensitive.
Once the password is entered and you have clicked on OK, Excel prompts the user to enter the password again for confirmation.
UNPROTECTING A WORKBOOK
To unprotected the workbook, select Tools > Protection > Unprotect Workbook, type the password, which was give to protect the worksheet.
WORKBOOK PROTECTION
Worksheet Protection is used to prevent the user from changing the contents of an individual sheet. To protect a sheet : Select Tools > Protect Sheet Following option will be available-
Contents è If checked, Protects worksheet cells and chart items.
Objects è Protect graphic objects on worksheet (including chance), if the option is marked.
Scenarios è If checked, Prevents changes to scenario definitions,
Password (Optional) è You can enter a password If necessary, which can be 255 character and it can include special characters an it is case sensitive.
CELL PROTECTION
This feature is used to protect the cells individually. By default all cells are locked. The lock is enabled when you select, Tools >Protection > Protect Sheet.
To unlock cells (i.e. of unprotect certain cells in the worksheet )
Block the cells to be unlocked.
Format >cells, then select the Protection Tab. Uncheck the Locked option and click on OK. Tools >Protection > Protect Sheet.
Cell protection does not take effect unless the worksheet is protected. After protecting the worksheet the workbook, the cells, which are unlocked, are available for modification while the rest of the cell are protected.
NotèThe Hidden Option cell under Protection Tab hides formulas after protecting a sheet
AUDITING
Auditing examines the relationships between cells and formulas on the sheet and identified errors. Auditing places arrows that point the cells that provide data to formulas and the cells that contain formulas that refer to the cells. A box is drawn around range of cells that provide data to formulas.
DEPENDENT AND PRECEDENT
Assume that cell B1 has the formula =A1. Then B1 is dependent cell, it depends on A1. A1 is the precedent cell, it precedes B1.
Similarly there can be many levels of dependency and in a complex worksheet a graphical representation of cell relation is achieved by using. Auditing commands.
PRECEDENT TRACER
In the given example, cell D2, has a formula.
Select a dependent cell i.e. D2
Choose Tools > Auditing > Trace Precedents
A tracer arrow will appear to show the prencedent cells of a formula.
DEPENDENT TRACER
Select a source cell i.e.C4.
Select Tools > Auditing > Trace Dependent.
Arrows are displayed pointing from precedent to dependent cell.
ERROR TRACERS
Suppose a cell has a formula, which returns an error. So, the error can be traced by-
Selecting the cell which contains the formula i.e. D3 Tools > Auditing > Trace Error.
Arrows are drawn from the error value in the active cell to the cell that might have caused the error.
Remove Arrows è If all the arrows on the worksheet are to be removed, then, Select Tools > Auditing >Remove All Arrows.
Note è individually the precedent and dependent arrows are removed using the Auditing Toolbars. To display the Auditing toolbar, Tools > Auditing > Show Auditing Toolbar.
ENTERING THE SUMMARY INFORMATION
For each particular workbook, file properties like the title, subject, author, keywords, and comments can be entered. This information is used to local the workbooks. To enter the
properties of a file-
Open the workbook, for which the properties have to be entered.
Select file > Properties.
Enter a title, subject, author, and keyword that might by useful in a future search and comments. Click on OK.
WHAT IF ANALYSIS Goal Seek
Essentially, Goal Seek solves formula backward. Here the result needed as well as the single input to be charged is known. To determine the value of the input to get the desired result, Goal seek is used. When goal seeking. Microsoft Excel varies the value in one specific cell until a formula that is
dependent on that cell returns the results required.
In the above worksheet, the sales made by different sales man, the commission rate and the commission received by each sales man (=B2 *C2) is given. Now sales man HARISH demands to get the commission amount of RS 150. To give him the commission 150, taking into account the same sales 5200 made by him, what will the commission rate be? This can be calculated using Goal Seek.
To start Goal Seek, select cell containing a formula (cell D3), then choose Tools > Goal Seek
The cell containing the formula will be the Set CELL. It will be automatically displayed. In the To value text box, type the target value. In out example, 100. in place of BY changing cell, type the address of cell, the value of which is to be changed. In our example, C3. And click on OK.
EXAMPLE è
To buy a car, a bank loan is available at 9% annual interest rate. The team of loan is 36 months. The loan requires a 20% down payment. The maximum monthly payment affordable is Rs.300. Now, find out at what price the car can be bought depending on the above conditions.
Create the following worksheet.
In cell F4 (loan amount), enter the formula =E4*0.8 (the loan amount is 80% of the purchase price)
In cell E7, enter the formula =PMT (C4/12, D4,-F4) the PMT function calculates the monthly payment, where C4/12 is the interest rate per period, D4 is the number of periods and F4 is the total amount of the loan).
Select cell E7, choose Tools > Goal Seek. Type the values as given below
Click on seek find the answer and display it isin the goal seek status dialog box.
To keep the answer the (and change the values in the worksheet), click on OK on the Goal seek status dialog box.
SCENARIO MANAGER
Scenario Manager lets you create and save different sets of input values, with their results, as scenarios. In excel, a scenario is a group of input values (called changing cells) sved with a name. each scenario represent a set of what if assumptions can be applied to a workbook modle to see the effects on the other parts of the model. Scenario manager can be use to do following:
Create multiple scenarios with multiple sets of changing cells.
View the results of each scenario on the worksheet.
Create a summary report of all input values and results.
Creating scenarios
Suppose a car is to be bought on loan and at present there are two cars, one for Rs 300000, and the other for Rs 400000. The following information applies to both the cars regarding loan conditions:
The term of the loan can be either 12 year or 20year. interest rate is 8% per annum and 25% down paymen t has to be done.
The problem is to find the monthly payment will be in the following case
If the car worth Rs. 3lakhs is purchased with the interest rate 7% and loan term 12 years.
If the car worth Rs. 3lakhs is purchased with the interest rate 7% and loan term 20 years.
If the car worth Rs. 4lakhs is purchased with the interest rate 7% and loan term 12 years.
If the car worth Rs. 4lakhs is purchased with the interest rate 7% and loan term 20 years.
So, there are four scenarios for all the cases given above. Scenario Manager can help to create, manage and summaries these scenarios.
Being by creating a scenario for the RS. 3lakhs car, with a 12 year loan.
1. enter the following on the new worksheet:
2. then calculate the loan amount:
Price * loan percent (100% - down payment) =B5*75% <Enter>
3. then calculate the payment:
=PMT (Rate, Term, - Loan Amount)
=PMT (C5/12, D5*12,- E5) <Enter>
4. Select Payment and choose Tool > Scenario. Then the Scenario Manager dialog box will appear
5. In this dialog box click on Add and add the Scenarios.
6. And then click on OK.
7. Do some editing or change the value in the above dialog box if required and click on ADD till all Scenario entered into the Scenario Manager other wise click on OK .
8. If we do click on OK then a dialog box appears….
9. then we do click on Summary. Then the Scenario Summary Report display like this.
if scenario Pivot table is selected then a Pivot Table will be created based on the
scenarios.
EDITING SCENARIOS
If at any time, changes have to be made to the existing scenarios, choose Tools >Scenario. Choose the name of the scenario from the list and click on Edit button. Make the necessary changes and click on OK.
Choose Tool > scenarios. Select the scenario to be deleted. click on the Delete Button.
PROTECTING SCENARIOS
Scenarios can be protected using the Prevent changes on the Add Scenario and Edit Scenario dialog boxes. By default, this setting are selected and takes effect when the worksheet is protected.
More Facts about Scenario Manager:-
up to 32 changing cells can be defined per scenario.
When a scenario is created or edited. The user name and date are recorded by scenario Manager. This information is displayed in the Scenario Manager dialog box and in the first outline level of the Summary report.
TEMPLATES
Creating Templates è Any workbook can be created as a template.
To Create a Workbook Template.
Create a new workbook, or open an existing one.
Enter constants, formula, and formatting (a template can also include charts and macros).
Select File > Save as to call up the Save As dialog box.
Select Template from the Save As Type drop-down list.
Enter filename, then click on Save.
Save the workbook under XLSTART folder as a template.
To Create a Workbook Template
Creating a template govern the appearance of all newly created individual worksheet.
These templates are called as Auto Templates.
For Example, suppose at any time, worksheet is to be inserted into an existing workbook by right clicking on the Sheet tab and selecting insert. Creating a worksheet template is to create a special sheet in the workbook. The formatting will be in place automatically.
Create a workbook consisting of one worksheet. Format the sheet as desired. Sane the file by specifying the file type as Template.
Name the file as SHEET and save it in the XLSTLART folder (within the Microsoft Excel folder) or the alternate startup folder.
To Insert the Worksheet Template
Open a workbook.
Right click on the sheet tab to insert a sheet.
From the Shortcut menu the icon with the name SHEET.
The new sheet is inserted. This is similar to sheet template.