Fields in Excel

Topic

New Reply

In WORD, there are “Fields” under the <Insert> <Quick Parts> <Fields>.  Why they are hidden under something with the useless name “Quick Parts”,  I do not know, but then I will never understand why Microsoft does the things it often does.  I do not seem to be able to find these “Fields” in Excel.  Are they hidden somewhere, or just not present?

Ron M

Reply | Quote

Replies

As far as I understand it, there is no such Quick Parts in Excel. You do have the option of creating Visual Basic scripts to create certain shortcuts to document information, for instance. (No, it’s not really as easy as using Quick Parts in Word.)

What fields were you trying to access?

Reply | Quote

Kirsty, thanks for confirming my suspicions.  I usually like to put “Page X of N” in my right Footer and “Printed on” in my left Footer.  I am having trouble trying to figure out where to find the appropriate “Page Numbers (X and N)” and “Dates” and how to insert them in the necessary parts of the Footer where I want them.  Any idea how I would do this?

I know how to find the Footer and its three parts and I can put in the words “Printed on:” and “Page” ,but I am unclear on where to find the data to put in these places.  It was so easy when “Fields” were available.

Ron M

Reply | Quote

[zeddy]

Hi Ron

To set your Footer as you want, in the Excel top-panel Ribbon, select [Page Layout], then, in the [Page Setup] section, click the tiny arrow in the bottom-right corner of this section (it’s underneath the Print Titles icon).

This will display the Page Setup options for the current worksheet. Click the 3rd tab, [Header/Footer]. Click the [Custom Footer..] button. This will then display the Footer options. You can now enter what you want in the 3 sections: Left Section; Center section; Right Section.

For example, in the Left section: type Filename: and then click the button-icon for the sheet. In the center Section, you can type Page and then click the [#] button icon, and then type ” of ” and then click the [++] button-icon for pages-count.

In the third footer section, you can type Date Printed : and then click the [calendar] button-icon etc etc etc You can type whatever you like in the footer sections e.g. CONFIDENTIAL, and if you want to change the Font-size or colour, click the [A] button-icon

here’s a picture..

zeddy

• This reply was modified 4 years, 5 months ago by zeddy.
• This reply was modified 4 years, 5 months ago by zeddy.
• This reply was modified 4 years, 5 months ago by zeddy.
• This reply was modified 4 years, 5 months ago by zeddy.

2 users thanked author for this post.

KP, doriel

Reply | Quote

[Ron M]

zeddy, thank you for this, it is great help.  I don’t know what happened, but two of my postings disappeared, so hopefully, if they reappear, this isn’t a repeat.

One more thing – is it possible to put a line above the Footer (and below the header) that goes from one side of the page to the other?  it didn’t come out in the posting exactly as I typed it in before I posted it.  Hopefully, I am making myself clear.  Like this without so much spacing between them…

________________________________________________________________

Left                                                     Centre                                                 Right

 

Ron M

• This reply was modified 4 years, 5 months ago by Ron M.
• This reply was modified 4 years, 5 months ago by Ron M.
• This reply was modified 4 years, 5 months ago by Ron M.
• This reply was modified 4 years, 5 months ago by Ron M.
• This reply was modified 4 years, 5 months ago by Ron M.

Reply | Quote

Hi Ron

This is the simplest way I know, other than using vba..

In each section of the Header, keep typing underscores “_” until there are 2 lines in the section..

In each section of the Footer, keep typing underscores “_” until there are 2 lines in the section..

This will draw lines across the page. If needed, you can add/delete underscore characters until your Print-Preview shows it as you want.

If the line is too long i.e. ‘wraps’ onto a second line, simply delete a few of the underscore characters (until you get the ‘correct’ length etc etc etc).

To add your actual Left-footer below that line, press [Enter] at the end of the line in the Left Section, and then add your required text (like filename). Ditto for Center section and Right section.

To add your required header text above the heading-line, press [Enter] at the start of each section, and type your required text etc, to ‘push’ the header-line down

zeddy

Reply | Quote

zeddy, thanks for the suggestions.  I tried that, but it just became too frustrating – try one setup, look at it, go back, try the next setup, look at it, etc., etc. I will live with it, unless someone has a better, less frustrating solution.

Maybe I will go and rattle Microsoft’s chain as it becomes obvious that the guys who produce this software never have to use it to any extent.

I have attached a Word document with the headers and footers setup as I like to do them for my documents – text is random.  Why this can’t be done from basic functionality, i.e., fields, in an Excel document, rather than “fancy icons” that are not necessarily easy to understand, I do not know.  The icons are easy to understand once you know what they mean, but fields are a lot easier, at least for me.

Thanks again for all your help with this.

Ron M

Reply | Quote

Ron,

I’m rather late to this party, but it occurs to me that you can create a page-wide line as a picture and then simply insert the picture into the header/footer either above or below the left/center/right entries. Excel allows multi-line headers & footers.

Norm

1 user thanked author for this post.

Ron M

Reply | Quote

Ron,

I should have given an example. I created a line in PowerPoint (because it is easy) and then copied it as a picture (Windows-shift-s combo-keys) . I then pasted the picture into a footer with the result shown in the attachment.line-in-the-footer-example

Norm

Reply | Quote

Hi Ron

There must be over 50 settings available for Page Setup in Excel. Word doesn’t have many array or math functions. So you have to work with what you have…

Here’s what I would do for page setup in Excel: Once you have got your required page setup for one worksheet, it is then very easy to replicate that same page setup to other worksheets. Simply click on the [Tab] worksheet that has the page setup you want to copy, then use [Ctrl]-click to select/deselect your other worksheets you want to have the same page-setup. Then, with the multiple sheets selected as a group, just click Print Preview. This has the effect of ‘copying’ the page-setup of the sheet you are actually viewing to all selected sheets in the group. To un-select the Group of worksheets, just click on any currently unselected worksheet to Cancel the group mode etc etc etc.

Perhaps you could even ‘keep’ your preferred Page-Setup-settings in a separate workbook.

For example, see attached file.

You could copy a page-setup-worksheet into any required file and then use the method described above to copy those settings to other worksheets in your file, as required.

zeddy

zeddy-PageSetups

2 users thanked author for this post.

Ron M, Kirsty

Reply | Quote