You would notice that Excel will autocorrect that misspelled word to Drink (as shown below). Somehow, Excel knew that this is not the correct spelling and autocorrected it to the right one. Now, it won’t autocorrect all the misspelled words. Just a few! For example, try the word ‘dirnk’. It would not be auto-corrected. The reason some words are autocorrected and others aren’t is because there is already a fix list of words that are prefilled in Excel to autocorrect....

It can also be used to quickly calculate the time that had elapsed between today and the date when an event occurred. For example, if you were born on 10 December 1987, you can calculate your age using this formula =TODAY()-DATE(1987,12,10). What it Returns It returns a serial number that represents the current date. Syntax =TODAY() Input Arguments TODAY function does not take any input arguments. It is used with empty parenthesis....

Excel XLOOKUP function has finally arrived. If you have been using VLOOKUP or INDEX/MATCH, I am sure you’ll love the flexibility that the XLOOKUP function provides. In this tutorial, I will cover everything there is to know about the XLOOKUP function and some examples that will help you know how to best use it. So let’s get started! What is XLOOKUP? XLOOKUP is a new function is Office 365 and is a new and improved version of the VLOOKUP/HLOOKUP function....

One common task many Excel users have to do is to extract the last name from the full name. While it may seem like an easy task, it can get complicated (especially when you’re dealing with data that is not consistent). In this tutorial, I will show you five different ways to extract the last name from the full name in Excel. I will cover different scenarios, where you only have the first and the last name in the dataset, or the first, middle, and last name....

You don’t need to know any formula or coding to quickly crunch thousands of rows of data and create quick summaries out of it. All you need to know is how to drag and drop and a little bit of knowledge of how to create a Pivot Table. In this tutorial, I will show you various ways to delete a Pivot Table from Excel. How to Delete a Pivot Table in Excel Since you’re reading a tutorial about removing or deleting a Pivot Table, I am assuming you already have a Pivot Table(s) in place that you want to remove....

Sometimes, when you have the text data, you may want to extract the first word from the text string in a cell. There are multiple ways you can do this in Excel (using a combination of formulas, using Find and Replace, and using Flash Fill) In this tutorial, I will show you some really simple ways to extract the first word from a text string in Excel. Extract the First Word Using Text Formulas Suppose you have the following dataset, where you want to get the first word from each cell....

Sometimes, it’s so frustrating that a new page is printed just to accommodate a few extra lines of data (which also leads to wastage of paper). There are some simple techniques you can use to make sure you Excel data in printed on one page (or in less number of pages in case you’re printing a big report). In this Excel tutorial, I will share some methods you can use to print the Excel sheet on one page....

When to use Excel MAX Function MAX function can be used when you want to get the largest value from a set of values. What it Returns It returns the largest value from a set of values. Syntax =MAX(number1, [number2], …) Input Arguments number1 – the first number from a set of values from which you want to get the maximum value. [number2], .. – (optional) second number from a set of values from which you want to get the maximum value....

When to use Excel ROUND Function ROUND function can be used when you want to return a number rounded to a specified number of digits. What it Returns A number rounded to a specified number of digits. Syntax =ROUND(number, num_diguts). Input Arguments number – the number that you want to round. num_digits – the number of digits to which you want to round the number argument. Additional Notes If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places....

When to use Excel SUMIFS Function SUMIFS function can be used when you want to add the values in a range if multiple specified criteria are met. What it Returns It returns a number that represents the sum of all the numbers for which the specified criteria are met. Syntax =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) Input Arguments range – the range of cells against which the criteria is evaluated....

When creating a drop-down list in Excel, it takes a range of cells as input and shows it as a list. There is not much you can do with how your list looks in the drop down. For example, you can not give a background color or change font color, font type, font size, etc. Despite these limitations, there are a couple of things you can do in a drop down list....

You can also use data validation functionality to create an Excel drop down list (which is definitely one of the coolest and most powerful features in Excel) Data Validation in Excel can be accessed through the Data tab in the Ribbon. In most cases, there are three situations where you would want to use Data Validation in Excel: When you want to restrict data entry to certain numbers/text/dates. Data that does not meet the validation criteria is not allowed....

For example, suppose you have the sales data for different regions (East, West, North, and South). You can combine this data from different workbooks into a single worksheet using Power Query. If you have these workbooks in different locations/folders, it’s a good idea to move all these into a single folder (or create a copy and put that workbook copy in the same folder). So to begin with, I have four workbooks in a folder (as shown below)....

For example, consider the following examples: There are multiple ways to skin this data cat in Excel. The method you choose to delete the rows will depend on how your data is structured and what’s the cell value or condition based on which you want to delete these rows. In this tutorial, I will show you multiple ways to delete rows in Excel based on a cell value or a condition....

When to Use Excel DAY Function Excel DAY function can be used when you want to get the day value (ranging between 1 to 31) from a specified date. What it Returns It returns a value between 0 and 31 depending on the date used as the input. For example, for the month of February, it will return a day value between 0 and 29. Syntax =DAY(serial_number) Input Arguments serial_number: It is the serial number of the date for which you want to get the day value....

These are called the Military Alphabet Code (also known as NATO alphabet code). It is often used by people (especially the call center guys) to communicate the name, email address or the home/office address. Generate Military Alphabet Code in Excel In my first job, I got a project where I had to do cold calling to get some information on medical devices. Since we were connecting with people from all over the world with all kinds of accents, it was – at times – difficult to share our name and email or get theirs....

Formulas are the life and blood of Excel spreadsheets. And in most cases, you don’t need the formula in just one cell or a couple of cells. In most cases, you would need to apply the formula to an entire column (or a large range of cells in a column). And Excel gives you multiple different ways to do this with a few clicks (or a keyboard shortcut). Let’s have a look at these methods....

Finding a correlation between two data series is one of the most common statistical calculation when working with large datasets, I was working as a financial analyst a few years ago, and although we were not heavily involved in statistical data, finding correlation was something we still had to do quite often. In this tutorial, I will show you two really easy ways to calculate correlation coefficient in Excel. There is already a built-in function to do this, and you can also use the Data Analysis Toolpak....

While this can be done manually, it would be time-consuming and error-prone. However, a simple VBA code can do this in a few seconds. Combine Multiple Excel Files into One File Here is the code that can combine multiple Excel workbooks in a specified folder into a single Excel workbook: How to Use this Code? Here are the steps to use this code: Put all the Excel files that you want to combine into a folder....

In case you try and enter any fraction in a cell in Excel, there is a possibility that Excel may change it to a date or a text string. This happens because fractions are not one of the data types that Excel understands. So when you give it a fraction, it tries and converts it into a date or a text string which are the data types that are acceptable for Excel....