MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


Excel Use Boolean Logic. Photo Credit: Fancycrave at Unsplash.com

Excel 2020: Use Boolean Logic »


August 6, 2020

I always cover IF in my seminars. And I always ask how people would solve the two-conditions problem. The results are often the same: 70–80% of people use nested IF, and 20–30% use AND. Just one time, in Virginia, a woman from Price Waterhouse offered the formula shown in this article.

Excel Handle Multiple Conditions in IF. Photo Credit: Issam Hammoudi at Unsplash.com

Excel 2020: Handle Multiple Conditions in IF »


August 5, 2020

When you need to do a conditional calculation, the IF function is the answer. It works like this: If , then ; otherwise .

Excel Protect Rows with an Array Formula. Photo Credit: Jon Moore at Unsplash.com

Excel 2020: Protect Rows with an Array Formula »


August 3, 2020

Here is an odd use for an array formula: Say that you don‘t want anyone to delete or insert any rows in one section of a worksheet.

Excel Turn Data Sideways with a Formula. Photo Credit: Pauline Loroy at Unsplash.com

Excel 2020: Turn Data Sideways with a Formula »


July 30, 2020

Someone built this lookup table sideways, stretching across C1:N2. I realize that I could use HLOOKUP instead of VLOOKUP, but I prefer to turn the data back to a vertical orientation.

Excel Use WORKDAY.INTL for a Work Calendar. Photo Credit: Colin Carter at Unsplash.com

Excel 2020: Use WORKDAY.INTL for a Work Calendar »


July 29, 2020

While NETWORKDAYS calculates the work days between two dates, the WORKDAY function takes a starting date and a number of days, and it calculates the date that is a certain number of work days away.

Calculate Nonstandard Work Weeks. Photo Credit: Ramón Salinero at Unsplash.com

Excel 2020: Calculate Nonstandard Work Weeks »


July 27, 2020

It certainly seems that if you work anything other than Monday through Friday or have a year ending any day other than December 31, a lot of things in Excel don’t work very well.

Excel Use Function Arguments for Nested Functions. Photo Credit: Landon Martin at Unsplash.com

Excel 2020: Use Function Arguments for Nested Functions »


July 23, 2020

The Function Arguments dialog shown above is cool, but in real life, when you have to nest functions, how would you use this dialog?

Discover New Functions by Using fx. Photo Credit: David Blume at Unsplash.com

Excel 2020: Discover New Functions by Using fx »


July 22, 2020

There are 400+ functions in Excel. Instead of taking 450 pages to describe every function, I am going to teach you how to find the function that you need.

Audit a Worksheet With Spreadsheet Inquire. Photo Credit: Michael Rodock at Unsplash.com

Excel 2020: Audit a Worksheet With Spreadsheet Inquire »


July 20, 2020

Inquire was developed by a company called Prodiance that offered the slick Spreadsheet Compare tool for $145 per person per year. The Excel team liked it so much that Microsoft bought out Prodiance and gave the tool for free to anyone who is on Pro Plus, Professional Plus, or Enterprise Level E3.

Excel See Which Cells Depend on the Current Cell. Photo Credit: Edgar Castrejon at Unsplash.com

Excel 2020: See Which Cells Depend on the Current Cell »


July 16, 2020

I previously explained how to Trace Precedents to See What Cells Flow into a Formula. Sometimes you have the opposite problem: You want to see which cells rely on the value in the current cell.

Excel Trace Precedents to See What Cells Flow into a Formula. Photo Credit: Johannes Ludwig at Unsplash.com

Excel 2020: Trace Precedents to See What Cells Flow into a Formula »


July 15, 2020

If you need to see which cells flow into a formula, you can use the Trace Precedents command in the Formula Auditing group on the Formulas tab.

Excel Highlight All Formula Cells. Photo Credit: José Alejandro Cuffia at Unsplash.com

Excel 2020: Highlight All Formula Cells »


July 13, 2020

If you are going to be auditing the worksheet, it would help to mark all of the formula cells.