17.6 Performance, Dashboards and Power Tools

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).