Excel

Sort a Table by IP Address

---

1. . Create a new column to the left of the IP Address column. Let's call it the Sorted column
2. . Copy the IP addresses from the IP Address column to the new column (Sorted)
3. . In the Sorted column, use the "find and replace" feature to remove the first three octets so that you are left with the last octet only
   1. . For example, it the IP addresses are "10.240.5.4", then find "10.240.5." and replace it with nothing. This will leave the "4" in the Sorted column.
   2. . All of the IP addresses in the Sorted column should only have the last octet now
4. . Highlight the entire table, click on Data > Sort, and Sort by Sorted (column name), Sort on Cell Values, Order Smallest to Largest.
5. . Voila. You can now hide or remove the Sorted column.

Ref: https://answers.microsoft.com/en-us/msoffice/forum/all/possibly-the-easiest-fastest-way-to-sort-ip/f94d0429-f12d-44f1-a29e-ba2001a625a0

PivotTables

---

This example shows how to create a PivotTable of a Vulnerability scan spreadsheet.

1. Click on the Insert tab.
2. Click on Recommended PivotTables
3. Select Count of Plugin Output by IP Address and Severity
4. In the PivotTable Fields pane, select additional fields DNS Name and Plugin Name
5. Change the Values title from "Count of Plugin Output" to "Vulnerabilities"
6. Change the Column Labels title to "Count by Severity"
7. (Optional) Then change the layout form of the PivotTable from Compact form to Tabular form.
   1. Click anywhere in the PivotTable and then select the Design tab.
   2. Click Report Layout and then select Show in Tabular form.
8. (Optional)
   1. To further organize the PivotTable, highlight the IP Address column header cell again, right click, and select Field Settings.
   2. Go to Layout & Print and select Show item labels in outline form. Leave only the Display subtotals at the top of each group check box selected.
   3. Then highlight the DNS Name column header cells, right click and select Field Settings.
   4. Go to Layout & Print and select Show item labels in outline form. Leave only the Display subtotals at the top of each group check box selected.
9. Tip: You can play with the Subtotals and determine whether you want to show it or not by going to the Design tab and playing with the options under the Subtotals drop down.