Excel 2007 & 2010 Tips Video Library
Microsoft Excel – the best Root Cause Analysis software…you didn’t know you had!
Most people think of Excel software as only an application for creating spreadsheets, but it's an excellent tool for capturing each element of a complete root cause analysis. By changing the way details are documented, a facilitator can improve the entire investigation process. Learn to take advantage of Excel's capabilities to change the way you investigate and prevent problems within your company.
Quick Access Toolbar
The Quick Access Toolbar in Microsoft Excel 2007/2010 gives you the opportunity to customize your version of Excel to best meet your needs. The Quick Access Toolbar is found to the right of the Office Button in the upper left-hand corner. To add any Excel function to the Quick Access Toolbar, right click and select "Add to Quick Access Toolbar". You can also add functions from a menu by clicking on the arrow to the right of the Quick Access Toolbar and selecting "More Commands". Then select "Customize" on the left-hand menu and pick from the drop-down menu of command menus. To add a command to the Quick Access Toolbar, highlight the command on the list on the left and select "Add".
Once you've added commands to the Quick Access Toolbar, you can order them however you'd like. To reorder your Quick Access Toolbar, click on the arrow to the right of the Quick Access Toolbar and selecting "More Commands". Then select "Customize" on the left-hand menu. Select any command on the right-hand side, and use the up and down arrows to move the tip to the left (up) or right (down).
You can access any command on the Quick Access Toolbar with an "ALT" key shortcut. To view these shortcuts, press the "ALT" key on your keyboard. White boxes containing numbers will appear over each command on the Quick Access Toolbar. Each command is ordered in number from left to right. Pressing "ALT" + "1" will open the command in the leftmost position on the Quick Access Toolbar (normally the "save" command). If there are more than 9 commands on your Quick Access Toolbar, the right-most commands will require "ALT" plus 2 numerical keys (such as "ALT" + "0" + "9" for the command in the tenth position).
Text Boxes
To insert a text box in Excel 2007 or 2010, select the "Insert" menu ribbon. Select "Text Box" from the Text portion of the menu ribbon. Your cursor will now look like a sword. Left click where you'd like the upper left-hand corner of the text box to be, and drag to the lower right-hand corner. To add the command to the quick access toolbar, right click over the "Text Box" command and select "Add to Quick Access Toolbar".
To change the font within a text box, select all the text, either by highlighting inside the text box or clicking on the border of the text box so that the dots turn blue and the line surrounding the text box is solid. Within the "Home" menu ribbon, in the "Font" section, choose the font from the drop-down menu in the upper left and the font size from the drop-down menu to the right of the Font menu. You can also access these options by right-clicking on the text or the text box border.
To align the text within a text box, select all the text, either by highlighting inside the text box or clicking on the border of the text box so that the dots turn blue and the line surrounding the text box is solid. Within the "Home" menu ribbon, in the "Alignment" section are options for vertical and horizontal alignment. The default text box in Excel 2007 & 2010 will be top, left aligned. We recommend middle, center align for clarity.
To change the size of a text box, click on the border of the text box. The Drawing Tools: Format menu will appear on the right of the menu ribbon. Select the Drawing Tools: Format menu. On the far right is the "Size" box. Set the height and width of the text box as desired. We recommend using a 0.7" x 1.0" text box.
To change the internal margin of a text box, right click on the border of the text box. From the drop-down menu, select "Format Shape". Then, on the left side menu, select "Text Box". Under "Internal Margin" you can set the left, right, top and bottom margins as desired. If you set each margin to 0.01", a text box using Arial 10 point font that is 0.7" by 1.0" will allow four lines of text.
The default border color for a text box in Microsoft Excel 2007 & 2010 is light gray. To change the border color, click on the border of the text box. The Drawing Tools: Format menu will appear on the right of the menu ribbon. Select the Drawing Tools: Format menu. Directly under the "Format" menu tab, select the drop-down menu for "Shape Outline". Select the desired color for the border. We recommend using a solid black border.
Once you have your text box set exactly the way you want it, you can save your desired settings as the default text box. This will change the text box font, font size, border, and internal margins. To change the default text box, right click on the text box with the desired settings. Select "Set as Default Text Box". The next time you insert a text box, it will have the new default formatting.
To change the internal color of a text box, click on the border of the box. On the "Home" menu, above "Font" click on the drop-down menu next to the paint can. You can preview the appearance of the box on the screen as you move your mouse over the color menu. Select the color you would like by clicking on the color sample.
To change the color of font within a text box, click on the border of the box. On the "Home" menu, above "Font" click on the drop-down menu next to the "A" underlined in color. You can preview the appearance of your text on the screen as you move your mouse over the color menu. Select the color you would like by clicking on the color sample.
Selecting Objects
The "Select Objects" or arrow cursor allows you to highlight and select drawing objects. The arrow cursor is found on the "Home" menu, above "Editing" on the drop-down "Find & Select" menu. Click on "Select Objects" to use the arrow cursor. Click again to go back to the cell-selector cursor. (You may want to add the arrow cursor to the Quick Access Toolbar by right clicking on the icon and selecting "Add to Quick Access Toolbar".)
To select individual objects, hold down the "Shift" key and left click on the objects you wish to select. (This allows, for example, to select only text boxes and not the associated connectors.)
Drawing Tools
The drawing toolbar in Excel 2007 & 2010 appears on an as-needed basis. When you select a drawing object, such as a text box, a new menu ribbon (Drawing Tools: Format) will appear. The drawing tools can be found on this menu ribbon OR by right-clicking on the border of a drawing object.
Using Connectors
Insert an elbow arrow connector by clicking on the "Insert" menu and selecting the drop-down "Shapes" menu above "Illustrations". Click on the elbow arrow connector icon - the 5th icon to the right under "Lines".
Right click on the elbow arrow connector and select "Lock Drawing Mode" to insert multiple connectors. Every time you left-click, a connector will be inserted until you press "Esc" or click on the connector icon for a second time.
To change the color of a connector, right click on the connector and select "Format shape". From the left side menu, select "Line Color" and select the desired color from the drop-down menu next to the paint can.
To change the color of a connector, right click on the connector and select "Format shape". From the left side menu, select "Line Style" and select the desired arrow style from the drop-down menu next to "End Type" under "Arrow Settings".
To change the color of a connector, right click on the connector and select "Format shape". From the left side menu, select "Line Style" and select the desired arrow style from the drop-down menu next to "End Type" under "Arrow Settings".
You can set the default connector to your desired settings, but you'll need to start with an elbow connector. Change the color and the arrow type to your desired settings as shown above. Once your connector is the way you want it, right click and select "Set as default line". As you add more elbow connectors (NOT elbow arrow connectors), they will follow the new default you have set.
An elbow connector will always contain a jog (bend), even if the boxes it connects are aligned. To remove the jog, change the connector from an elbow connector to a straight connector by right clicking on the connector and selecting "Connector Types: Straight Connector".
Align and Distribute
Align a vertical column of text boxes by first selecting the boxes you want to align using the shift key. (Hold the "Shift" key and left click on each text box you want to select.) Then, go to the "Drawing Tools: Format" menu and select the drop-down "Align" menu above "Arrange". "Align Left" will align the left edges of each box to the left-most box; "Align Right" will align the right edges of each box to the right-most box; and "Align Center" will center all the boxes on the center box. (I generally use "Align Center".)
Align a horizontal column of text boxes by first selecting the boxes you want to align. Then, go to the "Drawing Tools: Format" menu and select the drop-down "Align" menu above "Arrange". "Align Top" will align the top edges of each box to the top-most box; "Align Bottom" will align the bottom edges of each box to the bottom-most box; and "Align Middle" will center all the boxes on the center box. (I generally use "Align Middle".)
Evenly space a row or column of text boxes by first selecting the boxes you want to align. Then, go to the "Drawing Tools: Format" menu and select the drop-down "Align" menu above "Arrange". "Distribute Horizontally" will evenly space a horizontal row of text boxes and "Distribute Vertically" will evenly space a vertical column of text boxes.
Date and Time
Holding the "Ctrl" key and typing ";" in any cell will enter the current date (according to your computer's calendar).
Holding the "Ctrl" and "Shift" keys and typing ";" in any cell will enter the current time (according to your computer's clock).
Format a cell to display the date by right-clicking on the cell and selecting "Format Cells". On the "Number" tab, under "Category" select "Date". On the right hand side under "Type", choose the format you'd like your cell to have, then click "OK".
Format a cell to display the date by right-clicking on the cell and selecting "Format Cells". On the "Number" tab, under "Category" select "Time". On the right hand side under "Type", choose the format you'd like your cell to have, then click "OK".