Blog

How to calculate work days in Excel

Posted on by DevPaulC
Excel has some powerful functions to use for calculating the number of work days between two dates. Not only you can calculate the number of working days but you can exclude some special company specific holidays as well. Case study In the following case study, the HR department has a list of employees that took vacation and recorded the vacation Vacation Start Date and the Return to work date in a form. The Human Resources department has to calculate how many days were they out, how many vacation days (working days) they took and to take into account the...read more

Convert cm to inches

Posted on by DevPaulC
Build in a smart converter from cm to inches and back. There are two simple methods in Excel to build this. The methods below also wok in Google Sheets. 1 cm = 0.3937 inch and 1 inch = 2.54 cm Simple Calculation. To convert from inches to centimeters, multiply the value in inches with 2.54 or to convert from centimeters to inches, divide the centimeters value with 2.54 to get the value in inches. Using a formula Use the CONVERT formula to convert from any unit to any other unit. and vice-versa There it is. A simple converter, two...read more

Automatically refresh pivot table

Posted on by DevPaulC
You may need to refresh an Excel displayed dashboard or pivot table periodically if your data is coming from an external source (database, web or other). This tutorial will show you how to refresh your pivot table automatically. Here is what you will learn Refresh Automatically when you open the file Refresh Pivot Table every X minutes Refresh on external data change Refresh Automatically when you open the file To setup your pivot to refresh as soon as you open the file, right click on the pivot table and click on "Pivot Table Options". In the Data Tab, tun...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

A Simple Excel VLOOKUP example

Posted on by DevPaulC
In this example, I will show you a simple way to use VLOOKUP that even a beginner would understand. VLOOKUP represents Vertical Lookup. That means Excel will look for a value on the vertical, from top to bottom, and return the corresponding value from the same row where the value is found. Simple Enough? Let's see it in action In the table below in cell B2, I'm looking to write a VLOOKUP formula to get the color of the fruit Banana. As you can see, I have a reference table with fruit colors. Banana is in the table and...read more

10 BEST Excel YouTube Channels

Posted on by DevPaulC
Hello and welcome to our list of the top 10 YouTube channels for learning Excel! Whether you're a beginner looking to get started with spreadsheet basics or an experienced user looking to take your skills to the next level, these channels offer a wealth of resources and tips to help you master the powerful spreadsheet software. From step-by-step tutorials and exercises to helpful tips and tricks, these channels have something for everyone. So without further ado, let's dive in and explore the best YouTube channels for learning Excel. The best way to be introduced to these channels is to...read more

How to use the CONCATENATE function in Excel

Posted on by DevPaulC
If you want to join multiple texts, numbers or function values into one cell, Excel has a function called CONCATENATE(). You can use the function is some very creative ways. Simple text concatenation The CONCATENATE function works like this: = CONCATENATE( text1, text2, text3). In the example below, text1 in in A1 cell, text2 is a space (if you specify text you have to put it in quotes) and text3 is in cell B1. Addind Good, a space and Morning will result in "Good Morning" Concatenate text with numbers Concatenate works with text and numbers just as well. The...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

How to use XLOOKUP in Google Sheets

Posted on by DevPaulC
XLOOKUP may be a new function to Excel but it is not new to spreadsheet software. Google Sheets already has the LOOKUP function that works exactly like Excel's XLOOKUP. What about Excel's XLOOKUP support for Google Sheets? As Excel Released the XLOOKUP for most of the users now (if you don't have XLOOKUP in your Excel see here why), many users are asking what happens if you try to open a file that has an XLOOKUP formula in Google Sheets. As of February of 2020, this is not supported yet. You get an error when you try to do...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