Excel Data Handling
Techniques
Formatting, Sorting, Filtering, and More
Number Formatting
Effective number formatting is crucial for data interpretation. Excel provides various options to display
numerical data in a clear and meaningful way.
General: Default format, displays numbers as entered.
Number: Allows control over decimal places, thousands separators, and negative number
display.
Currency: Adds a currency symbol and aligns numbers.
Accounting: Similar to currency but aligns currency symbols and decimal points.
Date: Displays numbers as dates in various formats.
Percentage: Multiplies the number by 100 and adds a percentage sign.
Fraction: Displays numbers as fractions.
Scientific: Displays numbers in scientific notation.
Text: Treats numbers as text, preventing calculations.
Special: Formats numbers for specific purposes like zip codes or phone numbers.
Custom: Create your own number format codes.
Steps:
1. Select the cells containing the numbers you want to format.
2. Right-click and choose "Format Cells..." or use the Number format dropdown in the Home tab.
3. Select the desired category and customize the options.
4. Click "OK" to apply the formatting.
Example: To format a cell containing the number as currency with two decimal places, select
"Currency" in the Format Cells dialog and set the decimal places to .
Sorting Data
Sorting allows you to arrange data in ascending or descending order based on one or more columns.
This helps in analyzing and organizing information efficiently.
Ascending Order: Sorts from smallest to largest (A to Z for text).
Descending Order: Sorts from largest to smallest (Z to A for text).
Steps:
1. Select the data range you want to sort. Include headers.
2. Go to the "Data" tab and click "Sort".
3. In the Sort dialog box, select the column to sort by from the "Sort by" dropdown.
4. Choose the sort order (Ascending or Descending).
5. (Optional) Add more levels for sorting by additional columns using "Add Level".
6. Click "OK" to apply the sort.
Example: To sort a list of customer names alphabetically, select the data range including the header
row, go to Data > Sort, select the "Customer Name" column, and choose "A to Z" (Ascending) order.
Custom Sort
Custom sort allows you to define a specific order for sorting data based on a predefined list. This is
useful for sorting by non-alphabetical or non-numerical criteria.
Steps:
1. Select the data range you want to sort. Include Headers.
2. Go to the "Data" tab and click "Sort".
3. In the Sort dialog box, select the column to sort by.
4. In the "Order" dropdown, select "Custom List...".
5. In the Custom Lists dialog box, either select an existing custom list or create a new one by typing
the list entries in the "List entries" box and clicking "Add".
6. Click "OK" in both dialog boxes to apply the custom sort.
Example: To sort a list of priorities (High, Medium, Low) in the correct order, create a custom list with
the order "High, Medium, Low" and then use it for sorting the "Priority" column.
Flash Fill
Flash Fill automatically fills in data by recognizing patterns in your data. It's helpful for extracting or
combining data from multiple columns.
Steps:
1. In the first column next to your data, manually enter the desired output based on the pattern you
want to create.
2. In the cell below the first entry, start typing what you want. Flash Fill will often automatically
recognize the pattern and suggest the remaining entries.
3. If Flash Fill doesn't start automatically, go to the "Data" tab and click "Flash Fill" (or press Ctrl+E).
4. Review the suggested entries and press Enter to accept them.
Example: To extract first names from a column containing full names, type the first name of the first
entry in the adjacent column, then use Flash Fill to automatically extract the remaining first names.
Text to Columns (TTC)
Text to Columns separates text in one column into multiple columns based on delimiters (e.g.,
commas, spaces) or fixed widths. This is useful for splitting combined data into individual
components.
Steps:
1. Select the column containing the text you want to split.
2. Go to the "Data" tab and click "Text to Columns".
3. Choose either "Delimited" or "Fixed width" based on how the data is separated.
Delimited: Separates data based on characters like commas, spaces, or tabs.
Fixed width: Separates data at specific character positions.
4. Follow the wizard to specify the delimiters or fixed widths.
5. Choose the data format for each resulting column (e.g., General, Text, Date).
6. Click "Finish" to apply the conversion.
Example: To split a column containing comma-separated values (e.g., "John,Doe,30") into three
columns (First Name, Last Name, Age), use the "Delimited" option and specify comma as the
delimiter.
Filtering Data
Filtering allows you to display only the rows that meet specific criteria, hiding the rest. This is useful
for focusing on relevant data subsets.
Steps:
1. Select the data range you want to filter. Include headers.
2. Go to the "Data" tab and click "Filter".
3. Filter dropdown arrows will appear in the header row. Click on the arrow in the column you want
to filter.
4. Use the filter options (e.g., Text Filters, Number Filters, Date Filters, Checkboxes) to specify your
criteria.
5. Click "OK" to apply the filter.
Example: To display only customers from a specific city, click the filter arrow in the "City" column,
select "Text Filters" > "Equals", and enter the city name.
Advanced Filtering
Advanced Filtering provides more complex filtering options, including using criteria ranges to define
multiple conditions. It also allows you to copy the filtered results to a different location.
Steps:
1. Set up a criteria range. This range should include the header row from your data and the criteria
you want to use for filtering below the headers.
2. Select a cell in the data range you want to filter.
3. Go to the "Data" tab and click "Advanced".
4. In the Advanced Filter dialog box:
Set the "List range" to your data range (including headers).
Set the "Criteria range" to your criteria range (including headers).
Choose whether to "Filter the list, in-place" or "Copy to another location". If you choose to
copy, specify the "Copy to" range.
5. Click "OK" to apply the filter.
Example: To filter a list of products to show only those with a price greater than $50 and a quantity in
stock less than 100, set up a criteria range with headers "Price" and "Quantity", and the criteria ">50"
and "<100" below the respective headers.
Summary
This document covered essential Excel data manipulation techniques including number formatting,
sorting (standard and custom), Flash Fill, Text to Columns, and filtering (basic and advanced).
Mastering these skills will enable you to effectively manage and analyze data within Excel.
