"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

AutoFit Columns Based On Selected Cells Only


You may already know how to easily adjust column widths based on the contents of the column. Just double-click on the border between two column headings and the column will automatically adjust its width based on the widest entry in that column.

However, often you will find that you also have a report heading occupying a cell in the same column you want to adjust. The problem is, when you double-click, the column adjusts to fit the report heading which is usually much wider than the data in the cells below. Most users will then resort to manually adjusting the column width by dragging the border of the column heading.

What most Excel users are not aware of is that there is a way to adjust column widths to fit your data but ignore data in other cells in that same column. It's called AutoFit Selection.

1) Select just the cells you want to base the column width on. 2) From the Format menu select Column, AutoFit Selection

The column width will adjust to fit the widest entry in the selected cells only. Note also that this tip applies similarly to row heights.

(Extra Tip) How To Use Named Ranges

Another underused feature of Excel is Named Ranges. Named Ranges allow you to assign a meaningful name to a cell or range of cells and then use that name instead of the actual cell reference in your formulas. A name is easier to remember when you are constructing your formulas.

For example, assume you have a workbook that contains a lot of formulas that refer to a tax rate. You could simplify things by using one cell to store the tax rate, naming that cell 'TaxRate' and then, instead of using a cell reference in your formulas, you would use the name 'TaxRate'.

Try this:

  1. Enter 45% (tax rate) in cell B5.
  2. To name the cell, select Insert, Name, Define.
  3. Type 'TaxRate' and click OK.
  4. Now, in cells C10:C15 enter some numbers.
  5. In cell D10 enter the formula =C10*TaxRate and copy it down to cells D11:D15.
Using Named ranges helps you to create easier to understand and more organized workbooks. Now whenever you need to change the tax rate, you just change it in one cell and all dependent formulas are instantly updated.


Why not print (CTRL+P) this tip and share it with your friends and associates?

Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
You'll be amazed how much time you'll save just by learning a few of these tips.

Copyright 2003 All Rights Reserved by Francis Hayes (The Excel Addict)

If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.