Category: Excel

Multi-Level Row Pivot Table

Posted on by DevPaulC
If in the previous example we studied how to create a simple pivot table from a simple data-set, in this example we add an additional column to categorize our products in order to create a Multi-Level Row Pivot Table 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 to summarize...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

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

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

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

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

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

Top 10 Excel websites

Posted on by DevPaulC
Are you looking to improve your Excel skills and become a spreadsheet pro? Look no further! In this blog post, we've compiled a list of the best Excel websites that offer a variety of resources for all skill levels. From video tutorials to online courses, these websites have everything you need to take your Excel knowledge to the next level. So whether you're just starting out with Excel or looking to brush up on your skills, these websites have something for you. If you prefer to learn using video, here is a list of the best Excel YouTube channels....read more