# Excel Tips 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. 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. 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). 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. 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. 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. 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. 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. 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. 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…? 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. 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.