Learn Microsoft Advance Excel

Microsoft Advance Excel

Topic:-

1. Formats
2. V Lookup, H lookup
3. Filtering
4. Goal seak
5. Data validation
6. Protection
7. pivot table

Group:- For organizing the data sorting and grouping is used. In sorting we can use multiple columns to sort.
Data and Home Tab → sort → sort by → type column name.
Sort on → values
order  A-Z and  Z-A.

To do sort on multiple columns click on add level and continue the previous steps.

Grouping:- Creating a bundle of similar data group wise or  row or column wise.
Data → Group → Select rows or columns.

Data Validation:- To check the entered data or to allow valid data into columns we use data validating feacture.

Steps:- For already entered data.

1. Select the data where you want to apply the validation.

Data→  Data tools → Data validation → Select the data validation.

2. Select setting tab → allows → whole number → data → greater than → maximum → enter 100.

3. Select input message.

• Enter title and error message.
• Select error alert.
• Select style.
• Enter data into Title and error message.

To apply data validation rule select the data to go data validation. Select circle invalid data and rule will be applied.

To remove the rule go to data validation, select clear validation.

Second way of doing data validation:- Is through conditional formating.
Select the data go to home tab, select style`s group from there click on conditional formating and select heighlight cells.

❋ A define name can be use to describe the meaning or content of a cell or range of cells.
❋ These names are used in calculations instead of cell address or reference.
❋ It is present in formula tab defined names group.
❋ Two steps are involved in this operation.
Step:-1 Defining the names → Select range of cells to which you want to give name.
Formula → Define names → Define value → Enter details → Ok.
Step 2:- To apply the defined names .
Formula → Defined names → Use in Formula → Select the name and press enter.

Using built in functions:- Sum, Max, Min, Average, Count, Concatenate, If and , V Lookup.
If:- If (and(C4 < 5000, D4 → 2000),"ok", "Not ok").

Count:- Count is used to count cells with numbers.
Count A:- Count A used to count the cells with words.

Programming basic calculations (Count d):- ✱ The v lookup function searches the first column of a range of cells, and then return value from any cell on same row of the range.

✱ Syntax of the vlookup funtion:-✱ Vlookup (Lookup - Value, Table - array, Col - index - number)
✱ The arrangements of the vlookup functions are:-
1. Lookup value:- Is a numbers, a text string, or a cell reference to searched in the first column of a range of cells.

2. Table array:- Is the cell reference or the range name of the entire range of data.

3. Col - index - number:- Is the column from which the results are required.

4. Range lookup:- Specifies whether you what an exact or an approximate match.

Financial functions:-
PMT        ⇾Monthly installments.
PV           ⇾Present value.
FV           ⇾Future value.
NPER      ⇾No. of months (or) No. of periods.

✱ PMT:- Funtion is used to calculate monthly payment to be made for a loan or for an investment.
✱ Syntax:- PMT (Rate, NPER, PV, [FV], [Type])
✱ Rate of interest per month.
✱ NPER:- Total no. of payment for a loan (or) for an investment.
✱ PV:- It is present value of a loan or investment.
✱ FV:- It is the future value of a loan or investment.
✱ Type:- It indicates the time when the payments are due. It can can be zero or one. One means beginning of the month. Zero means end of the month.
✱ PMT example:-
Amount of loan          3,20,000.00
No. of periods             144 (12 years)
Rate of interest           7% per year
                = PMT (7/2, 144,3,20,000.00
✱ PV is present value use to calculate present value of an investment.
         =PMT (Rate, NPER, PV, [FV], [Type])
Ratio is rate of interest,
No. of period
Monthly installment
Future value
Indicates the payment due whether it is end of the month or beginning of the month.

Monthly you receivable      1000
No. of period is                    5
Rate of interest                    8%
                                      =PV (8, 5, 1000)

To summaries the data excel provides two tools:- 1. Chat
                                                                                    2. Pivot table✱ Charts are used to summaries the data at macro level.✱ To summaries the data at macro level,that in detailed analyse of data we use pivot table.

Customizing the chat allowed by using chat tools design, chat tolls layout, and chat tools format.

Chat tools ⇾ 1. Type
                       2. Data
                       3. Chat layout
                       4. Chat style
                       5. Move locations

Chart tools layout:- 1. Current selection
                                   2. Insert
                                   3. Labels
                                   4. Axes
                                   5. Background
                                   6. Anlyise
                                   7. Properties

Format:-   1. Shapes styles
                   2. Current selection
                   3. Word art Style
                   4. Arrange
                   5. Size

Populating the data from various source in to excel:-

• This can be done with the help of importing feature of excel.
• To get this feature go to data tab and click on get external data group.
• We can import from a coma separated or space separated text file or from a web.

There are two ways to import data from a text file:- 1. Open text file in worksheet.
                                                                                         2. Import text file as an external data range
Microsoft excel provides features of transferring data and graphs from a worksheet to web, email, PDF files, and a power-point slides.

When you export to power-point slides first we have to copy the data from excel then paste to power-point using following past options.
1. Use destination style.
    paste as per the style of the power-point.
2. Keep source formatting.
    Paste the data by keeping excel formatting as it is on to power -point presentation.
3. Embed.
    Past the data worksheet as an image of table.
4. Picture.
    Paste the data of the worksheet as an non-editable image of the table.
5. Keep text only.
    When this option is selected all the formatting will be removed from the data.

Protecting the workbook and sheet:-✱ Go to review tab select worksheet provide password and conform password.✱ Sheet can also be protected from the home tab under cells group use format option.✱ To protect entire workbook go to backstage view, under info select protect workbook with password.