Microsoft Excel is a spreadsheet application that you can use to calculate, analyze, and

manage your data. You can also import and modify Microsoft Excel spreadsheets.
Spreadsheets are often used for cash flow analysis, budgeting, decision making, cost
estimating, inventory management and financial reporting.
Excel provides you with functions that you can use to create formulas to perform
complex calculations on your data. You can also use to help you create your formulas.
Ex. Budget Spreadsheet
Cash Budget Forecast

January January
Estimated Actual
Cash in Bank ( Start of Month ) 1,400.00 1,400.00
Cash in Register ( Start of Month ) 100.00 100.00
Total Cash 1,500.00 1,500.00

Expected Cash Sales 1,200.00 1,420.00

Expected Collections 400.00 380.00
Other Money Expected 100.00 52.00
Total Income 1,700.00 1,852.00
Total Cash and Income 3,200.00 3,352.00
__________________________________________________
All Expenses (for Month) 1,200.00 1,192.00
__________________________________________________
Cash Balance at End of Month 2,000.00 2,160.00

Microsoft Excel formula always begin with equal sign ( =).

Microsoft Excel worksheet has 3 default worksheets.
Microsoft Excel has 256 columns.
Columns – are assigned alphabetic labels from A to IV.
Microsoft Excel has 65,536 rows.
Rows – are assigned numeric labels from 1 to 65,536.
Spreadsheet Object bar – contains icons that you can be used for all types of
documents.
Function bar – contains basic commands for applying manually formatting.
Formula Bar – located immediately below the toolbars, displays the contents of the
active cell.
Cell’s content – is the text, numbers, and formulas you enter into the cell.
Active cell – is the cell in which you are currently working.
Cell – is a rectangular area where a column and a row intersect.
Cell Reference – a column and row location
Pointer – is the indicator that moves on your screen as you move your mouse.
Sheet tabs – identifies the name of the worksheet.

Moving around a Worksheet

Before entering or editing the contents of a cell, you need to select the cell to
make it the active cell. You can select a cell using either the keyboard or the mouse.
Using the Mouse – you can quickly select a cell by placing the mouse pointer on the cell
and clicking the mouse button. If you need to move a cell that’s not currently on the
screen, use the vertical and horizontal scroll bars to display the area of the worksheet
containing the cell you are interested in, and then select the cell.
Using the Keyboard – calc provides you with many keyboard options for moving to
different cell locations within your worksheet
Keys to move around the worksheet:
Keystroke Action
Moves up, down, left or right one cell.
PgUp Moves the active cell up one full screen.
PgDn Moves the active cell down one full screen.
Home Moves the active cell to column A of the current row.
Ctrl + Home Moves the active cell to cell A1.
F5 ( function key ) Opens Go To dialog box, in which you enter cell address of cell
you want to make active cell.

Text, Values, Formulas and Functions

Text – entries include any combination any combination of letters, symbols, numbers and
spaces. Text is often used to label columns and rows in a
worksheet.
Values – are numbers that represent a quantity of some type: the number of units in
inventory, stock price, an exam score and so on.
Formulas – is the arithmetic used to calculate values appearing on a worksheet. A
Microsoft Excel formula always begins with an equal sign ( = ) .
Formulas are created by combining numbers, cell references,
arithmetic operators and or functions. An arithmetic operator
indicates the desired arithmetic operations.
Functions – is a predefined or built – in formula that a shortcut for commonly used
calculations. For example, the SUM function to create a formula
=SUM(F11:F15) instead of typing the longer
=F11+F12+F13+F14+F15. The SUM function in this example
adds the range F11 through F15.
Range – is a group of cells, either a single cell or a rectangular block of cells beginning
in the upper – left corner and ending in the lower right corner of
the range.

What – if analysis – revise the contents of one or more cells in a worksheet and observe
the effect this change has on all the other cells.

Creating a Worksheet
Using the AutoSum button – Sum function is used more often than any other function,
Excel includes the AutoSum button on the Standard toolbar.
Entering Formulas – is an equation that performs calculations in a cell. If a formula
contains more than one arithmetic operator, Microsoft Excel performs the calculations in
the standard order of precedence of operators.
The order of precedence – is a set of predefined rules that Microsoft Excel uses to
unambiguously calculate a formula by determining which part of the formula to calculate
first, which part second and so on.
Copying Formula using the Fill Handle – you can copy formulas using menu
commands, toolbar buttons, or the fill handle.
Fill Handle – is a small black square located in the lower right corner of the selected cell.
Relative References – are cell references that change when copied.
Absolute References - is a cell reference in a formula that does not change when copied
to another cell.

EXCEL Functions: AVERAGE, MAX, MIN

Syntax – specifies the set of rules that determine the order and punctuation of formulas
and functions in Microsoft Excel.
Arguments – specify the values the function must use in the calculation, or the cell
references that Microsoft Excel must include in the calculation.
AVERAGE function – is a statistical function that calculates the average, or the
arithmetic mean. The syntax for average function is = average (num1;num2)
MAX function – is a statistical function that finds the largest number.
MIN function – is a statistical function that finds the smallest number.
MEDIAN – returns the median, or the number in the middle of the set of given numbers.
= MEDIAN(num1;num2)
COUNT – counts the number of cells that contain numbers & numbers within the list of
arguments.
COUNTIF – counts the number of cells within a range that meet the given condition.
=COUNTIF(range;criteria)
Range – is the range of cells from which you want to count nonblank cells.
HLOOKUP – looks for a value in the top row of a table or array of values & returns the
value in the same columns from a row you specify.
=HLOOKUP(look_up_value;table_array;row_index_num)
Lookup-value – is the value to be found in the first column of the table & can be a
value, a reference or a text string.
IF – returns one value if a condition you specify evaluates to TRUE & another value if it
evaluates to FALSE. =IF(logical_test;value_if_true;value_if_false)
VLOOKUP – searches for a value in the leftmost column of a table and then returns a
value in the same row from a column you specify. By default, the table must be sorted in
an ascending order. =VLOOKUP(lookup_value;table_array;col_index_num)

Inserting a rows and columns into a worksheet

The process of inserting columns and rows is similar, you select the number of
columns or rows you want to insert and then use the Insert command to insert them.
Autoformat – is a command that lets you change your worksheet’s appearance by
selecting a collection of predefined worksheet formats.
Adding Headers and Footers
Headers – is a text printed in the top margin of every worksheet page.
Footers – is a text printed in the bottom margin of every page.
Adding Cell Comments
Cell Comments – can help users remember assumptions, explain complex formulas or
place reminders related to the contents of a specific cell.
Comment Indicator – a tiny red – triangle appears in the upper – right corner of the cell
indicating the cell contains a comment.

EXCEL FUNCTIONS CATEGORY:

DATABASE DATE & TIME FINANCIAL INFORMATION

DAVERAGE DATE PRICE CELL
DCOUNT DATEVALUE RATE CURRENT
DCOUNTA DAY DURATION FORMULA
DGET DAYS EFFECTIVE TYPE
DMAX HOUR
DMIN MINUTE
DPRODUCT MONTH
NOW
TIME
TODAY
YEAR

LOGICAL MATHEMATICAL ARRAY STATISTICAL

AND COUNTIF FREQUENCY AVERAGE
FALSE EVEN GROWTH COUNT
IF POWER MAX
NOT PRODUCT MEDIAN
OR QUOTIENT MIN
TRUE RADIANS MODE
ROUND

SPREADSHEET TEXT ADD - IN

HLOOKUP ARABIC DELTA
LOOKUP CHAR COMPLEX
VLOOKUP CODE
DECIMAL
DOLLAR
FIND
TRIM
VALUE
UNDERSTANDING CHARTS
Charts - are created to visually describe our data. It is important to create charts in
order to illustrate your ideas, especially, when you make a presentation, or show progress
or change in a report. You can also create a chart to show the changes in your data over
time, or how the parts of your data fit together as a whole.

CHART COMPONENTS
AUTOFORMAT CHART ® The button where you can interactively modify many
properties of the chart.
AXES ® The x axis is usually the horizontal axis and it contains the categories. The y
axis is usually the vertical axis and it contains data. The z axis rotation is not available
for all three dimensional charts.
CHART AREA ® The background behind all elements of the chart.
CHART FLOOR ® The lower area in 3D charts. This function is only available for 3D
charts.
CHART WALL ® The "vertical" background behind the data area of the chartDATA
LABEL ® Allows you to display information regarding the data.
DATA POINT ® A single piece of data, such as a sales for one quarter.
DATA SERIES ® A collection data points, such as sales for quarters 1 to 4. In a line
chart, all points in a data series are connected by the same line.
GRID LINES ® Lines you add to a chart that makes it easier to view and evaluate data.
LEGEND ® A guide that explains the symbols, patterns, or colors used to differentiate
the data series. Change the position of legends in the chart, and to specify whether the
legend is displayed.
MAIN TITLE ® The descriptive texts placed on top of the chart.
STATUS BAR ® The bar along the bottom of the window that will display the chart
category, chart type, chart variant of the selected chart, and other chart related activity.
SUBTITLE ® The descriptive texts located below the chart’s main title.
TIP ® A box that identifies the object that the mouse pointer is pointing to.