Category: Excel

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

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

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

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

How to get data from web in Excel

Posted on by DevPaulC
Did you know that you can get data stored on websites directly into Excel and refresh it with a click of a button? Here's how you do it Before you get into the method, it's important to know that not all websites will be open to be imported into Excel. The data in the website has to be in the form of a table. The best way to determine if the data can be imported in Excel is by trying to import it. You can also get an indication if it would work if you visit a website and...read more

How to convert text to date in Excel

Posted on by DevPaulC
There are many cases when the data you have in Excel appears to contain dates but when you look closer, the dates are not in a date format. This can be problematic if you need to sort dates, group them by month, year, week or days. So we have to find a way to convert them to a Date format. The direct text to date conversion This method may seem very simple and direct. Unfortunately it doesn't work in most of the difficult cases. However, it is the first method to try so you can see what are you...read more

How to create a dynamic Excel drop down

Posted on by DevPaulC
Learn how to create an Excel drop down list the right way. Yes, there is a wrong way and I will show you below. The right way to create a drop down list is to make it dynamic so you can always add new items to your list without changing any formulas or data validation rules in order to incorporate the new values. Static drop down lists - the wrong way Let’s assume you have a list of cars in the first column and you want to create a drop-down list to be able to pull your favorite car...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