Excel FAQs

Your name:  Required
Company:	 
Phone:	 Required
Email:	Required

        Data
        Charts
        Statistics

   Question Type

Your Question:

Please email any data or forms you are using to: lifestar@rmi.net

Macro error at cell A123

Your data may be in text format, not number format. Was your data imported from a database like Access?

Resolution:
To convert text data to numeric data in Excel

1. Enter a 1 in any blank cell
2. Click on the Cell and Select EDIT-COPY
3. Select your original data
4. Select EDIT-PASTE SPECIALThen check the MULTIPLY button
5. Rerun your chart with the new data.

For more information, check out our Lessons On Line.

In April 2004, the QI Macros were upgraded to correct your data automatically. If you have an older version of the QI Macros you might consider an upgrade to the latest version. See more information about QI Macros Upgrades.

How do I show stair step control limits?

See QI Macros Lesson 5.

How do I draw a target line on a control chart?

• Click on View-Toolbars-Drawing, then use the drawing toolbar to add a line to the chart or...
• Go to the intermediate worksheet created by the QI Macros and Insert a blank column after the LCL and populate it with your target value. Then click on the chart and choose Chart-Source Data-Series to add the new target data.

For more information, check out our Lessons On Line.

The March 2004 upgrade has examples of target charts under the Matrix and Diagram Selector.

How do I add data to a control chart?

The December 2002 upgrade made this easy.

1. Add new data to the intermediate worksheet created by the QI Macros,
2. Click on the chart and select "Add data" from the QI Macros pull-down menu.

1. Add new data to the worksheet created by the QI Macros,
2. Then click on the chart and choose Chart-Source Data-Series to increase the row numbers for the new target data in each range.

For more information, check out our Lessons On Line.

How do I flip or transpose my data?

1. Use Copy to copy your data

2. Switch to another spreadsheet or a blank area in your existing sheet and use Paste Special (Transpose)

3. Check the transpose button and you'll get the data transposed.

How do I turn off or change a macro short key?

Go to Tools, Macro, Macros - Select the macro you want to change and then click Options. A Macro Options window will open and allow you to edit the short key.

How do I get the right number of decimal points in Excel?

Excel, by default, formats every number as "General". To show the right number of decimal points:

1. select the cells with your data
2. Click on Format-Cells and choose Number
3. Set the number decimal points and click OK.
4. Use the QI Macros to draw your chart. The macros key off the cell formats to choose the precision.

Why are my data and dates spread out or compressed in my Excel Charts?

Excel tries to make the X axis show actual duration. Microsoft calls this "time-scale" format.

Resolution
1. Click on the Chart pulldown menu and
2. Select Chart Options-Axes-X Axis and c
3. Check "Category" instead of Time-Scale
4. Click OK.

Alternate: Check to make sure your dates have the correct years. When you enter 12/31 then 01/01, you'll get 12/31/04 and 01/01/04.

You can also convert your dates to text using Excel's TEXT formula shown at the top of this data sheet:

Then just copy your data (e.g., defects) over to the adjoining cells and run the chart using the new labels:

How do I average the occurrence of text values like "employee", "employee"?

You can't average text values. You must first count or summarize them. There are two Excel tools to help you do this:

COUNTIF

This is great for counting strings embedded in comment fields. In any cell, use the formula:
=countif(A:A,"=*employee*")
The "*" acts as a wild card character to match any cell in column A that contains "employee".

 

Pivot Table Report

Select Data-Pivot Table Report and let Excel's wizard guide you to summarize the text any way you want.

For more information about pivot tables, check out our Lessons On Line.

How do I change what prints on each chart?

Simply right click on the text, axis or data series and choose Format .... Then you can change font sizes, rotation of the text, colors, and so on.

You'll have to change the macros to lock in your changes.

1. Go to c:\program files\microsoft office\office(10)\xlstart and right click on the macro and uncheck "readonly".
2. Make a backup copy of the macro before you start.
3. Open Excel.
4. Click on Window-Unhide
5. Select the macro to be changed (e.g., histogram).
6. Everything in Column A is code; everything in Column B is a comment.
7. Simply find, change, or remove what prints (this is all near the end of each macro).
8. Select Window-Hide
9. Exit Excel and save the macro back into xlstart.
10. Change it back to "readonly".

Can you make new tools or customize the QI Macros for me?

Sure. Just give us a detailed description of what you want in the form below. We will evaluate what you're asking and give you a fixed price quote for the work.

We can save you lots of time.

My chart won't copy to Word or Powerpoint.

Most charts will easily copy and paste into Microsoft Word or Powerpoint. However, some users have had problems copying the Histogram into Powerpoint or Word 2007. If you have trouble follow these steps:

1. In Excel, select the chart and choose Edit/Copy
2. In Word or Powerpoint choose Edit then Paste Special.

• In Powerpoint - select one of the Picture Options instead of Excel Chart Object.
• In Word - select Excel Chart Object, jpg or try some of the other options if these don't work.