Pivot Table and VLOOKUP – Microsoft Excel Tutorial
Excel is extremely important and is always being used in the corporate world. I personally use Microsoft Excel at work almost all the time since this is the most useful tool when it comes to creation of report. Let us start the tutorial with Pivot Table and VLOOKUP because these are the most used functions in Microsoft Excel!
Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.
Microsoft Excel has the basic features of all spreadsheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations like arithmetic operations.
It has a battery of supplied functions to answer statistical, engineering and financial needs. In addition, it can display data as line graphs, histograms, and charts, and with a very limited three-dimensional graphical display. Click here for more details about Microsoft Excel.
Pivot Table and VLOOKUP
A pivot table is one of the easiest ways to reorganize or summarize the data in a spreadsheet. The summary include sums, averages, or other statistics (max and min) which the pivot table groups together in a meaningful way. Look at the example below. Let’s make a summary thru pivot table and look how much Total Sales per Item in each Location.
To create the Pivot table, go to Insert Tab > Pivot Table and Excel will automatically select the table or range of your data for you. Default location for pivot table is New Worksheet. But you can choose where you want the Pivot Table report to be placed. Pivot Table Fields will be visible along the right hand side of the worksheet once Pivot Table is created.
The fields from the list can be dragged in the pivot table layout which has four options:
Report Filter – to apply a filter to an entire table. For instance, I’d simply like to show how much Total Sales of Fruit in each Location. And so I could choose only Fruit from Category.
Column Labels – to apply a filter to one or more columns. It works as the header of the data set.
Row Labels – to apply a filter to one or more rows. Both Row and Column labels can take data from your columns (ex. Location can be dragged to either the Row or Column label, it just depends on how you want to see the data.
Summation Values – this usually carries a field that has numerical values that can be used for different types of calculations. It can be sum, count, average, max, or min. By default, Excel summarizes the data by either sum or count of items.
Use of VLOOKUP function
Do you have two sets of data in two different spreadsheets? Tired of manually transferring data from one sheet to another just to combine into a single spreadsheet?
The Vlookup function of Excel performs a vertical lookup in the left-hand column of a data array or table and returns the corresponding value from another column of the array.
The syntax of the function is:
VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup])
where the arguments are as follows:
lookup_value – the value that you want to look up.
table_array – the data array or table, containing the search values in the left-hand columns and the return values in another column.
col_index_num – the column number within the supplied table array that you want to return a value from.
[range_lookup] – can be set to TRUE or FALSE.
FALSE if the function cannot find an exact match to the supplied lookup_value, it should return to an error.
TRUE if the function cannot find an exact match to the supplied lookup_value, it should use the closest match below the supplied value thus table array must be in ascending order.