VLOOKUP is my first love of Excel, it is great function to get the corresponding values from a table easily, however there is situation where we might want to get the cell color and font color along with lookup value. VLOOKUP is a function which will return only values, but not the cell formats. Thankfully Excel allows […]
Articles Tagged: Logical Formulas
How to Calculate Estimated Travel Time in Excel?
Case1: How many Days/Hours/Minutes it will take to travel 56,986 miles if a vehicle is traveling at constant speed of 12.5 miles per hour? Additional Information: During the travel time vehicle stops for 5 times with 18 hours as break each time Also consider the Overhead/Maintenance time of 100 hours for the one way trip […]
Excel Tip : How to Generate Unique Usernames using the IF() and COUNTIF() Functions
Today let’s learn how to generate unique usernames in Excel, even when you have duplicates in your data. Formulas Used 1) IF( condition, [value_if_true], [value_if_false] ) Learn More about IF() Parameters or Arguments condition The value that you want to test. value_if_true Optional. It is the value that is returned if condition evaluates to TRUE. […]
How to Flag Multiple Matches In Your VLOOKUP Formula?
This article will explore the Excel VLOOKUP Formula and show you how to combine it with an IF statement to flag if there are multiple lookup values matches in your data set. VLOOKUP is really great for pulling in data from a larger table in Excel, but sometimes in a larger data sets there may […]
Excel Formula : SUMPRODUCT () for Advanced Users
When I first learned about the SUMPRODUCT formula in Excel , I almost dismissed it as a useless formula used by only few users. How often would anyone need to multiply 2 or more groups of numbers together and add up the results!? To get to the story, we know that formula is keyed in as: […]
How to Define a Named Range in Excel? What are the top 3 uses of Named Ranges?
Another underused feature of Excel is Named Ranges. Named Ranges allow you to assign a meaningful name to a cell or a range of cells and then use that name instead of the actual cell reference in your formulas. A name is easier to remember when you are constructing your formulas.
How to select cells that only contain Text, Number or Error in Microsoft Excel
At times you may want to select the cells that have specified type of data in it, by which you can distinguish between cells that contain different types of data, which allows you to delete, fill or lock cells by the data type for various other reasons.
Bonus Tip: How to use formulas to highlight the cells that only contain Text, Number or Errors
How to Highlight a Row in Excel Using Conditional Formatting
You can use conditional formatting to format or color the cells based on their contents. For example, you have a cell to turn red when it contains text ‘No’ or a value less than 90%. But how do you highlight an entire row? When a cell contain specified content.
This tutorial will help you learn how to highlight other cells based on a cell’s value? To see how this was done, read on.
How to extract initials from names in Excel?
Here I introduce few tricks to quickly extract initials from the name list in Excel, like TCS for Tata Consultancy Service and JFK for John F Kennedy. Check in here to learn how to do it.
How to get the Last Numeric Value in a Column?
Returning the final value in a column is particularly useful for spreadsheets that involve running balances, where you want to reference the final balance regardless of whether it is the smallest or largest value in the column.