Table of Contents
Data Tab in Excel
Sort A to Z
Sort is used to assign any name to A to Z and any number to 0 to 9.
- Click on the column to sort
- Data tab
- Click on Sort A to Z
Sort Z to A
Sort is used to assign any name to Z to A and any number to 9 to 0.
- Click on the column to sort
- Data tab
- Click on Sort Z to A
Sort
If you want to sort any column by value, cell color, font color and cell icon, then you can use sort.
- Click on the column to sort
- Data tab
- Click on the Sort
- Sort by (Select column name to sort)
- Sort on (Select Value, cell color, font color or cell icon)
- Order (Select order to sort A to Z or Z to A)
- Ok
Filter
Use of Filter Filtering any column, filter means to hide and show any details in that data. You can also sort any column using a filter.
- Click anywhere in the data
- Data tab
- Click Filter
- Go to the column to filter
- Click filter Button (Like ) then
- You can filter by contents
Clear
To clear the filter after applying the filter to any data.
- Click anywhere in the data
- Data tab
- click clear
Reapply
If you change anything in the data after applying the filter on any data, then you do not have to filter again. You can re-filter the same filter by clicking on the Reapply filter.
- Change anything after applying the filter on any data
- Data tab
- Click Reapply
Advanced Filter
Advanced filters are used to filter data from one sheet to another sheet, for which criteria are used.
- Give criteria on the first sheet
- Go to another sheet
- Data tab
- Click Advanced
- List Range (Select all data on first sheet)
- Criteria Range (Select the criteria on the first sheet)
- Click Copy to another location radio button
- Click Copy to box
- In the second sheet, click on the cell where the data is to be shown.
- ok
Data Tools Group
Text to Columns
Text to column is used to convert many different names or headings into one column into different columns and Used between two names or heading dot, comma, space, tab or another symbol.
- Type many different name or heading on first column
- Data tab
- Click Text to column
- Click Delimited Radio button
- Next
- Click symbol check box used between names
- Next
- Finish
Flash Fill
If you want any text and number from any column data from front and back, in other columns, you can use Flash Fill.
- Type whatever text and number you want from the first column to the second column.
- In the second column, select where to display the data.
- Data Tab
- Click Flash Fill
Remove Duplicates
Check and remove duplicate entries in any column.
- Click anywhere in the data
- Data Tab
- Remove Duplicates
- Click Unselect All Button
- Click the column check box to be check duplicate entry
- Ok
Data Validation
Using data validation to create a list of names in a single cell or by selecting any column, it contains as many numbers and text as we want. Like an equal, between, not between, and more.
- Select column or cell where you want to use data validation
- Data tab
- Data Validation
- Click Data Validation
- Go to Validation Criteria (Select Validation you want to use)
- Ok
Consolidate
Combine multiple sheet value into one new sheet.
- Go to last sheet and select first blank cell
- Data Tab
- Click Consolidate
- Click Reference box
- Go to first sheet select numerical data and click add button
- Go to step by step all sheet select numerical data and click add
- Go to last sheet and blank reference box
- Click Create links to source data check box
- Ok
What-if-Analysis
Scenario Manager
The Scenario Manager is used to add a value and change some value in it to update the previous value later.
- Select Value
- Data Tab
- What if Analysis
- Click Scenario Manager
- Click Add Button
- Type Scenario Name
- Click ok
- Again Click ok
Goal Seek
Goal Seek is used to increase or decrease the value in which the formula is applied.
- Click on the cell where the formula is placed.
- Data Tab
- What is Analysis
- Goal Seek
- Click Set Cell box and click on the cell where you insert formula To Value (Type new value)
- By Changing cell (Click cell where you want to changing)
- Ok
Data Table
After the output of any one of the things, using its output, we use the data table to see the results through multiple quantity and multiple rate of the same thing.
- First get the result of any one item
- Type Multiple Quantity in column and Rate in row Elsewhere
- Copy the result of the item and paste before multiple rate
- Select quantity and rate data with of the item
- Data tab
- What if analysis
- Data table
- Row input cell (Select item rate)
- Column input Cell (Select item quantity)
- Ok
Outline Group
Subtotal
Used subtotal to total multiple items together.
- Click on the item column
- Data tab
- Click sort A to Z
- Click subtotal (Under Outline Group)
- At each change in (choose item column)
- Use Function (Choose sum)
- Add Subtotal to (Click on the check box of the column for sum)
- Ok
Note: – You can see the values of all the columns have been totaled.
Note: – The group is already applied when you use subtotal.
Group
You can hide or show any data in row or column after using the group.
- Select the data you want to show and hide
- Data Tab
- Group
- Click Group
- Click row or column for use group
- Ok
- Use plus and negative symbol for hide or show data
Ungroup
Ungroup is used to ungrouping any selected data after applying the group.
- Select data for ungroup
- Data tab
- Ungroup
- Click Ungroup
- Ok
Show Details
Show details are used to view information about that data by putting a group on any data.
- Click on grouped data
- Data tab
- Click show Details
Hide Details
Hide details are used to hide information about that data by putting a group on any data.
- Click on grouped data
- Data tab
- Click hide Details