Using the Data Tab in Excel 2007
Tip: If you are unsure what the function of a feature is, let your cursor hover over the button (in Excel) to see a pop-up box explaining the feature. If you want to collapse the ribbon so that none of the buttons are displayed, double-click the name of the tab.
Get External Data 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.
Connections 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.
Sort & Filter 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.
Data Tools 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.
Outline 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.In the Table below, select which section of the Data tab you would use to perform the stated action. [This quiz has not been completely written yet.]