When to use Excel RIGHT Function RIGHT function can be used to extract text from right of the string. What it Returns It returns the specified number of characters from the right of the string. Syntax =RIGHT(text, [num_chars]) Input Arguments text – The text string that contains the characters you want to extract. [num_chars] – (optional) the number of characters to extract from the right of the text string. If you omit this, is defaults to 1....

What do you do when you have to copy a range of cells in Excel and paste it somewhere else? In most cases, the below three steps get the work done: But what if you have some hidden cells in the dataset? Then – these above three steps are not enough. Let me show you what happens when you try to copy cells that have hidden rows/columns in it. Suppose you have a dataset as shown below:...

Running total (also called cumulative sum) is quite commonly used in many situations. It’s a metric that tells you what’s the sum of the values so far. For example, if you have the monthly sales data, then a running total would tell you how much sales have been done till a specific day from the first day of the month. There are also some other situations where running total is often used, such as calculating your cash balance in your bank statements/ledger, counting calories in your meal plan, etc....

For example, in the dataset below, I can easily spot which are the months when the sales were low (highlighted in red) as compared with other months. In the above dataset, the colors are assigned based on the value in the cell. The color scale is Green to Yellow to Red with high values getting the green color and low values getting the red color. Creating a Heat Map in Excel While you can create a heat map in Excel by manually color coding the cells....

These interactive controls (such as a check box, radio button, combo box, scroll bars) are quite useful when you are creating interactive workbooks or excel dashboards. But there is a possibility that you may not have the Developer tab available in the ribbon. If that happens, don’t panic – and keep reading how to get the developer tab. Excel Developer Tab.. Where to Find it? Don’t worry if you can’t find the Excel developer tab in the ribbon....

Excel Paste Special functionality can save a lot of time. One of its features is ‘Operations’, which allows us to quickly perform mathematical calculations (such as addition, subtraction, multiplication, and division) on a range of cells. In this tutorial, I will show you how to multiply in Excel using paste special operators options. These come in handy when you want to avoid applying formulas or using helper columns. Suppose you have the data set as shown below....

You can specify the delimiter (such as a space, comma, or tab) and the Text to Columns would use this delimiter to split the content of the cells. Examples of this include splitting first and last names, or username and domain name in email ids. However, if you have a dataset where the delimiter is a line break (in the same cell), it gets tricky to split these multiple lines in the same cell into separate cells/columns....

Replace is a text function that allows you to quickly replace a string or a part of the string with some other text string. This can be really useful when you’re working with a large dataset and you want to replace or remove a part of the string. But the real power of the replace function can be unleashed when you use it with other formulas in Excel (as we will in the examples covered later in this tutorial)....

Maybe you want to create a back-up worksheet, or you want to create a new worksheet but don’t want to redo the formatting. The long (and inefficient) way is: Right-click on the worksheet tab for which you want to create a copy. Select the Move or Copy option. Check the “Create a Copy” check box. Click OK. This will create a copy of the worksheet. While this is a perfectly fine way to copy a worksheet in Excel, it’s not the most efficient way of doing this....

Since this is quite common, I thought of writing a tutorial just to tackle the #REF! error. In this Excel tutorial, I will cover what is the #REF! error, what causes it, and how to fix it. What is #REF! Error in Excel? A reference error is something you get when your formula doesn’t know what cell/range to refer to or when the reference is not valid. The most common reason you may end up with the reference error is when you have a formula that refers to a cell/row/column and you delete that cell/row/column....

While it would have been great had there been only a few possible reasons for malfunctioning formulas. Unfortunately, there are too many things that can go wrong (and often does). But since we live in a world that follows the Pareto principle, if you check for some common issues, it’s likely to solve 80% (or maybe even 90% or 95% of the issues where formulas are not working in Excel)....

In VBA, loops allow you to go through a set of objects/values and analyze it one by one. You can also perform specific tasks for each loop. Here is a simple example of using VBA loops in Excel. Suppose you have a dataset and you want to highlight all the cells in even rows. You can use a VBA loop to go through the range and analyze each cell row number....

There are only 3 Excel wildcard characters (asterisk, question mark, and tilde) and a lot can be done using these. In this tutorial, I will show you four examples where these Excel wildcard characters are absolute lifesavers. Excel Wildcard Characters – An Introduction Wildcards are special characters that can take any place of any character (hence the name – wildcard). There are three wildcard characters in Excel: Note: I have not come across many situations where you need to use ~....

If you have a huge dataset and the comments are scattered all over the worksheet, it may help to have all comments in a single place as a list. Get a List of All the Comments in a Worksheet In this tutorial, I will show you how to use a VBA code to get a list of all the comments in a Worksheet in a separate worksheet. Something as shown below:...

Using a combination of Excel functions and the date of birth, you can easily calculate age in Excel. You can either calculate the age till the current date or between the specified period of time. The technique shown here can also be used in other situations such as calculating the duration of a project or the tenure of the service. How to Calculate Age in Excel In this tutorial, you’ll learn how to calculate age in Excel in:...

Excel has some powerful functions to calculate the number of days between two dates in Excel. These are especially useful when you’re creating Gantt charts or timelines for a proposal/project. In this tutorial, you’ll learn how to calculate the number of days between two dates (in various scenarios): Calculating the Total Number of Days Between Two Dates in Excel Excel has multiple ways to calculate the days between two dates....

If you don’t want them to change anything, you have the option to either protect the entire worksheet/workbook or protect certain cells that have important data (that you don’t want the user to mess up). But even when you protect the worksheet, the end-user can still click on a cell and see the formula that’s used for calculations. If you want to hide the formula so that the users cannot see those, you can do that as well....

Since your Pivot Table is created using the Pivot Cache, when the existing data changes or when you add new rows/columns to the data, the Pivot Cache does not update itself automatically, and hence, the Pivot Table also does not update. You need to force a refresh every time there are changes. Once you force a refresh, the Pivot Cache gets updated, which is reflected in the Pivot Table. This tutorial covers a couple of ways to do this....

And when working with multiple sheets, you may sometimes need to change their names. In this Excel tutorial, I will show you some simple ways to rename a sheet in Excel (including a keyboard shortcut). And in case you want to rename all the sheets as one go (such as adding a prefix or suffix to all the sheets), check out the section on using VBA to do this (it’s not complicated)....

If you’re new to Excel and wondering how to subtract in Excel, you’re at the right place. In this tutorial, I will show you how to subtract in Excel (subtract cells, ranges, columns, and more). I will start with the basics and then would cover some advanced subtraction techniques in Excel. I also cover how to subtract dates, times, and percentages in Excel. So let’s get to it! Subtracting Cells/Values in Excel Let’s start with a really simple example, where I have two values (say 200 and 100) and I want to subtract these and get the result....