correction: numberFormat can be applied to unbounded ranges if the formatting is a 1x1 array #5400
Description
Type of issue
Outdated article
Feedback
Hi, this document claims that one can not set formatting on unbounded ranges:
You cannot set cell-level properties such as values, numberFormat, and formula on an unbounded range because the input request is too large.
However, I'm able to do so whenever we are broadcasting a 1x1 array, this is verified on Windows Excel desktop, Mac Excel desktop, and web Excel
// =========================================================================
// TEST B: 1x1 array to unbounded
// =========================================================================
console.log("\nTEST B: Assign 1x1 ARRAY to unbounded range");
try {
const range2 = sheet.getRange("A:A");
range2.numberFormat = [["$#,##0.00"]]; // 1x1 array
await context.sync();
// Verify it actually applied
const check = sheet.getRange("A1:A5");
check.load("numberFormat");
await context.sync();
console.log(" ✓ 1x1 array assignment worked");
console.log(" Applied formats:", JSON.stringify(check.numberFormat));
const hasFormat = check.numberFormat[0][0].includes("$") ||
check.numberFormat[0][0].includes("#,##0");
if (hasFormat) {
console.log("CONFIRMED: Format was actually applied!");
} else {
console.log("WARNING: No error but format not applied");
}
} catch (e) {
console.log("1x1 array assignment failed:", e.message);
}
What I'm not able to do is is set it for an unbounded range with anything more than a 1x1 array, i.e. a 1x2 array fails. So I'm hoping the team can help verify whether this is expected behavior and the documentation is incomplete, or if this is unexpected behavior and nobody should use it because it will go away with a bug fix? Here's a scriptlab script that can show this in action:
Office.onReady(() => {
document.getElementById("run").onclick = run;
});
async function run() {
try {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
// Clear first
const usedRange = sheet.getUsedRangeOrNullObject();
await context.sync();
if (!usedRange.isNullObject) {
usedRange.clear();
await context.sync();
}
console.log("=== NUMBER FORMAT BROADCASTING TEST ===\n");
// Results table at top
sheet.getRange("A1:E1").values = [["Test", "Range", "Array", "Status", "Notes"]];
sheet.getRange("A1:E1").format.font.bold = true;
const results = [];
// ALL TESTS IN ROW 10, LEFT TO RIGHT IN ORDER
// TEST 1: B-C columns
console.log("TEST 1: Bounded");
sheet.getRange("A10").values = [["TEST 1"]];
sheet.getRange("B10:C14").values = [[10, 20], [11, 22], [12, 23], [13, 24], [14, 25]];
try {
sheet.getRange("B10:C14").numberFormat = [["$#,##0.00"]];
await context.sync();
results.push(["1", "B10:C14", "1x1", "PASS", "Bounded"]);
console.log(" ✓ PASS\n");
} catch (e) {
results.push(["1", "B10:C14", "1x1", "FAIL", e.message.substring(0, 20)]);
console.log(" ✗ FAIL\n");
}
// TEST 2: E column
console.log("TEST 2: Unbounded E:E");
sheet.getRange("D10").values = [["TEST 2"]];
sheet.getRange("E10:E14").values = [[50], [55], [60], [65], [70]];
try {
sheet.getRange("E:E").numberFormat = [["$#,##0.00"]];
await context.sync();
const v = sheet.getRange("E10");
v.load("numberFormat");
await context.sync();
if (v.numberFormat[0][0].includes("$")) {
results.push(["2", "E:E", "1x1", "PASS", "Unbounded 1col"]);
console.log(" ✓ PASS\n");
} else {
results.push(["2", "E:E", "1x1", "FAIL", "Not applied"]);
console.log(" ✗ Not applied\n");
}
} catch (e) {
results.push(["2", "E:E", "1x1", "FAIL", e.message.substring(0, 20)]);
console.log(" ✗ FAIL\n");
}
// TEST 3: G-I columns
console.log("TEST 3: Unbounded G:I");
sheet.getRange("F10").values = [["TEST 3"]];
sheet.getRange("G10:I14").values = [[80, 90, 100], [81, 91, 101], [82, 92, 102], [83, 93, 103], [84, 94, 104]];
try {
sheet.getRange("G:I").numberFormat = [["$#,##0.00"]];
await context.sync();
const v = sheet.getRange("G10");
v.load("numberFormat");
await context.sync();
if (v.numberFormat[0][0].includes("$")) {
results.push(["3", "G:I", "1x1", "PASS", "Unbounded 3cols"]);
console.log(" ✓ PASS\n");
} else {
results.push(["3", "G:I", "1x1", "FAIL", "Not applied"]);
console.log(" ✗ Not applied\n");
}
} catch (e) {
results.push(["3", "G:I", "1x1", "FAIL", e.message.substring(0, 20)]);
console.log(" ✗ FAIL\n");
}
// TEST 4a: K-L columns (1x2 SHOULD FAIL)
console.log("TEST 4a: K:L + 1x2 (expect FAIL)");
sheet.getRange("J10").values = [["TEST 4a"]];
sheet.getRange("K10:L14").values = [[110, 120], [111, 121], [112, 122], [113, 123], [114, 124]];
try {
sheet.getRange("K:L").numberFormat = [["$#,##0.00", "$#,##0.00"]];
await context.sync();
results.push(["4a", "K:L", "1x2", "PASS", "UNEXPECTED!"]);
console.log(" ✓ UNEXPECTED!\n");
} catch (e) {
results.push(["4a", "K:L", "1x2", "FAIL", "Expected"]);
console.log(" ✗ FAIL (expected)\n");
}
// TEST 4b: N-O columns separately
console.log("TEST 4b: N:N and O:O separately");
sheet.getRange("M10").values = [["TEST 4b"]];
sheet.getRange("N10:O14").values = [[200, 210], [201, 211], [202, 212], [203, 213], [204, 214]];
try {
sheet.getRange("N:N").numberFormat = [["$#,##0.00"]];
sheet.getRange("O:O").numberFormat = [["$#,##0.00"]];
await context.sync();
results.push(["4b", "N:N, O:O", "two 1x1", "PASS", "Workaround"]);
console.log(" ✓ PASS\n");
} catch (e) {
results.push(["4b", "N:N, O:O", "two 1x1", "FAIL", e.message.substring(0, 20)]);
console.log(" ✗ FAIL\n");
}
// TEST 5a: Q-R union with getRange() (SHOULD FAIL)
console.log("TEST 5a: Union getRange() (expect FAIL)");
sheet.getRange("P10").values = [["TEST 5a"]];
sheet.getRange("Q10:R11").values = [[300, 310], [301, 311]];
sheet.getRange("Q13:R14").values = [[320, 330], [321, 331]];
try {
sheet.getRange("Q10:R11,Q13:R14").numberFormat = [["$#,##0.00"]];
await context.sync();
results.push(["5a", "Union", "getRange", "PASS", "UNEXPECTED!"]);
console.log(" ✓ UNEXPECTED!\n");
} catch (e) {
results.push(["5a", "Union", "getRange", "FAIL", "Expected"]);
console.log(" ✗ FAIL (expected)\n");
}
// TEST 5b: T-U union with getRanges()
console.log("TEST 5b: Union getRanges()");
sheet.getRange("S10").values = [["TEST 5b"]];
sheet.getRange("T10:U11").values = [[400, 410], [401, 411]];
sheet.getRange("T13:U14").values = [[420, 430], [421, 431]];
try {
const ranges = sheet.getRanges("T10:U11,T13:U14");
ranges.format.numberFormat = [["$#,##0.00"]];
await context.sync();
// VERIFY
const v = sheet.getRange("T10");
v.load("numberFormat");
await context.sync();
if (v.numberFormat[0][0].includes("$")) {
results.push(["5b", "Union", "getRanges", "PASS", "Workaround"]);
console.log(" ✓ PASS - verified\n");
} else {
results.push(["5b", "Union", "getRanges", "FAIL", "Not applied"]);
console.log(" ✗ Not applied!\n");
}
} catch (e) {
results.push(["5b", "Union", "getRanges", "FAIL", e.message.substring(0, 20)]);
console.log(" ✗ FAIL\n");
}
// Write results
sheet.getRange(`A2:E${1 + results.length}`).values = results;
sheet.getRange(`A1:E${1 + results.length}`).format.autofitColumns();
await context.sync();
console.log("=".repeat(70));
console.log("Check Excel: All test data in row 10");
console.log(" Reading left to right: TEST 1, 2, 3, 4a, 4b, 5a, 5b");
});
} catch (error) {
console.error("Test failed:", error);
}
}
Page URL
https://learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-ranges-unbounded
Content source URL
Author
Document Id
acdb3a2e-752f-c8b6-07b4-a3682bc59a9e
Platform Id
1d5932eb-0189-c3a6-4309-29eed712c7d2