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