EXCEL: SIMPLIFY PRINTING WITH RANGES
Last week I was working on a spreadsheet for a friend of mine, trying to compare the costs of two health plans. With myriad options, deductibles and scenarios presented by the insurance company the spreadsheet quickly grew to be really ugly. All I wanted to print was just a few rows of data, plus some headings so my friend could make a decent evaluation. I also wanted to print a different set of cells for my own use.
Each time I wanted to print my selected data I found I had to individually select the cells for my friend or the cells I wanted to review. Things got even more tedious because many of the cells weren't contiguous – that is, I needed a row here, a few cells there, and so on. What a pain!
Well, I found a much easier way to switch between the sets of cells for printing: I used named ranges. A named range is a set of cells to which you assign a name. The range name (and the cells it refers to) is stored as part of your worksheet. (Ranges offer a host of benefits besides easier printing, as we'll see next week.)
The cells in a range can be contiguous or non-contiguous. In my case, I wanted to print cells A1 through D5 and C25 through H25 for my friend, so I called this range Final. I created a different range named Analysis for the cells I wanted to print for my own review.
Creating a Range
To create a range, choose the cells you want to include. I first selected cells A1 through D5 (you can do this with the mouse or from the keyboard). Then I held down the Ctrl key and selected the remaining cells (C25:H25). From the main menu select Insert, Name, Define. Enter a range name in the "Names in Workbook" text box (I entered Final) and select OK.
Your range name can be up to 255 characters long, but the first character must be a letter or the underscore character. The rest of your range name can consist of letters, numbers, periods, and underscores. Spaces, however, are NOT allowed. Had I wanted to call the range Final Costs I would have to settle for something like Final_Costs or Final.Costs.
To print the named range, be sure the range is selected. In the upper left corner of your screen you'll see a small box that usually displays the name of the currently selected cell. To select your named range, click on the down-pointing arrow to the right of this box and select the range name from the drop-down list. (If your name is short, you can also enter the range name directly into the box.) Excel highlights your range automatically.
From the main menu, choose File, Print. In the Print dialog box choose Selection in the Print What area.
You'll notice a couple of things where printing is concerned.
- Print Preview doesn't show just your selected range unless you set the range to be the Print Area. To do this, choose the range and then use the main menu and choose File, Print Area, Set Print Area. If you're working with a brand new sheet, Print Preview shows you the entire worksheet if no print area has been selected.
- If your range includes non-contiguous cells, the Print command will print a new page for each contiguous group of cells. Thus, in this example, printing the Final range resulted in two printed pages. To date I have found no simple way to combine non-contiguous groups of cells into a single printed page.
Here is a list of shortcut keys in Microsoft Excel.
