Advanced Excel and Google Sheets Questions

Covers advanced spreadsheet skills used for data analysis, reporting, and ad hoc business intelligence work in both Microsoft Excel and Google Sheets. Core capabilities include lookup and reference functions such as VLOOKUP and INDEX MATCH, aggregation and conditional functions such as SUMIF and AVERAGEIF, logical functions such as IF, array formulas, and nested formulas. Candidates should be comfortable building and manipulating pivot tables to summarize data, using conditional formatting and data validation to ensure data quality, and structuring worksheets with named ranges and proper use of absolute versus relative cell references. The topic also includes creating dynamic formulas and simple dashboards for visualization, charting best practices, data cleaning techniques, and performance considerations for large worksheets. At an advanced level, familiarity with automation and workflow improvements such as macros or scripts, query and transform capabilities, and how spreadsheets integrate or compare with business intelligence tools is expected.

EasyTechnical

70 practiced

Describe three common conditional formatting rules you would use in a sales workbook and explain the formulas or settings to implement each in Excel or Google Sheets. Examples: highlight negative values, highlight top 10% performers, and flag duplicate customer IDs across a column.

EasyTechnical

73 practiced

Explain the difference between Freeze Panes and Split in Excel/Sheets, and give two scenarios where using Freeze Panes prevents user mistakes when working with large, multi-column datasets. Include instructions for how to apply Freeze Panes for freezing the top header and the leftmost column.

HardTechnical

108 practiced

Describe the steps to create a waterfall chart in Excel for monthly revenue changes, including how to calculate intermediate subtotal values (starts, increases, decreases, and ending balance). Provide formulas or helper column logic needed to feed the chart and explain formatting choices for clarity.

Sample Answer

Situation: You need a clear monthly waterfall in Excel that shows the starting revenue, each month’s increases/decreases, and the ending balance.

Steps + helper-column logic (assume columns A: Month, B: Change [positive for increase, negative for decrease], and cell B1 change for Month1, initial starting revenue in C1 as StartValue):

1) Build helper columns for clarity:- C (Start): C2 = $C$1 for first month (or Input start). For row n>2: Cn = En-1- D (Increase): =MAX(Bn,0)- E (Decrease): =MAX(-Bn,0) — converts negatives to positive magnitude- F (End / Cumulative): =Cn + Dn - En

Example formulas (row 2):- C2: = $C$1- D2: =MAX($B2,0)- E2: =MAX(-$B2,0)- F2: =C2 + D2 - E2Then copy down. This gives explicit starts, ups, downs and running balance.

2) Build chart series (stacked columns)- Series 1 (Base / Offset): use Start column (C). This creates invisible offsets so ups/downs float from the correct baseline.- Series 2 (Up): use Increase column (D)- Series 3 (Down): use Decrease column (E) — plot as positive but format color to red and use an overlap trick (stacked so it subtracts visually)- Optional Series 4: Ending total (for final month only) — add as separate column if you want final total standalone.

3) Create the chart- Insert → Column → Stacked Column.- Add three series in order: Base, Up, Down. X-axis = Month.- Make Base series fill = No Fill (so it’s invisible).- For Down series, change fill color red and set “Invert if negative” off since you used magnitudes; the stacked Base + Down will visually lower the bar.- Add data labels to Up/Down and the final End value (use F column). For Base labels hide them.- Reduce gap width (e.g., 25%) for compact bars. Use consistent colors: green for increases, red for decreases, gray/blue for totals.

4) Additional polish / validations- Add a calculated Total column for the final bar: Put blank (or NA()) for intermediate months and F for the final month; format as a separate series and show as standalone (not stacked).- Use number formatting (thousands, currency) and add +/- signs in data labels: =IF(Bn>=0,"+"&TEXT(Bn,"$#,##0"),TEXT(Bn,"$#,##0"))- Sort months chronologically and freeze header row.- Add connectors by thin borders or use a line series for clarity (optional).

Why this works:- The Base offset makes each increase/decrease start from the running balance.- Separating Increase and Decrease simplifies coloring, labels and prevents sign confusion.- The cumulative End column is a live check (useful for validation against source data).

Edge cases:- If there are zero changes, ensure helper columns return 0.- For many small periods, consider smoothing labels or interactive tooltip tool (Power BI/Tableau) for readability.

HardTechnical

64 practiced

When preparing Excel/Google Sheets data for ingestion into Tableau or Power BI, what best practices do you follow? Discuss schema design, normalization vs denormalization, date handling, column naming conventions, removing blank rows/columns, and strategies to enable scheduled refreshes and minimize refresh time.

Sample Answer

Situation: As a data analyst preparing Excel/Sheets for ingestion into Tableau or Power BI, I follow disciplined data-prep practices so reports are reliable, performant, and refreshable.

Schema design:- Treat the workbook as a small data warehouse: separate fact tables (transactional metrics) from dimension tables (customers, products, dates).- Keep one table per sheet and ensure each table has a single header row and consistent columns.

Normalization vs denormalization:- Prefer normalized source tables for storage/ETL (to reduce redundancy) but denormalize prior to visualization when it reduces join complexity and improves query performance (e.g., pre-joined monthly aggregates for dashboards).- Balance by keeping lookup dimensions separate and using keys in facts.

Date handling:- Always include a dedicated date dimension with continuous keys, fiscal/ISO fields, and datepart columns (year, quarter, month, week, day, weekday, is_holiday).- Store dates as true date types; avoid text formats. Include timezone-aware timestamps if needed.

Column naming conventions:- Use short, descriptive, consistent names (snake_case or TitleCase), avoid spaces/special chars, and prefix keys (customer_id). Add units where applicable (revenue_usd).

Cleaning (blank rows/cols, types):- Remove blank rows/columns and summary rows; ensure header row is first non-empty row.- Enforce consistent data types per column and replace mixed-type cells with nulls or standardized formats.- Trim whitespace, remove non-printable chars, and standardize categorical values.

Enable scheduled refreshes & minimize refresh time:- Use connector-friendly formats: save Sheets as a single table per sheet or publish as CSVs/Parquet where possible.- Avoid volatile formulas; materialize calculated columns in source or in an ETL layer.- Reduce row counts via aggregation, incremental refresh (partition by date), and query folding (use native queries in Power Query / Tableau Prep).- Minimize columns to only those used in visuals; hide or archive historical raw sheets.- Use credentials and gateway (on-prem) properly, and test incremental refresh logic.- Profile refresh times, cache static lookups, and monitor refresh logs to iterate.

This approach yields predictable joins, faster dashboard loads, and robust scheduled refreshes.

MediumTechnical

129 practiced

Explain when to use Excel's Data Model / Power Pivot instead of a standard Pivot Table. Describe how relationships between tables are created, when to use calculated columns vs measures (DAX), and a scenario where using the Data Model is essential for correct results.

Sample Answer

Use the Data Model / Power Pivot when you need to analyze multiple related tables, handle large datasets beyond Excel sheet limits, or build reusable, performant calculations with DAX. Standard PivotTables are fine for a single flattened table; Data Model is for normalized data (star/snowflake schemas), memory-compressed storage, and more sophisticated measures.

Creating relationships:- Import tables into the Data Model (Power Query or Data → Manage Data Model).- Define relationships by matching key columns (typically one-to-many: dimension → fact).- Relationships are managed in Diagram View; cross-filter direction can be single or both (use single where possible).- Ensure keys are same data type and have unique values on the “one” side.

Calculated columns vs Measures:- Calculated column: row-level calculation stored in the model. Use when you need a column to slice/filter (e.g., Category = IF([Sales]>100, "High","Low")).- Measure: aggregation computed at query time using DAX; not stored per row. Use for sums, ratios, time-intel (e.g., TotalSales, YoY growth). Measures are more memory-efficient and context-aware.Example DAX:

dax

TotalSales = SUM(FactSales[Amount])
SalesPerCustomer = DIVIDE([TotalSales], DISTINCTCOUNT(FactSales[CustomerID]))

Essential scenario:You have Orders, Customers, and Products tables. A Pivot that pulls from flattened joins double-counts when Products repeat per order line. Using the Data Model with proper one-to-many relationships and a measure like TotalSales prevents double-counting and correctly aggregates across slicers (e.g., show sales by Customer Region while filtering by Product Category).

Unlock Full Question Bank

Get access to hundreds of Advanced Excel and Google Sheets interview questions and detailed answers.

Join thousands of developers preparing for their dream job.