Skip to main content

Excel shortcuts and tips

This document contains links and tips to use as an aid during your Excel journey. If you have any other questions or concerns, reach out to our [help desk] or send a message to our [Slack].

Sorting by columns​

  • There are several ways you can sort rows by a specific column. For example, if you want to sort in alphabetical order based on your sample IDs, you can do one of the following methods:

  • Data > Sort > Select column > Select what to sort by > Select sort order > OK

  • This should sort your whole spreadsheet.

  • Select row 5 > Data > Filter > Select the dropdown of the column you want to sort by > Select sort order

  • For this method, you must select all the columns you want sorted. Otherwise, it will only sort the highlighted columns, which can cause your data to be mismatched.

Pasting text with underscores​

  • Underscores are common in sample IDs or filenames. Sometimes Excel separate the text by underscores into multiple cells when it’s pasted. To avoid this issue, make sure the following behavior is set:

  • Select a non-empty cell > Data > Tex to columns > Select Delimited > Next > Select Tab delimiter and disable the others > Deselect Treat consecutive delimiters as one > Next > Finish

  • You can also use formulas to combine the cells into the correct format. Refer to the following example for a filename “SampleName_mmddyy_R1_fastq.gz”, which has been copied into cells A1 - D1.

  • Formula 1: =A1&””&B1&””&C1&”_”&D1

  • Formula 2: =CONCAT(A1,””,B1,””,C1,”_”,D1)

Converting lat/long to decimal format​

  • We will be asking you to add your lat/long data for your samples in decimal degree format. If your coordinates are in degrees, minutes, seconds or degrees and decimal minutes, use the following steps to convert them to the right format.

  • Before you get started, make sure to have enough blank columns to avoid overwriting your data in your metadata spreadsheet. You can also do this in a blank spreadsheet to avoid any errors.

  • If your coordinates are in the same cell, do the following to separate them into different columns.

  • Select the cells to separate > Data > Text to columns > Select Delimited > Next > Select Space > Next > Finish

  • Make sure there’s at least 3 blank columns between the coordinates.

  • For a faster way to get the degree ( ° ) character, copy the character before following the next step.

  • Select lat or long cell > Data > Text to columns > Select Delimited > Next > Select Other > Insert character ( ° ) > Next > Finish

  • Continue this step and separate by ( ‘ ) and ( “ ). By the end, you should have all the degrees, minutes, and seconds in separate cells.

  • In the forth column, delete the “N” and enter the following formula: =A1+B1/60+C1/3600

  • Now your coordinates should be in decimal format.

  • You can perform these steps on multiple cells at once. The formula can be copied or dragged down the rest of the column to convert all your coordinates to decimal degree.

Ordering your filenames​

  • When it’s time to put in your file names, use the following steps for a faster way to sort everything. To avoid any mistakes, do this in a new spreadsheet. Note: If you use file names that are different from the Sample Names, you may need to write a code to sort them in the right order.

  • Open the folder with your fastq files in your file manager.

  • Select all the files (Ctrl+A on Windows, Cmd+A on Mac), copy (Ctrl+C on Windows, Cmd+C on Mac), then paste (Ctrl+V on Windows, Cmd+V on Mac) into Excel.

  • If all of your forward and reverse read file names were copied over as one, use the following steps to separate the forward from reverse.

  • Select a cell next to the list > use formula =SUM(--ISNUMBER(SEARCH({"R2"}, A1))) > copy or drag the formula down the rest of the column

  • Select all the formula cells > Data > Sort A to Z > Select Expand the selection > Sort

  • Your file names should now be sorted with all forward files sorted in the top half, while reverse files are sorted in the bottom half. You can take the reverse files and cut and paste them into the next column. You can paste over the formula cells, or paste in a new column (but be sure to delete the formula cells after).

  • To make sure your file names match up correctly with the sample names, use the following steps:

  • Select all the columns in your metadata spreadsheet > Data > Filter > Click the filter dropdown on the Sample ID column > Sort A to Z

  • This should have sorted your metadata spreadsheet based on the Sample ID column. Make sure all columns are selected before you do this, otherwise the data will be mismatched.

  • In the spreadsheet containing your file names, use the following steps:

  • Select both columns > Sort A to Z

  • Now you can copy / paste the two columns into your metadata spreadsheet under the forward and reverse read file name columns.

Source document: Google Doc