Data Tab

Get External Data

This group was removed in the 2017 March Update.

Get & Transform Data

In Excel 2013 and 2010 this functionality was made available by installing the Power Query add-in.

Get Data - (Changed in 2017 March). Drop-Down. Discover, connect and combine data from multiple sources. The drop-down includes the commands: From File, From Database, From Azure, From Online Services (New), From Other Sources, Combine Queries, Launch Query Editor (New), Data Source Settings and Query Options.
From Text/CSV - (Added in 2017 March). Import data from a text, comma-separated value or formatted text (space delimited) file.
From Web - (Added in 2017 March). Import data from a web page.
From Table/Range - (Renamed in 2017 March). Create a new query linked to the selected table. If the selected range is not part of a table, it will be converted into a table.
Recent Sources - Manage and connect to recent sources.
Existing Connections - (Added in 2017). Import data from common sources.

Queries & Connections

Refresh All - (Ctrl + Alt + F5). Button with Drop-Down. The button updates all the information in the active workbook coming in from external sources. The drop down includes the commands: Refresh, Refresh Status, Cancel Refresh and Connection Properties.
Queries & Connections - (Renamed in 2017 March). Displays the "Workbook Connections" dialog box. Data connections are links to data sources outside of this workbook.
Properties - This is only enabled when you select a cell that has been populated using an external connection.
Edit Links - Displays the "Edit Links" dialog box.

Sort & Filter

Sort A to Z - Sorts data alphabetically from A-Z and numerically from the lowest number to the highest.
Sort Z to A - Sorts data alphabetically from Z-A and numerically from the highest number to the lowest.
Sort - Displays the "Sort" dialog box.
Filter - Toggles filtering on the current selection.
Clear - Clears the filter and sort for the current selection.
Reapply - (Ctrl + Alt + L). Reapplies the filter and sort to the current selection.
Advanced - Displays the "Advanced Filter" dialog box.

Data Tools

Text to Columns - Displays the "Convert Text to Columns Wizard" dialog box.
Flash Fill - (Added in 2013). Automatically fills in values. more details
Remove Duplicates - Displays the "Remove Duplicates" dialog box. more details
Data Validation - Button with Drop-Down. The button displays the "Data Validation" dialog box. The drop-down includes the commands: Circle Invalid Data and Clear Validation Circles. more details
Consolidate - Displays the "Consolidate" dialog box. more details
What-If Analysis - (Moved in 2016 to Forecast group).
Relationships - (Added in 2013). Allows you to create or edit relationships between tables to show related data from different tables on the same report.
Manage Data Model - (Added in 2016). Opens the Power Pivot window. You must enable the Data Analysis add-ins to enable this feature. more details


What-If Analysis - (Moved in 2016). Drop-Down. The drop-down contains the commands: Scenario Manager, Goal Seek and Data Table. more details
Forecast Sheet - (Added in 2013). Displays the "Create Forecast Worksheet" dialog box that lets you forecast your timeline data. more details


You can quickly display the "Settings" dialog box, by clicking on the dialog box launcher in the bottom right corner of this group.

Group - Button with Drop-Down. The button lets you group a selection of rows and columns. The drop-down also contains the commands: Group and Auto Outline. The Auto Outline command creates an automatic outline from the current region.
Ungroup - Button with Drop-Down. The button lets you remove the outlines from the current rows and columns. The drop-down contains the commands: Ungroup and Clear Outline. The Clear Outline command clears all the levels of outline.
Subtotal - Automatically insert subtotals for the selected rows.
Show Detail - Expand a collapsed group of cells.
Hide Detail - Collapse a group of cells.


This group will be only be displayed if you have either the Analysis-ToolPak add-in or the Solver add-in loaded.

Solver - This is only displayed if you have the Solver add-in loaded. Displays the "Solver Parameters" dialog box.
Data Analysis - This is only displayed if you have the Analysis-ToolPak add-in loaded. Displays the "Data Analysis" dialog box.

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext