Top 100 Canadian Singles in Excel

canada If you’re looking for love, move along — the “Canadian Singles” in the article title refers to hit songs, not eligible bachelors. Last week, a new book was published with a list of top 100 Canadian singles, based on a poll of music professionals and fans.
In his J-Walk blog, John Walkenbach posted a link to the Canada’s Top 100 Singles list, and there was a lively discussion in the comments section.

Top 100 Canadian Singles List

No discussion is complete without a spreadsheet, so I copied the list into Excel, and cleaned it up. To make it more interesting, I found the release date for each hit song, and split them into decades, using the FLOOR function.
From that data, I created a pivot table, showing the count of songs from each decade, listed by rank. Was most of the best music released in the 1970s, or were most of the voters from that era?
The top 100 songs are grouped by 10s, to summarize the data.
I added conditional formatting to highlight the decades with the largest number of songs.

Repeat Pivot Table Item Labels

A new feature in Excel 2010 pivot tables is the ability to repeat the field item labels. In another copy of the pivot table, I put the decade in the row label area, and changed the pivot table report layout to Outline Form.
Then, I right-clicked on the Decade field, and clicked Field Settings. On the Layout & Print tab, I added a check mark to Repeat Item Labels, and clicked OK.
After changing that setting, the decade is repeated in each row, instead of showing just once, at the top of the section.

Download the Top 100 Canadian Single File

To see the list, and create your own pivot table, you can download the sample file. There’s a Top 100 Canadian Singles list in Excel 2007/2010 format, and Top 100 Canadian Singles list in Excel 2003 format.

AutoFill Excel Dates in Series or Same Date

If you’re entering dates on an Excel worksheet, and there’s data in the adjacent column, you can use the Fill handle to make the job quick and easy.
Enter the first date in the date column, then point to the Fill handle, and double-click.
That fills the dates down to the first blank cell in the adjacent column. The dates are entered in a series of 1 day intervals.
If you want all the dates the same, while the range of cells is still selected, press Ctrl+D on the keyboard. That’s the keyboard shortcut for Fill Down, and it copies the date from the first cell, into all the selected cells below.

Watch the Video

To see the steps for filling the dates, you can watch this very short video. I created this with the latest version of Camtasia Studio, which has a new feature that captures keyboard shortcuts, and can show the key combinations in the video.


Quick Excel Tips: Paste Values and Sort

How long is your attention span? It’s a short work week, so let’s start it off with a couple of quick Excel tips. And when I say “Quick,” I mean, “Don’t blink, or you’ll miss them.”
I recorded a few quick Excel tips, and posted them in the Excel Quick Tips playlist on my Contextures YouTube channel. If you can’t sit still through a lengthy 2 minute video, this might be the solution!

Select a Random Name

The first video shows you how to select a random name from a list. Enter the RAND function in the column adjacent to the list of name. Then, sort the column with the RAND function, and the name at the top is the winner.

This is the same technique that I used to select winners in my Excel Summer Giveaway and Excel Fall Giveaway contests. You can see the RAND function being used in the Excel random name draw video.

Paste Formulas as Values

In the second video, you can see how to use the mouse to copy cells that contain formulas, and paste the results as values.

If that was too quick for you, there’s a longer version of this video that shows the Paste Values in Excel with a Mouse Shortcut technique.

See Formulas on an Excel Worksheet

Last week I was testing a client’s workbook, and had filled in all the data entry cells, to make sure everything was working correctly.
Before sending the workbook back to my client, I wanted to clear all the data entry cells. Instead of selecting each cell individually, and clearing it, it would be easier to clear groups of adjacent cells where possible.
However, some cells had formulas, and I didn’t want to accidentally clear any of those. If the formulas are visible, that would prevent the problem.

To see the formulas in Excel 2003:

  • On the Tools menu, click Options
  • On the View tab, add a check mark to Formulas.


To see the formulas in Excel 2007

Click the Office button, then click Excel Options
Click the Advanced category
In the Display Options for This Worksheet section, add a check mark to Show formulas in cells instead of their calculated results.

Show or Hide Formulas with a Keyboard Shortcut

The keyboard shortcut to show or hide the formulas is Ctrl +   (accent grave, which may be above the Tab key on the keyboard)

Excel 2007 Keyboard Shortcuts for the Ribbon

While trying to type a hyphen in Excel 2007, I stumbled onto a handy keyboard shortcut.
I had a bit of an overreach on the hyphen, and hit the F10 key instead. To my amazement, the Ribbon filled with little tags.
Maybe I’ve been living under a rock, but I don’t remember seeing those before.
There’s a number or letter on each Ribbon tab, and each icon on the Quick Access Toolbar (QAT).

Use the Shortcut keys

Pressing the F10 key activates the Ribbon. Then, type the letter(s) or number(s) that you see on any tab or QAT command.
For example, I can type an A to activate the Data tab. (Note: Even if the Data tab is active, you’ll have to type an A to see its command tags.)
Tags will appear on the available Data commands.
Because there’s a table on my worksheet, most of the commands are available, so I can type T to add an AutoFilter or type Q to start an Advanced Filter.
It seems like a useful feature, and the only shortcut that I have to memorize is the F10 key. Anyway, it’s nice to know that a typo can lead to an interesting discovery.

AutoFilter by Selection in Excel 2007

A couple of weeks ago I described how you could select a cell in a table, and automatically filter the list based on that cell’s value. The same feature is available in Excel 2007, using a different technique.
Using the same example as in the previous post, the East region is selected in the table below. With a couple of clicks, and no programming, you can add an AutoFilter and filter the table to show only the East region orders.

Apply the AutoFilter

In previous versions of Excel, you had to add a toolbar button to use the filter by selection feature. In Excel 2007, the feature is available in a shortcut menu. The table doesn’t need to have an AutoFilter currently applied.

  1. In a table in Excel, right-click a cell that contains the criterion you’d like to use. For example, to filter for the East region records, right-click an East cell in the Region column.
  2. On the shortcut menu, click Filter, then click Filter by Selected Cell’s Value

An AutoFilter is added to the table, if there wasn’t already one in place. The table is filtered, and shows only the East region records.

Remove the Filter

To remove the filter, and show all the records again:

  1. In the Region column heading, click the AutoFilter drop down arrow
  2. Click Clear Filter From “Region”.

The filter is removed from the Region column, but the AutoFilter feature is still turned on.
For more information on Excel AutoFilters, visit the pages on Excel AutoFilter Basics and Excel AutoFilter Tips on my website.

Paste As Values on a Filtered Sheet

In related news, I recently discovered that the mouse shortcut to copy and paste as values doesn’t work anywhere on a filtered sheet, unless all the records are showing. Here you can see that it’s not available on the shortcut menu.
You can use other methods to copy and paste as values, such as the Ribbon command, but not the shortcut. I wonder why.

Show Excel Formulas Instead of Results

When you’re troubleshooting an Excel worksheet, it may help to see the formulas temporarily, instead of the results. With the formulas visible, you can quickly check that the cell references are correct and the formulas are consistent.
Tip: The keyboard shortcut to show or hide the formulas is Ctrl + ‘ (accent grave, may be above the Tab key on the keyboard).

To view the formulas in Excel 2003:

  1. On the Tools menu, click Options
  2. On the View tab, under Windows Options, add a check mark to Formulas.

To view the formulas in Excel 2007:

  1. On the Ribbon, click the Formulas tab.
  2. In the Formula Auditing Group, click Show Formulas.



Minimize the Excel 2007 Ribbon

If you’re working on a big worksheet, you might want to see as many rows as possible. The Excel 2007 Ribbon uses about an inch of space at the top of the worksheet, and if you’re just entering or editing data, you probably don’t need to use the Ribbon commands.
You can temporarily minimize the Ribbon, so it only uses a bit of space, and that will make room for a few more rows in the Excel window.

Minimize or Restore the Ribbon

To minimize the Ribbon, double-click on the active tab.
To restore the Ribbon, so the commands are visible, double-click on the active tab.

Close Excel

One more mouse shortcut, while you’re using the Ribbon — To close Excel, double-click the Office Button, at the top left of the Ribbon. If unsaved files are open, you may be prompted to save the changes.

See The Steps in a Video

To see the steps in action, you can watch this very short video.


Create a Desktop Shortcut To Open Files Quickly

While working at a client’s office, I sometimes create an Excel workbook that a user will enter data in every day. To make it easy to open the workbook, I add a shortcut to the user’s desktop (with their permission, of course). This makes it easy for the user to open the file, without trying to remember where it’s saved.

To quickly create a shortcut:

  1. Open Windows Explorer
  2. Select the folder that contains the Excel workbook
  3. Right-click on the Excel file
  4. In the popup menu, click Send To
  5. Click Desktop (create Shortcut)
  6. Close Windows Explorer.

A shortcut appears on the desktop, and you can move it to the spot where you want to store it.

To rename the shortcut

  1. Click on the shortcut to select it
  2. Press the F2 key on the keyboard, to select the name
  3. Type a new name and press Enter.


Re-open a Firefox Tab That You Accidentally Closed

Do you ever accidentally close a tab in Firefox, then want to get back to a website that you were exploring in that tab?

It happens to me a couple of times every day – I click on a link, and when I’m finished reading that page, I close the tab. Oops! That link hadn’t opened in a new tab, and now I’ve lost the original page that I was reading.

Re-open a Closed Tab From the History Menu

Fortunately, Firefox remembers what you’ve had open, and stores those tabs in its History.

To re-open a closed tab:

  1. Click the History Menu
  2. Click Recently Closed Tabs, to see a list of web page names.
  3. Click the name of the web page that you want to re-open.


Re-open the Last Closed Tab From the Shortcut Menu

You can also re-open the last closed tab by using a shortcut menu:

  1. Double-click in an empty area of the tab strip, for example, to the right of the last tab.
  2. In the shortcut menu that appears, click Undo Close Tab.