MrExcel Excel Videos

The fill handle is great for Months, Weekdays, Quarters, and Dates, but why won't it fill 1, 2, 3? This episode shows many workarounds. Also includes: You can fill a series by using the Fill Handle You can drag down or right Filling months works automatically Filling weekdays works automatically Type in ALL CAPS to have the fill be all caps Abbreviations work for months and weekdays For years and quarters: 1Q-2016 Any word followed by a number works Hold down Ctrl key to have 1 fill to 1 2 3 You can press Ctrl after you start dragging Hold down Ctrl to copy dates instead of filling Right-click and drag to fill weekdays or months or years

Create ad-hoc totals for an Excel data set where you will be filtering the data. Also in this video: Using SUBTOTAL(109,) for summing data that has been manual hidden rows. Using Ctrl+Ditto to copy the exact formula down one row.

There is a faster way to use the Filters in Excel. It is reminiscent of Filter by Selection from Access, but called AutoFilter in Excel. You will have to customize the Quick Access Toolbar to find the command.

Tip #1 from the MrExcel XL book - fast ways to copy a formula down a column. Also in this video: Ctrl+Down Arrow to move to end of a block of data Ctrl+Shift Up Arrow to select to top of a block of data Ctrl+D to fill the formula from the first row of a selection down Double-click the fill handle PROPER function & character for concatenation ROMAN function ARABIC function

John asks a common question: When I enter a value in B, can you immediately add the value to C and clear out B for the next entry? While this can be done, the loss of Undo makes it risky. I show a VBA macro to do immediately and also to sweep a batch of entries.

This video covers: Using the Fill Handle to generate a straight-line forecast. Using the new Exponential Smoothing ETS.FORECAST function in Excel 2016. Using Forecast Sheets in Excel 2016. Seeing R-Squared using a trendline on a chart.

Fabian asks if a VLOOKUP can sum all of the values in the matching row. This video looks at how to solve the problem using the "Address" version of INDEX. Also - a plug for my upcoming seminars in March 2016 in Australia and New Zealand

Sam wants to Stop Calculating some cells in a worksheet but keep the formula there. Other cells need to keep calculating. Paste Values would cause the formula to be lost. Although that functionality is not built in, a VBA macro simulates what Sam is looking for.

This is a follow up to yesterday's Duel 176 - with four new ways to solve the problem: Pivot table, AGGREGATE, Array Formula, MINIFS.