MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Thankful for 20 Years of MrExcel on November 21


November 20, 2018 - by Bill Jelen

Thankful for 20 Years of MrExcel on November 21

MrExcel.com debuted on November 21, 1998 when Bill Jelen answered a question about how to convert daily dates to monthly dates in Excel.

Kevin wrote:

I want to use a pivot table to summarize dates, but I want to see it at the month level instead of at the daily level. I tried simply formatting the date column to display a month, but the pivot table still breaks the data out by day.

My answer that day involved using a formula of =A2-DAY(A2)+1. Here is a screenshot of that first answer:

Screenshot of first MrExcel answer
Screenshot of first MrExcel answer

Sitting here today, twenty years later, I wonder why I did not use =EOMONTH(A2,-1)+1 or even use the Group Field solution in a pivot table to roll daily dates up to months and years.

The old MrExcel logo with stylized text lasted until 2002 when our current logo was invented.

Also: It was John "Mr Spreadsheet" Walkenbach who suggested I get rid of the annoying Excel grid in the background... "I would spend more time at your site, except for the annoying grid behind the words!".

continue reading »

More Reading


UNIQUE From Non-Adjacent Columns

UNIQUE From Non-Adjacent Columns »

November 15, 2018 - by Bill Jelen


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 - by Bill Jelen


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?

read more articles »


IMA Data Analytics Leveraging Excel

Featured Products


Excel Video Medley

Excel Video Medley »


Excel Video Medley covers everything from Pivot Tables to VLOOKUP to VBA. Learn how to create scientific graphs. Use Excel for statistics.

Your Excel Survival Kit

Your Excel Survival Kit »


This book helps you realize that Excel is on your side. It can be your friend, not your enemy. If and when you upgrade your Excel skills you will experience many positives: you will learn to love Excel, you will learn how to use Excel to become an invaluable asset in your workplace.

Supercharge Excel When You Learn to Write DAX For Power Pivot

Supercharge Excel When You Learn to Write DAX For Power Pivot »


Hands-on book to learn and master the DAX language!

Power Pivot and Power BI

Power Pivot and Power BI »


Power Pivot for Excel and its close cousin Power BI Desktop are Microsoft’s tightly-related pair of revolutionary analytical tools – tools that are fundamentally changing the way organizations work with data.

MrExcel LIVe

MrExcel LIVe »


A book for people who use Excel 40+ hours per week. Illustrated in full color.

Excel Subtotals Straight to the Point

Excel Subtotals Straight to the Point »


I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.

see more products »