Excel Tips

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.

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?

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.

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.

February 8, 2021

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

February 5, 2021

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

February 3, 2021

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

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.

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?

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.

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.