MrExcel Excel Videos

Several ways to transpose Excel data with a formula. In this episode: Paste Special Transpose does a snapshot of the data TRANSPOSE function requires Ctrl+Shift+Enter Using Ctrl+Shift+Enter with =2 off to the right INDEX function with position ROW(1:1) gives the number 1 It changes as you copy down Combine INDEX and ROW 3rd method: Replace = with ^=

View, Arrange All, Vertical lets you compare workbooks side by side. But what if you have to compare two worksheets in the same workbook? Episode 1985 shows you how to use New Window and View Side by Side to eyeball two worksheets. Episode Recap: Ctrl+PgDn and Ctrl+PgUp switch between sheets Create a second window with the workbook using View, New Window This creates a :2 version of the workbook View, Arrange All, Vertical First click selects window. Second click does the sheet To have them scroll together, use View Side by Side Toggle Synchronous Scrolling if rows are not lined up :2 version is not a new workbook It is a second camera looking at a different part of the workbook This tip sent in by Anne Walsh

Use a spearing formula or a 3D Reference to sum Jan through Dec. Episode recap: You need to total numbers on Jan, Feb, Mar, … Dec You could =Jan+Feb+Mar… But there is a faster way called a Spearing formula Also known as a 3D reference Start by typing =SUM( Click on first sheet Shift-click on last sheet Click on the cell Type ) and press Enter Anything between Jan through Dec is included Don't add new sheets with Grocery List 7 gallons of milk Don't randomly move August outside Mack Wilk trick using Start and End sheets Podcast 1836 Bizarre Bob Umlas wildcard trick =SUM('J*'!B4) Thanks to Othneil Denis for suggesting 3D Formula Reference

After watching episode 1981, you can now make copies of worksheets really fast. When your manager changes the request, you might have to change 12 sheets. This episode introduces you to the powerful but dangerous Group Mode. Also in this episode: Learned how to make copies of sheets quickly in podcast 1981 Manager 15 minute rule says your manager will change their mind 15 minutes after a request Select Jan sheet. Shift-click on Dec sheet to begin group mode Any change to Jan is happening to all sheets in the group Easy to forget that you are in group mode Every time I am in group mode, the phone rings Three ways to exit group mode Select a sheet not in the group. If all sheets are grouped, select any sheet other than the active sheet...

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