Unofficial Excel Stuff

Book

Suggestions

Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal

Sunday, January 30, 2011 – 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 excel
Labels: Formulas, General, Reference, Tips

<Doug Klippert@ 3:35 AM

Comments: Post a Comment

Saturday, January 22, 2011 – 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 excel
Labels: Customize, Formats, General, Reference, Tips, Tutorials

<Doug Klippert@ 3:28 AM

Comments: Post a Comment

Tuesday, January 18, 2011 – Permalink –

Slashed Zero

Oh!
ø

There is a discussion of the slashed zero at:
How to Insert a Slashed Zero (0 Overlaid with a /) - 211315

You can also download the Monaco font that has a slashed ø
(Monaco is an embeddable font)

Andale.ttf (Mono) has a dotted 0

Seagullscientific.com has a font called Crystal

Windows has a free font editor. Type eudcedit on the Start>Run line.
Vic Laurie has a description of the Private Character Editor- Eudcedit

You could also use the EQ field to create a strike through and assign it to an AutoCorrect entry.

{EQ \o (0,/)}

The easiest is, probably Alt+0216 or Alt+0248 It's a Latin "oh" with stroke, but it looks close.

The HTML character code is ø ø

See all Topics excel
Labels: Fonts, General, Tips

<Doug Klippert@ 3:36 AM

Comments: Post a Comment

Saturday, January 15, 2011 – 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 excel
Labels: General, Link, Reference, Tips

<Doug Klippert@ 3:27 AM

Comments: Post a Comment

Wednesday, January 12, 2011 – 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 excel
Labels: Customize, General, Shortcuts, Tips

<Doug Klippert@ 3:10 AM

Comments: Post a Comment

Saturday, January 08, 2011 – 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

From theExcel Blog team

See all Topics excel
Labels: Addins, General, Internet, Shortcuts

<Doug Klippert@ 3:46 AM

Comments: Post a Comment

Sunday, January 02, 2011 – 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 at the beginning of any year and any month.

DotXLS.com: Perpetual calendars

Microsoft: How to make calendars

See all Topics excel
Labels: General, Reference, Templates, Tips, Tutorials

<Doug Klippert@ 1:18 AM

Comments: Post a Comment