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



  Saturday, March 13, 2010 – Permalink –

This blog has moved


This blog is now located at http://unofficialexcelstuff.blogspot.com/.
You will be automatically redirected in 30 seconds, or you may click here.

For feed subscribers, please update your feed subscriptions to
http://unofficialexcelstuff.blogspot.com/feeds/posts/default.

<Doug Klippert@ 10:08 AM

Comments: Post a Comment


  Wednesday, March 03, 2010 – Permalink –

Mail Excel

VBA + sample

Excel Guru Ron de Bruin has put together the VBA code needed to send Excel via email.
He has also included the sample workbooks for those that are not VBA literate.

Also:
". . . a new add-in named RDBMail for Excel/Outlook 2007-2010
http://www.rondebruin.nl/mail/add-in.htm

The add-in create a new tab on the Ribbon named RDBMail with a lot of mail options.
You have the option to send as workbook or PDF for every mail option."



Code to send mail from Excel




See all Topics

Labels: ,


<Doug Klippert@ 3:43 AM

Comments: Post a Comment


  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

Comments: Post a Comment


  Saturday, February 20, 2010 – Permalink –

Selection Address

What's the count

". . . know what range is selected at any given time. You can look at the Name Box, but that only show the active cell."

Display number of Rows and Columns



See all Topics

Labels: , , , , , ,


<Doug Klippert@ 3:59 AM

Comments: Post a Comment


  Thursday, February 18, 2010 – Permalink –

Custom QAT

Access additions


Applications put most of the most-used commands on the Home tab's Ribbon, not everything is there. You may want to add Close, Close All, or Print commands, for example.

In the upper Left corner is the Quick Access Tool bar.

To update the QAT:
Click the down-pointing arrow to the right of the QAT.
Choose any common commands (New, Close, Print, etc.) by checking the option.




See all Topics

Labels:


<Doug Klippert@ 3:31 AM

Comments: Post a Comment


  Friday, February 12, 2010 – Permalink –

Custom Toolbars

You’re not restricted


You can create your own toolbars. Here's some code that helps:



Toolbars for Fun and Profit




See all Topics

Labels: , ,


<Doug Klippert@ 3:09 AM

Comments: Post a Comment


  Thursday, February 04, 2010 – Permalink –

Command Reference

2003-2007-2010

Those of you that are just now making the switch to the Ribbon world, will find this valuable.

ComputerWorld.com




See all Topics

Labels: ,


<Doug Klippert@ 3:14 AM

Comments: Post a Comment


  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
    "Instead of telling your users to go out to Microsoft.com and do a search, put hyperlinks to the printer-friendly version of tip sheets and articles on your company’s main portal page. Providing links to information you know they need will help you cover the training bases. And presenting the links on an internal web site they already use will show your users that it’s okay to go outside of their four firewalls to learn something new. Include your favorite hyperlink in your signature line so it goes out in every e-mail you send."
  • ONLINE TRAINING
  • E-LEARNING
  • WEBCASTS
  • VIRTUAL TRAINING
  • MULTILINGUAL SCREENTIPS AND TRANSLATIONS
  • COMMAND REFERENCE GUIDES
  • OFFICE ONLINE AT WORK
10 ways to train your users on Office 2007 for free




See all Topics

Labels:


<Doug Klippert@ 3:55 AM

Comments: Post a Comment


  Monday, February 01, 2010 – Permalink –

Tips for Word and Excel

Also some Windows hints


This site has useful information about:

  • Word
  • Word VBA
  • Excel
  • Excel VBA

    and
  • Windows
Tribbs.co.uk




See all Topics

Labels: , ,


<Doug Klippert@ 3:32 AM

Comments: Post a Comment


  Wednesday, January 27, 2010 – Permalink –

Color Scales

Conditional colors

Here is some information on Conditional color scales in Office 2010.

Microsoft Excel 2010 Blog 1

Microsoft Excel 2010 Blog 2

Microsoft Excel 2010 Blog 3



See all Topics

Labels: , , ,


<Doug Klippert@ 3:42 AM

Comments: Post a Comment


  Tuesday, January 26, 2010 – Permalink –

Where's the Template

Find and/change storage spots



Describes the different template categories and the locations of templates in 2007 Office programs. Also describes the registry settings that control where to find your custom templates.

Support.Microsoft.com




See all Topics

Labels: , ,


<Doug Klippert@ 3:48 AM

Comments: Post a Comment


  Wednesday, January 20, 2010 – Permalink –

Help ID's

VBA code

When you build a macro, you can call up information from the Excel Help file.
Ron DeBruin has the information needed through 2007.

Right clicking the Helpfile and choosing Properties will show the HP####### number in 2010.

Help Context IDs for Excel




See all Topics

Labels: , ,


<Doug Klippert@ 3:00 AM

Comments: Post a Comment


  Wednesday, January 13, 2010 – Permalink –

Text Files to Excel

A hard way to do an easy job

Text files can be imported or just pasted into Excel. If you want to learn more about SQL statements and non-code coding, look at this blog:


"Excel doesn't have an easy way to append multiple text files into one worksheet through the user interface. From time to time I hear customers asking how to do this in an easy way. Do you have to use Access or VB code to solve this problem? No. There's a way to do it in Excel by using a simple SQL statement in the connection string."


MSDN Excel Blog





See all Topics

Labels: , ,


<Doug Klippert@ 3:53 AM

Comments: Post a Comment


  Sunday, January 03, 2010 – Permalink –

Keyboard and Key Tips

Finger it out



2007 apps look different because of the ribbon, but the keyboard can still be used to speed up tasks.
Microsoft has an online course that may help

After completing this course you will be able to:
Accomplish tasks by using sequential shortcut keys, known as Key Tips, shown on the Ribbon.
Navigate around the Ribbon using the TAB key and arrow keys.
Accomplish tasks by using key combinations — keys you press at the same time - exactly as you've done in previous versions of Office.
Office.Microsoft.com/Training




See all Topics

Labels: ,


<Doug Klippert@ 3:54 AM

Comments: Post a Comment


  Saturday, January 02, 2010 – Permalink –

New Calendars

Another year

Here are free Excel calendars for downloading.

Twelve months on one sheet or one month per tab.



DotXLS.com





See all Topics

Labels: ,


<Doug Klippert@ 3:24 AM

Comments: Post a Comment


  Wednesday, December 30, 2009 – Permalink –

Chart Null Data Gaps

Fill in the spaces



When creating a chart, data can sometimes be missing. You have a choice of leaving the cells empty or making them zero.

By default, an empty cell will leave gaps in your graph. Zero entries will send a line graph down to the base line.

One way to force Excel to interpolate the data is to enter the function =NA() in the empty cells.

Another way is to go to Tools>Options and choose the Chart tab.

Pick how you want data plotted - with gaps, as zero, or interpolated.






Microsoft also has this suggestion:

Gaps between the dates
"If the datasheet data for the category axis contains date number formatting, Microsoft Graph automatically uses a special type of axis in your chart called a time-scale axis.
A time-scale axis shows a blank category for dates for which you have no data. If you do not want to see these gaps — for example, if you have data for 1-Jan, 15-Jan, 3-Feb, 12-Feb, and 2-Mar, and you want to plot the days next to each other - you can change the time-scale axis to a standard category axis.
On the Chart menu, click Chart Options, click the Axes tab, and then click Category under Category (X) axis."

Note, For 2007+, on the Design tab, click Select Data in the Data area, and then click Hidden and Empty Cells on the Select Data dialog box.




See all Topics

Labels: , , , ,


<Doug Klippert@ 3:18 AM

Comments: Post a Comment


  Tuesday, December 22, 2009 – Permalink –

Link Workbooks

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.
Create and Manage Links




See all Topics

Labels: ,


<Doug Klippert@ 3:10 AM

Comments: Post a Comment


  Monday, December 14, 2009 – Permalink –

Have a Geeky Christmas

And a Functional New Year


It is rather late in the season, but this is a gift that gives all year long:
This hilarious clock is the perfect accessory for any Excel power user. Each numeral has been replaced with a suitable Excel function that will evaluate to that numeral.

We've used one function from as many function categories as possible to really provide a broad range of functions.



For instance:
=FACT(3) - The FACT() function returns the Factorial of a number. The Factorial of 10 is 10x9x8x7x6x5x4x3x2x1. This function is great for statisticians calculating combinations and permutations. In our case, the Factorial of 3 is 3x2x1 or 6

MrExcel.com:

Excel Function Clock




See all Topics

Labels: ,


<Doug Klippert@ 3:20 AM

Comments: Post a Comment


  Saturday, December 12, 2009 – Permalink –

Display Row, Column Headings

User Function



Here's an odd little use of functions.

If you want to display the Row number on a spreadsheet, the formula
=Row()
works just fine.
You could then hide the Row and Column headings and format the Row numbers any way you want. If a Row is deleted the numbers will automatically update.

Column headings are a little harder. The formula =Column() will show the number of the Column, not the letter, i.e. "2" instead of "B".

The following formula extracts the Column letter:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")


To break it down:

=ADDRESS(row_num,column_num,abs_num)

This finds the address at Row number "1" and current Column number. The abs_num of "4 " says make the result a relative address.

The formula will produce a result such as "AA1".

SUBSTITUTE(text,old_text,new_text)

This function looks at the address, i.e. "AA1".
It replaces the Row number character ("1") with a null or empty value ("").
The formula will produce a result such as "AA".

Also see Daily Dose of Excel by Dick Kusleika.

Dick mused:'

"Sometime before the year 3,000, Microsoft will hopefully increase the number of columns in Excel (Hey, I can dream can't I). The challenge before you is to write a function that converts a column number to its letter equivalent assuming columns go to ZZZZ. That’s about 450,000 columns - maybe more than I need."


Of course Office 2007-10 has taken it up to 16,284 columns.




See all Topics

Labels: ,


<Doug Klippert@ 3:11 AM

Comments: Post a Comment


  Wednesday, December 09, 2009 – Permalink –

Sparklines

Quick graphic reinforcement


A graph or chart can give the reader a visual representation of a great deal of data. Concepts or results can be more easily grasped by a well formatted graphic.

Charts, usually, take up more space in a document than is absolutely required.

Edward Tufte has come up with the concept of Sparklines (Sparklines:Intense, Word-sized Graphics)
.
These are small graphs about the same height and width as common words. They are not out of place in the text of a document.

Sparklines give the reader a snapshot of the data that quickly supports the material being discussed.



See:
Bisantz Sparklines

The Sparkmaker can create Sparklines for Word, Excel, or PowerPoint. They can also be produced in HTML.




See all Topics

Labels: , , ,


<Doug Klippert@ 3:27 AM

Comments: Post a Comment


  Sunday, December 06, 2009 – Permalink –

Curves

and More


Gabriels Horn from the Curve Bank
Famous curves from Cal State at LA. All your favorites from Astroid to Witch of Agnesi.


Here is a collection of Functions relating to astronomy from Stargazing.net.

Can't tell who might be interested in the obliquity of the equator given date in days after J2000.0. See: Astro VBA

Other Curve stuff:

DelphiForFun.org: converting polar coordinates to Cartesian coordinates.

"Students of analytic geometry, (the kind that combines algebra and geometry), often work in one of two coordinate systems: Cartesian or Polar - and frequently must convert from one to the other.

The Cartesian system locates points on a plane by measuring the horizontal and vertical distances from an arbitrary origin to a point. These are usually denoted as a pair of values (X, Y).

The Polar system locates the point by measuring the straight line distance, usually denoted by R, from the origin to the point and the angle of an imaginary line from the origin to the point, θ, (Greek letter Theta), measured counterclockwise from the positive X axis."



See all Topics

Labels: , ,


<Doug Klippert@ 3:02 AM

Comments: Post a Comment


  Friday, December 04, 2009 – Permalink –

Password Background

Unencrypted

Alan Myrvold has written a background article on how Office handles passwords and what password strength means.

"Word, Excel, and PowerPoint have been able to password protect documents for several versions by setting the 'password to open'. What we felt could be improved was the ability to enforce password strength rules, similar to what may be required when logging into your computer at work."






See all Topics

Labels: ,


<Doug Klippert@ 3:55 AM

Comments: Post a Comment


  Sunday, November 29, 2009 – Permalink –

New Conditional Formatting

Much more capabilities


Pre-2007 Excel was limited to only 3 conditions. The new Office is more generous and versatile.
Here are some of the features:



Format all cells based on their values
Use this to create a data bar, 2-color or 3-color color scale, or icon set rule.
Format only cells that contain:
Use this to create the Excel 2003-style rules and more (format cells greater than, less than, greater than or equal to, less than or equal to, equal to, not equal to, between, not between). This is also the entry point to create rules of type: specific text, date occurring, blanks, non-blanks, errors, non-errors.
Format only top or bottom ranked values:
Use this to create top n, top n%, bottom n, bottom n% rule types.
Format only values that are above or below average:
Use this to create above average, below average, 1 or 2 or 3 standard deviation above, or 1 or 2 or 3 standard deviation below rule types.
Format only unique or duplicate values:
Use this to create rules that format unique or duplicate values.
Use a formula to determine which cells to format:
Use this to create Excel 2003-style rules where you can enter a formula to determine whether a format should be applied.


2007+ Conditional Formatting




See all Topics

Labels: ,


<Doug Klippert@ 3:48 AM

Comments: Post a Comment


  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
  • Use date and time formatting in your spreadsheet
  • Apply shading
Anatomy of Excel formatting: Part 2
  • Apply borders
  • Resize rows
  • Resize columns
Anatomy of Excel formatting: Part 3
  • Text formatting
  • Justify cell contents
  • Change the direction of the text in your spreadsheet
  • Word wrap text
Anatomy of Excel formatting: Part 4
  • Automatically format cells based on their contents
  • Change the margins for your printed page
  • Add a header and footer to your printer spreadsheet




See all Topics

Labels: , ,


<Doug Klippert@ 3:39 AM

Comments: Post a Comment


  Sunday, November 15, 2009 – Permalink –

Color News

A multidiscipline subject


Here is a study about how color effects a reader's choice of concentration.

It was intended for newspaper publishers, but the same knowledge can be used in Web design, PowerPoint, or any other reporting application. Word and Excel will also benefit.

Color, Contrast, and Dimension in News Design

ColorProject

The Poynter Institute is a school for journalists, future journalists, and teachers of journalists.
Poynter.org




See all Topics

Labels:


<Doug Klippert@ 3:08 AM

Comments: Post a Comment


  Thursday, November 05, 2009 – Permalink –

Change Code to Comments

Fast solution


When you're testing procedures, you can temporarily convert a block of VBA code to comments that will be ignored during a trial run.

Doing so manually by inserting an apostrophe before each line of code can be a real chore.

To simplify this task,
  1. Open any module in the Visual Basic Editor (VBE)
  2. Choose View >Toolbars>Edit from the menu bar to display the Edit toolbar.
  3. Select the lines of code that you want to turn into comments.
  4. 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.




See all Topics

Labels:


<Doug Klippert@ 3:42 AM

Comments: Post a Comment


  Wednesday, November 04, 2009 – Permalink –

Paste is Special

Versatile functions


If the data you brought into Excel comes through as text rather than numbers, Paste Special can fix it.

  1. Go to an empty cell.
  2. Copy it
  3. Select the "corrupted" data.
  4. Go to Edit>Paste Special and choose Add.
This works better than multiplying by one. Empty cells remain empty


What's So Special About "Paste Special"?

Excel Paste Special function

Pasting Using Paste Special




See all Topics

Labels:


<Doug Klippert@ 3:08 AM

Comments: Post a Comment


  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

Comments: Post a Comment


  Sunday, October 25, 2009 – Permalink –

Export Formatted Sheets

Access to Excel


Access provides an easy way to export data to Excel through the Office Links feature.

To use this feature, simply select a relevant database object and choose Tools>Office Links> Analyze It With Excel.

The worksheet Excel creates includes some minor formatting applied to the field headings that appear in row 1. Some formatting in your original Access database affects the worksheet cell formatting as well. For example, if you're exporting from a datasheet, gridline and font attributes are carried over to Excel. If you use the Office Links feature to export data behind a form, text box shading and font properties are applied.

The final result in Excel may not exactly match your Access data; however, you'll probably find that less work is required to get your Excel version of the data into an easily readable state.

In Office 2007-10 it's External Data>Excel






See all Topics

Labels:


<Doug Klippert@ 3:07 AM

Comments: Post a Comment


  Thursday, October 22, 2009 – Permalink –

Move Using Alt

One sheet to another



To move data from one worksheet to another, highlight the data.

Hold down the ALT key and move the mouse until the pointer arrow is on the border of the selection.

Drag the selection down to the destination worksheet tab.

When the arrow touches the tab, Excel switches to the desired worksheet.

Now drag the selection to the correct position. Let go of the mouse and then the ALT key.

To copy data hold down the CTRL+ALT keys and perform the steps above.

BTW the screen will not scroll while you hold the ALT key down.






See all Topics

Labels: ,


<Doug Klippert@ 3:56 AM

Comments: Post a Comment


  Saturday, October 17, 2009 – Permalink –

Shortcut Borders

Keyboard trick


Use Ctrl+1 to bring up Excels Formatting dialog box. Shift+B will take you to the Borders tab.

Now you can use the following key strokes (2002+):

ALT+T
Apply or remove the top border.
ALT+B
Apply or remove the bottom border.
ALT+L
Apply or remove the left border.
ALT+R
Apply or remove the right border.
ALT+D
Apply or remove the downward diagonal border.
ALT+U
Apply or remove the upward diagonal border.
ALT+H
Apply or remove the horizontal interior border.
ALT+V
Apply or remove the vertical interior border.
ALT+O
outlines the cells.
ALT+I
give interior (vertical and horizontal).
ALT+N
removes all borders.
ALT+C
brings up the color palette.



Shortcuts for Applying Borders

Ctrl+Shift+7 will outline a cell without having to display the Format dialog.




See all Topics

Labels: , ,


<Doug Klippert@ 3:28 AM

Comments: Post a Comment