Data Tab in Microsoft Excel

Data Tab in Microsoft Excel is use for importing data from external sources, generating and refreshing data connections, sort & filtering data and many other data management tools. The ribbon of Data Tab is divided into multiple sections with the name Get External Data, Connections, Sort & Filter, Data Tools and Outline. The detailed explanation about Data Tab in Microsoft Excel is given below.

Get External Data

• This section provides multiple options to import data from external sources. We can import data on worksheet from Access, from web, from text files or from other possible external sources.

Connection

• This section is use to refresh all data sources and manage connections to outside sources.  Using this option we can edit links and check properties of the imported data source.

Sort & Filter

• Sort – We can sort a range of data with this option as alphabetical or number order. This option provides multiple options as ascending or descending sorting, sorting data with text or number conditions etc.
• Clear – This option is use to clear the filtered and sorted state for the current range of data.
• Reapply – This option is use to reapply the new filter and modified data. New or modified data won’t be filtered or sorted until you click reapply.
• Advanced – Advanced Filter can be used to extract a list from a database with predefined criteria. It gives a lot of control as compared to regular filter.

Data Tools

• Text to Column – This option is use to convert the text in to column. For example a full name “Gyanendra Prasad Tomar”  written in cell, and want to separate the each part as first name, middle name and last name; so this option can distribute each part of the name in to other columns. This option easily convert text in to columns using spaces, tabs, commas etc.
• Remove Duplicate – This option is use to delete the duplicate data in the selected range.
• Data Validation
  • Data Validation – This option prevent the invalid data being entered in the cell. We can specify different data formats with multiple conditions.
  • Circle Invalid Data – This option make circles on the invalid Data.
  • Clear Validation Circles – This options removes all the circles of invalid data.
• Consolidate : This option is use to combine and sum multiple range of multiple worksheets in to new worksheet.
• What-If Analysis
  • Scenario Manager – A scenario is a collection of values, that allows to create, analyze and compare data results in different situations. We can store multiple versions of data within the same cell, and change them depending on a scenario’s goal to see results.
  • Goal Seek – This option is use to find a future value. For example we can use Goal Seek to find what Loan amount we can take based on interest rate, period and EMI.
  • Data Table – A data table is a range of cells in which we can change values in some of the cells to calculate different answers to a problem. An example to use data table is PMT function, it can be use to calculate different EMIs using Data Table by given loan amount, Period and interest rate.

Outline

• This section provide tools for group or ungroup rows or columns, collapse and expand the grouped rows and columns, hides or unhide details and obtains total and subtotals for grouped items.