Unofficial Excel Stuff

65,539 Question

2009-07-16T05:39:00.000-07:00

The eleventh place error

Sure, you got a recall notice on your new car because the drink holder was the wrong size.

Big deal, Excel 2007 also has/had a problem with some calculations.

The result of the calculation is a number from 65534.99999999995 to 65535. The calculation is performed correctly. However, the result is incorrectly shown as 100000.

The result of the calculation is a number from 65535.99999999995 to 65536. The calculation is performed correctly. However, the result is incorrectly shown as 100001.

Excel 2007 hotfix package

Calculation Issue Update

See all Topics
excel

Access-Excel-XML-HTML

2009-07-16T03:56:00.000-07:00

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

Also:
Some nice photos and calendar layout:
Monthly calendar with photos

See all Topics
excel

Restore Defaults

2009-07-15T03:28:00.000-07:00

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. Start any Office 2003 program.
On Help menu, click Detect and Repair.
Click to select the Discard my customized settings and restore default settings check box, and then click Start.
Quit the application, and then click Ignore.
Click OK when you receive the following message:
Reset of setting to default succeed.

Microsoft Office Diagnostics in 2007 replaces Diagnose and Repair:

Howtogeek.com

See all Topics
excel

Plain Numbers

2009-07-12T03:02:00.000-07:00

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 problemsPeople 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.PlainFigures.comSee all Topicsexcel

Polyglot Polynomials

2009-07-08T03:03:00.000-07:00

ToolPak Translator

I was disappointed recently when I tried to look up Eric Desart's ToolPak translator. I found his site "niet beschikbaar."

I won't use the boy and wet thumb story, but Ron de Bruin did spring up to save the day and make the download available.

"Ever wanted an oversight of the Analysis-ToolPak Add-In functions, their descriptions, their arguments, their VBA and Procedure names, and all of this in your LOCAL language including translations versus the corresponding English names?

This utility extracts this data from your LOCAL MS Excel edition.

As such this table can be generated for ANY LANGUAGE EDITION of MS Excel, even when this language is not yet integrated in the utility."

Analysis ToolPak Translator

See all Topics
excel

Thirtieth Condition Formatting

2009-06-30T03:09:00.000-07:00

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 xlDynamic.com offer a free download that extends the conditions to 30!

Extended Conditional Formatter

Also see:
Conditional Formatting (including 2007)

See all Topics
excel

Clip Art at Home

2009-06-21T03:04:00.000-07:00

Install more

Do you remember all of the clip art that was available locally with Office XP?

When you have an Internet connection, you have access to the Office Online collection, but if you would like more clip art installed on your machine:

A small amount of sample clip art images was included The 2007 Office systems and Office 2003 and is part of the "local collection" that is searched when you do not have Internet access to the Microsoft Office Online Clip Art and Media Web site. Office 2003 no longer included a media content CD with additional clip art. However, the Microsoft Office XP Media Content CD can still be installed locally or on a network share.

The Office XP Media Content CD contains approximately 35,000 clips that are a subset of the clips that are available on the Microsoft Office Online Clip Art and Media Web site. The Office XP Media Content CD was included with Microsoft Office XP Professional, Microsoft Office XP Standard, and Microsoft Publisher 2002 Deluxe Edition.

To install the contents of the Office XP Media Content CD on a computer, follow these steps:
Exit all programs that are running

Insert the Office XP Media Content CD into the CD drive or into the DVD drive
(Hold down the SHIFT key to prevent the program from automatically starting. If Microsoft Windows Installer automatically starts, click Cancel)
Click Start, click Run, type the following command, and then click OK:
msiexec.exe /i CD_drive:\CAG.MSI ADDLOCAL=ALL /qb
(CD_drive is the letter of the drive that contains the Office XP Media Content CD)

Support.Microsoft.com
How to add clip art to Clip Organizer in a 2007 Office system and in Office 2003

See all Topics
excel

VBA Variable Problems

2009-06-17T03:36:00.000-07:00

Explicit protection

It's good practice to always use the Option Explicit statement in the beginning of your code modules to ensure that all variables are unambiguously declared in your procedures.

With this process in place, you'll receive a "Variable not defined" error if you try to execute code containing undeclared variables. Without this statement, it's possible to mistype variable names, which would be interpreted as new Variant type variables.

This could severely impact the results of your code, and you might not ever know it. If you do find a problem, tracking down where the error is can be a chore.

Although you can manually type the statement into your modules, changing a setting in Access can ensure that the statement is always added to new modules.

Open a module (start the VBA Editor)
Choose Tools>Options from the menu bar
On the Editor tab of the Options dialog box, select the Require Variable Declaration check box in the Code Settings panel
Finally, click OK

See all Topics
excel

Julian Dates

2009-06-14T03:43:00.000-07:00

Gregorian to/from Julian

Julian dates refer to the number of days from the first of the year and the number of days until the end of the year.

The year -45 has been called the "year of confusion," because in that year Julius Caesar inserted 90 days to bring the months of the Roman calendar back to their traditional place with respect to the seasons. This was Caesar's first step in replacing a calendar that had gone badly awry. Caesar created a solar calendar with twelve months of fixed lengths and a provision for an intercalary day to be added every fourth year. As a result, the average length of the Julian calendar year was 365.25 days.

The Gregorian (Pope Gregory XIII) calendar is based on a cycle of 400 years, which comprises 146,097 days. Since 146,097 is evenly divisible by 7. Dividing 146,097 by 400 yields an average length of 365.2425 days per calendar year, which is a close approximation to the length of the tropical year. The Gregorian calendar accumulates an error of one day in about 2500 years.

Calendars by L. E. Doggett

From Chip Pearson's site CPearson.com:

"Many applications (especially mainframe systems) store dates in the Julian format, which is a 5-digit number, consisting of a 2-digit year and a 3-digit day-of-year number. For example, 24-August-1999 is stored as 99236, since 24-August is the 236th day of the year. Excel does not support Julian dates directly, but you can use them with only a few fairly simple formulas.
Converting A Standard Date To A Julian Date
The formula below will convert a standard Excel date in A1 to a Julian Date.

=RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")

This formula takes the 2 right-most characters of the YEAR of the date in A1, and then appends the number of days between the date in A1 and the 0th day of that year. The TEXT function formats the day-of-year number as three digits, with leading zeros if necessary.

Converting A Julian Date To A Standard Date

The formula below will convert a Julian date to a standard Excel date.

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))

If the year digits of the Julian date are less than 30 (i.e., 00 to 29), the date is assumed to be a 2000 century year. If the year digits of the Julian date are greater than or equal to 30 (i.e., 30 to 99), the date is assumed to be a 1900 century year. This formula works by taking advantage of the fact that the DATE function can handle days beyond the "normal" days in a month. For example, DATE correctly computes 100-Jan-1999 to be 10-April-1999.

These Julian dates must have the leading zero or zeros for years between 2000 and 2009. For example the 123rd day of 2000 must be entered as 00123. Format the cell as TEXT before entering the data, or enter an apostrophe before the Julian date -- e.g., '00123. This will prevent Excel from treating the Julian date as a number and suppressing the leading zeros."

US Naval Observatory has this definition (and a calculator):

Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BCE (on the Julian calendar). Almost 2.5 million days have transpired since this date.

April 29, 2004 at 6:00 AM would be 2453854.75

See all Topics
excel

Count the Colors

2009-06-01T03:17:00.000-07:00

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
excel

Report Manager

2009-05-29T03:34:00.000-07:00

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 Support.Microsoft.com)

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

On the View menu, click Report Manager.
Click Add.
In the Report Name box, type a name for the report.
In the Sheet box, click the sheet you want to use for the first section of the report.
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.
Click Add to enter the view or scenario as a section in the Sections in this Report box.
Repeat step 5 and 6 until you've created all of the sections you want in the report.
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.

Edit a report for printing

On the View menu, click Report Manager.
In the Reports box, click the report you want to edit, and then click Edit.
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

On the View menu, click Report Manager.
In the Reports box, click the report you want to print.
Click Print.
In the Copies box, type the number of copies you want to print.

Also see: Template Wizard with Data Tracking

See all Topics
excel

Name that Range

2009-05-26T03:54:00.000-07:00

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
excel

UDF is not a Baby Alien

2009-05-19T03:41:00.000-07:00

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:
=CtoF(A1)

frice's Weblog

Here are some other links:

Vertex42.com:
User Defined Functions

Support.Microsoft.com:
Functions to Calculate Light Years

See all Topics
excel

Tabs with the Number of the Week

2009-05-15T03:58:00.000-07:00

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

ExcelTips.VitalNews.com:
Naming tabs for weeks

See all Topics
excel

Data from the Other Sheet

2009-05-08T03:14:00.000-07:00

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

Also: BetterSolutions.com
Cell References

See all Topics
excel

Who was that font I saw you with last night?

2009-05-06T03:14:00.000-07:00

That was no font, that was my typeface

You can find the Fonts supplied with some Microsoft products
Select a product name from the list to get a list of fonts supplied with that product.

Microsoft's Typography is an interesting site to poke around in.

Here are some books I use for reference material:

Words into Type

by Marjorie E. Skillin, Robert Malcolm Gay ISBN 0139642625

Stop Stealing Sheep & Find Out How Type Works

by Erik Spiekermann, E.M Ginger ISBN 0201703394

The Elements of Typographic Style

by Robert Bringhurst ISBN 0881791326

A font can be defined as a collection of characters with the same style and size. A typeface is the design of the characters regardless of size or style. The terms are used interchangeably today.

See all Topics
excel

Google from the Help Menu

2009-04-30T03:46:00.000-07:00

Search with Excel, Word, PowerPoint

Where better to search the Internet for support on an Excel, PowerPoint, or Word problem than through the Help menu?

Would you like to add Google to that menu?

Ron de Bruin at rondebruin.nl has developed free add-ins that does just that.

"Google Search 6.0/7.0 places a new sub-menu item under the Help menu of whatever program you call it from. When that item is selected, up pops a user-friendly interface. This allows a largely intuitive completion and execution of a Google Search.

On clicking the Search button a lot goes on behind the scenes.
Your default (i.e. your usual) web browser is loaded and, without further instructions,
It's off to the Google Advanced Search Page.
It then fills in an Advanced Query to your specifications
Executes that query.
Once results are found (or not found) you are shown those results just as if you had carried out all of the steps of the process."

See all Topicsexcel

Lookup, Down, and Sideways

2009-04-23T03:01:00.000-07:00

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) Lookup.zip, is a study in lookup methodology.

Daily Dose of Excel:
VLookup on Two Comumns

See all Topics
excel

SpWebSheet

2009-04-16T03:17:00.000-07:00

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

NumSum.com

See all Topics
excel

Date an Octothorpe

2009-04-15T03:36:00.000-07:00

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

Wikipedia:
Punctuation

See all Topics
excel

Excel-lent E-Mail

2009-04-09T03:27:00.000-07:00

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
excel

Where Have All the Bytes Gone?

2009-04-03T03:56:00.000-07:00

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

Copy the code and open Excel.
Press Alt+F11 and, if necessary, on the Visual Basic Editor menu, Insert>Module
Paste the code.
You could use Alt+Tab to bring the worksheet forward.
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 Topicsexcel

Linked Table Problems

2009-03-30T03:18:00.000-07:00

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

MORE INFORMATION
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.

Support.Microsft.com:
You cannot change data in linked tables that are to an Excel workbook

You might try reinstalling and then only installing SP1.

Also:

VB123.com:
Guacamole dipped - Access to Excel linked table gotcha

PCWorld.com:
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
excel

Intro to Excel

2009-03-22T03:35:00.000-07:00

English Excel

The UCL (University College London) site for the High Energy Physics Group of the Department of Physics & Astronomy, has an introduction to Excel e-book on this page. It's the material used in a 10 week course.

"This web page contains material for the computing and data analysis elements of the first-year PHYS1B40 Practical Skills course.

The main elements of the course are:

Analysis of data and treatment of uncertainties of measurement (Data Analysis) - discussed in lectures and illustrated by exercises using Excel

Use of spreadsheets (Excel) for

Analysis and presentation of experimental data
Mathematical modeling, simulation, analysis and problem solving
Introduction to computer programming by means of Visual Basic for Applications (VBA)

Here are some links to the topics covered topics:

Excel

Data Analysis

Visual Basic

See all Topics
excel

Web Queries

2009-03-13T03:49:00.000-07:00

Do You Question the Web?

This feature can make data acquisition a lot easier than Copy-Paste-Reformat-Try again.

"Generally, though, people tend to overlook the option of using the Web as a data source for Excel, be that source the Internet, an intranet, an extranet, or a Web Service. But they shouldn't. Web queries are an easy, yet remarkably flexible and predictable way of bringing data into Microsoft Excel from anywhere on the Web. You can point a Web query at any HTML document that resides on any Web server - or even on a file server, for that matter - and pull part or all of the contents back into your spreadsheet...When you start using Excel's Web queries, you will realize they are almost as limitless as the Web is.

Well Kept Secret

On the menu bar, go to Data>Import External Data. (In 2007, Data>Get Extrnal Data>From Web). Then, select Import Data to use an existing Web query or select New Web Query to build a new one.

Also see:
Vertex42.com:
Excel Web Query Secrets Revealed

MSDN.Microsoft.com/library
Integrate Far-Flung Data into Your Spreadsheets with the Help of Web Services

Updating Excel From the Web

And:
Web Queries and Dynamic Chart Data in Excel 2002

See all Topics
excel