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



  Friday, January 19, 2018 – Permalink –

Chiropractics for Excel

Knead and pound numbers

Chad Rothschiller, a program manager on the Excel team, discusses using formulas to 'clean up' data in Excel.
Excel is a great tool to use when you need to take data in one format, manipulate it into another format, and push the results along to another process, e.g. a database. In this context, Excel is a great landing pad or middle man, serving as a data transformation tool to move data from one system to another.

This example considers a sample data set and walks through the steps to clean up the data and perform various transformations on the data set to massage it into a more desirable format.

I'm sure you've been faced with at least one of theses problems:

  • Import the data and don't accidentally drop the leading zeros!

  • Formatting SSN

  • Inserting hyphens

  • Fixing up names

  • Lower case E-Mail Address

  • Format Home Phone

  • Inserting parenthesis & hyphens

  • Trim extra spaces from Address

  • Add City values to the new table

  • Make all State value upper case

  • Fill in Postal Code

  • Finalize the values
Manipulate and massage  


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:54 AM

Comments: Post a Comment


  Wednesday, January 10, 2018 – Permalink –

Spreadsheet Boo-Boos

Design suggestions


A few links to sites with advice on spreadsheet design and error detection.

European Spreadsheet Risks Interest Group:
Spreadsheet mistakes - news stories

Raymond R. Panko, PhD University of Hawaii

What We Know About Spreadsheet Errors


See all Topics

Labels: , , ,


<Doug Klippert@ 3:43 AM

Comments: Post a Comment


  Monday, January 08, 2018 – Permalink –

Web Data

Numbers from the ether


Excel has had a feature called web query. Here's an add-in that makes it a little easier.

"The Excel 2007 Web Data Add-In makes it easy to use a Web page as a data source in Excel.

The add-in plugs into Excel 2007 seamlessly, its entry point located on the Data Tab under the From Web option. The system extracts data by learning from a user’s selection of data they wish to capture into Excel. The more selections, the more the system is trained."



Web Data

Getting data from the Web in Excel

2013 Web Data YouTube


See all Topics

Labels: , , , , , ,


<Doug Klippert@ 3:57 AM

Comments: Post a Comment


  Wednesday, December 27, 2017 – Permalink –

All the Basics

All(most) all you need to know


Office.Microsoft.com 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@ 3:45 AM

Comments: Post a Comment


  Thursday, December 21, 2017 – Permalink –

Chart Art

Apple π


Look at how different media organizations have used graphics/graphs to illustrate the news.

Lee LeFever:
The Art of Explanation


While Edward Tufte has concerns about representing data accurately in charts, he does, use pictures to demonstrate relationships.
(See Edward Tufte)

If you would like to try your hand, here are some links that will help to spice up your condiment report.

PC Magazine:
Add Images to Excel Projects

MacWorld:
Excel Chart Art

Andy Pope:
Dividing a graphic into sections

Excel 2007-13 has a great graphic look for charts, but these links still work


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:34 AM

Comments: Post a Comment


  Tuesday, December 19, 2017 – 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
Also:
Basic Financial Calculations


See all Topics

Labels: , , , , , , ,


<Doug Klippert@ 3:23 AM

Comments: Post a Comment


  Wednesday, December 13, 2017 – Permalink –

2003-07-10-13 Compatibility

Exchange the future and the past


"Microsoft has added new file formats to Microsoft Office Word, Excel, and PowerPoint 2007+. To help ensure that you can exchange documents between Microsoft Office releases, Microsoft has developed a Compatibility Pack for the Office Word, Office Excel, and Office PowerPoint 2007+ File Formats"

Use earlier versions of Excel, PowerPoint, and Word to open and save files from 2007-13 Office programs

Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint


See all Topics

Labels: , , ,


<Doug Klippert@ 3:11 AM

Comments: Post a Comment


  Saturday, December 09, 2017 – Permalink –

Excel Links

Spokes of the web


There's a lot of information out there. The problem is how to find it.
Here is a site that contains links to Excel information arranged in topics:

  • Excel Add-ins

  • Excel Help

  • Excel Password Recovery

  • Excel Templates

  • Excel Tips & Tricks

  • Excel Tutorials

  • Excel VBA

  • Free Excel Add Ins

  • Spreadsheet Research
Excel Links


See all Topics

Labels: , , ,


<Doug Klippert@ 3:40 AM

Comments: Post a Comment


  Tuesday, November 28, 2017 – Permalink –

Data Tables

Up one side; down another



Data tables are a neat Excel feature that has not been emphasized.

If you are looking at, for instance, a home loan with a number of interest rates and different loan periods, a Data table can lay out the results with a minimum of fluff and formulas.

Dick Kusleika, Microsoft MVP, has a description on his excellent Daily Dose of Excel blog.
Data Table Basics

J K Pieterse:
Excel 2007, 2010, 2013 tables

Microsoft:
How to Use Microsoft Excel Data Tables to Analyze Information in a Database

How to Create and Use Two-Input Data Tables in Microsoft Excel

Overview of Data Tables - 2003 and 2007

TechRepublic.com:
Teach two-variable Excel data tables with real-life examples


See all Topics

Labels: , , ,


<Doug Klippert@ 3:01 AM

Comments: Post a Comment


  Tuesday, November 21, 2017 – Permalink –

Clippy's Revenge

Roll your own OA



The Office Assistant has taken a lot of hits, but it is missing in 2007, but still around in earlier versions.
If you want to play with it, see John Walkenbach's:

Create A Fake Clippy



Here is a creature you could use to replace Clippy:

Nerd Bird

There are other articles in the MS library about Agents and Assistants:

Animating Office Applications with Microsoft Agent


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:22 AM

Comments: Post a Comment


  Tuesday, October 31, 2017 – 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.PDF

Office.Microsoft.com:
Calculate a running total


See all Topics

Labels: , , , , , ,


<Doug Klippert@ 3:13 AM

Comments: Post a Comment


  Thursday, October 26, 2017 – Permalink –

Insert Line Breaks With Code

Label Captions


If you've ever needed to insert line breaks in a message box prompt, you most likely built a string that incorporated a line feed or carriage return character. Unfortunately, label objects aren't as forgiving when it comes to using these characters.

If you're setting a label's Caption property with code, you'll find that the special control characters are interpreted as squares, since they're otherwise un-displayable.
To successfully insert a line break in a label caption, you need to include both a line feed character and a carriage return character, entered consecutively.

To do so, you can use the Chr() function, such as:

Me.Label1.Caption = "Line 1" & _
Chr(13) & Chr(10) & "Line 2"

However, you can also simplify your code using an built-in constant:
Me.Label1.Caption = "Line 1" & vbCrLf & "Line 2"



See all Topics

Labels: , , , , , ,


<Doug Klippert@ 3:08 AM

Comments: Post a Comment


  Monday, October 23, 2017 – Permalink –

Office Art

2007+ choices


Office 2007+ uses OfficeArt to format text boxes, graphics and pictures.

It's available in Word, Excel , and PowerPoint, but it is most active in PowerPoint and Excel.

Here's a description:

Office PPT Art



See all Topics

Labels: , , ,


<Doug Klippert@ 3:49 AM

Comments: Post a Comment


  Thursday, October 12, 2017 – Permalink –

Specifications for Excel 2017-2016

More flexibility


Lots of limits have been changed with 2007. The size of a worksheet is now 1,048,576 rows by 16,384 columns. The number of undo levels has gone up to 100.

Here are some more changes:

  • Worksheet and workbook specifications and limits

  • Calculation specifications and limits

  • Charting specifications and limits

  • PivotTable and PivotChart report specifications and limits

  • Shared workbook specifications and limits

Excel 2007-2013 Specifications


See all Topics

Labels: ,


<Doug Klippert@ 3:26 AM

Comments: Post a Comment


  Sunday, October 08, 2017 – Permalink –

Undo Excel

Level talk


In Excel 2007+. the number of levels of the "undo stack" was increased from 16 levels to 100.

Setting AutoFilters, showing/hiding detail in PivotTables, and grouping/ungrouping in PivotTables are now reversible.

And the undo stack is not cleared when Excel saves, be it an AutoSave or a Save by the user.

If you think the number of undos should be changed, here's how:


  1. Close any programs that are running.

  2. Click Start, click Run, type regedit in the Open box, and then click OK.

  3. In Registry Editor, expand one of the following registry subkeys, as appropriate for the version of Excel that you are running:

    Microsoft Office Excel 2010
    HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options

    Microsoft Office Excel 2007 uses Office\12.0\
    Microsoft Office Excel 2003 uses Office\11.0\
    Microsoft Excel 2002 uses Office\10.0\
    Microsoft Excel 2000 uses Office\9.0\


  4. On the Edit menu, point to New, and then click DWORD Value. Select New Value #1, type UndoHistory, and then press ENTER.

  5. On the Edit menu, click Modify.

  6. In the Edit DWORD Value dialog box, click Decimal under Base. Type a value between 0 and 100 in the Value box, click OK, and then exit Registry Editor.

  7. Start Excel. Excel stores an undo history for the number of actions that you specified in step 6.


Modify the number of undo levels
 

If you want to clear the undo stack, just run a macro such as:


Sub ClearUndo()
Range("A1").Copy Range("A1")
End Sub

Allen Wyatt:
Clearing the Undo stack


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:14 AM

Comments: Post a Comment


  Tuesday, September 12, 2017 – Permalink –

Data Validation

Control input



Mark Rowlinson provides a discussion by Kid Van Ouytsel that does an excellent job explaining data validation. He has also constructs a sample workbook that you can download and play with.

Data Validation
"Data validation is a tool that can help you control the input/changes someone can make in a spreadsheet. It can help you or your users to make choices, guide them to make relevant input/changes, or restrict input to a specific type of data or structure. It can help you or your users to save time and to keep formulae working properly."

Also:

Contextures:
Excel -- Data Validation

Ozgrid:
Data Validation and Conditional Formatting

Microsoft Office Online:
Overview and samples of data validation


See all Topics

Labels: , , ,


<Doug Klippert@ 3:41 AM

Comments: Post a Comment


  Wednesday, September 06, 2017 – Permalink –

Move a Column

No Cut, No Paste


With a little practice you can quickly move columns or rows.

  1. Select the entire column or row you want to move
    The move will effect whatever is selected; cells, or columns, or rows

  2. Move your mouse pointer to the edge of the selection until it changes from a normal pointer cross to an arrow (four headed arrow in 2007+).
    (Put the pointer on the selected cells, not the Row or Column indicators.)

  3. Hold down the shift key and click the left mouse button and drag the column/row to the new position.

  4. Release the mouse button before releasing the shift key



See all Topics

Labels: , , , ,


<Doug Klippert@ 3:21 AM

Comments: Post a Comment


  Saturday, September 02, 2017 – Permalink –

Indent Code

Realign a bunch


Indenting blocks of VBA code, such as statements within loops or If...Then statements, makes reading a procedure much easier.

You probably indent a code statement using the [Tab] key, and outdent by using [Shift][Tab].

However, you may not be aware that the [Tab] and [Shift][Tab] techniques also work when multiple code lines are selected.

The Visual Basic Editor also provides Indent and Outdent buttons on the Edit toolbar that allow you to easily reposition blocks of code.


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:35 AM

Comments: Post a Comment


  Thursday, August 31, 2017 – 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@ 3:15 AM

Comments: Post a Comment


  Wednesday, August 23, 2017 – Permalink –

Location Indicator

Point to the spot


Here's a link to the code that produces conditional formatting on the fly to the cells in the current row and column.



Color banding location


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment


  Friday, August 18, 2017 – Permalink –

Zeros - Before and After

Nothing's a problem



"When you import data into Microsoft Access, trailing zeros may be lost. This will happen when you import data that is formatted to show these zeros, but where the zeros are not actually part of the data.
For example, in a Microsoft Excel workbook, you can format the number 1234 so that it will be displayed as 1234.000. When you import this workbook into a Microsoft Access table, the number will be displayed as 1234.
This article shows you how to preserve trailing zeros when you import data into Microsoft Access."


How to Preserve Trailing Zeros When Importing Data
Also:

Word — Decimal Point or Trailing Zeros Missing When You Merge Microsoft Access Database

Excel — Using a Custom Number Format to Display Leading Zeros


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment


  Wednesday, August 16, 2017 – Permalink –

Link to Word

Excel to Word connection


The Insert Hyperlink dialog will allow you to link to any file.

To link to a particular bookmark in a Word document, you can specify the bookmark by adding it yourself, adding a # (pound sign) plus the bookmark name to the path and file name.

C:\My Documents\MyDocument.doc#MyBookmark

To link to a page in a Word document, add a pound sign and the page number after the document path and file name:

C:\My Documents\MyDocument.doc#4

See Jon Peltier's hyperlink collection:
Hyperlinks


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:48 AM

Comments: Post a Comment


  Sunday, August 13, 2017 – Permalink –

Unkept Secrets

From Microsoft

"After supporting Microsoft Excel for years, technical Support Professionals have found that some of the most powerful and useful features and functions in Microsoft Excel remain undiscovered by you, our users.

For example, you may create a new macro to perform a calculation when an existing formula or function can perform the task. Or, you may create a new macro to perform a task when you could use an existing feature that performs the task."

Undiscovered Tips About Microsoft Excel for Windows
Here are just a few:
  • Secret #1: Joining Text Together

  • Secret #3: Excluding Duplicate Items in a List

  • Secret #7: Using a Data Form

  • Secret #11: Linking a Text Box to Data in a Cell

  • Secret #12: Linking a Picture to a Cell Range

  • Secret #17: Using OFFSET to Manipulate Data in Cells that are Inserted

  • Secret #21: Using INDEX and MATCH to Look up Data

  • Secret #25: Returning Every Nth Number

  • Secret #29: Using One Keystroke to Create a new Chart or Worksheet

  • Secret #30: Setting up Multiple Print Areas on the Same Worksheet



See all Topics

Labels: , , ,


<Doug Klippert@ 3:24 AM

Comments: Post a Comment


  Thursday, August 10, 2017 – Permalink –

New Excel Web Grabber

Web Toy


"The Excel 2007 Web Data Add-In makes it easy to use a Web page as a data source in Excel. The add-in plugs into Excel 2007 seamlessly, its entry point located on the Data Tab under the From Web option.

The system extracts data by learning from a user's selection of data they wish to capture into Excel. The more selections, the more the system is trained.
An example scenario: You wish to import and track data from MSN's weather page. Visit the site using the tool, enter Data Capture mode, and select a row or two of data from the table. Then click Select Similar, and the system will find similar data based on your previous selections.

You then can click Import and leverage Excel's rich data-editing capabilities, including the Refresh command, which will revisit the Web page and extract potentially new, updated data."

Web Data Add-In

(Webservices 2013)

From the Excel Blog team


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:57 AM

Comments: Post a Comment


  Friday, August 04, 2017 – 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 be at the beginning of any year and any month.

DotXLS.com: Perpetual calendars
 
Microsoft: How to make calendars


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:17 AM

Comments: Post a Comment


  Monday, July 31, 2017 – Permalink –

Drop Down Menu

Validation



You can create a dropdown list that gets its choices from entries located in another part of the spreadsheet.

  1. Type the entries in one column or row.

    You can name the list by selecting the entries and then clicking in the Name box on the Formula bar and entering a name, and then press Enter.

  2. Next, select the cell where you want the dropdown list.

  3. On the Menu bar, go to the Data menu, click Validation, and then click the Settings tab.
    In the Allow box, click List.

  4. Enter the name of the list or its location.

    Make sure the reference or name is preceded with an equal sign (=).

    Make sure the In-cell dropdown check box is selected.

    If your list is short, you can type the entries directly in the Source box, separated by commas.
Debra Dalgleish, Excel MVP has a complete discussion on her Contextures.com site. She covers resizing the dropdown box. The site also includes other information about data validation:
Excel — Data Validation —Tips  


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:25 AM

Comments: Post a Comment


  Wednesday, July 26, 2017 – Permalink –

Hide Dups

Format don't show



Duplicate entries can be formatted to "disappear", but still be available for computation.
  1. Select the range

  2. Goto Format>Conditional Formatting

  3. Select Formula Is

  4. Enter
    =A2=A1

  5. Click the Format button.

  6. Select a font color to match the cell background color.

  7. Click OK and OK
Dups can also stand out:
  1. Select the range

  2. Go to Format>Conditional Formatting

  3. Choose Formula Is

  4. Enter
    =COUNTIF($A$2:$A$100,A2)>1

  5. Click the Format button.

  6. Select a font or background color for highlighting.

  7. Click OK and OK
Hide Duplicate Values

Also:
Hide Records with Duplicate Cell Entries


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:17 AM

Comments: Post a Comment


  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