How to extract last name from a name that has middle name

Hello All. In this tutorial, let’s look at how to extract the last name from a name with middle name. The formula can be complex and I will break the formula in to smaller steps so that you can follow every element of the formula used. Let’s look at the dataset and the formula Formula:Continue reading “How to extract last name from a name that has middle name”

How to find Intersection values in Excel

Hello All. In this tutorial let’s find a simple way to find intersection values in excel. First let’s look at the example data set. Here we have grade and subject assignments as below: We need to find out which teacher is assigned for a specified subject and specific class. Let’s see how this can beContinue reading “How to find Intersection values in Excel”

Picture lookup in Excel

Hello All. In this tutorial, let’s see how to do a picture lookup in excel. Imagine a list of name and corresponding pictures. If you select a name, the appropriate picture should list (something similar to a Vlookup / Hlookup). In our example, we are working on the below example: First, we need to assignContinue reading “Picture lookup in Excel”

How to create data validation with dependent Drop downs (a.k.a) nested data validation lists

Hello All. In this tutorial, let’s explore how to create drop down lists (data validation lists) based on a particular value selected from another list. Let’s look at an example below: Here, let’s create two lists. The first one will have the main category (State / Animal / Games / color). Based on the valueContinue reading “How to create data validation with dependent Drop downs (a.k.a) nested data validation lists”

How to change cases in Excel (Upper to lower and vice versa)

Let us look at how to change cases of text in excel. Changing the entire text range from lower to higher and vice versa is straight forward. However, if you want to change the case for a particular character (like only first character of a text string) needs a bit of logic in the formula.Continue reading “How to change cases in Excel (Upper to lower and vice versa)”

How to return a column title on given conditions

Imagine a complex data set where each department, in a corporation, spends on variety of items (example data set given below). How to get a summary of what each department spent? In our example how to find answers to the question marks? Dept 1 spent on “Maintenance”, “IT”, “Infrastructure” and did not spend on others. Continue reading “How to return a column title on given conditions”

How to populate stock exchange data in Excel

Do you know you can auto-populate the data of corporations registered with major stock exchanges across the globe? Lets see how to retrieve the data. Disclaimer: The data populated are as-is data available in stock exchanges about various corporations. Cannot be used to active trading. First to fetch data about corporations: Type in the listContinue reading “How to populate stock exchange data in Excel”

How to create custom Auto-Fill list in Excel

One of the popular question in the community is does Excel provide the flexibility to create user defined list that would be heavily used and can be auto populated – Its actually very simple. For example, suppose that you often have to enter a standard series of student name as the column or row headings inContinue reading “How to create custom Auto-Fill list in Excel”

How to add maps in Excel

How many of us know that Excel provides some cool data about countries and the same can be shown in maps? It’s a two step process – first to validate the country / state information. Next, convert the data into maps. Let’s explore. Write down the list of countries / states in any required cellContinue reading “How to add maps in Excel”

How to create Gantt chart in excel – Part 02

This is part 2 of the Gantt charts in Excel. Please note that we shall be using formulas in this section and the result would be a table based Gantt chart. We would be using a combination of Vlookup, IF, AND formulas. If you are not confident about using somewhat complex formulas, check out theContinue reading “How to create Gantt chart in excel – Part 02”