Excel Fun – Musical Spreadsheets

Last week I saw a an article by Giles Thomas, who uses a Resolver One spreadsheet to play music.

ResolverPlayer

Play Music in Excel

If it can be done in another spreadsheet, Excel must be able to play music too, so I checked with the most likely suspect, John Walkenbach. Sure enough, he created an Excel midi-player that you can download.

NO LONGER AVAILABLE

Play Music in Excel Workbook
Play Music in Excel Workbook

Duelling Banjos in Excel

John also has a Duelling Banjos Excel player, that he posted on his blog a couple of years ago.

DuelBanjo

Excel Web Radio

Another way to get music in Excel is with this Excel Web Radio Streaming sample file, created by Harald Staff.

Harald’s web radio Excel workbook is small (47 kb), and easy to use.

To get the Excel file, and start your own music station, go to the Harald Staff Sample Files page in my Contextures site.

excel web radio

Music Playlist Creator

Even if you’re not musically gifted, you can still use Excel to help you enjoy your music collection.

Dave Peterson has created an Excel Music Playlist Creator that you can download.

The code in Dave’s workbook creates a playlist of music from a selected folder, and puts it on your desktop.
_______________________________

Open Related Excel Files-Save Workspace

Do you have a few Excel files that you usually have open all at the same time?

  • Maybe the files are linked, and you update one and check the results in the other file.
  • Or maybe there are a few files that you open first thing every morning, to enter or update the data.

Open and Arrange Files

After you open the files, you might spend a minute or two arranging the files so you can see everything as you work.

Then you make the changes, close everything, and do the same thing again tomorrow.

Save Time With Excel Workspace

To save time, you can use Excel’s Save Workspace feature. It remembers which files are open, how you have them arranged, and where the files are located.

The Workspace file doesn’t contain the files themselves.

You’ll still be able to open the files individually, and use the Workspace file when you want to open them together.

  • [UPDATE: Unfortunately, Excel’s Save Workspace feature is not available in newer versions of Excel]

Prepare the Files

  1. Open all the files that you want to use.
  2. Arrange the files any way you’d like (Tiled, Vertical, etc.)

Create a Workspace File

In Excel 2007 / 2010, click Save Workspace, on the Ribbon’s View tab.

Excel 2007 / 2010, click Save Workspace
Excel 2007 / 2010, click Save Workspace

Excel 2003 Workspace

In Excel 2003, click the File menu, then click Save Workspace.

SaveWorkspace

Type a name for the Workspace file, and click OK. The xlw extension will be automatically added to the file name.

Close a Workspace File

In Excel 2003, hold the Shift key, click the File menu, and click Close All.
In Excel 2007, close each file individually, or add the Close All command to the Quick Access Toolbar.

Or, to close all the files, and Excel, hold the Shift key, and click the X at the top right of the Excel window.

Open a Workspace File

Open a Workspace File, just as you would open any other Excel File.

Click the Open button, select the Workspace file, and click Open.
__________________

Show or Hide Excel Data Entry Pop-Up Tips

When you set up a worksheet for other people to use, data validation messages can help them get started. The messages appear, like little ToolTips, when a user clicks on a cell.

Users Show or Hide Messages

After using the Excel workbook for a while, users might not need those messages anymore, and the pop-up messages become annoying, rather than helpful.

AlexJ, who recently shared his technique for hiding rows with Excel outlining, has created another useful sample.

In this file, he lets users turn those data validation messages on or off, by choosing TRUE or FALSE from a drop down list.

turn those data validation messages on or off
turn those data validation messages on or off

Show Messages – TRUE

When TRUE is selected, the label cells, such as Name, are green. Click on a label cell and a data validation input message appears, with instructions for that field.

Show Messages - TRUE
Show Messages – TRUE

Choose FALSE for No Messages

Select FALSE and the label cells turn white, and no data validation input message appears when you click on a label cell.

Choose FALSE for No Messages
Choose FALSE for No Messages

How Show/Hide Messages Works

The TRUE/FALSE drop down list is in a cell named ShowUserMsg, and the list is created with data validation.

APJShowMsg03

The labels cells also have data validation, which is set to allow Any value.

APJShowMsg05

For each label, an Input Message is entered in the Data Validation dialog box.

APJShowMsg06

Colour Cells with Conditional Formatting

The label cells and ShowUserMsg cell are coloured with Conditional Formatting.

Excel Conditional Formatting Rules
Excel Conditional Formatting Rules

Excel VBA Code -Show or Hide the Messages

When you select an option from the TRUE/FALSE list, a Worksheet_Change event runs, and turns the messages on or off.

To see the code in Alex’s sample file, right-click the Show User Messages sheet tab, and click on View Code.

APJShowMsg07

Download the Sample File

You can download the Show or Hide Messages file from AlexJ’s Sample Spreadsheets page on the Contextures website.

In the Data Validation section, look for DV0001 – Show or Hide User Tips
___________________

Custom Colour Tips For Excel

Do you use many colours in Excel?

I keep most of my Excel workbooks relatively colour free, except for a few headings or charts, or to mark cells for data entry. Usually, I use Excel’s standard colours, but sometimes I need something a little different.

For example, if I’m building a workbook for a client, I might want to match their corporate colours.

Modify a Colour in Excel 2003

The colour options are hard to find in Excel 2003. To use a new custom colour to your workbook, you’ll have to modify one of the existing colours.

  1. On the Tools menu, click Options.
  2. On the Color tab, click on one of the standard colours that you don’t plan to use in this workbook
  3. Click Modify, to open the Colors dialog box

ColorMod2003

Select a Colour in Excel 2007

In Excel 2007, you can use a Ribbon command to open the Color dialog box.

  1. On the Ribbon’s Home tab, click the arrow at the right of the Fill Color or Font Color button
  2. Click More Colors…

FillMoreColors

The Color Dialog Box

In both versions of Excel, the Colors dialog box looks the same.

ColorsCustom

Click on a colour in the Standard tab, or click the Custom tab for more choices.

Make It Bigger

To make it easier to see the colours, double-click the Colors title bar, and the dialog box will expand to fill the screen.

That lets you really zoom in on the colour choices, at the pixel level, and find what you need.

double-click the Colors title bar
Double-click the Colors title bar

Select a Custom Colour

If my client provided colour information, I can enter the Red, Green and Blue numbers on the Custom tab, for an exact match.

When I don’t need an exact match, I can move through the custom colour screen until I find something that looks appropriate for the workbook.

To move through the Custom Colors palette:

  • Click on a colour with the mouse pointer
  • Or, on the keyboard, use the arrow keys to move up, down, left or right

At the bottom left of the Colors window, the Red, Green and Blue numbers will change, as you move through the colours.

_____________________

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.

FirefoxRecentClose

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.

FirefoxUndoClose

__________________________________

What’s in Your Desk Drawer?

Recently, I’ve made some progress in clearing out my office, and organizing my files and bookshelves. This week, after I was stabbed by a push pin, while trying to dig out a paper clip, I realized that my desk drawer could use some improvement.

DeskDrawer

At some point, I had good intentions, because you can see the boxes and clear container with storage compartments, that were meant to organize things. Somewhere, though, things got off track.

  • Buried under the top layer are the paper clips, in assorted sizes, that I need occasionally.
  • The glue sticks and Scotch tape are easily accessible, but I don’t use them too often.
  • Stamps, that I use about once a month, are easy to find.
  • A nice eraser, for those paper-based mistakes
  • Hand cream and nail clippers, because good grooming is important
  • The rest of it, I should toss out, or at least move to a different part of the office, so I can find those paper clips when I need them.

That desk drawer is prime office real estate, and I’m cleaning it up.
How about you?

Is your desk drawer better organized than mine? Or are you risking injury every time you reach for a paper clip?
______________

Excel Scroll Wheel Shortcuts

I started on a Mac, where the mouse had one big button. Later, I moved to a PC, with a two button mouse. For the past few years I’ve used a mouse with two buttons and a scroll wheel.

Scroll Through Excel

At first, I completely ignored the scroll wheel, and found it faster to drag the scroll bar, than to move the scroll wheel. Now I use the wheel occasionally, but more often in Firefox or Word, than in Excel.

Use the Scroll Wheel With Outlines

AlexJ, who shared his technique to hide Excel rows with outlining a few days ago, also uses the scroll wheel to show and hide outline levels.

  1. Point to a cell within the outlined range
  2. Press the Shift key, then roll the scroll wheel down, to hide the outline levels for that cell.
  3. Or, press the Shift key, then roll the scroll wheel up, to show the outline levels for that cell.

Use the Scroll Wheel to Zoom

He also reminded me that you can use the scroll wheel to zoom in or out on the worksheet.

  1. Point to a cell within the outlined range
  2. Press the Ctrl key, then roll the scroll wheel down, to hide the outline levels for that cell.
  3. Or, press the Shift key, then roll the scroll wheel up, to show the outline levels for that cell.

Use the Scroll Wheel to Scroll

And yes, you can even use the scroll wheel to scroll. Really fast!

  1. Click the scroll wheel, and a four-headed arrow appears
  2. Move the mouse up, down, left or right, and a single arrow appears, pointing in the direction that the mouse is moving.
  3. The Excel sheet scrolls very quickly, increasing in speed as you move the single arrow toward the edge of the worksheet.
  4. To stop the scrolling, tap the scroll wheel, or click a mouse button.

Other Scroll Wheel Shortcuts?

Do you use the scroll wheel? Do you have any other shortcuts for it?

My Logitech mouse with its scroll wheel
My Logitech mouse with its scroll wheel

_________________________

Paste Values Shortcut For Excel

After I create formulas in Excel, sometimes I want to lock in the results of those formulas, so I use the Paste Values command.

For example, I might use the RAND function to create a set of random numbers. After creating them, I don’t want them to change every time the sheet calculates.

Continue reading “Paste Values Shortcut For Excel”

Do You Subscribe To Your Own Blog?

Ever since I started my blog, I’ve subscribed to the RSS feed. That lets me know if everything is working as expected with the feed, and I can investigate any problems as early as possible.

It’s also a good check when I upload an article with a delayed posting time. Sometimes I forget to set the posting time as AM, instead of PM, so the delay is much longer than I intended.

If I notice that the feed hasn’t been updated, I can go to the blog and change the publishing time.

Subscribe By Email Too

However, I also have an email subscription option on my blog, and hadn’t tested that.

Subscribe

I finally subscribed last week, which gave me the chance to see how the subscription process works (seemed pretty easy).

Now I get an email every morning, with the latest Contextures blog entry, and can follow up if there’s any problem with that delivery method.
___________