MrExcel Excel Videos

Use a formula in Excel to put the worksheet name in a cell. Also in this episode: =CELL("filename",A1) returns the path and file name Look for the right square bracket using FIND Add 1 character to the result of FIND Pass that to MID Uservoice votes at Create a function that returns the list of sheet name(s) in the workbook = SHEETNAME(), =SHEETNAMES()

Ctrl+Drag sheet tabs to make a copy. This episode also covers: Right-click tab and choose Move or Copy Ctrl+Drag worksheet for Mar through June Select Jan:June and Ctrl+Drag January to make 6 copies Vote for my uservoice idea Fill Handle For Worksheet Copy Using an Immediate Macro to rename sheets Podcast Episode 1497 had FillHandleSheets macro: Learn Excel 2010 - "Sheets Revisted": Podcast #1497 There is some bonus VBA here to rename 12 sheets with month names.

A great trick for adding Total Row and Total Column in a single click. Also in this episode: Fill Handle works with "any word" and a number Fill handle with custom list from episode 1978 Using RANDBETWEEN for fake data but not budgets AutoSum all around by choosing data + total row and column Formatting using Heading 4 cell style Formatting using Title cell style I hate the comma icon in the Number tab Using dialog launcher to get to Format Cells Saving a regular comma style as a new Style Adding comma style to all future workbooks - page 20

Use the Fill Series dialog in Excel to fill from 1 to 100,000 in a few clicks. Also in this episode: It would take a long time to fill 1 to 100,000 Even if you use the Ctrl key while dragging the fill handle Type a starting number in a cell Home, Fill, Series… Change from Rows to Columns Click OK You can Fill from a series Cool trick with Shift+Enter in Name Box

The fill handle can fill months, weekdays, quarters. But you probably have a list it can not fill: Cost Centers, Product Lines. Learn how to set up a custom list so all future workbooks can fill your list. Also in this episode: Excel can fill a lot of series You can add a custom list Type the list in a column Select the list File - Options - Advanced - 83% Edit Custom Lists This is where they store Jan, Feb, Mar Click Import to import the list Works in every workbook on this computer in this version of Excel If you edit an existing list, don't forget to click Add button! Jan, Feb, … Dec, Total Brady Bunch prank

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