Feeds:
Posts
Comments

Excel drop-down lists can be used in to make data entry more accurate or limit the selection of data to a pre-defined list.  Suppose I need to keep track of the various Microsoft Office courses I’m running over the first two weeks of June.  I might create a spreadsheet that looks something like this:

In addition to creating a column for dates and the courses I intend to run, I have created a list of the courses I offer in the range D4:D8.

To create a drop-down list from the list of courses:

  1. Select the range B4:B14.
  2. On the Data tab on the task ribbon, in the Data Tools group, click the Data Validation button.
    The Data Validation dialog box appears.
  3. On the Settings tab, click the down arrow in the Allow box and select “List”.
  4. Click in the Source box then drag to select D4:D8.
    Move the Data Validation dialog box, if necessary.
  5. Click OK.

To utilize the drop-down list:

  1. Select B4 then click the down arrow that appears.
  2. Select a course from the list and repeat for subsequent rows.

A 3-D reference utilizes the same cell, or range of cells, from multiple worksheets in a formula.  3‑D references will work with a number of functions in Excel including:  SUM, COUNT, and AVERAGE.  Let’s look at an example.

Imagine a company that has locations in several Canadian cities – Toronto, Calgary, and Vancouver.  This organization might want to record sales for each region on its own worksheet but, if the company’s products are consistent across all regions, each of the worksheets would likely be laid out identically making it easy to summarize data on a single sheet using 3-D references.

Examine the spreadsheet below.  Toronto’s food sales are recorded in cell B4 on the Toronto tab.  Calgary’s food sales are stored in cell B4 on the Calgary tab.  Vancouver’s food sales are stored in cell B4 on the Vancouver tab.

To calculate a summary incorporating data from all regions:

  1. Select the Summary tab then click cell B4.
  2. Type “=sum(“.
  3. Select the Toronto tab and click B4.
  4. Hold the SHIFT key then select the Vancouver tab and click B4.
  5. Type “)” and press ENTER.
  6. To copy the formula to the remaining cells on the Summary tab, select B4 then use the fill handle to copy the formula down to B6.

Headers and footers are areas at the top and bottom of each page.   They often contain document titles, dates, or page numbers.

To insert a header or footer, go to the Insert tab on the task ribbon and click the Header or Footer button in the Header & Footer group.  From the gallery, select one of the pre-defined headers or footers or click Blank to create your own.

Inserting a Header

When you select a pre-defined header or footer, you can fill in special fields, called content controls, to customize your document.  Let’s look at an example:

  1. Open a new, blank document in Microsoft Word.
  2. On the Insert tab, in the Header & Footer group, click Header.
    A gallery of header designs appears.
  3. In the gallery, scroll down and select Conservative.
    The Conservative header is added to your document.  The Title content control is active.
  4. Verify that the [Type the document title] prompt is selected then type “Working with Headers and Footers in Word 2007”.
  5. Click to select the [Pick the date] content control then click the down arrow and select Today.
    Today’s date is displayed.
  6. Click the Close Header and Footer button in the Close group on the Design tab.
  7. Save the document as “Headers and Footers”.

Inserting a Footer

Let’s insert the matching footer:

  1. Verify that “Headers and Footers” is still open.
  2. On the Insert tab, in the Header & Footer group, click Footer.
    A gallery of footer designs appears.
  3. In the gallery, scroll down and select Conservative.
    The Conservative footer is added to your document.  A Page number is included in the footer.
  4. Click the Close Header and Footer button in the Close group on the Design tab.
  5. On the View tab, in the Zoom group, click One Page to view the header and footer that you’ve added to the document.  Click 100% to resume editing the document.
  6. Click Save.

Editing Headers and Footers

In this topic, we will remove the Conservative footer and insert a blank footer that we will customize.

  1. Verify that the “Headers and Footers” document is open.
  2. On the Insert tab, in the Header & Footer group, click Footer.
    A gallery of footer designs appears.
  3. Click Remove Footer.
    The pre-defined footer is removed.
  4. On the Insert tab, in the Header & Footer group, click Footer.
    A gallery of footer designs appears.
  5. In the gallery, select Blank.
    A blank footer is added to your document.
  6. Verify that the [Type Text] prompt is selected then type “Step-by-Step Instructions”.
    Your text replaces the text prompt.
  7. Press TAB twice.
    Your cursor moves to the right side of the footer.
  8. Type “Page” followed by a space.
  9. On the Header & Footer Tools > Design tab, in the Header & Footer group, click Page Number then point to Current Position.
    The Page Number gallery is displayed.
  10. From the Page Number gallery, select Plain Number.
    A page number is added to the footer.
  11. Click the Close Header and Footer button in the Close group on the Design tab.
  12. Click Save.

Use a Different First Page Header and Footer

You can use a different header and footer on the first page of your document or you can suppress the header and footer entirely by leaving them blank.  In this section, we’ll use a blank header and footer on the first page and we’ll insert a title page.

  1. Verify that “Headers and Footers” is open.
  2. Double-click in the header area of your document.
    The Header and Footer Tools > Design tab appears.
  3. On the Header & Footer Tools > Design tab, in the Options group, click the Different First Page checkbox then click the Close Header and Footer button.
    A blank header and footer are displayed.
  4. Select the Cover Page button in the Pages group on the Insert tab.
    The Cover Page gallery appears.
  5. From the gallery, select the Conservative cover page.
  6. Click to select the Company content control and press DELETE.
  7. Delete the Subtitle and Abstract content controls as well.
  8. Click Save.

Use Different Headers for Odd and Even Pages

You can display different headers and footers for odd and even numbered pages.  With this setting you can display page numbers in the document’s outside margins – a very useful technique, if you are binding the material in a book.

  1. Verify that “Headers and Footers” is open.
  2. Double-click in the header area of your document.
    The Header and Footer Tools > Design tab appears.
  3. On the Header & Footer Tools > Design tab, in the Options group, click the Different Odd and Even Pages checkbox.
  4. On the Header & Footer Tools > Design tab, in the Navigation group, click Next Section.
    A blank Even Page Header and Footer are displayed.
  5. On the Header & Footer Tools > Design tab, in the Header and Footer group, click Header.
    A gallery of headers appears.
  6. From the Header gallery, select Mod (Even Page).
  7. Click Footer in the Header & Footer Tools group then select Mod (Even Page) from the Footer gallery.
  8. Click the Close Header and Footer button.
  9. Verify that the insertion point is on page 2 then type “This is an even page”.
  10. On the Insert tab, in the Pages group, click Page Break.
    A new page is added to your document.
  11. On the Header & Footer Tools > Design tab, in the Header and Footer group, click Header.
    A gallery of headers appears.
  12. From the Header gallery, select Mod (Odd Page).
  13. Click Footer in the Header & Footer Tools group then select Mod (Odd Page) from the Footer gallery.
  14. Click the Close Header and Footer button.
  15. Verify that the insertion point is on page 3 then type “This is an odd page”.
  16. Save and close the document.

Page numbers are normally included in a document’s headers or footers however, if a page number is all you require, here is a quick way to insert them.  Go to the Insert tab (on the task Ribbon in Word 2007), click the “Page Number” button in the Header & Footer group then select a location and style from the gallery.

If you’ve opened a document in Word and you’d like to save the changes you’ve made without overwriting the original file, press the F12 button on your keyboard to display the “Save As” dialog box.

In addition to being faster than clicking the Office button and selecting “Save As”, this shortcut will work in other Office applications like Excel and PowerPoint.

In Microsoft Word, press SHIFT + F3 to cycle through various combinations of uppercase, lowercase, and mixed upper / lowercase.

If you type a sentence, drag to select it, and press SHIFT + F3 once, the entire sentence will be converted to uppercase, as in:  KEYBOARD SHORTCUTS SAVE TIME.

If the sentence remains selected and you press SHIFT + F3 a second time, the entire sentence will be converted to lowercase.  Pressing SHIFT + F3 a third time will result in the sentence being converted to title case – the first letter is capitalized, subsequent letters are lowercase.

Follow

Get every new post delivered to your Inbox.