Introduction to Microsoft Excel
By-- SATISH KUMAR BARANWAL
What
is Microsoft Excel?
Microsoft Excel is a spreadsheet
program that is used to record and analyse numerical data. Think of a
spreadsheet as a collection of columns and rows that form a table. Alphabetical
letters are usually assigned to columns and numbers are usually assigned to
rows. The point where a column and a row meet is called a cell. The address of
a cell is given by the letter representing the column and the number
representing a row.
How
to Open Microsoft Excel?
Running Excel is not different from
running any other Windows program. If you are running Windows with a GUI like
(Windows XP, Vista, and 7) follow the following steps.
- Click on start
menu
- Point to all
programs
- Point to
Microsoft Excel
- Click on Microsoft
Excel
Alternatively, you can also open it
from the start menu if it has been added there. You can also open it from the
desktop shortcut if you have created one.
For this tutorial, we will be working
with Windows 8.1 and Microsoft Excel 2013. Follow the following steps to run
Excel on Windows 8.1
- Click on start
menu
- Search for Excel
even before you even typing, all programs starting with what you have
typed will be listed.
- Click on
Microsoft Excel\
Understanding
the worksheet (Rows and Columns, Sheets, Workbooks)
Three
most important components of Excel is which you need to understand first:
#1.
Workbook
A
workbook is a separate file just like every other application has. Each
workbook contains one or more worksheets. You can also say that a workbook is a
collection of multiple worksheets or can be a single worksheet. You can add or
delete worksheets, hide them within the workbook without deleting them, and
change the order of your worksheets within the workbook.
#2. Worksheet
A
worksheet is made up of individual cells which can contain a value, a formula,
or text. It also has an invisible draw layer, which holds charts, images, and
diagrams. Each worksheet in a workbook is accessible by clicking the tab at the
bottom of the workbook window. In addition, a workbook can store chart sheets;
a chart sheet displays a single chart and is accessible by clicking a tab.
#3. Cell
A
cell is a smallest but most powerful part of a spreadsheet. You can enter your
data into a cell either by typing or by copy-paste. Data can be a text, a
number, or a date. You can also customize it by changing its size, font color,
background color, borders, etc. Every cell is identified by its cell address,
cell address contains its column number and row number (If a cell is on 11th row
and on column AB, then its address will be AB11).
Window
Components
Before you start using it, it’s really
important to understand that what’s where in its window.
#1. Active Cell
A cell which is currently selected. It will be
highlighted by a rectangular box and its address will be shown in the address
bar.
You can activate a cell by clicking on it or by using
your arrow buttons. To edit a cell, you double-click on it or use F2 to as
well.
#2. Column
A column is a vertical set of cells. A single worksheet
contains 16384 total columns.
Every column has its own alphabet for identity, from A to
XFD. You can select a column clicking on its header.
#3. Row
A row is a horizontal set of cells. A single worksheet
contains 1048576 total rows. Every row has its own number for identity,
starting from 1 to 1048576.
You can select a row clicking on the row number marked on
the left side of the window.
#4.
Fill Handle
It’s a small dot present on the lower right corner of the
active cell. It helps you to fill numeric values, text series, insert ranges,
insert serial numbers, etc.
#5.
Address Bar
The address bar is the small input bar at the left side
of the window.
It shows the address of the active cell. If you have
selected more than one cell, then it will show the address of the first cell in
the range.
#6. Formula Bar
Formula bar is an input bar, below the ribbon. It shows
the content of the active cell and you can also use it enter a formula in a
cell.
#7.
Title Bar
The title bar will show the name of your workbook, followed
by the application name (“Microsoft Excel”).
#8. File
Menu
The file menu is a simple menu as like all other
applications. It contains options like (Save, Save As, Open, New, Print, Excel
Options, Share, etc).
#9.
Quick Access Toolbar
A toolbar to quickly access the options which you
frequently use. You can add your favorite options by adding new options to
quick access toolbar.
#10.
Ribbon Tab
Starting from the Microsoft Excel 2007, all the options
menus are replaced with the ribbons. Ribbon tabs are the bunch of specific
option group which further contains option.
#11. Worksheet Tab
This tab shows the all the worksheets which are present
in the workbook. By default you will see, three worksheets in your new workbook
with a name of Sheet1, Sheet2, Sheet3 respectively.
#12. Status Bar
It is a thin bar at the bottom of the Excel window. It
will give you an instant help once you start your working in Excel.
The
basics of Excel formulas
Before providing the basic Excel
formulas list, let's define the key terms just to make sure we are on the same
page. So, what do we call an Excel formula and Excel function?
Formula is an expression that calculates
the value of a cell.
For example, =A2+A2+A3+A4 is
a formula that adds up the values in cells A2 to A4.
Function is a predefined formula already
available in Excel. Functions perform specific calculations in a particular
order based on the specified values, called arguments, or parameters.
For example, instead of specifying
each value to be summed like in the above formula, you can use the SUM function
to add up a range of cells: =SUM(A2:A4)
Excel DATE Function
The Excel
DATE function creates a valid date from individual year, month, and day
components. The DATE function is useful for assembling dates that need to
change dynamically based on other values in a worksheet.
Purpose
Create a
date with year, month, and day
Return value
A valid
Excel date
Syntax
=DATE (year,
month, day)
Arguments
·
year - Number for year.
·
month - Number for month.
·
day - Number for day.
Excel YEAR Function
The Excel
YEAR function returns the year component of a date as a 4-digit number. You can
use the YEAR function to extract a year number from a date into a cell or to
extract and feed a year value into another formula, like the DATE function.
Purpose
Get the year
from a date
Return value
Year as
4-digit number
Syntax
=YEAR (date)
Arguments
·
date - A valid Excel date.
Excel MONTH Function
The Excel
MONTH function extracts the month from a given date as number between 1 to 12.
You can use the MONTH function to extract a month number from a date into a
cell, or to feed a month number into another function like the DATE function.
Purpose
Get month as
a number (1-12) from a date
Return value
A number
between 1 and 12.
Syntax
=MONTH
(date)
Arguments
·
date - A valid Excel date.
Excel DAY Function
The Excel
DAY function returns the day of the month as a number between 1 to 31 from a
given date. You can use the DAY function to extract a day number from a date
into a cell. You can also use the DAY function to extract and feed a day value
into another function, like the DATE function.
Purpose
Get the day
as a number (1-31) from a date
Return value
A number
(1-31) representing the day component in a date.
Syntax
=DAY (date)
Arguments
·
date - A valid Excel date.
Excel TODAY Function
The Excel
TODAY function returns the current date, updated continuously when a worksheet
is changed or opened. The TODAY function takes no arguments. You can format the
value returned by TODAY using any standard date format. If you need current
date and time, use the NOW function.
Purpose
Get the
current date
Return value
Valid Excel
date
Syntax
=TODAY ()
Excel NOW Function
The Excel
NOW function returns the current date and time, updated continuously when a
worksheet is changed or opened. The NOW function takes no arguments. You can
format the value returned by NOW as a date, or as a date with time by applying
a number format.
Purpose
Get the
current date and time
Return value
A serial
number representing a particular date and time in Excel.
Syntax
=NOW ()
Excel TIME Function
The Excel
TIME function is a built-in function that allows you to create a time with
individual hour, minute, and second components. The TIME function is useful
when you want to assemble a proper time inside another formula.
Purpose
Create a
time with hours, minutes, and seconds
Return value
A decimal
number representing a particular time in Excel.
Syntax
=TIME (hour,
minute, second)
Arguments
·
hour - The hour for the time you wish to create.
·
minute - The minute for the time you wish to create.
·
second - The second for the time you wish to create.
Excel COUNTIF Function
COUNTIF is a function to count cells that meet a
single criteria. COUNTIF can be used to count cells with dates, numbers, and
text that match specific criteria. The COUNTIF function supports logical
operators (>,<,<>,=) and wildcards (*,?) for partial matching.
Purpose
Count cells that match criteria
Return value
A number representing cells counted.
Syntax
=COUNTIF (range, criteria)
Arguments
·
range - The range of cells to count.
·
criteria - The criteria that controls which
cells should be counted.
Excel COUNTBLANK Function
The Excel
COUNTBLANK function returns a count of empty cells in a range. Cells that
contain text, numbers, errors, etc. are not counted. Formulas that return empty
text are counted.
Purpose
Count cells
that are blank
Return value
A number
representing blank cells
Syntax
=COUNTBLANK
(range)
Arguments
·
range - The range in which to count blank cells.
Excel COUNTA Function
The Excel
COUNTA function returns the count of cells that contain numbers, text, logical
values, error values, and empty text (""). COUNTA does not count
empty cells.
Purpose
Count the
number of non-blank cells
Return value
A number
representing non-blank cells.
Syntax
=COUNTA
(value1, [value2], ...)
Arguments
·
value1 - An item, cell reference, or range.
·
value2 - [optional] An item, cell reference, or range.
Excel COUNT Function
The Excel
COUNT function returns the count of values that are numbers, generally cells
that contain numbers. Values can be supplied as constants, cell references, or
ranges.
Purpose
Count
numbers
Return value
A number
representing a count of numbers.
Syntax
=COUNT
(value1, [value2], ...)
Arguments
·
value1 - An item, cell reference, or range.
·
value2 - [optional] An item, cell reference, or range.
Excel MAX Function
The Excel
MAX function returns the largest numeric value in a range of values. The MAX
function ignores empty cells, the logical values TRUE and FALSE, and text
values.
Purpose
Get the
largest value
Return value
The largest
value in the array.
Syntax
=MAX
(number1, [number2], ...)
Arguments
·
number1 - Number, reference to numeric value, or range
that contains numeric values.
·
number2 - [optional] Number, reference to numeric value,
or range that contains numeric values.
Excel MIN Function
The Excel
MIN function returns the smallest numeric value in a range of values. The MIN
function ignores empty cells, the logical values TRUE and FALSE, and text
values.
Purpose
Get the
smallest value.
Return value
The smallest
value in the array.
Syntax
=MIN
(number1, [number2], ...)
Arguments
·
number1 - Number, reference to numeric value, or range
that contains numeric values.
·
number2 - [optional] Number, reference to numeric value,
or range that contains numeric values.
Excel EXACT Function
The Excel EXACT function compares two text strings,
taking into account upper and lower case characters, and returns TRUE if they
are the same, and FALSE if not. EXACT is case-sensitive.
Purpose
Compare two text strings
Return value
A boolean value (TRUE or FALSE)
Syntax
=EXACT (text1, text2)
Arguments
·
text1 - The first text string to compare.
·
text2 - The second text string to compare.
Excel LEN Function
The Excel
LEN function returns the length of a given text string as the number of
characters. LEN will also count characters in numbers, but number formatting is
not included.
Purpose
Get the
length of text.
Return value
Number of
characters
Syntax
=LEN (text)
Arguments
·
text - The text for which to calculate length.
Excel FIND Function
The Excel
FIND function returns the position (as a number) of one text string inside
another. When the text is not found, FIND returns a #VALUE error.
Purpose
Get the
location of text in a string
Return value
A number
representing the location of find_text.
Syntax
=FIND
(find_text, within_text, [start_num])
Arguments
·
find_text - The text to find.
·
within_text - The text to search within.
·
start_num - [optional] The starting position in the text
to search. Optional, defaults to 1.
Excel LEFT Function
The Excel
LEFT function extracts a given number of characters from the left side of a
supplied text string. For example, LEFT("apple",3) returns
"app".
Purpose
Extract text
from the left of a string
Return value
One or more
characters.
Syntax
=LEFT (text,
[num_chars])
Arguments
·
text - The text from which to extract characters.
·
num_chars - [optional] The number of characters to
extract, starting on the left side of text. Default = 1.
Excel RIGHT Function
The Excel
RIGHT function extracts a given number of characters from the right side of a
supplied text string. For example, RIGHT("apple",3) returns
"ple".
Purpose
Extract text
from the right of a string
Return value
One or more
characters.
Syntax
=RIGHT
(text, [num_chars])
Arguments
·
text - The text from which to extract characters on
the right.
·
num_chars - [optional] The number of characters to
extract, starting on the right. Optional, default = 1.
Excel TRIM Function
The Excel
TRIM function strips extra spaces from text, leaving only a single space
between words and no space characters at the start or end of the text.
Purpose
Remove extra
spaces from text
Return value
Text with
extra spaces removed.
Syntax
=TRIM (text)
Arguments
·
text - The text from which to remove extra space.
Excel MID Function
The Excel
MID function extracts a given number of characters from the middle of a
supplied text string. For example, =MID("apple",2,3) returns
"ppl".
Purpose
Extract text
from inside a string
Return value
The
characters extracted.
Syntax
=MID (text,
start_num, num_chars)
Arguments
·
text - The text to extract from.
·
start_num - The location of the first character to
extract.
·
num_chars - The number of characters to extract.
Excel CONCAT Function
The Excel
CONCAT function concatenates (joins) values supplied as references or
constants. Unlike the CONCATENATE function (which CONCAT replaces), CONCAT
allows you to supply a range of cells to join, in addition to individual cell
references.
Purpose
Join text
values without delimiter
Return value
Result of
concatenated text
Syntax
=CONCAT
(text1, [text2], ...)
Arguments
·
text1 - First text value, cell reference, or range.
·
text2 - [optional] Second text value, cell reference,
or range.
Excel TEXT Function
The Excel
TEXT function returns a number in a specified number format, as text. You can
use the TEXT function to embed formatted numbers inside text.
Purpose
Convert a
number to text in a number format
Return value
A number as
text in the given format.
Syntax
=TEXT
(value, format_text)
Arguments
·
value - The number to convert.
·
format_text - The number format to use.
Excel CODE Function
The Excel
CODE function returns a numeric code for a given character. For
example, CODE("a") returns the code 97. The CODE function can be
used to figure out numeric codes supplied to the CHAR function.
Purpose
Get the code
for a character
Return value
A numeric
code representing a character.
Syntax
=CODE (text)
Arguments
·
text - The text for which you want a numeric code.
Excel CHAR Function
The Excel
CHAR function returns a character when given a valid character code. CHAR can
be used to specify characters that are hard to enter in a formula. For example,
CHAR(10) returns a line break on Windows, and CHAR(13) returns a line break on
the Mac.
Purpose
Get a
character from a number
Return value
A single
character specified by a number.
Syntax
=CHAR
(number)
Arguments
·
number - A number between 1 and 255.
Excel REPT Function
The Excel
REPT function repeats characters a given number of times. For example,
=REPT("x",5) returns "xxxxx".
Purpose
Repeat text
as specified
Return value
The repeated
text.
Syntax
=REPT (text,
number_times)
Arguments
·
text - The text to repeat.
·
number_times - The number of times to repeat text.
Excel REPLACE Function
The Excel
REPLACE function replaces characters specified by location in a given text
string with another text string. For example
=REPLACE("XYZ123",4,3,"456") returns "XYZ456".
Purpose
Replace text
based on location
Return value
The altered
text.
Syntax
=REPLACE
(old_text, start_num, num_chars, new_text)
Arguments
·
old_text - The text to replace.
·
start_num - The starting location in the text to search.
·
num_chars - The number of characters to replace.
·
new_text - The text to replace old_text with.
Excel LOWER Function
The Excel
LOWER function returns a lower-case version of a given text string. Numbers and
punctuation are not affected.
Purpose
Convert text
to lower case
Return value
Text in
lower case.
Syntax
=LOWER
(text)
Arguments
·
text - The text that should be converted to lower
case.
Excel RANDBETWEEN Function
The Excel
RANDBETWEEN function returns a random integer between given numbers.
RANDBETWEEN recalculates when a worksheet is opened or changed.
Purpose
Get a random
integer between two values
Return value
An integer
Syntax
=RANDBETWEEN
(bottom, top)
Arguments
·
bottom - An integer representing the lower value of the
range.
·
top - An integer representing the lower value of the
range.
Excel TRANSPOSE Function
The Excel
TRANSPOSE function "flips" the orientation of a given range or array.
TRANSPOSE converts a vertical range to a horizontal range, or a horizontal range
to a vertical range. You must enter the TRANSPOSE function as an array formula.
Purpose
Flip the
orientation of a range of cells
Return value
An array in
a new orientation.
Syntax
=TRANSPOSE
(array)
Arguments
·
array - The array or range of cells to transpose.
Excel TRUNC Function
The Excel
TRUNC function returns a truncated number based on an (optional) number of
digits. For example, TRUNC(4.9) will return 4, and TRUNC(-3.5) will return -3.
The TRUNC function does no rounding, it simply truncates as specified.
Purpose
Truncate a
number to a given precision
Return value
A truncated
number
Syntax
=TRUNC
(number, [num_digits])
Arguments
·
number - The number to truncate.
·
num_digits - [optional] The precision of the truncation
(default is 0).
Excel ABS Function
The Excel
ABS function returns the absolute value of a number. Negative numbers are
converted to positive numbers, and positive numbers are unaffected.
Purpose
Find the
absolute value of a number
Return value
A positive
number.
Syntax
=ABS (number)
Arguments
·
number - The number to get the absolute value of.
Excel GCD Function
The Excel
GCD function returns the greatest common divisor of two or more integers. The
greatest common divisor is the largest integer that goes into all supplied
numbers without a remainder. For example, =GCD(60,36) returns 12.
Purpose
Get the
greatest common divisor of two or more numbers
Return value
A number
representing the largest positive integer that divides the numbers without a
remainder
Syntax
=GCD
(number1, [number2], ...)
Arguments
·
number1 - The first number.
·
number2 - [optional] The second number.
Excel SQRT Function
The Excel
SQRT function returns the square root of a positive number. SQRT returns an
error if number is negative.
Purpose
Find the
positive square root of a number
Return value
Positive
square root
Syntax
=SQRT
(number)
Arguments
·
number - The number to get the square root of.
Excel POWER Function
The Excel
POWER function returns a number to a given power. The POWER function works like
an exponent in a standard math equation.
Purpose
Raise a
number to a power
Return value
Number
raised to power
Syntax
=POWER
(number, power)
Arguments
·
number - Number to raise to a power.
·
power - Exponent to raise power to.
Excel INT Function
The Excel
INT function returns the integer part of a decimal number by rounding down to
the integer. Note the INT function rounds down, so negative numbers become more
negative. For example, while INT(10.8) returns 10, INT(-10.8) returns -11.
Purpose
Get the
integer part of a decimal by rounding down
Return value
The integer
part of the number after rounding down
Syntax
=INT
(number)
Arguments
·
number - The number from which you want an integer.
Excel ROUND Function
The Excel
ROUND function returns a number rounded to a given number of digits. The ROUND
function can round to the right or left of the decimal point.
Purpose
Round a
number to a given number of digits
Return value
A rounded
number.
Syntax
=ROUND
(number, num_digits)
Arguments
·
number - The number to round.
·
num_digits - The number of digits to which number should be
rounded.
Excel LOG10 Function
The Excel
LOG10 function returns the base 10 logarithm of a number. For example,
LOG10(100) returns 2, and LOG10(1000) returns 3.
Purpose
Get the
base-10 logarithm of a number
Return value
The
logarithm
Syntax
=LOG10
(number)
Arguments
·
number - Number for which you want the logarithm.
Excel MOD Function
The Excel MOD
function returns the remainder of two numbers after division. For
example, MOD(10,3) = 1. The result of MOD carries the same sign as the divisor.
Purpose
Get the
remainder from division
Return value
The
remainder
Syntax
=MOD
(number, divisor)
Arguments
·
number - The number to be divided.
·
divisor - The number to divide with.
Excel SUM Function
The Excel
SUM function returns the sum of values supplied. These values can be numbers,
cell references, ranges, arrays, and constants, in any combination. SUM can
handle up to 255 individual arguments.
Purpose
Add numbers
together
Return value
The sum of
values supplied.
Syntax
=SUM
(number1, [number2], [number3], ...)
Arguments
·
number1 - The first value to sum.
·
number2 - [optional] The second value to sum.
·
number3 - [optional] The third value to sum.
Excel SUMIF Function
The Excel
SUMIF function returns the sum of cells that supplied criteria.
Criteria can be applied to dates, numbers, and text using logical
operators (>,<,<>,=) and wildcards (*,?) for partial matching.
Purpose
Sum numbers
in a range that meet supplied criteria
Return value
The sum of
values supplied.
Syntax
=SUMIF
(range, criteria, [sum_range])
Arguments
·
range - The range of cells that you want to apply the
criteria against.
·
criteria - The criteria used to determine which cells to
add.
·
sum_range - [optional] The cells to add together. If
sum_range is omitted, the cells in range are added together instead.
Excel AVERAGE Function
The Excel
AVERAGE function returns the average of values supplied as multiple arguments.
AVERAGE can handle up to 255 individual arguments, which can include numbers,
cell references, ranges, arrays, and constants.
Purpose
Get the
average of a group of numbers
Return value
A number
representing the average.
Syntax
=AVERAGE
(number1, [number2], ...)
Arguments
·
number1 - A number or cell reference that refers to
numeric values.
·
number2 - [optional] A number or cell reference that
refers to numeric values.
Excel IF Function
The IF
function can perform a logical test and return one value for a TRUE result, and
another for a FALSE result. For example, to "pass" scores above 70:
=IF(A1>70,"Pass","Fail"). More than one condition can be
tested by nesting IF functions. The IF function can be combined with logical
functions like AND and OR.
Purpose
Test for a
specific condition
Return value
The values
you supply for TRUE or FALSE
Syntax
=IF
(logical_test, [value_if_true], [value_if_false])
Arguments
·
logical_test - A value or logical expression that can be evaluated
as TRUE or FALSE.
·
value_if_true - [optional] The value to return when
logical_test evaluates to TRUE.
·
value_if_false - [optional] The value to return when
logical_test evaluates to FALSE.
Excel AND Function
The Excel
AND function is a logical function used to require more than one condition at
the same time. AND returns either TRUE or FALSE. To test if a number in A1 is
greater than zero and less than 10, use =AND(A1>0,A1<10). The AND
function can be used as the logical test inside the IF function to avoid extra
nested IFs, and can be combined with the OR function.
Purpose
Test
multiple conditions with AND
Return value
TRUE if all
arguments evaluate TRUE; FALSE if not
Syntax
=AND
(logical1, [logical2], ...)
Arguments
·
logical1 - The first condition or logical value to
evaluate.
·
logical2 - [optional] The second condition or logical
value to evaluate.
Excel OR Function
The OR
function is a logical function to test multiple conditions at the same time. OR
returns either TRUE or FALSE. For example, to test A1 for either "x"
or "y", use =OR(A1="x",A1="y"). The OR function
can be used as the logical test inside the IF function to avoid extra nested
IFs, and can be combined with the AND function.
Purpose
Test
multiple conditions with OR
Return value
TRUE if any
arguments evaluate TRUE; FALSE if not.
Syntax
=OR
(logical1, [logical2], ...)
Arguments
·
logical1 - The first condition or logical value to
evaluate.
·
logical2 - [optional] The second condition or logical
value to evaluate.
Excel NOT Function
The Excel
NOT function returns the opposite of a given logical or boolean value. When
given TRUE, NOT returns FALSE.When given FALSE, NOT returns TRUE. Use the NOT
function to reverse a logical value.
Purpose
Reverse
arguments or results
Return value
A reversed
logical value
Syntax
=NOT
(logical)
Arguments
·
logical - A value or logical expression that can be
evaluated as TRUE or FALSE.
