12

I'm in the process of changing to Google Drive from MS Office for all aspects of my work. Pretty much everything I need is there, but the one thing we can't work out how to do is how to get a header and footer into a Google Spreadsheet (although we have them working fine in Word documents).

Is there an approach to do this? Also, it's worth pointing out this is mainly for use when we export our spreadsheets as PDF's. So the other thing that would be great, is to be able to view the page breaks, as you can in MS Excel.

Improve this question

7 Answers 7

Reset to default
10

Headers and Footers show up in the Print dialog box. Click print and look for "Headers & footers" toward the bottom of the right panel. You can select page numbers, Workbook title, Sheet name and more as well as "EDIT CUSTOM FIELDS"

Improve this answer
1
  • Thanks noticed they added them recently too, do you kow if there is a way to get an image (such as a company logo) into the header ?
    – sam
    Dec 14, 2017 at 18:00
6

There exists no direct way to add a header and footer to a Google Spreadsheet.

Two pis-allers to add a header:

  • use the filename as the header
  • use a row header and check the "repeat row headers on each page" in the print settings.

As for the footer, you are screwed.

Lastly, it's not possible to view the page breaks.

Improve this answer
5

I agree with Franck's answer for headers. Here are two methods to approximate a footer on a one page document. Neither method is perfect (see limitations).

Method One

  1. Place the following formula above the row that you want to use as a footer. This formula will:
    • add a certain number of line breaks (number of breaks is set to 20),
    • add a line break for every blank row in range A1:A7, and
    • adjust for any line breaks that are already in range A1:A7:

=REPT(char(10), 20 +countblank(A1:A7) -sum( ArrayFormula( len(A1:A7) -len( substitute(A1:A7, char(10), "")))))

  1. Change the range in lines 3 and 4 from A1:A7 to the tallest range in your spreadsheet (the range is referenced twice in line 4).
  2. Reduce the height of all rows above your footer from the default to 2 pixels.
  3. Change the number in line 2 from 20 to any number to raise or drop the footer on the page.

Limitations:

  • The formula will not count the number of rows in wrapped text. It only counts the number of line breaks or char(10).
  • If you get an error that parameter 2 of REPT is less than zero, increase the number in line 2. If that still doesn't work, one of your cells has too many line breaks and you don't have enough room on the page.
  • The "footer" location is not fixed (drops 2 pixels per filled row).

Method Two

  1. Determine max number of rows that fit on your sheet when printed. This will be your 'top half.'
  2. Input the following formula after the last row of the 'top half': = if( A1="", " ", "") where A1 is the first cell in the 'top half'
  3. Extend the formula from step 2 downward for the same number of rows in your top half. This is your 'bottom half.'
  4. Input your footer after the last row of the 'bottom half.'
  5. Change the height of all rows in the 'top' and 'bottom' halves to the minimum 2 pixels.
  6. Since the sheet is a little taller than when you first measured, reduce the number of rows so that your footer fits on the first page. Delete 'top' rows first and then delete 'bottom' rows to avoid reference errors.

Limitations:

  • This method will not count the number of rows in wrapped text, nor will it count the number of line breaks within cells in the 'top half.'

Summary

  • Method One handles extra line breaks but doesn't completely fix the footer location.
  • Method Two fixes the footer location if there are no extra line breaks in your sheet.
  • Neither method adjusts for wrapped text.
  • Neither method works on multiple page documents.
Improve this answer
0
1

Google drive have recently launched a new feature which may allow a work around for this, its not 100% the same way it works in excel, but could be useful.

  1. Create a spreadsheet as your would normally in google sheets
  2. Copy the range you want to have a header and footer on
  3. open a google doc
  4. paste the data into the google doc (this will ask you if you want to dynamicly link the data, or paste a copy - You want the dynamic option
  5. Add header and footer to the google doc as you normally would

Now any changes made in the spreadsheet you will get an option in the google doc to click update an have the data changes displayed in your google doc table.

Improve this answer
1
  1. Click on Print option.
  2. A new window opens.
  3. Expand Headers & Footers Tab
  4. Now select Edit Custom Fields
  5. Here add your custom Headers & Footers.
Improve this answer
0

Isn't the header issue solved by simply putting whatever you want for the header as the top row and then selecting View --> Freeze --> 1 Row? That top row then repeats at the top of each page, thus becoming your header.

Improve this answer
1
  • Pretty sure the Asker is asking about print headers, not column headers.
    – ale
    Jan 2, 2019 at 20:14
0

Seems like, when you go to "Print", and change the "Header & footer" by changing "Edit Custom Field", if you "Confirm" but then "Cancel" then your changes don't get saved. However, if you "confirm", then press next, your printer dialog box opens up with print preview. If you "Cancel" at this point, then your header changes will be saved.
I just tried on 4 documents that earlier I thought I had changed it by Canceling, did not save any of them. Repeated the process for same files by continuing to print dialog, changed them all.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.