It also makes it really easy when you want to find out the total number of days between two dates, as you can simply subtract one from the other. In this tutorial, I’ll show you a couple of really easy ways to add or subtract days to dates in Excel. So let’s get started! Add or Subtract Days to Dates in Excel Using Paste Special Recently, I was working with one of the content writers on one of my projects....

Now, you are the one who gets this data, and you have to filter all these records that have a bold formatting. For example, suppose you have the data set as shown below, and you want to filter all the cells that have been formatted in bold font. Let’s face it. There is no straightforward way of doing it. You cannot simply use an Excel filter to get all the bold cells....

So your formula may look like something as shown below: Note that the part highlighted in yellow is the external link (also called external reference). This part of the formula tells Excel to go to this workbook (Score.xlsx) and refer to the specified cell in the workbook. The benefit of having an external link in your formula is that you can automatically update it when the data in the linked workbook changes....

For example, to select random winners from a list or to get a random list of numbers for data analysis or to create random groups of students in class. In this tutorial, you will learn how to generate random numbers in Excel (with and without repetitions). Generate Random Numbers in Excel There are two worksheet functions that are meant to generate random numbers in Excel: RAND and RANDBETWEEN. RANDBETWEEN function would give you the random numbers, but there is a high possibility of repeats in the result....

While you can always manually enter the sheet name, it won’t update in case the sheet name is changed. So if you want to get the sheet name, so that it automatically updates when the name is changed, you can use a simple formula in Excel. In this tutorial, I will show you how to get the sheet name in Excel using a simple formula. Get Sheet Name Using the CELL Function CELL function in Excel allows you to quickly get information about the cell in which the function is used....

You can hide a worksheet and the user would not see it when he/she opens the workbook. However, they can easily unhide the worksheet if they want (as we will see later in this tutorial). But what if you don’t want them to be able to unhide the worksheet(s). To do that, you need to take a couple of additional steps to make sure the worksheets are ‘very hidden’ (also covered later in this tutorial)....

Sometimes you want it, and sometimes you don’t. While Excel has been programmed in such a way that it automatically removes any leading zeros from the numbers, there are some cases when you may have these. In this Excel tutorial, I will show you how to remove the leading zeros in your numbers in Excel. So let’s get started! Possible Reasons You May Have Leading Zeros in Excel As I mentioned, Excel automatically removes any leading zeros from numbers....

And as more advanced tools such as Powe Query and Power Pivot now rely on Excel tables to be the data source, Excel tables are going to become even more important. But this tutorial is not about the awesomeness of Excel Tables, rather it’s about something that a lot of people find frustrating about it – the formatting. When you convert a data range into an Excel table, Excel automatically applies some formatting to it (you can, of course, customize or remove the table formatting)....

And one of the common tasks most Excel users have to do is to go to the end of the data in the column (i.e., the last filled cell). While you can quickly go to the end of the column to the last filled cell, or select the entire column till the last filled cell with an easy keyboard shortcut, things can get a bit complicated if you have blank cells in the column....

And one of the things I had to do while cleaning the data was to delete any blank columns in the data set. While you can always manually select columns and delete them one by one, doing so in a large data set, where you have tens or hundreds of columns in every data set, would be inefficient and error-prone. While there is no inbuilt functionality in Excel to date blank columns in one go, this can be achieved by using a combination of different functionalities....

For example, if you have an address, you can use the VBA Split function to get different parts of the address that are separated by a comma (which would be the delimiter in this case). SPLIT is an inbuilt string function in Excel VBA that you can use to split a text string based on the delimiter. Excel VBA SPLIT Function – Syntax Expression: This is the string that you want to split based on the delimiter....

Usually, the first column (or the first few columns) has the headers for each row. And when you scroll to the right of your worksheet, it would be helpful if the headers in the column were visible. When they are visible all the time, you do not have to keep scrolling back to see what the values further across the worksheet mean. This saves effort and time. This is where the ability to freeze columns comes in handy....

But don’t worry. A cool bunch of excel functions (or a little bit of VBA if you’re feeling fancy) can easily do this for you. In this tutorial, I will show a couple of ways to count words in Excel using simple formulas. And at the end, will also cover a technique to create a custom formula using VBA that will quickly give you the word count of any text in any cell....

It’s also a useful tool when I’m sharing my files with other people, as I can restrict data entry in some cells and only allow the selection to be made from the drop-down. However, in some cases, you may have a need to remove the drop-down from your Excel file. This could be because you no longer need the drop-down, or you want to enter something else other than the options in the drop-down....

For example, if you have the sales data for a month, you may want to know the total sales that have happened between two given dates or the total sales made on weekends vs weekdays. One of the ways to quickly get these answers is by using the SUMIFS function. SUMIFS (as the name suggests), allows you to sum a range based on criteria. Within SUMIFs, you can specify multiple conditions and it will sum only those cells/values that meet all the conditions....

In Excel. there are text functions that allow you to quickly change the case of the text (to lower case, upper case, or proper case). Below is an example of each type of case: Excel UPPER Function – Overview UPPER function is one of the many text function in Excel. What does it do? It takes a string as the input and converts all the lower case characters in that text string to upper case....

If you wonder what was my first post, here it is – Trump Excel’s First Post. Now coming back to cool Excel stuff, in this post I am sharing an amazing Excel Template that can help you manage your daily/weekly tasks with ease (and its fun to use) – TASK MATRIX Before you jump away and download it, here is some background to this template. Have you heard of 7 Habits of Highly Effective People?...

As it’s name suggests, it is used to split the text into multiple columns. For example, if you have a first name and last name in the same cell, you can use this to quickly split these into two different cells. This can be really helpful when you get your data from databases or you import it from other file formats such as Text or CSV. In this tutorial, you’ll learn about many useful things that can be done using Text to Columns in Excel....

If yes, then you may find the AutoFormat option useful in speeding up your formatting work. It allows you to quickly apply a preset formatting on a data set that has one header row and one header column. It allows you to quickly apply a preset formatting on a data set that has one header row and one header column. So you quickly get a result as shown below:...

When to use Excel WORKDAY Function Excel WORKDAY function can be used when you want to get the date after a given number of working days. For example, if I start a project today and it will take 20 working days to complete it, then I can use the WORKDAY function to get the completion date. This function is best suited when you want to calculate the invoice due date, project due date, delivery date, etc....