eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473502 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 09/01/2015 |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 120 |
| File size: | 27 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Read an Excerpt
Mr Excel XL
The 40 Greatest Excel Tips of All Time
By Bill Jelen, Szilvia Juhasz
Holy Macro! Books
Copyright © 2016 Szilvia JuhaszAll rights reserved.
ISBN: 978-1-61547-350-2
CHAPTER 1
#1 Double-Click the Fill Handle to Copy a Formula
You have thousands of rows of data. You've added a new formula in the top row of your data set, something like this =PROPER(A2&" "&B2), as shown below. You now need to copy the formula down to all of the rows of your data set.
Many people will grab the Fill Handle and start to drag down. But as you drag down, Excel starts going faster and faster. Starting in Excel 2010, there is a 200-microsecond pause at the last row of data. 200 microseconds is long enough for you to notice the pause but not long enough for you to react and let go of the mouse button. Before you know it, you've dragged the Fill Handle way too far.
The solution is to double-click the Fill Handle! Go to exactly the same spot where you start to drag the Fill Handle. The mouse pointer changes to a black plus sign. Double-click.
Excel looks at the surrounding data, finds the last row with data today, and copies the formula down to the last row of the data set.
In the past, empty cells in the column to the left would cause the "double-click the Fill Handle" trick to stop working just before the empty cell. But as you can see below, names like Madonna, Cher, or Pele will not cause problems. Provided that there is at least a diagonal path (for example, via B76-A77-B78), Excel will find the true bottom of the data set.
In my live Power Excel seminars, this trick always elicits a gasp from half the people in the room. It is my number-one time-saving trick.
Alternatives to Double-Clicking the Fill Handle
This trick is an awesome trick if all you've done to this point is drag the Fill Handle to the bottom of the data set. But there are even faster ways to solve this problem:
• Use Tables. If you would select one cell in A1:B112 and press Ctrl+T, Excel will format the range as a table. Once you have a table, simply enter the formula in C2. When you press Enter, it will be copied to the bottom.
• Use a complex but effective keyboard shortcut. This shortcut requires the adjacent column to have no empty cells. While it seems complicated to explain, the people who tell me about this shortcut can do the entire thing in the blink of an eye.
Here are the steps:
1. From your newly entered formula in C2, press the Left Arrow key to move to cell B2.
2. Press Ctrl+Down Arrow to move to the last row with data. In this case, B112.
3. Press the Right Arrow key to return to the bottom of the mostly empty column C.
4. From cell C112, press Ctrl+Shift+Up Arrow. This selects all of the blank cells next to your data, plus the formula in C2.
5. Press Ctrl+D to fill the formula in C2 to all of the blanks in the selection. (Note that Ctrl+R fills right, which might be useful in other situations.)
As an alternative, you can get the same results by pressing Ctrl+C before step 1 and replacing step 5 with Ctrl+V.
CHAPTER 2#2 Filter by Selection
The filter dropdowns have been in Excel for decades, but there is a much faster way to filter. Normally, you select a cell in your data, choose Data, Filter, open the dropdown menu on a column heading, uncheck Select All, then scroll through a long list of values, trying to find the desired item.
Microsoft Access invented a concept called Filter by Selection. It is simple. Find a cell that contains the value you want. Click Filter by Selection. The filter dropdowns are turned on, and the data is filtered to the selected value.
Nothing could be simpler.
Guess what? The Filter by Selection trick is also built into Excel, but it is hidden and mislabeled.
Here is how you can add this feature to your Quick Access Toolbar. Look at the top left of your Excel screen for the tiny row of icons with Save, Undo, and Redo. Right-click any of those icons and choose Customize Quick Access Toolbar.
There are two large listboxes in the dialog. Above the left listbox, open the dropdown and change from Popular Commands to Commands Not In the Ribbon.
In the left listbox, scroll to the command called AutoFilter and choose that command. That's right — the icon that does Filter by Selection is mislabeled as AutoFilter.
In the center of the dialog, click the Add>> button. The AutoFilter icon will move to the right listbox. Click OK to close the dialog.
Here is how to use the command. Say that you want to see all West region sales of Widgets. First, choose any cell in column B that contains West. Click the AutoFilter icon in the Quick Access Toolbar.
Excel turns on the filter dropdowns and automatically chooses only West from column B.
Next, choose any cell in column E that contains Widget. Click the AutoFilter icon again.
You could continue this process. For example, choose a Utilities cell in the Sector column and click AutoFilter.
How can it be that this cool feature has been in Excel since Excel 2003, but Microsoft does not document it? It was never really an official feature. The story is that one of the developers added the feature for internal use. Back in Excel 2003, there was already a Filter icon on the Standard toolbar, so no one would bother to add the apparently redundant AutoFilter icon.
Of course, this feature was added to Excel 2007's right-click menu, but three clicks deep: Right-click a value, choose Filter, then choose Filter by Selected Cell's Value.
Bonus Tip: Total Visible
After you've applied a filter, it would be great to see the total of the visible cells.
Select the blank cell below each of your numeric columns. Click the AutoSum button.
Instead of inserting SUM formulas, Excel inserts =SUBTOTAL(9, ...) formulas. The formula below shows you the total of only the visible cells.
Insert a few blank rows above your data. Cut the formulas from below the data and paste to row 1 with the label of Total Visible.
Now, as you change the filters, even if the data fills up more than one full screen of data, you will see the totals at the top of your worksheet.
CHAPTER 3#3 The Fill Handle Does Know 1, 2, 3 ...
Why does the Excel Fill Handle pretend it does not know how to count 1, 2, 3?
The Fill Handle is great for filling months, weekdays, quarters and dates. Why doesn't it know that 2 comes after 1?
In case you've never used the Fill Handle, try this: Type a month name in a cell. Select that cell. In the lower-right corner of the cell is a square dot. Click the dot and drag right or drag down. The tooltip increments to show the last month in the range.
When you let go of the mouse button, the months will fill. An icon appears giving you additional options.
How were we supposed to figure out that Ctrl makes the Fill Handle count instead of copy? I have no idea. I picked up the tip from Row 6 at an IMA seminar in Kent, Ohio. It turns out that Ctrl seems to make the Fill Handle behave in the opposite way. If you Ctrl+drag a date, Excel will copy instead of Fill.
I've heard another trick: type 1 in A1. Select A1 and the blank B1. Drag. Excel fills instead of copies.
Right-Click the Fill Handle for More Options
If you right-click and drag the Fill Handle, a menu appears with more options, like Weekdays, Months, and Years. This menu is great for dates.
Teach the Fill Handle a New List
The Fill Handle is a really handy tool. What if you could use it on all sorts of lists? You can teach Excel a new list, provided that you have anywhere from 2 to 240 items. Here is the easy way:
1. Type the list in a column in Excel.
2. Select the list.
3. Select File, Options, Advanced. Scroll almost to the bottom, and click Edit Custom Lists.
In the Custom Lists dialog, click Import.
I use this trick for lists that should be in Excel, such as a list of the U.S. States and a list of the letters of the alphabet.
Bonus Tip: Fill 1 to 100,000 in a Flash
What if you have to fill 100,000 cells of bagel flavors?
1. Type the first bagel flavor in A1.
2. Select A1.
3. Type A100000 in the Name box and press Shift+Enter. This selects from the current cell to A100000.
4. Home, Fill, Series. ... Click AutoFill in the Type box.
CHAPTER 4#4 Fast Worksheet Copy
Yes, you can right-click any sheet tab and choose Move or Copy to make a copy of your worksheet. But that is the very slow way to copy a worksheet.
The fast way: Hold down the Ctrl key. Drag the worksheet tab to the right.
The downside of this trick is that the new sheet is called January (2) instead of February — but that is the case with the Move or Copy method as well. In either case, double-click the sheet name and type a new name.
Ctrl+drag February to the right to create a sheet for March. Rename February (2) to March.
Select January. Shift-select March to select all worksheets. Hold down Ctrl and drag January to the right to create three more worksheets. Rename the three new sheets.
Select January. Shift-select June. Ctrl+Drag January to the right, and you've added the final six worksheets for the year. Rename those sheets.
Using this technique, you can quickly come up with 12 copies of the original worksheet.
Bonus Tip: Worksheet Name in a Cell
If you want each report to have the name of the worksheet as a title, use =TRIM(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)) &" Report"
The CELL() function in this case returns the full path\[File Name]SheetName. By looking for the right square bracket, you can figure out where the sheet name occurs.
Now You Are Making Copies Too Quickly
You've heard of the Manager's 15 minute rule, right? Any time your manager asks you for something, he or she comes back 15 minutes later and asks for an odd twist that he or she did not specify the first time. Now that you can create worksheet copies really quickly, there is more of a chance that you will have to make changes to all 12 sheets instead of just one sheet, when your manager comes back.
I will show you an amazingly powerful but incredibly dangerous tool called Group mode.
At this point, you have 12 worksheets that are mostly identical. You need to add totals to all 12 worksheets. To enter Group mode, right-click on the January tab and choose Select All Sheets.
The name of the workbook in the title bar now indicates you are in Group mode.
Anything you do to the January worksheet will now happen to all the sheets in the workbook.
Why is this dangerous? Because if you get distracted and you forget that you are in group mode, you might start entering January data and overwriting data on the 11 other worksheets!
When you are done adding totals, don't forget to right-click a sheet tab and choose Ungroup Sheets.
Bonus Tip: Create a SUM that Spears Through All Worksheets
So far, you have a workbook with 12 worksheets, one for each month. All of the worksheets have the same number of rows and columns. You want a Summary worksheet in order to total January through December.
The formula is =SUM(January:December!B4).
CHAPTER 5#5 Compare Sheets Side by Side with Synchronous Scrolling
This feature appeared in Excel 2003 with very little fanfare. Say that you have two workbooks that you want to compare side by side. You aren't looking for a VLOOKUP, but just want to "eyeball" the two workbooks. Open both workbooks. On the View tab, choose View Side by Side.
For whatever reason, Excel defaults to arranging the first workbook in the top half of the screen and the second workbook in the bottom half of the screen, which clearly means that someone on the Excel team does not know the meaning of "Side by Side."
The command doesn't say, "View One Above the Other," does it? While this choice annoys me, it is easy enough to truly get them side by side: Just select View, Arrange All, Vertical, OK.
At this point, one workbook is on the left half of your monitor, and the other workbook is on the right half. I have this monster 1080p monitor, which means that each workbook is only taking up a quarter of the screen real estate. So, for those of you with monitors more than twice as wide as your worksheet, hover over the right edge of the left workbook. You will see the mouse pointer below. Click and drag left.
Then drag the left edge of the right workbook to the left.
You end up with this arrangement of the two workbooks:
But this is nothing new. Excel 97 offered Arrange All Vertical. There is the big difference, though:
Grab the scrollbar and scroll the right workbook down so it starts at row 8. Miraculously, the left workbook scrolls at the same rate, and both workbooks are showing row 8 through 17.
This is great, until one workbook adds or deletes a row.
Then, things are out of sync. The Harlem Globetrotters were added on the left, so now we need row 19 on the left and row 18 on the right to scroll together.
The key is to temporarily turn off Synchronous Scrolling. This was turned on when you used View Side by Side. It is in the View tab, in the Window group, but these three icons collapse when the Excel window narrows, so you are likely to only see the icons, and not the words.
Turn off Synchronous Scrolling. Get the two workbooks lined up again. Turn on Synchronous Scrolling. As shown below, row 26 on the left is matched with row 25 on the right.
As you continue to scroll, the rows will remain lined up.
Can I Compare Sheet1 and Sheet2 of the Same Workbook?
Yes. Make sure you only have one workbook open. From the View tab, select New Window. It now appears that you have two workbooks open. One has :1 in the title bar, and one has :2 in the title bar. Select Sheet1 in :1 and Sheet2 in :2. Follow the View Side by Side steps from above. You will now be able to see both worksheets of the workbook side by side. When you are done, go to the :2 version of the file and click the X in the top right to close that window.
(Continues...)
Excerpted from Mr Excel XL by Bill Jelen, Szilvia Juhasz. Copyright © 2016 Szilvia Juhasz. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
Table of Contents
Contents
Dedication,Acknowledgments,
About the Authors,
About the Contributors,
Foreword,
Introduction,
Part 1 - Bill's Top 40 Tips,
#1 Double-Click the Fill Handle to Copy a Formula,
#2 Filter by Selection,
#3 The Fill Handle Does Know 1, 2, 3…,
#4 Fast Worksheet Copy,
#5 Compare Sheets Side by Side with Synchronous Scrolling,
#6 Turning Data Sideways with a Formula,
#7 Default Settings for All Future Workbooks,
#8 Recover Unsaved Workbooks,
#9 Perfect One-Click Charts,
#10 Paste New Data on a Chart,
#11 Sort East, Central, and West Using a Custom List,
#12 Sort Left to Right,
#13 Sort Subtotals,
#14 Easy Year-over-Year Report in a Pivot Table,
#15 True Top Five in a Pivot Table,
#16 Eliminate Pivot Table Annoyances,
#17 Replicate a Pivot Report For Each Rep,
#18 Use a Pivot Table to Compare Lists,
#19 Custom Chart Labels in Excel 2013,
#20 Build Dashboards with Sparklines and Slicers,
#21 GETPIVOTDATA Might Not Be Entirely Evil,
#22 Eliminate VLOOKUP with the Data Model,
#23 Budget Versus Actual via Power Pivot,
#24 F4 for Absolute and Repeat Last Command,
#25 The Fastest Way to Convert Formulas to Values,
#26 See All Formulas at Once,
#27 Discover New Functions Using fx,
#28 Calculate Nonstandard Work Weeks,
#29 Handle Multiple Conditions in IF,
#30 Cure Triskaidekaphobia with a Killer Formula,
#31 Troubleshooting VLOOKUP,
#32 Replace Nested IFs with a Lookup Table,
#33 Speed Up VLOOKUP,
#34 Protect All Formula Cells,
#35 Back into an Answer Using Goal Seek,
#36 Do 60 What-If Analyses with a Data Table,
#37 Find Optimal Solutions with Solver,
#38 Load a File List into Excel with Power Query,
#39 Pivot Table on a Map Using 3D Maps,
#40 Avoid Whiplash with Speak Cells,
Part 2 - 40 Keyboard Shortcuts,
Part 3 - Excel 30 Anniversary Tips,
#1 Wingdings and Other Unsung Font Heroes,
#2 In-Cell Charting with REPT(),
#3 AutoSum on a Matrix,
#4 Retrieve the Intersection Value – From Space!,
#5 How to Lose Your VBA-V (VBA-Virginity),
#6 Recording Your Very First Macro,
#7 Turn Your QAT into Your Personal Laugh Track,
#8 VBA For Troublemakers,
#9 Absolute, Relative, and Multiplication Tables,
#10 From Outline to Flat Table,
#11 Goal Seek for Renters and Shoe Lovers,
#12 Controlled Lists with Data Validation,
#13 The XSzil Guide to Excel Protection,
#14 Named Formulas: Yesterday and Tomorrow,
#15 Named Formulas: VLOOKUP Without the Sausage,
#16 Controlled Formula Lists with Data Validation,
#17 Dependent Data Validation Lists,
#18 Smile for the Excel Camera!,
#19 VLOOKUP – to the Left!,
#20 Data Shaping (Unpivot) with Power Query,
#21 XSzil Design Tips for Excel for Presenters and Trainers,
#22 Fill in a Flash,
#23 Suppress Errors with IFERROR,
#24 Plural Conditions with SUMIFS,
#25 Formatting as Façade,
#26 Extract Uniques with a CSE Formula,
#27 Consolidate Quarterly Worksheets,
#28 A2:INDEX() Is Like a Non-Volatile OFFSET,
#29 Sort and Filter by Color or Icon,
#30 Word for Excellers,
#31 40 Tips for the next MXL Book,
Part 4 - Excel Fun,
Excel Jokes,
Excel Tweets,
Excel Stories,
Excel Classifieds / Excel Resources,