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).
To move the quick access toolbar to be shown below the menu ribbon, click on the down arrow to the right of the quick access toolbar and select "Show Below the Ribbon".
Return to index
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.
With the default settings in Excel 2007 & 2010, drawing objects will be resized when the cells behind them are changed. To keep your drawing objects from being affected by the cells behind, select the object, right click, select "Size and Properties". On the "Size and Properties" box, select the "Properties" tab and pick "Don't move or size with cells".
Return to index
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.)
To group multiple objects, select the objects using the
"Shift" key or
arrow selector. Right click, select Group: Group.
Return to index
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.
Return to index
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".
Return to index
Align & 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.
Return to index
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".
Highlight date column. Go to Home: Editing: Fill: Series and select "Weekday" under "Date Unit".
Return to index
Photographs
To insert a photo, click on the "Insert" tab, then "Picture" (above "Illustrations"). Browse for and select the picture file you'd like to insert and click "Insert".
Compressing photos can reduce the file size of an Excel document. To compress one or more photos, select a photo, and then select the "Picture Tools: Format" tab. Above "Adjust" select "Compress Photos". If you want to compress only the selected file, ensure "Apply to selected pictures only" (2007) or "Apply only to this picture" (2010). Click "OK".
To add a label to your photo, insert a text box from the "Insert" tab above "Text". Place the text box on the photo. To remove the fill (so that you see the photo behind the text box), go to Drawing Tools: Format and click the drop-down menu under "Shape Fill" above "Shape Styles". Select "No fill". You can change the font, font size, and font color on the Home menu above "Font" to ensure that your label can be clearly seen above the photograph.
To add a circle to your photo, go to the "Insert" tab and click the drop-down menu for Shapes above "Illustrations". Select the oval, which is the second shape under "Basic Shapes". If you want a circle, hold down the "Shift" key while you drag the mouse to make your shape. Place the circle on the photo. To remove the fill (so that you see the photo behind the circle), go to Drawing Tools: Format and click the drop-down menu under "Shape Fill" above "Shape Styles". Select "No fill". You can change the outline color and width of the circle by using the drop-down "Shape Outline" menu from the "Drawing Tools: Format" directly below "Shape Fill".
Return to index
Worksheets
To insert a new worksheet, right click on the sheet after where you'd like the new sheet to appear. Select Insert: Worksheet and click "OK".
To rename a worksheet, right click on the tab of the worksheet and select "Rename". Type in the new name of the worksheet and press enter.
To change the color of the tab at the bottom of a worksheet, right click on the tab, scroll to "Tab color" and select the color you'd like for your tab.
Zoom in or out using the counter at the lower right-hand corner, or the View:Zoom menu, OR by holding down the "Ctrl" key and scrolling down (to zoom out) or up (to zoom in) with your mouse rollerball.
To change the default settings so that scrolling up with your mouse rollerball will zoom in, and scrolling down will zoom out, click on the down arrow next to the Quick Access Toolbar and select "More Commands". From the left-hand menu, select "Advanced" and then select the box next to "Zoom on roll with IntelliMouse." Click "OK". Now in order to use the mouse to scroll up and down in a worksheet, you will have to hold the "Ctrl" key.
You can move from worksheet to worksheet within a workbook by clicking on the tabs at the lower left. Or, you can move to the next worksheet by clicking "Ctrl" + "Page Down", or to the previous worksheet with "Ctrl" + "Page Up".
To move a worksheet to a new location within an Excel 2007 or 2010 workbook, left click on the title tab of the worksheet and drag the worksheet to the desired location along the bottom of the screen.
To make a copy of a worksheet within an Excel 2007 or 2010 workbook, right click and select "move or copy". Select "Create a copy" and choose the desired location for the new sheet.
Return to index
Working in Cells
To insert a row of cells, right click on the row below the row you want inserted and select "Insert".
To insert a column of cells, right click on the column to the right of the column you want inserted and select "Insert".
Format a cell for currency by right-clicking on the cell and selecting "Format Cells". On the "Number" tab, under "Category" select "Currency". On the right hand side select the number of decimal places and the currency symbol you'd like your cell to have, then click "OK".
To add a new line in a cell, use "Alt" + "Enter".
To move a column or row in Excel 2007 or 2010, right click on the row or column you'd like to move and select "Cut". Then right click on the row below or the column to the right of where you'd like to move the row or column to, and select "Insert cut cells."
To create a numerical sequence, such as a timeline entry for every day, insert the starting value in a cell (in this example, we'll assume it's A1). In the next cell (in this example, A2), enter in the formula bar below the ribbon "=A1+1". Copy that formula and paste in the A column to create a sequence.
To keep zeroes from showing up in cells with a zero value, go to the Office Button and select "Excel Options". (For Excel 2010, go to the File tab and select "Options"). On the left-hand menu, select Advanced. Scroll down to "Display Options for this Workbook" and uncheck "Show a Zero in Cells that have Zero Value".
Return to index
Excel Settings
You can hide the menu ribbon in Excel 2007 or 2010 by double-clicking on the name of the active (open) menu ribbon. It will then show only the tabs for the different menus. You can return to showing the full menu by double clicking on any menu tab.
In order to open a file created in Excel 2007 or 2010 in an earlier version of Excel, you'll need to save the file as a compatible version. In Excel 2007, click on the Office button in the top left hand corner. (In 2010, click File: Save As.) Select "Save As" from the left side menu, then "Excel 97 - 2003 Workbook" .
You can specify the default location in which files will be saved. In Excel 2007, click on the Office button in the top left hand corner. On the bottom of the dialogue box that opens, select Excel options. Choose "Save" from the left side menu. (In 2010, click File: Options: Save.) Enter the location where you'd like files to be saved in the "Default File Location".
Saving a file as a template will allow you to create other files from the template. To save as a template, go to the Office Button (or File tab for Excel 2010) and select "Save as". In the "type" box, select "Excel Template". The template will be placed in the template folder, as shown.
To add properties to a workbook, go to the Office Button (or File tab for Excel 2010) and select "Prepare", then "Properties". You will then be able to add an author, title and additional information to categorize your workbook.
Return to index
Charts & Graphs
Ensure that your data is formatted with column or bar titles in one column (or row), and the values to be graphed are in the next column/row(s). Select the data and headings to be graphed; go to insert: charts: and click the drop down menu for "bar" or "column". Select the style for the chart you desire. For the simplest bar & column graph, choose the 2-D clustered option.
Ensure that your data is formatted with headings in one column (or row), and the values to be graphed are in the next column/row. The values should add up to 100%, though the numbers do not need to be represented as percentages. Select the data and headings to be graphed; go to insert: charts: and click the drop down menu for "pie". Select the style for the chart you desire.
Ensure that your data is formatted with column or bar titles in one column (or row), and the values to be graphed are in the next column/row(s). Select the data and headings to be graphed; go to insert: charts: and click the drop down menu for "line" or "area". Select the style for the chart you desire.
Return to index