This post and video contain lots of tips for moving your charts and shapes with the arrow keys. This is very useful when you need to arrange and align multiple shapes on your worksheet. I also have a free Chart Alignment add-in that allows you to move the objects inside a chart (titles, labels, legends) with the arrow keys and alignment buttons.
Typically when you select a chart with your mouse, the box surrounding the chart looks like the following.
When the chart is selected like this, nothing happens when you press the arrow keys to try and move the chart around the worksheet. Instead, the objects within the chart are cycled through and selected. Not what we want.
Hold Ctrl and Left Click the chart – This selects the shape object that the chart resides in. Circles will appear on each corner of the chart.
The chart can now be moved around on the sheet with the arrow keys, making it easy to align it with other shapes, charts, or cells in your worksheet.
Select multiple charts or shapes – Hold the Ctrl key and select at least two charts using left click. The bounding box will still look like the same as when you only have one chart selected, but you are able to move the charts with the arrow keys.
Moving shapes inside a chart can get a bit confusing. Shapes, such as a text box or rectangle, can reside in two places:
The confusing part is that a shape residing outside the chart on the worksheet can be placed on top of the chart. This makes it look like the shape is inside the chart even though it is not. In this case, when you move the chart, the shape will NOT move with it unless you have both the shape and the chart selected.
A shape located inside the chart will move with the chart. This makes it easier to move and align the chart because you don't have to worry about selecting multiple objects when doing the alignment.
There are two ways to get a shape inside a chart:
Now that your shape is residing inside your chart, you can use the arrow keys to move it around and align it with the other objects in your chart (title, axis, legend, etc.) See instructions below the screencast.
To move a shape inside the chart with arrow keys:
You should now be able to get all your charts and shapes lined up to perfection using the incremental movements of the arrow keys. This is very useful if you read my post on Panel Charts as an alternative to Stacked Charts and want to create panel charts. In a future post I will share more tips and shortcuts for aligning and viewing your charts.
What are some shortcuts you use to align your charts?
Excel has a built-in feature to convert text to numbers, but it can be tricky to use when you are trying to select a long list of cells or multiple rows and columns. In the video below I describe how to use keyboard shortcuts to accomplish this very quickly.
Here's a quick guide for the keyboard shortcuts to select the range of cells you want to convert.
The Menu Key is located between the Alt and Ctrl keys on the right side of the keyboard. If your keyboard does not have a menu key, you can use Shift +F10 as an alternate.
I find the built-in feature to be the fastest, but there are other ways to accomplish this task:
Do you have a different or easier way? Please leave a comment.
The Quick Access Toolbar (QAT) in Excel 2007 is extremely useful for creating keyboard shortcuts for items that don't have a defined keyboard shortcut. For example, there is no defined keyboard shortcut to Paste Values but you can easily set it up on the QAT.
The QAT is accessed through the keyboard by pressing the Alt key. Press and release the Alt key and you will see numbers appear next to each icon on the QAT.
So Alt + [the icon number] is the keyboard shortcut for each item. In this example, the keyboard shortcut for paste values is Alt+1. The keyboard shortcut for the format painter is Alt+2.
There are two ways to use the keyboard shortcut.
I tend to move the icons around based on the task I'm working on in Excel. If I'm going to be using the format painter a lot, I will move it to the “1” position (farthest left) while I'm using it. This is mainly because it is easier to Press Alt+1, Alt+2, and Alt+3 on the keyboard by placing your right thumb on Alt and right index finger on the number. The other numbers can be a bit of a stretch, depending on your keyboard.
See my post on how to setup the QAT for more details.
The Quick Access Toolbar (QAT) in Excel 2007 is a great option for shortcuts to your most used commands. It's faster to click on an icon in the QAT (one click) versus clicking on the ribbon tab and then the command (two clicks + mouse navigation). And you can use keyboard shortcuts for the QAT to make things even easier.
Setting up the QAT is simple and consists of two steps: Adding commands and arranging the icons.
The easiest way to add commands to the toolbar is to:
The command icon will be added to the end of the QAT.
To arrange the icons:
The order of the icons becomes important when you are using keyboard shortcuts to access the commands. And I highly recommend this as a much faster way to execute commands in Excel, versus using the mouse. You can also use the Options window (above) to add and remove commands from the toolbar.
This will depend on what you use Excel for and which commands you use most often. If you are doing lots of data entry and manipulation you might want to have the “Paste Values” and “Format Painter” commands at the top of your list. Reporting and analyzing, you may want some charting and page layout commands. The key is to have the commands you use most often located closest to the left because the keyboard shortcuts are easier to press with your thumb and index finger.
The toolbar can be place above or below the ribbon by clicking the down arrow to the right of the QAT and selecting Show Below the Ribbon or Show Above the Ribbon.
I prefer to have it above because it reduces the total vertical height of the top toolbars, which gives you more spreadsheet real estate. But you may find that you have so many icons that it fits better below the ribbon. If you are a heavy mouse user (vs. keyboard shortcuts) then it is also easier to navigate to the QAT if it is below the ribbon.
Checkout my recommendations on commonly used commands for the Quick Access Toolbar.