PowerShell - Manage Data with 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.



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 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 data-set.

Pivot Table



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.


2020 | Scott W. Head  |  Sr. Systems Admin | Certified Microsoft Windows Server Administrator