Microsoft Excel Quirks and Shortcuts

For the most part, Excel is an easy and user-friendly tool with many uses. There are a few things about Excel one must know. Remember, most of these things can be changed with a highlight and click of a button.

1. Excel aligns text on the left side of cells, but it aligns dates on the right side of cells.
2. Excel aligns numbers on the right side of cells.
3. Formatting stays with the cell. It does not change when data entries change.

Other numbers and how to enter them

1. To enter fractions, leave a space between the whole number and the fraction. For example, 1 1/8.
2. To enter a fraction only, enter a zero first. For example, 0 1/4. For example, if 1/4 were entered without the zero, Excel will interpret the number as a date, January 4. Change the format for the cell as well, which will tell Excel what that you want it to read a date.
3. If (100) is used to indicate a negative number by parentheses, Excel will display the number as -100.

Here are two timesavers to enter data in Excel:

AutoFill Enter the months of the year, the days of the week, multiples of 2 or 3, or other data in a series. Type one or more entries, and then extend the series.
AutoComplete If the first few letters typed in a cell match an entry already made in that column, Excel will fill in the remaining characters. Just press ENTER when they are added. This works for text or for text with numbers. It does not work for numbers only, for dates, or for times.

Here are some convenient approaches while entering data in Excel:

Freeze/Unfreeze Panes allows the user to lock rows and/or columns in one area so that specific rows or columns remain visible when scrolling in the worksheet. For example, freeze panes to keep row and column labels to scroll or add data into a worksheet. There are several ways to use this mechanism. The user can lock only rows, only columns, or lock both rows and columns. To lock rows, select the row below where the row would freeze. To lock columns, select the column to the right of where the column would freeze. To lock both rows and columns, click the cell below and to the right of where the rows and columns would freeze. Once the rows and/or columns have been chosen to lock, then click on the Window menu and click Freeze Panes. To unlock rows/columns, click on the Window menu and click Unfreeze Panes.

Split Panes is similar to freeze panes; however this mechanism allows the user to view two areas of a worksheet at the same time and to scroll in both areas of the worksheet. Scrolling in the locked rows/columns cannot occur while using freeze panes. To lock rows, select the row below where the split would appear. To lock columns, select the column to the right of where the split would appear. To lock both rows and columns, click the cell below and to the right of where the split would appear. Once the rows and/or columns have been chosen to lock, then click on the Window menu, click Split. To remove the split, click Remove Split on the Window menu.

Zoom is a tool that you can use to view your data or your worksheet larger than it would appear on your normal screen. Zooming in or out does not affect printing. Sheets are printed at 100 percent unless you change the scaling on the Page tab of the Page Setup dialog box (File menu). To use zoom, make sure that "Zoom" is one of the buttons showing on your tool bar. To do this, click on View, then click on Toolbars. Several Toolbar names will show in this list. Choose the "Standard" toolbar from the list. Once the toolbar appears at the top of your worksheet, click on the down arrow at the right end of the tool bar. Clicking on this will give the option to show buttons on one row or to add or remove buttons. Click on add or remove buttons, then choose standard. The list of standard toolbar buttons will show and those that are active will have checkmarks beside them. If zoom does not have a checkmark, click on it to make it active. Zoom will then appear on your toolbar with the rest of your buttons. To make your view larger or smaller, click the down arrow in the Zoom box , click the size you want, or enter a number from 10 to 400.

Excel Shortcuts - Keys to press What Does it do?

F2: Edit the active cell and put the insertion point at the end of the line
ESC: Cancel an entry in the cell or formula bar
CTRL+SHIFT+ENTER: Enter a formula as an array formula
CTRL+A: Display the Formula Palette after you type a function name in a formula
CTRL+SHIFT+A: Insert the argument names and parentheses for a function, after you type a function name in a formula.
Enter: Complete a cell entry and move down in the selection
ALT+ENTER: Start a new line in the same cell
CTRL+ENTER: Fill the selected cell range with the current entry
SHIFT+ENTER: Complete a cell entry and move up in the selection
TAB: Complete a cell entry and move to the right in the selection (move forward)
SHIFT+TAB: Complete a cell entry and move to the left in the selection (move backward)
BACKSPACE: Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit the cell contents. Delete the character to the left of the insertion point, or delete the selection
DELETE: Delete the character to the right of the insertion point, or delete the selection
CTRL+DELETE: Delete text to the end of the line
Arrow Keys: Move one character up, down, left, or right
HOME: Move to the beginning of the line
SHIFT+F2: Edit a cell comment
CTRL + (Plus) on 10-key pad: Insert row or column
SHIFT F7: Thesaurus

Glossary

Active cell - Outlined in black and referenced in the cell reference. This is the cell in which data will be entered next.
AutoComplete - Excel will match an entry you already typed after you type the first couple of letters.
AutoFill - Enter the months of the year, the days of the week, multiples of 2 or 3, or other data in a series. You type one or more entries and then extend the series.
Cell - Space where one column and one row meet.
Cell reference - Alphabet heading and number heading combined. Also known as the cell address. (eg., A3)
Chart sub-type - Used to create charts that are sub types of the main category. Ex) Column chart is the main category of chart. Stacked column chart is a sub type of this chart.
Chart Wizard - The icon in Excel that helps to create a chart. It uses step by step instructions to create the chart.
Column chart - A chart that uses columns to report data.
Column gap - The gap between the columns in a column chart.
Column width - The width of a column. This may be the width of the column in a workbook or the width of a column in a column chart.
Columns - Goes from top to bottom (vertical) on the worksheet and are identified with alphabetical names across the top of the worksheet. (eg., A, B, C).
Data labels - Labels that identify specifically what the value is of the column, line or point.
Formatting - The process of changing the appearance of a cell, range, or object.
Freeze Panes - The process of keeping certain rows and columns in view no matter what cell you are working in on the worksheet.
Function - Formulas.
Gridlines - The lines in the chart that identify the value of the column, line, or point. These are usually in the background.
Legend - The box on a chart that shows what each symbol means.
Line chart - A chart using lines to identify data points. This type of chart may be used to show changes and trends over time.
Merge Cells - To combine two or more cells into one larger cell holding a single value.
Name Box - Displays the active cell address.
Pane - A portion of the document window bounded by and separated from other portions by vertical or horizontal bars.
Pie chart - Chart used to view parts of the whole. **Remember that you can only compare one set of data with a pie chart**
Rows - Goes from left to right (horizontal) on the worksheet and are identified with numerical names running down the left side of the worksheet. (e.g., 1, 2, 3).
Scatter charts - An XY Scatter chart compares two sets of numbers at once, one on the horizontal X axis, one on the vertical Y axis.
Sheet tab - Tab that can be renamed to identify each worksheet. These are found at the bottom left of the workbook window.
Split Panes - The process of splitting the worksheet so that certain areas are viewable and you are able to scroll in both areas.
Sort - A way to organize data in ascending or descending order, by variables that you determine such as age, date, alphabetically, year of entry, exit dates, and many more options.
Stacked column chart - A sub type of the column chart that stacks data on top of other data.
Workbook - The file that opens when you open Excel. The workbook contains 3 worksheets.
Worksheets - Pages of each workbook.
Wrap Text - Wraps the text to fit in the cell.
X-Axis - The horizontal axis.
Y-axis - The vertical axis.
Zoom in/out - To make text larger or smaller depending upon your needs.

References
Microsoft Online Excel Training. Retrieved from website on April 6, 2006. http://office.microsoft.com/en-us/training/CR061831141033.aspx
Microsoft Corporation (1999). Professor Teaches Excel 2000 CD Rom. Individual Software, Inc., Pleasanton, CA.
Walkenbach, J. (1999). Excel 2000 for Windows for Dummies Quick Reference. NY: Wiley Publishing Co.
www.dictionary.com April 19, 2006