Skip to main content

Essential Excel & Google Sheets Skills

Premium

Let’s get tactical. This is where we dig into the Excel and Google Sheets skills that top tech companies expect you to know, not just to get hired, but to actually do the job well.

Even at senior levels, these tools remain essential for rapid analysis, ad hoc reporting, and stakeholder communication.

Core data manipulation skills

These are the basics, and they come up in every real-world dataset and interview project.

SkillWhat it testsCommon mistakeExample
Sorting & FilteringCan you isolate top performers or narrow data by condition?Sorting without preserving data integrity e.g., breaking row alignment when sorting“Sort revenue DESC to find top 5 customers by region.”
Removing DuplicatesCan you clean data while preserving the right info?Removing rows without checking if all fields are truly duplicates“Remove duplicate orders by order_id, but keep the latest date.”
Handling Missing ValuesCan you handle NULLs and blanks in calculations and visuals?Allowing blanks to skew analysis or misinterpreting missing as zero=IF(ISBLANK(B2), 0, B2) to prevent blank impact
Text ManipulationCan you clean and extract info from messy text?Not normalizing case or trimming invisible characters that break joins or filtersExtract domain from email using RIGHT + FIND
Data ValidationCan you prevent bad data from entering your sheet?Over-relying on visual checks; inconsistent inputs like "CA", "ca", or "Calif"Use drop-down lists to enforce region standardization

Potential interview-style tasks:

  • Given a table of users, show those who are from the US and are paying members.
  • Clean a customer name column where trailing spaces and inconsistent casing are causing grouping issues.

Data aggregation & summarization skills

This is where you go from raw data to business insight.

SkillWhat it testsCommon mistakeExample
Basic AggregationsCan you calculate descriptive stats—SUM, AVERAGE, etc.?Using static ranges; mixing data types that distort calculations=AVERAGEIFS() for average revenue by region
Conditional AggregationsCan you apply multiple filters in one formula?Using SUMIF instead of SUMIFS when multiple conditions are neededTotal sales where region = "West" AND product = "A"
Pivot TablesCan you group, slice, and summarize multidimensional data?Dragging in raw data without aggregating, or not formatting properlyPivot: rows = region, values = revenue, filter = year

Even senior analysts often under-leverage pivot table features like grouping by date, custom sorting, or calculated fields.

Potential interview-style tasks:

  • Create a pivot table to show monthly revenue trends per client.
  • Calculate the average number of orders for clients with conversion rates above 5%.

Data lookup & joining skills

These are used constantly to combine and merge datasets—especially in business-facing roles.

SkillWhat it testsCommon mistakeExample
VLOOKUP / HLOOKUPCan you perform basic lookups between tables?Hardcoding column index; forgetting FALSE for exact match“VLOOKUP SKU to find the corresponding category name.”
INDEX + MATCHCan you build more flexible lookups (e.g., non-leftmost match)?Mixing up row/column logic; referencing wrong ranges“Match customer name to pull latest purchase date.”
XLOOKUPDo you know the modern lookup function that solves VLOOKUP’s flaws?Assuming compatibility in older Excel versions“Return plan name using customer ID, return ‘Unknown’ if not found.”

During your interview, you should explain why they choose INDEX-MATCH over VLOOKUP (e.g., for robustness and flexibility) if you encounter these questions.

Potential interview-style tasks:

  • Return the latest subscription plan for each user from a separate tab.
  • You have two tables—one with transaction logs and another with user demographic info. Create a combined table that includes transaction amounts and user age group.

Data visualization skills

Charts will absolutely show up in stakeholder dashboards and interview presentations. Make sure yours tells a story.

SkillWhat it testsCommon mistakeExample
Basic ChartsCan you visualize trends, comparisons, and distributions?Using pie charts for time series or too many segmentsBar chart for top 5 acquisition channels
Chart CustomizationCan you make charts clean, legible, and decision-friendly?Missing labels, inconsistent scales, unclear legendsTitle: "Conversion Rate by Channel", labeled axes
Color Coding & ThemesCan you highlight key insights and stay on brand?Overusing bright colors or ignoring company design standardsUse company color palette to highlight “above target” performance

Sometimes, even experienced analysts can overlook the importance of contextual labeling and formatting that drives executive understanding.

Potential interview-style tasks:

  • Given the table, visualize revenue trends by client across two months.
  • Given the information, create a scatter plot comparing CAC vs conversion rate per region.

Formula & function (Beyond aggregation & lookup)

These go beyond basics and test whether you can build smart, flexible logic in a spreadsheet.

SkillWhat it testsCommon mistakeExample
Logical FunctionsCan you use IF, AND, OR, NOT for branching logic?Nested IFs without clarity or fallbackFlag "High Performer" if West + revenue > 1000
Date FunctionsCan you analyze trends over time?Confusing text vs. date formats; not handling edge cases=YEAR(TODAY()) - YEAR(signup_date) for customer age
Statistical FunctionsCan you calculate variability, correlation, percentiles?Using STDEV.S instead of STDEV.P; not adjusting for outliersUse CORREL() to assess spend vs. ROI
Error HandlingCan you build resilient spreadsheets?Allowing raw errors like #DIV/0! to break workflowsWrap with IFERROR() to return "N/A"

Potential interview-style tasks:

  • Create a formula to flag churn-risk users who haven’t logged in for 30+ days.
  • Create a metric that segments clients by quartile based on total spend.

Efficiency & best practices

You’re not just being evaluated on your output—but how clean, fast, and scalable your work is.

SkillWhat it testsCommon mistakeExample
Clean LayoutCan you structure your sheet for other people to understand?Mixing inputs, outputs, and notes in one sheetUse separate tabs for raw vs processed data
Named RangesCan you make formulas readable and modular?Hardcoding ranges across formulasUse sales_total instead of $B$2:$B$200
Keyboard ShortcutsAre you efficient under pressure?Spending time with mouse clicks for every actionUse Ctrl+Shift+L to toggle filters, F4 to lock cells
Formula AuditingCan you debug and explain complex formulas if needed?Can’t trace dependencies or figure out what’s breakingUse “Trace Precedents” and “Evaluate Formula” tools

Potential interview-style tasks:

  • Review a broken formula and explain why it’s not returning results.
  • Clean up and annotate a messy analysis file for handoff to stakeholders.

When using Excel or Google Sheets to analyze data for take-home case questions, always link your formulas directly to the source data instead of copying and pasting values.

This ensures:

  • Transparency – Interviewers can trace your logic and see how your calculations were derived
  • Reusability – If the data changes, your analysis updates automatically
  • Good practice – It reflects how you'd structure real-world analysis for stakeholders or peer review

Avoid hardcoding values into formulas or creating disconnected summaries. Clean, auditable spreadsheets are a subtle but powerful signal of analytical maturity.

You don’t need to memorize every Excel or Google Sheets formula, but it’s critically important to understand how and when to use the core tools and functions effectively.

What really sets you apart, especially in interviews and take-home assignments, is your ability to:

  • Choose the right function or feature for the task
  • Clearly explain your logic and decision-making process
  • Communicate your approach confidently and concisely

This shows not only technical fluency but also analytical thinking and stakeholder readiness, which is what top companies are really looking for.

Next Lesson