Stick it in Word

Arrange the windows so that both applications can be seen.
(Right-click an empty area of the Task bar and choose "Tile Windows Vertically."

Type your introductory text in the Word document.

Switch to PowerPoint and open the PowerPoint file.

In Slide Sorter View, hold down the Ctrl key and select the slides you want to include.

Drag the selected group of slides onto the Word document.

You will only see the first slide in the document, but if you double-click on the image, the PowerPoint show will run.

It will also work in Excel.

(This, of course assumes that the target machine has PowerPoint or PowerPoint Viewer installed)



See all Topics
excel

Labels: General, Link, Tips

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



See all Topics
excel

Labels: Customize, Formats, Tips, Tutorials

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




See all Topics
excel

Labels: Formulas, Functions, Tips, Tutorials

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.



See all Topics
excel

Labels: Formats, Functions, Tips

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.






See all Topics
excel

Labels: General, Link, Tips

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: Formulas, Functions, Tips

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
excel

Labels: General, Templates, Tips, Tutorials

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



See all Topics
excel

Labels: Formats, Tips, Troubleshooting

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: Formulas, Functions, Tips

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: Tips, VBA

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: Tips, Tutorials

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




See all Topics
excel

Labels: General, Tips

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: General, Shortcuts, Tips

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



See all Topics
excel

Labels: Lists, Tips

Excel reading

Labels: General, Tips

Level talk

1. Close any programs that are running.
   
   
2. Click Start, click Run, type regedit in the Open box, and then click OK.
   
   
3. In Registry Editor, expand one of the following registry subkeys, as appropriate for the version of Excel that you are running:
   
   Microsoft Office Excel 2007
   

   HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options

   
   Microsoft Office Excel 2003 uses Office\11.0\
   Microsoft Excel 2002 uses Office\10.0\
   Microsoft Excel 2000 uses Office\9.0\
   
   
4. On the Edit menu, point to New, and then click DWORD Value. Select New Value #1, type UndoHistory, and then press ENTER.
   
   
5. On the Edit menu, click Modify.
   
   
6. In the Edit DWORD Value dialog box, click Decimal under Base. Type a value between 0 and 100 in the Value box, click OK, and then exit Registry Editor.
   
   
7. Start Excel. Excel stores an undo history for the number of actions that you specified in step 6.

Sub ClearUndo()
    Range("A1").Copy Range("A1")
End Sub

Labels: Customize, General, Macros, Tips

Eavesdrop on the experts

Labels: General, Tips

2007 is lacking

"Yes, unfortunately, the Insert from Scanner and Camera feature was removed in Office 2007.
This was a difficult call, but I think for the best of the product overall. The feature supported a limited number of scanners and the camera support had not been updated for some time.
We came to the conclusion that most users would be better off using the software that came with their camera, scanner or even built into Windows, to get their images from their device, to the file system, and then use the Insert Picture command to get them into Office.
The downside, is that for some users, this feature did work and so will be missed."

"The From Scanner or Camera option for adding pictures to a presentation, photo album, or workbook is not available in Microsoft Office PowerPoint 2007 or Microsoft Office Excel 2007.

Instead, you can add pictures from your camera or scanner by downloading the pictures to your computer first, and then copying them from your computer into PowerPoint or Excel."

Support.Microsoft.com
How to insert scanned images in Office 2007

You could also use the Microsoft Document Imaging/Scanning application.
About Microsoft Office Document Imaging

BTW, when the article calls for clicking on the Scan button, it's actually the picture of a scanner.





See all Topics
excel

Labels: Addins, Tips

No cut, No Paste

1. Select the entire column or row you want to move
   The move will effect whatever is selected; cells, or columns, or rows
   
   
2. Move your mouse pointer to the edge of the selection until it changes from a normal pointer cross to an arrow (four headed arrow in 2007).
   (Put the pointer on the selected cells, not the Row or Column indicators.)
   
   
3. Hold down the shift key and click the left mouse button and drag the column/row to the new position.
   
   
4. Release the mouse button before releasing the shift key

Labels: Shortcuts, Tips

No add-ins needed

• Raw text, HTML, XHTML, Microsoft Word, RTF, PDF, PS, Open Office, Star Writer, Pocket Word, Word Perfect
  
  
• CSV, dBase, Microsoft Excel, Pocket Excel, Lotus 123, Quattro Pro, Star Calc, Open Office spreadsheet
  
  
• MathML, Star Math, Open Office math
  
  
• Microsoft Powerpoint, Star Impress, Open Office presentation

Convert files into universal formats like Adobe PDF, PS (PostScript) or CSV to print, fax or simply read them on any computer, without special software.

Learn morse code with the text to morse converter ;-)

Labels: General, Tips

Functions and Macros

• Remove internal worksheet/workbook protection
  
• Why your sum is a penny off...
  
• Highlight row without losing color formatting
  
• Why use -- in SUMPRODUCT formulae
  
• Using SUMPRODUCT() to calculate variable rates and commissions
  
• Three-dimensional SUMIF()s
  
• Bitwise Logical Functions
  
• Sampling from a range
  
• Summing every Nth column or row
  
• Worksheet and Workbook names using the CELL() function

Labels: Formulas, Tips

Point to the spot

Labels: Customize, Formats, Tips

HEADING

From Microsoft

"After supporting Microsoft Excel for years, technical Support Professionals have found that some of the most powerful and useful features and functions in Microsoft Excel remain undiscovered by you, our users.

For example, you may create a new macro to perform a calculation when an existing formula or function can perform the task. Or, you may create a new macro to perform a task when you could use an existing feature that performs the task."

• Secret #1: Joining Text Together
  
  
• Secret #3: Excluding Duplicate Items in a List
  
  
• Secret #7: Using a Data Form
  
  
• Secret #11: Linking a Text Box to Data in a Cell
  
  
• Secret #12: Linking a Picture to a Cell Range
  
  
• Secret #17: Using OFFSET to Manipulate Data in Cells that are Inserted
  
  
• Secret #21: Using INDEX and MATCH to Look up Data
  
  
• Secret #25: Returning Every Nth Number
  
  
• Secret #29: Using One Keystroke to Create a new Chart or Worksheet
  
  
• Secret #30: Setting up Multiple Print Areas on the Same Worksheet

Labels: Shortcuts, Tips

Format don't show

1. Select the range
   
   
2. Goto Format>Conditional Formatting
   
   
3. Select Formula Is
   
   
4. Enter
   =A2=A1
   
   
5. Click the Format button.
   
   
6. Select a font color to match the cell background color.
   
   
7. Click OK and OK

1. Select the range
   
   
2. Go to Format>Conditional Formatting
   
   
3. Choose Formula Is
   
   
4. Enter
   =COUNTIF($A$2:$A$100,A2)>1
   
   
5. Click the Format button.
   
   
6. Select a font or background color for highlighting.
   
   
7. Click OK and OK

Labels: Formats, Tips

Where'd they hide that thing?

Wondering where your favorite Word 2003 commands are located in the new Word 2007 interface? Or just want to explore the rich, new design with a little guidance?

... rest the mouse pointer over a Word 2003 menu or button to learn its new location in Word 2007. To see an animation of the location of the command or button in Word 2007, just click it.

Command reference guides for:

Word
Excel
PowerPoint

The versions above require an internet connection.
Below are the links to download the guides to your machine.

PowerPoint
Excel
Word



See all Topics
excel

Labels: 2007, Addins, Shortcuts, Tips

Ribbon add-in tutorial portal

" This add-in adds a Get Started tab to the Excel 2007 Ribbon. Commands on this tab give you easy access to free content on Office Online, such as training courses, video demos, and other Office Online content designed to help you learn Excel 2007 quickly.

An Office Online interactive command mapping tool, also available on the Get Started tab, shows you where to find Excel 2003 buttons and commands in Excel 2007. Office Online Community discussions are also available directly from Excel with this add-in."

Labels: 2007, Addins, Shortcuts, Tips

Info source

"What is the Excel Wiki?

That is really up to the community! Right now, the message board is a pull technology. Someone asks a question and then someone answers it. My idea... allow the community to write articles on their favorite tips about Excel. In theory, we would eventually have an article about every Excel function and every Excel command.

Currently, there are sections for Commands, Functions, VBA, and Tips. If you think there should be a new section, feel free to edit the ExcelWiki and add a link to a new section."

Labels: Functions, Tips

One of the good ones

Labels: Tips