MS Excel Notes

Microsoft released the first version of Excel for Macintosh on September 30, 1985. After this, many versions of MS Excel launched in the market, each version has its own distinct features. AM Excel is a Microsoft spreadsheet program. This program is most commonly used in places where calculus is needed. It is a very fast and simple program to calculate. It includes tables, result sheets, calculations. Etc. work can be done

Introduction to MS Excel

It is a software of MS Office. With the help of which we can do data processing work on the database. And can store data. There are different types of tools for managing the database. Using which you can format the database. There are nine menus in the menu bar of this application software. It has different types of toolbars. You can use it easily. It has an application window. Which has a workbook inside. The workbook is contained within the workbook.

In Excel, data is stored in a worksheet. There are row and column in it. Cells are made up of ray and column, each cell has an address in it. Which is called a cell address. This address is made up of the names of columns and rows. Such as A1, BB10 etc. A work seat has 65536 rows and 256 columns. Rea's name is from Number. And the name of the call remains in alphabet. It has the following total cell count. 65536 * 256 = 16777216 Aworkbook contains 256 work seats. The file is made in it. Its secondary name is .XLS.

Workbook

This is an Excel file. In which there are many work seats. In which data is stored. A workbook contains 256 worksites. Y defaults are three work seats. New work seat can be added or deleted. Can be renamed and in this, the work of copying the seat etc. can be done easily. When opening the workbook, the work site opens automatically. Only one workbook can be worked on at a time. Which is called Active Burke Seat.

Worksheet

The worksite is like a page in a book. In which we can store data. A worksheet has 65536 rows and 256 columns. And 65536 * 256 = 16777216 is a cell. Each column has a name of 55. Which are alphabet. This range is from A to IV = 256. And Ro's name is numeric. Its range is up to 1 To 65536. It can be renamed.

Cell

 A cell is formed by the joining of Roe and Colum. A worksheet consists of 65536 * 256 = 16777216 cells. Data is written to the cell. 255 characters can be written in a cell. The name of the cell is formed by combining the name of Colum and Row. This is the address of the cell. The two cell ads together form the range ads. In this, two or more cells can be merged and the cell's formatting can also be done.

Functions and its Types

Formulas or functions are very important in Excel when we want to do some calculations like - adding some cells in a column, multiplying one number by another, averaging the data of a range, etc., then we can formulas for that calculation. Let's use a formula that is filled in the cell where we want to show the result of the calculation. Formulas in MS Excel always start with an equal sign (=).

Excel has pre-defined formulas called functions that can perform various types of functions such as addition, occurrence, multiplication, division, date, time, etc. These types are. Which is as follows.

1. Math or String
2. Date & Time
3. Text
4. Financial
5. Logical
6. Statically
7. Lookup or reference

Use of Function

By Direct Type: - In  this, type the function after the sign of "=" and insert the Argument into it. And finally you can get the result by clicking inter or clicking on it.

Through the menu : -  Clicking on the Insert menu → Function, the function dialog box appears. In which the function is selected. And click on ok button. And then give his arguments in it and click on the ok button.

Math Functions in Excel

1. SUM (): -  Numbers are added by this function. In this, the value, cell address or cell range can be given.

Example:- =SUM(a1:a4)

result:- 180

2. SQRT (): - With  the help of this function any number of SQUARE ROOT is extracted. In its argument, give the cell address of a number or give that number. Whose SQUARE ROOT is to be extracted.

Example:- =SQRT(25)

3. odd (): - With  this function even number can be converted to odd number.
Example = odd (80)
Result = 81

4. even (): - By this  function odd number can be converted into even number.
Example: - = even (79)
Result = 80

5. MOD (): - With  the help of this function, the remainder of any number is derived. Two arguments are given in it.
Example: - = MOD (26,5)
result = 1

6.POWER (): -  The power of any number can be calculated from this function. Two arguments are given in this, first number, second power.
Example: - = POWER (5,2)
Result = 25

7. ABS (): -  derives absolute value from it. That is, if there is any sign. Then it is removed. It takes a number in the argument.

Example:- =abs (-125)
Result = 125

8.Fact (): - With the help of this function, the Factorial number of any number can be extracted. It takes a number in the argument.
Like, there is a Factorial of 5. 1 * 2 * 3 * 4 * 5 = 120

Example:- =fact (5)
Result= 120

9. INT (): - Can extract the integer value of any number from this function. It takes a number in the argument.

Example:- =int(123.34)
Result=123

Text/String Functions in Excel

This function is used for tact. Therefore they are called tact function. This is the following.
1. UPPER (): - This function converts lower case letter to upper case.

Syntax:- =UPPER(TEXT)
Example:- UPPER(“Cyber Dairy Solutions”)
Result:- CYBERDAIRY SOLUTIONS

2. LOWER (): -  This function converts the upper case letter to lower case letter.

Syntax:- =LOWER(TEXT)
Example:- LOWER(“COMPUTER HINDI”)
Result:- computer hindi

3. Proper (): -  This sets the function text to proper case.

Syntax: =proper(TEXT)
Example:- proper(“COMPUTER HINDI”)
Result:- Computer Hindi

4. Len (): -  This counts the letters of the function text.

Syntax: =len(TEXT)
Example:- len(“Computer”)
Result:- 8

5. left (): -  This function extracts the letters of the word from the left side. Tact and how many letters to remove in it. Its number has to be given.

Syntax: =Left(TEXT, Number)
Example:- Left(“Computer”,3)
Result:- Com

6. Right (): -  This function extracts the letters of the word from the right side. Tact and how many letters to remove in it. Its number has to be given.

Syntax: =Right(TEXT, Number)
Example:- right(“Computer”,2)
Result:- er

7. TRIM (): - This function eliminates the space behind the front of the tactic.

Syntax: =Trim(TEXT)
Example:- trim(“   Computer ”)
Result:- Computer

8.MID (): - This function extracts  words from between the letters. In this, the letter has to be extracted from the tact and where as an argument. And how many letters to remove. Let's give his number.

Syntax: =MID(TEXT,START NUMBER , END NUMBER)
Example:- mid(“SUPER COMPUTER”,6,7)
Result:- COMPUTER

Date functions in Excel

DATE:-

1. NOW (): -  It gives the current date and time of the Function Computer.

=NOW()
OUT PUT- 10/20/2012 19:16

2. DAY (): -  This function removes the day from DATE.

Syntax:- day(date)
Example=day(22/7/2016)
Output=20

3. MONTH (): -  This function extracts the month from DATE.

Syntax:- month(date)
Example= month (10/20/2011)
Output=10

4. year (): -  This function extracts the year from DATE.

Syntax:- year(date)
Example= year (10/20/2011)
Output= 2011

5. Today (): -  It gives the function current date output.

=today()
Output:- 10/20/2011

6. Date (): -  This function converts the given numbers into dates.

Syntax:- date(year,month,day)
Example:- date(2011,22,10)
Output= 22/10/211

Time Functions in Excel

1. Time (): - This function changes the given hour, minute, second into time.

Syntax:- Time(hour,minute,second)
Example:- Time(4,30,10)
Output:- 4:30 AM

2. Second (): -  This function returns the output from the given time in seconds.

Syntax:- Second(Time)
Example:- Second(4:30)
Output:- 10

3. Minute (): -  This function returns the minutes output from the given time.

Syntax:- minute(Time)
Example:- minute (4:30)
Output:- 30

4. Hour (): -  This function gives the hour output from the given time.

Syntax:- hour(Time)
Example:- hour(4:30)
Output:- 4

Chart in Excel

The chart option is used to display Excel data in a graph. The data is easy to understand from the chart. In Excel it is created by the wizard.
Chart Making in Excel: - In this, the chart is made in four steps. Which is as follows.

Step First

Insert menu → Chart

Or

Standard Tool Bar → Click on Chart Button

On clicking the chart button, a dialog box named chart wizard appears. In this, we select the type of chart and click on the Next button. There are fourteen types of charts in Excel.

Step Second

In the second step, we select the database for the chart. This dialog box consists of two tables. In the first table, give the data range and select it. Whether the data is in row or in the column belongs to the second tab series. In this, we give the name of the series and its range. A new series can be added to it. And pre-existing series can be deleted. And the data to be displayed on the X Axis. Let's give her a range and click on Next Button.

Step Third

This dialog box has six tabs. The table with which the chart settings are done
is the following.
1. Titles: - In this, the title of the chart gives the title of X and Y Axis.
2. Axes: From this table, determine whether to display labels on X and Y Axes in the chart.
3. Grid lines: - In this table Grid lines are determined in the chart.
4. Legend: - In this table, the position of the legend in the chart is determined that the legend has to show in the chart.
5. Data label: - What to display in the chart as a label. Let us choose it.
6. Data Table: - Whether or not to show the table of data of the chart, it is set.
Similarly, after making all the settings, click on the Next button.

Step Fourth

In this step it is determined. Where to locate the chart on the current seat or the new seat. After this, the chart is created as soon as you click on Finish Button. After this, right-clicking on it can be done.

Data Type in Excel

Many types of data can be filled in an Excel worksheet. To fill a particular type of data in a cell of a worksheet, we have to format that cell to store and display that data before formatting the cells. It is necessary to understand the data type properly.

To set the data type, one has to select Row Column or Aria. After this the data type has to be set. There are twelve types of data types in Excel. But some of those data types are more important, mainly the following data types are used in Excel which is as follows.

General

This data type is used to store all types of data. This is a common data type.
Such as - A to Z letter, 0 to 9 number, symbol, date, time etc.

Number

 This data type is used only to set digits (from 0 to 9), decimal points.

Currency

This data type is used to change the currency-related format in which decimal digits and currency symbols have to be set.
Like - Rs. 15,000, $ 500, Rs. 1500.00, Rs. 15000

Date

This data type is used to change the format related to the date in which the format of the date is to be selected.
Such as - dd / mm / yyyy, mm / dd / yyyy, yyyy / dd / mm, 23-July-2016, 23 july.

Time

 This data type is used to change the format related to time, in which the format of the time is to be selected.

Percentage

This data type is used to change the format related to Percentage. It also has to set decimal points. It has a Percentage symbol.

Scientific

Number data type is stored in this data type. It has to set decimal digits.

Text

This data type is used to change the format related to TEX, in which mathematical calculations cannot be done.

Special

This data type is used to store special types of data. Zip codes and phone numbers etc. are stored.

Accounting

This data type is used to store data related to the account. In this, the decimal points have to be set and the currency symbol has to be selected.

Custom

In this data type, you can set the type of data as required.

Use of Data menu in MS Excel

Sort option

In MS Excel the Sort option is used to arrange the data of a particular column in an ascending or descending order.

1.      Ascending: - In this, data is sorted in A To Z order.

2.      Descending: - In this  , data is sorted in Z To A sequence.

Filter

This option is used to find data. There are two sub-options inside it.
(1) Auto Filter
(2) Advance Filter

Auto Filter: - Using  this option, Combo Box comes in all the Header Column. In which searching tools are found. Out of that, the user selects the search tool according to his needs.

Advance Filter: - By this option, the data from the list is searched according to the condition and displayed in another place. There are three types of Range used in it.
(1) List Range
(2) Criteria Range
(3) Output Range

(1) List Range: - This is the range. Records are searched from where.
(2) Criteria Range: - This is the range. Where condition is given According to this condition, data is filtered from the list range.
(3) Output Range: - This is the range. Where the output is displayed. Which are filtered by the list range according to the range criteria range. All those records are printed in this range.

Practically Approach:-
1. prepare a data list
2. copy header row
3 . Paste it twice at different location

a. first for criteria range
b. second for output range

4 set the condition in criteria range for filtering data
5 set the cell pointer at first cell of list range
6 select advanced filter option from filter them it  display a dialog box tell criteria range ,output range and  press ok button alter that you will see the filtered records in output range.

Form

User interface is created with the form option. With which the data is organized. The first cell pointer is placed in the first cell to form. And then this option is clicked.

Subtotal

This option is used there. Where there are many records with one name. And it is related to financial activities. For example, many salesmen in a company have to sell many items in different places. So there is a need to extract the total and grand total of every salesman. For this, we first sort the record in ascending order. After that, select the list and click on this option.

Validation

By this option, the rules of work methodology are established inside the seat. Like if we give salary between 5000 and 10000 to the employees of our company, then we want that if the salary column is less than 5000 and not more than 10000 from the Entry user, then we will put validation in the salary column. .

Table

This option is used there. Where you want to know the financial results, such as taking a loan from the bank, in how many months, at what rate, how much installment will have to be paid. For this, let's make a table and see.

Consolidation

This option is used there. Where the total or average of the value of two or more Locations is to be derived.

Pivot Table

The summery report of the data sheet is prepared by this option. In which one can get specific values ​​column & row wise total and grand total.

Concepts of Workbook & Worksheet

Workbook

This is an Excel file. In which there are many work seats. In which data is stored. A workbook contains 256 worksites. Y defaults are three work seats. New work seat can be added or deleted. Can be renamed and in this, the work of copying the seat etc. can be done easily. When the workbook is open, the work site opens automatically. Only one workbook can be worked on at a time. Which is called active work seat.

Worksheet

The worksite is like a page in a book. In which we can store data. A work seat has 65536 rows and 256 columns. And 65536 * 256 = 16777216 is a cell. Each call has a name. Which is defined by alphabet. This range is from A to IV = 256. And row is defined by numeric number. Its range is from 1 to 65536. It can be renamed.

Cell

A cell is formed by the joining of Roe and Colum. A work seat consists of 65536 * 256 = 16777216 cells. Data is written to the cell. 255 characters can be written in a cell. The name of the cell is formed by combining the name of Colum and Row. This is the address of the cell. The two cell ads together form the range ads. In this, two or more cells can be merged and the cell's formatting can also be done.

Average Function in MS Excel

Average, this formula is used to find the average in MS Excel, it is very easy, just like you use the Average Formula in Math, similarly the average can be extracted in MS Excel as well. Auto Average can also be calculated.

First let us see what is the Formula of Average in math’s -

Average = sum of numbers on list ÷ amount of numbers in list

That is, the sum of the numbers given in the list is 6.

For example - If an average of 2, 4, 6, 8 is to be obtained

2+4+6+8 ÷ 4 = 5

Now similarly we extract the average in MS Excel -

Average Extract Formula in MS Excel is 

= AVERAGE(number1,number2,…)

For example - If you have to calculate the average of numbers from cell e1 to cell e7, then you have to type this formula -

=AVERAGE(e1:e7)

In this formula, the number written in bracket is your cell range. Formula The second way to calculate the average of these numbers in MS Excel can also be done by first summing these numbers and then dividing them by 4.

Formula for adding numbers in Excel

How to use the sum formula in Excel is a very useful and simple formula –

First of all, see Image 1.1, where column & Raw is shown, it consists of a cell, before using formulas in MS excel, remember that the formula is always applied to the cell, whatever the number written in that cell is. Can.

If you want to use the sum formula in MS excel, then pay attention to the number written in the cell, not the cell -

For example - if you want to connect cells from b1 to b9 then type in b10 or any other cell.

=SUM(b1:b9)

See image 1.2 -

ou can also select individual cells as per your choice - like

= sum (b1 + b2 + b6) Here we have left the few cells and sum the rest of the cells.

Formula Operator in Excel

What is Formula Operator?

he operator is used to formulate the formula. The operator is used between two or more numbers that indicate what is to be the reaction between these numbers, i.e. the numbers have to be added, subtracted, multiplied, divided. To give or to compare, for example 26 + 78 we have used the '+' operator between two numbers telling us that these two numbers have to be added.

Types of Operator in Excel :-

1. Arithmetic
2. Comparison
3. Text operator
4. Reference operator

Mostly we use arithmetic operator and the rest operator is used in special work.

1. Arithmetic operator

Arithmetic operator is used to do mathematical work which is as follows
+ add
_ subtract
* multiply
/ divide
% percent

They can be used at the right place in the formula to do any calculation between two or more numbers, they have the same effect according to the laws of mathematics.

2. Logical operator

These operators are used to compare between two values ​​such as
= equal
> big
> = big or equal
<small
<= small or equal
<> not equal
These operators are commonly used with the logical function in Excel goes

3. Text operator

In Excel there is only one operator "&" which is used to add text. For example if cell B4 is written "Aero" and cell B5 is written "plane" then you can use the formula = B4 & b5 You can get "Areophane" written at the same time.

4. Reference operator

In Excel the reference operator is used to refer to a cell. The most common operator is the (: colon). It is used to denote a range. For example, the range A4: D5 means cells A4 to D5. All the cells that fall between it will be included.

How to move Cell and sheet in Excel

To input or edit data in Excel's worksheet, you need to move from one cell to another, the following method can easily go to any cell:

Moving the screen through the keyboard in MS Excel    -

Command	Work
Right arrow key	One column goes right
Left arrow Key	One column goes left
Up arrow Key	Go a cry
Down arrow Key	Come down a cry
Ctrl + up key	Data filled go to the top row first
Ctrl + down key	Data filled go to the first row at the bottom
Ctrl + Right Key	Go to the first full meal on the right
Ctrl + left Key	Go to the first filled food on the left
Home	To go to the starting row
End	To set End Mode
End + Enter	To go to the last cell
PgUp	One screen to go up
PgDn	To come down one screen
Ctrl + Home	To go to the first cell of the top row
Ctrl + End	To get to the bottom cell of a spreadsheet
Alt + PgUp	One screen to go left
Alt + PgDn	One screen to go right
Tab	To proceed to next sale
Shift + tab	To return to the first cell

Mouse to move the screen

You can move the worksheet very easily by moving the Tab key on the scroll bar in the required direction. Like - Left, right, Up, Down

If you want to move a row or column, click once on the scroll bar and if you want to move multiple rows or columns at once, hold and drag the tab with the mouse. The worksheet goes in the same direction in which the tab is dragged.

Move to a certain cell

To go to a distant cell in a worksheet, use the Go To command in the Edit menu. To use this method, the address of the cell you want to move to should be moved to a certain cell by the following method. Can:

1. On clicking Go to option in the Edit menu, Dialog box of Go to will appear on the screen.

1. Click Ok or press Enter.

The cell pointer will come to the specified cell address.

Moving from one worksheet to another in a workbook

In Excel, a workbook contains many workbooks that can exchange data from one another, thus using Ctrl + PgUp to go to another worksheet and Ctrl + PgDn key to return.

How to Open file in Excel (How to open file in Excel)

You have already saved the Excel worksheet to disk as a file. Suppose the file is in a folder named My Document, then you can open it as follows:

• Click on the File menu.

Select the Open option from here.

• In the box in look in, select the drive and folder that contains the file, for example, the file is in My Document.
• Press the Tab key and go to the box named File name and select or type the file name.
• Click on the Ok button. Now the file will appear on the screen.

How to save worksheet in Excel

How to save worksheet in excel (how to save worksheet in excel)

If you have already saved the worksheet, you can save it again by pressing Ctrl + S, but if you are going to save the worksheet for the first time, then follow the manual method:

• Click on File Menu

On selecting the Save option, the Save as dialog box will appear on the screen.

• The box with Save as shows the drive and folder in which to save the file now. If you want to save the worksheet in another drive and folder, select it from Save.
• After that, after pressing the Tab key, come to the box named File name and type a small name for the worksheet file that resembles or remembers the Matter written in that file.
• Highlight the Microsoft Excel Workbook in the box with Save as type.
• Then click on the save button.

This way the worksheet will be saved to disk as a file. This file can be used any time by opening it again.

How to Print Worksheet in Excel

Before printing in Excel, select the print area, you will select the area to be printed in the excel sheet, if you are not satisfied with the print area, you can also change it.

• Clicking on the File menu and clicking on the Page setup option, a dialog box of Page setup will appear.

On clicking the Sheet option in the above dialog box, the following dialog box will appear on the screen.

• Determine the section of the worksheet to be printed in the print area.
• After that, clicking on the Ok button, the selected range will be set in the Print area.

View the sheet before it is printed

If you can see the Print Preview on the screen before taking the print of the worksheet, then the problem of removing the wrong print can be avoided.

• Click on File menu
• By choosing the Print Preview option, your page will appear on the screen.
• Click on the Zoom button to see the location of the page clearly.
• If you want to print after viewing the preview, click on the print button.

Start Printing

After making all the settings, follow the procedure to start printing -

• Click on the File menu.
• Select print option
• Select the printer name in the Printer name option
• If you want to print all the pages in the page range, then you have to print all or a particular page, then type the number of those pages from from to in front of the page option.
• In the Copies option, type the number of copies to be printed.
• Clicking on the Properties option will open a dialog box where you can set Page Size, Portrait, Landscape etc.
• Then click on Ok button
• If you want to see what the worksheet will look like after printing, click on the Preview button.
• On clicking the Print button, the worksheet will start printing.

Features of MS Excel

Graphical user interface

Excel is a software based on the GUI (Graphical user Interface) principle, that means information is exchanged between the user and the software through images, so that the software can be easily worked on.

Automatic Recalculation

If changes are made to the value of a formula in a table created in Excel, Excel automatically recalculates and updates the result.

Use of Functions

Excel has a number of pre-built programs to perform many functions which when used in calculation avoids the lengthy process of calculating the user and soon results are obtained in various types of functions - mathematical, numerical, Financial, logical etc.

Formatting

Excel provides formatting to attract workbook. Excel also allows to insert text in various formats along with Numbers, date, time etc.

Database

The data available in Excel can be stored and controlled properly, the user can create reports according to the user according to the Sort, filter.

Creating Graph

In order to display the data effectively in Excel, the data can be displayed as a graph so that they can be analyzed properly. Charts make data easy to read and understand Excel has many types of charts like - Column, Bar, Line, Pie, XY Scatter, Doughnut, Surface, Bubble, Stock etc.

Editing

Once the data is typed in an Excel worksheet, improvements can be made under the correction to type new data, delete old data or make changes to it.

Saving and Printing

All worksheets created in Excel can be stored as files in secondary storage for future and hard copy of worksheet can also be obtained by printer.

Data editing in worksheet

The data interchanged in MS Excel can be changed as required, in which the data can be changed in four ways.

• During entry (while entering)
• After Entry (After Entry)
• Change Entry (Change Entry)
• Delete Entry

During entry

When we enter a cell, it is displayed in the formula, so when the entry is miss-typed, we can delete it again with the help of Backspace and make the correct entry again.

After Entry

If we have entered once and if we need to change it, we first click on the cell to be modified, after that the old entry can be changed by pressing the F2 key or by double clicking on the cell with the mouse. Older entries can be converted to cells.

Change Entry

If we have made a wrong entry in a cell, then this entire entry can be changed for this by moving the cell pointer to that cell - typing new entry from the board deletes the old one.

Delete Entry

If the entry of a cell is to be deleted, move the pointer to that cell, press the Del Key, or right mouse click on the cell and click on Clear content or delete.

How to change Column Width in Excel

If any data that has been input is larger than the cell, then the data in that cell is shown as "########", thus inputting data larger than the width limit in that cell. Can

After placing the data in a cell, according to it, you can make the cell smaller or smaller.

• Select the cell whose width is to be changed.
• Click on the Column option in the Format menu.
• click on the Width located in the sub menu of the column.
• Type the width in column width as the number of columns required to place the letter.

• lick on the Ok button.

Change the Column Width by Mouse

It is very easy to change the width of columns by mouse. The methods are as follows -

1. Point the margin to the right of the Column Heading with the mouse, as a result the pointer will look like a two-faced arrow.
2. As the column moves the arrow pointer forward or backward by the mouse, the column width will increase or decrease.

What is Chart in excel

In the form of data tables which are interchanged in Excel, we can display it in the form of charts, data in the form of charts becomes effective, interesting and easy to understand, making it easier to analyze and compare data.

There are two types of charts -

• Embedded chart
• Chart sheet

1. Embedded Chart

These charts are linked in the worksheet and can be moved, copy resize like any other graphical object. Its main advantage is that it can be viewed with data and many charts can be inserted into it.

2. Chart sheet

When a chart is created, there are different chart sheets on it. Only one chart is available in it. For this, choose the option Insert chart as new sheet.

Excel allows us to create two Dimensional and three Dimensional charts.

Types of Chart

There are 14 types of charts available in Excel, the main ones are -

1. Column Chart

This chart is made up of a series of vertical columns that show the comparison of two or more related items.

2. Bar Chart

These charts are made up of a series of bars that display two or more related items.

3. Line Chart

This chart displays each data series by different colors and lines of shading.

4. Pie Chart

This chart shows the relative percentage of each data sum of the data series.

5. XY Scatter Chart

In this chart, the values ​​of the data series represent the intersection of the X-axis and Y-axis.

6. Area Chart

This chart shows the detail of the change. It is a chart of the line of a stack. The area between the lines is filled with color and shedding. All series remain one above the other.

7. Doughnut Chart

This chart is just like pie chart but this chart shows more than one data series.

8. Radar Chart

This chart shows the values ​​of the data in relation to the center point and each point. All data series are connected by data lines.

Formatting Cell in Excel

In MS Excel, no data is always visible in a cell the way it is typed. The format of that cell depends on the type of data filled in it, such as-

General

This is the normal and default format. It does not have any special format for numbers. Text Data is shown from the right side but the numbers are shown according to their nature. Initially when you create a new worksheet, all the cells are in General Format. Format is done later you can format it in any cell or range.

Number

In this, the numbers are shown as simple decimal numbers which can also have 1 decimal point or symbol. In this format, you can decide the number of places to be shown after that decimal point.

Date

In this, the numbers are shown as a date by assuming the serial number of days, 1 January 2019 is considered as the first day and after that the serial number of each day is decided if you change a cell to the date format then the numbers To be shown as Date, there are many formats available to show the date in Excel, out of which you can choose any format of your choice, like on March 30, 2019, you can choose many forms Can show and as 03/30/2019, 30/03/2019, 30 march 2019, etc. |

Time

In this, the numbers are shown as time. For time you can select any of the many available formats. This format is used as the date format.

Text

In this format, all types of data are displayed as normal text. If a number is filled in a formatted cell in the text, it is shown exactly as it is typed.

Formatting the worksheet

There are many such features available in Excel to format the worksheet in many ways, by which your work will be more beautiful and effective.

Changing font, size and style

In Excel, the font, size and style of data in a selected Rang can be changed in the same way as you are converted to Word, you can use the buttons in the formatting tool bar or use the keyboard shortcut key Can do

Before applying any font, you should select the cell in which you want to format, each cell is independent in itself and the format of one cell has no effect on the other cell. Can also be done on cello.

To change the font of a cell or range of data, first select that cell or range, but select the appropriate font from the font drop-down list box in the Formatting tool bar. Similarly, to select font size drop the font size Select the appropriate font size from the down list box. Similarly, you can use the bold, italic, underline buttons of the formatting toolbar to change the style of the data of a selected range.

Adding borders in cells

You can also draw up-down, right-left lines of cells to highlight a part of a worksheet, to make it stand out from other parts, or just to enhance the beauty of a table created in a worksheet. For this task we have Buttons available in the Formatting toolbar. Let's use this method as follows

1. First select the cell or range in which you want to place the border.

2. After that select the arrow of the Border button in the formatting tool bar, this will open the border palette on your screen, in which all the available buttons are given.

3. Now select the style you want to select from the given Border style.

Select the cell whose border you want to open, open this dialog box and click on the Border tab sheet. This tab sheet also has the facility of drawing diagonal lines with many types of borders.

Click the button of the border you want to put in the selected range, its effect will be visible in the middle box, after clicking a button again, its effect is eliminated, thus you can create the desired border. No, from the Style list box, you can also select the size and thickness of those borders, the border you have created appears in the middle box, then click on the OK button. Sen sex He will be applied in the cell were selected Style |

How to use PROPER function in AMS Excel.What is the PROPER function? 

The use of the PROPER function in Microsoft Excel capitalises the first letter of the word (Word) located in the cell and sets the rest of the word in lower case. Numbers and punctuation are not affected.

The PROPER function in Excel is used to convert your input text into Proper Case. It can be used to capitalise each word in a given string. The PROPER function in Excel does not affect the number and punctuation marks in the given text or string. It will only change the first character to upper case and all other letters to lower case.

Syntax

=PROPER (text)

How to use PROPER function in AMS Excel. (How to use PROPER Function in MS Excel)

The PROPER function in Excel is very simple and easy to use. In the example below you can see some subject names are given but the first letter of each subject is not capitalised. Now if you want to capitalise the first letter of each subject in the column then you can use the PROPER function. So in this post you will learn how to capitalize the first letter of a word in Excel-

• Subject Name A is in column, so we will write our formula in column B. In cell B2, we will type a formula that tells Excel to capitalize the name in cell A2, the formula will look like this:

=PROPER(A2)

• Now after writing this formula, press Enter and as soon as you press inter, you will see that the first letter of the first subject's name has been capitalized.
• Now similarly if you want to capitalize the first letter of the name of the remaining subject, then click on B2 cell.
• After this, move the mouse pointer on the box that appears in the corner, as soon as you move the pointer to the choker box, you will see that your mouse pointer will turn into a plus sign.
• After that hold down the mouse pointer and drag it to the B5 cell and release the mouse.
• Now you will see that the first letter of the name of all the subjects has been capitalized.

Some useful things related to Proper Function -

• The PROPER function only affects the first character of each word in a text string. Does not change all other characters.
• It capitalizes the first letter of any word that follows a non-text character. For example: = PROPER (hello, excel) Result Hello, Excel
• Numbers, special characters, and punctuation are not changed by the PROPER function.
• If you use a Null character, it will return only a Null character.

How to use PMT function in Excel

PMT Function

The PMT function in Excel is a financial function that returns periodic payments for loans. You can use the NPER function to find the payment for a loan, the loan amount, the number of periods, and the interest rate.

The PMT function in Excel calculates payments for loans based on constant payments and constant interest rates.

Whenever you want to know about the actual monthly payment by comparing any loan term and interest rate, then use the PMT formula for this. The PMT function calculates the payment of a loan that has a constant payment and a constant interest rate.

Syntax

=PMT (rate, nper, pv, [fv], [type])

Arguments

rate - The interest rate for a loan.

nper - The total number of payments for a loan.

pv - the current value, or now the total value of all loan payments.

fv - (Optional) Cash balance you want. 0 to default

type - (optional) when payments are due. 0 = end of period, 1 = beginning of period. The default is 0.

Example : Calculate Payment on Personal Loan

By using PMT function, you can know how much you have to pay for the specific interest rate and the installment for the loan term.

For example, if you are taking 10,000 loans for 24 months with an annual interest rate of 8 months, then PMT can tell you what your monthly payment is.

D3 has the formula -

= PMT (C3 / 12, B3, -A3)

As you can see, when you can compare multiple loan terms at once, it brings out some realities.

Remember some things about PMT-

• #NUM! Error - When does it occur:

o    The given rate value is less than or equal to -1.

o    The value of a given nipper is equal to 0.

• #VALUE! Error - Occurs when the arguments provided are non numeric.
• When calculating monthly or quarterly payments, we need to change the number of annual interest rates or periods to months or quarters.
• If we want to find out the total amount paid for the term of the loan, we need to multiply the PMT by NPR.

How to use FV function in Excel

FV Function – (Future Value)

The FV function in Excel is a financial function that returns the future value of an investment. You can, from time to time, use the FV function to obtain the future value of an investment of constant payment with a constant interest rate.

As a financial analyst, the FV function helps to calculate the future value of an investment made by a business, with a constant interest rate that pays out continuously over time. It is useful in evaluating low-risk investments such as certificates of deposit or fixed rate annuities with low interest rates. It can also be used in relation to the interest paid on a loan.

Do you want to invest your money in Fix of Deposit (FD)? So this formula can make your work easier. With the help of this formula, you can compare the interest rate of different banks to see how much money you will get after one term.

Excel's FV function is a financial function that shows the future value of an investment. You can use the FV function to get the future value of an investment assuming a constant payment, along with the constant interest rate. The objective is to get the feature value of the investment.

Syntax

=FV (rate, nper, pmt, [pv], [type])

Arguments

rate - loan interest rate

nper - Number of payments (or investment period in months)

pmt - Paid in each period. (Usually monthly (| (This number must be entered negatively.)

pv - (optional) If the current initial balance is not, it is assumed to be zero. Must be entered as a negative number.

type - (optional) when payments are due. 0 = end of period, 1 = beginning of period. The default is 0.

= FV (A3 / 12, B3, -C3, D3)

Things to keep in mind-

1. Units for rate and NPR should be consistent. For example, if you make monthly payments on a four-year loan at 12 percent annual interest, then 12% / 12 (annual rate / 12 = monthly interest rate) for the rate and 4 * 12 for the nper (48 payments total. ). If you make annual payments on the same loan, use 12% (annual interest) for the rate and 4 (4 payments total) for the nper.
2.  #VALUE! Error - Occurs when any number of the given arguments is non-numeric.

How to use RRI function in Excel

RRI is a new function that was introduced in Excel 2013. It returns the same interest rate for an investment increase. The number of periods, Present Value and Future Value are required to determine this.

 

If you have cash, which you want to increase in future, then you can see in Excel what the interest required for it should be. With the help of Excel's RRI function, you can calculate the interest rate.

 

 

 

Note: The   RRI function is only available in Excel 2013 and beyond.

 

Suppose you have 10,000 rupees as on date, which you want to increase to 25,000 after 5 years. So how much rate of interest will you need for this?

 

Syntax

RRI calculates the given interest rate using NPR (number of periods), NP (present value), and FV (future value), the following equation -

RRI (nper, pv, fv)

 

Arguments

nper: period of investment

pv: current value of investment.

fv: Future value of the investment.

Formula in cell B5 will be like this.

= RRI (B2, B3, B4)

The result of this formula is quarterly rate. When it is multiplied by 4 to translate into an annual rate, the answer is 0.2011x 4 = .08 or about 8%.

How to use VLOOKUP Function in excel

VLOOKUP is one of the most useful and important functions in Microsoft Excel. It is typically used to visualize a particular value in large data sheets where it is difficult to manually search. The VLOOKUP function supports approximate and exact matches, and wildcards.

Meaning of VLOOKUP Function

"V" means "vertical". The word "Vertical" means that it can be used to view values ​​vertically, so it can be used to view values ​​within a column.

Definition of Excel VLOOKUP

According to Microsoft Excel, VLOOKUP can be defined as a function "that looks for a value in the left column of a table and then returns that value in the same row from the column you specify."

Note: The table's data must be sorted in ascending order when using the VLOOKUP function.

Syntax of VLOOKUP Function (Syntax of VLOOKUP Function)

The syntax of VLOOKUP consists of four types of information -

=VLOOKUP (value, table, col index, [range_ lookup])

This syntax contains Arguments like this -

Value - The value of the first column of the table to be searched.

Table - The table from which to get this value.

Col_index - The column of the table or range from which to derive values.

It has two options which are optional -

TRUE = Approximate Match and FALSE = Exact Match | If you do not specify anything, TRUE will be the default.

How to open a macro

• Open the View tab. On the right side of the ribbon you will find the Macro button.
• Click on the arrow shown below the Macro button. It will display three options; view macros, record macro and use relative references:

o    View Macro: Clicking on the Macro option opens the Macro dialog box where you can run, edit or delete the macro.

o    Record Macro: Click this option to record Macro. When clicked, it displays the Record Macro dialog box where you can fill in the details of your Macro.

o    Use Relative Reference: This option helps record Macro in relative mode. Using relative mode you can play the recorded data anywhere on the worksheet.

Steps to record a macro

• Click the Record Macro option. This will display the Macro dialog box.
• Macro name - Fill in the name of the macro you want to give.
• Shortcut Key - Fill in which shortcut key you want to use to run the macro.
• Description - Fill Macro's details and then click on Ok.

Now do the task you want to record. When the task is completed, click on the arrow under the Macro button, it will display three options, select Stop Recording from these options.

• our Macro is ready and it is ready to use. Click the Macro button to run the Macro. You can also click the View Macro option; It will display the dialog box with many options like run, edit and delete. Click on the Run option, it will display the recorded task.

How to split names in Excel with Text to Columns

Excel is a very useful software. Through Excel, we can make our work easy like in a column of your worksheet the names of some people have their first name and last name and you can separate the first and last name separately. Want to divide into columns. In Excel, this task can be accomplished in a few different ways - Text to Columns feature, formulas, and Split Names tool |

In cases when you have a column of names of the same pattern, for example only First and last name, or First, Middle and last name, the easiest way to divide them into separate columns is:

• Select the columns of Full Name that you want to separate.
• Go to Data tab> Data Tools group and click on Text to Columns.

On the first step to do the Convert Text to Columns Wizard, select the Delimited option and click Next

• Next select one or more delimiters and click on Next.

In our example, different parts of names are separated with Space, so we choose this Delimited. The Data preview section shows that all our names are correct.

Finally, you select the data format and destination, and click on Finish.