Can’t this also be done using comments in cells?? – Well Yes! Yes, you can use cell comments as well. The only drawback with comments is that it is displayed only when you hover the mouse over the cell. On the other hand, you can use Input Message in Excel to display a message whenever a cell is selected (using either mouse or keyboard). Something as shown below: Now whenever you either click on the cell or select it using the keyboard, it would display the message....

While there is no inbuilt method to do this specifically, there are some Excel features and formulas you can use to get this done. In this tutorial, I will show you 4 simple and easy ways to separate text and numbers in Excel. Let’s get to it! Separate Text and Numbers Using Flash Fill Below I have the employee data in column A, where the first few alphabets indicate the department of the employee and the numbers after it indicates the employee number....

Spell Check in Excel is often ignored given that Excel users often work with numbers as compared with text. But it is still one of the important checks one should have in place. As compared to MS Word or PowerPoint, where you can visually see a red underline below the word that has been misspelled, nothing of that sorts happen in Excel. Imagine a disastrous spelling error glaring out of your worksheet when you sent it to your client....

By Dynamic Hyperlinks I mean links that changes based on the selection (or any other user action) Suppose I have the data set as shown below: This is the back end data, and I have a summary sheet where I have drop downs where the person can select the Month. The idea is to update the hyperlink with the selections so that it takes the user to the right cell when the hyperlink is clicked....

In my last article, I wrote about mouse double-click tricks. In this tutorial, I will show you some amazing things you can do in Excel by using a combination of mouse and keyboard. Shift Rows/Columns/Cells Suppose you have three cells, and you want to shift the content of cell A1 to cell A3 (as shown below), how would you do it? Usually, we go for the longer approach with a minimum of 3 steps, where you copy and paste to achieve this....

But in some cases, you may want to apply conditional formatting to a cell or column based on values in another column. A simple example of this could be when I have the names of students and their scores in two separate columns, and I want to highlight those names where the students have scored less than 35. In this tutorial, I will show you to apply conditional formatting to one column based on the values in another column or cell....

While the concept of Area Under Curve is quite simple – which is to calculate the total area covered between the curve/line and the axis – there is no direct way to calculate this in Excel. But it isn’t too difficult as well! In this short tutorial, I will show you two ways you can use to calculate Area Under Curve in Excel. So let’s get started! Formula to Calculate Area Under Curve in Excel As I mentioned, there is no direct formula to calculate AUC, but we can calculate it using a helper column and a simple formula....

Date and Time in Excel are stored as numbers. This enables a user to use these dates and time in calculations. For example, you can add a specific number of days or hours to a given date. However, sometimes you may want these dates to behave like text. In such cases, you need to know how to convert the date to text. Below is an example where dates are combined with text....

One of the challenges while creating a dashboard is to present the analysis in limited screen space (preferably a single screen). Hence, it is important to make smart choices while creating the right chart. And here is where Bullet Charts score over others. Bullet charts were designed by the dashboard expert Stephen Few, and since then it has been widely accepted as one of the best charting representations where you need to show performance against a target....

These are helpful when the existing Excel functions are not enough. In such cases, you can create your own custom User Defined Function (UDF) to cater to your specific needs. In this tutorial, I’ll cover everything about creating and using custom functions in VBA. If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training. What is a Function Procedure in VBA? A Function procedure is a VBA code that performs calculations and returns a value (or an array of values)....

When to use Excel IF Function IF function in Excel is best suited for situations where you check whether a condition is TRUE or FALSE. If it’s TRUE, the function returns a specified value/reference, and if not then it returns another specified value/reference. What it Returns It returns whatever value you specify for the TRUE or FALSE condition. Syntax =IF(logical_test, [value_if_true], [value_if_false]) Input Arguments logical_test – this is the condition that you want to test....

Named ranges allow you to quickly name a cell or a range of cells so that instead of using the reference, you can use those names in the formulas. And when you get hooked to using named ranged, there is a possibility that you may end up creating a lot of named ranges and may want to delete some or all of them. In this short tutorial, I will show you two simple ways to delete named ranges in Excel....

When to use Excel LEN Function LEN function can be used when you want to get the total number of characters in a specified string. This is useful when you want to know the length of a string in a cell. What it Returns It returns a number that represents the number of characters in the specified string. Syntax =LEN(text) Input Arguments text – the string for which you want to find the number of characters....

When to use Excel SUM Function SUM function can be used to add all numbers in a range of cells. What it Returns It returns a number that represents the sum of all the numbers. Syntax =SUM(number1, [number2],…]) Input Arguments number1 – the first number you want to add. It can be a cell reference, a cell range, or can be manually entered. [number2] – (Optional) the second number you want to add....

Leading and trailing spaces in Excel often leads to a lot of frustration. I can’t think of a situation where you may need these extra spaces, but it often finds its way into the excel spreadsheets. There are many ways you can end up with these extra spaces – for example, as a part of the data download from a database, while copying data from a text document, or entered manually by mistake....

In some cases, you may want to insert an image in the comments boxes in Excel. For example, if you have a list of products, then inserting product images in the comment box could make your Excel sheet easy to use. As soon as the user would hover the mouse over a cell that has a comment, it would show the comment and the picture within it. I used this trick when I had a list of countries and I wanted to insert a flag image for each country in the cell comment....

A few days ago I was working with a dataset where I had multiple sheets full of data. I had to select the first 500 cells at one go from each worksheet and copy it. I then had to paste this data into a tool I was using. Now doing this manually isn’t hard, but since I had multiple worksheets, I started thinking of a faster way to do this....

You can easily add bullet points in a cell Excel (Yes.. easily). Until I knew this trick, I used to put a dash (-) or arrow greater than (») at the beginning of the text, and that could sometimes be a pain. I always wondered why was Excel devoid of such basic functionality. But that was just my ignorance. There is not one, but many ways to add bullet points in a cell in Excel....

However, in some of the cases, this long formula can be cut short by using the VLOOKUP function. Consider a scenario as shown below. You have a list of students and their marks in an exam. Now you need to assign a grade to each student, based on predefined criteria. Something as shown below: Now one way is to write a long nested IF function and waste your time. Other, more time efficient, is the VLOOKUP way....

Excel Drop Down Lists are intuitive to use and extremely useful in when you are creating an Excel Dashboard or a data entry form. You can create multiple drop-down lists in Excel using the same source data. However, sometimes, it is needed to make the selection exclusive (such that once selected, the option should not appear in other drop-down lists). For example, this could be the case when you are assigning meeting roles to people (where one person takes one role only)....