MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


Auto-number Records And Columns In An Excel Database

Auto-number Records And Columns In An Excel Database »


April 9, 2021

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.

Get An Array Of Unique Values From A List

Get An Array Of Unique Values From A List »


April 7, 2021

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

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).

Use Two-way Interpolation With A Single Formula

Use Two-way Interpolation With A Single Formula »


April 2, 2021

Many engineering design problems require designers to use tables to compute values of design parameters. Such tables contain values of the required parameter for a range of values of a control parameter, arranged in discrete intervals, and the designer is permitted to use linear interpolation for obtaining the parameter value for intermediate values of the control parameter.

Use A Self-referencing Formula

Use A Self-referencing Formula »


March 31, 2021

Shades was looking for a formula to reverse letters in a cell. This can easily be accomplished using a VBA function. However, Shades had challenged people to write a formula. A new member, Hady, came along with this solution.

Use Vlookup To Get The Nth Match

Use Vlookup To Get The Nth Match »


March 24, 2021

Your lookup table contains multiple occurrences of each key field. You would like to return the second, third, or fourth occurrence of the key.

Deal With Dates Before 1900

Deal With Dates Before 1900 »


March 17, 2021

Excel stores a date as the number of days that have elapsed since January 1, 1900. This means that all the cool date functions do not work for dates in the 1800s. This is a problem for historians and genealogists.

Helpful Secrets about ROUNDing in Power Query

Helpful Secrets about ROUNDing in Power Query »


March 10, 2021

There are several cases where the default behavior in Power Query does not match the behavior in Excel. Rounding numbers is one of those cases. As of March 2021, the differences are not being documented by Microsoft.

Sum Visible Rows

Sum Visible Rows »


March 9, 2021

A SUM function totals all the cells in a range, whether they are hidden or not. You want to sum only the visible rows.

Sum A Cell Through Several Worksheets

Sum A Cell Through Several Worksheets »


March 3, 2021

You have 12 identical worksheets, one for each month. You would like to summarize each worksheet. Is there a better way than using =Jan ! B4 + Feb! B4+Mar! B4+Apr! B4…?

Use Natural Language Formulas Without Using Natural Language Formulas

Use Natural Language Formulas Without Using Natural Language Formulas »


February 24, 2021

Excel 2003 offers relatively obscure natural language formulas, but they were removed from Excel 2007. The table nomenclature in Excel 2007 isn’t as easy to use.

Always Point To Cell B10

Always Point To Cell B10 »


February 17, 2021

You want to create a formula that always points to cell B10. Normally, if you have a formula that points to B10 or even $B$10, the formula changes if you cut and paste B10 or if you insert or delete rows above row 10.