A worksheet is an area that has all the cells where you can store data, enter formulas, insert charts and create reports and dashboards. When you open a new Excel workbook file, by default there is only one worksheet. Earlier versions of Excel (2013 or 2016 used to have 3 worksheets by default) There are some simple shortcuts and techniques that you can use to quickly insert new worksheets in the same workbook in Excel....

But in some cases, instead of getting the value, you may want the formula to return the cell address of the value. This could be especially useful if you have a large data set and you want to find out the exact position of the lookup formula result. There are some functions in Excel that designed to do exactly this. In this tutorial, I will show you how you can find and return the cell address instead of the value in Excel using simple formulas....

And sometimes, it could be a factor outside of Excel – such as a power failure or your system hanging because of an issue other than Excel. In such cases, to make sure you don’t lose your data, Excel has an AutoSave feature – which as the name suggests, will automatically save your work. With autosave enabled, Excel will save your work at regular intervals, which will make sure you don’t lose a lot of your work (at max a few minutes of work can be lost)....

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

One of the things where Power Query can save you a lot of time is when you have to merge tables with different sizes and columns based on a matching column. Below is a video where I show exactly how to merge tables in Excel using Power Query. In case you prefer reading the text over watching a video, below are the written instructions. Suppose you have a table as shown below:...

Sometimes, there is no alternative to it, however, you can still bring in some colors and zing to it by using Excel features to format the data. Formatting the data in Excel can increase its readability tremendously. Let me introduce you to some of the formatting features in Excel that can be quickly accessed through the Excel Ribbon. Suppose you have the data as shown below: Here are some ways you can format this data to make it look better:...

A lot of Excel functionalities are also available to be used in VBA – and the Autofilter method is one such functionality. If you have a dataset and you want to filter it using a criterion, you can easily do it using the Filter option in the Data ribbon. And if you want a more advanced version of it, there is an advanced filter in Excel as well. Then Why Even Use the AutoFilter in VBA?...

What it Returns It returns a number between 1 and 7 that represents the corresponding day of the week. Syntax =WEEKDAY(serial_number, [return_type]) Input Arguments serial_number: It is the serial number of the date for which you want to find the weekday number.[return_type]: (Optional) You can choose the week’s start day and the value it should return. By default, the week starts on a Sunday (which means that if the day of the specified date is Sunday, then it returns 1....

One such example is to find the closest match of a lookup value in a dataset in Excel. There are a couple of useful lookup functions in Excel (such as VLOOKUP & INDEX MATCH), which can find the closest match in a few simple cases (as I will show with examples below). But the best part is that you can combine these lookup functions with other Excel functions to do a lot more (including finding the closest match of a lookup value in an unsorted list)....

Sometimes, you may need to send a part of the worksheet or a single excel worksheet from the workbook to your colleague or boss. While there can be other ways of doing this, in this tutorial, I’ll show you the fastest and the easiest way to email a single Excel worksheet or a part of the worksheet. But there is some pre-work. You need to add an icon to the Excel Quick Access Toolbar....

When to use Excel REPT Function REPT function can be used when you want to repeat a specified text for a certain number of times. If you’re wondering what can be the use of just repeating characters, check out the examples covered later in this tutorial. What it Returns It returns a text string that has the specified text repeat for the specified number of times. Syntax =REPT(text, number_times)...

If you’re wondering which one scores higher in the VLOOKUP vs INDEX/MATCH battle, this article will try and answer it. During one of my Excel Training sessions, I was surprised to find out that most of the people were aware and, in some cases, proficient in using VLOOKUP, but almost none of them knew about the powerful INDEX/MATCH combo. And when I mentioned it, they had a lot of queries:...

Within a few hours, it was shot down by other excel experts as it contained volatile formulas. I immediately recognized the cardinal sin I had committed. So with this confession, let me share what I have learned about volatile functions in Excel. In plain simple terms, it is a function that will make your excel spreadsheet slow, as it recalculates the formula again and again. A number of actions can trigger this (described later in this post)....

I had to create a number of drop downs with the options ranging from 1 to 5. To make it more user-friendly, I also wanted to give an option of ‘Not Selected’, when a user does not want to make a selection in the drop down list in excel. Something as shown below in the pic: The problem here is that when I choose ‘Not Selected’ from the drop down, it returns the text Not Selected (see in the formula bar in the pic above)....

Sometimes you may have a need to delete every other row (i.e., alternate rows), or every third/fourth/fifth row in Excel. One use-case of this could be when you have weekly data and you only want the data for even weeks or odd weeks. Another could be when you get a data dump from a database and all the useful data is only in every second or third row. While you always have the option to manually select and delete rows, this is inefficient in case you have a large dataset....

Now I came across a problem where I had to lookup entire row and return the values in all the columns from that row (instead of returning a single value). So here is what I had to do. In the below dataset, I had Sales Rep names and the Sales they made in 4 quarters in 2012. I had a drop down with their names, and I wanted to extract the maximum sales for that Sales Rep in those four quarters....

In this Excel tutorial, I will show you how to start a new line in an Excel cell. You can start a new line in the same cell in Excel by using: A keyboard shortcut to manually force a line break. A formula to automatically enter a line break and force part of the text to start a new line in the same cell. Start a New Line in Excel Cell – Keyboard Shortcut To start a new line in an Excel cell, you can use the following keyboard shortcut:...

For such cases, Excel introduced the Watch Window feature in Excel 2010. In this tutorial, you’ll learn how to : Keep track of important cells/formulas using the Watch Window. Use Watch Window to keep track of cells in multiple sheets. Delete cells/formulas from the watch window. Let’s get started. How to Keep Track of Important Cells using Watch Window Suppose you have student’s scores dataset as shown below and you want to keep a track of the average score in the three subjects....

Learn the exact steps I take to create World Class Dashboards in Excel. Join the Excel Dashboard Course. A Key Performance Indicators (KPI) dashboard is one of the most used dashboards in business. Its primary objective is to show the performance of key KPIs and provide a comparative view of other KPIs or companies. In this tutorial, I will show you how to create a KPI dashboard in Excel. This is an improved version of a dashboard that I created last year in my previous job role as a financial/data analyst....

A really simple and often needed task is to calculate the square root in Excel. And as I said, there are multiple ways you can do that in Excel (formulas, VBA, Power Query). In this tutorial, I will show you different ways to calculate square root in Excel (and you can choose whatever method you like best). But before I get into how to calculate it, let me quickly cover what is square root (feel free to skip to the next section if I get too preachy)....