While most of the in-built features and default settings are meant to be useful and save time for the users, sometimes, you may want to change things a little. Converting your numbers into text is one such scenario. In this tutorial, I will show you some easy ways to quickly convert numbers to text in Excel. Why Convert Numbers to Text in Excel? When working with numbers in Excel, it’s best to keep these as numbers only....

It is possible to create a multi-category chart in Excel (as shown below). The trick is to arrange the data in a certain way, that makes Excel interpret as having multiple categories. Suppose I have the data for the monthly expenses I make on the food items (as listed below). The idea to create a chart for these items, and also categorize these as Fruits, Vegetables, and Packaged Food....

But at the same time, there are some simple things that are missing and often leads to a lot of frustration. For example, there are so many ways for you to quickly select cells and ranges but there was no way for you to deselect a range of cells after you have selected it. Imagine you have to highlight a range of cells and you accidentally end up selecting an extra row or column....

I remember spending a lot of time calculating factorials as a part of the probability calculations. But if you’re using Excel and somehow get into a situation where you need to calculate factorial, you don’t need to take out your pen and paper – a simple Excel formula would do that for you. What is a Factorial? If you already know this and are here just to know how to calculate it in Excel, jump to the next section....

A common scenario is where you want to plot X and Y values in a chart in Excel and show how the two values are related. This can be done by using a Scatter chart in Excel. For example, if you have the Height (X value) and Weight (Y Value) data for 20 students, you can plot this in a scatter chart and it will show you how the data is related....

Excel Crashed! Imagine working on that urgent report or that extremely important dashboard where you have done such a fantastic job. You are proud of yourself and all the hard work you have put into it. But you forgot a key step. You didn’t save the Excel file (gasp!!!) Ok, let me cut down on the dramatics and come straight to the point. Excel has a tendency to crash, especially when you need it not to....

Just like any other error in Excel, the #NAME error also occurs in specific situations (which I will cover in this tutorial), and there are some simple ways to find and fix the #NAME error. Let’s get to it right away! What causes the #NAME Error? When you use a formula and it gives you a NAME error, it means that the formula can not recognize something in it. Let’s have a look at some of the most common issues that can cause a name error to raise its ugly head in your spreadsheet....

You know something in Excel if you know how to use the VLOOKUP function. If you don’t, you better not list Excel as one of your strong areas in your resume. I have been a part of the panel interviews where as soon as the candidate mentioned Excel as his area of expertise, the first thing asked was – you got it – the VLOOKUP function. Now that we know how important this Excel function is, it makes sense to ace it completely to be able to proudly say – “I know a thing or two in Excel”....

Download Example File Watch Video – Goal Seek in Excel One Variable Data Table in Excel. Two Variable Data Table in Excel. Scenario Manager in Excel. Excel Solver. Goal Seek in Excel, as the name suggests, helps you in achieving a value (the goal) by altering a dependent value. For example, I have the data (as shown below), where I calculate the Monthly payment outflow with the given Loan Amount, Interest Rate and Number of Payments....

When to use Excel COLUMNS Function Excel COLUMNS function can be used when you want to get the number of columns in a specified range or array. What it Returns It returns a number that represents the total number of columns in the specified range or array. Syntax =COLUMNS(array) Input Arguments array – it could be an array, an array formula or a reference to a contiguous range of cells....

When to use Excel NOW Function NOW function can be used to get the current date and time value. It is useful when you want to update the time whenever someone opens a worksheet or when a calculation is dependent on current time. What it Returns It returns a serial number that represents the current date and time. The integer part of the serial number represents the date and decimal portion represents time....

One common scenario where you have to perform calculations with dates would be to find out how many years or months or dates have elapsed between two given dates. This could especially be useful if you want to calculate years of service of employees in your company (where you have their joining date and the end date). In this tutorial, I will show you some formula techniques that you can use to calculate the years of service in years (or years and months and days)....

My first instinct was to use the RANDBETWEEN function. I did a quick check with some random data in Excel, and it failed me – there were repetitions in the results. Here is what I got when I used the RANDBETWEEN function: So, I had to resort to the RAND function. It works well with a negligible probability of the numbers repeating (I tested it multiple times on more than 100,000 cells, and there were no repetitions....

The more you work with Excel, the more you would find yourself adjusting the row height and column width. But in some cases, you may want to lock the cell height and width so that a user cannot make any changes to it. One scenario where this may be needed could be when you have a fixed template that you share with other people and you don’t want them to mess up the formatting by changing the cell height/width....

One thing that often troubles new Pivot Table users is when they find that a Pivot Table Fields list (some people call this Pivot Table Fields menu) goes missing. You can’t do much with a Pivot table if you do not see the Fields list, and it’s not very intuitive on how to get it back (at least for new Excel users) In this short tutorial, I will show you two simple ways you can use to get back the Pivot Table Fields list....

Even for a short vacation or business trip, there is a lot of data management that needs to be done – travel details, bookings details, packing, pick-up/drop, getting all the documents in place, etc. To make it easier, here are some Excel templates you can use to be more organized while planning a vacation: Vacation Itinerary Template. Vacation Packing List Template. Vacation Shared Expense Calculator. You’ll still need to do a lot of preparations, but this template will make it easier for you to plan/manage your vacation....

When to use Excel COLUMN Function Excel COLUMN function can be used when you want to get the column number of a specified cell. What it Returns It returns a number that represents the column number of the specified cell. For example, =COLUMN(B4) would return 2, as B is the second column. Syntax =COLUMN([reference] Input Arguments [reference] – An optional argument that refers to a cell or range of cells....

When to use Excel ROW Function Excel ROW Function function can be used when you want to get the row number of a cell reference. What it Returns It returns a number that represents the row number of the specified reference. For example, =ROW(B4) would return 4, as it is in the fourth row. Syntax =ROW([reference]) Input Arguments [reference] – An optional argument that refers to a cell or range of cells....

In case you prefer reading over watching a video, below is the complete written tutorial. Sometimes in Excel, you may want to hide zero values in your dataset and show these cells as blanks. Suppose you have a dataset as shown below and you want to hide the value 0 in all these cells (or want to replace it with something such as a dash or the text ‘Not Available’)....

And I got one last week too. So I decided to create a tutorial and a video on it. It will save me some time and help the readers too. Below is a video where I show how to highlight the active row and column in Excel. In case you prefer written instructions, below is a tutorial with exact steps on how to do it. Let me first show you what we are trying to achieve....