computer excel course ~ computer science

excel notes

what is excel

Users of Microsoft Excel can format, organize, and calculate data in a spreadsheet. Data analysts and other users can make it easier to examine information as data is added or changed by organizing the data using tools such as Excel. Excel consists of a number of boxes called cells that are organized into rows and columns.

electronic spreadsheet

In a tabular structure with rows and columns, an electronic spreadsheet program accepts data values and establishes relationships between data values. This enables users to perform calculations using these values. Also, it formats the data in the desired way.

Spreadsheet Glossary

1. Workbook

2. Worksheet

3. row

4. Column

5. Cell

6. Formula

7. Work

Workbook:-

Worksheet:-

The first (leftmost) worksheet of the workbook is titled Worksheet(1), and its last worksheet is titled Worksheet(Worksheet. Count). The index count includes all worksheets, even those that are hidden. On the worksheet's tab, the name of the worksheet is displayed. The name of the worksheet can be set or returned using the name attribute.

Line:-

A row in a worksheet in Microsoft Excel runs horizontally along a grid pattern. The horizontal rows have numbers like 1, 2 and 3 as their numbers. Alphabetic values such as A, B, and C are used in the vertical column numbers. A row in a worksheet in Microsoft Excel runs horizontally along a grid pattern. The horizontal rows have numbers like 1, 2 and 3 as their numbers. Alphabetic values such as A, B, and C are used in the vertical column numbers. : A row is a horizontal section of cells. The rows are numbered from 1 to 1048576.

column:-

A column is a vertical block of cells, numbered from column A to XFD i.e. 16384 columns. To add a new column, right-click the entire column to the right of the desired location and choose Insert Column. To add multiple columns: Select the same amount of columns to the right of where you want to add new columns. Insert column using the right-click menu to select the option.

Room:-

The intersection of a row and a column that actually contains values is called a cell. The character limit of the cell is 255. A1 is an example of a cell reference, which is a unique identifier for each individual cell.

Formula:-

A formula is an equation that calculates values from a worksheet. It is a list of values, cell references, operators, and functions. It starts with an equal (=) sign.

· . To fill series in column

The shortcut key à + E + I + S

· To insert new Sheet à Shift + F11

To switch between sheets

previous sheet à Ctrl + peg up

Next Sheet à Ctrl + Peg Down

Work:-

Functions are pre-written formulas that accept values and perform complex computations using particular values in a particular order to obtain a result.

SUM() à This function adds up all the numbers specified in a range.

Syntax: =SUM(number1,number2,……)

Example: =SUM(A3,B3,D3)

come on

=SUM(A3:D3)

· avg() à This function returns the average of all the numbers specified in a range.

Syntax: =AVERAGE(number1,number2,……)

Example: = Average (A3, B3, D3)

come on

= more (A3:D3)

· Max() à This function returns the largest value in a range

Syntax à = max (number 1, number 2, number 3, …..)

Example: =max(A3, B3,D3)

come on

=max(A3:D3)

· min() à This function returns the smallest value in a range

Syntax à =min(number1, number2, number3,…..)

Example: =min(A3, B3,D3)

come on

=MIN(A3:D3)

· if() à This function is used to act as a decision maker. if() is used for conditional calculation or sending message, which is widely used in excel

Syntax à = if (condition, true part, false part)

logic function

1 AND ( )

2. OR ( )

3. NOT( )

4. IF ( )

Operators :->, < , >=, <=, =

AND() :- This function returns TRUE if all the conditions given as arguments are TRUE

Syntax: = AND (Logical 1, Logical 2, Logical 3, ...)

Example: =AND(a1>b1, b1<c1)

OR() :- This function checks whether all the conditions are true or not. Returns TRUE if any one condition is TRUE and FALSE if all conditions are FALSE.

Syntax: = OR (Logical 1, Logical 2, Logical 3,…)

Example: =OR(a1>b1, b1<c1)

NOT( ) :- This function converts True to False and False to True.

Syntax: = not (condition)

Example: =NOT(a1>b1)

IF( ):- This function checks whether the condition is satisfied or not. Return one value if true and another value if false

Syntax: =if (logical test, true part, false part)

Example: =IF(AVERAGE >= 50, "PASS", "FAIL")

=IF(NUMBER>=0,"positive number","negative number")

=IF(age>=18,"Eligible to vote","Not eligible to vote")

NESTED IF: Nested if() is an additional function of if(), which allows us to define one or more if() inside the if body.

Syntax :

= if (condition, true part, if (condition, true part, false part))

Example:

=IF(AVERAGE>=50,"PASS",IF(AVERAGE>=33,"COMPT","FAIL"))

statistics function :

count( ) :

This function counts the number of cells in a specified range that contain number values.
SYNTAX à =count(value1,value2,…)
EXAMPLE à =count(a1:a9)

COUNTA( ) :

This function counts number of cells in a specified range that are not empty(count all type of data ).

SYNTAX à =counts(value1,value2,…)

EXAMPLE à =counts(a1:a9)

COUNTBLANK() :

This function count the number of empty cells in a specified range.

SYNTAX à =countblank(value1,value2,…)

EXAMPLE à =countblank (a1:a9)

COUNTIF():

This function counts the number of cells in a specified range that meets the condition.

SYNTAX à =countif(range,criteria)

EXAMPLE à =countif(a1:a10, “manager”)

=countif(a1:a10,”>30000”)

COUNTIF( ): This function counts the number of cells in a specified range that meets the condition.

SYNTAX à =countif(range,criteria)

EXAMPLE à =countif(a1:a10, “manager”)

=countif(a1:a10,”>30000”)

what is Macro?

Ans:-

You can record a macro to automate actions in Microsoft Excel that you perform frequently. A macro is an action or series of acts that can be repeated endlessly. You are capturing your keystrokes and mouse clicks when you construct a macro. You can edit a macro after you've created it to make a few tiny modifications to how it functions.

Let's say you write a report for your accounting manager each month. The names of the clients who owe money should be formatted boldly and in red, respectively. To quickly apply these formatting changes to the cells you choose, you can construct and execute a macro.

How to create buttons using Macro in excel?

1. Type the data in excel sheet

2.Select the entire tableàApply Filter

3.Start the Macro (ViewàRecord Macro)

Recording will start, as per example of Mark sheet data.

4.Click on the arrow of Result column

a. Select Pass àok

5.Now stop the macro (View à Macro à Stop Recording)

Same goes for Fail option

For Button:-

6.Go to Insert Tab à Shapes àSelect the shape of your choice

7.Place it in Sheet à Type the text over it

To Assign Macro on button

8.Right Click on the shape

9.Assign Macro àselect the macro to assign

How to create buttons using Macro in excel?

(Using Advance Filter)

1.Type the data in excel sheet

2.Make the criteria in sheet

3.Start the Macro (ViewàRecord Macro)

Recording will start, as per example of Mark sheet data

4.After starting the macro, Go to Data Tab à Click on Advance Filter

5.A dialog box will appear asking for some selection

First will be the action

1.Filter the list, in-place

2.Copy to another location

Second

1.List Range àHere, select the entire table

2.Criteria Range àSelect the criteria long with heading

3.Copy to another location (optional) à It will be used to show the filtered data in another place. This option will be used only if you have selected “Copy to another location”.

6.Now stop the macro (View à Macro à Stop Recording)

For Button:-

7.Go to Insert Tab à Shapes àSelect the shape of your choice

8.Place it in Sheet à Type the text over it

To Assign Macro on button

9.Right Click on the shape

10. Assign Macro àselect the macro to assign

sales

Blog Archive

Disqus Shortname

Best sellers

Pages

Recent Posts

Menu

computer science