Spreadsheets to expedite taxonomic publications by automatic generation of morphological descriptions and specimen lists Visual guide, v1.2

This manual explains the use of a group of three spreadsheets that are intended to help the writing of taxonomic works by automatically generating textual outputs for sections whose writing is generally tedious and time-consuming: the description of specimens, summaries of measurement variation and lists of examined material. Each of the spreadsheets is explained in detail in the following pages. Spreadsheets are protected to avoid that users accidentally modify formulas or other important content, but they can be unlocked and edited without the need for a password. If using these spreadsheets, please cite: Magalhaes, ILF (2019) Spreadsheets to expedite taxonomic publications by automatic generation of morphological descriptions and specimen lists. Zootaxa .


Descriptions
This spreadsheet outputs specimen descriptions. Each row corresponds to an individual description (e.g. the male of Filistatinella hermosa). Column A identifies the species. Columns C-F refer to sex/stage, type status, locality and collection number and all are printed in the beginning of the description. In the example below, the output from row 5 is "Male holotype from 32 miles E Laredo, Texas, USA (AMNH, IFM-1219)." Columns G onwards contain characters. The first row contains character headers, which can be used to separate your description into body regions; these headers are printed to the output. The second row contains master characters, which initiate new sentences in descriptions, and thus their name is also printed to the output. In the example below, the description of row 4 would read "Cephalothorax. Anterior margin of the carapace unmodified. Total length 1.66." Please note that characters are only printed to the text of a particular species if the corresponding cell has data. In the example below, the description of row 4 would not mention its "Sternum", since the corresponding cells lack data. Important: if there are multiple columns referring to the same header and master character, and at least one of them contains data, data should be inputted in the first column mentioning the header or master character, otherwise they will not be printed to the output. Also, columns referring to the same header or master character should be consecutive, otherwise they will be printed multiple times.
If several descriptors refer to the same master character, its name is printed only once at the beginning of the sentence.
Character headers (row 1) and master characters (row 2) can be tagged so they can be easily formatted latter. To tag them, introduce the corresponding tags into cells F1 and F2, respectively. Tagging is optional and these fields can be left blank. You can also use tags that describe format intuitively (e.g., <bold>, <ital>, <smallcaps>, etc).
If tagging is enabled, the output for row 5 in the example above would read "<header>Cephalothorax<header>.
By applying the instructions explained below (see Formatting the text using tags below), one can use these tags to easily apply custom formatting to all descriptions in the manuscript. An example of formatted description would be applying bold + small caps to headers and italics to master characters: CEPHALOTHORAX. Anterior margin of the carapace unmodified. Sternum rounded, sigillae not visible. Total length 1.66.
The fourth row is not printed to the text; use it for comments, reminders, etc. The column headers are fully customizable, so change the character names to fit your purposes. All the characters are output in the order the columns are arranged. The current version of the spreadsheet supports 99 unique descriptors. If anyone should need to prepare descriptions with a larger number of characters, please let me know. Important: if you wish to add, remove, or rearrange the order of the characters, do not cut (ctrl+X) and paste data, and do not insert or delete columns. Use copy (ctrl+c) and paste to move data between columns G-DA and accommodate the characters. Cutting, inserting or deleting columns do not affect the formulas that concatenate the text, so if you make changes using these functions the reordering will not affect the output.
As you input the data, the descriptions are outputted in the final description sheet. Just copy the contents of the cells in column B (see below) and paste them into your favourite word processor.

Variation
This spreadsheet summarizes meristic counts and measurements. Each row should contain data for a single specimen. For the algorithm to work, the data should be sorted by species (column B) and then by sex/stage (column C). Row 3 contains character headers, which are included in the final output. The formula counts the number of measured individuals and fetches the minimum and maximum values of each character; outputting averages and standard deviations is optional (switch them on/off by typing "y" or "n" in the corresponding cells, A2 and C2). If a particular character is inputted in the table but you do not want it to be outputted to the final text (e.g. because you only needed it to calculate a ratio), you can prevent it from being outputted by typing "n" in the corresponding cell in row 2 (see example below: an "n" in J2 prevents "femur I length" from being outputted). Missing values are accepted, but the corresponding cell should have a textual string (such as a dash, -), otherwise it is interpreted as a zero.
The final text is outputted to the sheet variation per species; results are given separately by each sex/stage. Just copy the contents of the cells in column C (see below) and paste them into your favourite word processor.

Material examined
This spreadsheet generates lists of examined specimens sorted by locality. Data can be inputted in the following fields: Collection number, Species, Type status, stage1, stage2, stage3, stage4, stage5, Country, Admin1, Admin2, Locality1, Locality2, Habitat, Collecting method, Altitude, Coordinates, Collector, Date, Obs. Inputting data is only mandatory for the Species and Country fields (if there is no country data, I suggest inputting "No locality data" in this field); inputting data into Collection number and Admin1 fields is also strongly recommended. Other fields can be left blank and this will not affect the output. The headers of columns referring to stages (F-J) are printed to the text, so the user should rename them according to their needs (male, #f, ♀, immature, worker, etc.); these columns should contain the number of individuals.
Before inputting the data, the concat sheet can be used to concatenate geographical coordinates and collecting dates into single columns. The data separator can be defined by the user. To concatenate the coordinates, the user should also include the brackets. While this adds an extra step, different records can have different brackets; I use this to differentiate between records whose original label included coordinates from those which I georeferenced myself. Including the altitude at this step is optional, as there is a separate column for it in the data sheet. Important: when copying data from the concat to the data sheets, remember to paste values (Ctrl + Alt + V, check the "values" box, or right click > Paste special > values).
The convert sheet can convert coordinates from degree-minutes-seconds to decimals and vice-versa. If you input degreesminutes-seconds, please indicate the hemisphere using letters (N for north, S for south, E for east and W for west). When inputting coordinates as decimals, indicate the hemisphere by using positive (north, east) or negative (south, west) values. Failing to do so will result in the conversion returning an incorrect value.