Book

Suggestions

Delivered by FeedBurner

Bloglines

1906
2006

OfficeZealot

Scobleizer

TechRepublic

SpyJournal

 Web http://www.klippert.com

Monday, March 01, 2010 – Permalink –

## Video Tutorials

### What you see

"MIStupid.com is The Online Knowledge Magazine that publishes information that everyone should know, wants to know, or forgot.
MIStupid.com was created in 2001 by Greg Renza with the desire to eradicate the world's stupidity."
A few of the lessons include:
• Concatenating Cells
• Conditional Formatting
• Subtotals
• Data Sorting
• Formulas and Named Cells
• Create Graphs and Charts
MIStupid.com

See all Topics

Labels:

<Doug Klippert@ 3:42 AM

Tuesday, February 02, 2010 – Permalink –

## Office Training

### Suggestions

TechRepublic lists a number of areas that you might explore when training is needed for a new Office version.

Here are a few:

• LINKS TO TIP SHEETS AND ARTICLES
• ONLINE TRAINING
• E-LEARNING
• WEBCASTS
• VIRTUAL TRAINING
• MULTILINGUAL SCREENTIPS AND TRANSLATIONS
• COMMAND REFERENCE GUIDES
• OFFICE ONLINE AT WORK

See all Topics

Labels:

<Doug Klippert@ 3:55 AM

Tuesday, December 22, 2009 – Permalink –

### Tie them together

Excel is a flatfile database, but you can do some Access kinds of relationships.

"A link is a formula that gets data from a cell in another workbook. When you open a workbook that contains links (a linking workbook), Microsoft Excel reads in the latest data from the source workbook or workbooks (updates the links).

Use links when you want to maintain information in a single workbook, but also use the information in other workbooks. For example, if your product prices change frequently, you might keep a master price list.
Other workbooks that use the price data in calculations, such as purchase orders, inventory valuations, or sales estimates, can create links to the price list workbook so calculations always use current prices. When prices change, you only have to enter the new prices in one place."

1. Open both workbooks.

2. In the source workbook, select the cells you want to link to and click the Copy button.

3. Switch to the destination workbook and click the upper left cell of the range where you want the links.

4. On the Edit menu, click Paste Special, and then click Paste Link.

See all Topics

Labels: ,

<Doug Klippert@ 3:10 AM

Tuesday, November 24, 2009 – Permalink –

## Formatting Overview

### Looking good

The judicious use of formatting can make data easier to understand as well as pleasant to see.
Scott Lowe put together a series of articles on how to format data in Excel.

The Articles are on TechRepublic.com

Anatomy of Excel formatting: Part 1
• Boldface, italicize and underline cell content
• Change the size and font of your text
• Apply a default Excel style (i.e. dollar, percent, etc) to cells
Anatomy of Excel formatting: Part 2
• Apply borders
• Resize rows
• Resize columns
Anatomy of Excel formatting: Part 3
• Text formatting
• Justify cell contents
• Word wrap text
Anatomy of Excel formatting: Part 4
• Automatically format cells based on their contents
• Change the margins for your printed page

See all Topics

Labels: , ,

<Doug Klippert@ 3:39 AM

Thursday, October 29, 2009 – Permalink –

## Hep Me

### Help topic locations

This from Ron de Bruin:

"Using the Help Context IDs

To make use of this file(s), locate the help topic of interest and make a note of its context ID.

Then, write a VBA subroutine that displays the topic.

You can attach the subroutine to a button, or provide some other way for the user to execute the sub.

For example, if you'd like to display the help topic that show the Date/Time Functions List,
you'll find that the context ID for that particular topic is 5199659.

The following VBA procedure displays that help topic."

``````
Sub ShowHelp()
Application.Help "XLMAIN" & Val(Application.Version) & ".CHM" ,5199659
End Sub``````

Help Context IDs for Excel

See all Topics

Labels: , , ,

<Doug Klippert@ 3:09 AM

Sunday, October 11, 2009 – Permalink –

## Update Excel on the Web

### Auto Republish

You can save an Excel file as a Web page and makes it easy to update data in a worksheet that has already been saved to the Web.

Here is how to save an Excel file as a Web page and set it up it for automatic updates:
1. Click Save As Web page from the File menu. (click the Office logo in 2007)

2. In the Save As dialog box, click the Publish button. (click Publish, Create Document Workspace in 2007)

3. Use the drop down arrow beside Choose to select what you want to publish.

4. In the File name field, enter a file name.

5. Place a check beside Auto Republish every time this worksheet is saved. This way the data will be updated each time you make changes to the worksheet.

7. Click Publish.
Lockergnome.com:
Save Excel as Web Page

DevX.com:
Four Ways to Use Excel on the Web

Penn State:
Interactive Excel on the Web

See all Topics

Labels: ,

<Doug Klippert@ 3:49 AM

Thursday, September 17, 2009 – Permalink –

## Lock the Barn

John Walkenbach has put together an FAQ on Workbook/Worksheet/VBA protection.

The Microsoft Knowledge Base article KB 293445 Has a list of references to protection information.

Microsoft Excel provides multiple layers of protection to allow you to control who can access and change your data:
• Worksheet protection: You can protect elements on a worksheet (for example, cells with formulas) from all user access, or you can grant individual users access to the ranges that you specify.
• Workbook-level protection: You can apply protection to workbook elements, and you can protect a workbook file from being viewed and edited. If a workbook is shared, you can protect it from being returned to exclusive use and prevent the change history from being deleted.
... articles address some of the more frequently asked questions about workbook and worksheet protection in Excel:
• How can I grant only a few users access to a range in my worksheet?
• Why are users not allowed to edit the ranges that I established permissions for?
• What new features are available in workbook protection?
• Why don't the permissions that I set on ranges in my worksheets carry over to Windows 98 computers?
Here is more information
Overview of security and protection in Excel

See all Topics

Labels: , ,

<Doug Klippert@ 3:44 AM

You might want to check out Mike Alexander's blog post about how easy it is to remove worksheet protection in Excel 2007.

http://datapigtechnologies.com/blog/index.php/hack-into-a-protected-excel-2007-sheet/

Friday, September 11, 2009 – Permalink –

## AutoShapes

### Drawing bar objects

Kim Hedrich has put together a series of basic articles on AutoShapes for TechTrax.

AutoShapesPart 1 - How to draw circles, ovals, squares and rectangles; also modifying fill and line colour

AutoShapes Part 2 - Fill Effects

AutoShapes Part 3 - Shadows and 3-D

AutoShapes - Text Inside a Shape

See all Topics

Labels: ,

<Doug Klippert@ 3:13 AM

Saturday, August 22, 2009 – Permalink –

## Self Help

### Get started in the right direction

The Office of Technology Services of Towson University, located in Towson, Md., provides Self-Help Training Documents for many applications.

They are available for many levels of knowledge. They’re clean, clear, and concise.
• Access

• Dreamweaver

• Excel

• FrontPage

• Microsoft Office Tools

• Outlook

• Outlook Web Access

• PowerPoint

• Publisher

• Visio

• Windows

• Word Art

• Word
Tech Docs

See all Topics

Labels:

<Doug Klippert@ 3:06 AM

Thursday, July 16, 2009 – Permalink –

## Access-Excel-XML-HTML

### Transfer data

XML makes data transferable between applications.
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

Labels: ,

<Doug Klippert@ 3:56 AM

Tuesday, June 30, 2009 – Permalink –

## Thirtieth Condition Formatting

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

Labels: , , ,

<Doug Klippert@ 3:09 AM

Thursday, April 09, 2009 – Permalink –

## 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 see:

John Walkenbach:
Sending Personalized Email from Excel

See all Topics

Labels: , , ,

<Doug Klippert@ 3:27 AM

Sunday, March 22, 2009 – Permalink –

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

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

Labels:

<Doug Klippert@ 3:35 AM

Friday, March 13, 2009 – Permalink –

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

Labels: ,

<Doug Klippert@ 3:49 AM

Wednesday, February 25, 2009 – Permalink –

## Simple obfuscation

The kid said,
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

(***-**-5555)

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

(****-****-****-5555)

Microsoft Office Online:
Display only the last four digits of identification numbers

See all Topics

Labels: , ,

<Doug Klippert@ 3:14 AM

Wednesday, January 14, 2009 – Permalink –

## Make it work and look good

Timothy Miller uses the nom de screen of "Jethro" (Moses' Father-in-Law).

His SpyJournal.biz site/blog gives some tips on how to present an Excel solution

Design and layout

One of the easiest ways to set up spreadsheets that calculate or generate results that need to be reported is to separate the function from the form. Just like a shiny exterior on a car hides the internal engine and wiring. I always create my reports and front end menus to look good and generate results and calculations in more functional sheets.
Hiding unnecessary sections

If you must have calculations and working sections visible, then hide the unnecessary bits. Hiding a row or column is only one way of doing this. Using the group function you can rollup whole rows of information, e.g. components that add to a subtotal or constants and variables such as exchange rates, interest rates, and other indexes.
Use of colour and graphics

I like to use the company logo or other graphic as a design element in my spreadsheet. Sometimes I do this by using the corporate colours, other times by using the graphic itself. If I have a spreadsheet with a lot of macro buttons, I may use command objects and use the logo as a picture on the button.
Removing excel components

There are a number of excel components that you can turn off. Menu screens and reports screens may not need horizontal or vertical scroll bars, sheet tabs or row and column headings. Using macro buttons to return to a menu can overcome the need for sheet tabs. Not displaying gridlines will give a clean uncluttered look to a layout, and then using borders as necessary can create emphasis in the right areas.

You'll find the complete text here:
Design Presentation Tips
Also see:

See all Topics

Labels: , ,

<Doug Klippert@ 3:02 AM

Sunday, December 14, 2008 – Permalink –

## Format Numbers

Here is an almost forgotten sample spreadsheet. It was constructed, for Microsoft, back in 1997 by Lori B. Turner, and is still relevant.

The sheets are protected. In order to see the cell formatting, you need to go to Tools>Protection to Unprotect Sheet . . . There is no password required

Formatting sample Workbook

Also:

OzGrid:
Custom Formats

If you need to format numbers that will be displayed connected to text, here is one way to do it:

1. In cell A1, enter the text "You still owe the sum of".
2. In cell A2, enter the number "5434".
3. In cell A3, enter the text "for invoice # 2232 from 6/15/2001".
4. In cell A4, enter the formula =A1&" "&TEXT(A2,"#,##0")&" "&A3.

You still owe the sum of 5,434 for invoice # 2232 from 6/15/2001

Excel Tips
Formatting concatenated numbers and text

Or:

Also:
Excel Format Tips

Also:
A very well produced examination of the subject by Jon Peltier:
Number Formats in Microsoft Excel

See all Topics

Labels: , ,

<Doug Klippert@ 3:14 AM

Sunday, November 23, 2008 – Permalink –

## Good orderly direction

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

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

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

Excel's Array Formulas

Chip Pearson:
Introduction To Array Formulas

"Array Formulas are formulas that work with arrays, instead of individual numbers, as arguments to the functions that make up the formula"

PacBell.net/beban/:
The file "Array Functions" contains 27 Function Procedures and 2 Sub Procedures for manipulating arrays (and worksheet ranges).
Excel Tools

Bob Ulmas:
Using Array Formulas in Excel

Daily Dose of Excel:
Anatomy of an Array Formula

Labels: ,

<Doug Klippert@ 3:33 AM

Saturday, November 15, 2008 – Permalink –

## Highlight entries

Conditional formatting can be set up by selecting the whole range, or for the first cell in the range and then copy down that conditional format. I find it is usually just as easy to select the whole range to start with. The formula will adjust itself.

In this example, cell B2 has a heading of Product Numbers.

Select cell B3 (or the entire targeted range) and from the menu.

Select Format > Conditional Formatting.

The Conditional Formatting dialog opens with the initial dropdown saying
"Cell Value Is".

Click the arrow next to this, and choose
"Formula Is".

After selecting "Formula Is", the dialog box changes appearance.
Instead of boxes for "Between x and y", there is now a single formula box.

You can type in any formula as long as that formula will evaluate to TRUE or FALSE.

The formula to type in the box is
=COUNTIF(B:B,B3)>1

This says, "look through the entire range of column B.

Count how many cells in that range are the same value as what is in B3."
(In the graphic, B7 is the Active cell.)

That same comparison will be made in every cell that contains the conditional formatting.

(If your data is in column E and you are setting the first conditional formatting up in E5, the formula would be =COUNTIF(E:E,E5)>1.)

Anytime a duplicate appears in the range, it will receive the special formatting.

In this example, any time a duplicate number appears anywhere in column B, even if it is not itself formatted, the selected range will reflect the duplicate.

=COUNT(B:B,B3)>2 would count entries that appear more than two times.
=COUNT(B:B,B3)=2 would count entries that appear twice.

If you want only a part of the column in the formula, it is easier to use absolute addresses, such as =COUNT(\$B\$3:\$B\$200,B3)>1

Also see:

Chip Pearson's discussion of duplicates:
Duplicate And Unique Items In Lists

and:

Contextures.com:
Conditional Formatting
(See Hide Duplicate Values)

See all Topics

Labels: , ,

<Doug Klippert@ 3:45 AM

Saturday, November 01, 2008 – Permalink –

## Scenario suggestions

"I wonder how our net profit would be affected if we could reduce our variable cost per unit by just a few cents. How much could we save if we found a lower interest rate? Wouldn't it be nice to be able to play around with some scenarios, do some "what-ifs" — without messing up your current data? It's easy with Microsoft Office Excel . You can set up "scenarios" to experiment with the data and compare the possibilities. Who knows? It could be a road map to better solutions for your business."

Excel "what-if" scenarios

American Institute of Certified Public Accountants (AICPA.org):
" To find out how to use what-if functions, follow along as this tutorial takes you step-by-step through several problems. Excel 2000 is used here to illustrate these concepts, but the process is similar in all spreadsheet programs."

Labels: , ,

<Doug Klippert@ 2:29 AM

Wednesday, October 15, 2008 – Permalink –

## Still good after all these years

Back in the day, Barnes Consulting was a major player with Office 97. They've gone on to other consulting areas, but you can still study what they called "On-Line Experiences".

These detail the benefits and outline the steps of many Excel features. They are worth the download.

They are a great resource for learning advanced features, training, and tips on a great consistent presentation that you can use to format your workbooks.

While they were written for Microsoft Excel versions 5.0 and 7.0, they are still viable in Excel 200x.

These Experiences were developed for Microsoft Corporation by Baarns Publishing.

Here are the titles of the illustrated workbooks that are free to download:

• Subtotals Experience
• Group and Outline Experience
• Range Name Experience
• Functions Experience
• PivotTable Experience
• Auto Filter Experience
• Auto Format Experience
• Power Chart Experience
• Scenario Experience
• Solver Experience

See all Topics

Labels: , , ,

<Doug Klippert@ 3:21 AM

Wednesday, October 01, 2008 – Permalink –

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

Example:

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

From:
"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

Also:
BetterSolutions.com:
What are Consolidated Worksheets ?

See all Topics

Labels: , , ,

<Doug Klippert@ 4:16 AM

Tuesday, September 30, 2008 – Permalink –

## Distribute to everyone

Many times an office will provide Excel for all users, but not want or need to also install Access on every desk.

Helen Feddema has laid out a method to use the data in an Access database to create Excel workbooks. These workbooks can then be e-mailed to employees to be used to record time spent on projects.

The code provided is above the entry level user, but understandable.

There is a downloadable file that includes the instructions and samples of the Access and Excel files.

Go to Access Archon Columns from Woody's Office Watch.

The pertinent file is down near the bottom of the page. Look for article 127 and download accarch127.zip.

See all Topics

Labels: ,

<Doug Klippert@ 3:17 AM

Sunday, September 28, 2008 – Permalink –

## Free instructions

Michael Alexander has produced a collection of about 60 online video demonstrations of some interesting Excel maneuvers.

Topics include:

Basic Excel Concepts

• Using "Paste Special"
• Using Text to Columns
• Tips on Filtering with "AutoFilter"

• Create a Basic Pivot Table
• Sorting in a Pivot Table
• Create a Drill Down Effect with a Pivot Table

• Create Dynamic Chart Labels
• Create a Thermometer Effect in your Column Charts

And More:
DataPig Technologies

See all Topics

Labels:

<Doug Klippert@ 5:01 AM

Wednesday, September 24, 2008 – Permalink –

## What are the chances

"Excel is the widely used statistical package, which serves as a tool to understand statistical concepts and computation to check your hand-worked calculation in solving your homework problems. The site provides an introduction to understand the basics of and working with the Excel. Redoing the illustrated numerical examples in this site will help improving your familiarity and as a result increase the effectiveness and efficiency of your process in statistics."

Dr. Hossein Arsham

The site is very clearly written. While some of the concepts are advanced, Dr. Arsham explains them in simple terms. It is a good introduction to the Analysis ToolPak.

Here are some of the subjects covered:

• Descriptive Statistics
• Normal Distribution
• Confidence Interval for the Mean
• Test of Hypothesis Concerning the Population Mean
• Difference Between Mean of Two Populations
• ANOVA: Analysis of Variances
• Goodness-of-Fit Test for Discrete Random Variables
• Test of Independence: Contingency Tables
• Test Hypothesis Concerning the Variance of Two Populations
• Linear Correlation and Regression Analysis
• Moving Average and Exponential Smoothing

The University of Baltimore:
Statistical Data Analysis

See all Topics

Labels:

<Doug Klippert@ 4:30 AM

Thursday, September 18, 2008 – Permalink –

## Graph-ology

You don't have to be spreadsheet challenged to read this book. Many people become quite adept at using Worksheet functions and even VBA, but have little experience with charting.

This book has some great cartoons, and, by page 361, the reader will be exposed to step by step instructions covering both simple charts and some quite sophisticated graphing.

"Excel Charts For Dummies will show readers how to professionally display data in presentation-quality charts. How to create attractive charts and why to use specific charts in particular circumstances. Lots of real-world examples with step-by-step tutorials. How to embed graphics and pictures into charts; then use them in impressive PowerPoint presentations or Microsoft Word documents. The book features a 16-page full-color insert of the best Excel charts 'works of art.'"

Ken Bluttman is also the author of Excel Formulas and Functions for Dummies, Access Hacks, and Developing Microsoft Office Solutions.

By Ken Bluttman
ISBN 0-7645-8473-1
Wiley Publishing, Inc. 2005

Technical editor Doug Klippert

See all Topics

Labels: ,

<Doug Klippert@ 6:40 AM

Thursday, September 04, 2008 – Permalink –

## Circular solution

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

=C3+C3

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

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

Microsoft Support:
Create a running total in a cell comment

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

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

Also see:

Decision models.com:

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

See all Topics

Labels: , , ,

<Doug Klippert@ 12:06 PM

Monday, July 14, 2008 – Permalink –

## VLOOKUP

"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.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup

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

=VLOOKUP(C1,A1:B12,2).

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:

=VLOOKUP (C1,A1:B12,COLUMN(B1)-COLUMN(A1)+1).

A more esthetically pleasing, or sophisticated, function might be:

COLUMNS(array)

This returns the number of columns in an array or reference.

The modified lookup function looks like this:

=VLOOKUP (C1,A1:B12,COLUMNS(A1:B1)).

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

Also:

Allan Wyatt's ExcelTips:
Using the Column Function

OzGrid:
Copy Rows
(Scroll down to about the middle of the page)

See all Topics

Labels: , , , ,

<Doug Klippert@ 5:25 AM

Tuesday, June 10, 2008 – Permalink –

## Outlook Contacts in Access

Automatically set up links to data outside of Access.
It still works in Access/Outlook '07.

Try this:

1. Choose File >Open from the menu bar.
(Office button>Open in 2007)
2. Under Files Of Type choose Outlook().
3. Locate your Outlook PST files.
4. Choose Contacts, or if you have set up separate files for different groups choose an appropriate one.
5. The wizard walks you through the process of creating an Access database with a linked Contact table.

The changes made in Access will be reflected in Outlook and vice versa.

If you want to create a new database that will link to other data that isn't in an Access format, you can do it quickly.

The classic way is to use the File>Get External Data >Link Tables method.

However you can simply choose File >Open from the menu bar.

Select the appropriate data format from the Files Of Type dropdown list
(such as Microsoft Excel (*.xls)).

Open the file and Access will automatically create an MDB file with the same name as the data source you selected and will set up links to the data.

From there you can develop forms, queries and reports.

See all Topics

See all Topics

Labels: ,

<Doug Klippert@ 7:54 AM

Thursday, May 29, 2008 – Permalink –

## Double click the handle

If you have a column of data, you may wish to insert a new formula on each row, number the lines, or add a date column.

To fill the column down to the bottom of the database, just double-click on the fill handle - the tiny square at the bottom right corner of the active cell.

The duplication continues as long as there are entries in the adjacent column.

If you wish to fill down a series, make at least two entries so that the interval is apparent. For instance if there is a column of data in A1:A400, enter the number "1" in B1, "2" in B2.
Select B1:B2. Double click on the fill handle and Excel will fill the series down to B400.

You can also select a longer series, such as the name of a supervisor and the team members.

Format the supervisors name differently, if you want.

Select the list and double click the fill handle. The list will be repeated down the page, as long as there is a corresponding entry in an adjacent column. The formatting will also be repeated.

Also:

Custom Lists

F. David McRitchie:
Fill Handle

Microsoft Office Online:
Fill data in worksheet cells

If you don't see the Fill handle:
Click Options on the Tools menu and click the Edit tab.

(Use the Office button in the upper left corner in 2007)
Select or clear the Allow cell drag and drop check box.

See all Topics

Labels: , ,

<Doug Klippert@ 6:20 AM

Saturday, May 17, 2008 – Permalink –

## Group sheets

A common use for Excel is to keep periodic statistics; sales by quarter, or phone calls per month.

It can be tedious to try to create worksheets for each month and include duplicate data such as client or salesperson's names.

Set up the workbook with as many worksheets that may be needed; perhaps one for each month and one for cumulative year-end totals.

Click the tab for the first month, hold down the SHIFT key and select the last worksheet in the series.

All the sheets are now chosen. You will see [Group] on the Title bar.

Enter any common information on the first sheet and it will be duplicated on all of the grouped sheets.

When you are done, Right-click a sheet tab and choose Ungroup Sheets on the context menu.

Non-contiguous sheets can be selected using the Ctrl key.

If the sheets are grouped, they will all be printed together.

Also:

John Walkenbach:
Making an exact copy of a range of formulas

Microsoft KB:
Run a macro on multiple sheets in Group mode

See all Topics

Labels: , , ,

<Doug Klippert@ 7:51 AM

Sunday, May 04, 2008 – Permalink –

## If it's Tuesday, it must be mauve

Conditional formatting is one of Excel's better features. It allows you to preset certain font styles, colors, and cell-background colors based on cell values.

This can be very useful for highlighting important information and values outside an accepted range or providing a visual cue to associate value ranges with color codes.

The best part is that conditional formatting is very easy to set up.

Just click the cells you'd like to format and select Format >Conditional Formatting. The Conditional Formatting dialog box lets you set up the conditions by which the formatting of the cell will occur.

You pick the operator (between, equal to, less than, etc.) and the value or range of values. Click Format to open the Format Cells dialog box, where you can select the colors and styles to be used.

Each cell can have several conditional formats. For example, you might say that if a certain cell's value is between 20 and 50, the text should be blue on a yellow background.

However, you can format that same cell to exhibit red, bolded text on a green background if it contains a value between 51 and 100.

Before 2007, you could use up to three conditions, but earlier versions of Excel can be tricked to use more if it should become necessary.

Oz Grid:
Excel VBA Macro Code to Get Around Excel's 3 Criteria Limit in Conditional Formatting

Graham Barrow and Ray Blake
Highlight the current or past month in Excel with conditional formatting

Chip Pearson:
Conditional Formatting

Labels: , , ,

<Doug Klippert@ 7:03 AM

Tuesday, April 08, 2008 – Permalink –

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

MathTools.net:
Time and Date

And:
Date Arithmetic

See all Topics

Labels: , , ,

<Doug Klippert@ 6:18 AM

Thursday, March 13, 2008 – Permalink –

## Shake hands with a worksheet

Anneliese Wirth has written an article for Office.Microsoft.com about how to get used to the new user interface in Excel 2007.

• Add a Get Started tab to the Ribbon

• Minimize the Ribbon, maximize your workspace

• Choose your own default font

• Restore missing worksheet tabs

• Freeze panes here, there, and everywhere

Surviving the switch to Excel 2007

See all Topics

Labels: ,

<Doug Klippert@ 7:20 AM

Saturday, March 01, 2008 – Permalink –

## More dimensions than Star trek

When a company accumulates a great deal of information, it becomes un-wieldy to work with just basic Excel or Access databases.

There is a database concept called on OLAP cube (On-Line Analytical Processing).

This multidimensional collection of data can be thought of as a 3-D pivot table viewed from flat land.

MSDN:
Just What Are Cubes Anyway?
(A Painless Introduction to OLAP Technology)

OLAPReort.com:
What is OLAP

Wikipedia:
OLAP

Wang.se (Wang Sweden) a Swedish software company:

Create an OLAP Cube

See all Topics

Labels: ,

<Doug Klippert@ 7:09 AM

Tuesday, January 29, 2008 – Permalink –

## Split the sheets (?)

Joe Chirilov presents a spreadsheet solution to a friendship breaker.

Recently a large group of friends and I went on a multi-city tour of Europe that lasted a couple weeks. There was a lot of planning that went into this trip and responsibilities for booking different legs of the trip were spread out across the group. How do you efficiently handle paying back multiple people while getting reimbursed for your costs at the same time?

See all Topics

Labels: , , ,

<Doug Klippert@ 6:53 AM

Monday, December 10, 2007 – Permalink –

## Chiropractics for Excel

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

• 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@ 4:50 AM

Monday, November 12, 2007 – Permalink –

## 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.
• Subtract numbers
• Multiply numbers
• Divide numbers

Use simple formulas to do the math

See all Topics

Labels: , ,

<Doug Klippert@ 8:00 AM

Thursday, November 01, 2007 – Permalink –

## 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. "

Labels: ,

<Doug Klippert@ 5:55 AM