Chapter 6:MS-Excel

Overview of excel
 Microsoft Excel is a software program included in the Microsoft
Office suite.
 It is used to create spreadsheets, which are documents in
which data is laid out in rows and columns — like a big table.
spreadsheet?
 A spreadsheet is a special way of organizing data
into rows and columns to make it simpler to read and
manipulate.
Features of spreadsheet
1.Table formats:- By using the Format as Table icon you instruct Excel to
treat a table like a basic database. Formatted tables have many advantages,
such as: The table range automatically expands (including the format) when
you add new rows or columns to the table. Filter icons are automatically added
to the header row

2.Data forms
a)Labels or titles: Entering data into a spreadsheet is just like typing in a
word processing program, but you have to first click the cell in which you want
the data to be placed before typing the data. All words describing
the values (numbers) are called labels. The numbers, which can later be used
in formulas, are called values.
b)Numbers :Numbers are data the data that are directly entered into
cells of the spreadsheet.
c)Calculated entries: calculated entries are received using formula ,
the spread sheet software calculates the value and puts the
calculated entries data
3.Recalculation:spread sheet automatically calculates the results
after entering numbers and formulas.
4.Storage and retrieval:- the spread sheet software has ability to
store and retrieval the data as many times as required.
5.Presentation: Today most of the spreadsheet packages have
capacity to convert the figures into graph and charts.Helps in
understanding graphs and charts.
6.Standard formats:-Data is download from other computers and
data sources are used to standrard formats.

Work book
 In Microsoft Excel a workbook is a collection of one or more
spreadsheets or worksheets.
 It is a file where the user stores his data
 Each workbook consists of several worksheets .
Work sheets
 A worksheet or sheet is a single page in a file created with an
electronic spreadsheet program such as Microsoft Excel

Cell
 A cell is a rectangular box that occurs at the intersection of a vertical
column and a horizontal row in a worksheet
 A cell can only store 1 piece of data at a time. You can store data in
a cell such as a formula, text value, numeric value, or date value.
Open new workbook
1. Select file -> New from the menu bar
2. The new workbook pane appears on the right hand side of the screen.
Under the title new select blank workbook

Opening a existing workbook

 Click file-> open from menu bar,
Or
  Click open button found on the standard toolbar to open an existing
workbook.

 Select the folder that contains your workbook

 Select your workbook
 Click open
Saving a workbook
 Click file save from menu bar
or
 To save click on the save button found on the standard toolbar.
 Choose a directory to save the file in
Specified file name
 Click save
Formatting excel
To format worksheet cells:

1. Select the cells to format.

2. Select the command.

The Format Cells dialog box opens.

On the Number tab, set the number format for the selected cells.

1. Click the Alignment tab to specify text alignment options.

2. Click the Font tab to specify the font, font size, style, and color of
text in selected cells. See Formatting Text.

3. Click the Border tab to set cell border formatting options.

Select the Line Attributes, and then use the Presets or Preview buttons
to add or remove border elements.

4.Click the Patterns tab to specify fill options for the selected cells.
 5.If Vector works Design Series is installed, click the Images tab to specify
the type, size, view, and margin for images in the selected cells. For more
information, see Inserting Images in Worksheet Cells.

To apply number formats:

1. Select the cells for which you want to change the number format.
2. Select Format_Cells and select the Number tab.
3. Select a category for the number format from the Category list.
4. Type a number format or select a format type from the Type combo box.
5. You can type a built-in format or a custom format.
6. Click OK.
 
Formatting Fonts
You may choose from many different fonts, font sizes, font styles, and font
colours for any cells in your workbook.
 
The Format_Default Font menu option enables you to set a default font, font
size, and style that will apply to all cells in all worksheets in the workbook.
Later you can change these settings for individual cells. Changes you make to
individual cells will remain, even if you change the default settings.
 
Note that by default, the StatsDirect workbook uses Arial as the default font.
Be sure you always use a TrueType font as the default font in order for print
and display scaling to work correctly.
 
To set font formats for individual cells:
1.  Select the cells you wish to format.
2.  Choose Format_Cells and click the Font tab.
3.  Select the font format settings you want, and click OK.
 
Aligning Data
The StatsDirect workbook enables you to specify how data is aligned within a
cell. The standard alignment places text along the left edge of the cell and
numbers along the right edge of the cell. Logical and error values are centred.
 
To align text in a worksheet:
1. Select the cells for which you want to align the contents.
2. Select Format_Cells and select the Alignment tab.
3. Specify the horizontal and vertical alignment of data in the selected cells
using the Horizontal and Vertical lists.
4. Select the Wrap Text option to wrap long strings of data to multiple lines
within the cell.
5. Click OK.
 
Merging Cells
You may merge two or more cells in order to create headings that span many
columns or a column entries that span many rows. You can use merged cells
to, for example, create a heading for several different columns of data, or to
insert a block of text on a worksheet.
 
The Stats Direct workbook removes the cell borders between the merged cells
and replaces any data in the cells with the data in the top left cell in the
selection. To include all data in the range in the merged cell, copy all the data
into the upper-leftmost cell within the range before merging.
 
Merged cells function as a single cell on the worksheet, with the row/column
reference of the cell in the top-left corner of the range. For example, if you
merge cells A1:B5, the resulting cell will have the cell reference A1.
 
To merge cells:
1. Select the cells you want to merge.
2. Select Format_Cells and select the Alignment tab.
3. Select the Merge Cells option.
4. Click OK.
 
Pasting the entire merged cell range
Whenever you paste a range that contains merged cells, the Stats Direct
workbook requires you to paste one or more copies of the entire range. It will
not paste portions of the range containing the merged cell(s).
 
This means that you must select a destination range (the range you are pasting
into) that is:
 equal to the number of rows and columns in the source range, or
 a multiple of the number of rows and columns in the source range, or
 a single cell.

Setting Default Row Height and Width

The Stats Direct workbook provides menu commands that allow you to define
the default row height and default column width for your entire worksheet.
 
To define the default row height of a worksheet:
1. Select Format_Row_Default Height to display the Default Row Height
dialog box.
2. Select the Custom option button define the default height of rows (or for
StatsDirect to automatically adjust the height of rows based on the values in
cells, select the Auto option button).
3. Enter a custom setting for the row height in the Custom text box.
4. Select whether the custom row height is entered as inches or
centimetres from the Units drop-down list.
5. Click OK.
 
To define the default column width of a worksheet:
1. Select Format_Column_Default Width to display the Default Column
Width dialog box.
2. Enter a default width for columns and select a unit from the Units drop-
down list.
3. Click OK.

Sizing Rows and Columns Using Menu Commands

You can set the width of selected columns and the height of selected rows
using menu commands.
 
To set the row height of a selection:
1. Select the rows for which you want to set the height.
2. Select Format_Row_Height to display the Row Height dialog box.
3. Select the Custom option button define a custom row height for the
selected rows (or for StatsDirect to automatically adjust the height of rows
based on the values in cells, select the Auto option button.).
4. Enter a custom setting for the row height in the Custom text box.
5. Select whether the custom row height is entered as inches or
centimetres from the Units drop-down list.
6. Click OK.
 
To set the column width of a selection:
1. Select Format_Column_Width to display the Column Width dialog box.
2. Enter a custom width for the selected columns and select a unit from the
Units drop-down list.
3. Click OK.
 
Sizing Rows and Columns Using Click and Drag Actions
When you position the pointer on the right edge of a column heading or the
bottom edge of a row heading, the pointer changes to a double arrow to
indicate that the row or column can be resized. Simply click and drag to resize
the column or row.
 
If multiple rows are selected when you resize a row, all selected rows are
resized as you drag a row border. Multiple columns can be resized in the same
manner.
 
Double-click the bottom border of a row heading to automatically adjust the
height of all cells in the row to accommodate the largest font size in the row.
Double-click the right side of a column heading to automatically adjust the
width of all cells in the column to accommodate the largest entry.
 
You can also set the size of a selected group of columns or rows to match the
size of an existing row or column. First, select the group of rows or columns
you want to resize, including the row or column whose size you want to match.
Then, click the right border of the column heading or the bottom border of the
row whose size you want to match. The selected rows are resized to match the
size of the row or column you clicked.
 
Freezing Horizontal and Vertical Panes
To scroll through your worksheet and see designated headings for columns or
rows, split the worksheet into panes by "freezing" them.
 
Data contained in frozen panes cannot be edited. You must perform any data
editing in these panes prior to freezing. If you attempt to select a cell in a
frozen row or column, the entire row or column is selected, just as if you
selected a row or column heading.
 
To freeze horizontal panes:
1. Select a cell in the row below where you want to split the panes.
2. Select Format_Freeze Panes. The rows above the split are frozen.
 
To freeze vertical panes:
1. Select a cell in the column to the right of where you want to split the
panes.
2. Select Format_Freeze Panes. The columns to the left of the split are
frozen.
 
To freeze panes using the Format Sheet dialog box:
1. Select Format_Sheet Settings and select the View tab.
2. Enter a range for the cells that you want to freeze in the Fixed Rows and
Fixed Columns text boxes. For example, to freeze columns A - B and rows 1-
4 enter $1:$4 in the Fixed Rows text box and $A:$B in the Fixed Columns
text box.
3. Click OK.
 
Setting Cell Borders
Borders can be applied to the top, bottom, left, and right sides of a cell. When
you add a border to a range, you can place a border around the outside of the
range.
 
To format cells with borders:
1. Select the cells you want to format.
2. Select Format_Cells and select the Border tab from the Format Cells
dialog box. Note that you must select a line style and colour before you
select the location of the borders.
3. Select borders.
4. Click OK.
 
Setting Cell Fill Colours and Patterns
When you apply colours and patterns to a cell or range, you specify the pattern
and foreground and background colours used to fill the cells.
 

To format cells with colours and patterns:

1. Select the cells you want to format.
2. Select Format_Cells and select the Patterns tab from the Format Cells
dialog box.
3. Click a fill colour in the Fill Colour palette to select it.
4. Click a pattern colour in the Pattern Colour palette to select it.
5. Click a pattern style in the Fill Pattern palette to select it.
6. Click OK.
 
Formatting Row and Column Headings
In addition to formatting worksheet cells, many aspects of row and column
headings can be formatted. Worksheet headings contain three areas: the row
headings, column headings, and the box in the top left corner of the worksheet
where the row and column headings intersect.
 
To select headings interactively:
 Press CTRL+Shift and click the heading area.
 
After a heading area is selected, you can set:
 the alignment of the heading text.
 the font and colour of the heading text.
 the pattern and fill colour of the heading area.
 the border used to frame heading cells.
 
To interactively change the size of column headings:
 Click and drag the bottom edge of the top left corner.
 
To interactively change the size of row headings:
 Click and drag the right edge of the top left corner.
 
To interactively change the text for a row or column heading:
1. Double-click the heading for which you want to enter text to display the
Header Name dialog box.
2. Enter one or more lines of text to serve as the heading name.
3. Click OK.
 
Formulas in excel
 To perform calculations user can create formulas.
Formulae can be of three types.
1.Text formulae
2.Numeric formulae
3.Logical formulae.
Text formulae
 It uses text and hence it is called text formulae
 Example :- =A1&B1
=“computer”&”science”
Numeric formulae
 It uses arithmetic operators like +,-./,* or%
 Example:- =A2+B2
=B4/C4
Logical formulae
 It uses comparison operators like <,>,<=,>=,==,!=
 Example :- =A2>=18
Functions
 Function are special pre-written formulae that take a values or value to
perform operation and return a value or values.
Function consist of two parts
1.Function name
2.Argument name
=function(value,......)
Mathematical function :
1.ROUND():- this function rounds a number to the specified number of decimal
places
Syntax: ROUND(num1,num2)
Example: ROUND(3.786,2) it would return 3.7
2.SUM():-this function adds all the numbers in a range of cells or in the list of
numbers.
Syntax:-SUM(num1,num2)
Example:-SUM(10,20)
3.SQRT():- this function returns a square root of specified number
Syntax:- SQRT(num)
Example :-SQRT(16) returns 4
4.MOD():-this function returns the remainder after number is divided
Syntax:-MOD(num1,num2)
Example:- MOD(15,10)
5.PRODUCT():- this functions multiplies and returns the product of the number.
syntax:-PRODUCT(num1,num2)
Example :-PRODUCT (5,8) returns 40

Statistical function
1.Max ():- this function returns largest value in a set of values.
Syntax:- MAX(num1,num2,…..)
Example :-MAX(15,20,25,45,60)
2.MIN():- this functions returns smallest value in a set of values
Syntax :-MIN(num1,num2)
Example :-MIN(10,12,24,34)
3.COUNT():- this function counts the number of cells that contain numbers
Syntax:- COUNT(value1,value2,…..)
Example :-COUNT(15,25,35,45) returns 4
4.AVERAGE ():- this function returns the average of the arguments
Syntax:-AVERAGE(num1,num2,….)
Example:-AVERAGE(50,10,30,40,20) returns 30
Logical functions
1.And() :- this function return TRUE if all arguments are TRUE, else FALSE if one
or more arguments are FALSE.
Syntax:- AND(logical 1,logical2.....)
Example :-AND(3<5,8=8) returns TRUE.
2.OR():- This function returns TRUE if one or more arguments are true, returns
false if all arguments are false
Syntax:-OR(logical1, logical2,....)
Example :-OR(3<5,8!=8)
3.Not() : The NOT function changes TRUE to FALSE, and FALSE to TRUE.
Syntax: NOT(logical)
Example: NOT(3<5) return FALSE.
4.If():-this function returns one value if logical test evaluates true or other
value if it evaluates to false
Syntax:-if(logical_test, true_value,false_value)
Example:- if(89>80, “Distinction”, or “first class”);
Text functions
1.LEN(): this function helps to know the length of the string.
Syntax:-LEN(TEXT)
Example :-LEN(APPLE);
2.UPPER():-this functions helps us to convert the text into upper case from
lower case.
Syntax:- upper(text);
Example:-upper(apple);
3.Concatenate(): this function helps to join the text of two or more cells
Syntax:-concatenate(text1,text2);
Example:-concatenate(“computer”,”fundamentals”);
4.Rept ():this function used to repeat certain text number of times.
Syntax:-rept(text, number of times)
Example (A1,2)
Date and time function
1.Date():- To add a number of days to a date, use the following simple formula.
Syntax:-DATE(Year, Month, Day)
Example :- DATE(2019, 10, 20);
2.Day ():- this function returns the day of the month.
Syntax:- DAY()
Example :- Day(“12-april”)returns 12
3.Year ():-this function returns the year corresponding to serial number.
Syntax :-year()
Example :year(“12/4/2007”)
4.Now ():- this function returns the current date and time
Syntax:- Now()
Example :-Now()
Charts
A chart is a graphical representation of data, in which "the data is represented
by symbols, such as bars in a bar chart, lines in a line chart, or slices in a pie
chart". It can represent tabular numeric data, functions or some kinds of
qualitative structure and provides different info.
Types of charts:-
1. column chart is a graphic representation of data. Column charts
display vertical bars going across the chart horizontally, with the
values axis being displayed on the left side of the chart.
2. bar chart or bar graph is a chart or graph that presents categorical
data with rectangular bars with heights or lengths proportional to
the values that they represent. The bars can be plotted vertically or
horizontally. A vertical bar chart is sometimes called a line graph.
3. line chart or line plot or line graph or curve chart is a type
of chart which displays information as a series of data points
called 'markers' connected by straight line segments. It is a
basic type of chart common in many fields
4. pie chart is a circular statistical graphic, which is divided into
slices to illustrate numerical proportion. In a pie chart, the arc
length of each slice, is proportional to the quantity it
represents.
5. Area chart or area graph displays graphically quantitative data. It is based on
the line chart. The area between axis and line are commonly emphasized with
colors, textures and hatchings. Commonly one compares two or more
quantities with an area chart.

6.scatter plot, also known as a scatter graph or a scatter chart, is a two-

dimensional data visualization that uses dots to represent the values obtained
for two different variables - one plotted along the x-axis and the other plotted
along the y-axis.