Excel刷新与数据源扩展:更改数据源、刷新时保留格式

日常工作中，用Excel做数据分析最让人头疼的场景之一，莫过于辛辛苦苦调整好表格格式，一点“刷新”，列宽变了、字体变了、底纹没了，一切回到解放前。更麻烦的是，数据源每月都在增长，从几百行变成几千行，每次刷新还得手动改范围。

这篇文章就围绕两个核心问题展开：数据源怎么跟着数据一起“长大” ，以及刷新时怎么让格式纹丝不动。

一、数据源扩展：让Excel自动识别新增数据

问题根源

很多人的数据源是一个固定的单元格区域，比如 $A$1:$F$500。当数据增加到第501行时，数据透视表或查询依然只认前500行，新增数据被“视而不见”。每次都要手动去“更改数据源”里改范围，效率极低。

方案一：把普通区域变成“超级表”

这是最推荐的做法。选中数据区域任意单元格，按 Ctrl+T 将其转换为Excel表（超级表）。超级表有一个关键特性：当你在表格下方紧接着输入新数据时，它会自动扩展范围。

转换之后，再以这个表为数据源创建透视表或图表。此后新增数据只需刷新，透视表会自动识别扩展后的区域，完全不需要手动改范围。

方案二：手动更改数据源（应急用）

如果数据源不是超级表，或者需要切换到另一个完全不同的数据区域，可以手动操作：

• 点击数据透视表内任意单元格

• 在“数据透视表分析”选项卡中，点击 “更改数据源”

• 在弹出的对话框中重新框选或输入新的区域范围

• 确定后右键刷新即可

这种方法适合一次性切换数据源，比如从“1月数据”切到“2月数据”且格式结构完全不同时使用。

二、刷新时保留格式：分场景设置

格式丢失的原因因数据源类型而异，下面分三种常见场景给出对应设置。

场景A：数据透视表刷新后格式消失

这是最常见的“格式灾难”。症状是刷新后加粗消失、字体颜色变回默认、列宽被自动调整。

解决方法：

右键点击数据透视表任意位置，选择 “数据透视表选项” ，进入 “布局和格式” 选项卡，做两件事：

1. 勾选“更新时保留单元格格式” —— 如果已经勾选，先取消再重新勾选一次（有时选项状态会“卡住”）

2. 取消勾选“更新时自动调整列宽” —— 这一项是列宽被重置的元凶

设置完成后，之前手动调整的列宽、字体、底纹在刷新后都会保留。

场景B：Power Query / 外部数据连接刷新后格式丢失

通过Power Query或“数据→获取数据”加载到工作表的表格，刷新时格式丢失的解决位置在“外部数据属性”中。

解决方法：

在加载后的表格区域右键，选择 “表格” → “外部数据属性” （或在“表设计”选项卡中点击“属性”），在弹出的窗口中：

• 取消勾选 “调整列宽”

• 勾选 “保留单元格格式”

• 勾选 “保留列排序/筛选/布局”

这三项设置对数据透视表、超级表、普通查询表都同样适用。

场景C：格式顽固性丢失，连设置都不管用

少数情况下，即使做了上述设置，刷新后格式仍然被重置。这通常有两个原因：

• 工作表处于保护状态：格式保留需要工作表可编辑。先取消保护，刷新一次后再重新保护。

• 在筛选或折叠状态下应用了格式：建议在数据完全展开、无筛选的状态下设置格式，否则刷新时新暴露的单元格会使用默认格式，覆盖原有设置。

如果以上方法都无效，可以考虑用VBA宏作为“终极保障”：录制一个“刷新+重新应用格式”的宏，以后每次用宏来执行刷新。虽然不够优雅，但确实能解决所有格式问题。

三、一套“防丢格式”的配置清单

总结一下，在开始使用数据透视表或Power Query之前，花两分钟做好这几项设置，后续可以省下大量重复调整格式的时间：

数据源层面：

• 普通区域 → Ctrl+T 转为超级表，实现自动扩展

数据透视表层面：

• 透视表选项 → 布局和格式 → 勾选“更新时保留单元格格式”

• 同一位置 → 取消勾选“更新时自动调整列宽”

外部数据/查询表层面：

• 外部数据属性 → 取消“调整列宽”，勾选“保留单元格格式”和“保留列排序/筛选/布局”

设置一次，长期受益。下次再点刷新的时候，数据安心更新，格式原封不动。