Data Mangement - Excel

Categories

  • Sorting

    • Single Column Sort

    • Multiple Column Sort

  • Filtering

    • Applying Filters

    • Using Filters

  • Data Validation

    • Setting Up Data Validation

    • Defining Validation Criteria

    • Input Message and Error Alert

Sorting

Sorting is a method to organize data in a specific order. You can sort data alphabetically, numerically, or by date, either in ascending or descending order.

- Single Column Sort -

  • Select a cell in the column you want to sort.

  • Go to the Data tab.

  • Click on the Sort A to Z button for ascending order or Sort Z to A for descending order.

- Multiple Column Sort -

  • Select any cell within the data range.

  • Go to the Data tab and click on Sort.

  • In the Sort dialog box, choose the column you want to sort by, select the sort order (A to Z, Z to A, etc.), and add levels to sort by additional columns if necessary.

Example:

Suppose you have the following data:

Name Score
Alice 85
Bob 90
Charlie 78

To sort by scores in descending order:

  • Select the data range.

  • Go to Data > Sort.

  • Choose "Score" and sort "Largest to Smallest".

Result:

Name Score
Bob 90
Alice 85
Charlie 78

Filtering

Filtering allows you to display only the rows that meet certain criteria while hiding others. This is useful for analyzing subsets of data.

- Applying Filters -

  • Select the data range.

  • Go to the Data tab and click on Filter. Filter arrows will appear in the header row of your data.

- Using Filters -

  • Select the data range.

  • Go to the Data tab and click on Filter. Filter arrows will appear in the header row of your data.

Example:

Suppose you have the following data:

Name Score
Alice 85
Bob 90
Charlie 78

To display only scores greater than 80:

  • Apply filters.

  • Click the drop-down arrow in the "Score" header.

  • Choose Number Filters > Greater Than..., and enter 80.

Result:

Name Score
Alice 85
Bob 90

Data Validation

Data validation is a feature that helps control the type of data or the values that users enter into a cell. This ensures data accuracy and consistency.

- Setting Up Data Validation -

  • Select the cells where you want to apply data validation.

  • Go to the Data tab and click on Data Validation.

- Defining Validation Criteria -

In the Data Validation dialog box, under the Settings tab, choose the validation criteria:

  • Whole Number: Restrict entry to whole numbers within specified limits.

  • Decimal: Restrict entry to decimal numbers within specified limits.

  • List: Allow entry only from a predefined list of items.

  • Date: Restrict entry to dates within a specified range.

  • Text Length: Restrict the number of characters in the cell.

  • Custom: Use a custom formula to set validation criteria.

- Input Message and Error Alert -

  • Input Message: Display a message when the cell is selected, guiding the user on what to enter.

  • Error Alert: Display an error message when invalid data is entered.

Example:

To restrict entries to a list of predefined values:

  • Select the cell range (e.g., B1).

  • Go to Data > Data Validation.

  • In the Settings tab, choose List.

  • In the Source field, enter the values separated by commas (e.g., Yes,No).

Result:

Users can only enter "Yes" or "No" in the selected cells.