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 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

Learn to Write DAX

Learn to Write DAX »

Data analysis expressions (DAX) is the formula language of PowerPivot and Power BI. Simply reading a book is normally not enough to help Excel users learn DAX skills – you need to complete exercises and get plenty of practice to make the transition.

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.

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.

Excel Dynamic Arrays Straight to the Point

Excel Dynamic Arrays Straight to the Point »

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.

Excel 2016 in Depth

Excel 2016 in Depth »

Experienced with Excel? Don’t let Excel 2016 make you feel like a beginner again! This new full-color edition of the bestselling book has been completely overhauled. Gone is unnecessary and rarely used content; emphasis is on the most-used and new aspects of Excel 2016.

100 Excel Simulations

100 Excel Simulations »

Covering a variety of Excel simulations, from gambling to genetics, this introduction is for people interested in modeling future events, without the cost of an expensive textbook.

see more products »