Software

10 steps to beating Excel's character limit for headers and footers

Excel restricts the amount of text you can place into its headers and footers. Here's a quick way to outfox it.

Excel headers and footers are a great place to display page numbers, titles, an author's name, and so on. However, there is a limit to the number of characters the header or footer will accommodate. Excel will reject an entry of more than 255 characters. Most of the time, the limit isn't a problem. But when you run into a situation that requires more text, you can insert it as a graphic in 10 easy steps. We'll demonstrate the process by inserting a lengthy disclaimer in a worksheet footer.

Note: This article is also available as a PDF download.

1: Acquire the limit-busting text

It's hard to imagine a header of footer with more than 255 characters, but they're more common than you might think. For instance, the following text is a standard warning that many companies tack on to all their business documents:

The information contained in this document is intended only for the person or entity to which it is addressed and may contain proprietary and/or sensitive material. If you are not the intended recipient, please destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.

At 435 characters, without spaces, this disclaimer won't fit.

2: Open an existing sheet as your guide

It's best to open the sheet in which you'll actually use the footer so you can use the existing margins and formats as a guide. If the workbook doesn't exist yet, use one that closely represents your goal, even if that means creating a quick mockup. This technique isn't an exact science.

3: Change the view

It's easiest to work with the sheet in Page Break View so you can fit the text between the left and right margins. To switch to this view, choose Page Break View from the View menu. Figure A shows the sheet's appropriate page and margin breaks.

Figure A

Switch to Page Break View so you can position the text between the margins.

4: Add a text box to the sheet

The next step is to add a text box, in which you'll enter the footer text. Insert the text box as follows:

  1. Display the Drawing toolbar by choosing Toolbars from the View menu and then checking Drawing.
  2. Click the Text Box tool.
  3. Insert the box as shown in Figure B. At this point, you're just guessing so don't stress over its exact size.

Figure B

Insert the text box using the Text Box tool.

It doesn't matter where you position the text box, but inserting it just above the page break lets you see how much room the text consumes. You might want to adjust the width of the box to fit within the sheet's right and left margins. Don't worry about the text underneath. You'll delete the text box later.

5: Enter the footer text and adjust for size

Once the text box is positioned, enter the footer text. Type it or paste it from another document. Increase or decrease the size of the text box as necessary. Figure C shows a text box that includes a few blank lines at the top to provide white space between the data and the footer.

Figure C

Resize the text box so you can see all the text.

6: Format the text and text box

At this point, you're ready to format the text box and its text. Figure D shows the footer text in bold italics and aligned to the left margin. The font is Abadi MT Condensed, size 8. Experimenting will render the best results unless you have specific constraints. Toggling between Page Break View and Print Preview is helpful to get the right placement and size.

Figure D

Apply the necessary formats to the text.

You might want to inhibit the text box's border and fill color as well:

  1. Right-click the text box and choose Format Text Box.
  2. Click the Colors And Lines tab.
  3. In the Fill section, choose No Fill from the Color drop-down list.
  4. In the Line section, choose No Color from the Color drop-down list.
  5. Click OK.

7: Paste the text box into Paint

Now you can turn the text into a graphic using Windows Paint (but you can use any graphics program). First, copy the text box by selecting it and pressing [Ctrl] + C. Then, launch Paint (it's in Windows Accessories).

In Paint, resize the background field to the approximate size of the text box. (This step may not be necessary in another program.) Simply grab any of the small circles along the field's border and drag to resize it.

When Paint's background field is the right size, paste the text box control into Paint by pressing [Ctrl] + V or choosing Paste from the Edit menu. Figure E shows the text in Paint with a similarly sized background field. If Paint's field isn't the right size, press [Ctrl] + Z to undo the paste. Then, resize the field and try again.

Figure E

Paste the text box into Paint so you can save the text as a graphic file.

8: Save the text box as a graphic file

In Paint, you can continue to alter the text box, but for now, just save it as a graphic file as follows:

  1. From the File menu, choose Save As.
  2. Navigate to the folder where you want to save the graphic file.
  3. Name the file.
  4. Choose a graphic format from the Save As Type list.
  5. Click Save.

9: Create the footer in Excel

Now, return to Excel and insert the graphic:

  1. Choose Header And Footer from the View menu.
  2. Click Custom Footer.
  3. Click a section.
  4. Click the Insert Picture button (the second from the right).
  5. Locate the graphic file you saved in step 8.
  6. Select the file and click Save. Excel will display a code for the picture, as shown in Figure F.
  7. Click OK twice.

Figure F

Insert the graphic file as a picture into the footer.

10: View the footer

Move the text box to an unused sheet; you might want to reuse it. Click Print Preview to view the footer, shown in Figure G. The graphic file you created in Paint appears as text. Excel interprets the file as a single picture -- so there's nothing to count! If the footer covers data at the bottom of the page, just adjust the sheet's bottom margin.

Figure G

By inserting a picture of the footer text, you can overcome the footer's 255-character limit.


Check out 10 Things... the newsletter

Get the key facts on a wide range of technologies, techniques, strategies, and skills with the help of the concise need-to-know lists featured in TechRepublic's 10 Things newsletter, delivered every Friday. 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.

9 comments
cbosse
cbosse

Since I'm a big PowerPoint user, here is a way to use it to create the image: after copying the text box in Excel, open PowerPoint and use the Paste special function to paste it as a PNG or JPEG. Then right-click the image and use the Save as image function to save the file to your drive. The nice thing about this is that the image is practically the same size as the original text box (about 1/8 " larger in height and width).

Amnezia
Amnezia

When you first open Paint, if the default size of the "blank new graphic" is larger than your footer image, don't worry about resizing the box to almost the same size - just drag the bottom right placemarker of the default Paint image to a SMALLER size than your footer image. Paint will simply expand the default size to fit the image you're pasting. Easy! NOTE: If the default blank Paint image is larger than your footer image, Paint WILL NOT shrink it to fit your smaller footer image. You'll have to do that as mentioned in the original article.

inouyde
inouyde

Easy peasy lemon squeezy

bobjorg
bobjorg

I taught Excel for several years and never thought of this. A real good solution without complications.

ssharkins
ssharkins

PowerPoint is a great way to go -- thanks for mentioning it!

ssharkins
ssharkins

That's a great tip -- thanks for mentioning it!