Blog

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 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 a slicer in Excel

Posted on by DevPaulC
Slicers in Excel are filters that help slice the data in a modern and dynamic way. Before slicers, filters were the only way to "slice" a pivot table data. The slicers were a step forward to a dashboard and a first step to Power BI. If you are a Google Sheets user, you can use slicers there too. See here how to use slicers in Google Sheets. How to filter a pivot table without slicers Before slicers, filters were the only way to filter the data. The problem with using filters is that the filters are independent. The data...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 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 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 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 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 add numbers in a column in Excel

Posted on by DevPaulC
Adding numbers in a column or on a row is one of the most basic Excel Functions. Here are 3 easy ways to do it. Use simple addition ( the plus sign +) Use the SUM() function Use the AUTOSUM button Simple addition In the example below we have a list of cells containing the amount of money in sales for 12 months for 4 products. Assuming that we want to add all the amounts in January, let's do a simple addition of the 4 numbers highlighted. How to create a simple addition A simple addition looks like this:...read more