Posts

Note: Starting with Excel 2016, the FORECAST function was replaced with the FORECAST.LINEAR function. Microsoft recommends replacing FORECAST with FORECAST.LINEAR, since FORECAST will eventually be deprecated. Note: Starting with Excel 2016, the FORECAST function was replaced with the FORECAST.LINEAR function. Microsoft recommends replacing FORECAST with FORECAST.LINEAR, since FORECAST will eventually be deprecated. In statistics, linear regression is an approach for modeling the relationship between a dependent variable (y values) and an independent variable (x values)....

The value returned by the NOW function is a standard Excel date, including a fractional value for time. To display the result as a date, apply a date number format. Optionally customize the number format to include the time. If you want the current date without a time value, use the TODAY function. Examples The examples below show how the NOW function can be used in various ways: Static date and time If you need a static date and time that won’t change, you can use the following shortcuts:...

On the Mac, this shortcut works directly on the worksheet, and adds a bottom border to each cell in the selection. 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.

In the example shown, the formula in F7 is: Note: Microsoft classifies STDEV as a “compatibility function”, now replaced by the STDEV.S function. Standard Deviation functions in Excel The table below summarizes the standard deviation functions provided by Excel. Notes: STDEV calculates standard deviation using the “n-1” method. STDEV assumes data is a sample only. When data represents an entire population, use STDEVP or STDEV.P. Numbers are supplied as arguments....

Variation functions in Excel The table below summarizes the variation functions available in Excel. Notes VAR.P assumes data represents the entire population. If data represents a sample, compute variance with VAR.S. VAR.P only evaluates numbers in references, ignoring empty cells, text, and logical values like TRUE or FALSE. Arguments can either be numbers or names, arrays, or references that contain numbers. Arguments can be hard-coded values instead of references. To evaluate logical values and/or text, use the VARPA function....

Example In the example shown, the formula in F5 is: Note that the VARP function ignores the “NA” text in C8, while VARPA includes this in the variance estimate as zero. Variation functions in Excel The table below summarizes the variation functions available in Excel. Notes VARPA assumes data represents the entire population. If data represents a sample, use VARA or VAR.S VARPA evaluates text values and logicals in references, unlike VARP....

where Table1 is an Excel Table that contains 100 rows of data in B5:E104. The challenge in this example is to construct the logic needed to match records based on a partial match. The FILTER function does not support wildcards, so we need to use a different approach. In this case, we are using the SEARCH function together with the ISNUMBER function like this: Here, the SEARCH function is configured to look for text entered in cell H4, inside the “Last” column in the table....

The formula is based on the AND function, configured with two conditions. The first conditions checks to see if the column date is greater than or equal to the start date: The second condition checks that the column date is less than or equal to the end date: When both conditions return true, the formula returns TRUE, triggering the blue fill for the cells in the calendar grid. Note: both conditions use mixed references to ensure that the references update correctly as conditional formatting is applied to the calendar grid....

Which returns the word “time”. Working from the inside out, we use the SUBSTITUTE function to find all spaces in the text, and replace each space with 100 spaces: So, for example, with the text string “one two three” the result is going to look like this: With hyphens representing spaces for readability. Keep in mind that there will be 100 spaces between each word. Next, the RIGHT function extracts 100 characters, starting from the right....

Note: DATEDIF automatically rounds down to the nearest whole month. To round up to the nearest month, see below. Complete whole months In the example shown, we are calculating complete whole months between a start date and an end date with the DATEDIF function. DATEDIF takes 3 arguments: start_date, end_date, and unit. In this case, we want months, so we supply “m” for unit. In the example shown, the formula in D5, copied down, is: The behavior of DATEDIF is automatic....

In the example shown, the formula in F7 is: Note: the last argument is optional, and defaults to TRUE, but I like to explicitly set the matching mode. VLOOKUP simply looks up the age and returns the group name from the 2nd column in the table. This column can contain any values that you wish. Pivot tables Pivot tables can automatically group numbers, but the VLOOKUP approach allows you to perform completely custom grouping....

Let’s take a look. Here we have a set of data that’s already formatted as an Excel Table. Let’s use this table to create a pivot table and add some fields. Since the source data is already a Table, we’ll use the Summarize With Pivot Table command, on the Table Tools Design tab. Let’s accept the defaults, and let Excel create the pivot table on a new worksheet. A new pivot table doesn’t have any fields, so our first task is to add some....

Before dynamic array formulas, counting unique values in Excel involved complex array formulas, especially if you needed to count values based on one or more conditions. Now that dynamic array formulas, and especially the UNIQUE function, are part of Excel, this is all much easier. On this first worksheet, this list of colors contains duplicates. We want to get a count of the unique colors. Now, I can easily count these values with the COUNTA function....

Let’s take a look. To find a value in Excel, use the Find and Replace dialog box. You can access this dialog using the keyboard shortcut control-F, or, by using the Find and Select menu at the far right of the Home tab on the ribbon. Let’s try looking for the name Ann. Nothing happens until we click the Find Next button. Then, each time we click, Excel finds another match....

Here we have an example we looked at previously. With one conditional formatting rule that uses a formula, we’re able to highlight rows based on the task owner. This works well. But what if we want to highlight rows based on both priority and owner? In that case, we’ll need to extend the formula to handle 2 conditions. First, I’ll add an input cell for priority. I’m also name these both input cells to make the formula we’re going to create a little easier to read....

Let’s take a look at a few ways to do that. First, let’s take a look at the problem. Here’s a simple spreadsheet that captures hours worked in a week and displays a total. The total is calculated using the SUM function on all cells above. Let’s say you want to copy the formula that sums the hours to another location. If we just copy the original formula, then paste, it stops working properly because the cell references have been updated based on the new location....

Let’s take a look. In order to save an Excel workbook to a different file format, you need to use the Save As command. You can access Save As in several different ways. One way is to go to the File tab and click the Save As command at the upper left. The Save As dialog box allows you to choose a location, a file name, and select a file type from a long list of file formats....

Let’s take a look. The first thing to understand about selecting cells is that there is always at least one cell already selected in the worksheet. To see what is currently selected, check the name box. The name box will display a reference to the current selection. To select one cell in Excel, just click anywhere in the worksheet. Notice that the address shown in the name box is continuously updated....

which returns “x” when B6 contains “red” or “green”, and an empty string ("") if not. Notice the OR function is not case-sensitive. This is an example of nesting – the OR function is nested inside the IF function. Working from the inside-out, the logical test is created with the OR function: OR will return TRUE if the value in B6 is either “red” OR “green”, and FALSE if not....