Microsoft Excel is one of Microsoft's awesome technologies used for data analysis, visualization, processing, and many database functions. It uses spreadsheets to organize, store and track data sheets with formulas and functions.

Throughout this tutorial, we would be using a small sample of data of cars shown below of different companies with their respective brands and attached prices.

In our first example, we would be exploring some of the functions of Microsoft Excel which will help one perform tasks on the sample data above.

Concatenate

This function as the name implies is used to join strings and any type of inputted values together. The function call takes as many values as possible separated by commas. The first task is to create a car ID using the various columns except for the price column. A concatenate function will be used in addition to the LEFT, RIGHT, and MID functions. The LEFT function is used to extract strings from a text starting from the left side of the string taking the text and the number of characters needed as arguments. The RIGHT function works the same way as the Left function, but in this case, the number of characters extracted is with respect to the last element as the starting point. MID on the other hand gives flexibility as the function call takes into consideration the starting index as an extra argument in the function.

The CarId has been created using the function call in the formula bar in the figure above. We also used an extra helper function, UPPER() which converts a text argument to upper case.

VLOOKUP

This really cool function fully known as Vertical Lookup does the task of searching for a value from a column and returning another value of a different column in the same row. In simpler terms, it searches for a value (x) in a table and returns from a target column(y), the corresponding value of the searched value(x). The function takes fours arguments, the value to be searched for, the data, the index of the target column, and an optional parameter that considers if the return value is an approximate or exact match. It has broad applications in every day-to-day use of excel, used mostly by stores to get the prices of their products.

The next task in this tutorial is to use the Vlookup function to get the price of inputted car name. The car name column was gotten from a concatenation of Company and brand. The end product. Changing the Car name will automatically change the price to the target car name.

Note the first column of the data used for the Vlookup must be the column of the searched value. The target column (price) has an index of 5, counting from the searched value column. Also, another interesting thing about the Vlookup function is that the data can be gotten from other excel files and sheets.

Getting the hang of it?... Let's try out one more example.

We would be using Vlookup to extract the quarterly sum of sales for a particular group of products, for example, Louisiana's products but in this case, we would be doing it in another excel sheet.

Here, we are looking up the sum of sales for the first quarter of any product, and the data coming from another sheet. In some cases, there will be issues with referencing which excel uses two types (absolute and relative), which its explanation will be another tutorial on its own. This function has made it easy to look up any quarter sales for any product by just changing the name of the product and the index number in the Vlookup function to match what is being searched for and the function returns the target with ease. Look at the big picture when the data is very large, the task becomes a whole lot easier and simplified.

There are many other excel functions that make data analysis easy and fast with diverse applications from conditional statements to filters, sorting mathematical functions, and lots more with easy-to-use and understand syntax.

Hope this function helps you as you get started or work with excel sheets. Thanks