Power Excel with MrExcel - 2017 Edition: Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis
553
Power Excel with MrExcel - 2017 Edition: Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis
553Paperback(Fifth Edition, Fifth edition)
-
PICK UP IN STORECheck Availability at Nearby Stores
Available within 2 business hours
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615470495 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 01/01/2017 |
| Edition description: | Fifth Edition, Fifth edition |
| Pages: | 553 |
| Sales rank: | 1,108,558 |
| Product dimensions: | 8.40(w) x 10.80(h) x 1.40(d) |
About the Author
Read an Excerpt
617 Excel Mysteries Solved
By Bill Jelen
Holy Macro! Books
Copyright © 2017 Bill JelenAll rights reserved.
ISBN: 978-1-61547-049-5
CHAPTER 1
PART #1 - THE EXCEL ENVIRONMENT
WHY DOES OFFICE 365 HAVE BETTER FEATURES?
Problem: I have TEXTJOIN and Funnel Charts at home, but not at work, What is going on?
Strategy: You have Office 365 at home. By agreeing to pay a monthly or annual fee for Office, you are getting frequent updates and new features. Microsoft is at war with the I.T. departments who still want to buy Office the old way. If someone buys Office 2016, they get a few new features, but they will never get the new monthly features. The days of the annual Service Pack are gone.
In the good old days (2003, 2007, 2010, 2013), Microsoft would spend three years putting new features into Office and the customers would invest $400 every other release. Now, Microsoft wants you to rent your copy of Office. Pay $10, $12, or $15 a month or $99 a year and you will get monthly updates.
I originally said that I would never rent Office. But then Microsoft started putting must-have features in Office 365 and not in the regular release of Office, so now I can see that renting Office 365 is the only logical choice.
With Office 365, you will get to use mobile versions of Excel on an iPad, iPhone or Android device.
WHICH VERSION OF OFFICE 365 HAS POWER PIVOT?
Problem: The Office 365 website is super-confusing. I don't want to buy the wrong version.
Strategy: If you want Power Pivot and all options of Power Query, you need to go with the $12 a month Pro Plus plan or the $15 a month E3 plan. Surprisingly, the $12.50 Small Business plan does not have Power Pivot. And, in an incredibly short-sighted move, the University edition does not have Power Pivot. If you don't think you will ever need Power Pivot, then the $10 a month Home edition will allow you to install Excel on five computers.
WHY DO I HAVE TO SIGN IN TO EXCEL?
Problem: What is the deal with signing in to Office? Any why do they want my Flickr info in Excel?
Strategy: Even if you are not using Office 365 to subscribe to Office, Excel will ask you to store your Office account information in the File, Account pane. This is not some attempt to harvest e-mails so they can spam you about the next MrExcel Power Excel seminar. There are actually good things that happen when you sign in on all of your computers:
* Recent files that you save to OneDrive will appear in the recent list of all of your computers. If you were working on a file at work and save it to the cloud, it will be available when you get home. No more forgetting the USB drive at the office.
* Ribbon customizations are carried through to all of your computers.
Saving your Flickr information allows you to Insert, Online Pictures and easily add photos that you've uploaded to the file sharing sites. Twitter, LinkedIn, and Facebook information was used in Excel 2013 to allow posting a workbook to social networks. That feature never caught on and was removed from Excel 2016.
HOW CAN I USE EXCEL ON DUAL MONITORS?
Problem: Why is it so hard to use Excel on two monitors?
Strategy: This problem is fixed in Excel 2013. Every Excel workbook gets its own window, complete with a ribbon and formula bar. Open two workbooks, drag on to the other monitor and you will have 36 linear inches of Excel.
In Excel 2010, you have to use this hack:
* Force Excel 2010 to open a second instance of Excel. You can hold down the Shift key while opening Excel to create a second instance of Excel. Downside: you can not copy formulas from one instance to the other
HOW CAN I OPEN THE SAME WORKBOOK TWICE?
Problem: I used to open two copies of the same workbook. I could select cells in copy B, see the total in the status bar, and then type that information in to a different place in copy A. Now that Excel 2013 opens every workbook in a new window, I can not open the same workbook twice.
Strategy: Open the first instance of the workbook. Then, force Excel to open in a new instance by holding down the Shift key while opening Excel. In the second instance of Excel, use File, Open to open the workbook again.
FIND ICONS ON THE RIBBON
Problem: I know a certain feature exists in Excel, but I can not find it in the Ribbon.
Strategy: Use the new Tell Me feature in Excel 2016. Located to the right of the last tab in the Ribbon, a box with a lightbulb and "Tell Me What You Want To Do" appears. Click in the box and type the name of the feature. A selectable list of commands appears.
Gotcha: If you are in Excel 2013 and don't have Tell Me, open an Excel workbook at Office.Live.Com and use the Tell Me command in Excel Online.
WHERE IS FILE, EXIT?
Problem: What happened to the old Exit command?
Strategy: Although Exit is missing from the File menu in 2013-2016, you can use Alt+F, X to invoke the Exit command. Or, add Exit to the Quick Access Toolbar.
1. The top-left corner of Excel contains a tiny strip with icons for Save, Undo, and Redo. Right-click that strip and choose Customize Quick Access Toolbar.
2. The top left dropdown starts with Popular Commands. Open that dropdown and choose All Commands. You now have an alphabetical list of 2000+ commands.
3. Scroll through the list to find Exit. When you find your command, click the command. Click the Add>> button in the center of the screen to add the command to the Quick Access Toolbar.
WHERE ARE MY MACROS?
Problem: Did Microsoft abandon the macro facility? Where are the buttons to record a new macro, run a macro, and so on? How do I get to the Visual Basic Editor?
Strategy: Most of the macro icons are hidden. Three macro options appear on the extreme right end of the View tab. You use the Macros dropdown to view macros, record a macro, or use relative references while recording a macro.
To access the rest of the macro functionality, you need to enable a hidden Developer ribbon tab. Choose File, Options, Customize Ribbon. Add a checkmark next to Developer. The Developer tab offers macro commands, buttons from the former Forms toolbar and Control Toolbox, and XML settings.
Additional Details: When you are recording a macro, instead of seeing the Stop Recording icon floating above the Excel window, you now see it in the Status Bar, next to Ready.
The same area of the status bar includes a Record Macro button when you are not recording a macro. However, because there is not a Relative References button, you cannot effectively record macros without using either the View tab or the Developer tab of the ribbon.
CUSTOMIZING THE RIBBON
Problem: I want to customize the ribbon.
Strategy: Ribbon customizations in Excel 2013/2016 are weak compared with the customization capabilities in Excel 2003. You might feel like the Pivot Table command belongs on the Data tab rather than on the Insert tab. You can add a new group to the Data tab to hold the pivot table icons.
First, look at the ribbon and decide where you want the new group to appear. Perhaps a good location would be between the Sort & Filter group and the Data Tools group.
Right-click anywhere on the ribbon and choose Customize the Ribbon.
The Customize dialog contains two large list boxes. You will first be working with the list box on the right side of the screen. Expand the plus sign next to the Data entry to see the groups on the Data tab. If you want a new group to appear after the Sort & Filter group, click Sort & Filter, and then click the New Group button below the list box.
Excel adds a new group with the name of New Group (Custom). Click the Rename button below the list box.
Type a new name in the Rename dialog. Also, choose an icon. This icon will appear only when the Excel window gets small enough to force the group into a dropdown, as shown later in Fig 17..
Note: The 180 icons available are a far cry from the 4096 icons available in Excel 2003. As I pointed out at the beginning of this chapter, toolbar customization took a giant step backward after Excel 2003.
After renaming the new group in the list box on the right side, it is time to turn your attention to the list box on the left side. It starts out showing Popular Commands. Use the dropdown above the left list box to change from Popular Commands to All Commands.
Scroll down to the commands starting with Pivot. You will see a confusing array of commands. Click the first PivotTable icon, and click the Add button in the center of the screen. Click the second PivotChart icon, and then click the Add button. Click PivotTable and PivotChart Wizard, and then click the Add button.
It is sometimes difficult to figure out which icons you want. There are two icons that say PivotTable. The first icon is simply an icon. The second icon is an icon with a rightward-facing triangle on the right side of the list box. That triangle indicates that the second icon is actually a dropdown that leads to more choices. That second PivotTable dropdown icon is the icon at the bottom half of the Insert tab's Pivot Table group. It opens to enable you to choose between PivotTable and PivotChart. You might prefer to use that icon instead.
Two PivotChart icons are available. Hover over each icon to see that the first one is the PivotChart icon available on the PivotTable Tools Options tab. You will also see that the second icon is the one on the Insert tab. The first PivotChart icon will be grayed out unless you are in a pivot table. The second PivotChart icon is the one that is used to create a new pivot chart from a data set.
This figure shows the resulting group on the Data tab.
If you are wondering why you had to choose an icon back in Fig 13, it is for people who have the Excel window resized to a narrower width. If you make your Excel window narrower, the custom group will eventually get squished down to a single dropdown. Your icon will appear on that dropdown, as shown here.
Note back in Fig 9 that the Sort icon appears as a large icon with a caption and that the AZ and ZA icons appear as small icons without a caption. How can you specify that the pivot table icon should be large and the pivot chart and wizard icons should be small? You can't. At least not with the Excel interface.
If you want to start writing some XML and VBA, you can gain control over the size and images used in the ribbon. For an excellent book on this daunting task, look for RibbonX: Customizing the Office 2007 Ribbon by Robert Martin, Ken Puls and Teresa Hennig. Or, check out the Ribbon Commander utility described at http://mrx.cl/2dbS4Js.
I find that I spend most of my time on either the Home or the Data tab. If I could combine the left side of the Home tab with the right side of the Data tab, plus pivot tables, I would probably be able to spend all my time on one tab.
This figure shows a new MrExcel tab that reuses groups from other ribbon tabs to build a new tab.
The general steps for creating a new ribbon tab are as follows:
1. Right-click the Ribbon and choose Customize the Ribbon.
2. Click New Tab at the bottom right of the dialog.
3. Click Rename and give the tab a name.
4. Use the Up and Down buttons at the right side of the dialog to move the new tab into the proper location.
5. From the left dropdown, choose Main Tabs.
6. In the left dropdown, expand an existing tab and find an existing group that you want to add to your new tab. Click that group and click Add.
7. Repeat step 6 to add additional groups.
8. You can reuse a custom group that you created previously. In the left dropdown, choose Custom Tabs and Groups. You can move the Pivot Table (Custom) tab created earlier in this chapter onto your new ribbon tab.
9. Click OK to finish customizing the ribbon tab.
GO WIDE
Problem: My ribbon looks different than my co-workers.
Strategy: Invest in a wide-screen monitor. The Excel experience dramatically improves at a 1440x900 or 1920x1080 resolution.
When you reduce the size of the Excel window, Excel automatically starts consolidating ribbon options into smaller icons and then groups. The next four figures show details of the Home tab of the ribbon at different sizes.
If you are the go-to person for solving Excel problems and you are helping a co- worker over the phone without using GoToMeeting, there will be some frustration as you tell them to look for the Bad, Good, Neutral tiles and they can only see a Styles dropdown.
MINIMIZE THE RIBBON TO FREE UP A FEW MORE ROWS
Problem: The ribbon is taking up a lot of real estate at the top of my screen. It distracts me. I spend 99% of my Excel time in the grid, so I don't need to see the ribbon all the time.
Strategy: You can minimize the ribbon, reducing it to a simple line of Home, Insert, Page Layout, Formulas, and so on.
To minimize the ribbon, you can either press Ctrl+F1 or right-click anywhere on the ribbon and then choose Minimize the Ribbon. You can also use the carat (^) icon at the right edge of the ribbon.
Additional Details: When you either click a ribbon tab with the mouse or use an Excel shortcut key, the ribbon will temporarily reappear. When you select the command from the ribbon, it will minimize again.
Double-click any ribbon tab to permanently exit minimized mode. Or, open any ribbon tab and then use the thumbtack icon on the right edge of the ribbon.
USE A WHEEL MOUSE TO SCROLL THROUGH THE RIBBON TABS
If you point your mouse at the ribbon and scroll the wheel, you will quickly move from Home to Insert to Page Layout and so on.
WHY DO THE CHARTING RIBBON TABS KEEP DISAPPEARING?
Occasionally, new tabs will appear on the right side of the ribbon. These tabs appear when the current selection includes SmartArt graphics, charts, drawings, pictures, pivot tables, pivot charts, worksheet headers, tables, ink, or when you are in the legacy Print Preview mode.
(Continues...)
Excerpted from 617 Excel Mysteries Solved by Bill Jelen. Copyright © 2017 Bill Jelen. 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.