Enter your email address:

Delivered by FeedBurner

Home Page








Subscribe here
Add to 

My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!

eXTReMe Tracker

  Thursday, February 04, 2010 – Permalink –

Command Reference


Those of you that are just now making the switch to the Ribbon world, will find this valuable.

See all Topics

Labels: ,

<Doug Klippert@ 3:14 AM

Comments: Post a Comment

  Monday, February 01, 2010 – Permalink –

Tips for Word and Excel

Also some Windows hints

This site has useful information about:

  • Word
  • Word VBA
  • Excel
  • Excel VBA

  • Windows

See all Topics

Labels: , ,

<Doug Klippert@ 3:32 AM

Comments: Post a Comment

  Wednesday, January 27, 2010 – Permalink –

Color Scales

Conditional colors

Here is some information on Conditional color scales in Office 2010.

Microsoft Excel 2010 Blog 1

Microsoft Excel 2010 Blog 2

Microsoft Excel 2010 Blog 3

See all Topics

Labels: , , ,

<Doug Klippert@ 3:42 AM

Comments: Post a Comment

  Tuesday, January 26, 2010 – Permalink –

Where's the Template

Find and/change storage spots

Describes the different template categories and the locations of templates in 2007 Office programs. Also describes the registry settings that control where to find your custom templates.

See all Topics

Labels: , ,

<Doug Klippert@ 3:48 AM

Comments: Post a Comment

  Saturday, January 02, 2010 – Permalink –

New Calendars

Another year

Here are free Excel calendars for downloading.

Twelve months on one sheet or one month per tab.

See all Topics

Labels: ,

<Doug Klippert@ 3:24 AM

Comments: Post a Comment

  Monday, December 14, 2009 – Permalink –

Have a Geeky Christmas

And a Functional New Year

It is rather late in the season, but this is a gift that gives all year long:
This hilarious clock is the perfect accessory for any Excel power user. Each numeral has been replaced with a suitable Excel function that will evaluate to that numeral.

We've used one function from as many function categories as possible to really provide a broad range of functions.

For instance:
=FACT(3) - The FACT() function returns the Factorial of a number. The Factorial of 10 is 10x9x8x7x6x5x4x3x2x1. This function is great for statisticians calculating combinations and permutations. In our case, the Factorial of 3 is 3x2x1 or 6

Excel Function Clock

See all Topics

Labels: ,

<Doug Klippert@ 3:20 AM

Comments: Post a Comment

  Friday, December 04, 2009 – Permalink –

Password Background


Alan Myrvold has written a background article on how Office handles passwords and what password strength means.

"Word, Excel, and PowerPoint have been able to password protect documents for several versions by setting the 'password to open'. What we felt could be improved was the ability to enforce password strength rules, similar to what may be required when logging into your computer at work."

See all Topics

Labels: ,

<Doug Klippert@ 3:55 AM

Comments: Post a Comment

  Thursday, October 22, 2009 – Permalink –

Move Using Alt

One sheet to another

To move data from one worksheet to another, highlight the data.

Hold down the ALT key and move the mouse until the pointer arrow is on the border of the selection.

Drag the selection down to the destination worksheet tab.

When the arrow touches the tab, Excel switches to the desired worksheet.

Now drag the selection to the correct position. Let go of the mouse and then the ALT key.

To copy data hold down the CTRL+ALT keys and perform the steps above.

BTW the screen will not scroll while you hold the ALT key down.

See all Topics

Labels: ,

<Doug Klippert@ 3:56 AM

Comments: Post a Comment

  Thursday, September 17, 2009 – Permalink –

Lock the Barn

Protect your work

John Walkenbach has put together an FAQ on Workbook/Worksheet/VBA protection.
Spreadsheet Protection FAQ

The Microsoft Knowledge Base article KB 293445 Has a list of references to protection information.

Microsoft Excel provides multiple layers of protection to allow you to control who can access and change your data:
  • Worksheet protection: You can protect elements on a worksheet (for example, cells with formulas) from all user access, or you can grant individual users access to the ranges that you specify.
  • Workbook-level protection: You can apply protection to workbook elements, and you can protect a workbook file from being viewed and edited. If a workbook is shared, you can protect it from being returned to exclusive use and prevent the change history from being deleted.
... articles address some of the more frequently asked questions about workbook and worksheet protection in Excel:
  • How can I grant only a few users access to a range in my worksheet?
  • Why are users not allowed to edit the ranges that I established permissions for?
  • What new features are available in workbook protection?
  • Why don't the permissions that I set on ranges in my worksheets carry over to Windows 98 computers?
Here is more information
Overview of security and protection in Excel

See all Topics

Labels: , ,

<Doug Klippert@ 3:44 AM

You might want to check out Mike Alexander's blog post about how easy it is to remove worksheet protection in Excel 2007.

Post a Comment

  Friday, August 14, 2009 – Permalink –

Digital Signatures

How do I know it's real?

If you find a need to provide some sort of certification that your document has not been tampered with and is the rel thing, you might consider a digital signature.

This Microsoft Support article discusses the process.
What is a digital certificate?

What is a digital signature?

What occurs when I use a digital signature?

What Word files can I sign?

How can I obtain a digital signature?
  • Method 1: Obtain a digital certificate from a certification authority
  • Method 2: Create your own digital certificate
    Description of digital signatures and code

    Here's information for Excel.

    Digital Signatures for Excel

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:58 AM

    Comments: Post a Comment

      Thursday, August 13, 2009 – Permalink –

    Scroll Restrictions

    Without Protection

    You can protect a spreadsheet so that data is shielded from inadvertent entries.
    There is another way to set up a scroll area that does not involve protection.
    Open the Control Toolbox (right-click any existing tool bar) and click on the Properties icon.

    In the Scroll Area text box, type the scroll area range, or type the defined Name for the range.
    To cancel the Scroll Area restricted range, clear the Scroll Area text box.

    If you are using Office 2007, the Property icon is on the Developers tab.

    Multiple areas can be selected using Protection, but only one area is allowed using the Scroll Property.

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:29 AM

    Comments: Post a Comment

      Sunday, July 12, 2009 – Permalink –

    Plain Numbers

    I'd Like to Make It Clear

    Plain Figures is a method of transforming statistical and financial data into figures, tables and graphs that people readily understand.

    Have you ever:
    • squinted your eyes trying to see the numbers in a PowerPoint presentation?

    • scratched your head at a charity leaflet with an indecipherable pie chart titled 'Where your donation goes' ... and set it aside?

    • missed discussion at a meeting because you were busy trying to figure out the figures?

    • put aside a graph or table, thinking "I'm not good with numbers."?

    Then you know how important the clear display of numerical information can be. Common problems People have trouble using numerical information for many reasons. Most commonly, authors don't know:
    • what to include: when unsure what numbers are important, people frequently display them all, overpowering the reader with irrelevance.

    • which format to use: the choice between text and table, table and chart, bar and pie.

    • how to use the technology effectively: computer software generates graphs easily, but the results hide your point behind incomprehensible chartjunk.

    • how to explain the information: selecting the right words for titles, columns and captions.

    Plain Figures is a partnership between Sally Bigwood, located in Wakefield, Yorkshire, UK, and Melissa Spore, who divides her time between Toronto and Saskatoon, Canada. Sally and Melissa are sisters and both have dual citizenship in the United States. See all Topics

    Labels: , ,

    <Doug Klippert@ 3:02 AM

    Comments: Post a Comment

      Friday, April 03, 2009 – Permalink –

    Where Have All the Bytes Gone?

    Folder size list

    You can create a list in Excel of all the folders on a drive and their sizes.
    (The credit goes to Peter Beach, an Excel MVP.)

    Get Folder Size code

    1. Copy the code and open Excel.
    2. Press Alt+F11 and, if necessary, on the Visual Basic Editor menu, Insert>Module
    3. Paste the code.
    4. You could use Alt+Tab to bring the worksheet forward.
    5. Go to Tools>Macros and run the Macro named "GetFolderListing".

    It may take a little time to complete. BTW, if you feel geeky enough, here is a picture of some of the year 2005 MVPs from John Walkenbach's site. See all Topics

    Labels: , ,

    <Doug Klippert@ 3:56 AM

    Comments: Post a Comment

      Thursday, January 22, 2009 – Permalink –


    CrossNumber puzzles

    I have to admit that I have not caught the fever that these puzzles seem to have generated.

    Most every newspaper in the world has started publishing these brain teasers.

    "Sudoku , sometimes spelled Su Doku, is a logic based placement puzzle, also known as Number Place in the United States. The aim of the canonical puzzle is to enter a numerical digit from 1 through 9 in each cell of a 9x9 grid made up of 3x3 subgrids (called "regions"), starting with various digits given in some cells (the "givens"). Each row, column, and region must contain only one instance of each numeral.
    Completing the puzzle requires patience and logical ability. Its grid layout is reminiscent of other newspaper puzzles like crosswords and chess problems.
    Although first published in 1979, Sudoku initially became popular in Japan in 1986 and attained international popularity in 2005."

    Wikipedia — Sudoku

    Here is a download that will construct as many of these puzzles as you might be Jonesing for.

    Andy Pope Su Doku



    Web Su Doku

    Here's an Excel template from the Redmond people:
    1. Enter puzzle values in Starting position grid
    2. Set Game state to 1
    3. Press F9 to calculate
    4. The Possible numbers grid will show the first step in the solution
    5. The Final position will show the current result
    6. Continue to repeat calculation by pressing F9 until the puzzle is solved or the solver stops responding
    Sudoku solver

    See all Topics


    <Doug Klippert@ 3:37 AM

    Comments: Post a Comment

      Friday, January 09, 2009 – Permalink –

    Graph Data and Shapes

    Statistical collection

    Betty C Jung has put together a serious collection of tutorials, links, and data sources.

    Here are some of the topics:
    • Charting Data
    • Data Presentation
    • Cumulative Frequencies
    • Fishbone Diagram
    • Flowcharts
    • Frequency Distributions
    • Gantt; PERT Charts
    • Graphing With Excel
    • Analyzing and Plotting Data with Excel
    • Organizational Charts/Mapping
    • Social Network Analysis
    Graphing & Presenting Data

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:51 AM

    Comments: Post a Comment

      Monday, January 05, 2009 – Permalink –

    Excel 2007 Limits

    New Boundaries

    Here are a few of the major changes in Excel 2007.
    The total number of available columns in Excel

    Old Limit: 256 (2^8)
    New Limit: 16k (2^14)
    The total number of available rows in Excel

    Old Limit: 64k (2^16)
    New Limit: 1M (2^20)
    Number of unique colors allowed a single workbook

    Old Limit: 56 (indexed color)
    New Limit: 4.3 billion (32-bit color)
    Number of conditional format conditions on a cell

    Old Limit: 3 conditions
    New Limit: Limited by available memory
    Number of levels of sorting on a range or table

    Old Limit: 3
    New Limit: 64
    Number of items shown in the Auto-Filter dropdown

    Old Limit: 1,000
    New Limit: 10,000
    The total number of characters that can display in a cell

    Old Limit: 1k (when the text is formatted)
    New Limit: 32k or as many as will fit in the cell (regardless of formatting)
    The maximum length of formulas (in characters)

    Old Limit: 1k characters
    New Limit: 8k characters
    The number of levels of nesting that Excel allows in formulas

    Old Limit: 7
    New Limit: 64
    Maximum number of arguments to a function

    Old Limit: 30
    New Limit: 255
    The number of characters that can be stored and displayed in a cell formatted as Text

    Old Limit: 255
    New Limit: 32k

    These were reported by David Gainer.

    Some other numbers

    More information is available at David's blog:

    A discussion of what's new in Excel

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:41 AM

    Comments: Post a Comment

      Saturday, December 27, 2008 – Permalink –

    Mac vs. PC

    Spreadsheet vagaries

    The 1904 date system and the 1900 date system

    "The default date system in Excel 2004 for Mac is the 1904 date system. The default date system in Excel 2003 is the 1900 date system. Typically, the use of different date systems does not cause a problem.

    However, if you transfer a workbook from Excel to Excel for Mac, or vice versa, and then copy a date from one workbook to the other, the date may increase or decrease by four years and one day. This issue occurs if the two workbooks use different date systems.

    For example, if you copy the date 1/1/1998 from a workbook that uses the 1900 date system and then paste the date into a workbook that uses the 1904 date system, the date appears as 1/2/2002. Alternatively, if you copy the date 1/1/1998 from a workbook that uses the 1904 date system and then paste the date into a workbook that uses the 1900 date system, the date appears as 12/31/1993.

    As long as you know about the date systems that your workbooks use, the different date systems should not cause a problem.


    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:53 AM

    Comments: Post a Comment

      Friday, November 14, 2008 – Permalink –

    Sort Worksheets

    Order tabs

    Worksheets can be dragged and dropped into any order required. They can be set up in numeric or alpha order, but doing it by hand is a bother.

    Chip Pearson has written some macros that will do the job for you:

    • Sorting Worksheets In Alphabetical Order
    • Sorting In Custom Order
    • Grouping Sheets By Color

    Here's the code to sort by tab color:

    Sub GroupSheetsByColor()
    Dim Ndx As Long
    Dim Ndx2 As Long
    For Ndx = 1 To Worksheets.Count - 1
    For Ndx2 = Ndx To Worksheets.Count
    If Worksheets(Ndx2).Tab.ColorIndex = _
    Worksheets(Ndx).Tab.ColorIndex Then
    Worksheets(Ndx2).Move after:=Worksheets(Ndx)
    End If
    Next Ndx2
    Next Ndx
    End Sub

    Sorting Worksheets In A Workbook

    (The colorindex variable chooses one of the 56 colors in Excel's basic palette.
    Here are all the colors and numbers as compiled by F. David McRitchie:
    Excel Colors )

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:37 AM

    Comments: Post a Comment

      Wednesday, November 05, 2008 – Permalink –

    Lotus 1-2-3?


    You could tell when Lotus was being taught by the instructor's chants of the multiple keystrokes. "Slash, F, Down arrow, Return, Slash, . . ."

    "Another feature that illustrates 1-2-3's human-engineering design is its jargon-free prompts, explanatory messages, and fail-safe mechanisms.

    Suppose I want to delete a spreadsheet file. When I type "/F", I get a menu of possible file actions, the first of which is in inverse video. I can execute any action by either moving the inverse video cursor to that action name and hitting the Return key or hitting the key that is the first letter of the action name.

    If I hit the right-arrow key until the action name "Delete" is in inverse video, I get an explanatory note immediately below the command line that says, "Delete a worksheet, print, or graph file" ("worksheet" is Lotus's name for a spreadsheet).

    That's what I want to do, so I conveniently hit Return. The inverse video cursor is already positioned on the option I want, "Worksheet", so I hit Return again. 1-2-3 now reads the current disk and gives me a menu of all the spreadsheet files by name.
    I then move the inverse video cursor to the spreadsheet I want to delete and hit Return again (there's no need to type the file name - just point to it). 1-2-3 displays two options, "No" and "Yes", with the cursor on the "No" option and an explanatory note, "Do not delete the file". By moving the cursor onto the word "Yes" (which includes the note "Delete the file") and pressing Return, I can delete the spreadsheet file.

    Wouldn't you like a piece of software that does all that for you?"

    First Lotus 1-2-3 Review

    Lotus was another application that missed the Windows train.

    Here is a video discussing the spreadsheets about twenty years ago:
    "Excel now dominates the spreadsheet world, but once upon a time there was actual competition among spreadsheet products. This program looks at Quattro 1.0, Allways 1.0, Lotus 1-2-3 3.0, Ashton-Tate's Full Impact, and Excel 2.1. Guests include Gary Kildall, Jan Lewis, and Jared Taylor of PC Magazine. Originally broadcast in 1988."

    Spreadsheet Wars

    One of the problems expressed in this program, was that you needed to run Excel on a 286 or 386 machine.

    "The rise of Microsoft Windows in the personal computer market was accompanied by the rise in Microsoft's competing spreadsheet, Excel, and it gradually usurped the position of 1-2-3. Being loyal to OS/2, Lotus was slow to embrace Windows. Additionally, several versions of 1-2-3 were available concurrently, each with different set of functionality and slightly different interface."

    Lotus 1-2-3

    Here is the original PC spreadsheet program. You can download it, play with it, and then run back to Excel.


    VisiCalc: Information from its creators, Dan Bricklin and Bob Frankston

    See all Topics


    <Doug Klippert@ 3:44 AM

    Comments: Post a Comment

      Saturday, November 01, 2008 – Permalink –

    What if?

    Scenario suggestions

    "I wonder how our net profit would be affected if we could reduce our variable cost per unit by just a few cents. How much could we save if we found a lower interest rate? Wouldn't it be nice to be able to play around with some scenarios, do some "what-ifs" — without messing up your current data? It's easy with Microsoft Office Excel . You can set up "scenarios" to experiment with the data and compare the possibilities. Who knows? It could be a road map to better solutions for your business."

    Excel "what-if" scenarios

    American Institute of Certified Public Accountants (
    " To find out how to use what-if functions, follow along as this tutorial takes you step-by-step through several problems. Excel 2000 is used here to illustrate these concepts, but the process is similar in all spreadsheet programs."

    Using a spreadsheet to do "what-if" analyses

    See all Topics

    Labels: , ,

    <Doug Klippert@ 2:29 AM

    Comments: Post a Comment

      Tuesday, October 28, 2008 – Permalink –

    Normalization Model

    Excel Mock-up

    Normalization, simplistically, is setting up tables of data so that information is entered only one time. Access is not as malleable as Excel, so for demonstration purposes, spreadsheet entries can be used to show the concept.

    "This article explains how to use Microsoft Excel to create prototypes of your Access databases and how to make sure your data adheres to a set of rules called normal forms. Normal forms aren't hard to understand, and they're critical to designing useful databases."

    Design Access databases with normal forms and Excel

    Also see:

    Database normalization Webcast

    An Introduction to Database Normalization

    Database normalization basics

    Database Normalization Basics for Developers

    Also see:
    Building and using a relational database in Excel

    See all Topics

    Labels: ,

    <Doug Klippert@ 6:21 AM

    Comments: Post a Comment

      Wednesday, October 15, 2008 – Permalink –

    Download Demonstrations

    Still good after all these years

    Back in the day, Barnes Consulting was a major player with Office 97. They've gone on to other consulting areas, but you can still study what they called "On-Line Experiences".

    These detail the benefits and outline the steps of many Excel features. They are worth the download.

    They are a great resource for learning advanced features, training, and tips on a great consistent presentation that you can use to format your workbooks.

    While they were written for Microsoft Excel versions 5.0 and 7.0, they are still viable in Excel 200x.

    These Experiences were developed for Microsoft Corporation by Baarns Publishing.

    Here are the titles of the illustrated workbooks that are free to download:

    • Subtotals Experience
    • Group and Outline Experience
    • Range Name Experience
    • Functions Experience
    • PivotTable Experience
    • Auto Filter Experience
    • Auto Format Experience
    • Power Chart Experience
    • Scenario Experience
    • Solver Experience

    Downloadable Learning Experiences

    See all Topics

    Labels: , , ,

    <Doug Klippert@ 3:21 AM

    Comments: Post a Comment

      Saturday, October 11, 2008 – Permalink –

    Stock Answer

    Built in service

    You can easily insert an automatically updated stock quote for a specific company in a spreadsheet.

    Here are the instructions:

    1. On the Tools menu, click AutoCorrect options.
      (Use the Office button and Excel Options in 2007)
    2. Click the Smart Tags tab and place a check beside Label data with smart tags.
    3. Click OK.
    4. Type in a Stock symbol (such as MSFT) in a cell.
      (The symbol must be in all caps)
    5. Click outside of the cell.
    6. Place your mouse cursor over the purple triangle in the lower-right corner of the cell.
      Click the arrow.
    7. Click Insert refreshable stock price.
    8. Select whether to insert the stock price on a new worksheet or in a specific area on your current worksheet.

    You can refresh a stock quote price at any time by right clicking anywhere within your worksheet and selecting Data Range Properties. Enter a value for how often the stock quote price should be refreshed.

    See all Topics

    Labels: ,

    <Doug Klippert@ 4:20 AM

    Comments: Post a Comment

      Tuesday, August 12, 2008 – Permalink –

    Stop Online Help

    Use local Help

    When Office 2003 first came out, one of the new features was that the help files were "live."

    Rather than using stale information installed years before, the application connected with Redmond for the newest and best solutions.

    This can be a problem depending on how you connect to the Internet. If you're using a dial up service, or speeds slow to a crawl. Here is a way to use local information.

    1. Bring up the Help Task Pane (The F1 key will do this.)

    2. At the bottom of the "See also" box there is a hyperlink: "Office Online Settings"

    3. Click this link; you will get the Service Options dialog box

    4. Uncheck the option: "Search online content when connected"

    Office will now use the help files on the local hard drive. It is much faster!
    (Editing will affect all Office applications)

    In office 2007, left click on the "Connected to Office Online" and choose local

    If you need to disable its use through a Group Policy, or in the Registry, see:

    Microsoft Support:
    How to disable Microsoft Office Online featured links in Office

    Be aware that if you do turn it off, you might miss some of the Office online feature, like tutorials and downloads.

    Office Online: Get More Out of the Microsoft Office System

    See all Topics


    <Doug Klippert@ 4:03 AM

    Comments: Post a Comment

      Saturday, June 07, 2008 – Permalink –

    Good OLAP

    More data


    An introduction to the benefits of online analytical processing (OLAP)

    "Every day we create reams of data in customer relationship management applications, order entry applications, and warehouse management systems. We're drowning in a sea of data. However, even with all that data we don't have a large amount of information. We have the ones and zeros of the transactions, but we don't have the answers we need to simple questions like:

    • "Why was March better than February?"
    • "Where is the sales force having the most success?"
    • "In what conditions does the sales team struggle with making sales?"

    The article also has links to:

    • Oracle 9i makes data warehousing easy to implement
    • Seven highly effective steps to a smooth data warehouse implementation
    • Business intelligence is just a few steps away for SAP R/3 users
    • Resources for designing, planning, and implementing a data warehouse strategy
    • Making the operational case for data warehousing
    • TechRepublic Tutorial: Data warehousing defined

    Also see:
    OLAP Cube

    Data Warehousing and OLAP
    A Research-Oriented Bibliography

    FAQ Excel 2007 – OLAP



    See all Topics

    Labels: , ,

    <Doug Klippert@ 6:41 AM

    Comments: Post a Comment

      Thursday, May 29, 2008 – Permalink –

    Fill Handle

    Double click the handle

    If you have a column of data, you may wish to insert a new formula on each row, number the lines, or add a date column.

    To fill the column down to the bottom of the database, just double-click on the fill handle - the tiny square at the bottom right corner of the active cell.

    The duplication continues as long as there are entries in the adjacent column.

    If you wish to fill down a series, make at least two entries so that the interval is apparent. For instance if there is a column of data in A1:A400, enter the number "1" in B1, "2" in B2.
    Select B1:B2. Double click on the fill handle and Excel will fill the series down to B400.

    You can also select a longer series, such as the name of a supervisor and the team members.

    Format the supervisors name differently, if you want.

    Select the list and double click the fill handle. The list will be repeated down the page, as long as there is a corresponding entry in an adjacent column. The formatting will also be repeated.


    Custom Lists

    F. David McRitchie:
    Fill Handle

    Microsoft Office Online:
    Fill data in worksheet cells

    If you don't see the Fill handle:
    Click Options on the Tools menu and click the Edit tab.

    (Use the Office button in the upper left corner in 2007)
    Select or clear the Allow cell drag and drop check box.

    See all Topics

    Labels: , ,

    <Doug Klippert@ 6:20 AM

    Comments: Post a Comment

      Saturday, May 17, 2008 – Permalink –

    Enter in Multiple WorkSheets

    Group sheets

    A common use for Excel is to keep periodic statistics; sales by quarter, or phone calls per month.

    It can be tedious to try to create worksheets for each month and include duplicate data such as client or salesperson's names.

    Set up the workbook with as many worksheets that may be needed; perhaps one for each month and one for cumulative year-end totals.

    Click the tab for the first month, hold down the SHIFT key and select the last worksheet in the series.

    All the sheets are now chosen. You will see [Group] on the Title bar.

    Enter any common information on the first sheet and it will be duplicated on all of the grouped sheets.

    When you are done, Right-click a sheet tab and choose Ungroup Sheets on the context menu.

    Non-contiguous sheets can be selected using the Ctrl key.

    If the sheets are grouped, they will all be printed together.


    John Walkenbach:
    Making an exact copy of a range of formulas

    Microsoft KB:
    Run a macro on multiple sheets in Group mode

    See all Topics

    Labels: , , ,

    <Doug Klippert@ 7:51 AM

    Comments: Post a Comment

      Tuesday, May 13, 2008 – Permalink –

    Embed a Show

    Stick it in Word

    You might like to distribute a short PowerPoint slide show, and include some extra material.

    Open Word and PowerPoint.

    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

    Labels: , ,

    <Doug Klippert@ 6:23 AM

    Comments: Post a Comment

      Friday, April 18, 2008 – Permalink –

    Excuse Me Your Formula's Showing

    Formatting slip

    Try this experiment. On a sample worksheet, enter some arbitrary data in say the A1:B5 range.
    Select the C column and format it as Text.
    (Right click choose Format Cells - on the Number tab choose Text)

    In cell C1 enter a function, such as =sum(a1:b1)

    With C1 still selected, double click the Fill handle
    (the tiny box at the lower right corner of the cell.)

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

    See all Topics

    Labels: , ,

    <Doug Klippert@ 7:31 AM

    Comments: Post a Comment

      Saturday, March 22, 2008 – Permalink –

    Default Save

    Choose your own location

    When you choose to save most Office files, the Save dialog box defaults to the Documents or My Documents folder.

    (The following directions work in 2007, but you need to click on the Office button in the upper left corner of the Window)

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

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

    uses Tools>Options and the "Save" tab.

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

    Tools>Options "General".

    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.

    Change the folder where e-mail messages and attachments are saved

    D.C. Everest school district Weston, WI:
    Office Default Paths

    If you don't want to change the default, but would like to be able to quickly go to an alternate site, open the Save or Save Attachment dialog box. On the left side of the box is the Places Navigation bar. If you click the Desktop icon, that location will be used to save the file.

    You can add spots to the bar. Browse to the specific folder. Highlight the folder and click the down arrow beside the Tools option. Select "Add to My Places."

    The file or e-mail attachment can then be saved where you want.

    See all Topics

    Labels: ,

    <Doug Klippert@ 7:45 AM

    Comments: Post a Comment

      Thursday, March 20, 2008 – Permalink –

    Budget Spreadsheet

    Free money (tracker)

    One of the first things people do with Excel is make lists. Next they attempt to set up a budgeting worksheet.

    Michael Ham has a free downloadable budget spreadsheet. The formulas are protected, but you might consider it an exercise to figure out how they work.

    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.

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

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

    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.

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

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

    Ideas to help pare your expenses.

    See all Topics


    <Doug Klippert@ 8:02 AM

    Comments: Post a Comment

      Sunday, March 09, 2008 – Permalink –

    Camera Tool


    (This is the per-2007 routine. For 2007 see the bottom of this tip. )

    To create a linked picture of part of a spreadsheet for use elsewhere:

    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

    Labels: , ,

    <Doug Klippert@ 8:15 AM

    Comments: Post a Comment

      Saturday, March 01, 2008 – Permalink –

    OLAP Cubes

    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.

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

    OLAP (Wang Sweden) a Swedish software company:

    Create an OLAP Cube

    See all Topics

    Labels: ,

    <Doug Klippert@ 7:09 AM

    Comments: Post a Comment

      Tuesday, January 29, 2008 – Permalink –

    Split the Costs

    Split the sheets (?)

    Joe Chirilov presents a spreadsheet solution to a friendship breaker.

    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

    See all Topics

    Labels: , , ,

    <Doug Klippert@ 6:53 AM

    Comments: Post a Comment

      Wednesday, January 02, 2008 – Permalink –

    More than Sudoku

    Game with Excel

    "IT may be the biggest tool in the Office, but Microsoft Excel can be cool too"

    Here's an article on off-prescription Excel.

    Play with Excel

    Here are some of the games you can play for free:

    • Excel fun

    • 3D Viewer

    • Battleship

    • Blackjack

    • Breakout

    • Golf Stats

    • Mastermind

    • Maze

    • Minesweeper

    • Reversi

    • Rubix

    • Slots

    • Sudoku Solver

    • Tetris

    • Video Poker

    See all Topics


    <Doug Klippert@ 8:09 AM

    Comments: Post a Comment

      Sunday, December 02, 2007 – Permalink –

    Spreadsheet Boo-Boos

    Design suggestions

    A few links to sites with advice on spreadsheet design and error detection.

    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

    Labels: ,

    <Doug Klippert@ 4:19 AM

    Comments: Post a Comment

      Tuesday, November 27, 2007 – Permalink –

    Web Data

    Numbers from the ether

    Excel has had a feature called web query. Here's an add-in that makes it a little easier.

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

    Web Data

    Getting data from the Web in Excel

    See all Topics

    Labels: ,

    <Doug Klippert@ 6:18 AM

    Comments: Post a Comment

      Monday, November 19, 2007 – Permalink –

    Smart Tags

    Don't hide

    In Excel, if you type a stock ticker symbol, the options offered on the Smart Tag are:

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

    Smart tag functionality

    See all Topics

    Labels: , ,

    <Doug Klippert@ 7:40 AM

    Comments: Post a Comment

      Monday, November 12, 2007 – Permalink –

    All the Basics

    All(most) all you need to know has a short demo that shows you the main things anyone needs to know about Excel.

    There are many thousands of users who find that this is all they ever need.
    • Add numbers
    • Subtract numbers
    • Multiply numbers
    • Divide numbers

    Use simple formulas to do the math

    See all Topics

    Labels: , ,

    <Doug Klippert@ 8:00 AM

    Comments: Post a Comment

      Thursday, October 18, 2007 – Permalink –

    Links to Excel

    Spokes of the web

    There's a lot of information out there. The problem is how to find it.
    Here is a site that contains links to Excel information arranged in topics:

    • Excel Add-ins

    • Excel Help

    • Excel Password Recovery

    • Excel Templates

    • Excel Tips & Tricks

    • Excel Tutorials

    • Excel VBA

    • Free Excel Add Ins

    • Spreadsheet Research

    Excel Links

    See all Topics

    Labels: , ,

    <Doug Klippert@ 8:03 AM

    Comments: Post a Comment

      Wednesday, September 26, 2007 – Permalink –

    Send Your Template to MS

    Geek fame

    According to the Inside Office Blog, over 1 million people have downloaded free templates from Microsoft.

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

    Upload your template

    See all Topics

    Labels: ,

    <Doug Klippert@ 7:13 AM

    Comments: Post a Comment

      Tuesday, August 21, 2007 – Permalink –

    Office Art

    2007 choices

    Office 2007 uses OfficeArt to format text boxes, graphics and pictures.

    It's available in Word, Excel , and PowerPoint, but it is most active in PowerPoint and Excel.

    Here's a description:

    Office PPT Art


    See all Topics


    <Doug Klippert@ 8:16 AM

    Comments: Post a Comment

      Tuesday, August 14, 2007 – Permalink –

    Read All About IT!

    Excel reading

    Bastien Mensink, from the Netherlands, runs

    He has aggregated the headlines from a number of Excel Blogs.

    If you don't have them as part of your RSS list, you should.

    Weblog Headines

    See all Topics

    Labels: ,

    <Doug Klippert@ 6:47 AM

    Comments: Post a Comment

      Wednesday, August 08, 2007 – Permalink –

    Specifications for Excel 2007

    More flexibility

    Lots of limits have been changed with 2007. The size of a worksheet is now 1,048,576 rows by 16,384 columns. The number of undo levels has gone up to 100.

    Here are some more changes:

    • Worksheet and workbook specifications and limits

    • Calculation specifications and limits

    • Charting specifications and limits

    • PivotTable and PivotChart report specifications and limits

    • Shared workbook specifications and limits

    Excel 2007 Specifications

    See all Topics


    <Doug Klippert@ 7:15 AM

    Comments: Post a Comment

      Friday, August 03, 2007 – Permalink –

    Undo Excel

    Level talk

    In Excel 2007. the number of levels of the "undo stack" was increased from 16 levels to 100.

    Setting AutoFilters, showing/hiding detail in PivotTables, and grouping/ungrouping in PivotTables are now reversable.

    And the undo stack is not cleared when Excel saves, be it an AutoSave or a Save by the user.

    If you think the number of undos should be changed, here's how:

    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

      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.

    Modify the number of undo levels

    If you want to clear the undo stack, just run a macro such as:
    Sub ClearUndo()
    Range("A1").Copy Range("A1")
    End Sub

    Allen Wyatt:
    Clearing the Undo stack

    See all Topics

    Labels: , , ,

    <Doug Klippert@ 6:36 AM

    Comments: Post a Comment

      Friday, July 06, 2007 – Permalink –

    MS RSS Feeds

    Eavesdrop on the experts

    RSS feeds can give you a flow of new information.

    Microsoft knows the value of these web casts and provides a list of links from Access to SharePoint Server:

    RSS Feeds on Microsoft Office

    See all Topics

    Labels: ,

    <Doug Klippert@ 7:24 AM

    Comments: Post a Comment

      Friday, June 22, 2007 – Permalink –

    Convert Files

    No add-ins needed

    Here and there you pick up a lot of odd files. Some of them maybe for programs that have long since been sent to the great Recycle Bin.

    There is a web site that will convert a great many file formats up to 150mb.

    • 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 ;-)

    See all Topics

    Labels: ,

    <Doug Klippert@ 6:27 AM

    Comments: Post a Comment

      Thursday, March 29, 2007 – Permalink –

    System Information

    More than you wanted to know

    You can check which version of the Microsoft Office program is installed on your computer, and you can determine the product ID number of your copy of the program. You can also get information about your computer.

    In Office 2007, using Access, Excel, PowerPoint, or Word:
    1. Click the Microsoft Office Button, and then

    2. Cick Access Options, Excel Options, PowerPoint Options, or Word Options.

    3. Click Resources

    4. About Program Name, click About.
      Note Program Name is the name of the program you are in, for example, About Microsoft Office Word 2007.

    5. To see information about your computer, in the About Program Name dialog box, click System Info.

    In Word it's easier, just hit Ctrl + Alt + F1.

    See all Topics


    <Doug Klippert@ 7:24 AM

    Comments: Post a Comment

      Friday, March 23, 2007 – Permalink –

    Access or Excel

    When to use one or the other

    Use Access when you:

    • Require a relational database (multiple tables) to store your data.

    • Might need to add more tables, in the future, to an originally flat or nonrelational data set.

    • Keep a very large amount of data (thousands of entries).

    • Keep data that is mostly text.

    • Rely on multiple external databases to derive and analyze the data that you need.

    • Need to maintain constant connectivity to a large external database, such as one built by using Microsoft SQL Server.

    • Want to run complex queries.

    • Need many people working in the database and you want robust options that expose that data for updating.

    Use Excel when you:

    • Require a flat or nonrelational view of your data (that is, you do not need a relational database with multiple tables).

    • This is especially true if that data is mostly numeric - for example, if you want to maintain a financial budget for a given year.

    • Want to run primarily calculations and statistical comparisons on your data - for example, if you want to show a cost/benefit analysis in your company's budget.

    • Know that your dataset is manageable in size (no more than 15,000 rows).

    Use Access or Excel to manage your data

    See all Topics


    <Doug Klippert@ 7:50 AM

    Comments: Post a Comment