Elvis Sings Excel: A Little Less Concatenation

Last Friday, January 8th, would have been Elvis Presley’s 75th birthday. Sadly, he died in 1977, so he never had a chance to work with Microsoft Excel. Otherwise, he might have sung “A Little Less Concatenation”, instead of “A Little Less Conversation.”

Continue reading “Elvis Sings Excel: A Little Less Concatenation”

Excel Calorie Burning Calculator

Good work! You’ve stopped eating cookies, and you’re recording your daily food intake in the Excel Calorie Counter.

The pounds are magically melting away, and you’re keeping track of your weight loss.

Here’s one more tool to help you with your healthy living plans – the Excel Calorie Burning Calculator.

Continue reading “Excel Calorie Burning Calculator”

Happy New Year With Excel Fireworks

Happy New Year! All the best in 2010, and I hope you’re looking forward to the release of Office 2010.

Excel Fireworks

I thought about creating some fireworks in Excel, then discovered that Andy Pope already made some, based on an XY scatter chart.

Fortunately, there’s no noise, so if your head hurts a little today, the fireworks won’t make it worse.

In Andy’s workbook, you can set the number of rockets, the sparks in each rocket, and the effect of gravity. That could keep you busy for hours!

Excel Fireworks by Andy Pope
Excel Fireworks by Andy Pope

Excel Scrolling Banner

Andy also has a sample Excel workbook with scrolling text.

If you’re feeling adventurous, you could combine Andy’s two workbooks to show Happy New Year, and a fireworks display.

Excel Scrolling Banner
Excel Scrolling Banner

More New Year’s Day Fun

And for one more bit of Excel fun on New Year’s Day, Andy Pope has a free tool for converting a normal BMP image into an Excel scatter chart.

Here’s a screen shot of the BMP to Chart dialog box, where you can select a BMP format image file.

Andy Pope's BMP to Chart tool
Andy Pope’s BMP to Chart tool

Line Chart vs Scatter Chart

If you’re not familiar with Excel scatter charts, take a look at my blog post that compares Excel line charts and scatter charts.

difference between line chart (left) and scatter chart (right)
difference between line chart (left) and scatter chart (right)

_________________

Excel New Year’s Resolutions 2010

NewYears What will you do better in Excel next year? Do you have any Excel bad habits that you’ll break? Are there any Excel skill areas that you’ll improve?

Excel Resolutions

Based on the questions that I see in the Excel newsgroups and forums, lots of people could resolve to do one or more of these things:

  • Save your Excel files every few minutes as you work on them. Jan Karel Pieterse has a terrific (and free) Excel add-in – AutoSafe – that will automate the saves for you, and it’s way better than Excel’s built in autosave feature.
  • Make backup copies of every important Excel file, and update those backups regularly. Yes, something will go wrong eventually, and those backups are lifesavers.
  • Add an instruction sheet to your key Excel files and complex worksheets. Then, if you’re away for a holiday, your co-workers won’t have to call you with frantic questions, or mess things up on their own. Maybe that’s what happened to the banking industry.

My Excel Resolutions

Here are my Excel goals for 2010:

  • Spend a couple of hours each week working with the Excel 2010 beta, and PowerPivot for Excel add-in, so I’m ready to roll when the final version is released.
  • Make better notes when I write and edit code. Sometimes it’s clear what the code is doing, but I can’t remember why I decided to do things a specific way. A few notes could save time and frustration later.
  • Update my Pivot Table add-in so all the features work in Excel 2007. Jon Peltier is planning a series on creating Excel add-ins, so that could give me some ideas for improving my add-in.

Your Excel Goals for 2010

What about you? Do you have an Excel goals for 2010 that you’d like to share?

Making your Excel resolutions public might help you stick to them!
____________

Create Excel Grand Totals With One Click

I hope you had a wonderful Christmas, and with any luck, you’re taking this entire week off.

You might still be full of turkey and eggnog, so I’ll just give you a quick and easy Excel tip today – something that’s easy to digest.

Create Quick Excel Grand Totals

Instead of entering each SUM function individually, you can use the AutoSum feature to create all the grand totals with one click.

Here are the simple steps to follow:

First, select all the cells with numbers, and the blank cells below and to the right, of those cells, where you want the grand totals

Select number cells and blank cells
Select number cells and blank cells

Use the AutoSum Button

Next, go to the Excel Ribbon, and click the Home tab

At the right end of the Home tab, in the Editing group, click the AutoSum button, to insert the Grand totals.

Click AutoSum button on Home Tab
Click AutoSum button on Home Tab

Grand Totals in Blank Cells

On the worksheet, the SUM function is added to each grand total cell, to sum the cells above, or to the left.

Grand Totals for rows and columns
Grand Totals for rows and columns

Watch the Video

To see me create Grand Totals in Excel with one click on the AutoSum button, you can watch this 12-second Excel video.

P.S.: For more Excel SUM tips visit my Contextures Excel SUM Functions page.

Excel VBA: Show a Message Before Printing

A bit of Excel programming can help us save some time and paper. Our goal is to stop people from printing an order form if they forgot to enter a customer name.

Final Macro Changes

Last week, you created a macro to show a message if the customer name was missing in an order form.

Today, you’ll make a final change to the macro, so it will run automatically when someone tries to print the order form.

Sample File: You can download the Excel order form with last week’s message macro. Enable macros when the file opens.

Create the Event Code

To create Excel code that runs automatically, you can use Event procedures. Those are macros that run when something specific happens in Excel, such as changing a cell on a worksheet, or printing a worksheet.

  • To open the Visual Basic Explorer (VBE), right-click on the Order Form sheet tab, then click View Code.
Create the Event Code
Create the Event Code

In the VBE, find the order form workbook in the list of projects, and under Microsoft Excel Objects, double-click ThisWorkbook.

Excel VBE ThisWorkbook

  • At the top left of the Code Window, select Workbook from the drop down list.

Excel VBE Workbook

  • An event procedure for Workbook_open is automatically created, but that’s not the one we need, so just ignore it. At the top right of the Code Window, select BeforePrint from the drop down list.

Excel VBE Before Print

  • The cursor should be flashing between the Sub and End Sub lines for the Workbook_BeforePrint macro. We want to check the order form before someone prints it, so type the following 2 lines of code, to check the name of the active sheet:

VBE Before Print 02

  • Next, copy the code from the CustomerMessage macro, on the Module1, and paste it between the If and End If lines.

VBE Before Print 03

Now, when someone tries to print or print preview, Excel will check the sheet name.

  • If OrderForm is the active sheet, it will check for a customer name in cell B5.
  • If the customer name is missing, our warning message will appear.

Test the Event Code

To see if the BeforePrint code is working correctly, you can test the code by using Print Preview.

  1. On the Order Form worksheet, delete the Customer Name in cell B5.
  2. Click the Office Button, point to Print, then click Print Preview.

The good new is that the warning message appears, because the Customer Name cell is empty. The bad news is that the Print Preview opens, even though the customer name is missing.

Fine-Tune the Event Code

You can see “(Cancel as Boolean)” at the end of the first line in the Workbook_BeforePrint macro. Some Excel events have a Cancel option, which allows you to cancel the event, if necessary.

We’ll add one more line of code, to cancel the printing if the customer name is missing.

  • In the Workbook_BeforePrint code, add a blank line, after the MsgBox line.
  • In the blank line, type: Cancel=true

VBEBeforePrint04

  • Then, save the workbook, and try the print preview again.

Now the warning message should appear if the customer name is missing, and the print or print preview should be cancelled.

If the customer name is filled in, then no message will appear, and the order form will print, or show up in the print preview window.
_________________

Previous Excel VBA articles:

Getting Started With Excel VBA

Excel VBA: Edit Your Recorded Macro

Excel VBA: Show a Message to Users

Excel VBA: Show Message Automatically

_________________

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.
_____________