Ravindra BagaleCourses & study guides मराठी Track your progress

17. Interview Questions and Answers

17.6 Performance, Dashboards and Power Tools

By Last updated:

← All Excel dashboards

Q47. How do you handle a large, slow Excel file?

Replace volatile functions (OFFSET, INDIRECT, TODAY across many rows); avoid full-column references in heavy formulas; convert repeated calculations to helper columns or LET; remove unused formatting and styles; use Tables and Power Query/Power Pivot instead of thousands of lookups; switch to Manual calculation while editing (Formulas › Calculation Options); save as .xlsb; and check with Ctrl + End for a bloated used range.

Q48. What is Power Query and why use it?

Excel's ETL tool (Data › Get & Transform Data). It imports from files, folders, databases and the web, cleans and reshapes data with recorded, repeatable steps (Applied Steps), merges and appends tables, and refreshes with one click – ideal for monthly reports that arrive in the same messy format.

Q49. Power Pivot vs a normal PivotTable?

A normal pivot summarises one table. Power Pivot (the Data Model) holds multiple related tables with relationships, compresses millions of rows, and supports DAX measures such as Distinct Count or time intelligence. A pivot built on the Data Model can use fields from several tables.

Q50. How do you consolidate data from multiple sheets or files?

Power Query › From Folder or append queries (best, refreshable); Data › Data Tools › Consolidate; 3D references like =SUM(Jan:Dec!B2) for identical layouts; or a VBA macro that copies sheets into one.

Q51. How would you build a KPI dashboard in Excel?

Define audience, questions and KPIs; clean the data into Tables; build pivots on a hidden Calc sheet; create KPI cards linked to GETPIVOTDATA/SUMIFS cells; add PivotCharts; connect slicers and a timeline to all pivots; design for one screen with a clear hierarchy; add a data date; test and protect.

Q52. Excel dashboard vs Power BI dashboard?

Excel suits small-to-medium data, flexible ad-hoc analysis, offline use and users comfortable with spreadsheets. Power BI handles larger data models, scheduled refresh, row-level security, web/mobile sharing and richer interactivity. Many teams prepare data in Excel/Power Query and publish shared dashboards in Power BI.

Q53. Explain Goal Seek, Scenario Manager and Data Tables.

Goal Seek finds the one input that produces a target result (for example orders/day to break even). Scenario Manager stores and compares named sets of inputs (Normal, Festival, Monsoon). Data Tables show a result for many values of one or two inputs. Solver optimises with several variables and constraints.

Q54. How do you do regression or forecasting in Excel?

Scatter chart with a trendline (display equation and R²); SLOPE, INTERCEPT, RSQ, LINEST or FORECAST.LINEAR; the Analysis ToolPak's Regression tool; or Data › Forecast › Forecast Sheet for time series (exponential smoothing).

Ravindra Bagale's Tip

Many students answer the "large file" question with just "use Power BI" – the interviewer wants to hear solutions within Excel. Explain these 5–6 points in order: volatile functions, full-column references, formatting, Manual calculation, .xlsb and Power Query.

Loading practice tasks...

  1. Total billable hours (Easy): Add up Billable hours in the "Billable hours" column.
  2. Average rate per hour (Easy): Average of the Rate per hour ($) column, rounded to 2 decimals.
  3. Rows in Cloud (Easy): How many rows have Practice "Cloud"?
  4. Largest billable hours (Easy): The largest value in the "Billable hours" column.
  5. Billable hours for Cloud (Medium): Total of the Billable hours column where Practice is "Cloud".
  6. Cloud in Austin (Medium): Total of the Billable hours column where Practice is "Cloud" and City is "Austin".
  7. Best in Cloud (Medium): Largest value in the "Rate per hour ($)" column where "Practice" is "Cloud".
  8. Value of row 2 (Medium): Multiply the first-row Billable hours by the first-row Rate per hour ($).
  9. Look up Rohan Iyer (Medium): The Billable hours for "Rohan Iyer" (use a lookup on the "Name" column).
  10. Total value (Hard): Sum of "Billable hours" times "Rate per hour ($)" for all rows.
  11. Share of Cloud (Hard): Share of the "Billable hours" column that is "Cloud", rounded to 3 decimals.
  12. Total "Billable hours" for Cloud (Hard): Add up the "Billable hours" column for the rows where "Practice" is "Cloud".
  13. Above average? (Hard): "Above" if the "Rate per hour ($)" value in the first row is above the average of the "Rate per hour ($)" column, else "Below".

See all practice tasks in the Task Hub →

Worked example — performance dashboards and power tools

Why

Aamir Khan in Pune and Anna Kowalski abroad both need a clear, repeatable way to use performance dashboards and power tools on a GreenField Dairy sheet without breaking totals.

What

Performance Dashboards And Power Tools is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for performance dashboards and power tools.
  2. Enter a small made-up value for Aamir Khan using ₹ where money appears.
  3. Add a second value for Anna Kowalski using £ so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).
Result: The demo cell shows the expected result for GreenField Dairy's sample row.
Meaning: Once performance dashboards and power tools is correct on one row, copy the same pattern down the column.

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Performance Dashboards And Power Tools?
Card 1: What is Performance Dashboards And Power Tools? · Download PNG
Revision card 2: Tiny example — Performance Dashboards And Power Tools
Card 2: Tiny example — Performance Dashboards And Power Tools · Download PNG
Revision card 3: Checklist — Performance Dashboards And Power Tools
Card 3: Checklist — Performance Dashboards And Power Tools · Download PNG