MS Excel - Data Tab
Data Tab -
Through this tab, with the
help of MS Excel, Data can be arranged through Sort, Filter, Advance Filter,
Text to Columns, Remove Duplicates Options. The shortcut key to access this tab
is Alt+A.
Data Tab is mainly divided into 5 Groups -
Ø Get External Data Group
Ø Connections Group
Ø Sort & Filter Group
Ø Data Tools Group
Ø Outline Group
1.
Get External Data Group – Through this option, you
can import data from Access, Web, Document (Text), From other Sources, Existing
Connectins in MS Excel.
2.
Connections Group – This option is used for
connection management in MS Excel.
3.
Sorting:
Ø Let's
select that data range, which we want to sort.
Ø From the
Data menu, click on the sort option from the sort & filter group.
Ø Select the
name of that field in the Sort by box. Which you want to sort and select the
Ascending or Descending option.
Ø Let's click
on the Ok button.
4. Filter: This option
is used to find a subset of data (according to the condition) record from a
data list, it is a fast method to find the record. A filter data displays the
same rows for which you define the column criteria. MS Excel provides commands
to filter the data.
Auto Filter: It filters data by
simple selection.
Advanced Filter: It is defined for
complex criteria.
Auto Filter: This
is a fast way of selecting items from the data list which we want to filter.
Following are the steps for this &
Ø Select the
name of the field of the data base.
Ø Select the
filter option from the sort & filter group from the Data menu.
Ø When we use
the auto filter option, then the down arrows button is applied to the right of
the field name.
Ø For data
filtering, click on the down arrow and select related data.
Ø Through
custom auto filter, that row can be displayed in which more than one condition
has to be defined.
Advanced Filter: Advanced filter command can filter the list in any
location like auto filter command. But it does not display the drop down list
for the column. To apply Advance Auto Filter, follow the following steps -
Ø
Let's type the Criteria
range.
Ø
Select Advance Filter from sort &
filter group from Data menu.
Ø
Ø
In this dialog box, in the list range,
we select or enter that range of cells which we want to filter.
Ø
In Action, to hide the row which does
not match the specified criteria, select Filter the list, in-place and to
filter the filter list to another location, select Copy to another location and
address Let's select.
Ø Let's click on the Ok button.
Data Tools Group
-
Text to Column
This option is used to convert any
text into a column.
Data Tab → Data
Tools Group → Text to Column
Remove Dulplicate
This option is used to remove
duplicate data from the selected data.
Data Tab → Data
Tools Group → Remove Duplicate
Data Validation
This option is used to create rules
for the data to be entered. Like we want that only numbers between 18 to 35
should be entered in a column, apart from this no other number should be
entered.
Ø Select the area in which you
want to add validation.
Ø By clicking on the Data menu
and selecting the validation option, the Validation dialog box opens.
Ø This dialog box contains 3
tab buttons.
Settings:
Select the Comparison operator and select
the reference or data of the selected cell in the allow box.
Input Message:
In the Show input message when cell is
selected option, select the check box and type its title and input message
which we want to display.
Error Alert:
In this, we put a check mark on the show
error alert and select the title and style for the error alert and type the
error message which we want to display, we can give up to 255 characters for
the error message.
Consolidate
This option is used to summarize the
data selected from different locations.
What if Analysis
Scenario Management, Goal Seek, Data
Table can be used in this.
Comments
Post a Comment