SALIENT THINGS YOU SHOULD KNOW ABOUT EXCEL

CREATING LINKS:
A workbook can be linked to permit consolidation of values from supporting workbooks to a master workbook. The source workbook contains the original data. The dependent workbook is then updated whenever data in the source workbook is changed provided both the source and dependent workbooks are simt’taneously opened.
Creating Workbook Links:
To place linked copy of cell from one Worksheet to other.

1. Make sure that both the source and destination workbooks are open.
2. To select the cell or range of cells which you wish to link in the source workbook.
3. Choose copy from Menu or press right mouse button on the selection.
4. Go to destination Worksheet and select the cell which will be upper left corner of the range of cells that will contain the linked cells.
5. Choose Paste link from edit drop-down Menu. Remember any change made in source workbook will be reflected in the destination.
6. To make successful, link save the source workbook before and rename linked workbook.

Updating Links:
When the dependent workbook containing the formulae with links has been saved, a permanent link to the source workbook is established. In other words, providing the source and the dependent workbooks are open simultaneously any changes In the source data will immediately take effect in the dependent worksheets. Removing Links:
When a link is no longer needed, for example, if no further changes in the data will occur, the user can freeze the current value in the dependent file and remove link.

EXCEL DATABASE MANAGEMENT
Microsoft, excel has many advanced features that allows you to interact with the worksheet with ease. One of the important features of excel is its database utility, a database is nothing more than a collection of organized information, such as employee, customer, inventory records. A record corresponds to a worksheet row. And each record contains a collection of data that corresponds to columns. The row and columns that contain data can be organized and queried upon.
A LOOK ON DATA MENU
This menu is used to perform a number of powerful operations. Lets have a look at its contents.
it sorts out range of cells in ascending or descending order.
it is used to display print only those records tat match the criteria. This Ability performed by Auto Filter.
it gives you the ability to display the information that matches specified Value and use button to find, change, delete selected records.
it is used to display the field of each record.
it is used to have calculations on selected numbers.
it creates a table of the database.
it breaks the text information into column wise.
it combines values of multiple worksheet into selected cells. This can be Used for finding sums ofmore worksheets.
it provides a detailed information such as hide, expand, combine or group, ungroup outline and use of style.
it summarizes information within a worksheet.
it sets properties for fields within a table.
it refresh values within the selected worksheet.
DATA TABLE
Excel lets you create different kinds of tables that are used in business and finance. It is based on a formula that contains one or more variables. A table is a range of cells within a worksheet that contains input values, formulae and the result of calculation.
Generally there are two types of data table:

1. One Variable

It operates with formulae having single variable.

2. Two variable

It operates with a formula having two variables.
DATA SORT
Excel lets you arrange the database with more than one sort keys in either the ascending or descending order, it sort a database.
Select all the data that you want to sort excluding field names with the help of mouse.
Click on Sort option from the Data menu.
Click on now or column of sort by option in which the output is to be organized Specify the cell address to identify the column to sort by when sorting rows or columns and specify to sort the database in             ascending or descending order.
Specify the cell address in 2^nd key if there are any values duplicated in the 1^st key.
Similarly specify 3^rd key if there are any duplicate in the second key range.
Click OK button to perform the desired output.
DATA QUERY
To query the database, excel database lets you extract selected information of a database for reporting purpose. To perform data query follow the following steps.
Select the range of cells that database content and column headings,
Select Set Database option from Data Menu and then choose Form.
To add a record, click on new form From Menu to display a blank form to enter the new records and enter the values in             the fields.
Once the data entry is completed press <Enter> to add the records at the end of the database. Choose criteria to specify           a condition for query. Find prev., next to view the records step by step.
Delete to delete the currently displayed record and the same cannot be restored.
MACRO
A Macro is noting but a collection of keystrokes that is created and saved in an assigned control key. It is just like a “batch file” in DOS, that contains a series of commands to be executed. Macro is used t guide the data entry process. Macros can carry out sequence of actions much more quickly than done by the user himself.
Generally there are two types of data table:

1. One Variable

It operates with formulae having single variable.

2. Two variable

It operates with a formula having two variables.
DATA SORT
Excel lets you arrange the database with more than one sort keys in either the ascending or descending order, it sort a database.
Select all the data that you want to sort excluding field names with the help of mouse.
Click on Sort option from the Data menu.
Click on now or column of sort by option in which the output is to be organized Specify the cell address to identify the column to sort by when sorting rows or columns and specify to sort the database in             ascending or descending order.
Specify the cell address in 2^nd key if there are any values duplicated in the 1^st key.
Similarly specify 3^rd key if there are any duplicate in the second key range.
Click OK button to perform the desired output.
DATA QUERY
To query the database, excel database lets you extract selected information of a database for reporting purpose. To perform data query follow the following steps.
Select the range of cells that database content and column headings,
Select Set Database option from Data Menu and then choose Form.
To add a record, click on new form From Menu to display a blank form to enter the new records and enter the values in             the fields.
Once the data entry is completed press <Enter> to add the records at the end of the database. Choose criteria to specify           a condition for query. Find prev., next to view the records step by step.
Delete to delete the currently displayed record and the same cannot be restored.
MACRO
A Macro is noting but a collection of keystrokes that is created and saved in an assigned control key. It is just like a “batch file” in DOS, that contains a series of commands to be executed. Macro is used t guide the data entry process. Macros can carry out sequence of actions much more quickly than done by the user
himself.

EXCEL DATABASE MANAGEMENT
Microsoft, excel has many advanced features that allows you to interact with the worksheet with ease. One of the important features of excel is its database utility, a database is nothing more than a collection of organized information, such as employee, customer, inventory records. A record corresponds to a worksheet row. And each record contains a collection of data that corresponds to columns. The row and columns that contain data can be organized and queried upon.
A LOOK ON DATA MENU
This menu is used to perform a number of powerful operations. Lets have a look at its contents.
it sorts out range of cells in ascending or descending order.
it is used to display print only those records tat match the criteria. This Ability performed by Auto Filter.
it gives you the ability to display the information that matches specified Value and use button to find, change, delete selected records.
it is used to display the field of each record.
it is used to have calculations on selected numbers.
it creates a table of the database.
it breaks the text information into column wise.
it combines values of multiple worksheet into selected cells. This can be Used for finding sums ofmore worksheets.
it provides a detailed information such as hide, expand, combine or group, ungroup outline and use of style.
it summarizes information within a worksheet.
it sets properties for fields within a table.
it refresh values within the selected worksheet.
DATA TABLE
Excel lets you create different kinds of tables that are used in business and finance. It is based on a formula that contains one or more variables. A table is a range of cells within a worksheet that contains input values, formulae and the result of calculation.
How to create, store and run Macros

1. Excel has the capability of recording whatever the user does on an excel sheets and to store these steps on a macro sheet. Choose tools-record macro.
2. and record new macro dialog box asks you for a macro name. And option available in this menu lets you record macro and store in three different ways.
3. personal macro workbook:
4. this workbook:
5. new workbook:

The name of the macro :

1. must begin with a letter.
2. maximum 255 characters in length.
3. Can contain a combination of letters, numbers.
4. Cab be either uppercase or lowercase.

When you will finish recording click Stop Recording button on the toolbar.
Choose Tools-Macro. In the dialog box, choose the macro name, which you want to run and then dick on RUN.
AUTOMATIC MACRO
Special macros can be created that can run automatically when the workbook is opened or closed. Automatic macros can be created by recording macros in the workbook using special reversed names viz. Auto Open
If the macro is named auto open in a workbook, it runs automatically when the workbook is opened.

KEYBOARD IN MS EXCEL
Shortcut Key	Description
Right Arrow Left arrow Up arrow Down arrow Home PgUp Pgdown	Moves cell pointer towards right by one cell Moves cell pointer towards left by one cell Moves cell pointer towards up by one cell Moves cell pointer towards down by one cell Moves cell pointer towards first column of the row Moves one screen up by one window height Moves one screen down by one window height

Alt + PgUp	Moves one screen left by one window width
Alt + PgDn	Moves one screen right by one window width
Ctrl + Right Arrow	Moves the cell pointer to the farthest cell of the consecutively occupied cell on right
End + Right Arrow
Ctrl + Left Arrow	Moves the cell pointer to the farthest cell of the consecutively occupied cell on left
End + Left Arrow
Ctrl + Down Arrow	Moves the cell pointer to the farthest cell of the consecutively occupied cell in the
End + Down Arrow	Columns downwards
Ctrl + Up Arrow	Moves the cell pointer to the farthest cell of the consecutively occupied cell in the
End + Up Arrow	columns upwards
Ctrl + Home	Moves Cell Pointer to A1
Ctrl + End
End + Home	Moves cell pointer to the lowest and rightmost occupied cell.
Ctrl + PgUP	Moves to previous worksheet.
Ctrl + PgDown	Moves the next Worksheet
Ctrl + Spacebar
Click on column No. Shift + Spacebar	Select entire column.
Click on row no.	Select entire row
Ctrl + Shift + Spaceb Hold down Ctrl Key	r Select entire Worksheet
While Selecting	Select Random Cells

CELL REFERENCES

Cell Contents	Copied Cell	Description
$G$8	$G$8	Absolute Reference
G$8	H$8	Mixed Reference
SGS8	$G9	Mixed Reference
G8	H9	Relative Reference