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

Power Excel With MrExcel - 2017 Edition

Power Excel With MrExcel - 2017 Edition »

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.

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!

M is for (DATA) MONKEY

M is for (DATA) MONKEY »

Despite the moniker "data monkey", we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time and reduce it to a single button click every subsequent time.

Excel JavaScript UDFs Straight to the Point

Excel JavaScript UDFs Straight to the Point »

JavaScript custom functions - UDFs can be used like any other native functions or UDFs in Excel. This book shows the process of creating JavaScript UDFs in Excel Developer Preview for Office Insider program subscribers.

MrExcel LIVe

MrExcel LIVe »

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

see more products »