Table of Contents
MICRO SOFT EXCEL
Micro Soft Excel – Extn: -. XLSX
INTRODUCTION
Micro Soft Excel is window based application package of Micro Soft office, which is used to make calculation and drive analysis in the base of produced data in a tabular from.
Ques What is Work Book?
In Micro Soft Excel a work book is the file in which you work and store your data.
WORK SHEET: – Work sheet also known as spread sheet is the primary document that we use in Micro Soft Excel to store and work with data, a work sheet consists of cell, that one organized into columns and rows, there are total 16384 columns and 1048576 Rows in Micro Soft Excel.
Auto fill: – It is an option through which we drag the one selected field in row or column to get the desired Result. For egg: – days of week, month of year, sum, average, product, therefore auto fill can be used to create a series based on text or numeric.
How to apply formula?
Sum (): – This is a mathematical function which drives total value of the given Range.
Formula: =Sum (Range)
Average: – This mathematical function return arithmetic mean of the given number or
range.
Formula: =Average (total / Range)
Product: – This is an arithmetic function which is used to multiply the value of selected data
Formula: = Product (Range)
Subtract: – This is a mathematical function which is used to multiply the value of selected
data.
Formula: – = No1-No2
Ques How to give address to a cell?
Steps
1. Select the cell in which you want to give name
2. Click on formulas menu
3. Click on define name
4. Give name which you want
5. Click on ok.
Home Menu (Alt+H)
Micro Soft Excel provides with various ways of formatting Like general, number, currency, table etc.
1 Cell: – (ctrl + 1): – By this option we can give formatting in our selected cells
To Format cells
1. Select the cell or Range of cell you want to format
2. Click on home menu
3. Choose the option font cell, format cell window will get opened
4. Choose the format
a) Alignment: – This option is used to give text alignment to the selected cell and we can also change orientation of selected data range by choosing the direction of degree.
b) Font: – This option is used to format the selected file data range by change font, font style, size, font, font color etc. for making the work sheet effective
c) Border: – To set different types of border around cells.
To give Border
1. Select the cell or Range
2. Then click border drop down button on Home menu
3. Border drop down button provide with several type of border
Border Function in Micro Soft Excel
1 Left border on cell
2 Right border on cell
3 upper and lower border on the cell
4 Border around cell
5 No Border on cell.
d) Pattern: – This option gives the color background to the selected data Range.
FORMAT
By this option we can change height and width of selected Row or columns.
1 Row height: – By this option we can change height of our selected Row.
Steps
1. Select the cell
2. Click on Home menu
3. Click on format option
4. Click on Row height
5. Give the value of Row height
6. Click on ok
2 Auto fit Row height: – By this option we can set the Row height according to Range.
3 Column Width: – By this option we can change the width of our selected column.
4 Auto fit column width: – By this option we can set the width of column according to Range.
5 Default Width: – By this option we can change all the column width equally.
VISIBILITY
Hide & Unhide: – By this option we can hide or unhide the selected row, column or sheet.
ORGANIZE SHEETS in Micro Soft Excel
1 Rename sheet: – By this option we can change the name of our sheet.
2 Move or Copy sheet: – By this option we can create copy or move sheet
Steps
1. Select the work Book where you want to create move or copy sheet
2. Click on Home menu
3. Click on format
4. Choose the option move or copy sheet
5. Select the sheet name which you want copy or move
6. If you create copy, then on the option create a copy
7. Select the work book name
8. Click on ok.
3 Tab color: – By this option we can change tab color of sheet.
4 Protect sheet: – By this option we can protect our sheet by giving password.
1. Click on Home menu
2. Click on format option
3. Click on protect sheet option
4. Window will get open, type the password
5. Click on ok, again Repeat this password
6. Click on ok
Home Menu
1) Conditional formatting: – If a cell contains formula Result or other cell value that you want to monitor, you can identify the cell by applying conditional format like we can give different color shading, pattern, and font color according to condition.
Steps.
1. Select the Range
2. Click on Home menu
3. Click on conditional formatting option
4. Select the formatting according to our condition.
2) Format as Table: – Micro Soft Excel Provides some predefined format of the table you can apply to selected cells or database.
Steps
1. Select the range to provide format as table
2. Click on Home menu
3. Click On Format as table
4. Select the format you want to apply
5. Click on it.
3) Cell Styles: – By this option we can create our own styles to apply in cell.
Steps
1. Click on Home menu
2. Click on cell style option
3. Click on new cell style
4. Give the new cell style name
5. Click on Format
6. Select the format you want to apply
7. Click on ok
Insert Cell (ctrl + +): – By this option we can insert new cell in our sheet
Steps
1. Select the cell Where you want to insert new cell
2. Click on Home menu
3. Click on insert
4. Select the option insert cell.
a) Shift Cell Right: – Insert new cell and skips the current cell to right side.
b) Shift Cell Down: – Insert new cell and skip current cell down.
c) Entire Row: – By this we can insert entire row.
d) Entire Column: – By this we can insert entire column.
2 Insert Sheet Rows: – This command inserts an entire row to the selected cell.
3 Insert Sheet Column: – This command inserts a column left of selected cell.
4 insert sheet column (shift +f 11): -this option is used to insert new work sheet in your work book.
Insert Menu
Chart (Alt+F1): –chart is a graphical presentation of numerical data. By this we can show our data in chart using chart command we can create different style of chart Like (column bar, pie area etc.)
Steps
1) Select the data range
2) Click on insert menu
3) Select the chart type
4) Choose the chart sub type & click on it
5) Your chart will get open
6) If you want to create in creating chart
7) Click on layout menu
8) Choose the option chart title & give chart title
9) Choose exit title & choose layout etc.
Picture: –by this command we can insert picture from “my picture”
Hyper link (Ctrl+K): – by this we can create link b/w two or more files.
Steps
a) Select the cell
b) Click on insert menu
c) Click on hyperlink
d) Select the filename with which you want to create hyperlink
e) Click on ok
Pivot table: –by this command we can create our database in pivot table, pivot by table make to easy arrange & summarize complicate to easy data & drill down on detail
Steps
1) Select the data range
2) Click on insert menu
3) Click on pivot table option
4) Chose the option create table in new work sheet & existing worksheet
5) Click on ok
6) Choose the field name& put it in the pivot table
Pivot chart: – by this command we can create our data base in pivot chart, pivot chart makes it easy to arrange and summarized complicated data and drill down on details.
Steps: –
1) Select the data range
2) Click in insert menu
3) Click on pivot chart option
4) Choose the option create chart in new work sheet and existing worksheet location.
5) Click on ok.
6) Choose the field name and put it in the pivot chart.
FUNCTION
1) If: – if returns the value of a condition you specify evaluates to true and another value of its evaluates to false.
Formula: = if (logical test, value if true, value if false)
For egg: – =if (E6> 400,” Pass”,” Fail”)
= if (E6> 70,” A”, if (E6> 50,” B”, if (E6> 30,”C”,” fail”)))
2) Date: – If return the serial number that represents a particular date.
= Date (year, month, day)
=Date (2013, 10, 22)
=10/22/2013
3) Max: – It Return the Largest Value in A Selected Range.
Formula: – = max (Range)
4) Min: – it returns the lowest value in a selected data range.
Formula: – min (Range)
5) Sum if: – this function is used to calculate the sum of range according to a condition.
Formula: =min (range, criteria)
For example: – =sum if (range,”>500)
6) Power: – This function is used to find the value of the given the base according to the power.
Formula: – =power (no, power)
= power (4, 2)
7) Floor: – this function round down to the level of significance that you specify.
= floor (number, significance)
= floor (2.5,1)
8) Ceiling: – this function around a number away from zero to the nearest integer or to the nearest multiple of significance.
Formula: – =ceiling (numb, significance)
=ceiling (25.647, 1)
= 25
9) Round: – This function is used to round the value up to the given range.
Formula: – =Round (no, no of digit)
For ex: – =Round (67.6874, 2)
= 67.69
10) Days360: – This function is used to find the number of days between starting and ending date.
Formula: – =Days360 (starting date, ending date)
11) Count If: – This function is used to count the values according to given condition.
Formula: – =Count If (Range, “Criteria”)
= Count If (Cell, “>50”)
12) Count A: – This function is used to count the character cell to the selected range
= Counta (Range)
Micro Soft Excel
Formula Menu
1. Trace Precedents: – It show arrows that indicates cells affect the value of the currently selected cells.
For ex 2 3 = 6
3 2
= Product (3*2) =6
2. Trace Dependents: – It show arrow that indicates what cells are affected by the value of the currently selected cell.
For ex 10
2 20
3 30
4 40
3. Show Formulas: – It display the formula in each cell instead of the resulting value
Micro Soft Excel
DATA MENU
1. Goal Seek: – Goal Seek Solves format backwards use goal seeks when you know the result you want but need to determine how much to change a single input to get the desired result.
Steps
a) Go to Data Menu
b) Click on what If Analysis
c) Choose the option goal seek
d) In set cell text value, select cell you want to change
e) In to value text box, type the required value
f) In by changing cell text box, Select the single cell, whose value you want to
change
g) Click on ok
2. Scenario: – It is a group of input value (Called changing cell) Saved with a name These valued area stared in the work sheet as hidden names each Scenario represent a set of what if assumption that you can apply to a work book model to see the effect on other part of the model.
Steps
a) Click on Data Menu
b) Click on what if Analysis
c) Click on Scenario
d) Click on add button to display the add Scenario dialog box
e) Type the Scenario name
f) In changing text box select the cell
g) Click on ok
h) Enter the new value
i) Click on add to return to add
j) Click on ok.
3. Sort: – This is a Command which is used to arrange the data in ascending or descending order by choosing the field name. Excel can be sort the arrow of a list or data base or the column of worksheet
Steps
a) Select any one cell with Data base range
b) Click on Data menu
c) Click on sort command
d) Sort dialog box will appear
e) Choose the field you want to sort from the sort by drop down list
f) Choose the option button of ascending or descending order
g) Click on ok.
4. Filter: – Filtering is a list involves selecting the row / row of Information satisfying some criteria filtering temporarily hide all the row in the list that do not satisfy the criteria.
Steps
a) Select any cell in the database
b) Click on Data Menu
c) Point to filter
d) A drop down control will be attached in each field
e) Click on a drop down control to apply a filter to the field
f) Select one of the field & data to display only those records
g) Where field is equal to data.
5. Data Validation: – This option is used give restriction for the selected cell to show to error message if any on selected cell.
Steps
a) Select the cell
b) Click on Data Menu
c) Click on Data validation
d) Choosing the validation criteria (like text, data & time etc.)
e) Give input message and error alert message
f) Click on ok
6. Sub Total: – Subtotal are quick and Easy ways to Summarize data in a list with Subtotal command Excel create the formula insert the subtotal and grand row and outline the automatically. To use this, feature your list must be sorted because Subtotals are inserted whenever the value in a specified field to insert formula into list automatically move the cell pointer only where in the list.
Steps
a) Select the Data Range
b) Click on Data Menu
c) Click on Subtotal command
d) Dialog box will appear
The Dialog box offer the Following choice: –
1. At Each Change: – This drop down list display all field in your list. The field that you choose must be sorted.
2. Use Function: – This gives you choice of use different function.
3. Add Subtotal to: – Place the check mark next to the field or field the you want to subtotal.
4. Replace Currant Subtotal: – If this book is check any existing subtotal formula are remove any replace with new subtotal.
e) Click on ok
7. Data Table: – Data table allow you to see the result of any different possible input at the same time
Steps
a) Select the both row or columns
b) Click on Data menu
c) Click on what If Analysis
d) Choose the option Data table
e) Select the cell in row / column input cell
f) Click on ok.
8. Text to Column: – To use this feature to convert the selected text into table, splitting a text into column at each comma, space or other character you specify.
Steps
a) Select the cell
b) Go to Data menu
c) Click on text to column option
d) Select the option delimited
e) Click on text
f) Again click on text
g) Click on finish
1. Group: – By this we can tie arrange of cells together so that they can be collapsed or expanded.
Steps
a) Select the row or column
b) Click on data menu
c) Click on group option
d) Select the group row or column
e) Click on ok.
2. From Access: – By this option we can import data from a Microsoft access database or any other application.
3. Remove Duplicate: – By this option we can delete duplicate row from a sheet you can specify what columns should be checked for duplicate information.
4. Consolidate: – By this option we can calculate the selected range one by one reference.
Steps
a) Select the cell
b) Click on consolidate command
c) Choose the function (like sum, avg etc)
d) Click on browse
e) Add the Reference one by one
f) Click on ok
Micro Soft Excel
Page Layout Menu
1. Themes: – This option is used to change the overall design of the entire document, including color’s, font and effects.
2. Margin: – By this option we can select the margins size for the entire document or the current section
3. Orientation: – By this we can switch the pages between portrait & land scape.
4. Size: – By this command we can choose a paper size for the current section to apply a specify paper size to all section in the document.
5. Print Area: – By this command we can mark a specify area of the sheet for printing
Steps
a) Select the database for set a print area
b) Click on page layout
c) Click on Print Area command
d) Choose the option set print area and click on it
e) You can see the printed area data are show in dotted lines.
6. Back Ground: – By this command we can set the picture back ground in the sheet.
7. Break: – This option is used to insert the page break in open page at the selected cell, in page break preview it will show the break with a dotted line, we can also remove it by click on remove page break option.
Steps
a) Selected the cell where you want to break
b) Click on page layout menu
c) Choose the option break
d) Click on insert Page break
e) If you want to remove page break again click on break
f) Click on remove page break
8. Gridlines: – By this command we can show/hide the lines between rows and column in the sheet to make editing and reading easier. These lines will not print unless print is also checked.
9. Heading: – By this option we can show or hide the row and column heading that appears above the column on a sheet.
10. Order: – By this command we can change the series of two selected object foe eg: – bring to front, send to back etc.
11. Align: – By this option we can give alignment to the two selected object like align left, Right, top etc.
12. Group: – By this command we can create group between two selected objects.
13. Rotate: – By this command we can rotate our selected object according to angle like rotate 90 degree , Left etc.
14. Print Titles: – BY this option we can set the specific row and column to repeat on each printed Page.
Micro Soft Excel
Review Menu
1. Spelling (f7): – By this option we can check our spelling mistake and we can also correct our mistake.
2. Insert Comment: – By this command we can add a comment about the section.
3. Protect Sheet: – By this command we can protect our sheet by giving password nobody cannot do changes in our sheet.
Steps
a) Select the sheet
b) Click on review menu
c) Choose the option protect sheet
d) Window will get opened, Enter the Password
e) Again repeat this password
f) Click on ok
4. Protect Work Book: – By this command we can protect our work book by giving password. Person cannot delete, move, copy or insert new worksheet in our work book
Steps
a) Click on review menu
b) Choose the option protect work book
c) Give the password for protect work book
d) Again type the same password
e) Click on ok
5. Thesaurus: – By this option we can check or see the alternative meaning of our selected words.
6. Track Changes: – Track all changes made to the document, including insertions, deletions and formatting changes so, we can accept or reject this changes
Micro Soft Excel
View Menu
1. Normal: – By this we can view the document in normal view
2. Page Layout: – By this we can view the document as it will appear on the printed page
3. Full Screen: – By this option we can view the document in full screen mode.
4. Show/hide: – By this option we can show or hide our formula bar, heading or gridlines
5. Zoom: –By this command we can view the document according to choose percentage of zoom.
6. Zoom to selection: – By this we can zoom the work sheet so that the currently selected range of cells fills the entire window
7. New window: – By this we can open a new window containing a view of the current document
8. Arrange All: – By this command we can arrange our all opened window in Tile, Cascade, horizontal and vertical
9. Freeze Pane: – By this we can keep a portion of the sheet visible while the rest of the sheet scrolls
Steps: –
1. Select the cell where you want to freeze Pane
2. Click on view menu
3. Click on Freeze Pane
10. Save Work Space: – By this Command we can save the current layout of all windows as a work space so that it can be resorted later
11. Macros: – This command is used for recording purpose at the time of creating document
Steps: –
1. Click on view menu
2. Click on Macro option
3. Click on record macro
4. Give the macro name
5. Type any data
6. Click on stop recording
Micro Soft Excel
Advance Function
1 Mod: – It returns the remainder of number is divided by divisor
Formula: – =mod (no, divisor)
=mod (27, 5) =2
2 Now: – By this function we can see current date and time in a cell
Formula: – =Now ()
3 Today: – By this function it returns the current date in selected cell. This function needs no argument
Formula: – =today ()
4 Upper: – It converts our text into upper case
Formula: – =upper (text)
5 Lower: – It converts our text into lower case
Formula: – =lower (text)
6 Proper: – It converts our text into title case
Formula: – =proper (text)
7 Concatenate: – It is used to join several cell containing text into a single cell. By using this we can make a proper sentence.
Formula: – = concatenate (Text1, text2)
8 Power: – By this option we can see power of a number.
Formula: – power (number, power)
9 Square Root: – by this option we can see the square root of a number.
Formula: – sqrt (number)
10 Left: – By this option we can extract some character from left side.
Formula: – left (text, no. of character)
11 Right: – by this option we can extract some character from right side.
Formula: – Right (text, no. of character)
12 Len: – It count the no. of character in a cell
Formula: – =Len (text)
13 Exact: – by this we can check that two text are same or not
Formula: – Exact (text1, Text2)
14 Weekday: – It returns the day of the week from a particular date.
Formula=weekday (serial no.)
15 Roman: – By this we can convert our numeric number in Roman number
Formula: – Roman (number)
=Roman (5)
=V
16 Count A: – by this we can count our cells of character or number cells.
Formula: – =CountA (cells)
Hookup and VLookup function
Hookup value: – it looks for a value in the top row of a table or array of value and returns the value in the same column from a row you specify.
Formula: – =Hookup (Look up value, array table PressF4, row index number)
VLookup value: – It looks for a value in the left most 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.
Formula: – =VLookup (VLookup value, array table press F4, clo-index num)
Micro Soft Excel
DFUNCTION
1) Dmin: – The Dmin Function return the smallest number in a column in a list or database, based on a given criteria.
Formula: – Dmin (Database, Field, criteria)
2) DMax: – The DMax Function returns the largest number in a column in a list or data base, based on a given criteria.
Formula: – DMax (Database, field, criteria)
3) Dsum: – The Dsum function sums the number in a column or database that meets a given criteria
Formula: – Dsum (Database, field, criteria)
4) DGet: – The DGet function return a value from a database a single record that matches a given criteria.
Formula: – DGet (Database, field, criteria)
5) Dcount: – The Dcount returns the number of cells in a column or database that contains number and meets a given criteria.
Formula: – Dcount (Database, field, criteria)
6) Dcount A: – The Dcount A function returns the number of cells in a column or database that contain non-blank values and meets a given criteria.
Formula: – DcountA (Database, field, criteria)
7) Match Function: – The match function searches for a value in an array and return the relative position of that item.
Formula: – Match (Value, Array, Match type)
The type of match that the function will perform
Match Type Explanation
1 The match function will find the largest value that is less than or equal to value you should be sure to sort your array in ascending order
0 The match function will find the first value that is equal to value, the array can be sort -ted in any order.
-1 The match function will find the smallest value that is greater than or equal to value.
Wild Card
* Match any Sequence of character.
? Match any single character
Formula: – Match (Value, array, match type)
8) Index: – The Index function returns either the value or the reference to a value from a table or range.
Formula: – = Index (Array, Row num, Column)
Macros
1) Create a own cell style by macros
Steps
1) Create a work book
2) Click on view macro
3) Click on macro
4) Click on record macro
5) Give the macro name, click on ok
6) Select the formatting which you want to apply in your style like (font, size, color, etc)
7) To stop the recording
8) Again click on view menu
9) Click on stop recording
10) Select the cells in which you want to apply style
11) Click on view menu
12) Click on macros
13) Click on view macro or press alt+F8
14) Select the macro name
15) Click on run.
2) Select the formulas cells by macros
Steps
1) Click on view menu
2) Click on macro, Click on record macro
3) Give the macro name, Click on ok
4) Click on home menu
5) Click on find and select option in the right hand side of the window
6) Choose the option formulas
7) They select the formulas cells
8) Do the formatting according to our choice
9) Stop the recording, In view menu
10) Select any sheet
11) Press Alt+F8 for view macro
12) Select the macro menu
13) Press Run
3) Subtotal by macros and apply in action button.
Steps
1) Click on view menu
2) Click on macros, Give the macro menu
3) Select the range and apply the subtotal command from the data menu
4) Stop the recording by clicking stop recording from the view menu
5) Create any shape from the shape command in the insert menu (like rectangle, oval)
6) Select the range shape
7) Right click from mouse in the shape
8) Click on assign macros
9) Select the macros name
10) Click on ok
Micro Soft Excel
Advance Function
1. Networksdays: – This function returns the number of whole working days between start date and end date working days exclude weekends and any dates identified in holidays.
Formula: – =Networksdays (start date, end date, holidays)
2. PPMT: – This function return the payment on the principal for a given period for an investment based on periodic, constant payment and a constant interest rate
Formula: – =PPMT (Rate/12, per, nper, PV, 0)
3. Rate: – It returns the interest rate per period of an annuity. Rate is calculated by amount and can have zero or more.
Formula: – =Rate (nper*12, pmt, PV, fv, type)
4. Received: – This function return the amount received at maturity for a fully invested security.
Formula: – =Received (issue date, maturity date, investment, disrate, actual)
5. Coupnum: – It returns the number of coupons payable between the settlement dates, rounded up to the nearest whole coupon.
Formula: – =Coupnum (settlement, maturity, frequency, basis)
6. Maxa: – This function is also similar the max function. It find the largest value in a selected Range.
Formula: – = Maxa (Range)
7. Mina: – This function is also similar the min function. It find the smallest value in a selected range.
Formula: – =Mina (Range)
8. Large: – It returns the largest value in a data set.
Formula: – = Large (Range, Serial no)
9. Dollar:- This function convert a number to text using currency format, with the decimal rounded to the specified place
Formula: – =Dollar (no, decimal no)
10. Average A: – Calculate the average (arithmetic mean) of the values in the list of argument
Formula: – =Average A (Range)
11.Log: – It return the logarithm of a number to the base you specify
Formula: – =Log (number, base)
12. Disc: – This function returns the discount rate for a security.
Formula: – = Disc (settlement, maturity, pr, redemption, basis)
13.Countifs: – By this function we can count the particular value from the particular criteria and range.
Formula: – = Countifs (Range)
14.IPMT: – By this function we can find out the interest amount of the principal value.
Formula: – = -IPMT (Rate/12, per, nper, PV, type)
15.Mode: – This function is refers to arithmetic value rather than the location of the number in a list.
Formula: – =Mode (Range)
16.Median: – This function finds the difference between the two selected range.
Formula: – = Median (Range)
17. Future Value: – By this function we can find the future value of the invested amount today.
Formula: – = FV (Rate, time, PV, 0)
18.Present Value: – By this function we can find the present value of the invested amount received in future.
Formula: – = PV (Rate, time, fv, 0)
19.Round Down: – This round function is used to round a number down towards the next lowest number.
Formula: – = Round down (no, no digit)
20.Round Up:- This round up function is used to round a number upwards towards the next highest number
Formula: – = Round Up (no, no digit)
Micro Soft Excel
Look Up Function
The looks up function returns values either form a one- row or one- column range or form an array. The look up function has two Syntax Forms: – the vector form and the array form.
1. Vector Form: – A vector is a range of only one row or one column. The vector form of look up looks in a one- row or one- column. Range (known as a vector) for a value and return a value from the same position in a second one- row or one- column range, use this form of the look up function. When you want to specify the range that contains the value that you want to match the other form of look up automatically
looks in the first column or row.
Formula: – = Look Up (Look up value, look up vector, result vector) enter
Largest Value = Smallest Value
For ex: – Frequency Color
4.14 Red
4.19 Orange
5.17 Yellow
5.77 Green
6.39 Blue
=Look up value (4.19, A2:A6, B2:B6) = Orange
= Look up value (7.66, A2:A6, B2:B6) = Blue
2. Array Form: – The array form of lookup in the first row or column of an array for the specified value and return a value form the same position in the last row or column of the array. Use this form of look up when the values that you want to match are in the first row or column of the array. Use the other form of look up when you want to specify the location of the column or row.
Formula: – =Look up (look up value, array)
Largest value = less than = equal to value in array must be placed in ascending order.
For ex: – Score
45
90
78
Formula = look up (45, {0, 60, 70, 80, 90}, {“f”,” d”,”c”,” b”,” a”})
3. Not: – If logical is false, not returns true, if logical is true, not returns false.
Formula = Not (1+1=2) = False
=Not (120+540=640) enter = False
4. And: – It return true if all its argument evaluates to true; return false if one or more arguments evaluate to false.
Formula = and (1, A3, B3 100) = True
= IF (and (10 B3, B4 500), B4, 250)
5. OR: – It return true if any argument is true; return false if all arguments are false.
Formula: – =OR (Logical, Logical_ _)
For Ex: – = IF (and (10 B3, B4 500), or (B4 1000), 1200)
= IF (and (10 B3, B4 1500), or (B4 850), 1200)
6. Iferror:- It return a value you specify, If a formula evaluates to an error, otherwise, return of the formula
Formula: – If error (value, value if error)
Foe eg: – If error (c1/c2, “error message0”)
7. Index by Reference From
It return the reference of the cell at the inter section of a particular row and column, if the reference is made up of non adjacent selection, you can pick the selection to look in
Formula: – Index (Reference, row_num, column_num, area_num)
Forex:-
Fruits Price Count
Apples 0.69 40
Bananas 0.34 38
Lemons 0.55 15
Orange 0.25 25
Pears 0.59 40
Almonds 2.8 10
Cashews 3.55 16
Peanuts 1.25 20
Walnuts 1.75 12
Formula: – Sum (Index (A2: C12, 0, 3)
Sum (B2: Index (A3: A7, 3, 1)
Sum (B2: Index (A3: C7, 5, 1)
8. PMT: – Per Month Transaction: –
By this function we can find out the monthly Installment amount by giving the loan amount
Formula: – PMT (Rate, nper,-PV, Type)
For ex: – PMT (8%.12, 12, _93000, 0)
Paretos charts
Charts is a graphical presentation of numerical data. In paretos charts we shows your data in three axis i.e X,Y,Z in a different layout of charts.
Steps
Select the range (for ex: only select the three column range)
Click on Insert menu,Select the chart layout and click on it like 2-D column.
Chart will created.
Select the any bar in the chart and right click on it.
Click on chart area.
Select the secondary axis option.
If you want to Z-axis data show in any other chart layout.
Select the chart bar.
Click on layout in the chart tool bar.
Click on change chart type.
Choose the any layout of chart like Line Chart.
Click on OK, your Paretos charts can be created.
Dash board
In dashboard, we can show our data in pick list.
Create a chart by dashboard
Select the starting two-column range.
Create the column chart, chart will be created.
Remove the legend heading.
Create a heading by the validation command.
Below the drop down list.
Click on formula menu, click on fine cell.
Give the cell name like chart column.
In the refers to give the offset formula like =offset (select your heading,0, match (select the validation cell of drop down list,0)12,1)
Click on ok
Select the chart, click on design option in layout menu of table tool menu.
Selection the option select data
Click on edit, select the second heading from the validation cell
In the series value, remove the value after this sign! And the formula name by press F3 KEY
CLICK ON OK
Micro Soft Excel
FINANCIAL FUNCTION
1) DB: – It return the depreciation of an asset for a specified period by using the fixed declining balance method.
For examples: –
Machinery value=1,000,000
Estimated value =100000
Life time in year=6
= DB (Machinery value, estimated value, Year, Month)
2) Ddb: – it returns the depreciation of an asset for a specified period by using the double – decling balance methods or some other method you specify.
For Example: –
Fixed cost=2400
Estimated value=30
Lifetime in year=10, calculate the dep.amt of Ist day, Ist month, Ist year, 2nd year & tenth year.
FORMULA :- 1) DDB (FIXED COST, ESTIMATEDVALUE, 10*365, 1)
2) DDB (FIXED COST, ESTIMATED VALUE, 10*12, 1, 2)
3) DDB (A2, A3, A4, 1, 2)
3) CUMIPMT: – It return the cumulative interest paid on a loan between start period and end period.
EXAMPLE: – =CUMIPMT (Annual interest/12, year*12, loan, 13, 240)
= CUMIPMT (A2/12, YEAR*12, LOAN, 1, 1, 0)
4) CUMPRINC: – It return the cumulative principal paid on a loan between start period and end period
EXAMPLE: – =CUMPRINC (Annual interest/12, year*12, loan, 13,240)
= CUMPRINC (A2/12, YEAR*12, LOAN, 1, 1, 0)
5) ISPMT: – it calculates the interest paid during a specific period of an investment
EXAMPLE: –
Annual Interest Rate=10%
Period=1
No of Year in the investment=3
Amount of loan =800000
FORMULA: –
=ISPMT (rate/12, period, no of year*12, loan)
6) SLN: – It return the straight line depreciation of an assets for a period
EXAMPLE: –
Cost=30,000
Estimated=7500
Year of useful=10
FORMULA = SLN (cost, estimated, year)
7) EDATE=It return the serial number that represent the data that is the indicated number of month before or after a specified date (start date) use edate to calculate maturity dates or due dates the fall on the same day of the month as the date if issue
EXAMPLE: –
Start date=1/15/2008
Month=1
Formula: –
=edate(startdate,month)
If we set -1, reduce the 2 month
8) EOMONTH:-It return the serial number for the last day of the month that the indicated number of month of months before or after start date. the eomonth to calculate maturity dates or dues dates the fall on the last day of the month
FORMULA: –
=EOMONTH (date, month)
9) MONTH: – return the month of a date represent by a serial number the month is given as an interest, ranging from 1(Jan) to 12 (Dec)
FORMULA: –
=month (Date, type)
10) WORKDAY: – It calculate the no of work if days excluding the holidays
EXAMPLE: –
Start date=10/1/08
Completion day=151
Holiday=26/11/08
FORMULA: –
=WORKDAYS (start date, days)
SPEED O METER
SPEED O METER POINTER
START 0 VALUE 64
INETIAL 15 POINTER 1
MIDDLE 45 END 135
END 40
MAX 100
STEP
1) Create a circle chart in insert menu
2) Select the heading speed o meter in legend enter and select their values in series
3) Click on ok
4) Select the chart, click on format data series by right click
5) And the rotation by value 271
6) Select the below portion of chart and fill color no fill
7) Select the one by one and change the color you want
8) Remove the legend
9) Select the chart, click on design
10) Click on select data
11) Select the series name pointer
12) Select their value of pointer
13) Click on ok
14) Select the chart and convert in pie chart
15) Right click, click on format data series
16) Give the rotation value 270
17) Fill the color none
18) Right the text by = sign
Read Also Shortcuts in Excel | Microsoft Excel | Shortcuts That Every Windows User Should Know
Pingback: ISBLANK Trim and EDATE function in Excel - Last-Date