Excel & Power BI Interview Questions — Flashcards | inGrade

Question 1

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

Accepted Answer

Relative References (e.g., A1) adjust when a formula is copied to another cell. Absolute References (e.g., $A$1) remain fixed regardless of where the formula is moved. Mixed References (e.g., $A1 or A$1) fix either the row or column. Example: If =A1+B1 is copied to C3, it becomes =C3+D3 (relative), but =$A$1+$B$1 remains constant.

Question 2

How does VLOOKUP work and what are its limitations?

Accepted Answer

VLOOKUP searches for a value in the first column of a range and returns a value in the same row from a specified column. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Limitations: can only look to the right, requires the lookup column to be the first column, and is slower than INDEX-MATCH. XLOOKUP is the modern replacement that overcomes these limitations.

Question 3

What is a PivotTable and how do you create one?

Accepted Answer

A PivotTable is a powerful tool for summarizing, analyzing, and presenting large datasets. To create one: select your data range, go to Insert > PivotTable, choose where to place it, and use the field pane to drag fields into Rows, Columns, Values, and Filters areas. PivotTables allow you to quickly aggregate data, calculate percentages, and drill down into details.

Question 4

What is conditional formatting in Excel?

Accepted Answer

Conditional formatting dynamically changes cell appearance based on criteria (e.g., highlighting values above 100 in green). To create rules: select the range, go to Home > Conditional Formatting > New Rule, choose a condition and set formatting. It is a powerful tool for highlighting trends, outliers, and data quality issues in datasets.

Question 5

How do you combine INDEX and MATCH functions?

Accepted Answer

The INDEX function retrieves a value at a specific position, and MATCH finds the position of a value. Together: =INDEX(B1:B10, MATCH(5, A1:A10, 0)) finds 5 in A1:A10 and returns the corresponding value from B1:B10. This combination is more flexible than VLOOKUP as it can look in any direction and is not limited to the first column.

Question 6

What is Power Query and what are its benefits?

Accepted Answer

Power Query is a data transformation and connection tool in Excel and Power BI that simplifies importing, cleaning, and reshaping data from multiple sources. Benefits include: automating repetitive data preparation tasks, handling large datasets efficiently, connecting to diverse sources (Excel, SQL, APIs, web), and recording transformation steps that can be refreshed automatically.

Question 7

What is DAX and how is it used in Power BI?

Accepted Answer

DAX (Data Analysis Expressions) is a formula language used in Power BI for creating calculated columns, measures, and custom aggregations. Key functions include CALCULATE (modifies filter context), SUMX (iterates rows and sums), RELATED (looks up values from related tables), and time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR for period-over-period analysis.

Question 8

What is the difference between calculated columns and measures in Power BI?

Accepted Answer

Calculated columns are computed for each row in a table and stored in the data model — they increase file size. Measures are calculated at runtime based on the user's interaction with visuals, saving memory and improving performance. Best practice: use measures for aggregations (SUM, AVERAGE, COUNT) and calculated columns only when you need row-level values for filtering or grouping.

Question 9

What is Row-Level Security (RLS) in Power BI?

Accepted Answer

RLS restricts data access at the row level based on user roles. You define roles in Power BI Desktop using DAX filter expressions (e.g., [Region] = USERNAME()), then assign users to those roles in Power BI Service. This ensures that different users see only the data relevant to them — for example, a regional manager sees only their region's sales data.

Question 10

What are the SUMIF and COUNTIF functions in Excel?

Accepted Answer

SUMIF adds values in a range that meet a specified condition: =SUMIF(A1:A10, '>10', B1:B10) sums values in B1:B10 where A1:A10 is greater than 10. COUNTIF counts cells that meet a condition: =COUNTIF(A1:A10, 'Sales') counts cells containing 'Sales'. SUMIFS and COUNTIFS extend these to multiple criteria. These functions are essential for conditional aggregation without PivotTables.

Start your learning journey today