Microsoft Excel 2007 – Tips and Shortcuts
Using the Drawing Tool within Excel 2007
These are the 16 Best Tips for building Cause Maps and Process Maps using the drawing tool Excel 2007. They are intended for ThinkReliability clients who have attended a workshop or participated in a faciliation. This tip sheet is for ThinkReliability clients and Cause Mapping users who have used the drawing tool in Excel 2003 and have moved or are about to move to Excel 2007.
Menu Bar and Ribbon
The Menu Bar in Excel 2007 is different from 2003. It will take some practice, but all the functionality is still there - it is just in a different place. The menu bar is now Home, Insert, Page Layout, Formulas, Data, Review, View and in some cases Developer. The ribbon is a new Excel 2007 user interface. The ribbon consists of different commands that are organized by group. Each item on the menu bar has its own ribbon of commands.
Quick Access Toolbar
There is a Quick Access Toolbar that can be used to create a customized toolbar with different Excel commands and regularly used drawing tools. See the Strongly Suggested note under Tip 3 (Arrow Cursor - Select Objects).
1. Removing Grid Lines
Click View on the menu bar at the top of the screen. Inside of the second group, which is called Show/Hide, is the Gridlines function. Uncheck the box next to Gridlines to turn off the gridlines for that worksheet.
2. Making a Text Box
Click Insert from the menu bar. Inside the second group, which is called Illustrations, is a Shapes icon with a drop down. Click here for the expanded drop down for dozens of different shapes divided by section. The fourth section is Basic Shapes. The first shape in this section is the Text box. Click on the text box and release the mouse. Move the cursor, which now looks like a vertical line with a bar toward the bottom, to the spreadsheet. Click and hold the left mouse button to drag a box to the desired size. See Tip 6 for sizing text boxes.
3. Arrow Cursor - Select Objects
The arrow cursor for selecting objects is located by clicking on Home from the menu bar. The last group on the ribbon is called Editing. Inside of Editing is a Find & Select icon with a drop down. Click on Select Objects - the arrow - to select it. Strongly Suggested - Right click on Select Objects - the arrow - and then click Add to Quick Access Toolbar. The Quick Access Toolbar is the only toolbar that you are allowed to customize in Excel 2007. Placing the most frequently used drawing tools on the Quick Access Toolbar makes Excel 2007 much easier to use for drawing.
4. Drawing Tools
In Excel 2007 there is a ribbon to access the shapes for the drawing tools. To access the ribbon you first select (click on) an object, such as a text box, on the worksheet. Notice when you select an object a new item, Format, is immediately added to the end of the menu bar. Just above Format is a Drawing Tools tab. When you click on either Format or Drawing Tools the ribbon in Excel 2007 changes to a drawing tools ribbon. Remember you must have an object selected on the worksheet to access either Format or the Drawing Tools tab.
5. Box Border - Blue/Clear Dots
Clicking anywhere on a text box highlights that box with dots in each corner and a square on each of the four sides. Clicking directly on the thin border between the dots will turn the dots blue. Blue dots are for selecting the entire box. Clear dots show that the inside of the box has a cursor so that text can be added or edited. Click in the middle of the box or directly on the thin border to move between blue and clear dots.
6. Sizing Boxes
The dots in the corners and on the sides of the boxes can be dragged with the mouse to make the box smaller or larger. The boxes can also be sized from the Drawing Toolbar (Tip #4). The fifth group is Size. Vertical and horizontal dimensions can be set to exact dimensions. Several boxes can be sized at the same time by selecting multiple boxes. If the text box resizes itself to fit the text, right click on the text box, and choose Format Shape from the dropdown menu. Then select Text Box from the categories on the left, and uncheck the box labeled “Resize shape to fit text.” With 10 point font a box size of 0.7 by 1.0 works well. For 12 point font a larger box of 0.8 by 1.2 may be required.
7. Moving Boxes
To move a box click on directly on the thin border, hold down the left mouse button and drag the box to the desired location. The box can also be moved by clicking directly on the border, so that it has blue dots and using the arrows on the keyboard to move the box up, down, left or right.
8. Selecting Multiple Boxes
Hold down the shift key and left click on the each box you wish to add to the selection. Multiple boxes can be selected. This multiple selection using the shift key works for selecting any object including lines.
9. Copying Boxes
Copying a box requires the box to be selected with blue dots. See Tip #5 above. Here are four basic ways to copy a box:
9-1. Select box, make blue dots. Click on Home, the first group is Clipboard, and inside of that are the Copy and Paste functions.
9-2. Select box, make blue dots. Click Ctrl C, then Ctrl V to copy and past.
9-3. Right click directly on the box border and select Copy from the drop down menu.
This last one is the best tip (Great Tip)
9-4. Left click directly on the border, hold the mouse down and drag the box to the desired location. Before releasing the left mouse button hold down the Ctrl button on the keyboard. This is known as the Ctrl-Drag copy method. It is typically the fastest way to copy many boxes onto a worksheet.
10. Aligning Text inside a Box
The third group inside of Home on the menu bar is Alignment. There are top, middle, bottom and left, center, right alignment buttons inside that group.
11. Changing, Adding, or Removing Borders
Select an object using blue or clear dots (Tip 5). Click on Format or Drawing Tools (Tip 4). The second group on the Drawing Tools ribbon is Shape Styles. Inside of Shape Styles select the Shape Outline drop down to change, add or remove (No Outline) the border.
12. Adding Color to the Boxes
From the Drawing Tools ribbon select the Shape Fills drop down. This is in the same location as Removing Borders (Tip 11).
13. Using Connectors (Great Tip)
The connectors in Excel 2007 connect by clicking and holding the left mouse button at the beginning connection point and then moving the cursor to the ending connection point then releasing the mouse. The connector end is red when it is connected to a box and blue when it is not connected. The blue end can be dragged to an object. Note that in Excel 2007 the connectors will connect to different locations on, in and around a box so be careful to drop the connector directly on the red connecting square on the side of a box. Here are two different ways to access the connector arrow:
13-1. The connectors are located in Drawing Tools (Tip 4) in the first group, Insert Shapes. Click on the Elbow Arrow Connector (3rd column, 2nd row) to select one connector. This Elbow Arrow Connector from this menu is the only one that can be locked down for adding multiple connectors. Right click on that Elbow Arrow Connector and select Lock Drawing Mode. You must remember to left click on the connector again to unlock it.
13-2. The connectors can also be found by clicking Insert on the menu bar. In the Illustrations group select the Shapes drop down. In the Lines section the Elbow Arrow Connector is the 5th choice. Strongly Suggested – The Elbow Arrow Connector can be added to the Quick Access Toolbar by right clicking anywhere on the Quick Access Toolbar. Click on Customize Quick Access Toolbar to open a new window. From the vertical menu on the left side of the window click on Customize. Click the drop down at the top of the window labeled Choose Commands From and select All Commands. Use the scroll bar to move down the alphabetical list to the Elbow Arrow Connector. Click on that connector then click the Add button in between the two columns. Click OK to exit the window. The entire objects gallery can also be added to the Quick Access Toolbar. Click Insert from the menu bar. Inside the second group, which is called Illustrations, is a Shapes icon with a drop down. Click here for the expanded drop down with dozens of different shapes divided by section. Right click on anyone of those objects and select the Add Gallery to Quick Access Toolbar. This will add a small Shapes icon to the Quick Access Toolbar.
14. Aligning Boxes
Boxes can be aligned by moving them using the mouse or the arrows on the keyboard as explained in Tip 7. Boxes can also be aligned by selecting two or more boxes using the shift key (Tip 8) and clicking on the Drawing Toolbar (Tip 4) to access the fourth group named Arrange. The Align button is a drop down for aligning left, center, right, top, middle, bottom, etc. Align uses the extreme edge of the selected boxes for the chosen alignment. Note in Excel 2007 only use the shift key (Tip 8) to select and align the boxes. Using the arrow cursor (Tip 3) to select boxes and connectors will move the connectors from the boxes.
15. Moving Boxes in a Straight Line (Great Tip)
When moving a box hold down the shift key to move the box only at 90-degree angles: left, right, up or down. The shift button also works when copying something in only a straight line such as with the Ctrl-Drag copy method in Tip 9-4. This is the Shift-Ctrl-Drag method.
16. Zoom-in, Zoom-out
Zooming in and out can be done by selecting View on the menu bar and picking from the zoom options in the third group named Zoom. Zoom can also be done if you have a wheel on your mouse by holding the Ctrl key down as the wheel is moved forward or backward. This is an easy way to utilize the zoom. The wheel on the mouse can be swapped to be the zoom all the time by right clicking anywhere on the Quick Access Toolbar. Select Customize Quick Access Toolbar. Click Advanced from the vertical menu on the left side of the window that opens. The first section inside of Advanced is called Editing Options. The eighth choice down is Zoom on roll with IntelliMouse. Check this box and hit OK at the bottom of the window.