MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips

Add a Total Row and a Total Column with One AutoSum. Photo credit: Tim Mossholder at

Excel 2019: Add a Total Row and a Total Column with One AutoSum »

February 22, 2019

Say that you want to add a total row and a total column to a data set.

Excel Put the Worksheet Name in a Cell. Photo Credit: Jannes Glas at

Excel 2019: Put the Worksheet Name in a Cell »

February 21, 2019

If you want each report to have the name of the worksheet as a title, use this formula.

Fast Worksheet Copy. Photo Credit: Joe Green at

Excel 2019: Fast Worksheet Copy »

February 20, 2019

You can right-click any sheet tab and choose Move or Copy to make a copy of a worksheet. But that is the very slow way to copy a worksheet. The fast way: Hold down the Ctrl key and drag the worksheet tab to the right.

Excel Fill Jan, Feb, ..., Dec, Total. Photo Credit: Curtis MacNewton at

Excel 2019: Fill Jan, Feb, ..., Dec, Total »

February 19, 2019

While you can edit any custom list that you create, you cannot edit the first four lists in the Custom Lists dialog.

Excel The Fill Handle Does Know 1, 2, 3…. Photo Credit: David Wright at

Excel 2019: The Fill Handle Does Know 1, 2, 3… »

February 18, 2019

Why does the Excel Fill Handle pretend it does not know how to count 1, 2, 3? The Fill Handle is great for filling months, weekdays, quarters, and dates. Why doesn’t it know that 2 comes after 1?

Excel Total the Visible Rows. Photo Credit: Ruslan Bardash at

Excel 2019: Total the Visible Rows »

February 15, 2019

After you’ve applied a filter, say that you want to see the total of the visible cells.

Excel  Filter by Selection for Numbers Over/Under. Photo Credit: Conor Luddy at

Excel 2019: Filter by Selection for Numbers Over/Under »

February 14, 2019

What if you want to see all values greater than $20,000?

Excel Filter by Selection. Photo Credit: Fancycrave at

Excel 2019: Filter by Selection in Excel »

February 13, 2019

The filter dropdowns have been in Excel for decades, but there are two faster ways to filter. Normally, you select a cell in your data, choose Data, Filter, open the dropdown menu on a column heading, uncheck Select All, and scroll through a long list of values, trying to find the desired item.

Break Apart Data. Photo Credit: Libby Penner at

Excel 2019: Break Apart Data »

February 12, 2019

People often ask about how to parse data that is all in a single column. Say you wanted to sort the data by zip code.

Double-Click the Fill Handle to Copy a Formula. Photo Credit: rawpixel at

Excel 2019: Double-Click the Fill Handle to Copy a Formula »

February 11, 2019

You have thousands of rows of data. You’ve added a new formula in the top row of your data set, something like =PROPER(A2&" "&B2). You need to copy the formula down to all of the rows of your data set.

UNIQUE From Non-Adjacent Columns

UNIQUE From Non-Adjacent Columns »

November 15, 2018

The other day, I was about to create a unique combination of two non-adjacent columns in Excel. I usually do this with Remove Duplicates or with Advanced Filter, but I thought I would try to do it with the new UNIQUE function coming to Office 365 in 2019. I tried several ideas and none would work. So, I went to the master of Dynamic Arrays, Joe McDaid, for assistance. The answer is pretty cool, and I am sure I will forget it, so I am documenting it for you and for me. I am sure, two years from now, I will Google how to do this and realize "Oh, look! I am the one who wrote the article about this!"

Find Latitude and Longitude for Each City in Excel

Find Latitude and Longitude for Each City in Excel »

November 9, 2018

Say that you have a list of cities in Excel and need to know the latitude and longitude for each city. A new Geography Data Type feature coming to Office 365 will make this easy. Check the Data tab in the Excel ribbon. Do you have a new Data Type category with Stocks and Geography?