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

  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

  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, September 30, 2009 – Permalink –

When 28 is 30

How long is a month?

When you use the banker's DAYS360 function to calculate the number of days between two dates, you can get an odd answer.

If you use the DAYS360 function with a start date of February 28 and with an end date of March 28, a value of 28 days is returned.
You expect a value of 30 days to be returned for every full month. (12*30=360)

This behavior may occur if you use the U.S. method, also known as the NASD method, with the DAYS360 function.

To work around this behavior, use the European method with the DAYS360 function. With the European method starting dates and ending dates that occur on the 31st of a month become equal to the 30th of the same month. To use the European method with the DAYS360 function, use the following syntax:

=DAYS360(cell number of start date,cell number of end date,TRUE)

Using FALSE or omitting the third parameter uses the U.S. method
An unexpected value with the DAYS360 function

See all Topics

Labels: , ,

<Doug Klippert@ 3:19 AM

Comments: Post a Comment

  Thursday, August 20, 2009 – Permalink –

Still More Functions

Never Enough

Laurent Longre Has put together an Excel add-in with 65 more functions.

Here are a few you might find useful:


converts a value from a base into another base.


date of Easter Sunday for a given year


returns the formula of a cell


returns the N highest numbers of a range or an array


converts a positive number into spelled-out text (supports 13 languages)


returns the unique items of a range or an array

MOREFUNC.XLL, 65 add-in worksheet functions

See all Topics

Labels: ,

<Doug Klippert@ 3:14 AM

Comments: Post a Comment

  Monday, June 01, 2009 – Permalink –

Count the Colors

I bid 3 Red

What if you would like to know the color name or to count or to sum cells by a fill color? There is no built-in function in Excel.

In this case you can make a User Defined Function (UDF).

Here is the sample UDF that you can use to analyze, count and sum the cells depending on their filled color.

These UDF function can be used in the same way as built-in functions that you can use in the worksheet.

  • AnalyzeColor Returns the color name, the color index or color index in RGB.
    Syntax: AnalyzeColor(color range, optional; "text" or "index" or "rgb". When it is omitted "text" is used.)

  • CountColor Counts the number of cells depending on their filled color.
    Syntax: CountColor(color range, target range)

  • SumColor Adds all the numbers in a range of cells depending on their filled color.
    Syntax: SumColor(color range, target range)
Sum and Count by fill color

Chip Pearson:
Working with Cell Colors

See all Topics

Labels: ,

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

  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, 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

  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

  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

  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

  Saturday, March 29, 2008 – Permalink –

Week Numbers

Who's counting?

For most purposes, weeks are numbered with Sunday considered the first day of the week. This works most of the time, but it can be a little confusing certain years.

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

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

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

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

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

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

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

Week Dates

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

Also this from
Weeknumbers using VBA in Microsoft Excel

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

In Access:
DatePart Function

If your work week is always Saturday through Friday then


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

The last number sets these parameters:

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

See all Topics

Labels: , ,

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

  Thursday, November 01, 2007 – Permalink –

Loan Payment

Basic tutorial

Microsoft provides a number of learning activities related to fundamental tasks.

Here's one that walks the student through a worksheet designed to calculate interest and total payment for a purchase, based on different loan terms.

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

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

Dream Car


Loan templates
Basic Financial Calculations

See all Topics

Labels: ,

<Doug Klippert@ 5:55 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, April 22, 2007 – Permalink –

Numbers to Words

Cardinal numbers

From the Microsoft Knowledgebase collection:

How to Convert a Numeric Value into English Words - 213360

This support article contains the VBA code needed to create a User Defined Function, You can, then, change 32.50 into "Thirty Two Dollars and Fifty Cents" by entering something like the following formula into a cell:



From John Walkenbach:

If you use Excel 2002+, you may have discovered the new BAHTTEXT worksheet function. This function converts a number to Thai text, and adds a suffix of "Baht".
This is the only built in function that translates numbers to words and then only in Thai.

For Access see:
Access- Numbers to Words

To create cardinal numbers in Word, see:
Word - Numbers to Words

See all Topics

Labels: ,

<Doug Klippert@ 6:36 AM

Comments: Post a Comment

  Monday, March 12, 2007 – Permalink –

Workbook Wiki

Info source

Wikipedia is an interesting tool because of the democratic nature of its definitions and information.

Mr. Excel has constructed an Excel Wiki:

"What is the Excel Wiki?

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

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


See all Topics

Labels: ,

<Doug Klippert@ 6:56 AM

Comments: Post a Comment

  Wednesday, January 31, 2007 – Permalink –

Statistical Functions

Definitions and list

Excel contains a slew of functions relating to statistical analysis.

That's a slew not a skew.


Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean.

Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values.

Negative skewness indicates a distribution with an asymmetric tail extending toward more negative values.

Statistical Functions

Troubleshooting - Statistical

See all Topics


<Doug Klippert@ 6:49 AM

do you know of a way to take time in hh:mm:ss and convert that number into a decimal equivilant. I was looking for a excel spreadsheet or template, but cannot find one any where. I do this alot at work, and it sucks!
Multiply the time by 24.
Be sure to format the answer as a decimal number.

Post a Comment