Influence Professionals

Now you might be already aware of the countif function that most people use for their reports and excel sums. Again as I have explained in my previous blog about sumifs function ,   the ‘ countif’  function can fetch data using one exception whereas ‘ countifs’  can match up to 29 criteria.  Here, i will demonstrate you this function with the simple example: Example1 . Below is the table we will be working on in this tutorial: As you see in the above table, we have Year , Product , country , and total Sales . From this data table, we want to get our total sales using different criteria available in this table.  In the above table, we need count of Red trolley Bags in the year 2019 Here is the formula syntax: COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2, criteria_range3, criteria3, … criteria_range_n, criteria_n] )” In the above example, since we are looking for count of product Red trolley bags in 2019 . Next, we have the first...

This must have occurred with you multiple times in the office while preparing excel reports when you need to bring particular values from the massive chunk of data based on specific criteria and requirements. Essentially, if you ever want to make a sum with exceptions and specifically, if these exceptions are more than one which is typically the case in actual life. This is when 'sumifs' function in excel comes handy. Now you might be already aware of the ' sumif'  function that most people use for their reports and excel sums. However, ' sumifs'  is there in the system since Excel 2007 version, and many of us may have just skipped this. It is not only a time-saving function but also assists you to fetch data from large data arrays using several criteria in one go. while the ' sumif'  function can fetch data using one exception whereas ' sumifs'  can match up to 29 criteria.  Here, i will demonstrate you this function with the simple example: E...

This is a very fascinating feature of excel to visualize your data and make it more constructive and impressive to the viewer. Let’s begin: We will be using the below table to demonstrate how to add icons to your data in excel Assume we want to ascertain our target status in the above table more visual with the help of  icon Sets  from the format conditioning Here is the code table. For instance, we have assigned -1 to depict target status as  Not Met , 0 to show  Met  and 1   if the target is  exceeded.  And   we want to assign format icons to these classes as per the below table: Now we have to convert Target Status numbers to text values ( Met, Not met & Exceeded) which we defined earlier and, we will do the same with the help of custom formatting. Step 1. Select the entire column and right-click. Select  Format Cells  from the available options  (Refer to the below screenshot) Step 2 . A new window will appear. Please...

Excel is a sea of very useful and compelling features. You just need to give proper time to explore and operate it in the best possible way. Here I am going to demonstrate how to portray your percentage data in excel in a way that not only looks professional but also, a great way to impress your colleague and boss with your reporting skills.  Let’s begin: Below is the sample table which we are carrying here to demonstrate this tool to you So, in this data table, in the ' Difference'  column we want to exhibit increase\decrease arrows to show if the sales went above the defined target or down. Step 1 . Go to the ' Insert'  tab from the menu and select ' Symbols'  from the right side. ( Refer to the below image) Step 2. A new dialogue box will appear with multiple options. Just choose required titles as shown in the below image and add both up & down arrows to your excel sheet. (Refer to the below image) Once the selection is complete, add both the symbols one...

VLOOKUP is an exceptionally impressive tool in Excel which allows you to locate values in a table or range by row. It is very advantageous  when you need to work on a huge chunk of data and find things from the other sheets or workbooks. Here is the formula =VLOOKUP (What is the source reference value, from where you want to reference it with and pick the desired data, Column range for the desired value, Add 0(False) if you need exact outcome or add 1(True) if you need approximate value). You need to keep a few things in mind before I will explain it through an example. The reference value is also called the Lookup Value, and It should always be in the first column in the lookup range to work this formula without an error. Sample1 . Now take the example of the below table. Suppose we have two sheets ( Sheet1 & Sheet2 )    Sheet 1 Sheet 2 As per the above Image, we have huge data in sheet1 with multiple data fields, and sheet2 only has a name. Now we want to...

Excel is an excellent tool for Data entry, analysis, programming, accounting, task management and much more.  It is up to our aptitude and understanding how we want to use this robust tool to examine and cultivate our data. Here I will demonstrate about simple IF function and how you can use it more effectively to get the desired results. Example 1 : Suppose you want to analyze some financial values for the entire year, and you have a budgeted amount and the actual invested amount. Now you need to see if you have gone under the allotment cost or over budget during the whole year. Here is the sample data: Sample Data Table Now you want to see if you have over-spent or stayed under the specified cost. Here is the formula. IF Formula for the Sample Table As per this formula, if <B2> (Budgeted Amount) is less than <C2> (Total Spending) then it should say Over Budget , else Under Budget Sample Table with IF Formula Very straightforward. Isn’t it? Let’s try some more sampl...