Quickly Create Named Ranges in Excel

In an Excel workbook you might have a worksheet that contains several lists that you use as the source for data validation drop-down lists.

Worksheet List

For example, this worksheet has a list of countries, and lists of regions within those countries.

If each list has a heading, you can quickly create named ranges from the lists.

worksheet list of countries
worksheet list of countries

Create the First Named Range

To quickly create a named range, follow these steps:

  • Select the heading and the items in the first list that you want to name.

RangesSelected

  • On the Excel Ribbon, click the Formulas tab
  • In the Defined Names group, click Create from Selection
  • (Note: In Excel 2003 and earlier versions, click Insert > Name > Create)
Create from Selection command
Create from Selection command

Create Names dialog box

  • In the Create Names dialog box, add a check mark to Top row
  • Next, remove any other check marks, then click OK.
Create Names dialog box
Create Names dialog box

Name the Remaining Ranges

To name the next range, follow these steps:

  • First, select the range’s heading and items
  • Next, on the keyboard, press the F4 key
    • This is the Repeat Last Action keyboard shortcut
  • Repeat for all the remaining ranges.

_______________

Remove Old Addresses From Outlook Drop Down List

In Outlook, as you start typing a name in the To box, a drop down list may appear, showing names of people to whom you’ve previously sent an email.

Here’s how you can remove old addresses from Outlook drop down list, in Outlook for Windows or Outlook.com.

Continue reading “Remove Old Addresses From Outlook Drop Down List”

Filter Pivot Table Source Data in Excel

When you’re analyzing data in an Excel pivot table, you might want to see the detail behind one of the numbers.

To extract the data, you can double-click a data cell and a new worksheet is created, with the related records.

This is a nice feature, but you’ll end up with extra sheets in your workbook, and will need to clean things up occasionally.

Filter the Source Data

If the pivot table source data is in the same workbook, you can use the following macro, written by Héctor Miguel Orozco Díaz. It filters the source data, based on the pivot items connected to the double-clicked cell.

For example, if you double-click the cell circled in screenshot below:

pivotfilter01

the source data is filtered for Class_A, Month_3, Store_1, Code_A cost.

This lets you focus on the detail records, without creating new worksheets.

filtered source data for pivot table
filtered source data for pivot table

Download the Sample File

Héctor’s code is shown on my Contextures site, and you can download the sample file to filter a pivot table’s source data.

There is also a sample file with a shorter version of the code.
________________

Weekend Backups

Over the weekend I did a backup of my RSS feeds, and created backup files for my WordPress blog.

How often do I do this? Not often enough.

So, I’ve added both items to my monthly task list, and that might help me remember to keep the backup files up to date.

I also have a Maxtor external hard drive on both my computers, and they do an automatic overnight backup.

maxtor-one-touch-4 external hard drive
maxtor-one-touch-4 external hard drive

How about you? Did you do a backup recently?
_______________

Quickly Split a Word Window

I’ve been reviewing Word documents recently, and sometimes I’m halfway down the document and have to check on something that was in an earlier part of the file.

I could scroll up, to check the text, then scroll back down to the current spot, but that’s not too efficient.

It’s easier, and more efficient, to split the Word window, so I can see two sections at the same time.

To split the window, you can choose Window|Split, then click where you want the split.

Another way to create a split it to point to the Split marker, at the top of the Vertical Scroll Bar, and drag it to the position you want.

Remove the Split

To remove the Split marker, double-click on it.

______________

Desktop Reference Books

What’s on your desktop bookshelf? I only have a few inches of space on the bookshelf above my computer, and it holds the books that I refer to most often. From left to right, they are:

  • Roget’s Thesaurus
  • French/English Dictionary
  • Notebook
  • Excel 2007 PivotTables Recipes
  • Beginning Pivot Tables in Excel 2007
  • Excel Pivot Tables Recipe Book
  • Manual of Style for Technical Publications
  • Oxford Canadian Dictionary
  • Binder with Contextures Procedures

DeskRef

The thesaurus is about 100 years old, or at least looks that old. I’ve had it since university days, and like it much better than the newer style.

Microsoft Word has a built in thesaurus feature, which I use occasionally, but it’s no substitute for Roget’s version.

My books are there because I do lots of work with pivot tables and can’t remember all the obscure issues and workarounds that I covered in the books.

The binder doesn’t have too much in it yet, but I’m trying to add more checklists and procedures, to make monthly tasks a bit easier.

I’d have room for more books if I removed the CD storage rack that’s to the right. It holds music CDs and I can’t remember the last time that I listened to one on the computer.

Everything that I want to hear is in an MP3 file, so maybe these should go out to the car, or down to the stereo system.
_________________________

No All Day Events in Outlook 2007 To-Do Bar

At the right of the Outlook 2007 you can display a To-Do Bar that shows a navigation calendar, a list of upcoming appointments and a task list. Very handy!

When I open Outlook to check my email, I can see my calendar and tasks at a quick glance. Here’s what the top of the To-Do Bar looks like.

ToDoBarAppts

Unfortunately, it’s not as useful as I first thought, since the To-Do Bar doesn’t show all day events. So, if you’ve added a conference that will span a few days, or entered the birthdays for your friends and family members, those won’t show up.

Fortunately, my mom’s birthday is in February, so I noticed this problem just in time! And if you didn’t get a birthday card from me, it’s Outlook’s fault.

Show Outlook Today

I’ll keep using the To-Do Bar, because I have a wide monitor, and the To-Do Bar is useful for the non-event items and tasks. However, I decided to change my Outlook settings to show Outlook Today upon opening.

That sounded like an easy thing to do, but I couldn’t find a switch to turn it back on. Outlook Help said to go to the Outlook Today view, and customize the settings there.

Great advice, but how do I get to the Outlook Today view? Outlook Help said to look in the Navigation Pane.

OutlookTodayClick

Well, I couldn’t see that icon with the others, at the bottom of the Navigation Pane.

OutlookTodayIcons

I hunted through the menus, checked Help again, and searched in Google, but couldn’t find any help on restoring my missing Outlook Today icon.

Then, I accidentally clicked on the top folder in my folder list, and Outlook Today miraculously appeared!

OutlookTodayIcon

Oh, that icon. Maybe the Outlook Help could have mentioned the Folder list when describing where to look for the icon.

Outlook Today Icon on the Advanced Toolbar

Update: Thanks to Jon Frank, who commented that the Outlook Today icon is also on Outlook’s Advanced Toolbar. To show that toolbar, click the View menu, then click Toolbars, and click Advanced.

Change the Outlook Today Startup Setting

After that frustrating exercise, the rest of the process was easy. At the top left of the Outlook Today view, I clicked the Customize Outlook Today button.

OutlookTodayCustBtn

Then I added a check mark to When starting, go directly to Outlook Today, then clicked Save Changes.

OutlookTodayCust

Now, when Outlook opens, I can see all my upcoming appointments and events.

It would be ever better if you could specify that you only wanted to see this the first time you open Outlook each day, but maybe they’ll add that to the next version.
__________________

Hide an Excel Macro

When you add a macro to an Excel workbook, users can see that macro in a list when they choose Tools|Macro|Macros.

MacroListShow

Hide Your Macros

In some workbooks, you might want to hide one or more of your macros, to prevent users from running them.

Note: This technique won’t add any protection to your code, it will simply remove the macro from the list, so casual users won’t accidentally or intentionally run it.

Make the Macro Private

In this example, I have two macros – one that the users can run, and one that I’d prefer to keep hidden.

Here’s the original Excel VBA code:

MacroListCode

Revised VBA Code

If I add the word Private at the start of the Sub MySecretMacro line, that macro will NOT be included in the macro list.

MacroListPrivate

Private Macro Not in List

Now only the MyVisibleMacro appears in the list.

Private Macro Not in List
Private Macro Not in List

Run the Hidden Macro

Now that you’ve hidden the macro, so casual users won’t find it, how can you run it yourself? Here are a few ways you can run it

  • Open the Visual Basic Editor (Alt+F11), click somewhere in the macro’s code, then click the Run button.

MacroListRun

OR

  • If you know the name of the macro, type it in the Name Box, at the left of the Formula bar.
  • Press the Enter key, and the Visual Basic Editor will open automatically, with the cursor flashing in the macro’s code
  • Click the Run button.

MacroListName

OR

  • Before you hide the macro, choose Tools|Macro|Macros, select the macro, and click the Options button
  • Type a keyboard shortcut (Ctrl + Shift + M in this example) then click OK

MacroListOption

  • Close the Macro dialog box.
  • Add Private to the macro code, then use your keyboard shortcut to run it when required.

______________________

Print Excel Sheet On One Page

Often an Excel worksheet is just a bit too wide to print on one page.

You could change it to Landscape orientation, so more will fit across, but you may prefer to keep it in Portrait orientation, especially if it’s a long list.

Make It Fit Page Width

In Excel’s Page Setup dialog box, you can adjust the scaling so the sheet will fit across on one page.

  1. On the Page tab, click the ‘Fit to’ option for Scaling.
  2. Enter 1 as the number of page(s) wide.
  3. Clear the box for the number of pages tall, to leave that box empty
  4. Finally, click OK.
Page Setup Dialog Box Scaling
Page Setup Dialog Box Scaling

No Tall Setting

Those changes to the page scaling settings will :

  • limit the file to printing one page across (wide)
  • will NOT limit the number of pages down (tall)

You don’t have to guess the number of pages required. Just leave that setting blank ,and Excel will figure it out for you.
_____________