Excel Mastery — Proficiency Exam

15 questions · pass with 50%

1. A column of prices will not add up. The amounts sit left-aligned with a warning. The most likely cause is:
Excel cannot add money in any versionThe digits are stored as text, not as numbersYou must merge the cells before summingCharts have replaced the need for totals
2. You copy a formula down a column and it stops pointing at the tax rate in cell H1. What usually happened?
The reference to H1 was relative, so it shifted as you copiedExcel deletes named cells overnightTax rates cannot be used in formulasYou must chart the tax rate first
3. Why should you avoid merging cells in a data table?
Merged cells make files smallerMerged cells are required for PivotTablesMerging breaks sorting, filters, and many formulas that expect a simple gridMerging converts text into numbers automatically
4. What is the difference between a formula and a function in Excel?
They are identical in every wayFunctions can only format cells, never calculateFormulas cannot use cell referencesA formula is the full expression; a function is a named calculation you can call inside it
5. Why do many analysts prefer INDEX with MATCH, or XLOOKUP, over classic VLOOKUP?
Those older lookups cannot return any value at allThey look up related values without depending on a fixed column index that breaks when columns are insertedThey automatically email the workbook to financeThey replace the need for headers
6. A lookup returns an error for a customer ID you can see in both sheets. The first thing to check is:
Whether one ID is a number and the other is text, or whether extra spaces differWhether the file name contains the word BudgetWhether you have used enough chart coloursWhether the ribbon is in dark mode
7. The main reason to convert a growing list into an Excel Table is:
Tables prevent anyone else from opening the filePrint margins become optionalNew rows and table-aware formulas can expand together instead of leaving a stale rangeTables delete the need for headers
8. What is a structured reference?
A way to point at a table column or row by name instead of a fragile A1-style blockA type of chart axisA password on the workbookA print area only
9. You filter a table to one region, then use a normal SUM on the whole Amount column. The total:
Always equals only the visible rows, in every Excel version, with no exceptionsConverts Amount to textDeletes hidden rows permanentlyMay still include hidden rows — a filter hides data but does not always change a full-column SUM
10. A PivotTable is most useful when you need to:
Merge every cell in the source sheetSummarise many rows into totals or counts by category without a new formula for every sliceReplace saving the fileConvert all text into dates
11. After you add new rows to the source table, a PivotTable shows old totals until you:
Change the workbook themeDelete all chartsRefresh the PivotTable (and confirm it points at the table, not a stale range)Rename the file twice
12. Before presenting a pivot as official, you should know:
What one source row represents, and whether a sample total matches a simple SUMOnly the colour of the headerWhether the pivot has at least twelve fieldsWhether macros are enabled
13. You want to show how monthly revenue changed over a year. The most appropriate default is:
A line chart or time-ordered column chartA pie chart with twelve unlabeled slicesA 3-D cone chartA scatter chart of department names
14. A column chart's vertical axis starts at 90 instead of 0, making a small gap look huge. This is a problem because:
Excel forbids any axis other than zero in all casesTitles cannot include units when axes are truncatedYou can never chart financial dataA truncated axis can exaggerate differences unless you clearly warn the reader
15. When category labels are long department names, a bar chart is often better than a column chart because:
Bars cannot show numbersHorizontal bars give labels room to be read without rotating text into a staircaseBar charts ignore the data tableColumns cannot be sorted

Answer all 15 questions to submit.