The one query that I get a lot is – ‘how to compare two columns in Excel?’. This can be done in many different ways, and the method to use will depend on the data structure and what the user wants from it. For example, you may want to compare two columns and find or highlight all the matching data points (that are in both the columns), or only the differences (where a data point is in one column and not in the other), etc....
Comparing two Excel files (or comparing two sheets in the same file) can be tricky as an Excel workbook only shows one sheet at a time. This becomes more difficult and error-prone when you have a lot of data that needs to be compared. Thankfully, there are some cool features in Excel that allow you to open and easily compare two Excel files. In this Excel tutorial, I will show you multiple ways to compare two different Excel files (or sheets) and check for differences....
Macros allow you to automate many time-consuming tasks and also create some functions and functionalities that are not already built-in in Excel. As a part of the security measure, if you receive a file that has a macro, Excel will disable it by default and you will have to manually enable the macros in the file so that they can be used. In this tutorial, I will show you how to enable macros in Excel and all the important things that you need to know about it....
For our example we’re going to pull the historical share prices for Apple Inc. These can be found here: https://www.google.com/finance/historical?q=NASDAQ%3AAAPL We are going to import this entire table into Excel using Power Query. Power Query makes this type of data grabbing very easy, quick and painless. To import the data: In the Navigator window: Now the data will appear in your sheet. If you need to refresh the data all you need to do is go to the Data tab and hit the Refresh button....
To view multiple sheets simultaneously, we need to open multiple windows and then arrange them. In the above example, I have created 4 windows to show each of the 4 sheets in the workbook and arranged them using the Tiled method.
But you can’t look-up images using these formulas. For example. if I have a list of team names and their logos, and I want to look up the logo based on the name, I can’t do that using the inbuilt Excel function. But that doesn’t mean it can’t be done. In this tutorial, I will show you how to do a picture lookup in Excel. It’s simple yet it’ll make you look like an Excel Magician (all you need is this tutorial and sleight of hands-on your keyboard)....
In Excel, a ‘Workbook’ is an object that is a part of the ‘Workbooks’ collection. Within a workbook, you have different objects such as worksheets, chart sheets, cells and ranges, chart objects, shapes, etc. With VBA, you can do a lot of stuff with a workbook object – such as open a specific workbook, save and close workbooks, create new workbooks, change the workbook properties, etc. So let’s get started....
Excel allows you to add multiple sheets within a workbook. This is a great way to organize your spreadsheet solutions as you can separate your inputs, data, calculations, reports, and visuals into different sheets. Organizing your workbooks with sheets can also make the spreadsheet easier to navigate for any user. How can you add new sheets to an Excel workbook? Follow this post to find out all the ways to add sheet tabs in Excel....
For the purpose of this tutorial, let’s consider the following: Quarter 1 – Jan, Feb, and Mar Quarter 2 – Apr, May, and Jun Quarter 3 – July, Aug, and Sep Quarter 4 – Oct, Nov, and Dec In case you’re working with financial data where the quarter starts from April, then you can adjust the formulas accordingly (also covered later in this tutorial). Now let’s see how to use simple formulas to find out the quarter from the date....
The Immediate window allows you to quickly do things such as: Get information about Excel files (such as sheet count in a workbook or name of the workbook) Get Debug.Print information quickly Run a line of code in seconds Run a macro (or call a function) Get information about variables (or set the value or variables) While you don’t need to use immediate window to work with VBA in Excel, knowing how to use it well can make you a lot more efficient....
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....
This method uses the legacy Excel 4 XLM functions. These functions aren’t like Excel’s other functions such as SUM, VLOOKUP, INDEX etc. These functions won’t work in a regular sheet, they only work in named functions and macro sheets. For this trick, you will need to create a named function. Use the above link to download the example workbook from this post. Create a named function. Specify the folder path....
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....
I worked at a large insurance company and we were in the midst of a project to take inventory of all the spreadsheets used. We created a VBA procedure that would take a folder path and spit out all the file names in that folder. Each department was responsible for using this to take inventory of all their files. Fortunately for me, my department used only a few folders and subfolders for our work otherwise I would have tried to modify the code to loop through all subfolders in a folder....
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....
Example Generic Formula What It Does This formula will return the weekday for a given date. How It Works The TEXT function will return a text string based on any custom format. This formula uses the “dddd” custom date format to return the weekday name for any given date. In our example, TEXT(“2030-01-01″,”dddd”) will return “Tuesday” since January 1st 2030 falls on a Tuesday. We can also use the “ddd” custom format to return abbreviated weekday name: Mon, Tue, Wed, Thu, Fri, Sat & Sun....
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....
