Data Ribbon Tab

Introduction:

The “Data” ribbon tab in the Excel application of Microsoft 365 is one of the main tabs located at the top of the Excel window. The “Data” tab provides various tools and options for managing and manipulating data within your Excel worksheets. It includes features related to importing and exporting data, sorting and filtering data, data analysis, data validation, and more.

Here are some common groups of commands you’ll find on the “Data” tab:

1.Get and Transform Data
2.Queries & Connections
3.Data Types
4.Sort & Filter
5.Data Tools
6.Forecast
7.Outline

A. Get and Transform Data

Get & Transform Data: This group contains tools for importing data from external sources, such as databases, websites, and other files. You can use Power Query to connect to and transform data before it is loaded into your workbook.

There are 7 command buttons in this group to connect to and transform data in the spreadsheet.

1. Get Data
   1. From File – you can get from files such as CSV, Excel, XML, JSON, etc.
   2. From Database – Import data from SQL databases.
   3. From Azure – Import data from Azure products such as SQL databases and other data sources.
   4. From Online Services – you can collect data from online services such as Salesforce, SharePoint, Microsoft Exchange, etc.
   5. From Other Sources – Data can also be imported from sources such as ODBC connectors, Hadoop, Web, Microsoft query wizard, etc.
   6. Combined Queries – You can merge or append queries from this workbook or launch the power query editor.
2. From Text/CSV – you can import data from text files or delimited files such as CSV (comma Separated values) or TSV (Tab Separated Values) file types.
3. From Web – Import data or a table from the webpage.
4. From Table/Range – create a new query from the selected table, named range, or array from this workbook.
5. From Picture – imports a picture such as a JPEG or PNG file.
6. Recent Sources – shows you the list of recently used data sources.
7. Existing connections – You can get a list of existing connections to ODBC or other data sources.

B.  Queries & Connections

Queries and Connections: The “Connections” group allows you to manage data connections to the external data sources. You can refresh data from connections and modify their properties.

1. Refresh All
   1. Refresh All – Get the latest data by refreshing all data sources.
   2. Refresh – Refresh a connection.
   3. Refresh Status – to know the refresh status of a data source.
   4. Cancel Refresh – Using this you can cancel a connection refresh.
   5. Connection Properties – You can edit and change the connection properties such as username, password, etc.
2. Queries & Connections – View and manage the queries and connections in this workbook.
3. Properties – Specify how cells connected to a data source will update.
4. Edit Links – Update or remove the links to other files this spreadsheet is connected to.

C.  Data Types

Data Type: This group allows you to extract certain types of data and organize it in a more structured and meaningful form. It helps you to source data related to stocks, currency, and geography.

The three functions in the Microsoft 365 Data Types group are:

1. Stocks: This command helps you to pull the stock price in a cell by using the Ticker of the companies in a specified cell.
2. Currencies: This button helps to get the currency exchange rates by using currency symbols separated by “/” or “:”. Such as EUR/USD or INR: AUD.
3. Geography – Convert cells with names of countries/regions or cities such as London to get the statistics such as population, etc.

D.  Sort & Filter

Sort & Filter: This group offers options for sorting data in ascending or descending order and applying various filtering techniques to display specific subsets of data.

1. Sort
   1. A to Z – Sorts from lowest to highest.
   2. Z to A – Sorts from Highest to lowest.
2. Filter – To turn on the filtering on selected cells or columns.
3. Clear – Clear the applied filtering.
4. Reapply – Reapply a filtering.
5. Advance – Gives you options to filter using complex criteria.

E.  Data Tools

Data Tools: The “Data Tools” group provides commands for performing data analysis tasks, such as removing duplicate data, consolidating data, and using the “What-If Analysis” tools.

1. Text to Column: Use this option to convert text separated by space or delimiter to columns or you can even define fixed spaces to convert a text of the cell to columns.
2. Flash Fill – Fills cells automatically just enter a few cells with examples you want as output.
3. Remove Duplicates: Using this option, you can remove the duplicated values from a selected table or range.
4. Data Validation
   1. Data Validation – Pick from a list of rules, the type of data you want to enter in the cells.
   2. Circle Invalid Data – circles the cells where the data is invalid e.g. in the case of numeric data validation rule you entered text data so clicking this option will circle the invalid cells.
   3. Clear Validation Circle – Using this option you can clear the invalid data circles created by the circle invalid data option above.
5. Consolidate: Summarize data from different ranges to a single range.
6. Relationship: create or edit relationships between tables of this worksheet.
7. Go to the Power Pivot Window – add and prepare data or continue working with the data in this workbook.

F.  Forecast

Forecast: The “Forecast” group allows you to create forecast sheets to analyze and predict trends in your data.

a) What if Analysis

1. 1. Scenario Manager – Create different groups of values and scenarios and switch between them.
   2. Goal Seek – Find the right input for the value you want.
   3. Data Table – You can see the results of multiple inputs at the same time.

b) Forecast Sheet – Create a new worksheet to predict data trends.

G.  Outline

Outline: This group includes options for creating and managing outlines to group and summarize data in your worksheet.

a) Group

1. 1. Group – Group rows or columns and create an automatic outline.
   2. Auto Outline – Creates automatic outlines of groups.

b) Ungroup

1. 1. Ungroup – Ungroup is a range of cells that were previously grouped.
   2. Clear Outline – This is used to clear the previously created outlines.

c) Subtotal – quickly create rows of data for subtotals and totals.

The “Data” tab is particularly useful when you need to work with large sets of data, perform data analysis, import data from external sources, and ensure data accuracy and consistency. It provides a comprehensive set of tools to help you manage and analyze your data effectively in Excel.