Blog

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

What is Excel VBA? A Beginner’s Guide to Macros and Automation

Posted on by DevPaulC
Excel VBA refers to Visual Basic for Applications, a programming language that helps automate tasks in Microsoft Excel. VBA is a powerful tool for creating macros and customizing Excel functionalities. Not only does it work in Excel, but it’s also compatible with other Office applications like Word, Outlook, and PowerPoint. What is VBA used for? Excel VBA is commonly used to automate repetitive tasks through macros. These macros can simplify tasks like data analysis, generating reports, or modifying large sets of data. If you’re new to VBA, don’t worry—you don’t need to be a programmer to get started, as...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 use SUMIF in Excel

Posted on by DevPaulC
SUMIF formula is great for summarizing data in a large dataset. A better method to summarize the data is a pivot table. But if only a quick summary is required, SUMIF might be an easier method. Here is the dataset In a previous tutorial you can see how to do a pivot table of this 1464 row dataset and sum up all the sales by category and products. The SUMIF method Another way to obtain the same result as in the pivot table is to use a SUMIF function. For example, the SUMIF could summarize the sales for Broccoli...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

Today’s date and time in Excel

Posted on by DevPaulC
Use Excel to calculate today's date and today's date and time with the functions TODAY() and NOW() plus variations and keyboard shortcuts. These guides work for Google Sheets as well. Today's Date To simply add today's date, use the function TODAY() with no arguments Add or subtract integer numbers to the TODAY() function to get tomorrow's date or yesterday's date add a 7 and you get next week's date and so on Today's Date and Time To calculate today's Date and Time, use the function NOW(). This will add the current time to the cell. same as with TODAY()'s...read more

Convert kg to lbs

Posted on by DevPaulC
If you’re looking to convert kilograms (kg) to pounds (lbs) in Excel, this guide will show you two easy methods. Whether you're creating a simple conversion tool for everyday use or incorporating conversions into your spreadsheet calculations, you’ll learn how to do it efficiently using both direct calculations and Excel’s built-in functions. Method 1: Direct Calculation in Excel The conversion factors you'll need are:- 1 kg = 2.20462 lbs (pounds)- 1 lb (pound) = 0.45359 kg These are the values to use in our calculation. In our example, we will open a spreadsheet and create two sets of headers....read more

Multi-row and Multi-column Pivot Table

Posted on by DevPaulC
In the previous tutorials for Pivot Tables we looked at the simple Pivot tables with one row and one column, then we added a category to the data-set to create a multi-row pivot table. In this tutorial we will use one of the existing columns in our data set, the column "Date" to build on the pivot table we created in the previous step and add columns. Note: If you are a Google Sheets user, Pivot Tables in Google Sheets work very similar. Initial Data Set and Pivot Table This is a simple Pivot Table with one Value for...read more

What is an Excel Pivot Table

Posted on by DevPaulC
Pivot tables are Excel's most powerful feature. A pivot table lets you summarize detailed data automatically. In this tutorial you will learn what is an Excel Pivot table and how to create an Excel pivot table to summarize a simple dataset. Case Study A grocery store wants to keep track of daily sales of the main fruits thei are selling: Apples and Oranges. They start with a blank Excel sheet and each day add two lines. One for the sales of Apples and one for the sales of Oranges. Towards the end of the month, the store manager wants...read more

The excel ribbon

Posted on by DevPaulC
When first introduced, the ribbon was not liked by many experienced Excel users. Personally I hated the ribbon. Now, I can't even imagine how Excel looked before the ribbon. If you don't know, or don't remember, this used to be instead of the ribbon. How does it look to you now? Compared with the current ribbon Much better, right? The ribbon is not just one ribbon. It is actually 10 ribbons that change depending on the tab selected. Each tab has it's own set of commands. The Home tab has worksheet related commands, the Insert tab has commands related...read more