Post #2528

Excel Basics for Data Analytics Excel sits at the start of most analysis work. What you use Excel for • Cleaning raw data • Exploring patterns • Quick summaries for teams Core concepts you must know • Data setup – Freeze header row. View → Freeze Top Row. – Convert range to table. Ctrl + T. – Use proper headers. No merged cells. One value per cell. • Data cleaning – Remove duplicates. Data → Remove Duplicates. – Trim extra spaces. =TRIM(A2) – Convert text to numbers. =VALUE(A2) – Fix date format. Format Cells → Date. – Handle blanks. Filter blanks, fill or delete. – Find and replace. Ctrl + H. • Essential formulas – Math and counts ▪ SUM. =SUM(A2:A100) ▪ AVERAGE. =AVERAGE(A2:A100) ▪ MIN. =MIN(A2:A100) ▪ MAX. =MAX(A2:A100) ▪ COUNT. Counts numbers. ▪ COUNTA. Counts non blanks. ▪ COUNTBLANK. Counts blanks. – Conditional formulas ▪ IF. =IF(A2>5000,"High","Low") ▪ IFS. Multiple conditions. ▪ AND. =AND(A2>5000,B2="West") ▪ OR. =OR(A2>5000,A2<1000) – Lookup formulas ▪ XLOOKUP. =XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B) ▪ VLOOKUP. Old but common. ▪ INDEX + MATCH. Powerful alternative. – Text formulas ▪ LEFT. =LEFT(A2,4) ▪ RIGHT. =RIGHT(A2,2) ▪ MID. =MID(A2,2,3) ▪ LEN. =LEN(A2) ▪ CONCAT or TEXTJOIN. ▪ LOWER, UPPER, PROPER. – Date formulas ▪ TODAY. Current date. ▪ NOW. Date and time. ▪ YEAR, MONTH, DAY. ▪ DATEDIF. Date difference. ▪ EOMONTH. Month end. • Sorting and filtering – Sort by multiple columns. – Filter by value, color, condition. – Top 10 filter for quick insights. • Conditional formatting – Highlight duplicates. – Color scales for trends. – Rules for thresholds. Example. Sales > 10000 in green. • Pivot tables – Insert → PivotTable. – Rows. Category or Product. – Values. Sum, Count, Average. – Filters. Date, Region. – Refresh after data update. • Charts you must know – Column. Comparison. – Bar. Ranking. – Line. Trends over time. – Pie. Share or percentage. – Combo. Actual vs target. • Data validation – Dropdown list. Data → Data Validation → List. – Prevent wrong entries. • Useful shortcuts – Ctrl + Arrow. Jump data. – Ctrl + Shift + Arrow. Select range. – Ctrl + 1. Format cells. – Ctrl + L. Apply filter. – Alt + =. Auto sum. – Ctrl + Z / Y. Undo redo. • Common analyst mistakes to avoid – Merged cells. – Hard coded totals. – Mixed data types in one column. – No backup before cleaning. • Daily practice task – Download any sales CSV. – Clean it. – Build one pivot table. – Create one chart. Excel Resources: https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i Data Analytics Roadmap: https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02/1354 Double Tap ♥️ For More