Power Excel with MrExcel: Master Pivot Tables, Subtotals, Charts, VLOOKUP, IF, Data Analysis in Excel 2010-2013
529
Power Excel with MrExcel: Master Pivot Tables, Subtotals, Charts, VLOOKUP, IF, Data Analysis in Excel 2010-2013
529eBookFourth edition (Fourth edition)
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615471256 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 04/01/2015 |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 529 |
| File size: | 45 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Read an Excerpt
Power Excel with MrExcel
567 Excel Mysteries Solved
By Bill Jelen
Holy Macro! Books
Copyright © 2015 Bill JelenAll rights reserved.
ISBN: 978-1-61547-348-9
CHAPTER 1
PART 1 THE EXCEL ENVIRONMENT
WHAT IS OFFICE 365? DO I HAVE TO WORK IN A BROWSER?
Problem: My company says they are switching to Office 365. Does that mean I have to edit all of my workbooks online?
Strategy: No. When you get Office 365, you get the full version of Excel 2013. It streams to your computer. You are not stuck working in a browser. You can keep saving your workbooks locally on your own computer or company network. Optionally, if you have to work on a file at home, you can securely save it to your OneDrive account and then open the file from home.
The PR people at Microsoft tell this story: Back in the 1930's, people were afraid of banks, so they kept their money under the mattress. If there was a house fire, their savings were lost. Microsoft says that people keeping their files on the local hard drive is a similar situation: if there is a catastrophe, you local hard drive and the backup media are all going to be lost. I am not quite sure I buy the analogy just yet. After all, the FDIC insures my money in the bank. I don't see an FDIC for data yet.
What's In It For Me: You can get new features sooner. For the last decade, Microsoft would release a version of Office every 3 years. All of the teams: Excel, Word, PowerPoint all had to coordinate and release on the same day. It is inefficient. The Excel team might dream up a new feature, code it, and then you have to wait 3 years to get it. With Office 365, you always get the latest bits. There is discussion that Excel 2015 will be the last large release of Excel. After that, it will all me incremental updates.
As one tiny part of Office 365, you do get to use online versions of Word, Excel, and PowerPoint. These are versions of Excel running in a browser. They work great in an emergency (you are visiting grandma and she doesn't have Excel). In fact, the Excel Web App does a few things better than regular Excel. You can create online surveys. You can have 20 co-workers all working in the same spreadsheet at the same time. Read more about these features later.
WHY DO I HAVE TO SIGN IN TO EXCEL 2013?
Problem: What is the deal with signing in to Office? Any why do they want my Facebook 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.
• You have a picture of yourself in the Excel window, in case you forget who you are.
4. You are signed in to Office.
Saving your Flickr & Facebook information allows you to Insert, Online Pictures and easily add photos that you've uploaded to the file sharing sites.
WHY FACEBOOK, LINKEDIN AND TWITTER?
Problem: Why would I connect Excel 2013 to Twitter?
Strategy: You can tweet your workbooks for public use. Follow these steps:
1. Go to File, Accounts and enter credentials for Facebook, Twitter, and/or LinkedIn.
2. Save the workbook to OneDrive.
3. In Excel 2013, go to File, Share, Post to Social Networks.
4. Chose if people can edit or not.
5. Add a message.
6. Click Post.
7. Close the workbook so others can access it.
5. Post to social media.
6. If you have run out of celebrity gossip to tweet, you can always tweet your Excel formulas.
When someone follows the 1drv link, they can view, edit, or download your workbook.
Gotcha: Your Facebook friends will think you are a geek.
Tip: If you are new to Twitter, follow @MrExcel for daily Excel tips. I also accept all LinkedIn invites.
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 do one of these hacks:
* Open Excel on the left monitor. Use the "Restore Down" icon in the top right to make the window not be full screen. Drag the right edge of the application window across to the other screen. Use View, Arrange All, Tiled and drag windows to the right side. Downside: the ribbon and formula bar is always on the left monitor.
* 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.
FIND ICONS ON THE RIBBON
Problem: The new ribbon user interface might be great for people new to Excel, but I knew the old Excel perfectly well. Why did Microsoft put pivot tables on the Insert tab instead of the Data tab
Strategy: The transition does not take that long (in cosmic time), if you follow a few basic rules.
Here are some simple guidelines:
* The Home tab contains the most frequently used commands. If you want to find something, start looking on the Home tab. The Excel 2003 Edit menu, Format menu, and Formatting toolbar is here.
* The most-used commands on the Excel 2003 Insert menu are not on the Insert tab in Excel 2013! Instead, commands to insert cells, rows, columns, and worksheets are on an Insert dropdown on the Home tab. Commands to insert a function or a name are on the Formulas tab. Insert Comment is on the Review tab.
* The Pivot Tables command has been moved from the Data menu to the left side of the Insert tab.
* Most Excel 2003 File menu commands are on the Excel 2013 File tab. This tab is called the Backstage view and is discussed in detail later. A few File commands are elsewhere. For example, Save Workspace is on the View tab. Page Setup and Print Area are on the Page Layout tab.
* Everything on the old Window menu and most things from the old View menu are now on the View tab.
* Help is now the question mark icon at the top right of the window. Everything else that used to be on the Help menu is now under File, Help. Except Clippy. Clippy has retired.
* The old Tools menu has been split among many tabs. You will find these commands spread among the Review, Formulas, Home, Data, View, and File tabs.
Microsoft does not provide a classic mode, but third party companies do. Check out the Classic Excel Menu from AddInTools.com or Toolbar Toggle. With both of these add-ins, you can work in the Excel 2003 menu, and then switch over to the ribbon menu when you need to access new features.
SERIOUSLY, I REALLY CAN'T FIND THIS
Problem: I've looked everywhere, and I can not find certain commands.
Strategy: I will show you some strategies for finding icons, but first, here are the ones that through me for a loop:
* Help is now the question mark at the top right of Excel.
* Save Workspace is now on the right side of the View tab.
* Protect Sheet is now on the Review tab.
* Move or Copy Sheet is in the Format dropdown on Home.
* Insert Rows, Insert Columns is in the Insert dropdown on Home.
* The "X" to close Excel is missing from Excel 2013. It is at the bottom of the Excel 2010 File tab. In either case, you should add the Exit to the Quick Access Toolbar or use Alt+F+X.
Alternate Strategy: You can find any command in the Customize dialog box. Hover over the command to see a tooltip explaining where to find the command in Excel. Follow these steps to locate a command.
7. Hover over a command to find the tab and group name.
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. When you find your command, hover. A tooltip appears showing you the ribbon tab, the group, then the command.
Gotcha: Sometimes, the tooltip will indicates that your command is in the dreaded Commands Not In The Ribbon category. You will have to add these commands to the Quick Access Toolbar or to the Ribbon in 2010.
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?
8. A subset of macro commands are available on the View tab.
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.
9. Microsoft disable the Developer tab by default.
10. If you use macros, enable the Developer tab.
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.
11. Once you've recorded a macro, the Stop and Record buttons will appear 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.
WHAT HAPPENED TO TOOLS, OPTIONS?
Problem: Where is the Excel 2003 Options command?
Strategy: Microsoft now provides an Excel Options dialog. You access it with File, Options.
12. Now 10 categories instead of 13 tabs.
The new Excel Options dialog has 9 categories instead of the previous 13 tabs. It incorporates most settings that used to be in the Options dialog, plus Tools, AutoCorrect, Tools, Macro, Security, Tools, Add-Ins, and many options from the old Help menu.
In what seems like a confusing move, Excel took a few options from the old General tab, combined them with some new options, and moved them to the General category in the Excel Options dialog.
The Advanced category in Excel offers 10 different sections. You will find that many of the former tabs from the Excel 2003 Options dialog have moved to the Advanced category. Instead of flipping from tab to tab to tab in Excel 2003, you can scroll through the long list of Advanced options in Excel.
The final four categories in the Excel Options dialog do not correspond to the Excel 2003 Options dialog:
* Customize Ribbon: For adding groups to the ribbon
* Quick Access Toolbar: For adding icons to the Quick Access toolbar
* Add-Ins: For managing add-ins (similar to Tools, Add-Ins in 2003)
* Trust Center: For managing security and much more
The following table maps the Excel 2003 Options dialog tabs to the Excel 2010 Excel Options dialog categories:
Excel 2003 TabExcel 2010/2013 Category
View
Advanced (Groups 4 - 6)
Calculation
Formulas & Advanced (Group 6)
Edit
Advanced (Groups 1 and 2)
General
General and Advanced (Group 9)
Transition
Advanced (Group 10)
Custom Lists
Advanced (Group 11) or Popular in Excel 2007
Charts
Advanced (Group 4)
Color
Save
International
Advanced (Group 1)
Save
Save
Error Checking
Proofing
Spelling
Proofing
Security
Removed from the dialog; select File, Save As, click
the Tools button, and choose the General category
CUSTOMIZING THE RIBBON
Problem: I want to customize the ribbon.
Strategy: Ribbon customizations in Excel 2010/2013 are weak compared with the customization capabilities in Excel 2003. However, they are better than the inability to customize in Excel 2007.
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.
13. Decide where you want the new group to appear.
Right-click anywhere on the ribbon and choose Customize the Ribbon.
14. Right-click the ribbon to access this menu.
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.
15. Choose where the new group should go.
Excel adds a new group with the name of New Group (Custom). Click the Rename button below the list box.
16. Choose to rename the group
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 21.
17. Type a new name and choose an icon to represent the group.
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.
18. Choose icons to add to the new group.
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.
19. The custom group is added to the ribbon.
If you are wondering why you had to choose an icon back in Fig 17, 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.
20. The icon from Fig 17 shows with a smaller window size.
Note back in Fig 13 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.
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.
(Continues...)
Excerpted from Power Excel with MrExcel by Bill Jelen. Copyright © 2015 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.