Excel 的网格状外观有时看起来是静态的,无法创建自定义界面来满足存储在工作表中的数据的需求。
尽管这在某种程度上是正确的,但Microsoft已在(Microsoft)Excel中内置了即时自定义列宽和行高以匹配单元格中数据大小的能力。
了解如何使用 Excel 的AutoFit 功能(AutoFit feature)自动将列和行的大小更改为工作表中数据的大小。
在Excel中更改列宽和行高之前(Column Widths and Row Heights)
在Excel中创建(Excel)列和行(columns and rows)的大小是有限制的。值为 0 的列宽和行高隐藏在(Column)Excel 工作表(Excel worksheet)中。这相当于使用隐藏列或隐藏行功能(column or hide row feature)。
列的最大宽度可以是 255。这个数字表示在标准(Columns)字体大小(font size)下每列可以容纳的最大字符数。
更改字体、字体大小(font size)以及向字体添加其他特征(例如斜体和粗体)会大大减少一列可以容纳的最大字符数。Excel 列(Excel column)的默认大小(default size)为8.43,对应于 64 像素。
行的最大高度可以是 409。这个数字表示行可以容纳多少 1/72 英寸。Excel 行(Excel row)的默认大小(default size)为15,对应于 20 像素或大约 1/5 英寸。
使用 Excel 的自动调整功能
假设(Suppose)在Excel 工作表中, (Excel worksheet)A1单元格中的文本超出了 8.43(64 像素)默认列宽(column width)。请注意(Notice),虽然列的宽度不能包含文本的长度,但Excel允许文本溢出到相邻的列中。
选择A 列(Column A),单击功能区上的“(Ribbon)主页”(Home)选项卡,然后找到功能区(Ribbon)中几乎一直到右侧标记为“单元(Cells)”的部分。
单击标题为Format的按钮并找到菜单中标有Cell Size的部分。请注意,有三个选项可用于更改列的宽度。
列宽(Column Width)- 此选项允许您通过键入数字手动更改列宽。此选项(option isn)没有用,因为您只需将列拖动到所需大小即可更轻松地完成相同的操作。
AutoFit Column Width – 这是我们想要的选项。此选项将根据占用最多空间的列中单元格内容的长度将列的大小更改为新大小。
默认宽度(Default Width)——这个选项(option doesn)实际上不会改变任何列的宽度;它只是更改特定工作表的列的默认宽度。(default width)
在Format按钮的菜单上,选择AutoFit Column Width并注意A 列(Column A)的宽度已更改为包含A1单元格中文本的长度。
请注意,您还可以通过简单地选择一列或多列(column or columns)然后双击任何选定列的右侧边界来自动调整列。
如果您想将所有列宽重置为默认宽度(default width),只需选择格式 - 默认宽度(Format – Default Width)并输入8.43。您还可以使用键盘快捷键自动调整(keyboard shortcut)Excel中的列。
只需按ALT + HALT 和 H(ALT and H)后,您可以松开它们。所以完整的组合键(key combination)是ALT + H + O + I。
与列宽不同,Excel会自动调整行高以适应每行中占据最大垂直空间的文本的高度。
因此, “格式”按钮上的(Format)“自动调整行高”(AutoFit Row Height)功能不如“自动调整列宽”(AutoFit Column Width)功能有用。
将数据粘贴到Excel 工作表(Excel worksheet)时,有时行高不会自动调整。发生这种情况时,文本将被截断,而不是溢出到上方或下方的另一行。
要解决此问题,请选择带有截止文本的行,然后单击(cut-off text and click)Format Format > AutoFit Row Height。这将强制Excel更改行高以适应异常高的文本。
对于行,您可以使用非常相似的键盘快捷键(keyboard shortcut) ALT + H + O + A来自动调整行高。
使用 Excel 的AutoFit功能,您可以自动更改工作表中的列宽和行高,以适应不同大小的文本,而无需手动更改宽度和高度值(width and height values)。
AutoFit功能可实时节省时间,还可以使您的工作表中的数据更易于访问和阅读(access and read)。享受!
AutoFit Column Widths and Row Heights in Excel
Exсеl’s grid-like appеarance can seem static at times without the possibility of creating customized interfaces tо mаtch the needs of the data stored in a worksheet.
Although this is true to some extent, Microsoft has built into Excel the ability to instantly customize column widths and row heights to match the size of the data in the cells.
Learn how to use Excel’s AutoFit feature to automatically change the size of columns and rows to the size of the data in a worksheet.
Before You Change Column Widths and Row Heights in Excel
There are limits to how big and small you can make columns and rows in Excel. Column widths and row heights with a value of zero are hidden in an Excel worksheet. This is the equivalent to using the hide column or hide row feature.
Columns can have a maximum width of 255. This number represents the maximum number of characters a column can hold at the standard font size.
Changing the font, the font size, and adding other characteristics to the font such as italics and bolding greatly reduce the maximum number of characters a column can hold. The default size of an Excel column is 8.43, which correlates to 64 pixels.
Rows can have a maximum height of 409. This number represents the how many 1/72nds of an inch the row can hold. The default size of an Excel row is 15, which correlates to 20 pixels or about 1/5th of an inch.
Using Excel’s AutoFit Feature
Suppose in your Excel worksheet you have text in the A1 cell that extends beyond the 8.43 (64 pixels) default column width. Notice that although the column’s width cannot contain the length of the text, Excel allows the text to spill over into adjacent columns.
Select Column A, click on the Home tab on the Ribbon, and locate a section of the Ribbon almost all the way to the right labeled Cells.
Click on the button titled Format and locate a section of the menu labeled Cell Size. Notice that there are three options for changing the width of a column.
Column Width – This option lets you manually change the width of a column by typing in a number. This option isn’t useful because you can more easily accomplish the same thing by simply dragging the column to your desired size.
AutoFit Column Width – This is the option we want. This option will change the size of the column to a new size depending on the length of the contents of the cell in the column that takes up the most space.
Default Width – This option doesn’t actually change the width of any columns; it simply changes the default width of the columns for a particular worksheet.
On the Format button’s menu, select AutoFit Column Width and notice that the width of Column A has changed to contain the length of the text in the A1 cell.
Note that you can also autofit a column by simply selecting the column or columns and then double-clicking on the right-hand boundary of any selected column.
If you want to reset all column widths back to the default width, just select Format – Default Width and type in 8.43. You can also use a keyboard shortcut to autofit columns in Excel.
Just press ALT + H and then press O and then I. You can let go ALT and H once you have pressed them. So the full key combination is ALT + H + O + I.
Unlike column widths, Excel automatically adjusts the height of a row to accommodate the height of the text that takes up the most vertical space in each row.
Therefore, the AutoFit Row Height feature found on the Format button is not as useful as the AutoFit Column Width feature.
When pasting data into an Excel worksheet, there are times when the row heights do not adjust automatically. When this happens, text will be cut off rather than spill over into another row either above or below.
To fix this, select the row with the cut-off text and click on Format > AutoFit Row Height. This will force Excel to change the height of the row to accommodate the unusually tall text.
For rows, you can use a very similar keyboard shortcut of ALT + H + O + A to autofit row heights.
Using Excel’s AutoFit feature you can automatically change the width of columns and the height of rows in a worksheet to accommodate different sized text without having to manually change the width and height values.
A real time saver, the AutoFit feature can also make data in your worksheet easier to access and read. Enjoy!