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

  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

  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

  Sunday, December 06, 2009 – Permalink –


and More

Gabriels Horn from the Curve Bank
Famous curves from Cal State at LA. All your favorites from Astroid to Witch of Agnesi.

Here is a collection of Functions relating to astronomy from

Can't tell who might be interested in the obliquity of the equator given date in days after J2000.0. See: Astro VBA

Other Curve stuff: converting polar coordinates to Cartesian coordinates.

"Students of analytic geometry, (the kind that combines algebra and geometry), often work in one of two coordinate systems: Cartesian or Polar - and frequently must convert from one to the other.

The Cartesian system locates points on a plane by measuring the horizontal and vertical distances from an arbitrary origin to a point. These are usually denoted as a pair of values (X, Y).

The Polar system locates the point by measuring the straight line distance, usually denoted by R, from the origin to the point and the angle of an imaginary line from the origin to the point, θ, (Greek letter Theta), measured counterclockwise from the positive X axis."

See all Topics

Labels: , ,

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

  Thursday, April 23, 2009 – Permalink –

Lookup, Down, and Sideways

A very useful Excel feature

Excel does not have "relational" tables like database applications such as Access.

You, however, can make use of database functions including the ability to look up values in a table based on a value.

You could, for instance look up a salesperson's records based on an employee ID.

All 'Bout Computers has an article describing "Lookups".

Using VLOOKUP, HLOOKUP, INDEX, and MATCH in Excel to interrogate data tables

John Walkenbach has a book "Excel 2003 Formulas" with a 24-page chapter on Lookup functions and other database/list tricks.

Chip Pearson talks about lookups on his site as well.

Aaron Blood's site offers download files explaining lookup. See numbers 36, 37, and 44.

One of the zipped Workbooks (number 35), is a study in lookup methodology.

Daily Dose of Excel:
VLookup on Two Comumns

See all Topics


<Doug Klippert@ 3:01 AM

Comments: Post a Comment

  Sunday, November 23, 2008 – Permalink –

Array Formulas

Good orderly direction

An array is defined as "An orderly arrangement". It can be thought of as a collection of data packaged in a container. The individual items in the container can be selected by referring to their location; first, second, and so on.

  • Each argument within an array must have the same amount of rows and columns.
  • You must enter an array by pushing Ctrl+Shift+Enter.
  • You cannot add the {} (braces) that surround an array yourself, pushing Ctrl+Shift+Enter will do this for you.
  • You cannot use an array formula on an entire column.

"Have you ever sat in front of your monitor pulling your hair out trying to identify duplicate entries in a list? If so, you should learn about Microsoft Excel's array formulas. In fact, you can use array formulas to perform calculations that are otherwise impossible in Excel, and you can enhance the power of some of the program's existing functions."

Excel's Array Formulas
By Helen Bradley

Chip Pearson:
Introduction To Array Formulas

"Array Formulas are formulas that work with arrays, instead of individual numbers, as arguments to the functions that make up the formula"
The file "Array Functions" contains 27 Function Procedures and 2 Sub Procedures for manipulating arrays (and worksheet ranges).
Excel Tools

Bob Ulmas:
Using Array Formulas in Excel

Daily Dose of Excel:
Anatomy of an Array Formula
Sample Visual Basic macros for working with arrays

Limitations for working with arrays in Excel

When to use a SUM(IF()) array formula

See all Topics

Labels: ,

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

  Thursday, September 04, 2008 – Permalink –

Running Total in Comment

Circular solution

You can't have a worksheet formula that looks like this:


But you can do something similar if you use VBA and store the results in another location.

"In Microsoft Excel you can avoid circular references when you create a running total by storing the result in a non-calculating part of a worksheet. This article contains a sample Microsoft Visual Basic for Applications procedure that does this by storing a running total in a cell comment."

Microsoft Support:
Create a running total in a cell comment

The macro runs each time the value of a cell changes.
It adds the current value of the cell to the value of the cell comment. Then it stores the new total in the cell comment.

I'm sure someone can come up with other uses for this macro.

Also see:

Repetitive Calculation Features and Add-Ins

Daily Dose of Excel
Dick Kusleika
Circular References - The Good Kind

See all Topics

Labels: , , ,

<Doug Klippert@ 12:06 PM

Comments: Post a Comment

  Friday, August 01, 2008 – Permalink –

Rank Formatting

Highlight the best

Use Conditional formatting to highlight the rank of items in a list.
Select the range. Go to Format>Conditional Formatting....

Change the first box to "Formula Is".

Enter the following formulas. (Click Add to set the 2nd and 3rd Condition.)


Rank Conditional Formatting

(Notice the three way tie for third.)

Does a tie for first or third make sense?

If you want a unique rank, try a formula like:


This will rank the numbers in the order they appear in the list.

For a detailed discussion of ranking see:

Chip Pearson:
Ranking Data In Lists
(There is a workbook you can download)

See all Topics

Labels: ,

<Doug Klippert@ 2:42 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:


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:


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

  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

  Tuesday, April 08, 2008 – Permalink –

Date and Time Entry

Month Day, Day Month

QDE An Excel Date Entry Add-In
Ron de Bruin

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

Also see:

Chip Pearson:
Date and Time Entry
Time and Date

Date Arithmetic

See all Topics

Labels: , , ,

<Doug Klippert@ 6:18 AM

Comments: Post a Comment

  Tuesday, February 19, 2008 – Permalink –

Zero 0

Zero is nothing

If a zero isn't worth anything, why show it?

Here is a Microsoft tutorial about how to deal with zilch:

  • Display or hide all zero values on a worksheet

  • Use a number format to hide zero values in selected cells

  • Use a conditional format to hide zero values returned by a formula

  • Use a formula to display zeros as a blanks or dashes

  • Hide zero values in a PivotTable report

Hide Zeros

See all Topics

Labels: , ,

<Doug Klippert@ 9:22 AM

Comments: Post a Comment

  Sunday, December 23, 2007 – Permalink –

Date Arithmetic

The drunken cousin

Working with dates has a few twists.

Excel believes that time began on January 1, 1900.

Each day since then is counted so that September 1, 2003 in Excel-speak would be → 37,865.
9/1/03 7:33 A.M. is a decimal → 37865.31458333333

When you subtract one date from another, for instance 9/1/2003 (A1)minus 7/4/2001 (A2),
Excel displays the odd answer of → 2/27/1902.

Excel formats the result of a formula with the same format as the source cells,
Right-click the formula cell (=A1-A2).
Select Format Cells ..., and then choose a Number format with zero decimals.

The correct number of days → 789 will now be displayed.

Another way is to use the rarely documented DATEDIF function. Chip Pearson calls it "the drunken cousin of the Function family."



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


John Walenbach:
Extended Date Functions Add-In

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

MS Knowledge Base:
How To Use Dates and Times in Excel

See all Topics

Labels: , ,

<Doug Klippert@ 7:14 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, August 30, 2007 – Permalink –

Calculate Running Total

Using the OFFSET function

Adding up a running balance can be frustrating when new data is added or old transactions are removed.

"How to create a data list to manage transactions, add and delete rows from the list, and accurately calculate a running balance using the OFFSET function."

Cash flow using OFFSET
Calculate a running total

Using Auto Calculate for Running Totals

See all Topics

Labels: ,

<Doug Klippert@ 8:15 AM

Comments: Post a Comment

  Sunday, June 17, 2007 – Permalink –

Tips and Formulae

Functions and Macros

I'm always looking for Excel sites. A fresh perspective can make the view more clear.
While he does approach from a Mac angle, the Excel world welcomes those of all persuasions.

J.E. McGimpsey's XL Pages

Here are some of the tips:

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

See all Topics

Labels: ,

<Doug Klippert@ 8:07 AM

Comments: Post a Comment

  Wednesday, May 16, 2007 – Permalink –

Calendars - Perpetual

It's that year again

One Month and One Year perpetual calendars are available.

These Excel file calendars do not use macros and can be used in OpenOffice or other Excel compatible software.

  • Perpetual calendars with Week starting on Sunday

  • Perpetual calendars with Week starting on Monday

  • A simple "universal" one month calendar that will update for any month and any year (after 1900) just by changing a date cell.

  • A "universal" one year calendar that will show 12 months starting with a user defined Month and Year.
    Start dates can at the beginning of any year and any month.
Perpetual calendars

How to make calendars

See all Topics

Labels: ,

<Doug Klippert@ 6:13 AM

Comments: Post a Comment

  Tuesday, January 23, 2007 – Permalink –

Result is a Picture

If 4, show kumquat

Allen Wyatt has a cool procedure that will let you show a picture of an object on your spreadsheet depending on a value.

Maybe a snow suit when it's 29 or, say, a pair of bloomers when the computed temperature is 70.

The procedure does not use any VBA, just equations and bright thinking.

Display Images based on a Result

See all Topics


<Doug Klippert@ 5:32 AM

Comments: Post a Comment

  Monday, January 15, 2007 – Permalink –

Worksheet Name

Formula construction

There may come a time when you need to display the name of a worksheet.

This formula will do the job:

returns the path, the Workbook name and the Worksheet name. (C:\Documents\[April.xls]\Costs)
selects the text that starts at a certain point and goes on for a certain number of characters.

The formula, as written, looks at the full path and selects the first time a closing bracket (]) is found.

It then moves 1 character to the right and displays the results up to 31 characters.
(A worksheet name cannot be more that 31 characters long.

You could include a reference to that cell on other worksheets.

See all Topics


<Doug Klippert@ 8:15 AM

Comments: Post a Comment