This is a great tip from Allen Wyatt’s Excel Tips

Suppose that you have a large worksheet that requires 16 pages when printed out. You may wonder if there is a way, when working within the worksheet, to jump to some given page, such as page 5.
Word users know that they can, within Word, use the Go To dialog box to jump to various pages, but no such feature exists in Excel. There are a couple of ways you can approach the problem, however.
One approach is to select the cell that appears at the top of a page. (For instance, that cell that appears at the top-left of page 5.) You can then define a name for the cell, such as Page05. Do this for each page in your worksheet, and you can then use the features within Excel to jump to those names.
Another way you can do this is to use the page break preview mode. (To switch to page break preview, choose View | Page Break Preview or, in Word 2007, display the View tab of the ribbon and click the Page Break Preview tool.) You can then see where the page breaks are, select a cell on the page you want, and then return to normal view.
It is possible to also create a macro that will let you jump to a specific page, but it isn’t as easy as you might think. The reason has to do with the possible use of hard page breaks, which can change where pages start and end. The following macro might do the trick for you, however. It prompts the user for a page number and then selects the top-left cell on the page entered.

Sub GotoPageBreak()
Dim iPages As Integer
Dim wks As Worksheet
Dim iPage As Integer
Dim iVertPgs As Integer
Dim iHorPgs As Integer
Dim iHP As Integer
Dim iVP As Integer
Dim iCol As Integer
Dim lRow As Long
Dim sPrtArea As String
Dim sPrompt As String
Dim sTitle As String

wks.Cells(lRow, iCol).Select
Set wks = Nothing
End Sub

Tip #5823 applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Possibly one of the most under used functions in Excel, subtotal is very useful in reports where you need to be sure that the subtotals through the report are not added into the grand total at the bottom of the report. Have a look at the picture on the right. How many times have you seen the grand total formula that refers to all the subtotals.

Where you would normally use the sum function and then add up each of the sums, Subtotal allows you to place a subtotal anywhere in the report and it will be excluded from all other subtotal functions. The formula to get the Grand totals of 97 in this picture are =C9+C15+C21 in the sum() column and =SUBTOTAL(9,F5:F22) in the subtotal() column.

Now imagine you have 20 or more subtotals and you can see how the subtotal function can be very useful.

In order to get the subtotal to add (sum) use the 9 option. SUBTOTAL(function_num, ref1, ref2, ...)
The other options available in the function are …

Sumif and Countif are excellent ways to summarise data in reports. For this example open my data example. This will open in another window. So in the example there are three tabs, Sheet1 Sheet2 & Sheet3. The data is on Sheet1 and has the following headings ….

SUMIF allows you to add values in a data range based on a criteria. It works like this.

SUMIF(range,criteria,sum_range)

Range is the range of cells that you want evaluated by criteria. In my example I am using the Region column.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. You can use a cell reference that contains your criteria. You can use expressions like 32, “32″, “>32″, or “apples”.

Sum_range are the actual cells to add if their corresponding cells in range match criteria. In my example look at the cells high lighted in red. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria. This will only work where the range contains numeric data.

COUNTIF allows you to count values or records in a data range based on a criteria. It works like this.

COUNTIF(range,criteria)

Range is the range of cells from which you want to count cells.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. You can use a cell reference that contains your criteria. You can use expressions like 32, “32″, “>32″, or “apples”.

You can get the data example in xls form by clicking here