Advanced Excel Presentation

PRESENTATION
ON MS EXCEL
Milan
Singh
Mechanical
191105028
INTRODUCTION
TO EXCEL
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. Every cell is identified by its cell address, cell address contains
its column number and row number (If a cell is on the 11th row and on column A, then its
address will be A11).
WORKSHEET
A worksheet is a collection of cells where you keep and manipulate the
data.
WORKBOOK
A workbook is a collection of multiple worksheets. When you start excel
click blank workbook to create an excel workbook from scratch.
FILL HANDLE QUICK ACCESS TOOLBAR
It's a small dot present in the lower right corner A toolbar to quickly access the options which
of the active cell. It helps you to fill numeric you frequently use. You can add your favorite
values, text series, insert ranges, insert numbers options by adding new options to the quick
etc. access toolbar.
WORKSHEET TAB
This tab shows all the worksheets which are
ADDRESS BAR
present in the workbook. By default you will see,
It shows the address of the active
three worksheets in your new workbook with the
cell.
names Sheet1, Sheet2, and Sheet3 respectively.
FORMULA BAR RIBBON

The formula bar is an input bar below the Ribbon tab contains multiple commands logically
ribbon. It shows the content of the active sub-divided into groups or tabs. There are 9 tabs
cell and you can also use it to enter a in excel ribbon.
formula in a cell.
OTHER FINANCIAL FUNCTIONS
FV FUNCTION
FV function returns the future value of an investment using constant payments
and
a constant interest rate.
SYNTAX
FV(rate,nper,pmt,[pv],[type])
Arguments :
rate: A constant interest rate that you want to use in the calculation.
nper: Number of payments.
pmt: A constant payment amount to pay periodically throughout
the investment time.
[pv]: The present value of future payments. It must be entered
as a negative value. 0 if omitted.
Similarly other financial functions with the
CONCATENATE AND FILL HANDLE
The CONCAT( ) or CONCATENATE( ) helps to
combine the contents of two or more cells into a
singular content.
Since primary goal is to combine, concatenate

function needs atleast one text as it's argument.
SYNTAX
concat(text1, text2, .....)
you can also put space in-between the concatenation

as :
=CONCATENATE( cell_no_1 ,” “, cell_no_2)
FILL HANDLE
Now suppose we have the "First Name" and "Last
Name"
data of 1000 students.
We don't need to manually apply concat to every
column, we simply drag down the fill handle in the
DATA FORMATTING IN EXCEL
Excel provides a pool of tools called formatting tools which customize the data in such a way
that
it only affects the appearance of the data and not the content.
Following are the steps to apply the formatting:
1.Select the range of cells on which formatting needs to be done.

2.Select the suitable formatting tool from the toolbox(ribbon).
3.Formatting can be observed on the sheet.
Some of the formatting tools shown down below are : Font, Font Size, Increase/Decrease font
size, Bold and italic, Underline, Border, Fill Colour, Font Colour etc.
BASIC STATISTICAL INTERMEDIATE STATISTICAL FUNCTIONS
FUNCTIONS
AVERAGE
COUNT The COUNT function is used to count the number of cells
containing a number. Always remember one thing that it will
The COUNT function is used to count the number of
only count the number.
cells containing a number. it will only count the
Formula for COUNT function = COUNT(value1, [value2], …)
number.
Formula for COUNT function = COUNT(value1)
AVERAGEIF
The function will return the arithmetic mean or an average of
the cell in a given range that meets the given criteria.
COUNTA
Formula for AVERAGEIF function = AVERAGEIF(range,
This function will count everything, it will count the number of criteria, [average_range])
the cell containing any kind of information, including numbers,
error values, empty text. MEDIAN
Formula for COUNTA function = COUNTA(value1)
The MEDIAN function will return the central value of the data.
Its syntax is similar to the AVERAGE function.
COUNTIFS
Formula for MEDIAN function = MEDIAN(number1, [number2],
COUNTIFS function is the most used function in Excel. The …)
function will work on one or more than one condition in a
STANDARD DEVIATION
given range and counts the cell that meets the condition.
This function helps us to determine how much observed
Formula for COUNTIFS function = COUNTIFS (range1,
value deviated or varied from the average. This function is
criteria1)
one of the useful functions in Excel.
Formula for STANDARD DEVIATION function =
CELL REFERENCE
• A cell reference is a cell or a cell range, which identifies its location on a

worksheet.
• Cell references can be used as arguments in a formula/function.
• As such, if the values of our referenced cells change, our result values (calculated
by formulas) will auto-update without having to editing the formulas.
RELATIVE MIXED AND ABSOLUTE

CELL REFERENCES
To switch between the
relative, mixed and
absolut
absolute cell Both column and rows are fixed when
references
e $A$1
highlight copied into other cells
the cell reference and

Both column and rows are not fixed A1
press F4 on your keyboard.
relativ
Either the column or the row is fixed $A1
e but not both, when copied into other or
LOGICAL
OPERATORS
A logical operator, or Boolean operator, is used to compare between 2 values evaluating the result as either TRUE or FALSE.
The syntax is identical to a formula involving a mathematical operator (+,-,*, /,^), except we use a logical operator instead. As usual,
the arguments can be constants, functions, or cell references.
DATA ANALYSIS
1Sort: You can sort your Excel data on one column or multiple columns. You can sort in
ascending or descending order.
2Filter: Filter your Excel data if you only want to display records that meet certain
criteria. 3 Conditional Formatting: Conditional formatting in Excel enables you to highlight
cells with a certain color, depending on the cell's value.
4 Charts: A simple Excel chart can say more than a sheet full of numbers. As you'll see,
creating charts is very easy.
LOOKUP
VLookUP
FUNCTIONS
VLOOKUP stands for vertical lookup. It is a function that makes search for a certain value in a column, in order to return a
value from a different column in the same row.
A VLOOKUP consists of 4 components :
1.The value you want to lookup.
2.The range in which you want to find the value and the return value.
3.The number of the column within your defined range, that contains the return value.
4. 0 or FALSE for an exact match with the value you are looking for, 1 or TRUE for an aproximate
match. Syntax : VLOOKUP([value], [range], [column number], [false or true])
CONDITIONAL
FORMATTING
Conditional formatting allows you to automatically apply formatting such
as colors, Icons, and data bars to one or more cells based on the cell
value.
To create a conditional formatting rule :
1. Select the desired cells for the conditional formatting rule.
1.From the Home Tab, click the conditional formatting command. A

dropdown menu will apear.
2.Hover the mouse over the desired conditional formatting type, then select
the desired rule from the menu that appears.
3.A dialogue box will appear. Enter the desired value into the blank field
4.Select a formatting style from the drop-down menu.
5.The conditional formatting will be applied to the selected cells.
DATA
BARS

Advanced Excel Presentation

Uploaded by

Copyright:

Available Formats

Advanced Excel Presentation

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Advanced Excel Presentation

Uploaded by

Copyright:

Available Formats

PRESENTATION

FORMULA BAR RIBBON

Since primary goal is to combine, concatenate

you can also put space in-between the concatenation

Following are the steps to apply the formatting:

1.Select the range of cells on which formatting needs to be done.

• A cell reference is a cell or a cell range, which identifies its location on a

RELATIVE MIXED AND ABSOLUTE

the cell reference and

To create a conditional formatting rule :

1. Select the desired cells for the conditional formatting rule.

1.From the Home Tab, click the conditional formatting command. A

You might also like