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

  Saturday, February 20, 2010 – Permalink –

Selection Address

What's the count

". . . know what range is selected at any given time. You can look at the Name Box, but that only show the active cell."

Display number of Rows and Columns

See all Topics

Labels: , , , , , ,

<Doug Klippert@ 3:59 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

  Wednesday, January 20, 2010 – Permalink –

Help ID's

VBA code

When you build a macro, you can call up information from the Excel Help file.
Ron DeBruin has the information needed through 2007.

Right clicking the Helpfile and choosing Properties will show the HP####### number in 2010.

Help Context IDs for Excel

See all Topics

Labels: , ,

<Doug Klippert@ 3:00 AM

Comments: Post a Comment

  Wednesday, January 13, 2010 – Permalink –

Text Files to Excel

A hard way to do an easy job

Text files can be imported or just pasted into Excel. If you want to learn more about SQL statements and non-code coding, look at this blog:

"Excel doesn't have an easy way to append multiple text files into one worksheet through the user interface. From time to time I hear customers asking how to do this in an easy way. Do you have to use Access or VB code to solve this problem? No. There's a way to do it in Excel by using a simple SQL statement in the connection string."

MSDN Excel Blog

See all Topics

Labels: , ,

<Doug Klippert@ 3:53 AM

Comments: Post a Comment

  Sunday, January 03, 2010 – Permalink –

Keyboard and Key Tips

Finger it out

2007 apps look different because of the ribbon, but the keyboard can still be used to speed up tasks.
Microsoft has an online course that may help

After completing this course you will be able to:
Accomplish tasks by using sequential shortcut keys, known as Key Tips, shown on the Ribbon.
Navigate around the Ribbon using the TAB key and arrow keys.
Accomplish tasks by using key combinations — keys you press at the same time - exactly as you've done in previous versions of Office.

See all Topics

Labels: ,

<Doug Klippert@ 3:54 AM

Comments: Post a Comment

  Wednesday, December 30, 2009 – Permalink –

Chart Null Data Gaps

Fill in the spaces

When creating a chart, data can sometimes be missing. You have a choice of leaving the cells empty or making them zero.

By default, an empty cell will leave gaps in your graph. Zero entries will send a line graph down to the base line.

One way to force Excel to interpolate the data is to enter the function =NA() in the empty cells.

Another way is to go to Tools>Options and choose the Chart tab.

Pick how you want data plotted - with gaps, as zero, or interpolated.

Microsoft also has this suggestion:

Gaps between the dates
"If the datasheet data for the category axis contains date number formatting, Microsoft Graph automatically uses a special type of axis in your chart called a time-scale axis.
A time-scale axis shows a blank category for dates for which you have no data. If you do not want to see these gaps — for example, if you have data for 1-Jan, 15-Jan, 3-Feb, 12-Feb, and 2-Mar, and you want to plot the days next to each other - you can change the time-scale axis to a standard category axis.
On the Chart menu, click Chart Options, click the Axes tab, and then click Category under Category (X) axis."

Note, For 2007+, on the Design tab, click Select Data in the Data area, and then click Hidden and Empty Cells on the Select Data dialog box.

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:18 AM

Comments: Post a Comment

  Saturday, December 12, 2009 – Permalink –

Display Row, Column Headings

User Function

Here's an odd little use of functions.

If you want to display the Row number on a spreadsheet, the formula
works just fine.
You could then hide the Row and Column headings and format the Row numbers any way you want. If a Row is deleted the numbers will automatically update.

Column headings are a little harder. The formula =Column() will show the number of the Column, not the letter, i.e. "2" instead of "B".

The following formula extracts the Column letter:


To break it down:


This finds the address at Row number "1" and current Column number. The abs_num of "4 " says make the result a relative address.

The formula will produce a result such as "AA1".


This function looks at the address, i.e. "AA1".
It replaces the Row number character ("1") with a null or empty value ("").
The formula will produce a result such as "AA".

Also see Daily Dose of Excel by Dick Kusleika.

Dick mused:'

"Sometime before the year 3,000, Microsoft will hopefully increase the number of columns in Excel (Hey, I can dream can't I). The challenge before you is to write a function that converts a column number to its letter equivalent assuming columns go to ZZZZ. That’s about 450,000 columns - maybe more than I need."

Of course Office 2007-10 has taken it up to 16,284 columns.

See all Topics

Labels: ,

<Doug Klippert@ 3:11 AM

Comments: Post a Comment

  Wednesday, December 09, 2009 – Permalink –


Quick graphic reinforcement

A graph or chart can give the reader a visual representation of a great deal of data. Concepts or results can be more easily grasped by a well formatted graphic.

Charts, usually, take up more space in a document than is absolutely required.

Edward Tufte has come up with the concept of Sparklines (Sparklines:Intense, Word-sized Graphics)
These are small graphs about the same height and width as common words. They are not out of place in the text of a document.

Sparklines give the reader a snapshot of the data that quickly supports the material being discussed.

Bisantz Sparklines

The Sparkmaker can create Sparklines for Word, Excel, or PowerPoint. They can also be produced in HTML.

See all Topics

Labels: , , ,

<Doug Klippert@ 3:27 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

  Sunday, November 29, 2009 – Permalink –

New Conditional Formatting

Much more capabilities

Pre-2007 Excel was limited to only 3 conditions. The new Office is more generous and versatile.
Here are some of the features:

Format all cells based on their values
Use this to create a data bar, 2-color or 3-color color scale, or icon set rule.
Format only cells that contain:
Use this to create the Excel 2003-style rules and more (format cells greater than, less than, greater than or equal to, less than or equal to, equal to, not equal to, between, not between). This is also the entry point to create rules of type: specific text, date occurring, blanks, non-blanks, errors, non-errors.
Format only top or bottom ranked values:
Use this to create top n, top n%, bottom n, bottom n% rule types.
Format only values that are above or below average:
Use this to create above average, below average, 1 or 2 or 3 standard deviation above, or 1 or 2 or 3 standard deviation below rule types.
Format only unique or duplicate values:
Use this to create rules that format unique or duplicate values.
Use a formula to determine which cells to format:
Use this to create Excel 2003-style rules where you can enter a formula to determine whether a format should be applied.

2007+ Conditional Formatting

See all Topics

Labels: ,

<Doug Klippert@ 3:48 AM

Comments: Post a Comment

  Tuesday, November 24, 2009 – Permalink –

Formatting Overview

Looking good

The judicious use of formatting can make data easier to understand as well as pleasant to see.
Scott Lowe put together a series of articles on how to format data in Excel.

The Articles are on

Anatomy of Excel formatting: Part 1
  • Boldface, italicize and underline cell content
  • Change the size and font of your text
  • Apply a default Excel style (i.e. dollar, percent, etc) to cells
  • Use date and time formatting in your spreadsheet
  • Apply shading
Anatomy of Excel formatting: Part 2
  • Apply borders
  • Resize rows
  • Resize columns
Anatomy of Excel formatting: Part 3
  • Text formatting
  • Justify cell contents
  • Change the direction of the text in your spreadsheet
  • Word wrap text
Anatomy of Excel formatting: Part 4
  • Automatically format cells based on their contents
  • Change the margins for your printed page
  • Add a header and footer to your printer spreadsheet

See all Topics

Labels: , ,

<Doug Klippert@ 3:39 AM

Comments: Post a Comment

  Sunday, November 15, 2009 – Permalink –

Color News

A multidiscipline subject

Here is a study about how color effects a reader's choice of concentration.

It was intended for newspaper publishers, but the same knowledge can be used in Web design, PowerPoint, or any other reporting application. Word and Excel will also benefit.

Color, Contrast, and Dimension in News Design


The Poynter Institute is a school for journalists, future journalists, and teachers of journalists.

See all Topics


<Doug Klippert@ 3:08 AM

Comments: Post a Comment

  Wednesday, November 04, 2009 – Permalink –

Paste is Special

Versatile functions

If the data you brought into Excel comes through as text rather than numbers, Paste Special can fix it.

  1. Go to an empty cell.
  2. Copy it
  3. Select the "corrupted" data.
  4. Go to Edit>Paste Special and choose Add.
This works better than multiplying by one. Empty cells remain empty

What's So Special About "Paste Special"?

Excel Paste Special function

Pasting Using Paste Special

See all Topics


<Doug Klippert@ 3:08 AM

Comments: Post a Comment

  Thursday, October 29, 2009 – Permalink –

Hep Me

Help topic locations

This from Ron de Bruin:

"Using the Help Context IDs

To make use of this file(s), locate the help topic of interest and make a note of its context ID.

Then, write a VBA subroutine that displays the topic.

You can attach the subroutine to a button, or provide some other way for the user to execute the sub.

For example, if you'd like to display the help topic that show the Date/Time Functions List,
you'll find that the context ID for that particular topic is 5199659.

The following VBA procedure displays that help topic."

Sub ShowHelp()
Application.Help "XLMAIN" & Val(Application.Version) & ".CHM" ,5199659
End Sub

Help Context IDs for Excel

See all Topics

Labels: , , ,

<Doug Klippert@ 3:09 AM

Comments: Post a Comment

  Sunday, October 25, 2009 – Permalink –

Export Formatted Sheets

Access to Excel

Access provides an easy way to export data to Excel through the Office Links feature.

To use this feature, simply select a relevant database object and choose Tools>Office Links> Analyze It With Excel.

The worksheet Excel creates includes some minor formatting applied to the field headings that appear in row 1. Some formatting in your original Access database affects the worksheet cell formatting as well. For example, if you're exporting from a datasheet, gridline and font attributes are carried over to Excel. If you use the Office Links feature to export data behind a form, text box shading and font properties are applied.

The final result in Excel may not exactly match your Access data; however, you'll probably find that less work is required to get your Excel version of the data into an easily readable state.

In Office 2007-10 it's External Data>Excel

See all Topics


<Doug Klippert@ 3:07 AM

Comments: Post a Comment

  Saturday, October 17, 2009 – Permalink –

Shortcut Borders

Keyboard trick

Use Ctrl+1 to bring up Excels Formatting dialog box. Shift+B will take you to the Borders tab.

Now you can use the following key strokes (2002+):

Apply or remove the top border.
Apply or remove the bottom border.
Apply or remove the left border.
Apply or remove the right border.
Apply or remove the downward diagonal border.
Apply or remove the upward diagonal border.
Apply or remove the horizontal interior border.
Apply or remove the vertical interior border.
outlines the cells.
give interior (vertical and horizontal).
removes all borders.
brings up the color palette.

Shortcuts for Applying Borders

Ctrl+Shift+7 will outline a cell without having to display the Format dialog.

See all Topics

Labels: , ,

<Doug Klippert@ 3:28 AM

Comments: Post a Comment

  Thursday, October 08, 2009 – Permalink –

Copy Paste

Excel tables

One way to create a new table in an Access database from information included in an Excel spreadsheet is to select the pertinent data on the spreadsheet, including the field names.

Copy the selection (Edit>Copy, or CTRL+C)

Switch back to Access .

With Tables objects being shown in the database window, choose Edit>Paste, or use the CTRL+V shortcut.

Access will ask if the first row contains the field names and then will paste the information as a new database table.

See all Topics


<Doug Klippert@ 3:46 AM

Comments: Post a Comment

  Thursday, September 24, 2009 – Permalink –

Shut it All Down

Close the spreadsheet and Excel

As it comes out of the box, at this point, Excel 2007 is set to display spreadsheets on the task bar. No big deal 2003 did too.

If, however, you only have one workbook open and want the application to shut down when you close the book you must make a little change.

Just as before, you must deselect Windows in the Taskbar from the View Options.

In 2007 it is located by clicking on the logo icon, then choose Excel Options. The Windows entry is on the Personalize screen

See all Topics

Labels: ,

<Doug Klippert@ 3:44 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

  Wednesday, September 02, 2009 – Permalink –

Dynamic Tabs

Change tab names automatically

Changing the names of tabs is easy, just double click the tab or right click and choose rename.

Allen Wyatt has a small piece of code that will automatically update the tab name based on the value of a cell in the spreadsheet.

Sub myTabName()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Allen also has some error checking code on his site:

Dynamic Worksheet Tabs

Dick Kusleika suggests another way using a change event:

Naming a sheet based on a cell

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:33 AM

Comments: Post a Comment

  Tuesday, August 25, 2009 – Permalink –

Charting Tools

Sample graphs

Ed Ferrero has a number of sample Excel charts and tools.

One he calls a Wind Rose:

How to build a wind rose. Converts compass directions to degrees and radians, and plots a nice-looking chart.

Ferrero Consulting Pty Ltd:

See all Topics

Labels: ,

<Doug Klippert@ 3:05 AM

Comments: 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

      Wednesday, August 05, 2009 – Permalink –

    Large Text Files

    Split between worksheets

    While this problem is alleviated in Excel 2007 with its 1,048,576 rows by 16,348 columns, The old XL versions are still here.

    Text files with a large number of records are better handled in a program like Access. Having said that, there can be times that these lists must be imported into Excel. If the file has over 65,536 records, the data will not fit on a single worksheet.

    Here's a Microsoft Knowledge Base article with the macro code needed to bring oversized text data into Excel and split it into multiple worksheets:

    Sub LargeFileImport()
    'Dimension Variables
    Dim ResultStr As String
    Dim FileName As String
    Dim FileNum As Integer
    Dim Counter As Double
    'Ask User for File's Name
    FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
    'Check for no entry
    If FileName = "" Then End
    'Get Next Available File Handle Number
    FileNum = FreeFile()
    'Open Text File For Input
    Open FileName For Input As #FileNum
    'Turn Screen Updating Off
    Application.ScreenUpdating = False
    'Create A New WorkBook With One Worksheet In It
    Workbooks.Add template:=xlWorksheet
    'Set The Counter to 1
    Counter = 1
    'Loop Until the End Of File Is Reached
    Do While Seek(FileNum) <= LOF(FileNum)
    'Display Importing Row Number On Status Bar
    Application.StatusBar = "Importing Row " & _
    Counter & " of text file " & FileName
    'Store One Line Of Text From File To Variable
    Line Input #FileNum, ResultStr
    'Store Variable Data Into Active Cell
    If Left(ResultStr, 1) = "=" Then
    ActiveCell.Value = "'" & ResultStr
    ActiveCell.Value = ResultStr
    End If
    'For Excel versions before Excel 97, change 65536 to 16384
    If ActiveCell.Row = 65536 Then
    'If On The Last Row Then Add A New Sheet
    'If Not The Last Row Then Go One Cell Down
    ActiveCell.Offset(1, 0).Select
    End If
    'Increment the Counter By 1
    Counter = Counter + 1
    'Start Again At Top Of 'Do While' Statement
    'Close The Open Text File
    'Remove Message From Status Bar
    Application.StatusBar = False
    End Sub

    Importing Text Files Larger Than 16,384/65,536 Rows

    Notice the code about 17 lines from the bottom of the macro.

    'For xl97 and later change 16384 to 65536.

    Also, after import, the data must be parsed. Use Data>Text to columns.

    If you have not worked with macros before, Dave McRitchie has a tutorial:

    Getting Started with Macros and User Defined Functions

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:13 AM

    Comments: Post a Comment

      Thursday, July 16, 2009 – Permalink –


    Transfer data

    XML makes data transferable between applications.
    Here is a tutorial with downloadable files.
    Some simple guidance of how to transfer data from Excel or Access into HTML web pages using XML data files. VBA programs can be used to export data tables from Excel or Access into simple XML files. There are several examples of using different methods to display the XML and XSL files on web pages in order to quickly share your data with others.

    An introduction to Excel and XML data files

    Some nice photos and calendar layout:
    Monthly calendar with photos

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:56 AM

    Comments: Post a Comment

      Wednesday, July 15, 2009 – Permalink –

    Restore Defaults

    Office 2003 redo

    To reset the original settings in Office 2003, follow these steps.
    Make sure that you back up your files before you follow these steps.
    1. 1. Start any Office 2003 program.
    2. On Help menu, click Detect and Repair.

    3. Click to select the Discard my customized settings and restore default settings check box, and then click Start.
    4. Quit the application, and then click Ignore.
    5. Click OK when you receive the following message:
      Reset of setting to default succeed.

    Microsoft Office Diagnostics in 2007 replaces Diagnose and Repair:

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:28 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

      Tuesday, June 30, 2009 – Permalink –

    Thirtieth Condition Formatting

    Three is not always enough

    Pre-2007 Excel gives the user the ability to specify up to three conditions under Format>Conditional Formatting.

    If that is not enough, Frank Kabel and Bob Phillips of offer a free download that extends the conditions to 30!

    Extended Conditional Formatter

    Also see:
    Conditional Formatting (including 2007)

    See all Topics

    Labels: , , ,

    <Doug Klippert@ 3:09 AM

    Comments: Post a Comment

      Friday, May 29, 2009 – Permalink –

    Report Manager

    Define Print & View Areas

    The Excel 2000 Report Manager add-in is not included with Excel 2002+, as was the case with previous versions of Excel.

    To use it, you must first download the Excel 2002 Report Manager add-in from Downloads on Microsoft Office Online. Follow the instructions on the Downloads page to install the Report Manager.
    (For 2007 see

    from Microsoft Office Online

    "Using the Report Manager add-in program, you can combine worksheets , views (a set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.), and scenarios (a named set of input values that you can substitute in a worksheet model.) into printable reports. For example, if you have a Best Case and a Worst case scenario, a Summary view, and a Details view, you can create a report that presents the Best Case scenario in the Details view and another report that presents the Best Case Scenario in the Summary view. Reports that you create are automatically saved with your workbook so that you can print them at any time."

    Note If the Report Manager is not available on the View menu after downloading the add-in, click Add-ins on the Tools menu, and then select Report Manager.

    You can use the Report Manager to do the following:

    Create a report for printing

    1. On the View menu, click Report Manager.
    2. Click Add.
    3. In the Report Name box, type a name for the report.
    4. In the Sheet box, click the sheet you want to use for the first section of the report.
    5. Do one of the following:
      • To use a view for the first section of the report, select the View check box, and then click the view in the View box.
      • To use a scenario for the first section of the report, select the Scenario check box, and then click the scenario in the Scenario box.
    6. Click Add to enter the view or scenario as a section in the Sections in this Report box.
    7. Repeat step 5 and 6 until you've created all of the sections you want in the report.
    8. To change the order of the sections, in the Sections in this report box, click the section you want to move, and then click either Move Up or Move Down.
    9. To number the pages of the report consecutively, select the Use Continuous Page Numbers check box.
    Note Microsoft Excel prints sections of a report in the order in which they're listed in the Sections in this report box.

    Edit a report for printing

    1. On the View menu, click Report Manager.
    2. In the Reports box, click the report you want to edit, and then click Edit.
    3. Do one or more of the following:
      • To add a new section, click the sheet, view, and scenario you want under Section to Add, and then click Add.
      • To delete a section, click the section in the Sections in this report box, and then click Delete.
      • To change the order of the sections, in the Sections in this report box, click the section you want to move, and then click either Move Up or Move Down.
      • To number the pages of the report consecutively, select the Use Continuous Page Numbers check box.
      Note Microsoft Excel prints sections of a report in the order in which they're listed in the Sections in this report box.

    Print a report

    1. On the View menu, click Report Manager.
    2. In the Reports box, click the report you want to print.
    3. Click Print.
    4. In the Copies box, type the number of copies you want to print.
    Also see: Template Wizard with Data Tracking

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:34 AM

    Comments: Post a Comment

      Tuesday, May 26, 2009 – Permalink –

    Name that Range

    How to use names in Excel

    Named ranges are one of the more powerful tools in Excel.

    Jan Karel Pieterse of JKP Application Development Services has written a tutorial that will help you understand this technique.

    Here are some of the chapter titles:

    • How To Define Range Names

    • How To Use Range Names

    • Absolute And Relative Addressing

    • The Context Of Names

    • Special Names

    • A Step Further: A Formula In A Defined Name

    • Dynamic Names

    • Passing Arguments To A Defined Name Formula

    • Bugs in Excel's Name Object
    Range Names in Excel

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:54 AM

    Comments: Post a Comment

      Tuesday, May 19, 2009 – Permalink –

    UDF is not a Baby Alien

    Things should to function

    Frank Rice has written a "show how" about creating functions that are not included in the box.

    "Excel allows you to create custom functions, called "User Defined Functions" (UDF's) that can be used the same way you would use SUM(), VLOOKUP, or other built-in Excel functions.
    The Excel user who wishes to use advanced mathematics or perform text manipulation is often seriously disappointed by Excel's limited library of formulas and functions. A UDF is simply a function that you create yourself with VBA.

    The following is a sample that is a good candidate for a UDF:
    Function CtoF(Centigrade)
    CtoF = Centigrade * 9 / 5 + 32
    End Function

    In the Worksheet you would enter something like:

    frice's Weblog

    Here are some other links:
    User Defined Functions
    Functions to Calculate Light Years

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:41 AM

    Comments: Post a Comment

      Friday, May 15, 2009 – Permalink –

    Tabs with the Number of the Week

    Count to 52

    Excel no longer has a theoretical limit on the number of worksheets in a workbook. One common use of this ability is to add a worksheet for each week in the year.

    Here's a macro that does the trick:
    Sub YearWorkbook()
    Dim iWeek As Integer
    Dim sht As Variant
    Application.ScreenUpdating = False
    Worksheets.Add After:=Worksheets(Worksheets.Count), _
    Count:=(52 - Worksheets.Count)
    iWeek = 1
    For Each sht In Worksheets
    sht.Name = "Week " & Format(iWeek, "00")
    iWeek = iWeek + 1
    Next sht
    Application.ScreenUpdating = True
    End Sub
    Naming tabs for weeks

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:58 AM

    Comments: Post a Comment

      Friday, May 08, 2009 – Permalink –

    Data from the Other Sheet

    Sometimes we must go next door

    Here is a discussion about how to use data that may be on a different worksheet, or another workbook.

    Getting data from other sheets can be very easy: or it can be very hard, it all depends on what you need to do. This article looks at some of the ways you can get data from other sheets.

    • The basics
    • Using the Indirect function
    • Back to the old school, XLM! (previous and next sheet)
    • VBA user defined functions (previous and next sheet)
    • Links
    Methods In Excel

    Cell References

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:14 AM

    Comments: Post a Comment

      Thursday, April 16, 2009 – Permalink –


    No Excel needed

    Here's a site that lets you design and post spreadsheets on the web.
    "Keep your records, lists and spreadsheets in one place online. Easily slice, dice and share with others.

    With a free account, you can...
    • Access your spreadsheets from anywhere
    • Share your spreadsheets easily - just email the URL
    • Add spreadsheets to your weblog
    • FREE storage

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:17 AM

    Comments: Post a Comment

      Wednesday, April 15, 2009 – Permalink –

    Date an Octothorpe

    Date an Octothorpe

    Some more of those things I'm sure I used to know

    The keyboard combination of Alt+Shift+D inserts the current date in MS Word and PowerPoint. Ctrl+; (semicolon) does it in Excel and Access.

    If you do not like the date's format, select a different one with Insert>Date and Time and, if you would like to make that permanent, click on the Default button in the lower left corner of the dialog box (in PowerPoint it's in the lower right corner).

    In Excel, Ctrl+Shift +# formats the entry as day-month-year. Ctrl+1 will display the "Format cells" dialog box.

    BTW, the "hash, pound or number" sign # is also called an "octothorpe".

    The person who named it combined Octo for the eight points and Thorpe for James Thorpe.

    "Bell Labs engineer, Don Macpherson, went to instruct their first client, the Mayo Clinic, in the use of the new (touch tone phone system). He felt the need for a fresh and unambiguous name for the # symbol. His reasoning that led to the new word was roughly that it had eight points, so ought to start with octo-. He was apparently at that time active in a group that was trying to get the Olympic medals of the athlete Jim Thorpe returned from Sweden, so he decided to add thorpe to the end."

    While we're at it, the "backwards P, Enter mark" is actually named a "pilcrow".

    The pilcrow was used in medieval times to mark a new train of thought, before the convention of using paragraphs was commonplace.

    Also see:
    Geek-speak names for punctuation marks


    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:36 AM

    Comments: Post a Comment

      Thursday, April 09, 2009 – Permalink –

    Excel-lent E-Mail

    Outlook, Excel, and VBA

    Ron de Bruin, Microsoft MVP - Excel, has put together a collection of VBA routines to make Excel e-mail friendly.

    See if these topics tempt you:

    Example Code for sending mail from Excel
    • Mail Workbook
    • Mail one Sheet
    • Mail more than one Sheet
    • Mail the Selection or range
    • Mail Every Worksheet with Address in cell A1
    • Mail sheet or sheets to one or more people
    • Mail range or sheet in the body of the mail (Send personalized email)
    • Mail a message to each person in a range with Outlook
    • Mail a message to each person in a range with CDO (no security warnings)
    • Sending a different file to each person in a range with Outlook
    • Zip the ActiveWorkbook and mail it with Outlook
    • Security (Prevent displaying the dialog to Send or not Send)

    Also Download Addins for Excel e-mail information

    Also see:

    John Walkenbach:
    Sending Personalized Email from Excel

    See all Topics

    Labels: , , ,

    <Doug Klippert@ 3:27 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

      Monday, March 30, 2009 – Permalink –

    Linked Table Problems

    Lost functionality

    Have you found that you cannot update linked Excel tables in Access 2003?

    If you have installed Microsoft Office 2003 Service Pack 2 (SP2):

    Because of legal issues, Microsoft has disabled the functionality in Access 2003 and in Access 2002 that let users change the data in linked tables that point to a range in an Excel workbook. However, when you make changes directly in the Excel workbook, the changes appear in the linked table in Access.
    You cannot change data in linked tables that are to an Excel workbook

    You might try reinstalling and then only installing SP1.

    Guacamole dipped - Access to Excel linked table gotcha
    Patent ruling costs Microsoft $8.9 million

    "A jury in U.S. federal court found that Microsoft infringed on a Guatemalan inventor's 1994 patent on technology linking the company's Access and Excel programs, and ordered the world's largest software maker to pay $8.9 million in damages.

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:18 AM

    Comments: Post a Comment

      Wednesday, March 04, 2009 – Permalink –

    Animate Window Size

    So cool!

    The following macro has little or no practical computing value, but it can add a "way cool" element when a worksheet is unhidden.
    There are three states that a worksheet can be in; Minimized, Maximized, and Normal.

    This macro will gradually resize a worksheet from small to Maximized. The worksheet appears to be growing:

    Sub SheetGrow()
    Dim x As Integer
    With ActiveWindow
    .WindowState = xlNormal
    .Top = 1
    .Left = 1
    .Height = 50
    .Width = 50

    For x = 50 To Application.UsableHeight
    .Height = x
    Next x

    For x = 50 To Application.UsableWidth
    .Width = x
    Next x

    .WindowState = xlMaximized
    End With
    End Sub

    (By Mark William Wielgus)

    Also fun:

    Sub SheetGrow()

    Dim x As Integer, xmax As Integer

    With ActiveWindow

    .WindowState = xlNormal

    .Top = 1

    .Left = 1

    .Height = 50

    .Width = 50

    If Application.UsableHeight > Application.UsableWidth Then

    xmax = Application.UsableHeight


    xmax = Application.UsableWidth

    End If

    For x = 50 To xmax

    If x <= Application.UsableHeight Then .Height = x

    If x <= Application.UsableWidth Then .Width = x

    Next x

    .WindowState = xlMaximized

    End With

    End Sub

    # posted by Joerd : 12/30/2005

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:27 AM

    Comments: Post a Comment

      Wednesday, February 25, 2009 – Permalink –

    Hide Digits

    Simple obfuscation

    The kid said,
    "Daddy, I know the secret password!
    It's star, star, star, star!"
    You can use functions to hide parts of sensitive data.

    Social Security Number 555-55-5555

    =CONCATENATE("***-**-", RIGHT(B2,4))

    Combines the last four digits of the SSN with the "***-**-" text string


    Credit Card Number 5555-5555-5555-5555

    =CONCATENATE(REPT("****-",3), RIGHT(B3,4))

    Repeats the "****-" text string three times and combines the result with the last four digits of the credit card number


    Microsoft Office Online:
    Display only the last four digits of identification numbers

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:14 AM

    Comments: Post a Comment

      Tuesday, February 10, 2009 – Permalink –

    Dynamic AutoShape Link

    Show the star

    Here's a hint that I had forgotten about.

    You can tie the result of a cell to an AutoShape.

    This displays the value in a more dramatic manner.
    1. Create an AutoShape on the Worksheet
    2. With the shape selected, type an equal sign in the formula bar.
    3. Enter the address of the linking cell (or click the cell)
    4. Hit Enter
    Thanks to for the reminder.

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:21 AM

    Comments: Post a Comment

      Wednesday, January 14, 2009 – Permalink –

    Spreadsheet Design

    Make it work and look good

    Timothy Miller uses the nom de screen of "Jethro" (Moses' Father-in-Law).

    His site/blog gives some tips on how to present an Excel solution

    Design and layout

    One of the easiest ways to set up spreadsheets that calculate or generate results that need to be reported is to separate the function from the form. Just like a shiny exterior on a car hides the internal engine and wiring. I always create my reports and front end menus to look good and generate results and calculations in more functional sheets.
    Hiding unnecessary sections

    If you must have calculations and working sections visible, then hide the unnecessary bits. Hiding a row or column is only one way of doing this. Using the group function you can rollup whole rows of information, e.g. components that add to a subtotal or constants and variables such as exchange rates, interest rates, and other indexes.
    Use of colour and graphics

    I like to use the company logo or other graphic as a design element in my spreadsheet. Sometimes I do this by using the corporate colours, other times by using the graphic itself. If I have a spreadsheet with a lot of macro buttons, I may use command objects and use the logo as a picture on the button.
    Removing excel components

    There are a number of excel components that you can turn off. Menu screens and reports screens may not need horizontal or vertical scroll bars, sheet tabs or row and column headings. Using macro buttons to return to a menu can overcome the need for sheet tabs. Not displaying gridlines will give a clean uncluttered look to a layout, and then using borders as necessary can create emphasis in the right areas.

    You'll find the complete text here:
    Design Presentation Tips
    Also see:

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:02 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

      Sunday, December 14, 2008 – Permalink –

    Format Numbers

    It's your choice

    Here is an almost forgotten sample spreadsheet. It was constructed, for Microsoft, back in 1997 by Lori B. Turner, and is still relevant.

    The sheets are protected. In order to see the cell formatting, you need to go to Tools>Protection to Unprotect Sheet . . . There is no password required

    Formatting sample Workbook


    Custom Formats

    If you need to format numbers that will be displayed connected to text, here is one way to do it:

    1. In cell A1, enter the text "You still owe the sum of".
    2. In cell A2, enter the number "5434".
    3. In cell A3, enter the text "for invoice # 2232 from 6/15/2001".
    4. In cell A4, enter the formula =A1&" "&TEXT(A2,"#,##0")&" "&A3.

    You still owe the sum of 5,434 for invoice # 2232 from 6/15/2001

    Excel Tips
    Formatting concatenated numbers and text


    Excel Format Tips

    A very well produced examination of the subject by Jon Peltier:
    Number Formats in Microsoft Excel

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:14 AM

    Comments: Post a Comment

      Friday, December 05, 2008 – Permalink –

    Merge Formatting

    $ lost

    The data in Access or Excel has been formatted. You have leading zeros, percents, currency is formatted and so on.

    Word 200+, however loses the formatting when a mail merge is attempted.

    Here's a fix.

    Word has three potential data access methods, the "old fashioned" ODBC or DDE and the newer OLE DB.

    ODBC and OLE DB can, quickly, extract data from a source application without opening the program. The application does not even have to be installed.

    The downside is that these methods do not transfer the formatting in the data file. Individual MERGEFIELDs need to be formatted in Word.

    DDE can be used with Excel and Access. It communicates with the source and carries the formatting into the target document. This is how it worked before Word 2002.

    To have a choice go to:
    Tools>Options>General "Confirm Conversions at Open"

    When you connect to the Data Source, a dialog box will give you the opportunity to choose the type of connection to use.

    If you don't see DDE, check Show all.

    Also see:

    Answer Box:
    Numbers don't merge right in Word


    Cindy Meister:
    Mail Merge FAQ

    Here are some other Mail Merge resources:
    Mail Merge Links

    See all Topics


    <Doug Klippert@ 7:38 AM

    Comments: Post a Comment

      Sunday, November 30, 2008 – Permalink –

    Time Without Limits

    No Delimiters

    Excel is most happy when you enter dates and times with the correct separators.

    1/1/2004 is a good date. So is 1-1-2004.

    If you just entered 112004 in a cell formatted as a date you'll get:

    Wednesday, August 27, 2206

    the 112,004th day since January 1, 1900.

    Chip Pearson has come up with VBA code, using the Worksheet_Change event procedure, that will allow you to enter dates without dashes or slashes.

    Date And Time Entry

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:45 AM

    Comments: Post a Comment

      Tuesday, November 25, 2008 – Permalink –

    Information Functions

    Who, What, Where

    If you need to know if automatic calculation is on or off or the number of worksheets that are active, Excel can display the INFO.

    System Information:

    Current directory

    Available bytes of memory

    Memory in use

    Total bytes of memory

    Number of active worksheets

    Cell currently in the top left of the window

    Operating system

    Recalculation mode

    Excel version

    Name of system. (PC or Mac)

    Excel Spreadsheet Information Functions

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:12 AM

    Comments: Post a Comment

      Saturday, November 15, 2008 – Permalink –

    Locate Duplicates with Conditional Formatting

    Highlight entries

    Conditional formatting can be set up by selecting the whole range, or for the first cell in the range and then copy down that conditional format. I find it is usually just as easy to select the whole range to start with. The formula will adjust itself.

    In this example, cell B2 has a heading of Product Numbers.

    Select cell B3 (or the entire targeted range) and from the menu.

    Select Format > Conditional Formatting.

    The Conditional Formatting dialog opens with the initial dropdown saying
    "Cell Value Is".

    Click the arrow next to this, and choose
    "Formula Is".

    After selecting "Formula Is", the dialog box changes appearance.
    Instead of boxes for "Between x and y", there is now a single formula box.

    You can type in any formula as long as that formula will evaluate to TRUE or FALSE.

    The formula to type in the box is

    Conditional Formatting

    This says, "look through the entire range of column B.

    Count how many cells in that range are the same value as what is in B3."
    (In the graphic, B7 is the Active cell.)

    That same comparison will be made in every cell that contains the conditional formatting.

    (If your data is in column E and you are setting the first conditional formatting up in E5, the formula would be =COUNTIF(E:E,E5)>1.)

    Anytime a duplicate appears in the range, it will receive the special formatting.

    In this example, any time a duplicate number appears anywhere in column B, even if it is not itself formatted, the selected range will reflect the duplicate.

    =COUNT(B:B,B3)>2 would count entries that appear more than two times.
    =COUNT(B:B,B3)=2 would count entries that appear twice.

    If you want only a part of the column in the formula, it is easier to use absolute addresses, such as =COUNT($B$3:$B$200,B3)>1

    Adapted from

    Also see:

    Chip Pearson's discussion of duplicates:
    Duplicate And Unique Items In Lists

    Conditional Formatting
    (See Hide Duplicate Values)

    See all Topics

    Labels: , ,

    <Doug Klippert@ 3:45 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

      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

      Wednesday, October 01, 2008 – Permalink –

    Data Comparison

    No formulas

    The Data Consolidation technique allows you to compare lists quickly and easily.

    With the Consolidation technique, you can identify the number of duplicate entries in two or more lists without using a formula.
    (not that it's easier, just that there are no formulas)


    In the sheet there are two lists: List 1 is in column A (in cells A2:A10), and List 2 is in column C (in cells C2:C10).

    1. In Cell B1 type "List number".
    2. In Cells B2:B10, enter the number 1.
    3. In Cells D2:D10, enter the number 2.
    4. Cut Cells C2:D10 and paste them into Cell A11.
    5. Press Ctrl+*, then press Ctrl+F3, and enter a name for the list (such as Compare).
    6. Select cell D4 or another worksheet.
    7. From the Data menu, select Consolidate.
    8. Select Count as the Function.
    9. In the Reference box, press F3 and paste the Name you defined for the list.
    10. Click Add.
    11. Select both Top row and Left column "Use labels in" checkboxes.
    12. Click OK.

    The numbers appears in Column B are the totals of the list number in Column B.

    If the result = 1, the name appears in List 1 and does not appear in List 2.
    If the result = 2, the name appears in List 2 and does not appear in List 1.
    If the result = 3, the name appears in both lists (1+2=3).

    The action is not dynamic, so if you make changes, the Consolidation must be rerun.

    "Mr Excel ON EXCEL" (Holy Macro Books)

    Also see:
    John Walkenbach:
    Comparing Two Lists With Conditional Formatting

    Chip Pearson:
    Duplicate And Unique Items In Lists

    Here's a more complex method:
    Microsoft Office Online:
    Use Excel to compare two lists of data

    What are Consolidated Worksheets ?

    See all Topics

    Labels: , , ,

    <Doug Klippert@ 4:16 AM

    Comments: Post a Comment

      Tuesday, September 30, 2008 – Permalink –

    Access Data - Excel Time Sheets

    Distribute to everyone

    Many times an office will provide Excel for all users, but not want or need to also install Access on every desk.

    Helen Feddema has laid out a method to use the data in an Access database to create Excel workbooks. These workbooks can then be e-mailed to employees to be used to record time spent on projects.

    The code provided is above the entry level user, but understandable.

    There is a downloadable file that includes the instructions and samples of the Access and Excel files.

    Go to Access Archon Columns from Woody's Office Watch.

    The pertinent file is down near the bottom of the page. Look for article 127 and download

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:17 AM

    Comments: Post a Comment

      Sunday, September 14, 2008 – Permalink –

    Add a Picture to a Comment

    Also graphs

    1. Right-click the cell that contains the comment.
      (or choose Insert Comment)
    2. Choose Edit Comment, and clear any text from the comment.
    3. Click on the border of the comment, to select it.
    4. Choose Format>Comment
      (or Ctrl+1)
    5. On the Colors and Lines tab, click the drop-down arrow for color.
    6. Click Fill Effects
    7. On the picture tab, click Select Picture
    8. Locate and select the picture
    9. Click Insert, click OK, click OK


    Charles Maxson has some code that will place a picture in the comment box based on the contents of a cell.

    " Imagine that you have a list of parts for a product and you want to assign them to cells... then your users could see them as they hover over the cells."

    Excel: Code to add picture to Excel Comment

    Mary Ann Richardson, on, suggests saving a graph as a JPG and using that as a picture in a comment.

    Add an Excel chart to a comment

    See all Topics

    Labels: ,

    <Doug Klippert@ 2:48 AM

    Comments: Post a Comment

      Wednesday, August 13, 2008 – Permalink –

    Value of Cell in Header

    or Footer

    The header and footer cannot contain a link to a cell. You can create and run a macro that will put the value of a cell into a footer or header. You could run this macro each time the contents of the specified cell changes.

    Sub AssignCenterHeaderToValueInA1OnActiveSheet()
    ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
    End Sub

    Or use it as an Event code:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
    End Sub
    Information from a cell in a worksheet

    Erlandsen Data Consulting:
    Insert headers and footers

    Chip Pearson:
    Headers and Footers

    Jan's Computer literacy 101:
    Excel Basics: Setup Header/Footer

    University of Wisconsin at Eau Claire:
    Modifying Header and Footer Information

    See all Topics

    Labels: , , ,

    <Doug Klippert@ 4:27 AM

    Comments: Post a Comment

      Monday, July 14, 2008 – Permalink –

    Column(s) Function


    "Excel will adjust cell references in formulas when you insert or delete rows or columns.

    For example, if the cell C1 contains the formula =A1/B1 and you insert a column to the left of column A; the formula will change to =A1/C1.

    The problem then occurs with VLOOKUP. Its column index number argument is a simple number, not a reference.


    For Example:

    1. Choose a blank worksheet
    2. In cells A1 and A2, enter the values 1 and 2.
    3. In B1 and B2, enter Jan and Feb.
    4. Select all four cells and drag the fill handle at the bottom right-hand corner of the selection downward to row 12.

    You should now have the numbers 1 through 12 in column A and the months Jan through Dec in column B.

    In cell D1 enter the formula


    Now enter any number from 1 to 12 in cell C1. The formula will select the corresponding month name.

    To demonstrate the problem, right-click on the heading of column B and choose Insert.

    The formula changes to =VLOOKUP (D1,A1:C12,2), which returns 0.

    Excel correctly changed the cell reference from C1 to D1 and expanded the lookup range to include the inserted column, but it cannot change the column index number.

    Press Ctrl-Z to undo the column insertion.

    The solution is to modify the formula so that the column index number is not hard-coded but instead is calculated from cell references.

    You could use the COLUMN() function that returns the column number of the reference and, as in this example, compute the number of columns between the first and last columns:

    =VLOOKUP (C1,A1:B12,COLUMN(B1)-COLUMN(A1)+1).

    A more esthetically pleasing, or sophisticated, function might be:


    This returns the number of columns in an array or reference.

    The modified lookup function looks like this:

    =VLOOKUP (C1,A1:B12,COLUMNS(A1:B1)).

    Either way, now if a column is inserted in the middle of the range, the column index will be adjusted."

    From a PC Magazine article
    By Neil J. Rubenking


    Allan Wyatt's ExcelTips:
    Using the Column Function

    Copy Rows
    (Scroll down to about the middle of the page)

    See all Topics

    Labels: , , , ,

    <Doug Klippert@ 5:25 AM

    Comments: Post a Comment

      Thursday, July 03, 2008 – Permalink –

    Show Formulas in Cell Comments

    Display properties

    Select the cells and then run this macro:

    Sub CommentThem()
    Dim cell As Range
    On Error Resume Next
    On Error GoTo 0
    For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
    If cell.Formula <> "" Then
    cell.Comment.Visible = False
    On Error Resume Next 'fails on invalid formula
    cell.Comment.Text Text:=" Address: " & cell.Address(0, 0) & Chr(10) & _
    " Value: " & cell.Value & Chr(10) & _
    " Format: " & cell.NumberFormat & Chr(10) & _
    " Formula: " & cell.Formula
    On Error GoTo 0
    End If
    Next cell

    End Sub

    Formulas in Comments

    by David McRitchie

    Show FORMULA of another cell in Excel

    [Edited entry from 3/12/2005]

    See all Topics

    Labels: , ,

    <Doug Klippert@ 5:11 AM

    Comments: Post a Comment

      Tuesday, June 24, 2008 – Permalink –


    Only what you want

    Excel has a tool to sort lists with a number of criteria.

    Select a single cell in the table and go to Data>Filter AutoFilter.
    Click on the down arrow next to the field name and choose Custom.

    The illustration shows how to set up a filter that displays data between two dates.

    AutoFilter Tips

    "Some tips and techniques for working with AutoFilters, and some workarounds for problems you may encounter."

    Microsoft Assistance:
    All about AutoFilters

    Jay Walkenbach:
    Displaying AutoFilter criteria

    The University of North Carolina at Charlotte:
    Using Custom Views with Autofilter

    "If you frequently use Autofilter to view portions of your worksheets, you might find Custom Views to be a useful tool. Custom Views can be easily set up based on your Autofilter criteria. Once that is done, the worksheet views that you have created display in a drop down list so you (or others) can select them."

    "The Display Filter Criteria doesn't work well. It only works when after set the filter, the cell with the formula is selected. Then press F2 en after that push the Enter key. Then it works. but the cell with the formula isn't updated automatically..."

    # posted by W. van Dam : 7/06/2005

    See all Topics

    Labels: ,

    <Doug Klippert@ 4:22 AM

    Comments: Post a Comment

      Tuesday, June 17, 2008 – Permalink –


    Macro control

    Here are some useful macros concerning Excel and printing.
    They were written by Ole P. Erlandsen of:


    Change the default printer
    Change the default printer with a macro.

    Insert headers and footers
    This example macro inserts a header/footer in every worksheet in the active workbook. It also inserts the complete path to the workbook.

    Print all workbooks in a folder
    With these macros you can print all workbooks in a selected folder. You have more control with what is printed than you have if you do this from Windows Explorer.

    Print multiple selections on one sheet
    If selected multiple cell ranges is printed out on different sheets, you can use this macro example to print all the selected areas on one sheet.

    Select a printer tray before printing
    In Excel you don't have the opportunity to set the properties FirstPageTray or OtherPagesTray like you can in Word. It's possible to create a simple solution by using SendKeys.

    See all Topics

    Labels: ,

    <Doug Klippert@ 5:25 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