MS Excel 2007
Introduction to excel
Microsoft
Excel is a spreadsheet program that allows us to enter, analyze and manage our
numerical data by providing an enhanced user interface. The new Microsoft Excel
2007 user interface includes some new powerful tools and features as well as
enhancements to the existing tools and features. With the introduction of the
new and improved user interface, creating and designing Excel spreadsheets has
become much quicker in excel 2007. In the new Microsoft excel 2007 user
interface, we have more color formats to group and manage our worksheet data,
more styles to apply on them and new table formats to group and manage our
worksheet data, working with charts has also become easier with the
introduction of the new chart tools.
We
can apply special formatting to some worksheet cells that we want to
distinguish from other cells on the worksheet. Special formatting may include
formatting a range of worksheet cells according to the values stored inside the
cells, which is called as conditional formatting.
Working with Tables and Charts
Tables
and charts are the two most popular mediums that you can use to present your
worksheet data in a more attractive and professional looking manner and also
make the analysis of data a lot easier. Presenting your worksheet data in the
form of a table gives a more professional look to your worksheet data and also
makes the analysis of worksheet data much easier and faster. When creating a
table from a blank cell range, you need to first select a cell range in the
worksheet, convert it into a table, and then add data to the table.
Working with charts
Graphics
are always considered as a strong medium to grab the immediate attention of the
reader. Hence by presenting some idea or concept in the form of graphics, you
can easily explain it to the reader without making too much effort. A chart is
a medium that is used to graphically present the worksheet data in excel Formula and Functions
While
working in excel, we often come across situation when we need to perform some
sort of calculation on the data present in a worksheet. Performing calculations
in excel largely involves the use of formulas and functions. It is easy to
perform calculations manually as long as we are dealing with a small amount of
data in an excel worksheet. However, if we are dealing with a large volume of
data, then performing calculations manually is no longer feasible. For
examples, consider a case where we have to calculate total marks and percentages
of 1000 candidates who appeared in an examination. In this scenario, performing
all the calculations manually will take a lot of time as well as it does not
guarantee 100% accuracy. Here comes the need of formulas and functions that
make us perform large and complex calculations very quickly and accurately.
Working with formulas
Like
a mathematical formula, an excel formula is also used to perform some
calculation on data. However, unlike a mathematical formula, an excel formula
always begins with an equal sign (=). Suppose we want to calculate the sum of
two numbers 5 and 3. In general mathematical terms, we write it as 5+3=. The
same using an excel formula will be written as =5+3. The equal sign in the
beginning of an excel formula suggests that whatever follows is a formula
rather than a normal cell entry. An excel formula may contain any or all of the
following:
Functions: these are prewritten formulas,
for example excel’s PI function, which returns the value of Pi, that is, 3.142.
Cell references: a cell reference is an address
that uniquely identifies a cell in a worksheet. A cell reference has two
parts-first, the name of the columns that the cell belongs to, for example, A,
B, C and second, the row number for the row of the cell belongs to, for example1,
2, 3. Hence the cell reference B will refer to the fifth cell down in the
column B in an excel worksheet.
Constants: these are the numbers or text
values entered directly into a formula, such as 5.
Operators: These are used to perform
operations on the value used in a formula, such as + (used to perform addition
operation) and * (used to perform multiplication operation). Equal sign (=) is
also an operator that comes in the beginning of all excel formulas.
Creating a formula
In
order to perform any calculations on our worksheet data, we have to create
formulas in excel. While creating a formula, we can use the cell reference for
a worksheet cell to reference the value that is stored in the cell in the
formula instead of directly entering the cell value in the formula. For
example, if we want to add the values stored in two worksheet cells, A1 and B1,
we will write the formula as =A1+B1.
Referencing cells from other worksheets
When
you create a formula in an excel worksheet, you can reference cell from other
worksheets, within the same workbook, in that formula. We can reference a cell
from a worksheet, other than the one we are currently working on, by first
typing the worksheet name, then an exclamation sign (!) and finally the cell
reference. For example we can reference a cell B5 of a worksheet named sheet 3
as sheet3! B5.
Working with functions
Suppose
we are dealing with a large volume of data and we need to perform same types of
calculations again and again in one or more of our worksheets. In this scenario
for performing same types of calculations, we need to create same type of
formula again and again, which does not seem a feasible solution. Excel
introduces functions to provide us with a better alternative to solve this
problem. An excel function is a prewritten formula, which takes some arguments
as input, performs some calculations with these arguments, and gives the final
result of the calculations as output. An excel functions also begins with an
equal sign (=) because it is also a formula. Excel 2007 provides many
predefined functions, which are very helpful in performing large and complex
calculations. Some commonly used excel functions are given in table.
|
Functions |
Syntax |
Description |
|
SUM |
SUM (numb1, numb2...) |
It adds the specified arguments. |
|
AVERAGE |
AVERAGE (numb1, numb2…) |
It returns the average of a number of
arguments. |
|
SQRT |
SQRT(number) |
It returns the positive square roots of a
number |
|
MIN |
MIN (number1, number2..) |
It returns the smallest value from the
list |
|
MAX |
MAX (number1, number2…) |
It returns the largest value from the
list |
|
COUNT |
COUNT (value1, value2…) |
It counts for the number in a list |
|
LOWER |
LOWER (text) |
It converts text to lowercase |
|
UPPER |
UPPER (text) |
It converts text to uppercase |
|
TEXT |
TEXT (value, format_text) |
It formats a number and converts to text |
|
VALUE |
VALUE (text) |
It converts text argument to number |
Applying a function
You
can apply a function on a worksheet cell, where you want to display the result
of a calculation. An excel function begins with an equal sign followed by
function name and a comma separated list of arguments kept inside a pair of
parentheses. You can pass either addresses of individual cells or reference to
cell range as arguments to a function. A range of cells can be referenced in a
function as the cell reference of the first cell in the cell range followed by
a colon and the cell reference of the last cell in the cell range. For example
values stored in two worksheet cells, A1 and B1 can be added using the SUM
function as =SUM (A1,B1), where A1 and B1 are the arguments passed to the
function. On the other hand values stored in the cells of a cell range in which
the first cell is A2 and the last cell is B5, can be added using the SUM
function as =SUM (A2:B5). We can also simultaneously pass individual cells as
well as ranges of cells as arguments to function.
Let’s
perform the following steps to apply a function on a worksheet cell in excel:
Assigning Name to a cell range
By
now, we know that a range of cells can be referenced in a function by first
typing the cell reference of the first cell in the cell range followed by a
colon and the cell reference of the last cell in the cell range. Instead of
using this cumbersome way to reference a cell range in a function, we can first
assign a name to the cell range and then use the range name to reference the
cell range in the function. While assigning a name to a cell range, we can also
define the scope in which the range name can be used. By defining the scope for
a range name, we can specify whether the range name can be used in only a
particular worksheet, in some of the worksheets of the workbook, or in the entire
workbook.
No comments:
Post a Comment