Office hacks: Excel tables in the graphic limit techniques

In Excel spreadsheets, sometimes we will encounter the embarrassment of changing the position of the chart or text. For example, we want to insert a chart in a fixed position, but after changing the width of a column, the chart also moved position. For example, when we add more text to the cell, the text will overflow the current cell, covering the contents of the adjacent cells to display. Also, the default width of a column or all columns in the table is not appropriate, always need to manually adjust. All of these we can solve through the limit settings in Excel. The following is an example of the operation in Excel 2019 to illustrate.

1. lock the chart position regardless of cell changes

Right-click on the outer frame of the chart in order to select the chart (note that you are not right-clicking on other components of the chart). Select the “Set Chart Area Format” option from the pop-up menu (Figure 1).


In the “Set Chart Area Format” dialog panel that then pops up, click the third icon to switch to the “Size and Properties” tab. Then expand the “Properties” option, and select “Do not change position and size with cells” from the list of options (Figure 2).


2. Restrict the display in the cell to prevent cell text overflow

Text overflow in cells can disrupt the visual or create an imperfect visual effect. We can solve each case separately depending on the situation.

If the text overflow is due to a column or a few non-consecutive columns of inappropriate width, simply move the mouse cursor to the dividing line of the two column headings, press and drag to adjust to the desired width.

If the table has a number of consecutive columns of the width is not appropriate, manually adjust one by one is more troublesome, you can first select the cell area to operate, and then switch to the “Start” tab, and select “Cell → Format → Automatic adjustment of column width” (Figure 3). In this way, all columns will be immediately and automatically adjusted to the most appropriate width.


The column width of Excel blank table is fixed. When we want to fill in the blank table of the maximum width of the cell content is a definite data, you can set the default cell width to reduce the workload of table adjustment. We just need to select the “Default Column Width…” in the above “Format” menu, and then in the pop-up “Standard Column Width” dialog box, the standard column width to the desired value (Figure 4).


In addition, if you do not want to increase the width of the table, but to display the contents of the cell in full, just right-click on the cell selection and select “Set Cell Format”, followed by a pop-up window to switch to the “Alignment” tab, select “automatic line feed” option and OK (Figure 5).



If the row height is also inappropriate, the adjustment method is similar to the adjustment method of column width.



Leave a Comment