Double clicking on the individual row labels or the corresponding count opens a new worksheet that has details of all the hits for that particular sequence. Interpreting the dataĪs you can see, the Pivot table gives you a count of hits for each sequence that was used as a BLAST query.
Click to open full size image in a new window. Choose any empty cell (say O2) and click OK.įigure 7. You can insert it into a new sheet, but I like to have a summary in the data sheet itself. Excel will ask you about the data range and where to put the Pivot table (Figure 5). To begin data analysis, select the data range by going to the last cell with data value in it (Ctrl+Shift+Rt arrow and then down arrow) and then click on Insert and select Pivot Table.
Let’s assume that you have already pasted/imported the output text to an excel spreadsheet and inserted column names. Pivot tables are excellent tools to get an overview of the data without needing to filter the data. In this particular example, in which we used a batch BLAST search, the output table contains multiple subject hits for each query sequence and hence the number of rows that will be filled up can easily exceed a few thousand, making filtering laborious. You could use Excel’s built-in filtering to sort and look at the data that you want to analyze, but a better way is to utilize Pivot tables to get a summary of the data. Insert a row above the first row in the Excel spreadsheet and enter the column headings to enable quick data analysis and filtering. The column headings are shown below each column. The output is a tab delimited text file that can be copy-pasted or imported into Excel.
We will use the mouse sequences as the query and human tRNA sequences as the database.įigure 4. Save the files in a folder of your choice or under C:\BioEdit\database.
I am going to download the Mus musculus (mouse) and human tRNA sequences in fasta format from genomic tRNA database (Figure 1). We next need two nucleotide/protein files: one acting as our database and the other as our query.
So download the latest version of BioEdit and install it on your PC. BioEdit is freeware that provides an extremely easy way to do sequence analysis on the Windows platform. For a sequence file of a few hundred sequences, the whole exercise can be done in less than an hour (depending on your workstation configuration and personal inclination of course!) Choose your sequencesįor the purpose of this exercise, I am creating a database of human tRNAs in BioEdit.
So in this small guide, I am going to show you how to set up a local BLAST database in BioEdit, do a BLAST run using parameters of your choice, and then use Microsoft Excel Pivot tables and charts for analysis and quick BLAST summaries. However, when you have to analyze hundreds or a few thousand queries, running graphical outputs can be computationally intensive, difficult to analyze and time consuming.Ĭommand line warriors on Unix and Linux platforms can get data cracking using text editors and programming languages, but what if you don’t have the training, time or resources to do that for the job at hand?Īll BLAST webservers offer the data in text editable formats such as *.csv files which are amenable to quicker analysis. When users have one or few queries, the data are easy to interpret based on the graphical output that the search provides. Basic Local Alignment Search Tool (BLAST) remains the cornerstone of biological sequence analysis.