1 00:00:00,080 --> 00:00:05,063 You enter a formula expecting a short list and Excel shows #SPILL! instead. 2 00:00:05,063 --> 00:00:08,897 In our fictional ticket-status workbook, a supervisor note already 3 00:00:08,897 --> 00:00:10,430 occupies one output cell. 4 00:00:10,430 --> 00:00:14,647 We will locate that exact obstruction, preserve the note, and then 5 00:00:14,647 --> 00:00:16,564 verify four distinct status values. 6 00:00:16,564 --> 00:00:20,781 This lesson covers a blocked spill range; the same error can 7 00:00:20,781 --> 00:00:24,997 have other causes, so read Excel's message before changing anything. 8 00:00:25,597 --> 00:00:28,890 Download the practice workbook and open its Statuses sheet. 9 00:00:28,890 --> 00:00:33,645 Column A has six fictional tickets, and column B has six status labels. 10 00:00:33,645 --> 00:00:38,767 The intended output begins in D two, but D four contains KEEP: supervisor note. 11 00:00:38,767 --> 00:00:40,596 Do not remove it yet. 12 00:00:40,596 --> 00:00:44,620 The independent answer check lists the expected four unique statuses, so 13 00:00:44,620 --> 00:00:49,010 we can tell whether the repair worked without trusting a screenshot alone. 14 00:00:49,610 --> 00:00:54,303 Select D two and enter the UNIQUE formula shown here and in the guide. 15 00:00:54,303 --> 00:00:58,326 It asks Excel for distinct values from B two through B seven. 16 00:00:58,326 --> 00:01:03,019 If the output were clear, four results would occupy D two through D five. 17 00:01:03,019 --> 00:01:08,717 But the note in D four blocks that rectangle, so Excel should report #SPILL! in D two. 18 00:01:08,717 --> 00:01:13,075 Do not copy the formula down; a dynamic array uses one anchor cell. 19 00:01:13,675 --> 00:01:15,522 Click the cell with #SPILL! 20 00:01:15,522 --> 00:01:19,586 and inspect the dashed border showing where results want to go. 21 00:01:19,586 --> 00:01:24,019 Microsoft's help says the error alert can offer Select Obstructing Cells 22 00:01:24,019 --> 00:01:25,496 for a blocked range. 23 00:01:25,496 --> 00:01:29,560 Use that command when available; if your Excel surface does not 24 00:01:29,560 --> 00:01:32,146 show it, inspect the outlined range manually. 25 00:01:32,146 --> 00:01:35,470 In this exercise, D four is the occupied cell. 26 00:01:35,470 --> 00:01:37,687 Other error messages need different fixes. 27 00:01:38,287 --> 00:01:41,799 The note in D four belongs to the practice record. 28 00:01:41,799 --> 00:01:45,662 Cut that cell and paste it in F four, outside the 29 00:01:45,662 --> 00:01:48,472 intended D two through D five result range. 30 00:01:48,472 --> 00:01:53,389 Confirm F four still reads KEEP: supervisor note before you consider the obstruction cleared. 31 00:01:53,389 --> 00:01:56,199 Excel should recalculate automatically when the range opens. 32 00:01:56,199 --> 00:02:00,063 If you are working on a real file, first check whether 33 00:02:00,063 --> 00:02:02,521 the obstructing entry feeds any other work. 34 00:02:03,121 --> 00:02:07,187 Now inspect D two through D five in order: Intake, Review, 35 00:02:07,187 --> 00:02:08,665 Approved, and On hold. 36 00:02:08,665 --> 00:02:13,100 Review appears twice in the source, but UNIQUE should list it once. 37 00:02:13,100 --> 00:02:17,165 Compare those four cells with the independent answer check and verify 38 00:02:17,165 --> 00:02:20,122 that F four still contains the original note. 39 00:02:20,122 --> 00:02:22,709 If a different status appears or #SPILL! 40 00:02:22,709 --> 00:02:27,513 remains, check the source range and every cell in the outlined output rectangle. 41 00:02:28,113 --> 00:02:31,688 A blocked cell is only one cause of #SPILL!. 42 00:02:31,688 --> 00:02:35,659 Microsoft also documents merged cells, formulas inside Excel Tables, and 43 00:02:35,659 --> 00:02:38,837 output ranges that run past the sheet edge. 44 00:02:38,837 --> 00:02:42,411 Follow the error detail instead of clearing unknown data. 45 00:02:42,411 --> 00:02:46,780 In this exercise, the result is four unique statuses in D2 46 00:02:46,780 --> 00:02:50,354 through D5 and the original note preserved in F4. 47 00:02:50,354 --> 00:02:53,531 If both checks pass, the obstruction is resolved.