Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Note: you can also use Control + mouse scroll wheel to zoom in and out on both Windows and Mac. On Windows, the shortcut appears to be new with Excel 2016, and decreases the zoom level shown in the lower right of the worksheet by 15% each time Control - is used. Use Ctrl Alt + to zoom out by 15%. On the Mac, there is no native keyboard shortcut to zoom in Excel, as far as we know....
These are called the Military Alphabet Code (also known as NATO alphabet code). It is often used by people (especially the call center guys) to communicate the name, email address or the home/office address. Generate Military Alphabet Code in Excel In my first job, I got a project where I had to do cold calling to get some information on medical devices. Since we were connecting with people from all over the world with all kinds of accents, it was – at times – difficult to share our name and email or get theirs....
Because TODAY always returns the current date, the formula will continue to calculate the correct age in the future. In the example shown, the goal is to calculate age in years. The formula in E5 is: The first two arguments for DATEDIF are start_date and end_date. The start date comes from cell D5 (May 15, 2001) in the example. The end date is generated with the TODAY function. TODAY always returns the current date in Excel....
The result is the number 365, since there are 365 days between Jan 1, 1999 and Jan 1, 2000. Note: To see the result as a number and not a date, format the result with the General number format. Working with today To count the number of days between an earlier date and today, you can use the TODAY function: To calculate the number of days between a later date and today, use: Note that TODAY will recalculate on an ongoing basis....
In the example the formula in D7 is: Note: this only works for text values, not numbers. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
The result is the monthly close price for each symbol listed in column B over the past 6 months. Note that STOCKHISTORY returns an array of values that spill onto the worksheet into multiple cells. The STOCKHISTORY is only available in Excel 365. Basic example STOCKHISTORY retrieves historical stock price information for a given symbol and date range. STOCKHISTORY takes up to 11 arguments, most of which are optional. The syntax for the first 5 arguments looks like this: The interval argument specifies the time period to use between prices....
In the example shown, we have a number of transactions, each with a timestamp. Let’s say you want to group these transactions into buckets of 3 hours like this: 12:00 AM-3:00 AM 3:00 AM-6:00 AM 6:00 AM-9:00 AM 9:00 AM-12:00 PM For example, a time of 2:30 AM, needs to go into the 12:00 AM - 3:00 AM bucket. A time of 8:45 AM needs to go into the 6:00 AM-9:00 AM bucket, and so on....
Formulas are the life and blood of Excel spreadsheets. And in most cases, you don’t need the formula in just one cell or a couple of cells. In most cases, you would need to apply the formula to an entire column (or a large range of cells in a column). And Excel gives you multiple different ways to do this with a few clicks (or a keyboard shortcut). Let’s have a look at these methods....
Finding a correlation between two data series is one of the most common statistical calculation when working with large datasets, I was working as a financial analyst a few years ago, and although we were not heavily involved in statistical data, finding correlation was something we still had to do quite often. In this tutorial, I will show you two really easy ways to calculate correlation coefficient in Excel. There is already a built-in function to do this, and you can also use the Data Analysis Toolpak....
While this can be done manually, it would be time-consuming and error-prone. However, a simple VBA code can do this in a few seconds. Combine Multiple Excel Files into One File Here is the code that can combine multiple Excel workbooks in a specified folder into a single Excel workbook: How to Use this Code? Here are the steps to use this code: Put all the Excel files that you want to combine into a folder....
In this video, we’ll show you how to add a message at the top of a filtered list that displays this information. Here we have a list of properties. If we enable “filtering,” and filter the list, Excel will display the current and total record count in the status bar below. However, if we click outside the filtered list, and back again, this information is no longer displayed. Let’s add our own message at the top of the list that stays visible....
The New menu on the File tab The keyboard shortcut Control-N The Quick Access toolbar Let’s take a look. When you first start Excel, you’ll see you have a new blank document ready to use. If you want to create your own new document, you have several options. First, you can create a new workbook by going to the File tab, and selecting New. You can then choose Blank Workbook, or choose from many available templates....
Here we have historical data showing average 30 year mortgage rates over a 5 year period. When I create a line chart, the vertical axis is a value axis showing the mortgage rate, and the horizontal axis is a category axis, grouping the data in specific date intervals. Let’s walk through some of the options for customizing the vertical value axis. To start off, right-click and select Format axis. Make sure you’re on the axis options icon....
In case you try and enter any fraction in a cell in Excel, there is a possibility that Excel may change it to a date or a text string. This happens because fractions are not one of the data types that Excel understands. So when you give it a fraction, it tries and converts it into a date or a text string which are the data types that are acceptable for Excel....
Sometimes you get a set of data that isn’t complete because it’s been organized like an outline, with main headings and sub-headings appearing just once at the start of a new section. This isn’t a problem for humans because we can see and understand the overall structure of the data. But it won’t work if you want to analyze the data using filters, or pivot tables, or even conditional formatting....
Let’s take a look. When using the Find and Replace dialog box in Excel, there are actually two options for finding matches: Find Next, which we’ve already covered, and Find All. The Find All button will build a list of every cell that meets the current search criteria and report a total at the bottom. In this case, there are 19 cells that match the search for Ann. You can resize the window to show more files in the list....
So wouldn’t it be nice if there was a way to just jump to the bottom or jump to the top of that list? Well, there is. You just have to learn the keyboard shortcut. Let’s take a look. So here we are in Excel, and if I start scrolling, you can see this is a big list. In fact, this list is over 30,000 rows. So it’s just not practical for me to scroll to the top or the bottom of that list manually....
In this video we’ll show you a simple way to do it using the RAND function. Here’s a list of names that represent entries in a contest. Suppose we’d like to pick five winners. In a real drawing, we’d pick five random names out of a hat. But how do we do that in Excel? Well, when you use a hat, you can think of the winners as having the lowest numbers in the contest....
