MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Auto-number Records And Columns In An Excel Database


April 9, 2021 - by Bill Jelen

Auto-number Records And Columns In An Excel Database

Challenge: You want to build formulas to automatically serially number records and column headers in a database to which AutoFilter is applied and in which selected columns are hidden.

In the database shown in Figures 65 and 66, the records as well as columns are numbered normally. Figure 66 illustrates how the database appears when AutoFilter is applied to show records for total marks > 335 and the columns for Chemistry and Math are hidden. You want to auto-number the records (1 through 7 in this example) and column headers (1 through 5 in this example) by using formulas.

continue reading »

More Reading


Get An Array Of Unique Values From A List

Get An Array Of Unique Values From A List »

April 7, 2021 - by Bill Jelen


You want to extract all unique values from a column of text data that may contain several instances of a particular value. A procedure like this is useful when you need to populate a list box or combo box with unique values for user selection.

Find The Sum Of All Digits Occuring In A String

Find The Sum Of All Digits Occuring In A String »

April 5, 2021 - by Bill Jelen


You want to build a formula to return the sum of all the digits in a string of text. For example, applying the formula on the text string “I am 24 years old and my Dad is 43” should yield 13 (2+4+4+3).

read more articles »

Featured Products


The Absolute Guide to Dashboarding & Reporting with Power BI

March 2019

Learn Power BI Dashboarding Techniques by Watching A Pro!


Continuing Financial Modelling

November 2020

You wait for one decent Financial Modelling book to come out and then two come along at once. This edition is aimed at those who wish to advance their knowledge and expertise in financial modelling by addressing common problems that occur day to day in the world of business / decision analyses, forecasting, and valuations.


MrExcel 2020 - Seeing Excel Clearly

January 2020

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.


Your Excel Survival Kit - Second Edition

March 2021

This book helps you realize that Excel is on your side. It can be your friend, not your enemy. If and when you upgrade your Excel skills you will experience many positives: you will learn to love Excel, you will learn how to use Excel to become an invaluable asset in your workplace.


see more products »