Automatic Refresh in Excel

Editing the registry to enable automatic refresh in Excel so that Excel will not prompt you every time you open a file containing a query or PivotTable that has been configured to refresh automatically.

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Modify the registry at your own risk.

• Exit your version of Excel.
  
• Click Start, click Run, type regedit, and then click OK.
  
• Select the appropriate registry key, depending on the version of the product that you are using:
  
  

  Excel 2003 - HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options.
  

  Excel 2002 - HKEY_CURRENT_USER \Software\Microsoft\Office\10.0\Excel\Options.
  

  Excel 2000 - HKEY_CURRENT_USER \Software\Microsoft\Office\9.0\Excel\Options.
  

• After you select the key that is specified in step 3, point to New on the Edit menu, and then click DWORD value.
  
• Type QuerySecurity, and then press ENTER.
  
• Right-click QuerySecurity, and then click Modify.
  
• In the Value data box, enter 2, and then click OK. The allowed values are listed here.
  
  

  0 = Excel prompts you every time that you open a file containing a query or PivotTable that has been configured to refresh automatically. This is the default behavior.
  

  1 = Excel will not prompt you when you open a file containing a query or PivotTable that has been configured to refresh automatically. Excel will not refresh the query or PivotTable automatically.
  

  2 = Excel will not prompt you when you open a file containing a query or PivotTable that has been configured to refresh automatically. Excel will refresh the query or PivotTable automatically.
  

• On the File menu, click Exit to quit Registry Editor.

Horizontal Lines in Word

Word allows you to insert several types of horizontal lines into your document simply by typing the same character three times at the beginning of a line. Enter return to start a new line and then type three dashes (---) and then hit return again and you will get a horizontal line. Other characters produce different types of horizontal lines:

--- produces a single line

=== produces a double line

___ produces a bold line

### produces a triple line where the middle line is bold

*** produces a dotted line

~~~ produces a wavy line

Multiple lines of text in a single cell

A couple years ago, I discovered a way to enter multiple lines of text in a single cell in an Excel spreadsheet.

Previously, if I wanted multiple lines of text, I had entered it into multiple cells as follows:

First line of text

Second line of text

Alternatively, I could have forced the text to wrap to multiple lines by adjusting the column width or by adding a lot of spaces.

The easy and more elegant way to do this is to hold down the Alt key and hit Enter at the point where you want a new line of text to begin. This allows you keep multiple lines of text in a single cell. You may need to adjust the row height to be sure that all of your text is displayed.

First line of text Second line of text

Those Annoying Personalized Menus

If the "personalized menus" in Windows and MS Office annoy you, there is a way to turn them off…

The personalized menus don't show you all of the items on the menu (only those that have been used recently). In order to see the complete menu you have to wait a few seconds or else click on the double arrow at the bottom of the menu.

On way to get around this is simply to double-click on the menu to open it – this will display the full menu.

To turn off the "personalized menus" altogether, go to Tools } Customize… in Outlook, Word, Excel, or PowerPoint and you will get a window like the following:

Select the Options tab and uncheck the box labeled Menus show recently used commands first. Then Close. A quick way to do this is simply open Word, Excel, or PowerPoint and then hold down the Alt key and type t-c-o-n. Then type enter. This will turn off the "personalized menus" in MS Office products.

To turn off personalized menus in the Start menu and Windows Explorer, go to the Start menu and select Settings } Taskbar & Start Menu to open the following window:

Uncheck the box labeled Use Personalized Menus and click OK.

Multiple views of the same file

Sometimes, you will want to look at different parts of an Excel file at the same time. If you want to view two parts of the same worksheet, then you can use the Freeze Panes or the Split features but they won't help if you want to look at two different worksheets at the same time. If you want to see two different worksheets from the same file at the same time, use the New Window feature. Go to the Window menu and select New Window. This opens up a separate window showing the same file.

This feature also works in PowerPoint and Word.

Removing individual legend entries from a chart

Suppose that I wanted to create an Excel chart that displayed a legend but I didn’t want all the data series to show up in the legend. How could I do that? Excel provides a way to remove a legend entry and still retain the series itself.

1. Build the chart along with all of the data series that you want to display


2. Show the legend


3. Click on the legend and then click on an individual entry in the legend. Note that you can select the entire legend entry (series name and graphic) or just the key portion (graphic).


1. If you select only the key portion and hit delete, it deletes the series


2. If you select the entire legend entry and hit delete, it deletes only the legend entry but continues to display the series on the chart.

Why would you ever want to do this? Consider the following chart. I am charting 10 data series along with the Minimum, Average, and Maximum. I don’t want to have a separate legend entry for each series but I still want to display each series to visually demonstrate the way the data is
distributed. I do want to show Minimum, Average, and Maximum in the legend.

---