Unofficial Excel Stuff

Book

Suggestions

Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal

Friday, January 09, 2009 – Permalink –

Graph Data and Shapes

Statistical collection

Betty C Jung has put together a serious collection of tutorials, links, and data sources.

Here are some of the topics:

Charting Data

Data Presentation

Cumulative Frequencies

Fishbone Diagram

Flowcharts

Frequency Distributions

Gantt; PERT Charts

Graphing With Excel

Analyzing and Plotting Data with Excel

Organizational Charts/Mapping

Social Network Analysis

Graphing & Presenting Data

See all Topics
excel
Labels: Charts, General, Lists

<Doug Klippert@ 3:51 AM

Comments: Post a Comment

Thursday, August 21, 2008 – Permalink –

Choose List Criteria

Set values

Somewhere in the workbook, create three named ranges:

Type (elements of this named range should be Sedans, SUVs)
Sedans (elements of this named range should be Toyota Camry, Nissan Altima, Mazda 6)
SUVs (elements of this named range should be Toyota RAV4, Ford Escape, Jeep Liberty)

Select A1:A10, go to Data>Validation select List. Under source, type:
=Type

Select B1:B10, go to Data>Validation select List. Under source, type:
=INDIRECT(A1)

Choose a vehicle type from the selection that appears in the A column, and the choices in B will be limited to the entries in the appropriate list.

To create a named range, select the list:

Hit Ctrl+F3. Enter the name and hit OK (or Enter),

or
Type the name in the Name Box on the left side of the Formula bar and hit the Enter key,

or
Go to Insert>Name. If the list selected includes a label/title, choose Create, otherwise choose Define, type the name and hit Enter).

Also
Contextures.com:
Data Validation -Create Dependent Lists

See all Topics
excel
Labels: Lists

<Doug Klippert@ 2:01 AM

Comments: Post a Comment

Tuesday, June 10, 2008 – Permalink –

Auto Link

Outlook Contacts in Access

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

Try this:

Choose File >Open from the menu bar.
(Office button>Open in 2007)
Under Files Of Type choose Outlook().
Locate your Outlook PST files.
Choose Contacts, or if you have set up separate files for different groups choose an appropriate one.
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
excel
Labels: Lists, Tutorials

<Doug Klippert@ 7:54 AM

Comments: Post a Comment

Saturday, June 07, 2008 – Permalink –

Good OLAP

More data

From Builders.com.com:
An introduction to the benefits of online analytical processing (OLAP)
"Every day we create reams of data in customer relationship management applications, order entry applications, and warehouse management systems. We're drowning in a sea of data. However, even with all that data we don't have a large amount of information. We have the ones and zeros of the transactions, but we don't have the answers we need to simple questions like:

"Why was March better than February?"
"Where is the sales force having the most success?"
"In what conditions does the sales team struggle with making sales?"

The article also has links to:

Oracle 9i makes data warehousing easy to implement
Seven highly effective steps to a smooth data warehouse implementation
Business intelligence is just a few steps away for SAP R/3 users
Resources for designing, planning, and implementing a data warehouse strategy
Making the operational case for data warehousing
TechRepublic Tutorial: Data warehousing defined

Also see:
OLAP Cube

Data Warehousing and OLAP
A Research-Oriented Bibliography

FAQ Excel 2007 – OLAP

Microsoft:

OLAP

See all Topics
excel
Labels: General, Lists, Tips

<Doug Klippert@ 6:41 AM

Comments: Post a Comment

Thursday, October 18, 2007 – Permalink –

Links to Excel

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
excel
Labels: General, Link, Lists

<Doug Klippert@ 8:03 AM

Comments: Post a Comment

Sunday, September 30, 2007 – 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 tables

Microsoft:
How to Use Microsoft Excel Data Tables to Analyze Information in a Database
How to Create and Use One-Input Data Tables in Microsoft Excel
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
excel
Labels: Lists, Tips

<Doug Klippert@ 7:44 AM

Comments: Post a Comment

Saturday, May 12, 2007 – Permalink –

Drop Down Menu

Validation

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

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.

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

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

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
excel
Labels: Lists

<Doug Klippert@ 8:22 AM

Comments: Post a Comment

Sunday, January 07, 2007 – 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.
ClickImport.
OKyour way out.

Now you can delete the entries on the spreadsheet.

Choose any cell and type one of the items that is 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: Lists

<Doug Klippert@ 5:11 AM

Comments: Post a Comment