MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips

Upgrade to Excel 2007 or Excel 2010? »

October 29, 2010

I recently received a call from an old colleague. His company was finally upgrading from Excel 2003 and asked if I could send him a book. I figured that I would send him Rev Up to Excel 2010: The Upgraders Guide to Moving from Excel 2003 to Excel 2010.

PowerPivot? PowerPoint? »

October 21, 2010

During the beta, PowerPivot was know as Gemini. When they announced the official name as PowerPivot, I caught myself getting hung up on whether I was talking about PowerPivot or PowerPoint.

Pasting into a Filtered Data Set »

March 24, 2009

Say you have 1000 records on Sheet1. You filter that data set to show you only the records with ProdID = Z. Select the data set and paste to Sheet2. Nicely, Excel brings only the visible records, so you have a contiguous block of cells.

Cool Spreadsheets to Download »

December 4, 2008

Occasionally, I receive an e-mail from someone who has developed a cool spreadsheet and they would like to donate it, to allow anyone who can use it, to download it for free.

Bug When Pasting Validation in VBA »

June 8, 2005

If you’ve read the first few chapters of VBA & Macros for Microsoft Excel, you know that I complain that the Excel macro recorder doesn’t do the greatest job of recording usable code.

Rearranging Rows in Excel by Sliding in Word »

September 27, 2004

Excel is excellent at sorting data. Sometimes, though, you need to do a manual sort.

Using Web Queries and a Loop to Download 4000 Database Entries from 4000 Web Pages »

September 26, 2004

One day, I received a broadcast e-mail from Jan at the PMA. She was passing along a great idea from Gary Gagliardi of Clearbridge Publishing. Gary mentioned that some search engines assign a page rank to a page based on how many other sites link to the page. He was suggesting that if all 4000 members of the PMA would link to all 4000 other members of the PMA, it would boost all of our rankings.

Create a Magic Square for Any Number »

September 15, 2004

Dave had mentioned that his grandfather would perform a trick where he would ask someone in the room for any number. He would then think very hard and slowly but deliberately build a 4x4 matrix. Every row, column, and diagonal of numbers would add up to exactly the number chosen. This type of figure is called a magic square.

Protecting a Single Cell in Excel »

September 5, 2004

Is it possible to protect a single cell in an Excel worksheet without protecting the whole worksheet?

Rounding Time in Excel »

May 30, 2004

Excel stores times in an interesting way. Each 24 hour period is equivalent to the number 1. 6AM is actually stored as 0.25. Noon is 0.5. 6PM is 0.75. We see a time on the Excel spreadsheet because the cell is formatted with a time format.

GetPivotData() Functions in Excel XP »

February 4, 2004

Formula pointing at a cell inside the pivot table keeps pointing at the original cell when copying the formula down to the next rows.