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

• Concatenating Cells
• Conditional Formatting
• Subtotals
• Data Sorting
• Formulas and Named Cells
• Create Graphs and Charts

Labels: Tutorials

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

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

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

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

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

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

Drawing bar objects

Labels: Graphics, Tutorials

Get started in the right direction

• Access
  
  
• Adobe Acrobat
  
  
• Dreamweaver
  
  
• Excel
  
  
• FrontPage
  
  
• Microsoft Office Tools
  
  
• Outlook
  
  
• Outlook Web Access
  
  
• PowerPoint
  
  
• Publisher
  
  
• Visio
  
  
• Windows
  
  
• Word Art
  
  
• Word

Labels: Tutorials

Transfer data

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.

Labels: Tips, Tutorials

Three is not always enough

Labels: Formats, Tips, Tutorials, VBA

Outlook, Excel, and VBA

• 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 Download Addins for Excel e-mail information

Also see:

John Walkenbach:
Sending Personalized Email from Excel




See all Topics
excel

Labels: Addins, Tips, Tutorials, VBA

English Excel

"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

Use of spreadsheets (Excel) for

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)

Labels: Tutorials

Do You Question the Web?

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

Labels: Queries, Tutorials

Simple obfuscation

Labels: Functions, Tips, Tutorials

Make it work and look good

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.

Labels: Formats, Tips, Tutorials

It's your choice

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
excel

Labels: Formats, Tips, Tutorials

Good orderly direction

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

"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

Support.Microsoft.com:
Sample Visual Basic macros for working with arrays

Limitations for working with arrays in Excel

When to use a SUM(IF()) array formula



See all Topics
excel

Labels: Formulas, Tutorials

Highlight entries

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

Labels: Formats, Tips, Tutorials

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

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

Using a spreadsheet to do "what-if" analyses



See all Topics
excel

Labels: General, Tips, Tutorials

Still good after all these years

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

Labels: General, Templates, Tips, Tutorials

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.
   
10. Click Add.
    
11. Select both Top row and Left column "Use labels in" checkboxes.
    
12. Click OK.

Labels: Formulas, Functions, Tips, Tutorials

Distribute to everyone

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



See all Topics
excel

Labels: Tips, Tutorials

Free instructions

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

Labels: Tutorials

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:

Labels: Tutorials

Graph-ology

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

Labels: Charts, Tutorials

Circular solution

"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:
Repetitive Calculation Features and Add-Ins

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



See all Topics
excel

Labels: Formulas, Macros, Tutorials, VBA

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
excel

Labels: Formulas, Functions, Tips, Tutorials, VBA

Outlook Contacts in Access

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.

Labels: Lists, Tutorials

Double click the handle

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
excel

Labels: General, Shortcuts, Tutorials

Group sheets

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
excel

Labels: General, Tips, Tutorials, VBA

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

GR Business Process Solutions:
Graham Barrow and Ray Blake
Highlight the current or past month in Excel with conditional formatting

Chip Pearson:
Conditional Formatting

Also

Compatibility in Excel 2007

Conditional Format Functions in Excel 2007

Adding Customized Rules to Excel 2007

Demo:
Data takes shape with conditional formatting



See all Topics
excel

Labels: Customize, Formats, Tips, Tutorials

Month Day, Day Month

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

Labels: Formulas, Functions, Tips, Tutorials

Shake hands with a worksheet

• Add a Get Started tab to the Ribbon
  
  
• Take advantage of the Quick Access Toolbar
  
  
• Minimize the Ribbon, maximize your workspace
  
  
• Choose your own default font
  
  
• Restore missing worksheet tabs
  
  
• Freeze panes here, there, and everywhere

Labels: 2007, Tutorials

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
excel

Labels: General, Tutorials

Split the sheets (?)

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?

Split Costs

You can download the spreadsheet here:Split_Costs.zip




See all Topics
excel

Labels: General, Templates, Tips, Tutorials

HEADING

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.

• Import the data and don't accidentally drop the leading zeros!
  
  
• Formatting SSN
  
  
• Inserting hyphens
  
  
• Fixing up names
  
  
• Lower case E-Mail Address
  
  
• 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

Labels: Tips, Tutorials

All(most) all you need to know

• Add numbers
  
• Subtract numbers
  
• Multiply numbers
  
• Divide numbers

Labels: Formulas, General, Tutorials

Basic tutorial

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

Dream Car

Also:

Loan templates
Basic Financial Calculations



See all Topics
excel

Labels: Functions, Tutorials