Manage Servers | Using Excel

Excel - VLOOKUP, Filtering, Pivot Tables and more…

I always believe in working smarter not harder so that’s why I wanted to go over functions in Excel for filtering data and also summarizing results. PowerShell is nice but sometimes in a very large environment or over slow links, it’s easier to just pull the data and do the filtering in Excel.



VLOOKUP

Compares list in column B to list in column A

=IFERROR(VLOOKUP($B$1:$B$26,$A$1:$A$24,1,0),"Value Not Found in Colum A")

Once the formula is in place, just click on the area marked with the arrow and drag down to copy the formula throughout the column.



PIVOT TABLES

Pivot tables are a great way to summarize and display data. Instead of trying to count through all sorts of different things in a column you can select the data and then click on the Insert table and select Pivot table. Below is a simple example but these can become completed depending on your dataset.

Pivot Table



Dataset



FILTERING

Filtering is pretty basic but very useful tool. Select your columns and then
click on the Home tab and select Sort & Filter from the menu bar / ribbon.





CHARTS

There are many types of charting for data in Excel. Below is a chart made
from the data below. Simple but much my eye catching and easy to see who stands where.