About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store



Past Tip of the Day


From this weekend's mailbag:

Carla writes: how do you copy & paste a subtotaled table onto a worksheet?

Let's say you have a thousand-line table. With clever subtotaling, you create a 30-line summary. You want to copy this summary onto a worksheet containing other information. It's a one-page summary report with data from all over heck n gone.

With ordinary copy-and-paste, the whole entire damn table gets copied. I've been re-tying the information most laboriously. There must be a shortcut!

Yes, Carla there is. It involves the use of the Goto-Special dialog and then selecting Visible Cells Only. All the details are in the tip of the archive. See Use Goto - Special - Visible Cells to copy just the visible cells. This is a great tip for copying the results of using Data - Subtotals.

Troy asks: Is there a way to convert a database of phone numbers that look like this
123/456-7890 or 123-456-7890
to (123) 456-7890?

Yes - Assuming your phone numbers are in A2:A4001, enter this formula in a blank B2:
="("&Left(A2,3)&") "& Mid(A2,4,8)
Then copy down to B2:B4001.

Before deleting column A, you will want to change the formulas in column B to values. Highlight B2:B4001. Use Ctrl+c to Copy, then Edit - PasteSpecial - Values - OK to change the formulas to values.

Arun asks, all Excel functions - list them please
How do people end up with Excel, but not the help files? You can download a dictionary of Excel functions here:

By Bill Jelen on 21-Oct-2001 Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.


Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.