Wrangling work sheets: Up, down, all around in Excel

by on
in Excel Training,Office Technology

Sizing rows and columns

One of the most common frustrations in Excel is sizing rows and columns accurately. Columns are sometimes not wide enough to show all the numbers, which is what all those # symbols mean. Or columns might be too wide, rows too short or too tall.

Straightening this out could mean a lot of clicking, dragging and adjusting. But a simple double-click in the column’s right edge will make the column adjust to fit all the data in it. To right fit all the columns on a work sheet, click the cornerstone, which selects every cell on the work sheet, and then double-click between any two column headers. The same technique works with the rows: Click the cornerstone, and then double-click between any two rows.

To make all the columns a uniform size, select all the columns you want the same width and adjust the size of one. All the other selected columns will be the same size. A similar trick with rows gives the illusion of double spacing without actually inserting blank rows. Select the rows, and just click and drag one to the right height.

Splitting workbooks

The next tip for wrangling work sheets will be of special interest to those who already write formulas and need to access information on other work sheets in the same workbook. Toggling back and forth between work sheets can be tedious and makes it too easy to make mistakes. An easier way would be to show both work sheets.

From the View tab and the Window group, click on the New Window button.  Then, click on the Arrange All button. Choose whichever format will work best for you. Vertical will allow you to have one work sheet on the right and one on the left. At first, it shows the same work sheet on both sides of the screen. Just select a different work sheet, and now you have two different work sheets from the same workbook. You can now begin your formula on the left and simply click on the right to include a value from that work sheet.  

Hint: You can do this between two workbooks as well. Just open both and click on Arrange All, then Vertical.

Workbook navigation

For example, say you’re working with a 12-month workbook that stores budget data for a year. What if you just want to look at December? You could use the scroll arrows or scroll bar at the bottom until you get to it. Or, you could just right-click, select December and, with a click, navigate there. Right-click and go back to January, as easily. This is much faster and less frustrating than scrolling through really big workbooks.

Leave a Comment