Skip to main content

Microsoft App Notes

Microsoft Word

Clicking on the style drop-down just shows the main styles in use. Shift click reveals all the built in styles, including the otherwise hidden 'caption' style.

Microsoft Excel

Linking to a results file produced by a command line application

It is often easiest and most efficient to to gather and preprocess data using a command line app, which creates a series of results files in csv format, and then import then into Excel to examine them.

To setup the initial link use 'Data/Import External Data/Import Data', then select files of type *.txt, *.csv etc, and then locate and double click on the file. The file import wizard appears, and after clicking next select 'comma' if the file is csv format. After the data has been imprted, right click within the imported data, select 'data range properties', and unselect 'prompt for filename on refresh'. You may wish to change how it will deal with changes in the number of rows. The data can be refreshed by right clicking and selecting 'refresh'.

A button can be created which, when pressed runs a macro along the following lines, which changes the link to point to the file in the current directory and refreshes the data. This allows the spreadsheet and associated csv file to be moved to a different directory.

Path = ThisWorkbook.Path
With Sheets("neighbours").Range("A1").QueryTable
.Connection = "TEXT;" & Path & "\neighbours.csv"
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With

Selecting a hidden line in an excel graph

It is sometimes convenient to hide a line on a graph by setting both the line and the marker to none. How then do you select it to re-enable it?

The answer is to select another line, and then use the up and down arrow keys to move between the graph components until the line you want is selected (The 'selected' box in the top right tells you what is selected) and then right click on one of the selection markers and select 'Format Data Series'.

Linking chart titles to spreadsheet cells

Click the title, label, or text box, type '=' in the formula line at the top of the spreasheet and then select the cell to which you want to link, or type in the name of the range.

Using named ranges in charts

http://office.microsoft.com/en-gb/excel-help/using-named-ranges-to-create-dynamic-charts-in-excel-HA001109801.aspx