Mind maps are great, spreadsheets too. During testing my choice for tools is context driven.

Did you notice the dates, my dear Spreadsheet?

During testing I noticed some strange dates in a table. It was “01-01-0001”. That is more than two millennia ago. (One of the most influential men had his first birthday party on that particular date.)
I thought again:
“How would I mark an item with no end date?
01-01-0001.
But why are there more than 2 items with the same name and no end date?”
I found a bug. Next I scrolled through the table and found other pairs. This process should be automated.

1. Export the table with the header in the database to a csv file.
2. Import the file into a spreadsheet program.

If all data is still shown in one column, then

1. Select the column.
2. Use the spreadsheet function Convert text to columns.
3. Select Separated.
4. Choose the delimiting character ; (semi colon)
5. Click Finish.

Now it is time to sift the information:

1. Select all the columns of the table.
2. Order the table on the item name and then on the end date.
   Column A is item name and column C is end date.
3. Select the cell right to the heading  in the first row.
   Column G is the first empty column.
4. Add an extra attribute to the heading e.g. “Same” (in cell G1).
5. Select the cell under the attribute Same. (G2)
6. Go to the formula editor and make the following formula:=IF(AND(A2=A3; C2=C3);”yes”;”no”)
   This means, when the item names of row 1 and 2 are the same and the end dates of row 1 and 2 the same, show the text “yes”;
   if this is not the case, show the text “no”.
7. Copy the formula by selecting the cell with the formula (G2), placing the mouse on the right lower corner of the cell with the formula, press the left mouse button, move the mouse downwards until the lowest row of the table and release the left mouse button.
   The formula has been copied to all relevant cells in the column Same.
8. Set a filter in the header of the table.
9. Set the filter of the column Same to “yes”.
10. Set the filter of the C column or End date to “01-01-0001”
    All rows with items with the  same name and no end date are shown.

Can you handle Big Data, Mister Spreadsheet?

A spreadsheet program is great to analyse csv files. But my first analysis of more than 70,000 lines of data was not so great: it took some time to load and convert the data in the spreadsheet program and it was not possible to load all data at once. There was a chance, that I might miss something. And I did not want to miss a bug.

Some people abhor the use of command lines. And if I mention UNIX some people think: “Run for the hills”. However there are some powerful commands, which make the life of a tester easier. grep is a Unix command, which can be used to filter information from ASCII files using pattern recognition like item id. So I searched for “Windows grep” on the Web and found an interesting open source tool.

I started with a csv file of table with no header. The table description was in a pdf file. I only had to examine all records with specified item id.

1. Use a windows grep program to extract all lines containing the string with item id.
2. Export the output to a csv file.
3. Import the csv file in a spreadsheet program as described in the previous paragraph.
4. Copy the column with the attribute names from the table description in the pdf file.
5. Paste these cells in another worksheet of the spreadsheet.
6. Transpose these cells into a decent heading.
   (Change a vertical row cells to a horizontal row cells using a standard spreadsheet function.)
7. Copy and insert the heading above the table.
8. Set a filter on the heading.
9. Set the filter of item id in the heading to the item id to be analysed.
   All records containing the item id are shown.

“Walk your talk” is a common advice. For me it became “Mind map your talk”. During my employment of an IT company I became a mind map teacher. I needed lots of examples, so I started using mind maps for many occasions.

The frequent visitors of my blog might suspect, that I use mind maps for too many things. Here is some proof against it:

• In the office I use spreadsheet as a log book and table formatter (Make a  comprehensive table in a spreadsheet program, copy ,and paste it in a word processor).
• In this blog I hided uses of spreadsheets here and here.

So I am not an Invading Spreadsheet Snatcher. You’re just lucky.

Can you cover me, Spreadsheet?

A fast way to show the coverage of functions or attributes is to put it in a mind map.

1. Just open the document in a word processor.
2. Copy the group items.
3. Paste the items in a mind map.
4. Use icons to register the progress.

It can be cumbersome in particular cases. How about more than 20 items? With related information like syntax, position, etcetera.

Once I had to test a change request. There were good technical specifications about a new file format in pdf format. And the same specifications in a spreadsheet. :)

These were the steps I took:

1. Open the spreadsheet.
2. Determine the column with the field names.
3. Insert a column “Changed”.
4. Add columns “To be tested” and “Comment” at the right end of the table.
5. Put a filter on the heading of the table.
6. Fill in, whether rows have been changed with “Old”, “New”, or “Changed”.
   “New” means completely new field.
   “Changed” means, that the field has changed position or the size, etc.
7. Set “To be tested” to Yes, if Changes is “New” or “Changed”.
8. Check, whether the rows Changed and To be tested are filled with the filter.
9. Test, whether a subset of changes have been implemented within a time box.
   i.e. the rows with “To be tested” equal to Yes.
10. Note down, what has been tested, and ids of bug reports in the column “Comment”.

Feedback loop 1

1. Talk with a fellow tester and programmer, which other modifications must be tested
   i.e. the rows with “To be tested” equal to No.
2. Change “To be tested” to other values after this talk and add additional information in the column “Comment”.
3. Test, whether the determined changes have applied within a time box
   i.e. the rows with “To be tested” equal to Yes.

Feedback loop 2

1. Talk to a business analyst, which other modifications must be tested
   i.e. the rows with “To be tested” equal to No.
2. Change “To be tested” to other values after this talk and add additional information in the column “Comment”.
3. Test, whether the determined changes have applied within a time box
   i.e. the rows with “To be tested” equal to Yes.

Can you help me with the question, Spreadsheet?

A supplier had changed a table, which was already used in production. A programmer came in with an urgent request to check the changed values. Just a few checks were good enough. I had only a hard copy of the changed specs. After a small request I got the pdf version.

These were the steps I took:

1. Open the pdf file.
2. Go to text mode.
3. Copy the complete table.
4. Paste the table in a spreadsheet.
5. Select the cells with identifiers.
6. Transpose these cells.
   (Change a vertical row cells to a horizontal row cells using a standard spreadsheet function.)
7. Save the sheet to a csv file.
8. Replace “; ” (semi colon)  by “,” (comma) in the csv file.
9. Copy the content from this file in a sql tool.
10. Make a query with the following format:
    Select * from the_table where id in (<copied string>)
11. Execute the query.
12. Talk with programmer about the result.

Bonus Spreadsheet material for free (Really)

In case there is no fancy pdf or digital file, then the following steps can be followed:

1. Scan the hard copy.
2. Use OCR software to convert the picture or graphical file into a text file.
3. Correct errors in the text.
4. Format the text.
5. Copy the information in a spreadsheet.
6. Make nice columns  with “Text to columns”
7. Follow steps described above.

Just

mind your spreadsheet program.