Office Suite Abuse

Contents

• Introduction
• Word Processors
  • Style Sheets
  • File Sharing
• Spreadsheets
  • Named Ranges
  • Units
  • Data Tables
  • Drop Down Menus
  • Concatenation
  • File Sharing
• Glossary

Introduction

Your Office Suite should be your obedient slave. Any professional required to communicate with other professionals, and with the outside world, must be able to use these tools, and get whatever output they damn well please. This is not a comprehensive HOW-TO on office suites. It is a collection of hacks, that make these tools more productive. I am running GNU/Linux here at home and Libre Office as my office suite. As noted below, there are a whole bunch of office suites that work similarly. This is a generic article. The ideas presented are valid for users of Microsoft Office, and Calligra Suite.

Word Processors

With the advent of graphical user interfaces (GUIs), there are two kinds of word processor out there, WordPerfect, and Microsoft Word. WordPerfect does what word processor users think it is doing. You highlight text and hit a format key, and it gets formatted. You turn on some formatting button and you type. The formatting continues until you turn off that button.

Microsoft Word actually does not work this way, although this is how people use it. What Word really does is cool. Most formatting is attached to the paragraphs. There is a stylesheet embedded in the file. You can select styles from the stylesheet, and attach these to your paragraph. When you change the format of a particular style, all the paragraphs with that style, update. There are all sorts of word processors out there that work like Microsoft Word, including Libre and OpenOffice Writer, Calligra Words, the Applixware word processor, and Abiword.

There are no WordPerfect clones out there.

Style Sheets

Load a word processor document, and click on a paragraph. You may have to open a new document and type in a few paragraphs, without formatting.

Look for Styles and Formatting This may be under the Format or the Style menu. In Microsoft Word, the styles are displayed on the ribbon bar. In Libre Writer, select Style, then select Manage Styles. Styles and Formatting should be one of the menus on the right hand side of the screen, as shown to the right.

Note the style highlighted in your menu, in this case Text Body. There should be a button on the top left corner of your word processor, showing the same style.

You can change the style of your paragraph by double-clicking in the style menu, or by pulling down the top-left button. Search the menu or ribbon bar for the style Heading 1 and double-click on it. Your paragraph should switch to a large, bold font. Try some other styles and see what happens.

Now, let's go back to Text Body. Double-click on it to set the paragraph style, then do a single right-click. Select Modify. You should get a window like the one to the left. You can click on the image to see a larger one. You need to explore this and try stuff out. We will look at the interesting ones.

Click on Indents & Spacing. This window is self explanatory. You can indent either or both sides of your paragraph, and you can indent the first line. You can set the spacing between the paragraphs. There is no need ever again to insert an extra Enter at paragraph end. Note how all the paragraphs in your document update when you change anything, and hit Apply.

Click Alignment. Again, this is self explanatory. Again, any changes you make are applied to all of your Text Body paragraphs update.

Click Text Flow. There are a bunch of things you can do here, but two are interesting and very useful. I like the option Do not split paragraph. I keep my paragraphs fairly short, and I don't like it when they split at the end of the page. Keep with next paragraph again allows you to control page breaks. For example, if your paragraph is a heading of some sort, you do not want a page break between it, and the next paragraph. Your line spacing is affected by fonts. These vary between versions of Microsoft Windows. They certainly vary between other operating systems like Mac or GNU/Linux, and different office suites.

Do not use manual page breaks ever again!

Font does exactly what you think it does. Have fun!

Outline & Numbering is a rather complicated feature. Word processors allow you to insert a table of contents that has the right page numbers, and that updates when you modify your document. Outline level: allows you to tag your paragraph to be included in the table of contents. The other thing you can do is automatically number your paragraphs. The best thing to do here is play with these features, and see what they can do.

Click on Organizer. In Libre Writer, the style is named "Text Body". Inherit from: is an awesome resource. You have a root style. Your other styles cascade down from this, inheriting all the features you do not change. For example, you can bring up "Default Style", and set the font there. If you do not change any font families, you can control all the fonts in your document from the style "Default Style".

Next style: is also useful. If you create a heading style, you want to select the style, type in the heading, hit Enter, and find yourself back in your paragraph style. If you want to write some sort of question and answer document like a FAQ, you can set your stylesheet to toggle between question and answer styles.

The latest versions of Microsoft Office allow you to select from a series of style sheets. This affects font, colours, and the headings and footers. Very little of this cool formatting will be visible to users of Libre Writer and of older versions of Microsoft Word, but maybe you are not communicating with people like this! They still will see the standard headings.

File Sharing

Sharing files where you work should be fairly idiot resistant. Probably, you all are running the same OS and word processor. Things get complicated when you share files with people outside your organization. You need to understand everybody's lowest common denominator.

If you are part of a sports club, or you are doing volunteer work, or just communicating with friends, you are up against whatever computers your fellows are using. Most people at home do not fanatically update their computers. If it ain't broke, they don't fix it!

The best word processor transfer format is Microsoft .DOC.

The newer Microsoft .DOCX format is becoming more reliable, but there may be some very old machines out there. If you are running Libre or OpenOffice Writer or Calligra Words, use .ODF for your personal files only. Open Document Format is a great idea, but you cannot rely on everybody's computer to support it. Do not bother with the old Star Office .SWX format.

Do not use manual page breaks.

As noted above, different Windows versions, different operating systems, and different Office suites use different fonts. You cannot rely on consistent line spacing. Use the text flow features described above to control page breaks.

Do not use weird fonts.

Your favourite font may not be supported by the other guy's computer.

If your cool formatting really matters, save your file in Adobe Acrobat/Acroread (.PDF) format. PDF files store font definitions, and they preserve whatever format it is you have up on your screen. PDF used to be a good way to ensure your file was read‑only. There are all sorts of PDF editing tools out there that make this no longer true.

Spreadsheets

The basic functionality of a spreadsheet is to provide a table in which you input numbers and calculations. So far, so good. There are all sorts of things we can do with intelligent tables.

Named Ranges

A popular criticism of spreadsheets is that the equations are gibberish.

The code =b22*b23^3/3/b11/b21 is gibberish.

You can apply names to spreadsheet cells. In Libre Calc, you pull down Sheet and select Named ranges and Expressions. In Gnumeric, pull down Edit, select Modify.... and select Names... This brings up the "Define Names" window. In the window, click on the plus sign next to "Workbook". Type in your name. Data and select Named Areas....

Microsoft Excel is very friendly. Just right-click and select Name. Excel will guess your intended name by searching the cells to the left or above your cell.

Now, your equation says =Force*Length^3/3/Elasticity/Moment2ofArea. This is way more understandable.

Names can be applied to single cells. They can be applied to ranges of cells. They can be applied to absolute or relative cell references ($C$11 versus $C11 or C$11).

Units

Now that we understand cell names, let's use them.

When I do calculations, I reserve the last page of my spreadsheet to units and conversions. To the right, we see a screen shot of Gnumeric, a small, Excel clone. The highlighted cell has been named ft. The number in the cell is the conversion to the appropriate MKS units, in this case, metres. Note the top left-hand cell showing the cell name. Also, note how I did not bother to read up on the conversion from feet to metres. Now, I can enter my values in whatever units I want, and I can do conversions in the cell.

=22*ft+6.5*in

=255*mm

=25e3*lb/in^2

That last equation comes out in N/m², or Pa. Now, we can see how the conversion is being done, and we are showing clearly what conversion we are doing!

Data Tables

We can create data tables. We can search for data in the tables. We can recover the data from the tables by specifying rows and columns.

Copy the table below, and paste it into a new page in your spreadsheet. This should work. If it doesn't, get a new spreadsheet.

Now, you have a table of data in your spreadsheet. This comes from Design of Machine Elements by V.M. Faires, from Table AT3.

1. Click on the cell containing the word "Aluminium".
2. Name this cell "Aluminium".
3. In a cell elsewhere in the spreadsheet, type in =OFFSET(Aluminium,2,4)

This should produce the result "60e3", or "60000". This is the ultimate shear stress for alloy 2014-T4. Change the 2,4 couple to some other numbers, and see what happens.

Command: =OFFSET(TopCorner,Row,Column)

You can select a row and a column, and grab the data. You can read the help files on OFFSET. It does quite a bit more stuff than is shown here.

You can search through rows or columns of data, and look for matches to your data.

1. Highlight the Alloys column from 3003H‑14 to 356‑T6.
2. Assign to these the name "Alloys".
3. In some other cell, type in =MATCH("6061-T6", Alloys, 0)

This should produce the result "4". The text "6061-T6" is the fourth cell of the series you highlighted and named. Note how it is the fourth line of the data table, and that you can feed this number into the OFFSET command, above.

Command: =MATCH(Text, DataSeries, 0)

Watch that last parameter "0"! This tells MATCH that you want an exact match. If you leave this blank, it behaves weirdly. Read up on this command if you want more information.

Drop Down Menus

Rather than typing data into spreadsheets, you can create drop-down menus. Thank you Bernardo Tech on YouTube. I am using Libre Calc here. Microsoft Excel appears to have the same validation menu.

Let's start with the data tables we defined in the previous section. We have defined the cell name Alloys.

Click on the graphics above, to enlarge them.

1. Click on a cell.
2. Pull down the menu Data.
3. Click on Validity..., bringing up the window shown above.
4. Pull down the Allow: menu and select Cell Range as shown.
5. In Microsoft Word, for Source, type =Alloys. In LibreOffice as shown, just type Alloys. What you need here is a range of cells. You could have simply click on the cell range with your list of aluminium alloys, something like $Data.$C$10:$C$16. By naming this cell range, you have made everything meaningful.
6. Click OK. Now, you have a pull-down menu from which you can select a material.

Now we can create a material specification for a calculation, with the material selectable from a pull-down menu. The second window above shows a beam analysis. The data cells are named BeamMatl, BeamSu, BeamSus, BeamSy, etc., as shown. Cell B3 is the pull-down menu. Cell B6 shows the calculation needed to find the yield stress for aluminium 6061‑T6. Try setting this up.

Concatenation

When you were a kid, were you even allowed to say "concatenation"?

You can take data from several cells, or several operations, and concatenate the results. This is a useful resource for displaying results. It is even more useful when the spreadsheet data is exported to other applications. SolidWorks' design tables are Excel spreadsheets. This is a great way to set up BOM entries and other meta-data, in SolidWorks.

You may not be able to copy and paste the table above. Some spreadsheets think they are clever, and they interpret the fractions as dates. Try typing the fractions in manually with a leading quote...

'3/16

Before we concatenate stuff, we will demonstrate a useful application of cell names.

1. In the first data line, click on the "2".
2. Assign the name "Size" to the cell. Do not hit Enter immediately.
3. In the listing of the cell location, delete the dollar sign next to the row number, $Sheet1.$D$6 changes to $Sheet1.$D6
4. In the first data line, click on the "56".
5. Name it "TPI", and set the address to $Sheet1.$E6.
6. In the first data line, click on the "3/16".
7. Name it "Length", and set the address to $Sheet1.$F6.
8. Click below "Screw Description".
9. Type ="CAP SCREW "&Size&"-"&TPI&"UNC X "&Length and hit Enter.
10. Copy this to all the lines below.

You should now have a list of screw descriptions, controlled by the data columns along the row.

Note how the cell names are relative to to the line. You can just as easily make your cell names relative to the columns. This makes your table expressions way more meaningful.

File Sharing

Export to Microsoft .XLS format.

You need to work at the lowest common denominator. At work, probably you all are using the same version of spreadsheet, and you can get away with Microsoft .XLSX format. This advanced format may not be supported by older computers. It is too bad Open Document .ODF format is not better supported.

If your spreadsheet's formatting and fonts matter, save it in Adobe Acrobat/Acroread .PDF format.

If your spreadsheet must be intelligent and executable, create it and manage it as a .XLS file. An .XLSX or .ODS file can lose functionality when it is saved as .XLS. If you use all the cool new features in your applications, don't expect them to work on other computers. For security reasons, a lot of email programs do not allow attached spreadsheets to execute. You may have to advise your recipients to save the file and then load it with read/write permissions.

Again, do not use weird fonts.

Be very, very careful copying and pasting data from one spreadsheet to another. There are quite a few ways for a spreadsheet to manage external named cells. You may not notice the resulting weird results in time. Strongly consider copying, and then going Paste Special, and plain Text.

Glossary

• Key -- on your keyboard
• Menu -- pull-down
• Button -- in program window

Howard Gibson — Email hgibson@eol.ca — Last updated: 2021/04/14