Data Tab and its usage in Excel

What is Data Tab ? Where is it in Excel ?

        Data tab is another most important part of excel. It is between Formulas Tab and Review Tab. We use Data tab for the large amount of data.It is useful to import the data by connecting with the server, and we can import data automatically from web, MS Access etc. And sort & filter are very helpful options we have in Excel; it makes easy to read vast data.
It looks like  imagae below.

Data Tab

Data Tab is Divided in to Five Group.
1. Get External Data
2. Connections
3. Sort & Filter
4. Data Tools
5. Outline

1. Get External Data:

 In Excel, we can import data from MS Access, Web, Text and other sources. Also, we can import the data from other applications.

From Access - Import data from a Microsoft Access database.

From Web - Import data from a web page.

From Text - Import data from a text file.

From Other Sources - Import data from other data sources.

Existing Connections - Connect to an external data source by selecting from a list of commonly used sources.

2. Connections:

      It is used to display the entire data connections for the workbook. Data connections are links to the data outside the workbook which can be updated if the source data changes. And updated data can be obtained by refreshing all sources in workbook.

Refresh All - Update all the information in the workbook that is coming from a data source. The keyboard shortcut to Refresh All is Ctrl + Alt + F5 .

Connections - Display all data connections for the workbook. Data connections are links to data outside of this workbook which can be upgraded if the source data changes.

Properties - Data Range Properties - Specify how cells connected to a data source will update, what contents from the source will be displayed, and how changes in the number of rows or columns in the data source will be handled in the workbook.

Edit Links - This is used to break a link to an external reference. This command will be unavailable if the workbook does not contain linked information.

3. Sort & Filter:

To set the data in ascending or descending order on the basis of value or as per the first letter of a word, we use Sort option. Also, we can put the basic and advanced filter from here only.

Sort A to Z - Sort the selection so that the lowest values are at the top of the column.

Sort Z to A - Sort the selection so that the highest values are at the top of the column.

Sort - Launch the Sort to sort data based on several criteria at once.

Filter - Enable filtering of the selected cells. Once filtering is turned on, click on the arrow in the column header to choose a filter for the column. The keyboard shortcut to filter is Ctrl + Shift + L.

Clear - Clear the filter and sort data for the current range of data.

Reapply - Reapply the filter and sort in the current range. New or modified data in the column won't be filtered or sorted until you click Reapply. The keyboard shortcut to reapply is Ctrl + Alt + L.

Advanced - Specify complex criteria to limit which records are included in the result set of a query.

4. Data Tools:

This option is very important to make the report interactive; it helps us to make the data authentic, and using this tab, we can restrict or validate the entries if the data is being updated by multiple users. Text to Columns helps us to split the single column into multiple columns as per data. Flash fills the values in the range. We can delete duplicate rows from the data by using Remove Duplicates option. We use Data Validation to provide the list that can be entered in cell, or we can restrict the entries, or we can validate the entries in the cell. We use Consolidate option to summarize data from separate ranges, and consolidate the result in a single output range. We use What-if-Analysis to analyse the data.

Text to Columns - Separate the contents of one Excel into separate columns. For example, you can separate a column of full names into separate first and last name columns. In Word you would use this feature to convert the selected text into columns at each comma, period, or other character you specify.

Remove Duplicates - Delete duplicate rows from a sheet. You can specify which columns should be checked for duplicate information.

Data Validation - Prevent invalid data from being entered into a cell. For example, you could reject sates or numbers greater than 1000. You can also force input to be chosen from a dropdown list of values you specify.

Consolidate - Combine values from multiple ranges into one new range.

What-If Analysis - Try out various values for the formulas in the sheet. Scenario Manager allows you to create and save different groups of values, or scenarios, and switch between them. Goal Seek will find the right input when you know the result you want. Data Tables allow you to see the results of many different possible inputs at the same time.

5. Outline:

We use this option to make the data more analytical and understandable. We can make group of rows or columns or automatically create an outline. We can ungroup the data; quickly calculate rows of related data by inserting subtotals and totals. We use Show and Hide options when we want to insert the Subtotal in data.

Group - Tie a range of cells together so that they can be collapsed or expanded. The keyboard shortcut to Group is Shift + Alt + Right.

Ungroup - Ungroup a range of cells that were previously grouped. The keyboard shortcut to Ungroup is Shift + Alt + Left.

Subtotal - Total several rows of related data together by automatically inserting subtotals and totals for the selected cells.

Show Detail - Expand a collapsed group of cells.

Hide Detail - Collapse a group of cells.

Diagonal Arrow - Click on the arrow in the bottom right corner of Outline to see the Settings dialog box.
If you liked our blogs, share it with your friends on Facebook. And  Twitter. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Please Comment in on the post.