MS-Excel (Data Menu)

DATA TAB IN EXCEL

Get External Data: This option is used to access data from different types of other program's files.

1. From Access: This option used to access data from an already existing access file.

2. From Web: This option is used to access data from the web (Internet)

3. From Text: This option is used to access data from an already existing notepad file.

4. From other sources: This option is used to access data from a SQL Database, XML file and from other Database programs.


Existing Connections: This option is used to connect excel workbook with an external data source by selecting from a list of commonly used sources.


Refresh All: This option is used to refresh all already existing connections.


Connections: This option is used to show all already existing connections and a connection can exist with working Microsoft excel file with another program such Microsoft Access, Notepad, Web, and others.


Sort: this option is used to sort selected data either numerical or text in ascending and descending order.


Filter (Ctrl + Shift + L): This option is used to add filter button at the top of all selected columns and after adding filter buttons, we can easily filter their data items.
READ MORE>>

Clear: This tool is used to remove filter effect from the current worksheet.


Advanced Filter: This tool is used to filter selected data by complex criteria and we can also copy filtered data in another location by using it.
READ MORE>>

Text To Columns: This option is used to separate the contents of one excel cell into separate columns, for example, you can separate a column of full names into separate first and last name columns.


Remove Duplicates: This option is used to remove duplicate values or text from selected column.


Data Validation: This tool is used to prevent invalid data from being entered into a cell, for example, you could reject invalid dates or numbers greater than 100.


Consolidate: This option is used to combine values from multiple ranges (often from different worksheets) into one new range.


What-If Analysis: This tool consisted of Scenario Manager, Goal Seek, Data Tables and function of these three options are described below.

1. Scenario Manager: It allows you to create and save different groups of values, or scenarios, and switch between them.

2. Goal Seek: It will find the right input when you know the result whatever you want.

3. Data Tables: It allows you to see the results of many different possible inputs at the same time.

Group (Shift + Alt + Right): This option is used to tie a range of cells together so that they can be collapsed or expanded.


Ungroup (Shift + Alt + Left): This option is used to ungroup a range of cells that were previously grouped.


Subtotal: This tool is used to total several rows of related data together by automatically inserting subtotals and totals for the selected cells.

HOME