Renumber rows after filtering

Resolved · Medium Priority · Version 2013

Tue 29 Apr 2014, 14:23 Delegate Sue said...

Sue has attended:
Excel Intermediate course
Word Intermediate course

Renumber rows after filtering

Hi.
I would like to know if there is a way for Excel to renumber rows when a filter is applied?
e.g before filters

Number Name
1 John
2 Paul
3 Patrick
4 Simon

If I apply a filter that only names beginning with P are shown then Paul would be number 1 and Patrick would be 2.

Many thanks

Sue

Tue 29 Apr 2014, 15:05 Trainer Jens said...

RE: Renumber rows after filtering

Dear Sue,

Thank you for the question.

You can use the subtotal function to do what you want.

I have attached a workbook where you on sheet 1 can see an example or you can follow the link below to a website where you can find the method.

http://blog.contextures.com/archives/2010/03/19/number-the-visible-rows-in-excel-autofilter/

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Read more: https://www.stl-training.co.uk/post-34560-formula-shows-quarters.html #ixzz2wE8kbKnu

Attached files...

filterExample.xlsx

Tue 29 Apr 2014, 15:30 Delegate Sue said...

RE: Renumber rows after filtering

Thank you so much Jens. Just perfect!

Kind regards
Sue

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Bracketed negative numbers

Often Excel users wish to display negative numbers in colour red and bracketed

Intstructions
Step1. Select Format > Cells menu options. Within Numbers tabsheet, select Category = Custom.
Step 2. Select a type such as #,##0;[Red]-#,##0;; that specifies a colour in square brackets.
Step 3. Amend as follows; #,##0;[Red](#,##0;;

Notes: Excel formatting featues are of the form
"Positive; Negative;Zero;Text" separated by semicolon.

View all Excel hints and tips