Data tab in Excel

Data tab in Excel : Click the Data tab in Excel and you will find the buttons needed to import data from access, web, text file and filtered the data in a Excel worksheet.

There are five groups appear on the Data tab :

1. Get External Data Group
2. Connections Group
3. Sort & Filter Group
4. Data Tools Group
5. Outline Group

Get External Data Group

This group of tools is used to import external database to the Excel sheet for the data to be used like MS Access, Text file, from Web, from some other sources or existing connections.

Connection Group

This group of tools is used to establish or validate the connections with other databases used the excel file.

Refresh All (Ctrl+Alt+F5)

By this command, you can get the latest data by refreshing all sources in the workbook. This command has following options :

• Refresh All
• Refresh
• Refresh Status
• Cancel Refresh

Connections

This option displays all data connections for the workbook. Data connections are links to data outside of this workbook which can be updated if the source data changes.

Properties

This option specify how cells are connected to a data source will update, what contents of the source will be display and how changes in the number of the rows and columns in the data source will be handled in the workbook.

Edit Links

View all of the other files this spreadsheet is linked to so that you can update or remove the links.

Sort & Filter Group

Sort & Filter group is used for arrange the cell entries either ascending or descending order or filtered data from Excel worksheet.

Sort A to Z

This option sorts data from lowest to highest order.

Sort Z to A

This option sorts data from highest to lowest order.

Sort

This is used to arrange the record of Excel either in ascending or descending order. When sorting data in Microsoft Excel it is very important to decide whether you want just part of the data sorted or the whole dataset.

Filter (Ctrl+Shift+L)

The filter feature applies the drop down menu to each column heading, allowing you to select specific choices to narrow a table.

Steps to Filtering Data :

1. Select the range of data to filter or select a cell within the range.
2. Go to Data tab and select the Filter tool from Sort & Filter group. Filter buttons that look like arrow appear in the first cell of each field header.
3. Click the filter button for the column to filter and click the check boxes of values to display.

Clear

This option clear the filter and sort state for the current range of data.

Reapply (Ctrl+Alt+L)

This option reapply the filter and sort on the current range so that changes you have made are included.

Advanced

This command has options for filtering using complex criteria.

Data Tools Group

Data Tools group is used for separate the contents of one Excel cell into separate columns, remove duplicate rows values, validate data entry, and combine values from multiple range into one new range.

Text to Columns

This option splits a single column of text into multiple columns. For example, you can separate a column of full names into separate first and last name columns.

Flash Fill (Ctrl+E)

This option automatically fill in values. Enter of couple of examples you want as output and keep the active cell in the column you want filled in.

Remove Duplicates

This option delete duplicate rows from a sheet.

Data Validation

Data validation is used to restrict the cell entries within a specified range. It define the type of contents that can be entered in that cell. It provides help message that define the content rules you have set up for the cell and blocks the invalid data. 

To specify the data validation settings, do this:

1. Select a cell or range of cells for which you want to validate data.
2. Click on the Data tab, and then select the Data Validation.
3. In the Data Validation dialog box, click the Setting tab property sheet.
4. Select the type of data to be validated or checked from the Allow drop down list.
5. Select an operator for validation from the Data drop down list.
6. Enter the appropriate values in Minimum and Maximum collapsible box.

Consolidate 

This option summarise data from separate ranges, consolidating the results in a single output range.

What If Analysis

By using this command, you can try out various values for the formulas in a sheet using Scenerio Manager, Goal Seek and Data Table.

Scenario Manager

By using this option, you can create different groups of values or scenario and switch between them.

Goal Seek

Goal Seek is a function to adjust cell value, so a calculation will have a desired outcome. With Goal Seek, you specify the value you want a formula to calculate and then Excel changes the data in the formulas cell reference to tell you what values in need to achieve that goal.

To use Goal Seek feature, do this :

1. Under the Data tab in Excel, in the Data Tool group, click on What If Analysis and then click on the Goal Seek.
2. In Set cell box, enter the reference for the cell that contains a formula.
3. In To value box, type the new value you want in above cell reference.
4. In By changing cell box, enter the reference for the cell whose value you want to adjust.
5. Click OK.

Now it will show the status of Goal Seek.

Data Table 

By using this option, you can see the results of multiple inputs at the samne time.

Relationships

By using this option, you can create or edit relationships between tables to show related data from different tables on the same report.

Outline Group

Outline group is used for group or ungroup the rows and columns, total several rows of related data together by automatically inserting subtotals and total for the selected cells.

Group (Shift+Alt+Right)

By using this option, you can group rows and Columns or automatically create an outline. This command has two options: 

• Group
• Auto Outline

Ungroup (Shift+Alt+Left)

By using this option, you can ungroup a range of rows and columns. This command has two options :

• Ungroup 
• Clear Outline

Subtotal

By using this option, you can quickly calculate rows of related data by inserting subtotals and totals.

Show Detail

This option expand a collapsed group of cells.

Hide Detail

This option collapse a group of cells.

You can also join us at QUORA and PINTEREST