In such cases, it’s good to know how to manipulate text data and get the desired result. One of the most common tasks most Excel users have to do is work with a dataset of names. Often you’ll find that the first name and the last name are in separate columns, and you may have a need to combine these first and last names and get these as a combined name in a cell....

This will enable you to easily show and hide different areas of the worksheet so that only relevant data is visible. In this tutorial, I will show you three methods to group columns in Excel. Note: Ensure that the worksheet does not have any hidden columns before applying any of the following methods. Method #1: Select the Columns to be Grouped and Apply the Group Command When we use this method, we first select the columns that we want to group and then apply the Group command....

One of the most used ways is using the Merge & Center option in the Home tab. The issue with using Merge & Center is that it can merge the cells, but not the text within these cells (i.e., you lose some data when you merge the cells). Let’s say we have a data set as shown below: If I select cell A1 and B1 and use the Merge & Center option, it will keep the text from the left-most cell (A1 in this case) and but you will lose the data from all other cells....

Using VBA Macros in Excel can be a huge time saver. You can automate a lot of repetitive tasks and create new functions and functionalities in Excel with simple VBA macro codes. But in some cases, you may want to remove all the macros from an Excel workbook (or delete specific macros only). This may be the case when you get a workbook from someone else and you want to make it macro-free, or when you’re sending a file with macros to someone and the receipt doesn’t need these in the workbook....

While there is no way to do this using inbuilt functionalities in Excel, it can easily be done using VBA. Something as shown below: In this tutorial, I will give you the VBA code and show you the exact steps to get this done in Excel. Click here to download the example file and follow along. Let me first give you the VBA code that will select every third row in the dataset that you have selected....

When to use Excel COUNT Function COUNT function can be used to count the number of cells that contain numbers. What it Returns It returns a number that represents the number of cells that contain numbers. Syntax =COUNT(value1, [value2], …) Input Arguments value1 – the first item, cell reference, or range within which you want to count numbers. [value2], … – (optional) up to 255 additional items, cell references, or ranges within which you want to count numbers....

When to use Excel COUNTA Function COUNTA function can be used when you want to count all the cells in a range that are not empty. What it Returns It returns a number that represents the number of cells that are not empty. Syntax =COUNTA(value1, [value2], …) Input Arguments value1 – the first item, cell reference, or range within which you want to count the number of cells that are not empty....

A basic understanding of fill handle in Excel could save you some time and make you more productive. What’s this Fill Handle in Excel? ‘Fill Handle’ is a tool that you can use to autocomplete lists in Excel. For example, if you have to enter numbers 1 to 20 in cell A1:A20, instead of manually entering each number, you can simply enter the first two numbers and use the fill handle to do the rest....

Data forms the backbone of any analysis that you do in Excel. And when it comes to data, there are tons of things that can go wrong – be it the structure, placement, formatting, extra spaces, and so on. In this blog post, I will show you 10 simple ways to clean data in Excel. #1 Get Rid of Extra Spaces Extra spaces are painfully difficult to spot. While you may somehow spot the extra spaces between words or numbers, trailing spaces are not even visible....

To handle errors, Excel has provided a useful function – the IFERROR function. Before we get into the mechanics of using the IFERROR function in Excel, let’s first go through the different kinds of errors you can encounter when working with formulas. Types of Errors in Excel Knowing the errors in Excel will better equip you to identify the possible reason and the best way to handle these. Below are the types of errors you might find in Excel....

When to use Excel NETWORKDAYS.INTL Function Excel NETWORKDAYS.INTL function can be used when you want to get the number of working days between two given dates. It does not count the weekends and holidays, both of which can be specified by the user. It can be used when you want to calculate the benefits accrued to employees over time. It also enables you to specify the weekend (for example, you can specify Friday and Saturday as the weekend, or only Sunday as the weekend)....

Now don’t confuse gridlines with borders. Gridlines are visible on the entire worksheet while borders can be applied to the entire worksheet or to a selected region in the worksheet. You can change border settings such as color, width, style, etc., but in the case of gridlines, you get limited options to change the look of the gridlines. Working with Gridlines in Excel In this tutorial, you’ll learn: How to remove gridlines from the entire worksheet....

And one of the common tasks was to combine the data in such a way that there were no duplicate records. For example, below is a dataset that multiple records for the same region. And the final result needs to be a consolidated dataset where each country is reported only once. In this tutorial, I will show you how to combine duplicate rows and sum the values to create a single consolidated dataset....

As usually is the case, there were thousands of rows of data. There is no way she could have used Excel in-built charts as that would have taken her ages to create charts for each set of data points. This got me thinking, and fortunately, conditional formatting came to the rescue. I was able to quickly create something neat that fits the bill. Suppose you have sales data for 12 months for three products (P1, P2, and P3)....

If you prefer written instruction instead, below is the tutorial. Hidden rows and columns can be quite irritating at times. Especially if someone else has hidden these and you forget to unhide it (or even worse, you don’t know how to unhide these). While I can’t do anything about the first issue, I can show you how to unhide columns in Excel (the same techniques can also be used to unhide rows)....

One of the trainers was a 40 something guy with awesome Excel skills. I used to call him the Excel Superman. I still remember the first thing that he told us was to avoid the use of the mouse as much as possible and use the keyboard instead. Till date, I completely agree with him. If you want to be super fast in Excel, start using the keyboard. BUT… Don’t dump the Mouse....

As the name suggests, you can use conditional formatting in Excel when you want to highlight cells that meet a specified condition. It gives you the ability to quickly add a visual analysis layer over your data set. You can create heat maps, show increasing/decreasing icons, Harvey bubbles, and a lot more using conditional formatting in Excel. Using Conditional Formatting in Excel (Examples) In this tutorial, I’ll show you seven amazing examples of using conditional formatting in Excel:...

When to use Excel OR Function Excel OR function can be used when you want to check multiple conditions. What it Returns It returns TRUE if any one of the conditions evaluates to TRUE, else it returns a FALSE. Syntax =OR(logical1, [logical2],…) Input Arguments logical1 – the first condition that you want to evaluate for TRUE or FALSE. [logical2] – (Optional) This is the second condition that you want to evaluate for TRUE or FALSE....

In this tutorial, I will show you how to calculate IRR in Excel, how is it different from another popular measure NPV, and different scenarios where you can use in-built IRR formulas in Excel. Internal Rate of Return (IRR) Explained IRR is a discount rate that is used to measure the return of an investment based on periodical incomes. The IRR is shown as a percentage and can be used to decide whether the project (an investment) is profitable for a company or not....

This is mostly because dates can be formatted in many different ways in Excel. So while you may see two dates in Excel and think those are the same, there is a possibility that these might be different values in the back end (or vice versa, you may think two cells have different dates, and it may be the same). In this tutorial, I will show you a couple of techniques that you can use to compare dates in Èxcel....