2003-2007-2010

Labels: General, Ribbon

Suggestions

• 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

Labels: Tutorials

Also some Windows hints

• Word
• Word VBA
• Excel
• Excel VBA
  
  and
• Windows

Labels: General, Tips, VBA

Conditional colors

Labels: 2010, Formats, General, Tips

Find and/change storage spots

Labels: Customize, General, Templates

VBA code

Labels: Reference, Tips, VBA

A hard way to do an easy job

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

Labels: Formulas, Functions, Tips

Finger it out

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.

Labels: Shortcuts, Tips

Another year

Labels: General, Templates

Fill in the spaces

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

Labels: Charts, Customize, Formats, Formulas, Tips

Tie them together

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

Labels: Link, Tutorials

And a Functional New Year

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.

=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

Labels: Gadgets, General

User Function

=Row()

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

=ADDRESS(row_num,column_num,abs_num)

SUBSTITUTE(text,old_text,new_text)

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

Labels: Functions, Tips

Quick graphic reinforcement

Labels: Charts, Fonts, Graphics, Tips

and More

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

Labels: Formulas, Graphics, Link

Unencrypted

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

Labels: General, Tips

Much more capabilities

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.

Labels: Formats, Tips

Looking good

• 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

• Apply borders
  
• Resize rows
  
• Resize columns

• Text formatting
  
• Justify cell contents
  
• Change the direction of the text in your spreadsheet
  
• Word wrap text

• Automatically format cells based on their contents
  
• Change the margins for your printed page
  
• Add a header and footer to your printer spreadsheet

Labels: Formats, Tips, Tutorials

A multidiscipline subject

Labels: Tips

Fast solution

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

Labels: VBA

Versatile functions

1. Go to an empty cell.
   
2. Copy it
   
3. Select the "corrupted" data.
   
4. Go to Edit>Paste Special and choose Add.

Labels: Tips

Help topic locations

"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

Labels: Reference, Tips, Tutorials, VBA

Access to Excel

Labels: Tips

One sheet to another

Labels: General, Shortcuts

Keyboard trick

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.

Labels: Formats, Shortcuts, Tips

Auto Republish

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.
   
   
6. Select Open published page in Web browser to view the data in your Web browser after you click Publish.
   
   
7. Click Publish.

Labels: Internet, Tutorials

Excel tables

Labels: Tips

How long is a month?

=DAYS360(cell number of start date,cell number of end date,TRUE)

Labels: Functions, Reference, Troubleshooting

Close the spreadsheet and Excel

Labels: Customize, Tips

Protect your work

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?

Labels: General, Tips, Tutorials

Declare each one

Labels: VBA