Excel Guide for Data Analysts – With
Examples
1. Excel Basics
• Understand the interface: Ribbon, workbook, worksheets, cells.
• Navigation: Ctrl+Arrow Keys, Ctrl+Home/End.
• Data types: Text, Number, Date, Currency.
2. Data Cleaning
• Use TRIM() to remove extra spaces.
• Use TEXT TO COLUMNS to split data.
• Remove Duplicates: Data > Remove Duplicates.
• Find & Replace: Ctrl + H to clean values.
3. Formulas & Functions
• SUM, AVERAGE, MAX, MIN – Basic arithmetic.
• IF, AND, OR – Logical decisions.
• COUNTIF/COUNTIFS – Count with conditions.
• SUMIF/SUMIFS – Conditional summation.
• Example: =IF(A2>100, "High", "Low")
4. Lookup & Reference
• VLOOKUP: =VLOOKUP(A2, Sheet2!A:B, 2, FALSE)
• INDEX + MATCH: =INDEX(B2:B10, MATCH(1001, A2:A10, 0))
• HLOOKUP for horizontal lookup.
• XLOOKUP (Modern Excel): =XLOOKUP(A2, A:A, B:B)
5. Text Functions
• LEFT, RIGHT, MID – Extract part of strings.
• LEN – Count characters.
• UPPER, LOWER, PROPER – Change case.
• CONCATENATE / TEXTJOIN – Combine strings.
• Example: =LEFT(A2,4)
6. Date Functions
• TODAY(), NOW() – Current date/time.
• DATEDIF – Date difference.
• TEXT – Custom date format: =TEXT(A2, "dd-mm-yyyy")
• NETWORKDAYS – Count working days.
7. Data Validation
• Create dropdowns: Data > Data Validation > List.
• Restrict numeric ranges or dates.
• Custom formulas for validation rules.
8. Conditional Formatting
• Highlight duplicates, top 10%, custom formulas.
• Use Data Bars, Color Scales, Icon Sets.
• Example: =MOD(ROW(),2)=0 for alternate row colors.
9. Sorting & Filtering
• Sort by one or multiple columns.
• Use Filter dropdowns to analyze subsets.
• Custom sort by cell or font color.
10. Pivot Tables
• Insert Pivot Table: Insert > PivotTable.
• Drag fields into Rows, Columns, Values, Filters.
• Summarize data, apply slicers and grouping.
• Show Values As: % of Total, Running Total.
11. Charts & Visualizations
• Column, Line, Pie, Bar charts.
• Combo charts with secondary axis.
• Use chart elements (labels, title, legend).
• Sparklines: Show trends within a cell.
12. Dashboards
• Combine PivotTables, Charts, and Slicers.
• Use Named Ranges for dynamic dashboards.
• Link dropdowns to charts using formulas.
13. Excel Tables
• Ctrl + T to convert range to Table.
• Structured references simplify formulas.
• Auto-expand, filter, and style data easily.
14. Error Handling
• Common errors: #DIV/0!, #REF!, #VALUE!, #N/A.
• Use IFERROR: =IFERROR(A2/B2, "Check Value").
15. Useful Shortcuts
• Ctrl + T: Convert to Table.
• Alt + E + S + V: Paste Special Values.
• Ctrl + Shift + L: Toggle filters.
• Ctrl + ; for current date, Ctrl + Shift + #: Date format.
