MrExcel Excel Videos

What if you need some extra text on the subtotal rows in Excel? Episode 1995 shows you how. Episode recap: If you need additional text on a subtotal row Collapse to #2 view Select the range where the text should be Home, Find & Select, Go To Special, Visible Cells Build a formula pointing one row above and use Ctrl+Enter Bonus trick for using SUM on most totals and Count on one Excel's method puts Total on one row and Count on another Better: Put Sum in all columns, then Ctrl+H to Replace Replace (9, with (3, Custom number format to show Count: 47

After adding subtotals by customer in Excel, can you sort the largest customer to the top? Yes, you can. Recap: Thanks to Derek Fraley from Springfield MO for this tip Add Subtotals Use Group and Outline button to show only subtotals Sort descending by revenue Excel sorts chunks of records Sort again by customer Grand total goes back to the bottom

If your Excel columns are in the wrong sequence, you can sort left to right. Excel can sort left to right Insert a row with the correct column sequence Use the Sort dialog Click Options, then Sort Left to Right Also note this dialog offers case-sensitive sorting Column widths do not correct, but Alt+OCA will fix Bonus tip with Shift+Spacebar and Ctrl+Spacebar How to remember which is which? Ctrl starts with C, just like Column Shift key is wider than Ctrl key, just as a row is wider than a column Once you select the entire row or column, Shift+Drag it into location

Using Cut and Insert Cut Cells is a slow way to rearrange rows. Podcast 1978 introduced Custom List for the Fill Handle. Sorting data into a special sequence is another benefit of custom lists. Type the list into the correct sequence. File, Options, Advanced, 83%, Edit Custom Lists, Import Use the Sort dialog In Sort Order, open the dropdown and choose Custom List Interesting (?) that you can sort the list reverse after choosing Thanks to @NeedForExcel for suggesting this tip

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