Using Excel

Go to End

Note

01 Feb 2019: Bracketing negative numbers and hiding zeros added.

26 Sep 2018: Showing one-digit as two-digits added.

19 Sep 2018: First created.

Insert page number and total number of pages

Select Page Layout > Print Titles > Header/Footer > Custom Footer:

(By the way, click "Scale with document" and "Align with page margins" to enable the Header and Footer not to encroach into the margins.)

Select icon to insert Page number:

Enter " / ".

Select icon to insert Number of Pages:

Select "OK".

Select Page:

Change First page number from "Auto" to "1".

If it is "Auto", the page number of all Worksheets selected to print will run consecutively.

If it is "1", each Worksheet will start at page 1.

However, the total number of pages will be the total of all Worksheets selected printed, not the total number of the respective Worksheet. This may not be desirable.

The next is a solution.

Show total number of pages of a Worksheet

Select Formulas > Name Manager > New.

Define:

  • Name: NumberOfPages
  • Refers to: GETDOCUMENT(50)

Enter in a cell a formula =NumberOfPages

The displayed value will be the total number of pages of the worksheet.

Enter in a cell a formula ="Total " & NumberOfPages & " Pages" (note spaces) will display a meaningful text.

This cannot be used in the Header or Footer.

Show single-digit whole number as two-digit whole number

Press Ctrl-1 > Number > Custom,

Enter "00" against Type. Press OK.

To make the double-digit number actually become a text for other uses, use the TEXT() formula, where "00" represents the Format Type used above, as follows:

Show negative numbers in brackets

(added 01 February 2019)

Use: 

#,##0.00;(#,##0.00)

The part before ";" defines the format of positive numbers.

The part after ";" defines the format of positive numbers.

Show negative numbers in brackets

Right-align positive numbers with negative numbers before the ")"

(added 01 February 2019)

Append "_-" to the first part:

#,##0.00_-;(#,##0.00)

 

Show negative numbers in brackets and align

Hide zeros for individual cells

(added 01 February 2019)

Append ";;@" to the end:

#,##0.00;(#,##0.00);;@

Hide zeros for individual cells

Hide zeros for the whole worksheet

(added 01 February 2019)

Click File > Options > Advanced.

Un-click Show a zero in cells that have zero value.

All zeros will be hidden.

Excel-Worksheet-HideZeroForWhole

 

 

End of Page