Tag: excel

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

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

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

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

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 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 Calculate BMI in Excel

Posted on by DevPaulC
The BMI calculation formula is =weight(kg)/height(m)^2. In this example, we'll apply this formula in Excel. The formula works for the metric measurements. If you have your measurements for height and weight in imperial units, you'll first have to convert them to metric. Step 1: Calculate BMI in Excel in the metric system. The weight is listed in cell A2, the height is listed in cell B2 and the BMI is calculated in cell C2. The calculation here is =A2/(B2*B2). Make sure you isolate the (B2*B2) to ensure the order of operations. Another way to write the formula would be...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

How to use VLOOKUP in Excel

Posted on by DevPaulC
Excel Vlookup is the most powerful formula in Excel. Also vlookup is one of the formulas that are least used because so many users are scared of it. In this tutorial you will learn: how to use vlookup in Excel what is the vlookup syntax how to do a vlookup between two spreadsheets why is my vlookup not working why is xlookup coming and how will it improve vlookup If you know vlookup and pivot tables you will find the most value out of Excel. How to use vlookup in Excel We'll continue with our example of the Grocery...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