For many reports, it is good to see the largest items in a list (top 10 web pages, top 20 keywords, etc). You are only getting part of the story if you don’t keep those items in perspective to the whole – what was the total for all the rest of the items? In this article, I’ll show how easy it is with Nextanalytics to combine all those ‘other’ items in your reports.

After you have loaded your data into Nextanalytics for Excel, select the Analyze tab, and start by sorting the list so the largest numbers are first (descending order).

Then it is a matter of deciding how many items you want displayed (top 5, top 10, or top 100?). By selecting the checkbox to Show Advanced script commands, you pick the Select Row-First 10 command.

In Nextanalytics, many of the script commands can be easily modified to produce different results. For example, if you want more or less than 10 items in our report, we can change the Select Row-First 10 command to use a different count. By clicking once on the command description in the command list, the actual script command appears in an edit window below. In this case, the number 10, can be changed to 25, then the Add button can be pressed to include our modified command in the list to be executed.

Then you use a Combine command to Total and Remove the Unselected Rows (Other). This will take all of the rows that were not selected in the previous step and add them up, and replace them with a single row titled ‘Other” that contains the Sum of all their values.

In reports where you wanted the average of the rest, not the total, you could simply change the text ‘Sum’ in the command to ‘Average’. Also, if you wanted the new row to be named something besides ‘Other’, you can change that word in the command.

In the worksheet, we will see the new line “Other” has been added to the top of the list. For some cases, we would stop here, but many people are used to seeing the ‘Other” item at the bottom of the list, not the top. That is quickly remedied…

Simply select the first row and move it to the last position. (Select / Select Row-First, Arrange / Move-Selected Rows-to Last). Now all your data is displayed in the order as you want it to appear.

You might think it would be easier to simply drag the cells around in Excel, or even to use an Excel Sum() formulas to calculate the ‘Other’ group. While there is nothing stopping you from doing this, if you wanted to refresh the report with new data, you would have to make all those changes manually again. If more rows suddenly appeared in the source data, you might need to update the range used in your Sum formula (and you won’t know it is wrong if you don’t check it).

The Nextanalytics scripted commands, however, will automatically refresh with no additional effort, and it is not affected if you suddenly have more rows in your source data. Now you can easily include those top 10 list in your reports!