1
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceMICROSOFT EXCEL 2007
Contents
1. Definition and Advantages of Spread sheet
2. MS Excel 2007 Environment or Parts of MS Excel 2007
3. Create a Workbook
4. Save a workbook
5. Open a workbook
6. Entering Data
7. Charts in MS Excel 2007
Parts of charts
Types of charts
Draw a chart in MS Excel 2007
Modifying Charts
8. Functions in MS Excel 2007
Definition
Parts of a functions
About function wizard
2
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science Definition of a Spread sheetElectronic Spreadsheet (or) work sheet is a table of values arranged in rows and columns. These values can take many forms such as text, dates and times, and numbers (including currency and percentages). Each value is stored in a cell.
The spreadsheet applications include preparation of reports, payrolls, bills, income statements etc. We also create formulas in cells. Excel helps you quickly build formulas using your keyboard and mouse.
Advantages of Spreadsheet
1. Calculation can be done easily and quickly.
2. It produces accurate results.
3. Date in a worksheet can be displayed and manipulated.
4. Data can be converted into a graph.
5. The entire worksheet or part of it can be printed.
************************************************************************************
3
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science The Microsoft Office ButtonIn the upper-left corner of the Excel 2007 window is the Microsoft Office button. When you click the button, a menu appears. You can use the menu to create a new file, open an existing file, save a file, and perform many other tasks.
The Quick Access Toolbar
Next to the Microsoft Office button is the Quick Access toolbar. The Quick Access toolbar gives you with access to commands you frequently use. By default, Save, Undo, and Redo appear on the Quick Access toolbar. You can use Save to save your file, Undo to roll back an action you have taken, and Redo to reapply an action you have rolled back.
The Title Bar
Next to the Quick Access toolbar is the Title bar. On the Title bar, Microsoft Excel displays the name of the workbook you are currently using. At the top of the Excel window, you should see "Microsoft Excel - Book1" or a similar name.
The Ribbon
4
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science WorksheetsMicrosoft Excel consists of worksheets. Each worksheet contains columns and rows. The columns are lettered A to Z and then continuing with AA, AB, AC and so on; the rows are numbered 1 to 1,048,576. The number of columns and rows you can have in a worksheet is limited by your computer memory and your system resources.
The combination of a column coordinate and a row coordinate make up a cell address. For example, the cell located in the upper-left corner of the worksheet is cell A1, meaning column A, and row 1. Cell E10 is located under column E on row 10. You enter your data into the cells on the worksheet.
************************************************************************************
Create a Workbook To create a new Workbook:
Click the Microsoft Office Toolbar
Click New
5
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceIf you want to create a new document from a template, explore the templates and choose one that fits your needs.
6
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science Save a WorkbookWhen you save a workbook, you have two choices: Save or Save As. To save a document:
Click the Microsoft Office Button
Click Save
You may need to use the Save As feature when you need to save a workbook under a different name or to save it for earlier versions of Excel. Remember that older versions of Excel will not be able to open an Excel 2007 worksheet unless you save it as an Excel 97-2003 Format. To use the Save As feature:
Click the Microsoft Office Button
Click Save As
Type in the name for the Workbook
7
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science************************************************************************************
Open a Workbook
To open an existing workbook:
Click the Microsoft Office Button
Click Open
Browse to the workbook
Click the title of the workbook
8
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science************************************************************************************
Entering Data
There are different ways to enter data in Excel: in an active cell or in the formula bar. To enter data in an active cell:
Click in the cell where you want the data
Begin typing
To enter data into the formula bar
Click the cell where you would like the data
9
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceType in the data
************************************************************************************
Explain about Charts in MS Excel 2007
Charts allow you to present data entered into the worksheet in a visual format using a variety of graph types. Before you can make a chart, you must first enter data into a worksheet.
Types of Charts
Microsoft Office Excel 2007 provides various types of charts to help you to display data in different ways as per the need of the viewers. You can create a new chart or can change the existing chart, from the wide range of chart subtypes available for each type of available chart types as given below
Column Chart:
This type of chart is used to compare values across categories. They give very effective results to analyze the data of the same category on a defined scale.
Line Chart:
Data represented in columns or rows in a worksheet can be plotted with the help of line chart. Line charts can be used to display continuous data over time with respect to a common scale.
Pie chart:
In a situation where one has to show the relative proportions or contributions to a whole, a pie chart is very useful. In case of pie chart only one data series is used. Small number of data points adds more to the effectiveness of pie charts. Generally there should be maximum five or six data points or slices in a pie chart. If the points are more in number then it becomes very difficult to interpret the chart.
10
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science Bar Chart:Bar charts are used to show comparisons between individual items. To make a bar chart the data should be arranged in the form of rows and columns on a worksheet.
Area Chart:
The data which is arranged in the form of rows or columns on a worksheet can be plotted in an area chart. Area charts are used to highlight the degree of a change over time. Area charts are also used to draw attention to the total value across a trend.
XY (Scatter) chart:
XY charts are also known by other names like scatter grams or scatter plots. The point of difference between XY charts and other types of charts is that in XY charts both axes display values i.e. they have no category axis. Such type of charts is generally used to show the relationship among two variables.
Stock chart:
Stock chart can be used to plot data arranged in columns or rows in a particular order on a worksheet.Even scientific data can be plotted by stock chart, e.g., the fluctuation of daily or periodic temperature. The data must be arranged in a correct order to generate stock charts.
Bubble chart:
A bubble chart can be used to plot the data values which are arranged in the columns of a worksheet so that x values are listed in the first column and matching y values and bubble size values are listed in adjacent columns.
Radar chart:
11
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science Components of Chart in MS Excel 2007Chart Title: A title given to the whole chart.
X-Axis Title: A title given to the X
Y-Axis Title: A title given to the Y
X-Axis Category: These are the categories of the data which have been plotted. These are taken from the first column or first row of your data range.
Y-Axis Value: This is the data range marked to plot the data series.
Data Labels: The values of the data series plotted.
Legends: Specifies the color, symbol or pattern used to mark data series.
Grid Lines: Displays lines at the major intervals on the ca
B Naresh, Lecturer in BVRICE, Dept., of Computer Science B Naresh, Lecturer in BVRICE, Dept., of Computer Science B Naresh, Lecturer in BVRICE, Dept., of Computer Science B Naresh, Lecturer in BVRICE, Dept., of Computer Science Components of Chart in MS Excel 2007
A title given to the whole chart.
A title given to the X-axis data range.
A title given to the Y-axis data range.
These are the categories of the data which have been plotted. These are taken from the first column or first row of your data range.
This is the data range marked to plot the data series.
The values of the data series plotted.
Specifies the color, symbol or pattern used to mark data series.
Displays lines at the major intervals on the category (x) axis and/or Y
B Naresh, Lecturer in BVRICE, Dept., of Computer Science B Naresh, Lecturer in BVRICE, Dept., of Computer Science B Naresh, Lecturer in BVRICE, Dept., of Computer Science B Naresh, Lecturer in BVRICE, Dept., of Computer Science
These are the categories of the data which have been plotted. These are taken from
12
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science How to Draw a Chart in Excel 2007Following steps are given to draw a Chart
1. Enter data in the work sheet: Suppose you entered data as given in Fig. 8.16.
2. Now select data range: By using the mouse high light the range of data you want to take (see Fig.
8.17).
13
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science4. Select the sub type of chart
14
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science5. Select the Title of the chart
To give a title to a chart, click on the chart. Now you can see layout tab available. Click on
Layout tab.
Choose(click) on chart title option available in the Label group
Click on the chart title and write a title.
6. Give a name to X-Axis
Click on Layout tab.
Then select Axis Titles from Labels Group.
15
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceNow, click on the Axis Title and write an X-axis title. Follow the same steps to give a title to Y-axis.
Editing of a Chart
1. How to change the legend position
Click on Layout tab.
Then click on Legend option available in Labels Group.
16
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science2. How to change the position of the Data Labels
Click on Layout tab.
Then click on Data Label option available in Labels Group.
17
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science3. How to show Data Tables Along with Chart, with or without Legend Keys
Click on Layout tab.
Then click on Data Table option available in Labels Group.
Now choose a style to display data table
4. How to Hide/Unhide Grid Lines (Horizontal and Vertical)
Click on Layout tab.
In the Axis Group, click on Gridlines option then choose Primary Horizontal Grid Lines.
Now select appropriate style from the available options (None, Major Gridlines, Minor
18
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science************************************************************************************
FORMULAS AND FUNCTIONS
Excel’s worksheet functions are power tools that help to perform complex computations. In Excel 2007, the formulas are available in the Formulas Tab. If you click on the Formulas tab, you can see the corresponding ribbon display with available formulas, as shown below.
The parts of a function:
Each function has a specific order, called syntax, which must be strictly followed for the function to work correctly.
Syntax order:
19
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science2. After the = sign, define the function name (e.g., Sum).
3. Then there will be an argument. An argument is the cell range or cell references that are enclosed
by parentheses. If there is more than one argument, separate each by a comma.
An example of a function with one argument that adds a range of cells, A3 through A9:
An example of a function with more than one argument that calculates the sum of two cell ranges:
Use of Function Wizard in Excel
Excel’s Function wizard greatly simplifies the use of the functions. It provides the necessary steps to work with the functions in Excel. The following are the steps to work with the Function wizard in Excel.
1. Start by activating the cell where we want to paste the function.
2. Begin the formula with an equal sign(=).
3. To open the function wizard go to Formulas tabInsert Function. Then the following window will
20
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science4. Pick a category from the left list box and select the desired function from the right list box.
5. Read the wizard’s description of the function to make sure about the function.
6. Click the Next button and the second wizard dialog box appears.
For example the following is the dialog box for FV function.
7. We can type directly in the wizard’s entry boxes or use the mouse pointer to point the cells
21
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science Categories of functionsThe following are the various categories of functions.
1. Date and Time
2. Financial
3. Information
4. Logical
5. Mathematical and Trigonometric
6. Statistical
7. Text
Date and Time Functions
These functions can be used to work with dates and times in MS-Excel.
1. Date: Returns the serial number that represents a particular date
Syntax: Date(year,month,day)
2. Now: Returns the current date and time. If we simply paste the formula =Now () into a cell, it will
display current date followed by the current time, then update the cell’s content every time the
worksheet is recalculated.
Syntax: Now()
Financial Functions
1. FV: Returns the future value of an investment based on periodic, constant payments and a
constant interest rate.
Syntax: FV(rate,nper,pmt,pv,type)
Here, Rate is the interest rate per period.
Nper is the total number of payment periods in an annuity.
Pmt is the payment made each period
Pv is the present value. If pv is omitted, it is assumed to be 0 (zero).
Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is
assumed to be 0.
Problem: For example we deposit $1,000 into a savings account that earns 6 percent annual interest
22
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Scienceevery month for the next 12 months. How much money will be in the account at the end of 12 months?
Sol: FV(0.5%, 12, -100, -1000, 1) equals $2301.40
2. PV: Returns the present value of an investment.
Syntax: PV(rate,nper,pmt,fv,type)
Here, Rate is the interest rate per period.
Nper is the total number of payment periods in an annuity.
Pmt is the payment made each period
FV is the future value. If fv is omitted, it is assumed to be 0 (zero).
Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is
assumed to be 0.
Example: PV(0.08/12, 12*20, 500, , 0) equals -$59,777.15
Math & Trig Functions
1. ABS(): Return the absolute value of a number. The absolute value of a number is the number
without its sign.
Syntax: ABS(number)
Number is the real number of which you want the absolute value.
Examples: ABS(2) equals 2
ABS(-2) equals 2
2. PI: Return the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
Syntax : PI( )
Examples: PI()/2 equals 1.57079...
3. Power: Returns the result of a number raised to a power.
Syntax: POWER(number,power)
Number is the base number. It can be any real number.
Power is the exponent to which the base number is raised.
Examples: POWER(5,2) equals 25
4. Product: Multiplies all the numbers given as arguments and returns the product.
Syntax: PRODUCT(number1,number2, ...)
Number1, number2, ... are 1 to 30 numbers that you want to multiply.
Examples: If cells A2:C2 contain 5, 15, and 30:
23
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science5. Sin: Returns the sine of the given angle.
Syntax: SIN(number)
Number is the angle in radians for which you want the sine
Example: Sin(90)= 0.893997
6. Cos: Returns the cosine of the given angle.
Syntax: COS(number)
Number is the angle in radians for which you want the cosine
Example: Cos(0)=1
7. Sum: Adds its arguments
Syntax: Sum(arg1,arg2,....)
Example: Sum(10,20,30)=60
8. SQRT: Returns a positive square root for the given number
Syntax: SQRT(Number)
Example: SQRT(9)=3
Statistical Functions
1. Average: Returns the average of its arguments.
Syntax: AVERAGE(number1,number2, ...)
Example: If A1:A5 contains the numbers 10, 7, 9, 27, and 2, then:
AVERAGE(A1:A5) equals 11
2. Count: Counts how many numbers I the list of arguments.
Syntax: COUNT(value1,value2, ...)
3. Max: Returns the maximum value in a list of arguments.
Syntax:Max(value1,value2,....)
4. Min: Returns the minimum value in the list of arguments
Syntax: Min(value1,value2,...)
5. CountA: Counts how many values in the list of arguments
Syntax: CountA(value1,value2,....)
6. Large: Returns the kth largest value in a data set
Syntax: Large({value1,value2,....},2)
It finds the 2nd largest value in the given list of values.
24
B Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer ScienceB Naresh, Lecturer in BVRICE, Dept., of Computer Science Syntax: Small({value1,valu2,....},2)It finds the 2nd smallest value in the given list of values.
Text Functions
These functions are used to analyze and manipulate strings of text in cells.
1. CHAR: Returns the character specified by the code number.
Syntax: Char(number)
Example: Char(65)=A
2. Lower: It converts the given text into lower case.
Syntax: Lower(text)
Example: Lower(“LAB”)=lab
3. Upper: It converts the given text into upper case
Syntax: Upper(text)
Example: Upper(“abc”)=ABC
Logical Functions
Excel provides some type of logical functions to perform the logical operations easily. For
example consider the following logical function.
IF(): Use IF to conduct conditional tests on values and formulas. It returns one value if a
condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
Syntax
IF(logical_test,value_if_true,value_if_false)
Information Functions
These functions inspect things and report back. For example, the INFO() function, returns
information about the current operating environment.
Syntax: INFO(type_text)
Type_text is text specifying what type of information you want returned
For example,
