Book

Suggestions


Enter your email address:

Delivered by FeedBurner


Home Page

Bloglines

1906
CelebrateStadium
2006


OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal












Subscribe here
Add to 

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

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com



  Wednesday, July 12, 2017 – Permalink –

Numbers to Words

Cardinal numbers



From the Microsoft Knowledgebase collection:

How to Convert a Numeric Value into English Words

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:

=SpellNumber(32.50)


Also:

For Access see:
Access- Numbers to Words


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


See all Topics

Labels: , , ,


<Doug Klippert@ 3:25 AM

Comments: Post a Comment


  Tuesday, July 04, 2017 – Permalink –

Select by Code

Programmatically pick cells


Microsoft has provided 22 ways to select cells/ranges by using Visual Basic procedures in Excel.

Here are a few of the subjects covered:

  • How to Select a Cell on the Active Worksheet

  • How to Select a Cell on Another Worksheet in the Same Workbook

  • How to Select a Range of Cells on the Active Worksheet
  • How to Select a Named Range on a Worksheet in a Different Workbook

  • How to Select a Cell Relative to the Active Cell

  • How to Select the Union of Two or More Specified Ranges

  • How to Select the Intersection of Two or More Specified Ranges

  • How to Select the Last Cell of a Column of Contiguous Data

  • How to Select the Blank Cell at Bottom of a Column of Contiguous Data

How to select cells/ranges by using Visual Basic procedures

Dick Kusleika has some comments on the coding:
Spreadsheets are the Devil


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:43 AM

Comments: Post a Comment


  Tuesday, June 27, 2017 – Permalink –

Getting 2010-13 Reference Guides

Where'd they hide that thing?


Wondering where your favorite Word 2003 commands are located in the new Word 2010-13 interface? Or just want to explore the rich, new design with a little guidance?

... rest the mouse pointer over a Word 2003 menu or button to learn its new location in Word 2010-13. To see an animation of the location of the command or button in Word 2010-13, just click it.


Command reference guides for:

Office 2010

Office 2013




See all Topics

Labels: ,


<Doug Klippert@ 3:53 AM

Comments: Post a Comment


  Sunday, June 25, 2017 – Permalink –

Comment Code

Edit toolbar



You'll many times want to change blocks of code to comments in VBA modules; temporarily convert a block of VBA code to comments so that it's ignored during a trial run. Inserting an apostrophe before each line of code is a bother. Office 2000+ simplifies this task by letting you convert a block of code to comments with a click of a button.

Open any module in the Visual Basic Editor (VBE), and then choose View>Toolbars and choose Edit from the menu bar to display the Edit toolbar.

Select the lines of code that you want to turn into comments. Then, click the Comment Block button on the Edit toolbar (it's the sixth button in from the RIGHT end of the toolbar).
Each line of the selected code is now preceded with an apostrophe.



To convert the comments back to executable code, select the appropriate lines and click the Uncomment Block button, which is immediately to the right of the Comment Block button.
This, of course, works in any application that uses the VBE.

It's been suggested that two or three apostrophes (sometimes called inverted commas) be placed around existing comments. When the Comment Block is used, the original comments will not be removed.


See all Topics

Labels: , , ,


<Doug Klippert@ 3:43 AM

Comments: Post a Comment


  Saturday, June 24, 2017 – Permalink –

Data Security Charts

Ways to look closer



Professional statisticians typically have powerful software at their disposal to perform advanced analyses and create slick graphs. But many professionals in the quality field don't enjoy that luxury.

Faced with a limited budget, they must be resourceful with the software they already have. Besides, not everyone needs the capability to perform nonlinear regression with custom loss functions for maximum likelihood!

Fortunately, many occasional data analysts already own a versatile software capable of providing most basic quality analyses -- Microsoft Excel.

  • Shewhart control charts

  • Pareto charts

  • Simple box-and-whisker plots


Excel for Data Analysis


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:28 AM

Comments: Post a Comment


  Tuesday, June 20, 2017 – Permalink –

Use Access or Excel

When to use one or the other


Use Access when you:
  • Require a relational database (multiple tables) to store your data.

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

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

  • Keep data that is mostly text.

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

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

  • Want to run complex queries.

  • Need many people working in the database and you want robust options that expose that data for updating.
Use Excel when you:
  • Require a flat or nonrelational view of your data (that is, you do not need a relational database with multiple tables).

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

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

Use Access or Excel to manage your data


See all Topics

Labels: , , ,


<Doug Klippert@ 3:25 AM

Comments: Post a Comment


  Monday, May 22, 2017 – Permalink –

Progress Brs

Don't go away



If your macro is going to take a bit of time to complete, it is good manners to inform your users what is going on. If screen updating is turned off, they may think their machine has frozen.

Andy Pope has some great charting examples and also demonstrates a number of
Progress meters

Here's a static example:

Progress display

John Walkenbach, also, has tips about how to create a Progress indicator

also see:
Chip Pearson


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:01 AM

Comments: Post a Comment


  Friday, May 19, 2017 – Permalink –

Open 2007-2010 in 200-2003

Not everyone is going to jump at once


How to open and to save Word 2007, Excel 2007, and PowerPoint 2007 files in earlier versions of Office
Microsoft Office Word, Microsoft Office Excel, and Microsoft Office PowerPoint versions 2000 through 2003 cannot natively open documents that are stored in the Office Open XML Formats in 2007 Microsoft Office programs.

You can install the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats to open or to save 2007 Office files.

Microsoft Office XP and 2003
Word, Excel, and PowerPoint

After you install the Compatibility Pack, you can use your existing version of Word, Excel, and PowerPoint to open, edit, and save the file formats that are new to Word 2007, Excel 2007, or PowerPoint 2007. For example:
  • You can open Word, Excel, or PowerPoint 2007 files by double-clicking them exactly as you do with your existing Word, Excel, and PowerPoint presentation(s).
  • You can save Word, Excel, or PowerPoint 2007 files by clicking the Save button in your version of Word, Excel, or PowerPoint.
Microsoft Office 2000
Word, Excel, and PowerPoint
Word 2000
  • After you install the Compatibility Pack, you can open, edit, and save the document file formats that are new to Word 2007 within Word 2000.
  • You can open files in the formats that are new to Word 2007 by double-clicking the files.
  • You can save files in the formats that are new to Word 2007 by clicking Save in Word 2000.
Excel 2000 and PowerPoint 2000
  • After you install the Compatibility Pack, you can open and save the file formats that are new to Excel 2007 and to PowerPoint 2007 from the Microsoft Windows operating system.
  • You can open files in the formats that are new to Excel 2007 and to PowerPoint 2007 by double-clicking the file on the desktop, in the My Documents folder, or in Microsoft Windows Explorer.
  • You can save files in the formats that are new to Excel 2007 and to PowerPoint 2007 by right-clicking an Excel 2000 file or a PowerPoint 2000 file and then clicking Save As.
Compatibility Pack Functions

Compatibility Pack Download



See all Topics

Labels: , , ,


<Doug Klippert@ 3:02 AM

Comments: Post a Comment


  Sunday, May 14, 2017 – Permalink –

1040 on a Spreadsheet

Free tax forms


All right, we spend our waking hours SUMming with Excel and then when one of the more stressful times of the year shows up, we turn it all over to TurboTax.

Here is a collection of IRS forms Glenn Reeves has done as interlocking spreadsheets.

The formulas are already set up; ready for data entry.
"No PASSWORD is needed!

If a cell is protected, it is because it contains a calculated value.

Many times, a manual override cell is provided if you need to override the calculated value.

After a while, you will figure out that the spreadsheet is completely functional without needing to unprotect it."



(Included on the site are forms back to 1996.)

http://www.excel1040.com


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:06 AM

Comments: Post a Comment


  Saturday, May 13, 2017 – Permalink –

Update the FUI Ribbon

Let the add-ins begin


It is said that the Office 2007 Graphical User Interface Ribbon cannot be as easily changed or modified like it has been in previous versions.

This may be partially true, but not all is lost.

Here is some information from the equine's mouth:


Learn how to customize the Ribbon user interface (UI) in the 2007+ Microsoft Office release. Also learn how new features in Microsoft Visual Studio 2005 Tools for the 2007+ Microsoft Office System support RAD development of Ribbon customization. (40 printed pages)

Customizing the Office Ribbon


Monsieurs MS also have a downloadable spreadsheets with the Control IDs. There are files for 2003 as well.

Lists of Control IDs


2007 Office System Add-In: Icons Gallery

2013 Icons Gallery



See all Topics

Labels: , , , ,


<Doug Klippert@ 3:19 AM

Comments: Post a Comment


  Monday, May 08, 2017 – Permalink –

Statistical Functions

Definitions and list


Excel contains a slew of functions relating to statistical analysis.

That's a slew not a skew.

SKEW(number1,number2,...)

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

Training - Statistical


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:51 AM

Comments: Post a Comment


  Wednesday, May 03, 2017 – Permalink –

Font Properties Plus

Everything you need to know


To embed a font in a document or slide show so it can be displayed on any other machine, the font must support that action. The standard Windows properties statement does not show all the needed information.

The bottom of this illustration shows the standard information shown when you right-click a font file, and choose properties. The two views at the top are what appear when the Microsoft Font properties extension is installed.


Font Properties

If you right click on a font file in Windows its basic properties are displayed. The Font properties extension adds several new property tabs to this properties dialog box. These include information relating to font origination and copyright, the type sizes to which hinting and smoothing are applied, and the code pages supported by extended character sets.

It also will tell you if the font can be embedded and/or edited in a document.



Protected
The font may not be embedded, copied, or modified. If you use a protected font in a document and if the document is opened on a computer that does not have the font installed on it, a font substitution occurs. Word substitutes the closest font available on the computer for the missing protected font.
Print/Preview
The font is embedded and temporarily loaded on the target computer. Documents that contain print/preview fonts must be opened read-only, and no edits are stored in the document. Embedding a font of this nature has the least impact on file size increase.
Editable
The font behaves just like the print/preview fonts, except that you may also apply the font to other text in the same document.
Installable
The font is installed on the target computer permanently when you open the document. This allows you to use the new fonts as if you installed the fonts directly into Windows yourself. This type of embedded font has the greatest impact on file size because the entire font or fonts are included with the document.


Versionand Features tabs
The Version tab includes version and date information. The Features tab describes the font in terms of number of glyphs, number of kerning pairs, the possible existence of a euro symbol and the presence of embedded bitmaps within the font.

Linkstab
If a font doesn't include a Web site URL, but does include a 'vendor ID code' a link will be provided to Microsoft's font vendor database.

Font properties extension, version 2.3
(32-bit only)


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:52 AM

Comments: Post a Comment


  Tuesday, May 02, 2017 – 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.

ExcelRibbon.Tips.net

Display Images based on a Result


See all Topics

Labels: , , , , , , ,


<Doug Klippert@ 3:55 AM

Comments: Post a Comment


  Sunday, April 23, 2017 – 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:
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)

=CELL("filename",$A$1) 
returns the path, the Workbook name and the Worksheet name. (C:\Documents\[April.xls]\Costs)
=MID(text,start_num,num_chars) 
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

Labels: , , , ,


<Doug Klippert@ 3:56 AM

Comments: Post a Comment


  Saturday, April 15, 2017 – Permalink –

Custom Lists

Your Way



At times, you will want to enter the same information repeatedly in a spreadsheet.
It could be employee names, products or State names.

Here is a way to produce the information when needed.

Make a list of the items.
Enter the data in, say, A1, B1, C1 etc.
Be sure to enter in the right order; maybe Supervisor and then team members in alphabetic order.

Select all of the cells.
Go to Tools>Options and choose the Custom lists tab.
Click Import.
OK your way out.

Now you can delete the entries on the spreadsheet.

Choose any cell and type one of the items that are on your Custom List.
Click and drag on the fill handle; the tiny square at the bottom right corner of the selected cell.
As you pull, Excel will duplicate your list.

(In Excel 2007+, go to the Office Logo. Choose Excel Options. Edit Custom Lists will be on the Popular page.)

Also see:

The First Shall Be Last
by Dick Kusleika

Sorting by Color
by Chip Pearson


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:19 AM

Comments: Post a Comment


  Monday, April 03, 2017 – Permalink –

Control Sort

Your choice

You can determine how Excel sorts your data.

ExcelTips


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:27 AM

Comments: Post a Comment


  Saturday, April 01, 2017 – Permalink –

SS Zip Codes

Excel to Access

Social Security Numbers and ZIP Codes can present special challenges when importing them into Access from Excel. This tip explains how you can prepare the data for the smoothest transition possible.
ExcelRibbon.Tips.net


See all Topics

Labels: , , ,


<Doug Klippert@ 3:43 AM

Comments: Post a Comment


  Monday, March 27, 2017 – Permalink –

Stacked Columns

On on another

Instructions how to put one column on top of another in a chart.



People.stfx.ca


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:15 AM

Comments: Post a Comment


  Sunday, March 19, 2017 – Permalink –

Dynamic Charts

Keep on changing

Instructions and examples of charts that change as the data changes.

Tushar-Mehta.com


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:12 AM

Comments: Post a Comment


  Friday, March 17, 2017 – Permalink –

Speed up your Macro

Wha's a macro?

"The purpose of this column is simply to provide information to anyone interested in how to make their work life more efficient through the use of macros. When your average computer user finds out that macros are all about little bits of code, the fear in their hearts becomes palpable, all the way from here. And so today I want to ease those fears and invite those of you who've been curious about macros to take the plunge and finally learn about them."

Dummies.com


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment


  Monday, March 13, 2017 – Permalink –

Why Pie Charts

Why Pie?

"The popularity of pie charts is a bit of a mystery. The data visualization goals of a pie chart can easily and more effectively be shown with a bar chart or even a simple table. In the worst of cases, a whole pie chart is used to show a single number."

JuiceAnalytics.com


See all Topics

Labels: , , ,


<Doug Klippert@ 3:12 AM

Comments: Post a Comment


  Sunday, March 05, 2017 – Permalink –

Conditional Axes Labels

Custom

Here is how to change axis formatting based on values.




AndyPope.com


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:49 AM

Comments: Post a Comment


  Monday, February 27, 2017 – Permalink –

Positive Negative Colors

Vary

Change the color when going from positive to negative.


AndyPope.info


See all Topics

Labels: , , , , , ,


<Doug Klippert@ 3:38 AM

Comments: Post a Comment


  Sunday, February 19, 2017 – Permalink –

Formatted Input Boxes

Use text boxes


"You can use a Data Validation Input Message to display a message when a cell is selected. However, the font can't be changed, nor can message box size be controlled.

To overcome these limitations, you can create a text box to display the message, and use programming to make it appear if cells that contain a data validation Input Message are selected."


Contextures.com


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:24 AM

Comments: Post a Comment


  Wednesday, February 08, 2017 – Permalink –

Excel to PPT

And Access

This example shows how to place the VBA code in PowerPoint, retrieve data from Excel and or Access and display it in a PowerPoint table.

PPTFAQ.com


See all Topics

Labels: , , ,


<Doug Klippert@ 3:58 AM

Comments: Post a Comment


  Tuesday, January 31, 2017 – Permalink –

Code Copy

Excel to Word

You can select, copy, and paste to put Excel data into a Word document, but this piece of VBA automates to process.


VBAExpress.com


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:27 AM

Comments: Post a Comment


  Tuesday, January 24, 2017 – Permalink –

Custom Functions

Write your own

Excel allows you to create your own functions. If =SUM() is not enough for you, look at this link:

Custom Functions - User Defined Functions


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:10 AM

Comments: Post a Comment


  Tuesday, January 10, 2017 – Permalink –

Mail a Range

Combine Excel and Outlook

"The subs will send a newly created workbook with just the visible cells in the selection or range.
The cells will be PasteSpecial as values in the workbook you send. 
It saves the workbook before mailing it with a date/time stamp. 
After the file is sent, the workbook will be deleted from your hard disk."
RondeBruin.nl


See all Topics

Labels: , , ,


<Doug Klippert@ 3:35 AM

Comments: Post a Comment


  Tuesday, January 03, 2017 – Permalink –

Spreadsheets in Education

Samples and papers


"SIE is an electronic journal devoted to the publication of quality refereed articles concerned with studies of the role that spreadsheets can play in education. Our aim is to provide a focus for advances in our understanding of the role that spreadsheets can play in constructivist educational contexts.
Spreadsheets in Education (eJSiE) is a free facility for authors to publish suitable, peer reviewed articles and for anyone to view and download articles."

eSIE


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:24 AM

Comments: Post a Comment


  Tuesday, December 27, 2016 – Permalink –

Excel to Outlook

Concatenate Excel Outlook

A common task is to bring a contact list into Outlook from Excel. Usually Excel will have names separated into columns. Other information, such as addresses may also be divided.
Here’s how to format the Excel file for a seamless import.

Howto-Outlook.com


See all Topics

Labels: , , ,


<Doug Klippert@ 3:54 AM

Comments: Post a Comment


  Saturday, December 24, 2016 – Permalink –

Windows Split

Easy trick

Here's how to split the open window and/or create new windows.

SaraFord


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:38 AM

Comments: Post a Comment