Comprehensive Excel cheat sheet for data analysts

Shortcuts

- Ctrl + S: Save

- Ctrl + C: Copy

- Ctrl + V: Paste

- Ctrl + Z: Undo

- Ctrl + Y: Redo

- Alt + =: AutoSum

- F2: Edit cell

- F5: Go to cell

- F11: Full-screen mode

Functions

- SUM: =SUM(range)

- AVERAGE: =AVERAGE(range)

- COUNT: =COUNT(range)

- MAX: =MAX(range)

- MIN: =MIN(range)

- IF: =IF(logical_test, [value_if_true], [value_if_false])

- VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

- INDEX-MATCH: =INDEX(range, MATCH(lookup_value, range, [match_type])

Data Manipulation

- PivotTables: Alt + F1

- Power Query: Data > New Query

- Data Validation: Data > Data Validation

- Conditional Formatting: Home > Conditional Formatting

Data Analysis

- Regression: Data > Data Analysis > Regression^[1]

- Correlation: Data > Data Analysis > Correlation

- Histogram: Data > Data Analysis > Histogram

Data Visualization

- Charts: Insert > Chart

- Tables: Insert > Table

- Power BI: Data > Power BI

Productivity

- Quick Analysis: Review > Quick Analysis

- Flash Fill: Data > Flash Fill

- AutoComplete: Formulas > AutoComplete

Error Handling

- IFERROR: =IFERROR(cell, value_if_error)

- IFBLANK: =IFBLANK(cell, value_if_blank)

Pivot Tables

• Key interview skills:

• Creating calculated fields
• Grouping by date (months, quarters)
• Using slicers for interactive filtering
• Creating pivot charts

• Common task: "Show sales trends by product category over time"

Power Query

• Essential for data cleaning:

1. Removing duplicates
2. Splitting columns
3. Merging tables
4. Unpivoting data

• Example workflow:

1. Import messy data
2. Transform (clean) in Power Query
3. Load to worksheet
4. Refresh when source data changes

Additional Must-Know Features

• Conditional Formatting

• Highlight top/bottom values
• Color scales for trends
• Data bars for visual comparison

• Keyboard Shortcuts

• Ctrl + Arrow keys (navigate data)
• Alt + = (AutoSum)
• Ctrl + Shift + L (Filter toggle)
• F4 (lock cell references)

Interview Tips:

1. Always mention data validation and error handling
2. Explain your thought process while solving problems
3. Show how these tools connect in real workflow: Data Import → Clean → Analysis → Visualization

Practice Exercise: 

Try combining these skills:

1. Import raw data using Power Query
2. Clean and transform
3. Create lookup table with INDEX/MATCH
4. Summarize with SUMIFS
5. Visualize in pivot table
6. Add conditional formatting

Common Interview Tasks to Practice:

1. "Find duplicate transactions"
2. "Calculate month-over-month growth"
3. "Match customer data across multiple sheets"
4. "Create a sales dashboard"^[2]

"Data Analytics Live Workshop "

If You are interested in Learning Data Analytics, You can enroll for the online live Workshop

Sunday 10:00 AM Onwards

SPeaker : Aditi Gupta

Designed for beginners.

Complete practical and interactive live workshop.

Includes an end to end project and certificate.

Here is the link to register  - https://techtip24workshop.com/