id="info"

Software

Reclaim space in Microsoft Excel by changing text alignment

Having just a column or two extend past the right edge of the screen is annoying. A simple way to reclaim text is to angle column labels.

My Microsoft Excel users make many of the same requests, and one thing I hear often is I want to see my whole spreadsheet in one screen. Scrolling to the right to view just a column or two annoys some people. I happen to be one of these people, and I extend that irritation to web sites that force me to scroll just a tad to see additional information or click a button to continue. So, I empathize with this particular request.

Now, you can't help a user who has dozens of columns. You can create ranges and show them how to use F5 to quickly access a specific area without scrolling—your users will love you for it. Unfortunately, you're never going to get that much data on screen in a readable format.

For those with smaller spreadsheets, there are several ways to reclaim space, but they all require specialized tweaking and that takes time and an expertise your users might not have. In addition, if users are dealing with limiting conventions, you might have to alter the sheet yourself. On the other hand, if you're looking for a quick fix that users can apply themselves, show them how to angle their header labels.

First, ask the user if the heading labels are considerably longer than the actual data. Often, we waste screen space on displaying these long headings. There are at least two traditional, but time-consuming methods for reclaiming some of that wasted space:

  • You can reduce the size of the heading labels, but you have to visit each one and often, you really can't change the headings enough to warrant that route.
  • You can wrap the heading labels, but that only works if the headings are comprised of multiple words and even then, this solution only goes so far.

Both methods are hit or miss—you can't guarantee the results. Users could spend a lot of time experimenting and still not accomplish their goal.

An easier solution that usually satisfies with little effort is to angle the header text. This method works when the header labels are significantly longer than the actual text. You reduce the column widths to accommodate the data and angle the header labels, accordingly.

At 100% zoom, the following spreadsheet extends off-screen. Also notice that the headings are all single words so they shouldn't wrap. Keeping the labels intact would require even wider columns. Your first thought might be to use the Auto-Fit column width, but the resulting column widths will be inconsistent, which is a visual distraction in and of itself. (Of course, in this simple example, the obvious solution is to abbreviate the months, but for the sake of the example, please play along—that won't always be the case.)

The first step is to reduce the column size. A column width of 4 is adequate and it certainly gets all the data on screen. (Make sure that the column width is large enough to accommodate any possible value.) As you can see, the header labels look even worse than they did before, but that's quickly fixed.

Once you've reduced the column width, you're ready to angle the headings as follows:

  1. Select the header values (A1:M1).
  2. Right-click the selection and choose Format Cells.
  3. Click the Alignment tab. By default, the Degree orientation is 0%.
  4. Change the Degree setting to 65. Between 45 and 85 usually yields the best results. The highest setting is 90. Lower settings require a larger column width.
  5. Click OK.

The result is still a bit of a mess, but easily fixed. Just drag the row header cell down until the row height setting is large enough to display the text.

There's one more benefit—you can increase the zoom and still see everything on one screen. At 100%, you couldn't see December. Now, not only can you see all the data, you can still see it all at 150%. (That matters to my old eyes!)

Angling header text is an easy way to reclaim a bit of screen space and it doesn't hurt any that it looks cool. In addition, this is something you can train users to do for themselves.

TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks