| Hi ,
This week's newsletter is jam packed with tips. First, if you're tired of changing the layout of each new PivotTable you create, you'll want to watch the video below where I show you how to set the default layout, colours and NUMBER FORMATS:
And next we'll look at the Quick Access Toolbar, or QAT as it's also known. The QAT is not only a handy place for commonly used icons, but it also enables some super easy keyboard shortcuts for your favourite tools. The QAT sits either above or below the ribbon. I prefer to place it above the ribbon as this takes up less space because it sits in the green header bar: 
Whereas below the ribbon adds an extra row to my header area, although the upside is you get nice, coloured icons which are easier to see at a glance: 
You can change the location of the QAT via the drop down: 
Watch the Video 
Quick Access Toolbar Keyboard Shortcuts Adding an icon to the QAT is as easy as right-clicking on the icon > Add to Quick Access Toolbar: 
Alternatively, you can click on the drop down at the end of the QAT and choose from popular commands, or open the 'More Commands' dialog box as shown with the star in the list in the image below: 
The More Commands dialog box allows you to search through all icons available and add them to the QAT: 
QAT Keyboard Shortcuts The first 9 positions in the QAT are the most prime real estate in Excel because with the click of two keys you have a very handy set of keyboard shortcuts. Pressing the ALT key and then a number from 1 to 9 is the equivalent of clicking the icons with your mouse. In the image below you can see the number for each icon which appears after pressing ALT: 
It's as simple as learning the number for each icon and you're off and running! Note: you can also access the icons after position 9 by entering their number code e.g. the undo icon is number 09 so the shortcut is ALT > 0 > 9, it's just not quite as nice as entering a single number. And of course, in the case of Undo, it's easier to press CTRL+Z. Which Icons to put in the Quick Access Toolbar There's no perfect list of icons to include in the Quick Access Toolbar, but for such a prime piece of Excel real estate it's important to consider what gets a spot based on some strict criteria. For me that criteria are as follows: - There must not already be a super easy keyboard shortcut. e.g. don't put copy, paste or undo up there. Everyone knows CTRL+C, CTRL+V and CTRL+Z are the keyboard shortcuts for these most commonly used commands.
- It should save me multiple clicks. Most of the time the Home tab of the ribbon is visible, so putting commands in the QAT that are available here with a single click is probably a waste. One of the icons in my QAT is the Clear All button which requires a click on the drop down menu to expose it and then a second click on the icon, and there's no easy keyboard shortcut already available:
- And of course, it should be something I use regularly. Just because I have an icon in my QAT doesn't mean you should. If you don't use Power Pivot, then don't waste a spot in your QAT with the Power Pivot icon. And remember to update it regularly. Work habits change, so be sure to update your QAT in line with your current needs.
Mynda's QAT In case you're wondering what's in my QAT, below is a list of the icons: 
You might be wondering why I have some icons I expressly said not to include, so I'll explain: - Paste Values – although there's a couple of keyboard shortcuts for this, none are as short as ALT > 1
- Clear All requires two clicks to get to the button and it's something I use regularly to clear PivotTables, which the DELETE key simply can't handle.
- Clear All Filters is super handy when working with Excel Tables, which I do a lot.
- Refresh All is great for refreshing all queries and PivotTables in a workbook. There's a keyboard shortcut, CTRL+ALT+F5 but I find it cumbersome.
- Launch Power Query Editor – there's no practical keyboard shortcut for getting to this and it's at least a two click task depending on the route you take.
- Insert PivotTable – at least a couple of clicks or a cumbersome keyboard shortcut ALT > N > V > T
- Open Power Pivot Window – at least a couple of clicks unless you're lucky enough to be on the correct tab of the ribbon when you need it.
- Select Objects – I use this all the time and it bugs me having to click so many times to get to it.
- Manage Conditional Formatting Rules – at least two clicks and the option is at the bottom of a long list, which bugs me!
- Undo – this is outside the top 9 so it's not taking up a prime position. I keep it in the QAT so that I can see the undo stack from the drop down:
- Redo – same as for Redo
- Save – I have autosave always turned on these days, but sometimes I like the satisfaction of clicking the button and knowing that the file is actually saving. Call me old fashioned!
Have a great day! Mynda Treacy
Co-Founder | My Online Training Hub
| 
Learn with us!
No comments:
Post a Comment