Posts

Example In the example shown above, the formula in cell E13 is: where sales (C5:C12), periods (B5:B12), and confidence (J4) are named ranges. With these inputs, the FORECAST.ETS.CONFINT returns 198.92 in cell E13. This formula is copied down the table, and the resulting confidence interval values in column “CI” are used to calculate the upper and lower bounds of the forecast, as explained below. The forecast value in cell D13 is calculated with the FORECAST....

The MROUND function takes two arguments, number and significance. Number is the numeric value to round. The significance argument is the multiple to which number should be rounded. In most cases, significance is provided as a numeric value, but MROUND can also understand time entered as text like “0:15” or “0:30”. Number and significance must have the same sign, otherwise MROUND will return a #NUM! error. Examples Below are some examples of MROUND formulas with hardcoded values: To round a number in A1 to the nearest multiple of 5, you can use MROUND like this:...

It gives you quick access to some of the commonly used data analysis functionalities in Excel so that you don’t have to go into the ribbon and find the correct option (hence saving some clicks and a little bit of time). In this tutorial, I will tell you everything you need to know about the Quick Analysis Tool in Excel. I’d also cover different use cases where you can use this tool to be more productive and save time....

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: this shortcut is unusual on a Mac because the Command key is required (Option doesn’t work), even on Macs with Page Up / Page down keys. 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.

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.

The used range is continually updated as changes are made to a worksheet, and includes any cell that has ever been used. For example, if A1 contains a value, and that value is deleted, cell A1 is still considered used. Sometimes, a worksheet will have a used range that is much larger than expected. This can cause performance problems and other unexpected behavior. One way to “reset” a used range is to delete all unused columns and rows....

In general, there is no need to use the VALUE function because Excel automatically converts text to numbers as needed. However, VALUE is provided for compatibility with other spreadsheet applications. Use the VALUE function to convert text input to a numeric value. The VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. Normally, Excel automatically converts text to numeric values as needed, so the VALUE function is not needed....

In this worksheet we have some sample order data in a table called “data”. Let’s use the FILTER function to find all “blue” orders in June. To visualize how this works I’m going to set up the logic in helper columns first. Then, I’ll move that logic into the FILTER function, to make an all-in-one formula. First, we’ll test for dates in June with the MONTH function. When I provide the full set of dates to MONTH in a range, we get back a dynamic array with a month number for each date....

where “holidays” is the named range G5:G9. In the example shown, the project end date is in column C, and days is in column D. The end date represents a due date or target date, and days represents duration in work days. In column E, the WORKDAY function calculates a date by which the project must start in order to finish by the end date. In this case, we need to count backwards from the end date, so we supply days as a negative number....

where values is the named range K4:M7 and the rule is applied to all data in B4:I15. Although this is a difficult problem for the human eye, it is exactly the kind of thing Excel does very well. The solution above shows matching data in green, and matching values in blue. The colors are applied automatically with conditional formatting and will update instantly if values change. This requires two separate conditional formatting rules, each with its own formula....

Let’s take a look. Here we have a pivot table that is summarizing sales data by region. As you can see, there are four regions total: East, Midwest, South, and West. Let’s assume that there has been a reorganization, and the East and South regions are now combined into a new region called Eastern. One option is to modify the source data to replace all instances of East and South with Eastern....

Here we data that shows answers to the survey question “How do you rate your skill with Excel?” Let’s plot this data in a bar chart. A bar chart in Excel shows horizontal bars, and it’s a good option when you want to compare data with longer labels, since there is plenty of room for text to the left of the bars. You can see we have the count for each response, with a little over 1900 responses total....

To use the data entry form you will need your data to be in certain format. Step 1: Adding the data entry form to the Quick Access Toolbar. Open the quick access toolbar options menu. Add the Form to your quick access toolbar. Step 2: Using the data entry form. Now we can use the data entry form. Scrolling through your data Adding a new record Deleting an existing record....

Let’s take a look. After you’ve created a chart, you normally don’t need to worry about updating the chart manually. That’s because Excel will automatically update the chart when the source data changes, as long as the calculation is set to Automatic. You can verify that Calculation is set to automatic on the Formulas toolbar. Just click Calculation Options and confirm that Automatic is checked. For this chart, the source values are in the range B7:C11....

Excel has a great built in function called SUBSTITUTE which allows you to find one bit of text within another text string and substitute it for another bit of text. Copy and paste this table into cell A1 in Excel In the above example we can use the SUBSTITUTE function to replace all instances of apples with cookies using the following formula. Copy and paste this table into cell A1 in Excel Now if we also want to replace bananas with chocolate we could do this by using a nested SUBSTITUTE formula....

To resolve circular references, you’ll need to find the cell(s) with incorrect cell references and adjust as needed. The article below provides more information and steps to resolve. This creates a circular reference because the formula, entered in cell F7, refers to F7. This in turn throws off other formula results in D7, C11, and D11: Circular references can cause many problems (and a lot of confusion) because they may cause other formulas to return zero, or a different incorrect result....

In this worksheet, let’s generate 20 random times between 7:00 AM and 6:00 PM. To do this, we’ll use the RANDARRAY function. Now, RANDARRAY can generate both integers and decimal values. For Times, we want decimal values, because Excel times are fractional values of one day. For example, 6:00 AM is .25, 12:00 PM is .5, 6:00 PM is .75, and so on. To make it easy to change the Start and End times, I’ve put these values in cells C4 and C5....