Understanding Basic Concepts of Microsoft Excel
Microsoft Excel is a powerful spreadsheet program with features like worksheets, workbooks, cells, ranges, and functions. Learn about terms like merging, unmerging, inserting cells, and using arguments in functions to enhance your Excel skills.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Ms Excel Meaning:- It is basically a spreadsheet program formed by Microsoft, in which there are rows and column which used to organizethe data with function and formula. Basic Terms:- WORKSHEET:- It is a grid of horizontal rows and vertical column. Rows:- Rowsare horizontal staring from 1 and goas 2,3,4....Maximum row in ms excel worksheet is 1,048,576(2019). Column:- Column are vertical staring from A and go as B, C. Colum are lettered as A-Z, AA-AZ, BA-BZ, AAA-AAZ, ABA-ABZ and go on... Maximum columnin excel worksheet is 16384(2019).
WORKBOOK:- A spreadsheet allows youto combinemore than one worksheet in a file. Such a file having multiple worksheets is know as a workbook. DIFFERNCEBETWEEN WOEKBOOK AND WORKSHEET A WORKSHEET is a grid of cells made up of rows and columns. Multiple worksheets can be combinedundera file know as WORKBOOK. CELLS:- Cell is a basic unit of worksheet where numbers, texts, formulas etc. Can be placed. Cell is formed by intersection of rows and column and this intersection gives a cell a unique address i.e. the combinationof the column letter and row number. FOR EX:- If a row 4 intersected by a column E, then the cell formed out of it gets an address E4. Cell Pointer:- It is a cell boundarythat specifies which cell is active at that moment Current cell:-It is a cell which is active. This is the cell where cell pointer point to. And it is the cell where the next entry would take place. Range of cells:-A range of cells is a group of contiguouscells that form rectangulararea in shape. For ex:- a range starting from F7 till G14 would be written as F7:G14 in ms excel.
RANG E CELL CELL
Important terms Merging and unmerging of cells:- Combiningmultiple cells (two or more) that are in the same row and/orin the same columnto a single cell is known as merging cells. Separating the cells that are merged is known as unmerging cells
Inserting a cell:- 1.Select the cells, rows, or columns where youwant the new, blank cells to appear. 2.Click the drop-down arrow attached to the Insert button in the Cells group of the Home tab. 3. Click Insert Cells on the drop-downmenu. Shift Cells Right shifts existing cells to the right to make room forthe blank cells youwant to insert. Shift Cells Down instructs Excel to shift existing cells down.This is the default option. Entire Row inserts complete rows in the cell range. Youcan also select the row number on the frame beforeyouchoose the Insert command. Entire Column inserts complete columns in the cell range.Youcan also select the column letter on the frame beforeyouchoose the Insert command. Thenclick Ok
MS EXCEL FUNCTION BASIC TERMS ARGUMENTS:-Arguments are the values passed to the functions, using which the function carries out some task. Syntax:- The structure of a function begins being with function name, followed by an opening parenthesis, the arguments for the function are separated by comas, and a closing parenthesis.
1. SUM Meaning:- This function is used for adding number in excel Syntax:- Sum function=sum(number1, number 2...) Arguments :- number1 - The first value to sum. number2 - [optional] The secondvalue to sum.
Exampl e:- 1. Enter the function A B C 2. Select the column 3. press enter 1 NAME MARKS1(20) MARKS2(20) 2 RITA 12 14 3 SEEMA 14 16 4 SONIA 16 18 5 TARUN 18 10 6 TINA 19 17 7 TOTAL =sum(B2:B6) =sum(C2:C6) Marks1 total is i.e. 79 same apply to marks 2 columni.e. 73
2.IF Meaning:- Function is used for whether conditionis true or false Syntax:- =IF (logicaltest, [value if true], [value if false]) Arguments:- logicaltest - 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.
Example:- A B After applying the function of IF:- 1 NAME MARKS = if(B2:B6>35, pass", fail") This means marks greater than 35 true and less than 35 false 2 MEENA 75 3 SURAJ 60 4 SONALI 35 5 REKHA 54 6 GEETA 30 FUNCTIONIS PLACED FFLRGT A B C 1 NAME MARKS RESULT CLICK ON CELL THEN GO RIGHT CORNER OF CELL THAN SCROLL DOWN FOR GETTING REMAINGRESULT 2 MEENA 75 pass 3 SURAJ 60 pass 4 SONALI 35 fail 5 REKHA 54 pass 6 GEETA 30 fail
2A. IF OR Meaning:- One condition to be true or false Syntax:- =IF(OR(logical test 1,logical test 2) , [value if true], [value if false]) Arguments:- Logicaltest 1 - The first conditionor logical value to evaluate. Logical test 2 - [optional] The second condition or logical value to evaluate. 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.
Example:- A B C D FUNCIONIS 1 PRODUCT WAREHOUSE1 WAREHOUSE2 RESULT APPLIED 2 TABLE 0 10 INSTOCK 3 CHAIR 0 0 OUTSTOCK CLICKONCELL THENGO RIGHT CORNEROF CELL 4 BED 5 1 INSTOCK 5 CABINET 0 0 OUTSTOCK THENSROLLDOWN After applyingthe function:- =if(or(warehouse1>0,warehouse2>0),"instock","outstock")
2B. IF AND Meaning:- All conditionneed to be true Syntax:- =IF(AND(logicaltest1,logical test2) , [value if true],[value if false]) Arguments:- Logicaltest 1 - The first conditionor logical value to evaluate. Logicaltest 2 - The second conditionor logicalvalue to evaluate. Value if true - The value to return when logical test evaluates to TRUE. Value if false - The value to return when logical test evaluates to FALSE.
Example:- A B C D FUNCTIONIS 1 STUDENT MARKS1 MARKS2 RESULT 2 STUDENT 1 30 60 FAIL APPLIED 3 STUDENT 2 50 40 FAIL 4 STUDENT 3 75 64 PASS 5 STUDENT 4 66 35 FAIL 6 STUDENT 5 80 77 PASS After applying the function:- =if(and(marks1>40,marks2>40), pass", fail")
3. SUM IF Meaning:- The powerfulSUMIF function in Excel sums cells based on certain criteria Syntax:- =SUMIFS(sum range, range1, criteria1, [range2], [criteria2],...) Arguments;- Sum range - The range to be summed. range1 - The first range to evaluate. criteria1 - The criteria to use on range1. range2 - [optional] The secondrange to evaluate. criteria2 - [optional]The criteria to use on range2.
Example:- FUNCTION A B APPLIED 1 A 1000 2 B 1300 3 C 900 4 A 500 5 B 1500 6 C 800 After applyingthe function:- =SUMIF(A1:A6,"=A",B1:B6) ONLY A alphabet amount added that is 1500
Summary Sum:- =sum(number1, number 2...) If:- =IF (logicaltest, [value if true], [value if false]) If Or:- =IF(OR(logical test 1,logicaltest 2) , [value if true], [value if false]) If And:- =IF(AND(logical test 1,logical test 2) , [value if true], [value if false]) Sum If:- =SUMIFS (sum range, range1, criteria1, [range2], [criteria2], ...)
