MS Excel for Data Analysis - курсы

📊 Excel Basics #33 – Sorting & Filtering Data When working with hundreds or thousands of rows, you don't want to manually search through the entire dataset. Excel's Sorting and Filtering features help you quickly organize and analyze your data. 📌 1. What is Sorting? Sorting rearranges your data based on a specific column. You can sort: • A → Z • Z → A • Smallest → Largest • Largest → Smallest • Oldest → Newest • Newest → Oldest Example: Employee| Sales Rahul| 75000 Priya| 45000 Amit| 90000 Neha| 60000 Sort Sales from Largest to Smallest: Employee| Sales Amit| 90000 Rahul| 75000 Neha| 60000 Priya| 45000 📌 2. Basic Sorting Select your dataset and go to: Data → Sort & Filter You can choose: Sort A to Z or Sort Z to A For numbers: Smallest to Largest or Largest to Smallest 📌 3. Multi-Level Sorting You can sort using multiple columns. Example: First sort by: Region → A to Z Then by: Sales → Largest to Smallest This groups employees by region and ranks sales within each region. Go to: Data → Sort Then click: Add Level 📌 4. What is Filtering? Filtering temporarily hides rows that don't meet your selected criteria. Example: Employee| Region| Sales Rahul| North| 75000 Priya| South| 45000 Amit| North| 90000 Neha| West| 60000 Filter Region to North. Excel displays only: Employee| Region| Sales Rahul| North| 75000 Amit| North| 90000 The other rows are hidden, not deleted. 📌 5. Enable Filters Select your dataset and use: Data → Filter Keyboard shortcut: Ctrl + Shift + L Small dropdown arrows will appear in the column headers. 📌 6. Filter by Number For numeric columns, you can filter using conditions such as: • Equals • Greater Than • Less Than • Between • Top 10 • Above Average • Below Average Example: Sales → Number Filters → Greater Than → 50000 Excel displays only sales above 50,000. 📌 7. Filter by Text For text columns, you can use: • Equals • Does Not Equal • Begins With • Ends With • Contains • Does Not Contain Example: Department → Text Filters → Contains → "Data" This displays rows where the department contains the word "Data". 📌 8. Filter by Date For date columns, you can filter by: • Today • Yesterday • Tomorrow • This Week • This Month • Last Month • Between two dates This is extremely useful for analyzing transactions and business activity over specific periods. 📌 Real-World Example Imagine you have 100,000 sales records. You want to find: 👉 Sales from the North region 👉 With sales greater than ₹50,000 You can apply both filters: Region = North AND Sales > ₹50,000 Excel immediately shows only the relevant records. 📌 Sorting vs Filtering Sorting → Changes the order of the rows. Filtering → Temporarily hides rows that don't match your criteria. Think: 👉 Sort = Rearrange 👉 Filter = Show only what I need 📌 Common Mistakes ❌ Sorting only one column instead of the entire dataset. ❌ Forgetting to include column headers. ❌ Assuming filtered rows are deleted. ❌ Adding filters to inconsistent or poorly structured data. ✅ Best Practices • Keep headers in the first row. • Select the complete dataset before sorting. • Convert your dataset into an Excel Table for easier filtering. • Clear filters when you're finished analyzing. • Be careful when sorting data with formulas or related columns. 💡 Double Tap ❤️ For More