PolPal in MS Excel Download POLPAL.xls Home

Pollen counting, diagram drawing and numerical analysis in Excel (up to 2010)

Adam Walanus, Department of Geoinformatics and Applied Computer Science, AGH University of Science and Technology, Al. Mickiewicza 30, 30-059 Krakow, Poland, walanus (at) geol.agh.edu.pl
Dorota Nalepka, W. Szafer Institute of Botany, Polish Academy of Sciences, Lubicz 46, 31-512 Kraków, Poland



Data manipulated by POLPAL have a very typical form of a table, with taxa in columns and samples (spectra) in rows. The first column consists of sample depths, ages or any other number systematicali decreasing or increasing down (in the last case, the minuses will be added automatically to "depths" to obtaine proper direction). Presented below is the spredsheet Data, in which tha source table to be placed. (The exemplary table is small one, for space saving here.)

The table sholud be the only data present in the spredsheet Data. It is the source for any calculations, however, not the only source. Only one procedure here, that of counting is allowed to modify the source table. Of corce the user can do anything withe the numbers and taxa names.

It is important that the table is placed starting with the cell A1, and that the row below the table, as well as the column at it's the right side are empty. Program, simply have to calculate the number of rows and columns, recognizing the table size. The formatting of the first column (vertical taxa names) is not essential for the program operation. The exemplary table present in the POLPAL.xls file is simply to be replaced by users data.


To plot pollen diagram it is enough to click the Plot diagram button at the Buttons spredsheet.

If some taxa are to be excluded from the pollen sum, then the button Copy Taxa is to be used. Click on it creates List of taxa (at spredsheet Taxa, see below). It is simply copy of taxa from the Data table. Aditionally, the two letter codes for taxa are proposed, to be used in Counting. The free column B is to type here any text (for example, the letter 'e') to indicate that the given taxon is to be excluded from the pollen sum (below, Picea is to be excluded (for an unknown reason)). Must be mentioned, mainly for the reason of Counting, that that is not necessery to create (Copy) list of taxa for each new table. Any list of taxa present in column C of Taxa spredsheet can be used by plotting procedure. The procedure will search for any taxon presumably to be added to the sum if it is present in the List, and if is marked as to be excluded.

Consult the parameter Percent Step [%] at Parameters spreadsheet. For larger values the diagram may appear more clearly.

Counting pollen (or other grains)

Clicking the Counting button starts the window Counting (see below). Just after the window became visible, click on it the button Start. The taxa will be read out from the table (Data) into the Counting window, and the number of row to be "counted" will be proposed. It is number of the row (not sample depth or age) selected before starting the Counting window (below: 5). That number can be changed to any other necessary, however, the best way is to click the proper row (anywhere) befor starting the Counting window. While the window is visible, the spreadseet is unavailable. That is the reason for two small buttons Data and Taxa for switching between two spreasheets essencial for counting. Closing the window (by clicking normal red 'X'), and starting it again makes no any harm.

Counting (adding 1) consists in the double clicking taxon name. The better way, however is to use the taxa codes. Pressing a letter on keybord adds counts to the taxon coded by the letter. Codes are case sensitive, so 'a' and 'A' indicate to different taxa, for example Alnus and Abies. For the less frequent taxa two letter codes can be used. In such a case three keystrokes are necessary, because the two letter code is to be started bt pressing the 'space' key (to informe the silly maschine that there are no two one letter codes).

Taxa codes are in the Taxa spreadsheet (in column A, see the second figure above). At the very beginning, clicking the button Counting in the spreadsheet Buttons creates the List of taxa, simply copying taxa from the table. Additionally the codes for taxa are proposed, as two starting letters. These codes, as a rule, are to be modified, because (1) may be not unique, and (2) probably are not very convenient. Codes consist of one or two letters, small or capital. The one letter codes are the most convenient ones. Using capitals needs of course 'Shift' pressing. Two letter codes (space + a + b) needs more keystrokes but are necessary to cover all taxa, and sometimes can by easier for memorization. For example the code 'ab' is better for Abies alba than 'y' if 'a' and 'b' are already occupied.

The list of taxa with the codes prepared can be used with any table pasted to the Data spreadsheet. Simply don't use the button Copy Taxa. Of course, taxa names in Data and Copy Taxa must be identical. It is easy if the list of taxa is treated as a main source of taxa names for taxa added to the table.

Confidence interval

Calculates, for the selected taxon, 95% confidence interval.

Select taxon at spreadsheet Data, by clicking (selecting) any cell of the given taxon. Then click the Button Confidence interval. The result will be displayed at spreadsheet Parameters, in columns J to O. The Raw data are repeated here, as well as two columns with the boundaries of the confidence intervals of the counts, and errors recalculated to the percentages. The last columns are ready to be added to the proper diagram curve as "Error bars".



Plots the "tree" of CONISS analysis, which helps to group in neighboring samples. The figure is sent to Plots.

Rarefaction analysis

Calculates the rarefacted number of taxa (scaled down to the lowest sum). Check the 'Raref. Minimal sum' value at Parameters which excludes samples with too lower sum. The result is in numerical form, preapared for easy plotting (see below).

Download POLPAL.xls

AW Main page