ThinkLeaf Consulting

How to use Choose function in Excel – Microsoft Excel Tips

In this posting we are going to explore about how to use CHOOSE function. First we will understand how CHOOSE function works. Let us discuss on the syntax of this function:

CHOOSE (index_num, value1, [value2], …)

The CHOOSE function syntax has the following arguments: Index_num    – This is a mandatory input. It must be a number between 1 and 254, or a formula or reference to a cell containing a…

Hiding Charts, Shapes and Pictures in Excel – Microsoft Excel Tips

We all are familiar how to hide rows, columns and even sheets in excel. In this posting we are going to explore on options how to hide graphical objects like charts, pictures, SmartArt, shapes which are present in an excel sheet. There are some times we would like to share the report which contains only the data. What if we already have some graphical objects like charts, pictures etc. In this…

HOW TO USE MAXA AND MINA FORMULAS IN EXCEL – MICROSOFT EXCEL TIPS

In this post we are trying to understand how to use MAXA and MINA formulas in excel. MAX and MIN formulas will help us to find out the maximum and minimum in a set of cells.

MAX and MIN gives you proper result only if all the cells contain numbers. If your data contains a mix of numbers, text and logical values(TRUE/FALSE) then MAX and MIN will provide us an erroneous result, whereas MAXA and…

USING FIND FORMULA EFFECTIVELY – Microsoft Excel Tips

In this posting we are trying to understand how to use the FIND formula in excel effectively. FIND formula will help us to find out an occurrence of a particular character or a group of characters or a text with in a sentence.

Let’s understand how the FIND function works: =FIND (FIND_TEXT, WITH_IN_TEXT, STARTNUM)

FIND_TEXT –First input is which character / text you want to search in the text /…

VLOOKUP ON A HORIZONTAL DATA TABLE – Microsoft Excel Tips

If you are already a user of VLOOKUP, you should be aware that VLOOKUP formula can be used only on a vertical data structure. Suppose we have horizontal data structure, from which we need to extract the data using some reference value like emp id or product code etc.

Commonly found methods used by many users are, using HLOOKUP formula or copy and use Paste Special option TRANSPOSE to change the…

Create Your Own Tab in Excel 2010 / 2013 – Microsoft Excel Tips

Based on the field of work and type of data we use certain features of excel frequently. If you are using Excel 2010 or greater then you can now add new tabs to the Excel ribbon. When you create a tab, you can place those frequently used features on it, so that you do not have move around to different tabs to access them. Custom tab created by yourself will house everything you need to work on…

How to use WATCH WINDOW in Excel – Microsoft Excel Tips

Let us take a situation to understand, how to use the WATCH window feature of excel effectively. You have an excel file which contains 13 sheets in total, for every month data you have provided a sheet and 13th sheet you have the consolidated data. You have a cell value in the 13th sheet, if you change it, all the 12 sheet formulas are going to be affected. Taking this situation, if you really…

HOW TO USE YEARFRAC FUNCTION – MICROSOFT EXCEL TIPS

In this posting we will discuss about, how to use YEARFRAC formula to find out the number of years between two dates, including the decimal portion representing partial years.

First we will understand the syntax of this formula:

=YEARFRAC(start_date, end_date, basis)

start_date    – starting date out of your two dates from which you want to find the difference end_date    – ending date out of your…

Using TIMEVALUE Function – Microsoft Excel Tips

In this posting we will discuss about, how to use TIMEVALUE formula to handle time type of data which are not getting formatted despite of using Time formats from FORMAT CELLS option.

First we will understand the syntax of this formula:

=TIMEVALUE(time_text)

Time_text   – A text data that represents a time in any one of the Microsoft Excel time formats; for example, “6:45 PM” and “18:45″ text…

How to use INDIRECT function? – Microsoft Excel Tips

In this posting we are going to learn how to use the INDIRECT function in excel. First we will understand how to use this function.

=INDIRECT (string_reference, [ref_style])

string_reference A textual representation of a cell reference / sheet reference.

ref_style Optional. It is either a TRUE or FALSE value. TRUE indicates that string_reference will be treated as an A1-style reference. FALSE…

How to use XOR formula in EXCEL 2013 – Microsoft Excel Tips

In this posting we are explaining how to use XOR function in EXCEL 2013. First we need to understand how we call this function, we need to call it as “Exclusive OR”. This formula is used for a complicated conditional requirements. Before going ahead, let us get a clarity what is the difference between XOR and OR.

OR function work in this concept. =OR (LOGICAL1, LOGICAL2,….) If anyone LOGICAL or…

Using Array Formula to find Nth Largest / Nth Smallest value of a particular category – Microsoft Excel Tips

In my last posting I was illustrating how to find condition based maximum and minimum using Database functions like DMAX and DMIN.

We are tweaking the last posting requirement a little bit so that it gives us the opportunity to learn one more concept in this posting.

Change in our requirement is, how to find 2nd / Nth largest value of a particular category or 2nd / Nth smallest value of a…

Finding Maximum / Minimum value of a particular category – Using Database Functions – Microsoft Excel Tips

When you need to find a condition based total or average or count the below listed Excel functions can be used. SUMIF, SUMIFS AVERAGEIF, AVERAGEIFS COUNTIF, COUNTIFS

But if the requirement goes like maximum/minimum value based on the condition, we do not have function like MAXIF or MINIF. That’s where the utilization of the Database function comes in to play.

Let us take a particular scenario to…

Using MID, SEARCH, LEN formulas to extract information from a combined text – Microsoft Excel Tips

In this post we are explaining how to use the text functions like MID, SEARCH and LEN. To understand these functions clearly we are taking a scenario.

Our scenario is to extract the required information from a combined text format. Please find below the screenshot to understand the combined text format.

Combined Text Format Required Result 1:

Use the formula to extract only the question Requir…

VLOOKUP Formula With Wild Characters – Excel Tips

Wildcard characters are mainly used when we search for certain types of files in our computer. In Microsoft Excel, we use these wildcard characters in filters, formulas even in VBA codings. Wildcard characters are used when you are searching text values based on its length or based on criterias like starting with / ending with / containing a specific character in a text value.

? and * are called…

Different Types Of Resources – Microsoft Project Tips

In Project, after entering tasks we need resources and materials to perform it. There are different types of resources which can be assigned for the tasks. Once you enter the details of the resource cost and availability, it helps you to track the progress on project cost and schedule.

If you want to view the complete details of the resources of a project, in Microsoft Project 2010, you can view…

Calculating Multiple Conditions Based Total Using SUMPRODUCT – Microsoft Excel Tips

SUMPRODUCT is widely used to perform complicated summarization based on multiple conditions. When you need to add two or three columns of data based on four different conditions, SUMPRODUCT can be a real deal.

We can do it through SUMIFS but it will be multiple SUMIFS which is added finally using SUM function to arrive to such kind of result. SUMPRODUCT will do the job much easily using two…