MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


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.

Use Indirect To Get A Data From A Multi-cell Range

Use Indirect To Get A Data From A Multi-cell Range »


February 15, 2021

INDIRECT is pretty cool for grabbing a value from a cell. Can INDIRECT point to a multi-cell range and be used in a VLOOKUP or SUMIF function?

Get Data From Another Worksheet By Using Indirect

Get Data From Another Worksheet By Using Indirect »


February 12, 2021

You have 31 daily worksheets in a workbook, 1 for each day of the month. A cell on the summary worksheet contains a date. You want to use the date cell in INDIRECT to grab data from a certain day’s worksheet, but the formula always returns a #REF! error.

Point To Another Worksheet With Indirect

Point To Another Worksheet With Indirect »


February 10, 2021

You need to grab cell B4 from one of many worksheets. You have to determine which worksheet, based on a cell label or a calculation, and you’re wondering if INDIRECT can point to another worksheet.

Refer To A Cell Whose Address Varies, Based On A Calculation

Refer To A Cell Whose Address Varies, Based On A Calculation »


February 8, 2021

You need to refer to a cell, but the cell address varies, based on a calculation.

Use Get Cell To Highlight Non-formula Cells

Use Get Cell To Highlight Non-formula Cells »


February 5, 2021

You want to highlight all the cells on a worksheet that do not contain formulas.

Understand Boolean Logic: False Is Zero; And Is *,or Is + And Everything Else Is True

Understand Boolean Logic: False Is Zero; And Is *,or Is + And Everything Else Is True »


February 3, 2021

You want to become a guru at Excel formulas. To master conditional computing formulas, you need to understand Boolean logic facts.

Introducing The Boring Use Of SUMPRODUCT

Introducing The Boring Use Of SUMPRODUCT »


February 1, 2021

Your IT department sends you a file with unit price and quantity sold. You need to calculate total revenue. You usually add a new column and total that column, but there must be a way to total the 5,000 line items with only one formula.

Why the Minus Minus? Coerce Numbers from True/False

Why the Minus Minus? Coerce Numbers from True/False »


January 29, 2021

While IF and other functions that expect logical tests can easily convert TRUE and FALSE values to 1s and 0s, the SUMPRODUCT function cannot do this. Why do you sometimes use a minus minus in SUMPRODUCT?

Sum Every Other Row or Every Third Row

Sum Every Other Row or Every Third Row »


January 27, 2021

Someone set up a worksheet with dollars in rows 2, 4, 6, 8, and so on and percentages in rows 3, 5, 7, 9, and so on. You want to sum only the dollars, which are stored in the even rows. While you’re at it, you’d like to know how to sum the odd rows or every third row.

Store Holidays in a Named Range

Store Holidays in a Named Range »


January 25, 2021

The NETWORKDAYS and WORKDAY functions can take a list of company holidays as the third argument. If you store the list of holidays in AZ1: AZ10, there is a chance that someone will inadvertently delete a row, so you want to move the range of company holidays to a named range.