Ozgrid, Experts in Microsoft Excel Spreadsheets
Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com
Learn how to create Excel dashboards.

Ozgrid Excel Newsletter. Excel Newsletter Archives  


A footer is something that appears at the bottom of every worksheet, as opposed to a header, which appears at the top. It is a good idea to use a document identifier, such as the full name and path of your file as your footer. Not only with this enable you to find quickly where you have stored your workbook, but it will enable others to find and modify your workbook should they need to.

Footers are not viewable when your spreadsheet is in Normal view, only Page Layout view or Print Preview.

To insert a footer into your workbook follow these steps:

On the worksheet you wish to have the footer in;

1. Select the Insert tab
2. Select Header & Footer under Text options
Notice that the View of your workbook has changed to Page Layout view and you are clicked in the centre “Header” area of your worksheet.
3. Select the Go to Footer option under the Navigation group.
You could scroll down the page and click into the Footer area with your mouse if you prefer.
4. Make sure you are in the left-hand footer area and select File Path from Header & Footer elements
This will insert what are called “field codes” into your document. A field is something that can update either automatically or by a prompt. These codes will show the full filename and path of your document once you have saved it.
5. As a footer should be unobtrusive, highlight and make it a size 8 font.

Note here that the lowest font available in the font sizing box in the Font group on the home tab is 8, but you can actually type as low as 1pt if you like.

Another handy field that you can put into your footer is the page number and number of pages.

1. Click in the right hand side of your footer
2. Select the Design tab under Header & Footer tools
3. Type in the word Page and a space. Click the Page Number option under Header & Footer tools
4. Now type in the word of and another space.
5. Select the Number of Pages option under Header & Footer tools
6. Highlight your field codes and make them a size 8.

Again notice the “field codes” that have been placed in your worksheet. These will show the current page number and the number of pages in your file.

If you wanted to force your page number to be a certain number ie; if you wanted to call your first page page 2, try this:

1. On the Page Layout tab, select the dialog box launcher on the Page Setup group
2. Select the Page tab
3. Under the option First Page Number change the word Auto to page 2, or whatever number you want.

There are other fields that you can place in your footer, such as the current date and the current time. Variations of these and other header/footer fields can be found by selecting either Header or Footer from the Header & Footer group on the Design tab under Header & Footer tools

Note that if you saved your file as another name, or adjusted the number of pages, or changed the date printed, or in face anything that will force your fields to update, The footer will update either when you save the file, or when you send it to print.


By the fastest way to delete rows meeting a condition, is with the use of AutoFilters. The code below offers a lot of flexibility for the end user. See Also Delete Rows By Up To 4 Conditions across 4 Columns and Using the Find Method To Delete Rows

Option Explicit

Sub FastestAndMostFlexible()
'Written by www.ozgrid.com

Dim rRange As Range
Dim strCriteria As String
Dim lCol As Long
Dim rHeaderCol As Range
Dim xlCalc As XlCalculation

    On Error Resume Next
    'We use Application.InputBox type 8 so user can select range
    Set rRange = Application.InputBox(Prompt:="Select range including header range" _
        , Title:=strTitle & " STEP 1 of 3", Default:=ActiveCell.CurrentRegion.Address, Type:=8)
    'Cancelled or non valid rage
    If rRange Is Nothing Then Exit Sub
     'Awlays use GoTo when selecting range so doesn't matter which Worksheet
     Application.Goto rRange.Rows(1), True
    'We use Application.InputBox type 1 so return a number
    lCol = Application.InputBox(Prompt:="Please enter relative column number of evaluation column" _
        , Title:=strTitle & " STEP 2 of 3", Default:=1, Type:=1)
    If lCol = 0 Then Exit Sub

    'We use default InputBox type as we want Text
    strCriteria = InputBox(Prompt:="Please enter a single criteria." & _
        vbNewLine & "Eg >5 OR <10 OR Cat* OR *Cat OR *Cat*" _
        , Title:=strTitle & " STEP 3 of 3")
    If strCriteria = vbNullString Then Exit Sub
    'Store current Calculation then switch to manual.
    'Turn off events and screen updating
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    'Remove any filters
    ActiveSheet.AutoFilterMode = False
    With rRange 'Filter, offset(to exclude headers) and delete visible rows
      .AutoFilter Field:=lCol, Criteria1:=strCriteria
      .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    'Remove any filters
    ActiveSheet.AutoFilterMode = False
      'Revert back
    With Application
        .Calculation = xlCalc
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   On Error GoTo 0
End Sub

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Become an ExcelUser Affiliate & Earn Money

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft