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:
SUM Function: Adds up all the numbers in a given range.
Formula:
=SUM(A1:A10)
AVERAGE Function: Calculates the average (arithmetic mean) of a range of numbers.
Formula:
=AVERAGE(A1:A10)
COUNT Function: Counts the number of numeric entries in a range.
Formula:
=COUNT(A1:A10)
COUNTA Function: Counts the number of non-empty cells (both numbers and text).
Formula:
=COUNTA(A1:A10)
IF Function: Performs a logical test and returns different values based on the condition.
Formula:
=IF(A1>50, "Pass", "Fail")
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])
HLOOKUP Function: Similar to VLOOKUP but works with horizontal data.
Formula:
=HLOOKUP(lookup_value, table_array, row_index, [range_lookup])
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)
CONCATENATE (or CONCAT) Function: Joins multiple text strings into one.
Formula:
=CONCATENATE(A1, " ", B1)Newer version:
=CONCAT(A1, " ", B1)
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:
Font Formatting:
Change font style, size, color
Bold (
Ctrl+B), Italic (Ctrl+I), Underline (Ctrl+U)
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.
Conditional Formatting:
Highlights cells based on conditions (e.g., highlight values above 50 in red).
Access via:
Home -> Conditional Formatting
Borders and Fill Colors:
Apply borders (
Ctrl+Shift+7) and background colors to improve visibility.
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:
Sorting by Single Column:
Select the column.
Go to
Data -> Sort -> Choose Ascending (A-Z) or Descending (Z-A).
Sorting by Multiple Columns:
Click
Sortin the Data tab.Add levels to sort by multiple columns (e.g., first by Name, then by Age).
Sorting by Color or Icon:
If conditional formatting is applied, sort data based on cell color or icon set.
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:
Basic Filter:
Select the dataset.
Click
Data -> Filter.Dropdown arrows appear in column headers; select criteria to filter.
Custom Filters:
Filter by conditions like “greater than,” “less than,” or “contains.”
Access:
Data -> Filter -> Text/Number Filters.
Advanced Filters:
Apply multiple conditions using the
Advanced Filteroption.Access:
Data -> Advanced Filter.
Clear Filter:
Remove all filters by clicking
Clearin the Filter menu.
Basics of Formula/Functions.
Excel formulas help automate calculations and data manipulation. Basic concepts include:
Formula Structure:
All formulas start with
=.Example:
=A1+B1adds two cells.
Using Cell References:
Relative Reference:
=A1+B1(Changes when copied).Absolute Reference:
=$A$1+$B$1(Does not change when copied).
Common Operators:
Addition (
+), Subtraction (-), Multiplication (*), Division (/)Example:
=(A1+B1)*C1
Error Handling Functions:
IFERROR(value, alternate_value): Handles errors in formulas.ISERROR(value): Checks if a value is an error.
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:
Select the data range (including headers).
Click
Insert -> Tableor pressCtrl + T.Ensure the “My table has headers” checkbox is checked (if applicable).
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 Stylesto 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-ZorSort Z-Afor sorting.Choose
Filterto display specific data based on criteria.Use
Clear Filterto 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
Yesto confirm.
8. Removing a Table in Excel
To delete a table:
Select the entire table.
Press
DeleteorBackspaceto clear the data.To remove the formatting, go to
Table Design -> Clear.