Inserting page numbers in Excel takes a little more work as compared with other Microsoft applications such as Word or PowerPoint. Watch Video – How to Insert Page Numbers in Excel How to Insert Page Numbers in Excel In this tutorial, you’ll learn how to insert page numbers in Excel worksheets. How to Insert Page Numbers in Excel using the using the Page Layout View. Insert Page Numbers in Excel Using the Page Setup Dialogue Box....

When to use Excel SEARCH Function Excel SEARCH function can be used when you want to locate a text string within another text string and find its position. What it Returns It returns a number that represents the starting position of the string you are finding in another string. Syntax =SEARCH(find_text, within_text, [start_num]) Input Arguments find_text – the text or string that you need to find. within_text – the text within which you want to find the find_text argument....

In this tutorial, I will show you different ways to use IFERROR with VLOOKUP to handle these #N/A errors cropping up in your worksheet. Using the combination of IFERROR with VLOOKUP allows you to show something meaningful in place of the #N/A error (or any other error for that matter). Before getting into details on using this combination, let’s first quickly go through the IFERROR function and see how it works....

And just like every other thing in Excel, there are multiple ways to insert columns as well. You can insert one or more single columns (to the right/left of a selected one), multiple columns (adjacent or non-adjacent), or a column after every other column in a dataset. Each of these situations would need a different method to insert a column. Insert New Columns in Excel In this tutorial, I will cover the following methods/scenarios to insert new columns in Excel:...

Download Example File Watch Video – Two-variable Data Table in Excel One Variable Data Table in Excel. Scenario Manager in Excel. Goal Seek in Excel. Excel Solver. Two-variable data table is best suited in situations when you want to see how the final result changes when two of the input variables change simultaneously (as against One Variable Data Table where only one of the input variable changes). If you want to analyze data when more than 2 variables change, scenario manager is the way to go....

While I believe the utility of this feature is high in Word, PowerPoint, and Outlook, it may sometimes be the tool you need in Excel as well. As the name suggests, it converts text into speech and can read out to you while you close your eyes, rest your head on a table and relax. Getting Access to Excel Text to Speech Similar to the Excel Camera tool, even this feature is not readily available in the Quick Access Toolbar or the ribbon....

And when you start working with data in Excel, one of the common tasks you have to do is to adjust the row height in Excel based on your data (or adjust the column width). It’s a really simple thing to do, and in this short Excel tutorial I will show you five ways to change row height in Excel Change the Row Height with Click and Drag (Using the Mouse) The easiest and the most popular method to change row height in Excel is to use the mouse....

What is a Sales Funnel? In any sales process, there are stages. A typical sales stage could look something as shown below: Opportunity Identified –> Validated –> Qualified –> Proposal –> Win/Loss If you think about the numbers, you would realize that this forms a sales funnel. Many opportunities are ‘identified’, but only a part of it is in the ‘Validated’ category, and even lesser ends up as a potential lead....

Working efficiently with Excel also means that you have to work with multiple worksheets within the same workbook. Excel allows you easily add multiple worksheets in the workbook, at the same time you can also easily delete these worksheets. In this Excel tutorial, I will show you how to quickly delete worksheets in Excel. I would cover multiple methods such as using keyboard shortcuts, options in the ribbon, and VBA to delete sheets in Excel....

Most Excel users spend the majority of their time working with the cells in Excel – be it entering data or formulas or editing the already entered formulas/text. In this tutorial, I will cover everything you need to know about editing cells in Excel. While it’s a straightforward process, knowing the right way to edit cells and all the keyboard shortcuts you can use to edit cells in Excel would be quite useful in making you more efficient....

Rounding a number to the nearest integer or the nearest 0.5 or 5 or 10 multiple is a common task for many people. For example, if you’re a project manager involved in effort estimate, you can’t have 12.7 full-time resources working on a project. You need to round-up this number to 13 (the nearest integer). Similarly, if you’re buying stocks that are sold in a batch of 5, and you can afford a maximum of 123 shares only, you need to round it down to 120 (i....

One such area is when you enter numbers in Excel. Sometimes, Excel automatically changes these numbers to dates. For example, if you enter 1/2 in Excel, Excel will automatically change this to 01-02-2020 (or 02-01-2020 if you’re in the US) Similarly, if you enter 30-06-2020, Excel assumes you want to enter a date, and it changes this to a date (as shown below) While this may be what you want in most cases, but what if I don’t want this....

With 3D referencing, you can refer to the same cell in multiple worksheets, and can also use this in formulas such as the SUM or AVERAGE, or COUNT. In this short tutorial, I will show you how to quickly sum across multiple worksheets using this 3D reference feature. Sum Across Multiple Sheets in Excel – Single Cell Below I have a dataset with quarter-wise sales for 10 stores. In the screenshot above, you can see the sales data for Quarter 1 in the sheet named Q1, and I have similar data for the other three quarters (Q2, Q3, and Q4) in three separate worksheets....

Being undocumented means that you will not find it in the formula list or as a part of the IntelliSense (the prompt that shows up when you type a formula name to show you the names of the matching functions). When to use Excel DATEDIF Function Excel DATEDIF function can be used when you want to calculate the number of years, months, or days between the two specified dates. A good example would be calculating the age....

When to use Excel LARGE Function Excel LARGE function can be used to get the Kth largest value from a range of cells or array. For example, you can get the 3rd largest value from a range of cells. What it Returns It returns the value which is the Kth largest value from a range of cells. Syntax =LARGE(array, k) Input Arguments array – the array or range of cells from which you want to fetch the kth largest value....

For example, if I’m using my VBA code to open a .exe file or any other format file (such as Word or PowerPoint), and I want a delay in my code so that I can see that the file has opened correctly. This can easily be done using the inbuilt SLEEP and WAIT commands in Excel VBA. Let’s look at a couple of examples you can use to delay or pause your VB code in Excel....

This is a huge milestone for me and I am completely overwhelmed by the love and support I have got from my readers. I started this journey ~2 years ago and it has been a heck of a ride so far. I started this blog an effort to share what I learn every day about Excel. And to know that more than 2,000 people benefit from what I do is a nice and warm feeling....

When to use Excel INDEX Function Excel INDEX function can be used when you want to fetch the value from a tabular data and you have the row number and column number of the data point. For example, in the example below, you can use the INDEX function to get the marks of ‘Tom’ in Physics when you know the row number and the column number in the data set....

A few days ago, a colleague came up with this problem. He had a list of URLs as shown below, and he needed to extract all the characters after the last forward slash (“/”). So for example, from https://example.com/archive/January he had to extract ‘January’. It would have been really easy has there been only one forward slash in the URLs. What he had was a huge list of thousands on URLs of varying length and a varying number of forward-slashes....

In case you prefer reading written instruction instead, below is the tutorial. Conditional Formatting allows you to format a cell (or a range of cells) based on the value in it. But sometimes, instead of just getting the cell highlighted, you may want to highlight the entire row (or column) based on the value in one cell. To give you an example, below I have a dataset where I have highlighted all the rows where the name of the Sales Rep is Bob....