Category: Excel

How to start a macro with a button

Posted on by DevPaulC
In this tutorial you will learn how to create an Excel Macro Button. This is a follow up from creating a a macro tutorial. If you want to learn how to record a macro and have Excel write the code for you, click here. In the previous Case Study, we created a macro that sorts a list of names alphabetically. In order to start this macro, you can add a Macro Button. A button is just a shape. To start creating a button, start by creating a shape. Go to Insert - Shapes and choose a shape. As an...read more

How to use multiple conditions in the Excel IF function

Posted on by DevPaulC
If you want to use multiple conditions in an IF function in Excel, you have to use the AND or the OR function to evaluate all the conditions (two or more conditions). All conditions evaluated have to be TRUE to make the output of the AND function TRUE. Only one condition have to be TRUE to make the output of the OR function TRUE Example: =IF(AND(condition1,condition2,condition3), ValueIfTrue,ValueIfFalse) =IF(OR(condition1,condition2,condition3), ValueIfTrue,ValueIfFalse) AND and OR functions AND and OR are the logical function used in all programming languages. Here is how these functions work. The AND function AND (TRUE, TRUE) = TRUE...read more

How to update Excel 365

Posted on by DevPaulC
If you think you may not be running the latest version of Excel as part of the Office 365 subscription, follow he steps below to check and update your desktop version. Note: If you are looking for an updated version of Excel to try the new XLOOKUP, you may not have it yet if you are on a Semi-Annual Channel. Learn about the Office update cycles and how you can still use XLOOKUP even if it's not in your desktop version of Excel. 1. Go to the file menu in Excel. Click on account 2. Click on the drop-down...read more

How to find duplicates in Excel

Posted on by DevPaulC
Finding duplicates in Excel is a very common task when dealing with large sets of data. Excel has some very easy methods to remove or highlight duplicates. You can also use some functions to help you identify duplicates in your data. Case Study. Let's assume we have a list of the best movies in the decade of 2000s and we want to get a list of the Studios that produced them. The original list looks like this. As you can see, there are many duplicates in the list of studios. Just Warner Bros. Pictures alone had many movies in...read more

How to create an Excel macro without coding – Excel macro tutorial

Posted on by DevPaulC
Excel macros sound scary but they don't have to be. You don't have to be a programmer to start with Excel macros. If you are not a programmer or don't even intend to be, this short and simple Excel macro tutorial is for you. You'll learn: how to get Excel to write some code for you how to review the written code how to launch a macro with a keyboard shortcut or a button how to launch a macro automatically when you need it (when the data changes) What are Excel macros Macros are programs that you can use...read more

How to start a macro when a worksheet changes

Posted on by DevPaulC
In this tutorial you will learn how to start a macro when a worksheet changes. This is a follow up tutorial from the creating a macro tutorial. In that tutorial, the goal was to have a macro that would sort records automatically when a new line was added. In order to run this macro automatically when the worksheet changes, you need to add the macro code in the "worksheet change" function. Excel has many automated functions that run based on events like before saving the workbook, when the workbook changes, etc. More details about these functions here in the...read more

How to get data from web in Excel

Posted on by DevPaulC
Did you know that you can get data stored on websites directly into Excel and refresh it with a click of a button? Here's how you do it Before you get into the method, it's important to know that not all websites will be open to be imported into Excel. The data in the website has to be in the form of a table. The best way to determine if the data can be imported in Excel is by trying to import it. You can also get an indication if it would work if you visit a website and...read more

How to create a dynamic Excel drop down

Posted on by DevPaulC
Learn how to create an Excel drop down list the right way. Yes, there is a wrong way and I will show you below. The right way to create a drop down list is to make it dynamic so you can always add new items to your list without changing any formulas or data validation rules in order to incorporate the new values. Static drop down lists - the wrong way Let’s assume you have a list of cars in the first column and you want to create a drop-down list to be able to pull your favorite car...read more

How to convert text to date in Excel

Posted on by DevPaulC
There are many cases when the data you have in Excel appears to contain dates but when you look closer, the dates are not in a date format. This can be problematic if you need to sort dates, group them by month, year, week or days. So we have to find a way to convert them to a Date format. The direct text to date conversion This method may seem very simple and direct. Unfortunately it doesn't work in most of the difficult cases. However, it is the first method to try so you can see what are you...read more

How to calculate age in Excel

Posted on by DevPaulC
To calculate age from date of birth in Excel, you must use one of the two formulas below =(TODAY()-DOB)/365.25=DATEDIF(DOB,TODAY(),"Y")where DOB is the range (cell) that has the Date of Birth value Age calculation can be done if you have a set of data containing the Date of Birth. Excel has two simple ways to do it. How to calculate age in Excel using subtraction Here is some sample data for the first formula (calculation was done on 2019-10-08, that is the value of TODAY()) How to calculate age in Excel using DATEDIF If you're asking yourself what is the...read more