Tag: excel

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

Generating Random Numbers in Excel

Posted on by DevPaulC
How to Generate a Random Number in Excel Using RAND and RANDBETWEEN Functions If you’re wondering how to generate a random number in Excel, there are two main functions that make this easy: RAND() and RANDBETWEEN(). These functions allow you to generate random numbers for various purposes, from random decimals to whole numbers within a specified range. How to Use RAND() to Generate a Random Number in Excel Between 0 and 1 The RAND() function generates a random decimal number between 0 and 1. This is useful when you need to work with fractional values. One important thing to...read more

5 simple Excel XLOOKUP examples

Posted on by DevPaulC
Let's put the new XLOOKUP in Excel to the test with these 5 simple examples. The data below represents a list of serial numbers shipped in boxes to various companies in several countries. For simplicity, we keep the dataset simple and on the same screen. The data set Example 1: Basic XLOOKUP In the first example, we will look for the country where a certain serial number is shipped. the XLOOKUP function will look like this: =XLOOKUP(F2,A:A,D:D,"not found") The function looks for the value in F2 "1125181" that can be found in the first column A:A and finds a...read more

What is xlookup in Excel

Posted on by DevPaulC
XLOOKUP is the newest addition to the lookup formulas in Excel. If VLOOKUP is a Vertical lookup function and HLOOKUP is a Horizontal lookup function, XLOOKUP is capable of replacing them both by performing the lookup both horizontally and vertically. What is the difference between VLOOKUP and XLOOKUP? VLOOKUP is structured like this VLOOKUP ( value to look for, in this big array where the value has to be in the first column, return the value in the X column on the right after the first column, exact or approximate match) Notice in bold the restrictions of VLOOKUP. you...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

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

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

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 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

Excel Formula: Get day name from a date in Excel

Posted on by DevPaulC
Here are a few methods to get a day name (Monday, Tuesday, Wednesday etc) from a date field in Excel. If you prefer video, see the tutorials below. Use Excel TEXT formula The Excel Text formula will extract the day in a specified text type format. Here is how it works. The formula for this is =TEXT(cell,"dddd") Short Day Name for Date An alternative text for this is a short name for the day (Mon, Tue, Wed, etc). To achieve that, use the formula =TEXT(cell,"ddd") Day Month and Year Names for Date The TEXT function can be used for...read more