Skip to content
Advanced Excel
All coursesPlacement Report
‹ Back to previous sectionCourse homeAll page URLs

Excel Fundamentals and Formatting Interview Questions for Advanced Excel

25 Excel Fundamentals and Formatting questions with answers for advanced excel roles — 15 easy, 7 medium, 3 hard.

About Excel Fundamentals and Formatting

The base every Excel job tests: cell references, Tables, number formats, conditional formatting, data validation, protection and the shortcuts that make you fast. These topics decide whether your workbooks are clean, reliable and easy for others to use, and interviewers often start here before moving to formulas and pivots.

Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
3 questions free Unlock the rest in seconds
01

What is the difference between relative, absolute and mixed cell references?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
A relative reference like A1 shifts when a formula is copied, an absolute reference like $A$1 never shifts, and a mixed reference like $A1 or A$1 locks only the column or only the row. In a multiplication grid, =$A2*B$1 copied across and down keeps the header column and header row fixed. Press F4 while editing a reference to cycle through the four forms.
02

What is the difference between a workbook, a worksheet and a range?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
A workbook is the whole file (.xlsx, .xlsm or .xlsb). A worksheet is one tab inside it with 1,048,576 rows and 16,384 columns. A range is any group of cells such as A2:D100 that a formula, chart or format refers to. Good practice is to keep raw data, calculations and output on separate worksheets of the same workbook so each layer is easy to audit.
03

What does converting a range into an Excel Table (Ctrl+T) give you?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
A Table expands automatically when new rows or columns are added, so formulas, charts and pivot tables based on it pick up new data without editing ranges. It also gives banded rows, header filters, a Total Row, calculated columns that fill down by themselves, and structured references like Sales[Amount]. For any data that grows every month, a Table is the safest starting point.
04

What are structured references? Give an example.

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Structured references use Table and column names instead of cell addresses, which makes formulas readable and self-adjusting. For a Table named Sales: =SUM(Sales[Amount]) totals the whole column =[@Qty]*[@Price] multiplies values in the same row =COUNTIFS(Sales[Region],"North") If rows are added or a column moves, the formula still points to the right data.
05

What is conditional formatting? Give two business uses.

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Conditional formatting changes a cell's colour, font, icon or data bar based on a rule, so patterns stand out without reading every number. Common uses are highlighting invoices overdue by more than 30 days in red and adding data bars to a sales column so top performers are visible at a glance. Keep rules few and consistent, because many overlapping rules slow down large files.
06

What is Data Validation and how do you create a dropdown list?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Data Validation restricts what users can type in a cell, which prevents bad input in forms and trackers. For a dropdown, select the cells, go to Data > Data Validation, choose Allow: List and set Source to a range such as =$K$2:$K$10 or a typed list like Open,Closed,On Hold. Pointing the source at a Table column keeps the list updated automatically when new options are added.
07

Tell me about a time you had to clean up a messy spreadsheet someone else built.

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Situation: I inherited a sales tracker with merged cells, hard-coded totals and three different date formats. Task: my manager needed reliable weekly numbers. Action: I kept a backup copy, converted the data into an Excel Table, fixed dates with Text to Columns, replaced typed totals with SUMIFS and added data validation dropdowns. Result: weekly reporting time fell from two hours to twenty minutes and the totals stopped being questioned.
08

Explain the useful options inside Paste Special.

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Paste Special (Ctrl+Alt+V) lets you paste only part of what was copied. Values removes formulas, Formats copies only styling, Transpose flips rows into columns, and Skip Blanks avoids overwriting with empty cells. The Operation options can multiply or divide a whole range by a copied number, for example converting rupees to lakhs by copying 100000 and choosing Divide.
09

SUM returns 0 on a column of numbers exported from software. How do you fix it?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
The numbers are most likely stored as text, often with leading apostrophes, trailing spaces or non-breaking spaces (CHAR(160)). Check with =ISTEXT(A2) or LEN. Quick fixes are Data > Text to Columns > Finish, the green error triangle > Convert to Number, or Paste Special > Multiply by 1. For stubborn cases use =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),""))) or set the column type to Decimal in Power Query.
10

You change input values but the totals do not update until you press something. What is happening and how do you fix it?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
The workbook is probably in Manual calculation mode, often inherited from another file opened first in the same session. Press F9 to recalculate now, then set Formulas > Calculation Options > Automatic. If only some cells stay stale, check whether they are formatted as Text or whether Show Formulas is on. For very heavy files, manual mode can be kept deliberately, but tell users to press F9 before reading results.
11

What are named ranges and why are they useful?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
A named range gives a meaningful name, such as GST_Rate or Target_FY26, to a cell, range or constant. Formulas like =Amount*GST_Rate are easier to read and audit than =B2*$Z$1. Names are managed in Formulas > Name Manager and can be scoped to the workbook or a single sheet. They also work as navigation targets in the Name Box and as Data Validation sources.
12

Which Excel keyboard shortcuts do you use daily?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Ctrl+Arrow jumps to the edge of data and Ctrl+Shift+Arrow selects to it. Ctrl+T creates a Table, Ctrl+Shift+L toggles filters, Ctrl+1 opens Format Cells, Alt+= inserts AutoSum, F4 toggles absolute references or repeats the last action, F2 edits a cell, Ctrl+; inserts today's date, Ctrl+Alt+V opens Paste Special and Ctrl+E runs Flash Fill. Mentioning a few you genuinely use is better than reciting a long list.
13

What is the difference between Flash Fill and Text to Columns?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Text to Columns splits a column by a fixed delimiter or fixed width in one go, which suits clean data like comma-separated codes. Flash Fill (Ctrl+E) learns a pattern from one or two examples you type, such as extracting first names or formatting phone numbers. Both produce static values, so if the source changes you must redo them, unlike formulas or Power Query steps.
14

Tell me about a time you used Excel to save time for your team.

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Situation: in my internship, the team spent two hours every morning copying sales figures from three exports into one summary. Task: I was asked to make it faster. Action: I converted the exports to Tables, used XLOOKUP and SUMIFS to build a summary sheet, and added data validation for the date. Result: the report took ten minutes, errors from manual copying stopped, and the format was reused by another team.
15

An imported report has merged cells, blank spacer rows and subtotal lines, so sorting and filtering keep failing. How do you make it usable?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Work on a copy. Unmerge all cells, then fill the blanks with the value above using Go To Special > Blanks and =cell above, followed by paste as values. Filter out blank rows and subtotal lines and delete them, keep one header row and convert the range to a Table. If the same report arrives every month, repeat these steps once in Power Query so the cleanup becomes a single refresh.
16

How do you highlight an entire row when the Status column says Overdue?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Select the full data range, for example A2:H500, then Home > Conditional Formatting > New Rule > Use a formula. Enter: =$F2="Overdue" Choose a fill colour and click OK. The dollar before F locks the column, while the relative row 2 lets the rule move down row by row, so every column in that row gets coloured when column F matches.
17

How do you build a dependent dropdown, where the City list changes based on the selected State?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Create a list of cities under each state name and give each list a named range matching the state, for example Maharashtra or Karnataka (no spaces). In B2 make a State list. In C2 use Data Validation > List with source: =INDIRECT(B2) In Microsoft 365 you can avoid named ranges with a spill formula in a helper cell: =FILTER(Cities[City],Cities[State]=B2) and point the validation at that spill range using =$H$2#.
18

A column shows amounts rounded to whole rupees, but its total differs from a manual total of the displayed values by a few rupees. Why?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Number formatting only changes how a value looks; Excel still adds the full decimals stored in each cell. So the sum of hidden paise differs from the sum of the rounded figures shown. If the report must add up exactly as displayed, round the values with =ROUND(value,0) in the source formulas rather than using the Precision as displayed option, which permanently changes stored data for the whole workbook.
19

Write a custom number format that shows positives in green, negatives in red brackets and zero as a dash.

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Custom formats have up to four sections separated by semicolons: positive; negative; zero; text. Press Ctrl+1 > Number > Custom and enter: [Green]#,##0;[Red](#,##0);"-";@ For Indian lakh-crore grouping you can use: [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0 The value stays unchanged; only the display changes.
20

What is the difference between Protect Sheet, Protect Workbook and Encrypt with Password?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Protect Sheet stops edits to locked cells; you first unlock input cells through Format Cells > Protection so users can type only there. Protect Workbook locks the structure, so sheets cannot be added, deleted or renamed. Encrypt with Password (File > Info) is the only option that blocks opening the file. Sheet and workbook protection are about preventing accidents, not real security, because they are easy to bypass.
21

You need to find and remove duplicate customer records but keep the original data safe. How do you do it?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
First highlight duplicates with Conditional Formatting > Highlight Cells Rules > Duplicate Values to review them. Then work on a copy of the sheet and use Data > Remove Duplicates, selecting the key columns such as Mobile and Email. In Microsoft 365 a non-destructive option is =UNIQUE(A2:C500) on a new sheet. Always record how many rows were removed so the change can be explained later.
22

Describe a time you found an error in a report just before it was sent.

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Situation: a monthly MIS was due to leadership in an hour. Task: I was doing a final review. Action: I noticed the region totals did not match the grand total, traced it to a SUMIFS range that stopped at row 500 while data had grown to 620 rows, converted the source to a Table and re-checked totals against the raw export. Result: the report went out on time with correct numbers, and I added a reconciliation check cell to the template.
23

A shared Excel file has grown to 40 MB and takes ages to recalculate. What do you check?

Excel Fundamentals and FormattingHard
Save to ▾
Show answer
Start with Ctrl+End to see if the used range is far beyond the real data and delete the extra rows and formats. Then look for volatile functions like OFFSET, INDIRECT and TODAY, whole-column references inside SUMPRODUCT or array formulas, and duplicated conditional formatting rules. Replace repeated lookups with one helper column, move heavy transforms to Power Query, and save as .xlsb if size still matters.
24

What are volatile functions and why should you limit them?

Excel Fundamentals and FormattingHard
Save to ▾
Show answer
Volatile functions recalculate every time anything in the workbook changes, not only when their inputs change. Examples are NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, CELL and INFO. One TODAY cell is harmless, but thousands of OFFSET or INDIRECT formulas force the whole dependency chain to recalculate on every keystroke. Replace OFFSET with INDEX-based ranges or Tables, and put TODAY in a single cell that others reference.
25

Fifteen people edit the same tracker and you keep getting conflicting versions over email. How would you fix the process?

Excel Fundamentals and FormattingHard
Save to ▾
Show answer
Move the file to OneDrive or SharePoint so everyone co-authors one live copy, and stop emailing attachments. Restructure it into a single input Table with Data Validation, lock formula columns with sheet protection, and give each person a filtered view or their own sheet view. Use version history for recovery. If edits keep growing, move the data to a SharePoint list or database and keep Excel for reporting.

Frequently asked questions

How important is Excel Fundamentals and Formatting in Advanced Excel interviews?

The base every Excel job tests: cell references, Tables, number formats, conditional formatting, data validation, protection and the shortcuts that make you fast. These topics decide whether your workbooks are clean, reliable and easy for others to use, and interviewers often start here before moving to formulas and pivots.

How should I use the Excel Fundamentals and Formatting sheet?

Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.

More question sheets

Logical, Text and Date Functions questionsLookup and Dynamic Array Functions questionsPivot Tables and Data Summaries questionsCharts and Dashboards questionsPower Query and Data Cleaning questionsPower Pivot, Data Model and DAX questionsMacros and VBA Automation questions