Category: Excel

How to calculate age in Excel

Posted on by DevPaulC
To calculate age from date of birth in Excel, you must use one of the two formulas below =(TODAY()-DOB)/365.25=DATEDIF(DOB,TODAY(),"Y")where DOB is the range (cell) that has the Date of Birth value Age calculation can be done if you have a set of data containing the Date of Birth. Excel has two simple ways to do it. How to calculate age in Excel using subtraction Here is some sample data for the first formula (calculation was done on 2019-10-08, that is the value of TODAY()) How to calculate age in Excel using DATEDIF If you're asking yourself what is the...read more

Excel IF statement

Posted on by DevPaulC
IF statement is one of the most common programming function. It is also one of the most used ones in Excel. The IF function can be used with one condition, as described below or with multiple conditions evaluated using the AND / OR functions How does the IF function work The IF function works like this. There are two branches. An IF branch and an ELSE branch. As an example you can use this to assign a value if something happens or not. If event happens then assign a value else assign another value. For example: IF Traffic Light...read more

How to add numbers in a column in Excel

Posted on by DevPaulC
Adding numbers in a column or on a row is one of the most basic Excel Functions. Here are 3 easy ways to do it. Use simple addition ( the plus sign +) Use the SUM() function Use the AUTOSUM button Simple addition In the example below we have a list of cells containing the amount of money in sales for 12 months for 4 products. Assuming that we want to add all the amounts in January, let's do a simple addition of the 4 numbers highlighted. How to create a simple addition A simple addition looks like this:...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

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

COUNT functions in Excel

Posted on by DevPaulC
There are a number of COUNT functions in Excel that you can use to count your records. Here are the most used ones: COUNT - count the number of cells in a range that contain numbers COUNTA - counts the number of cells in a range that are not empty COUNTBLANK - counts the number of cells in a range that are empty COUNTIF - counts the number of cells in a range that meet a condition COUNTIFS - counts the number of cells in a range that meet a set of conditions Let's get into the details COUNT...read more

Cells and Ranges

Posted on by DevPaulC
Cell In Excel, one grid box is called a cell. A cell is named as the intersection of the column and row. In the example below, the Cell is named C4 because it is at the intersection of row 4 and column C. You can see the name of the cell that you currently have selected in the name field (highlighted in yellow) Range A Range is a collection of two or more cells. If you select a cell and then with the left mouse button pressed, drag the mouse down and right, you would select a range of...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

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