MrExcel Excel Videos

Pasting data on an Excel chart is one way to compare two different-sized populations on an XY Scatter chart. Recap: X-Y Scatter Charts with 2 series are difficult to create Create a one-series chart Select the new data, including the headings Click the chart Paste Special Choose X setting Add a legend to tell the series apart

Update last month's charts by pasting a new data point to the chart. Recap: You do not have to re-create your charts each month Type the new data near the old data Select the new data including the heading Ctrl+C to copy Select the chart Ctrl+V to paste the new data on the Excel chart To remove data use the blue handles To remove the center series you can hide the row To remove the center series, select it and press Delete To remove a series in 2013 or newer, use the Funnel icon

Alt+F1 creates a clustered column chart. But you can customize Alt+F1 to create the perfect chart for your job. Recap: F11 was the old shortcut key to create a chart as a new sheet Alt+F1 creates the same chart on the current sheet You can customize what you get from F1 Customize your favorite chart and save as a template Change Display Units to have scale appear in millions Add data labels (they will also be in millions) Move legend to the top Change color of any series Next time, you can use the dialog launcher to find your template Right-click the template and set as Default Chart You can now create perfect charts with Alt+F1 Thanks to Areef Ali, Olga Kryuchkova, and Wendy Sprakes for suggesting this feature.

You close Excel and answer "Don't Save" when you really needed to save. There are two potential ways to get the file back. Thanks to Office MVP Beth Melton for rounding out my knowledge of this trick. Workbooks that never had a name You have a new workbook. You make changes. The workbook gets through an AutoSave You close without saving. When you open Excel, scroll all the way down to find Recover Unsaved Workbooks Workbooks that did have a name You open a workbook. You make changes. The workbook gets through an AutoSave You close without saving. Normally, Excel keeps last five autosave versions (one every 10 minutes, provided there has been a change) When you close without saving, all are lost, except the last version. Go to File, Info...

Save your favorite margins, headers, footers & styles in the default workbook template. Recap: Start with a blank workbook with a single sheet Do all of your favorite customizations Fit to 1 page Narrow Margins Custom Footer Change styles? Create a default pivot table style and delete the pivot table? Decide XLTM (allows macros) or XLTX (does not) Save the workbook to %APPDATA% then Microsoft\Excel\XLStart Save it twice. Once as "Book". Once as "Sheet" Use Ctrl+N to create a new workbook with the settings from Book Insert Worksheet to insert a worksheet with settings from Sheet

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...