Day-2 Basic Formatting , Formula , Function and Sorting and Filtering in Excel.

Day-2 Basic Formatting , Formula , Function and Sorting and Filtering in Excel.

Basic Functions of Excel

Microsoft Excel provides a wide range of functions to simplify data management, calculations, and analysis. Some of the most commonly used functions include:
  1. SUM Function: Adds up all the numbers in a given range.
    • Formula: =SUM(A1:A10)
  2. AVERAGE Function: Calculates the average (arithmetic mean) of a range of numbers.
    • Formula: =AVERAGE(A1:A10)
  3. COUNT Function: Counts the number of numeric entries in a range.
    • Formula: =COUNT(A1:A10)
  4. COUNTA Function: Counts the number of non-empty cells (both numbers and text).
    • Formula: =COUNTA(A1:A10)
  5. IF Function: Performs a logical test and returns different values based on the condition.
    • Formula: =IF(A1>50, "Pass", "Fail")
  6. VLOOKUP Function: Searches for a value in a column and returns a corresponding value from another column.
    • Formula: =VLOOKUP(lookup_value, table_array, col_index, [range_lookup])
  7. HLOOKUP Function: Similar to VLOOKUP but works with horizontal data.
    • Formula: =HLOOKUP(lookup_value, table_array, row_index, [range_lookup])
  8. LEFT, RIGHT, MID Functions: Extract portions of text from a cell.
    • LEFT Formula: =LEFT(A1, 5) (Extracts first 5 characters)
    • RIGHT Formula: =RIGHT(A1, 3) (Extracts last 3 characters)
    • MID Formula: =MID(A1, 3, 4) (Extracts 4 characters starting from the 3rd character)
  9. CONCATENATE (or CONCAT) Function: Joins multiple text strings into one.
    • Formula: =CONCATENATE(A1, " ", B1)
    • Newer version: =CONCAT(A1, " ", B1)
  10. LEN Function: Returns the number of characters in a text string.
    • Formula: =LEN(A1)

Basics of Formatting in Excel

Formatting in Excel improves the readability of data and enhances presentation. Key formatting options include:
  1. Font Formatting:
    • Change font style, size, color
    • Bold (Ctrl+B), Italic (Ctrl+I), Underline (Ctrl+U)
  2. Cell Formatting:
    • Merge & Center: Combines multiple cells into one.
    • Wrap Text: Ensures content fits within a cell.
    • Number Formatting: Choose between General, Currency, Percentage, Date, and more.
  3. Conditional Formatting:
    • Highlights cells based on conditions (e.g., highlight values above 50 in red).
    • Access via: Home -> Conditional Formatting
  4. Borders and Fill Colors:
    • Apply borders (Ctrl+Shift+7) and background colors to improve visibility.
  5. Alignment Options:
    • Horizontal and vertical text alignment.
    • Text rotation and indentation.

Sorting Data in Excel – Different Methods to Sort

Sorting in Excel helps organize data in ascending or descending order. Methods include:
  1. Sorting by Single Column:
    • Select the column.
    • Go to Data -> Sort -> Choose Ascending (A-Z) or Descending (Z-A).
  2. Sorting by Multiple Columns:
    • Click Sort in the Data tab.
    • Add levels to sort by multiple columns (e.g., first by Name, then by Age).
  3. Sorting by Color or Icon:
    • If conditional formatting is applied, sort data based on cell color or icon set.
  4. Custom Sorting:
    • Create a custom list (e.g., sorting months in January, February order instead of alphabetical order).
    • Access: Sort -> Order -> Custom List

Filtering Data in Excel

Filtering helps display specific data while hiding the rest. Steps to apply filters:
  1. Basic Filter:
    • Select the dataset.
    • Click Data -> Filter.
    • Dropdown arrows appear in column headers; select criteria to filter.
  2. Custom Filters:
    • Filter by conditions like “greater than,” “less than,” or “contains.”
    • Access: Data -> Filter -> Text/Number Filters.
  3. Advanced Filters:
    • Apply multiple conditions using the Advanced Filter option.
    • Access: Data -> Advanced Filter.
  4. Clear Filter:
    • Remove all filters by clicking Clear in the Filter menu.

Basics of Formula/Functions.

Excel formulas help automate calculations and data manipulation. Basic concepts include:
  1. Formula Structure:
    • All formulas start with =.
    • Example: =A1+B1 adds two cells.
  2. Using Cell References:
    • Relative Reference: =A1+B1 (Changes when copied).
    • Absolute Reference: =$A$1+$B$1 (Does not change when copied).
  3. Common Operators:
    • Addition (+), Subtraction (-), Multiplication (*), Division (/)
    • Example: =(A1+B1)*C1
  4. Error Handling Functions:
    • IFERROR(value, alternate_value): Handles errors in formulas.
    • ISERROR(value): Checks if a value is an error.
  5. Using Named Ranges:
    • Assign names to cell ranges (Formulas -> Name Manager) to make formulas easier to read.

Excel Tables

Tables in Excel are a powerful tool that helps organize, analyze, and manage data efficiently. Below is a detailed guide on how to use tables in Excel.

1. What is a Table in Excel?

An Excel table is a structured range of data with enhanced features like sorting, filtering, and formatting. It automatically expands as new data is added and provides built-in styles for better readability.

2. Creating a Table in Excel

To create a table:
  1. Select the data range (including headers).
  2. Click Insert -> Table or press Ctrl + T.
  3. Ensure the “My table has headers” checkbox is checked (if applicable).
  4. Click OK. The selected data is now formatted as a table.

3. Benefits of Using Tables in Excel

  • Automatic Formatting – Tables come with predefined styles for better readability.
  • Easy Sorting & Filtering – Built-in dropdown filters for quick data analysis.
  • Dynamic Range Expansion – Automatically extends when new rows or columns are added.
  • Structured References – Simplifies formulas by using column names instead of cell addresses.
  • Quick Totals – Provides an easy way to calculate sums, averages, and more.

4. Table Formatting Options

Once the table is created, you can customize it:
  • Table Styles: Go to Table Design -> Table Styles to change the look.
  • Banded Rows/Columns: Alternate row colors improve readability.
  • Header Row: Keeps column names visible for easy reference.
  • Total Row: Add quick calculations like Sum, Count, or Average.

5. Sorting & Filtering Data in a Table

Excel tables automatically include sorting and filtering options:
  • Click on any column header dropdown.
  • Select Sort A-Z or Sort Z-A for sorting.
  • Choose Filter to display specific data based on criteria.
  • Use Clear Filter to remove applied filters.

6. Using Formulas in Excel Tables

Tables support structured references, which make formulas easier to read:
  • Instead of =A2+B2, use =[Column1] + [Column2].
  • Formulas automatically adjust when new data is added.

7. Converting a Table Back to a Normal Range

If you want to remove the table format while keeping the data:
  • Click anywhere inside the table.
  • Go to Table Design -> Convert to Range.
  • Click Yes to confirm.
8. Removing a Table in Excel
To delete a table:
  • Select the entire table.
  • Press Delete or Backspace to clear the data.
  • To remove the formatting, go to Table Design -> Clear.

Leave a Reply

Your email address will not be published. Required fields are marked *