If it's Tuesday, it must be mauve

Conditional formatting is one of Excel's better features. It allows you to preset certain font styles, colors, and cell-background colors based on cell values.

This can be very useful for highlighting important information and values outside an accepted range or providing a visual cue to associate value ranges with color codes.

The best part is that conditional formatting is very easy to set up.

Just click the cells you'd like to format and select Format >Conditional Formatting. The Conditional Formatting dialog box lets you set up the conditions by which the formatting of the cell will occur.

You pick the operator (between, equal to, less than, etc.) and the value or range of values. Click Format to open the Format Cells dialog box, where you can select the colors and styles to be used.

Each cell can have several conditional formats. For example, you might say that if a certain cell's value is between 20 and 50, the text should be blue on a yellow background.

However, you can format that same cell to exhibit red, bolded text on a green background if it contains a value between 51 and 100.




Before 2007, you could use up to three conditions, but earlier versions of Excel can be tricked to use more if it should become necessary.

Oz Grid:
Excel VBA Macro Code to Get Around Excel's 3 Criteria Limit in Conditional Formatting

GR Business Process Solutions:
Graham Barrow and Ray Blake
Highlight the current or past month in Excel with conditional formatting

Chip Pearson:
Conditional Formatting

Also

Compatibility in Excel 2007

Conditional Format Functions in Excel 2007

Adding Customized Rules to Excel 2007

Demo:
Data takes shape with conditional formatting



[Edited entry from 1/2/2005]




See all Topics

Labels: Excel

Excel the way you've wanted it

You can download a 30 day trial at PUP V6 Home

Here are some features just in the area of Chart & Graphics Tools:

Chart Data Labeler: IMPROVED

Lets you apply labels (contained in a range) to a chart data series -- a feature that is surprisingly missing from Excel. Enhancement: Now ignores data that is hidden as a result of autofiltering.

Resize Charts: NEW

Makes it easy to make a chart an exact size, or make all charts the same size.

Convert Chart To Picture: NEW

Makes a copy of a chart as a static picture. This is useful for saving charts that depict various what-if scenarios.

Chart Report: NEW

Produces a detailed report that documents a chart, or produces a report that documents all charts.

Object Align, Size & Space: NEW

Back by popular demand. This utility was removed from PUP v5, but now it's back. It provides an easy way to precisely align, size, and space a group of objects or embedded charts.

Object Hide / Unhide: NEW

An interactive way to hide or unhide charts and other objects on a worksheet.

There is, of course, PUP v7 Power Utility Pak v7

You can also see his blog at J-WalkBlog.com.

The blog has little or anything to do with Excel.
It covers everything else in all the other worlds.

[Edited entry from 12/17/2004]




See all Topics

Labels: Excel

Formatting slip

The formula is filled down the column as long as there is data in an adjacent column.

But wait! I don't see the value. I see the formulas!
In addition, the formulas are still in lower case and the relative references have not been updated.

Easy to fix, I hear you say. Just reformat the column as General.

Nothing happens.

To fix the problem, make sure the column is formatted as General.

Select the first cell. Click in the formula bar and hit the Enter key.

Now double click the Fill handle.

(You could also use Edit>Replace to replace = with =. However, Relative references will be incorrect and unless you have reformatted the whole column as General, any new formulas will still display as text.)

The reverse also causes a problem. In a column formatted as General, enter some formulas.

Now reformat the column as Text. The formulas still work, but if you edit one of them, it reverts to a text display.

Microsoft KB:
Cell Linked to Text-Formatted Cell Shows Formula Not Value

Formulas can, of course, be toggled using CTRL+~ (Tilde)
(Though it really should be called CTRL+` (Grave Accent), since the Shift key is not used.)

[Edited entry from 11/9/2004]



See all Topics

Labels: Excel

Month Day, Day Month

"QDE is a fully-functional Excel Add-in that provides quick input of dates, in all international formats. It handles quick data entry interpretation and reflects the three interacting issues of Date System, Day, Month Year ordering, and number of digits used in the quick date entry. With QDE you enter just as many digits as needed to clearly identify the date, QDE will do the rest."

Also see:

Chip Pearson:
Date and Time Entry

MathTools.net:
Time and Date

And:
Date Arithmetic

[Edited entry from 11/24/2004]




See all Topics

Labels: Excel

Who's counting?

2004 has 53 weeks. January 1 is the only day in the first week of 2005. Week 2 starts on Sunday 1/2/2005.

Chip Pearson is the Date and Time guy:
Week Numbers In Excel

"Under the International Organization for Standardization (ISO) standard 8601, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.

While this provides some standardization, it can lead to unexpected results - namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.

An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec."

The first week of 2005 should start on January 3. The first and second would be part of week 53 of 2004.

Wikipedia:
Week Dates

If your week starts on a different day, you can use the Analysis ToolPac function:
=WEEKNUM(A1, 2) for a week that starts on Monday, =WEEKNUM(A1) if it starts on Sunday.

Also this from ExcelTip.com:
Weeknumbers using VBA in Microsoft Excel

"The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function shown here will calculate the correct week number depending on the national language settings on your computer."

In Access:
DatePart Function

If your work week is always Saturday through Friday then

datepart("ww",[DateField],7,1)

will return 1 for 1/1/2005 through 1/7/2005, 2 for January 8-14/2005, etc.
Otherwise use 1 for Sunday through 7 for Saturday.

The last number sets these parameters:

1, Start with week in which January 1 occurs (default).
2, Start with the first week that has at least four days in the new year.
3, Start with first full week of the year.

[Edited entry from 12/2/2004]



See all Topics

Labels: Access, Excel

Choose your own location

Word

you can change the default location by going to Tools>Options. On the "File Locations" tab you can modify the storage location.

Excel

Tools>Options. On the "General" tab change the default location.

PowerPoint

uses Tools>Options and the "Save" tab.

Access

Tools>Options and the "General" tab for Databases and Projects

Publisher

Tools>Options "General".

Outlook

will make you take an underground tour into the Registry to change the location to save e-mail attachments.

FrontPage/Expression Web

appears to require the same sort of spelunking.

Labels: Access, Excel, Outlook, PowerPoint, Word

Free money (tracker)

Your income

Just your take-home pay, which is what you control and spend.

Cash reserve

A reserve equal to 3-6 months of take-home pay.

Savings

A portion of your income set aside to fund your retirement.

Fixed expenses

Expenses that you must pay over the course of a year.

Replacements

You also must pay to replace things that eventually wear out.

Weekly allowance

Partly discretionary, partly not; paid by cash from your pocket.

Periodic purchases

Discretionary purchases paid by check periodically.

Future purchases

Big-ticket discretionary purchases that you must save for.

Summary

A summary of your income and where it goes—and where you are.

QuickForm

You can switch to this format once you're familiar with the method.

Tips

Ideas to help pare your expenses.

Labels: Excel

Shake hands with a worksheet

• Add a Get Started tab to the Ribbon
  
  
• Take advantage of the Quick Access Toolbar
  
  
• Minimize the Ribbon, maximize your workspace
  
  
• Choose your own default font
  
  
• Restore missing worksheet tabs
  
  
• Freeze panes here, there, and everywhere

Labels: Excel

Smile!

1. Edit>Copy the Cell or Range
   
2. Choose the target cell
   
3. Hold down the Shift key and choose Edit>Paste Picture Link

If you're going to do this frequently, go to Tools>Customize. Choose the Commands tab and locate Camera in the Tools category. Drag to place the Camera button on your toolbar.

If the Camera tool is on your toolbar:

1. Select the Cell/Range
   
2. Click the Camera tool
   
3. Move the cursor to the target position and click to insert the linked picture

To create a static picture from cells, or a chart to use in another part of your workbook, or another program:

1. Clear cell gridlines if you do not want them displayed in your picture.
   (Click Options on the Tools menu, click the View tab, and then clear the Gridlines check box")
   
   
2. On the worksheet or chart sheet, select the cells or click the chart you want to copy as a picture
   
3. Hold down SHIFT and click Copy Picture on the Edit menu
   (For the best picture quality, make sure As shown on screen and Picture are selected)
   
4. Click OK
   
5. Select the worksheet or other document where you want to paste the picture
   
6. Edit>Paste.

You can use the Picture toolbar to change the image.
(to display the toolbar, right-click any toolbar and choose Picture)

To paste information you've copied from another program as a picture in Microsoft Excel, hold down SHIFT and click Paste Picture or Paste Picture Link on the Edit menu.


You can, also, create a dynamic linked text box by using the tool on the Drawing toolbar to place the object on the worksheet.

Now click to the Formula box. Type = and the click on the cell you wish to link.

Also see: Copy - Paste Methodology
by Jon Peltier

The process is a little different in 2007. Either add the Camera tool to the Quick access toolbar, or just select the range and drop down the Paste options.

Choose copy as Picture.

Next pick a location and choose Paste as picture.




[Edited entry from 12/26/2004]




See all Topics

Labels: Excel

More dimensions than Star trek

When a company accumulates a great deal of information, it becomes un-wieldy to work with just basic Excel or Access databases.

There is a database concept called on OLAP cube (On-Line Analytical Processing).

This multidimensional collection of data can be thought of as a 3-D pivot table viewed from flat land.

MSDN:
Just What Are Cubes Anyway?
(A Painless Introduction to OLAP Technology)

OLAPReort.com:
What is OLAP


Wikipedia:
OLAP

Wang.se (Wang Sweden) a Swedish software company:

Create an OLAP Cube

[Edited entry from 12/9/2004]


See all Topics

Labels: Access, Excel

Go back

"When you use Windows Explorer or the desktop to create a new 2007 Microsoft Office file, a new Office file is created in an XML file format (.dox or .xlsx). For example, this behavior occurs when you right-click the desktop, you point to New, and then you click Microsoft Office Word Document. By default, files that you create in the 2007 Office system are in XML file formats.

This article is about how to create legacy Office files, such as .doc files, .xls files, .ppt files, or .mdb files in the 2007 Office system. You can create legacy Office files without opening any Office applications. To do this, you must modify some settings. The modified settings will apply to all the users who log on to the computer."

Labels: Access, Excel, PowerPoint, Word

An easier read

Use named arguments for cleaner VBA code.

Most likely, you use positional arguments when working with VBA functions. For instance, to create a message box, you probably use a statement that adheres to the following syntax:

 MsgBox(prompt[, buttons] [, title] [, helpfile, context])

MsgBox "Hello World!", , "My Message Box"

MsgBox Title:="My Message Box", _
       Prompt:="Hello World!"

Labels: Access, Excel, PowerPoint, Word

Zero is nothing

• Display or hide all zero values on a worksheet
  
  
• Use a number format to hide zero values in selected cells
  
  
• Use a conditional format to hide zero values returned by a formula
  
  
• Use a formula to display zeros as a blanks or dashes
  
  
• Hide zero values in a PivotTable report

Labels: Excel

Different can be good

• Why changes were made in Microsoft Office Excel 2007
  
  
• What happened to the File menu?
  
  
• Different screen resolutions can change what you see
  
  
• Put commands on your own toolbar
  
  
• Using the new keyboard shortcuts
  
  
• How to work with people who don't have Excel 2007

• Demo: Up to speed with Excel 2007
  
  
• Interactive: Excel 2003 to Excel 2007 command reference guide
  
  
• Reference: Locations of Excel 2003 commands in Excel 2007
  
  
• Keyboard shortcuts in the 2007 Office system

Labels: Excel

Split the sheets (?)

Recently a large group of friends and I went on a multi-city tour of Europe that lasted a couple weeks. There was a lot of planning that went into this trip and responsibilities for booking different legs of the trip were spread out across the group. How do you efficiently handle paying back multiple people while getting reimbursed for your costs at the same time?

Split Costs

You can download the spreadsheet here:Split_Costs.zip



See all Topics

Labels: Excel

Formatting bloat

For more information, check out:

How to reset the last cell in Excel 2007

Microsoft Knowledge Base article 211478

Also see:

Spreadsheet Diet

Beyond Technology:
Identifying the Real Last Cell

Microsoft:
Formatting Cleaner Add-in

[Edited entry from 10/30/2004]




See all Topics

Labels: Excel

Game with Excel

• Excel fun
  
  
• 3D Viewer
  
  
• Battleship
  
  
• Blackjack
  
  
• Breakout
  
  
• Golf Stats
  
  
• Mastermind
  
  
• Maze
  
  
• Minesweeper
  
  
• Reversi
  
  
• Rubix
  
  
• Slots
  
  
• Sudoku Solver
  
  
• Tetris
  
  
• Video Poker

Labels: Excel

The drunken cousin

=DATEDIF(EarliestDate,LatestDate,Interval)

=DATEDIF(A2,A1,"d")

Here's THE source for date math:
Chip Pearson:
All About Dates

Also:

John Walenbach:
Extended Date Functions Add-In

"Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. The Extended Date Functions add-in (XDate) corrects this deficiency, and allows you to work with dates in the years 0100 through 9999."

Labels: Excel

Context menu redo

For some reason (to be determined later) my shortcut menu started to accumulate a string of "Edit Formula" and "Delete Formula" entries.

They didn't cause a problem, but were distracting.

This little VBA code puts it back to the original settings until I can find a cause.

Sub ResetShortcutMenu()
    Application.CommandBars("Cell").Reset
End Sub

Labels: Excel

Video + Free code

"Learn tips and use sample code for several Office applications. These tips can help you to be more productive and can also be a starting point for developing your own tools, utilities and techniques."

• Update Word Document Statistics in the Title Bar
  
• Create Outlook Rules Programmatically
  
• Delete Repeated Text Throughout a Word Document
  
• Run Macros Based on the Value of One or More Excel Spreadsheet Cells
  
• Disable Related Controls on a PowerPoint Slide After a User Clicks an Input Control
  
• Display Reminder Information When a User Opens an Office Document
  
• Synchronize an Access Main Form to a Subform and Vice Versa
  
• Log Worksheet Changes to an XML File
  
• Merge Body Text from Multiple Outlook E-mail Messages to a Word Document
  
• Use the Office Assistant as an Alternative to Displaying and Retrieving User Input

Labels: Access, Excel, Outlook, PowerPoint, Word

HEADING

Excel is a great tool to use when you need to take data in one format, manipulate it into another format, and push the results along to another process, e.g. a database. In this context, Excel is a great landing pad or middle man, serving as a data transformation tool to move data from one system to another.

This example considers a sample data set and walks through the steps to clean up the data and perform various transformations on the data set to massage it into a more desirable format.

• Import the data and don't accidentally drop the leading zeros!
  
  
• Formatting SSN
  
  
• Inserting hyphens
  
  
• Fixing up names
  
  
• Lower case E-Mail Address
  
  
• Format Home Phone
  
  
• Inserting parenthesis & hyphens
  
  
• Trim extra spaces from Address
  
  
• Add City values to the new table
  
  
• Make all State value upper case
  
  
• Fill in Postal Code
  
  
• Finalize the values

Labels: Excel

Design suggestions

European Spreadsheet Risks Interest Group:
Spreadsheet mistakes - news stories

John F. Raffensperger:
(oldies but . . .)
Spreadsheet Style

Microsoft Assistance:
Worksheet design strategy

Raymond R. Panko, PhD University of Hawaii
What We Know About Spreadsheet Errors

[Edited entry from 10/18/2004]




See all Topics

Labels: Excel

Numbers from the ether

"The Excel 2007 Web Data Add-In makes it easy to use a Web page as a data source in Excel.

The add-in plugs into Excel 2007 seamlessly, its entry point located on the Data Tab under the From Web option. The system extracts data by learning from a user’s selection of data they wish to capture into Excel. The more selections, the more the system is trained."

Labels: Excel

Don't hide

• Stock quote from MSN MoneyCentral
  
  
• Company report from MSN MoneyCentral
  
  
• Recent news on MSN MoneyCentral
  
  
• Insert a refreshable stock price
  
  
• Remove the Smart Tag
  
  
• Smart Tag options (This brings up the Smart Tags tab on the AutoCorrect menu.)

Complete tasks quickly with Smart Tags




How to turn on smart tags.

Smart tag functionality is turned off by default. Before you can use smart tag functionality, you must turn on smart tag recognition. To do this, follow these steps, as appropriate for the version of Excel that you are running.

Microsoft Office Excel 2007

1. Click the Microsoft Office Button, and then click Excel Options.
   
   
2. Click Proofing.
   
   
3. Click AutoCorrect Options.
   
   
4. In the AutoCorrect dialog box, click the Smart Tags tab.
   
   
5. Click to select the Label data with smart tags check box.
   
   
6. In the Recognizers box, click to select the check boxes next to the specific smart tag recognizers that you want to turn on, and then click OK.
   
   
7. Click OK to close the Excel Options dialog box.

Microsoft Office Excel 2003 and earlier versions of Excel

1. On the Tools menu, click AutoCorrect Options.
   
   
2. In the AutoCorrect dialog box, click the Smart Tags tab.
   
   
3. Click to select the Label data with smart tags check box.
   
   
4. In the Recognizers box, click to select the check boxes next to the specific smart tag recognizers that you want to turn on, and then click OK.
   

Labels: Excel

All(most) all you need to know

• Add numbers
  
• Subtract numbers
  
• Multiply numbers
  
• Divide numbers

Labels: Excel

Apple π

Look at how different media organizations have used graphics/graphs to illustrate the news.
Poynter Online:
The Art of Explanation


While Edward Tufte has concerns about representing data accurately in charts, he does, use pictures to demonstrate relationships.
(See Edward Tufte)

If you would like to try your hand, here are some links that will help to spice up your condiment report.

PC Magazine:
Add Images to Excel Projects

MacWorld:
Excel Chart Art

Andy Pope:
Dividing a graphic into sections

Excel 2007 has a great graphic look for charts, but these links still work

[Edited entry from 10/8/2004]




See all Topics

Labels: Excel

Office Wide

"Automation (formerly known as OLE Automation) is a feature of the Component Object Model (COM), an industry-standard technology that applications use to expose their objects, methods, and properties to development tools, macro languages, and other applications.

For example, a spreadsheet application might expose a worksheet, chart, cell, or range of cells--each as a different type of object. A word processor might expose objects such as an application, document, paragraph, bookmark, or sentence.

When an application supports Automation, the objects that the application exposes can be accessed through Visual Basic. You can use Visual Basic to manipulate the objects by invoking methods or by getting and setting properties of the objects."

Here's an example:

Inserting Data into a Microsoft Word Document

With Automation code, you can open a Microsoft Word document and move to a bookmark location in the document. The following example opens a Microsoft Word document and inserts text after a bookmark.

This example assumes that you have Microsoft Word on your computer, that you have an existing document called C:\My Documents\WordTest.doc, and that the document contains a pre-defined bookmark named City.

Sub FindBMark()

   Dim wordApp As Word.Application
   Dim wordDoc As Word.Document
   Dim wordRange As Word.Range

   Set wordApp = CreateObject("Word.Application")
   Set wordDoc = wordApp.Documents.Open("C:\My Documents\Wordtest.doc")

   wordApp.Visible = True

   ' Go to the bookmark named "City".
   Set wordRange = wordDoc.Goto(What:=wdGoToBookmark, Name:="City")
   wordRange.InsertAfter "Los Angeles"

   ' Print the document.
   wordDoc.PrintOut Background:=False

   ' Save the modified document.
   wordDoc.Save

   ' Quit Word without saving changes to the document.
   wordApp.Quit SaveChanges:=wdDoNotSaveChanges

   Set wordApp = Nothing

End Sub

Microsoft Support provides an entire Help file to assist you. It includes theory and examples.

The file is called XPAutomation.chm.

Download it and then double click on the file to run it. You could also set up a shortcut on the desk top, if it will be used frequently.


Microsoft Knowledge Base Article: 302460

This was aimed at Office 2002 but it can be used with later versions:

• Microsoft Access
  
• Microsoft Excel
  
• Microsoft Outlook
  
• Microsoft PowerPoint
  
• Microsoft Word

Labels: Access, Excel, Outlook, PowerPoint, Word

Basic tutorial

"This practical spreadsheet lesson offers easy answers to life's perplexing math problems like How much will my dream car really cost after financing?

Students will calculate the cost of purchasing their very own Lamborghini sports car and determine if the ultimate price tag is really worth the investment. "

Dream Car

Also:

Loan templates
Basic Financial Calculations



See all Topics

Labels: Excel

Beta Toy

"The Excel 2007 Web Data Add-In makes it easy to use a Web page as a data source in Excel. The add-in plugs into Excel 2007 seamlessly, its entry point located on the Data Tab under the From Web option.

The system extracts data by learning from a user's selection of data they wish to capture into Excel. The more selections, the more the system is trained.
An example scenario: You wish to import and track data from MSN's weather page. Visit the site using the tool, enter Data Capture mode, and select a row or two of data from the table. Then click Select Similar, and the system will find similar data based on your previous selections.

You then can click Import and leverage Excel's rich data-editing capabilities, including the Refresh command, which will revisit the Web page and extract potentially new, updated data."

Labels: Excel

Exchange the future and the past

"Microsoft has added new file formats to Microsoft Office Word, Excel, and PowerPoint 2007. To help ensure that you can exchange documents between Microsoft Office releases, Microsoft has developed a Compatibility Pack for the Office Word, Office Excel, and Office PowerPoint 2007 File Formats"

Use earlier versions of Excel, PowerPoint, and Word to open and save files from 2007 Office programs

Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007



See all Topics

Labels: Excel, PowerPoint, Word

Spokes of the web

• Excel Add-ins
  
  
• Excel Help
  
  
• Excel Password Recovery
  
  
• Excel Templates
  
  
• Excel Tips & Tricks
  
  
• Excel Tutorials
  
  
• Excel VBA
  
  
• Free Excel Add Ins
  
  
• Spreadsheet Research

Labels: Excel

The eleventh place error

The result of the calculation is a number from 65534.99999999995 to 65535. The calculation is performed correctly. However, the result is incorrectly shown as 100000.

The result of the calculation is a number from 65535.99999999995 to 65536. The calculation is performed correctly. However, the result is incorrectly shown as 100001.

Labels: Excel

Up one side; down another

Data tables are a neat Excel feature that has not been emphasized.

If you are looking at, for instance, a home loan with a number of interest rates and different loan periods, a Data table can lay out the results with a minimum of fluff and formulas.

Dick Kusleika, Microsoft MVP, has a description on his excellent Daily Dose of Excel blog.
Data Table Basics

J K Pieterse:
Excel 2007 tables

Microsoft:
How to Use Microsoft Excel Data Tables to Analyze Information in a Database
How to Create and Use One-Input Data Tables in Microsoft Excel
How to Create and Use Two-Input Data Tables in Microsoft Excel

Overview of Data Tables - 2003 and 2007

TechRepublic.com:
Teach two-variable Excel data tables with real-life examples

[Edited entry from 9/30/2004]




See all Topics

Labels: Excel

Geek fame

"You probably have a document you use over and over again, something you created to solve a particular problem. You may even find yourself occasionally sending the document to others in e-mail because it's so useful. Now you can share your clever solution with everyone who uses the 2007 Microsoft Office system!

People like you all over the world are allowing others to download and use their document templates on Office Online. Some of these templates have tens of thousands of downloads. They were submitted by people who either wanted to help others or show their great solutions. You can, too.

Labels: Access, Addendum, Excel, PowerPoint, Word

Roll your own OA

The Office Assistant has taken a lot of hits, but it is missing in 2007, but still around in earlier versions.

If you want to play with it, see John Walkenbach's Create A Fake Clippy



Here is a creature you could use to replace Clippy:
Nerd Bird

There are other articles in the MS library about Agents and Assistants:
Animating Office Applications with Microsoft Agent

And:
Using the Office Assistant to Display Help

Programming the Office Assistant

The VBA help file give a list of motions for the assistants. Look for Assistants>Animation.
The code is simple:

With Assistant
.On = True
.Visible = True
.Move xLeft:= 400, yTop:= 300
.MoveWhenInTheWay = True
.TipOfDay = True
.Animation = msoAnimationGreeting
End With

Here are a few of the actions:

• msoAnimationBeginSpeaking
  
• msoAnimationCheckingSomething
  
• msoAnimationGetArtsy
  
• msoAnimationGetTechy
  
• msoAnimationGetWizardy
  
• msoAnimationRestPose
  
• msoAnimationSendingMail
  
• msoAnimationThinking
  
• msoAnimationWorkingAtSomething
  
• msoAnimationWritingNotingSomething
  
• msoAnimationCheckingSomething
  
• msoAnimationGetTechy
  
• msoAnimationListensToComputer

Labels: Excel