A simple example where you need to split cells in Excel is when you have full names and you want to split these into first name and last name. Or you get address’ and you want to split the address so that you can analyze the cities or the pin code separately. How to Split Cells in Excel In this tutorial, you’ll learn how to split cells in Excel using the following techniques:...

When to use Excel RANDBETWEEN Function RANDBETWEEN function can be used when you want to generate evenly distributed random numbers between a top and bottom range specified by the user. What it Returns It returns a number between the top and bottom range specified by the user. Syntax =RANDBETWEEN(bottom, top) Input Arguments bottom – The smallest integer RANDBETWEEN will return. top – The largest integer RANDBETWEEN will return. Additional Notes RANDBETWEEN is a volatile function and should be used with caution It recalculates whenever the excel workbook is open or whenever a calculation is triggered in the worksheet....

When working with Excel, there are some small tasks that need to be done quite often. Knowing the ‘right way’ can save you a great deal of time. One such simple (yet often needed) task is to number the rows of a dataset in Excel (also called the serial numbers in a dataset). Now if you’re thinking that one of the ways is to simply enter these serial number manually, well – you’re right!...

And since you’re reading this article, I can assume that it’s not happening with your Excel. One common issue with arrow keys in Excel is that it moves the worksheet, but the cursor doesn’t move. It’s as if the cursor is stuck in the cell and doesn’t move with the arrow key, but the worksheet page scrolls. And it has a really easy fix – the scroll lock. In this short tutorial, I will show you how to fix when arrow keys are not working as expected in your Excel file....

There is no inbuilt function in Excel to extract the numbers from a string in a cell (or vice versa – remove the numeric part and extract the text part from an alphanumeric string). However, this can be done using a cocktail of Excel functions or some simple VBA code. Let me first show you what I am talking about. Suppose you have a data set as shown below and you want to extract the numbers from the string (as shown below):...

A trendline, as the name suggests, is a line that shows the trend. You’ll find it in many charts where the overall intent is to see the trend that emerges from the existing data. For example, suppose you have a dataset of weekly visitors in a theme park as shown below: If you plot this data in a line chart or column chart, you will see the data points to be fluctuating – as in some weeks the visitor numbers decreased as well....

Excel allows you to format dates in many different ways. You can choose to show the date in a short date format or in a long date format. You can also only show the day number, the month name, or the year from a given date. In this short Excel tutorial, I will show you some easy methods to get the month name from a date in Excel. So, let’s get started!...

Making VLOOKUP Case Sensitive Suppose you have the data as shown below: As you can see, there are three cells with the same name (A2, A4, and A5) but with a different letter case. On the right (in E2:F4), we have the three names (Matt, MATT, and matt) along with their scores in Math. Excel VLOOKUP function is not equipped to handle case-sensitive lookup values. In this above example, no matter what lookup value case is (Matt, MATT, or matt), it’ll always return 38 (which is the first matching value)....

By default, this setting is disabled, which means that when you print a worksheet, the comments are not printed. While Excel provides a couple of options to print comments, it is not readily available in the ribbon. Print Comments in Excel using the Page Setup from Ribbon If you have a lot of comments in a worksheet and you print it, by default the comments would not get printed. You need to make a few changes in the settings to get this done....

Excel gives you a lot of options when you’re trying to print your work. You can choose to print the entire worksheet, a specific area in the worksheet, print multiple sheets, or all sheets at one go. In this tutorial, I will show you how you can print multiple sheets in Excel at one go. These could be some selected sheets or all the sheets in the workbook. And in case you want to print a specific area in multiple/all sheets, you can do that too with a little bit of VBA magic....

In this detailed guide, I will cover all that you need to know to get started with recording and using macros in Excel. If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training. What is a Macro? If you’re a newbie to VBA, let me first tell you what a macro is – after all, I will keep using this term in the entire tutorial....

If you work with Excel day in and day out, Excel Keyboard Shortcuts can help you shorten your workday and save a lot of time. Additionally, it also has the power to enchant your co-workers and boss into thinking you’re an Excel Wizard. When I started my career as an analyst, I was completely in awe with one of my colleagues who was super-fast while working with Excel. He always seemed to have a keyboard shortcut for everything....

It gives you quick access to some of the commonly used data analysis functionalities in Excel so that you don’t have to go into the ribbon and find the correct option (hence saving some clicks and a little bit of time). In this tutorial, I will tell you everything you need to know about the Quick Analysis Tool in Excel. I’d also cover different use cases where you can use this tool to be more productive and save time....

When to use Excel COUNTBLANK Function COUNTBALNK function can be used when you have to count all the empty cells in a range. What it Returns It returns a number that represents the number of cells that are empty. Syntax =COUNTBLANK(range) Input Arguments range – it is the range in which you want to count the blank cells. Additional Notes Cells with formulas that return “” (empty text) are considered blank and are counted....

When to use Excel RAND Function RAND function can be used when you want to generate evenly distributed random numbers between 0 and 1. What it Returns It returns a number between 0 and 1. Syntax =RAND() Input Arguments RAND function does not take any input arguments. It is used with empty parenthesis. Additional Notes RAND is a volatile function and should be used with caution. It recalculates whenever the excel workbook is open or whenever a calculation is triggered in the worksheet....

Below is an example where I have some address in column A and these address overflow to the adjacent cells as well. And in case you have some text in the adjacent cell, the otherwise overflowing text would disappear and you will only see the text that can fit the cell column width. In both cases, it doesn’t look good and you may want to wrap the text so that the text remains within a cell and not spill over to others....

Excel is an amazing tool when it comes to slicing and dicing text data. There are so many useful formulas as well as functionalities you can use to work with text data in Excel. One of the very common questions I get about manipulating text data is – “How to separate first and last names (or first, middle, and last names) in Excel?“. There are a couple of easy ways to split names in Excel....

What comes to your mind when you see text with a strikethrough in Excel (something as shown below)? In most cases, it means that the task or activity has been completed and has been checked off. Those who work with Microsoft Word use this extensively, and there is strikethrough icon right there in the ribbon. However, there is no icon in Excel ribbon to do this. In this tutorial, I will share various ways to access the strikethrough option and apply it to text in Excel....

This triad enables you to get into the contents in a cell. and analyze each character separately. For example, suppose you have Excel123 in a cell, and you want to identify if it contains a numeric value or not (which it does!!). Excel inbuilt formulas can not help you here as excel consider this as text (Try and use Type() function to see for yourself). What you need here is a way to check each character separately and then identify if it contains a number....

Wouldn’t it be great if there was a function that could count colored cells in Excel? Sadly, there isn’t any inbuilt function to do this. BUT.. It can easily be done. How to Count Colored Cells in Excel In this tutorial, I will show you three ways to count colored cells in Excel (with and without VBA): #1 Count Colored Cells Using Filter and SUBTOTAL To count colored cells in Excel, you need to use the following two steps:...