What You See When You Open Excel:
- sheet tab
- columns - denoted by letters
- rows - denoted by numbers
- one cell is always the active cell, heavy border surrounds active cell;
name box also shows which cell is active; row and column heading for active
cell change color
- cell is the intersection of a row and a column; a cell is referred
to by column, then row, e.g. C5, which is the opposite of the way things are
normally done in math or computer science (row, then column)
Setting the active cell - (1) Click on the cell, or (2) Typing the cell reference in the name box and hitting Enter, or (3) use arrow keys. Hitting Enter moves the active cell down one row.
Information is entered into the active cell only.
Entering information into a cell - when finished, either hit enter, an arrow key, or click on or click on another cell. If amount typed exceeds the available space in the cell, the overflow is displayed in the cell immediately to the right as long as it contains no data.
To edit a cell - double click in cell puts insertion point in middle of cell whereas hitting F2 key puts insertion point at the end of the cell; in either case, you can then move the insertion point to the desired point by clicking or using arrow keys. Backspace will delete to the left of the insertion point whereas the delete key will delete to the right of the insertion point.
Syntax for formulas - begin with an equal sign, e.g. =b2+b3+b4. One can type the cell reference or click on the cell. Excel follows usual mathematical precedence rules, exponentiation before multiplication and division, which in turn have greater precedence than addition or subtraction. So 2+3*4 is equal to 14, not 20. Use parentheses to override these rules, or make clear the order in which operations are to be performed. e.g. =(2+3)*4 or =2+(3*4).
Several methods for copying from cell to another: (1) Click and drag fill handle at lower right corner from cell to be copied to destination cells. (2) Select origin cell, select Copy icon on menu and then move to destination, and select Paste icon. (3) Same as (2) except that you can use Ctrl-C to copy or Ctrl-V to paste. (4) Select origin cell, right click click and choose Copy, select destination cell, and then right-click, and choose Paste. In any of these above methods, you can simply hit Enter for the paste step.
You can copy a range of cells, just like you copy a single cell. When you do the paste step, it is not necessary for you to select a group of cells with the same dimensions as the source; simply select the desired location of the upper left hand corner of the range of the cells.
Relative, absolute, and mixed addressing - A relative reference will be adjusted when copied; to prevent this, use a $ sign in front of the portion of the cell reference you don't want copied.
Autosum icon - Excel makes a guess as to which cells you want to sum. You can change this guess by dragging the right bottom corner.
Summing multiple columns at same time - Drag mouse through area and then click summation sign
Select cells in spreadsheet that contain values and labels for chart, but do not select totals -- and then click Chart Wizard button and follow directions.
Moving a chart within a worksheet - Select it, and then drag it, can resize it using the handles on the sides like in other Windows applications
Syntax for functions - = functionname(argument1, argument 2, ...). To enter a function in a cell, one can click the fx in the toolbar and then select the appropriate function. The syntax for the function will be shown and there will be a link to what is often extremely useful additional information about the function. The parameters or arguments of the function can be entered in the dialog box, and one can designate cells either by typing the cell reference or by clicking the area with the red arrow at the right and then selecting the desired cells with a mouse. If one wants to designate a range of cells, the cells are separated by a period or a colon (Excel will change the period into a colon).
Comparison operators - <> for not equal to
Important Concept: In a spreadsheet formula or function, you should use cell references to refer to values that might change rather than putting the current value into the formula or function. If the value won't change (e.g. the number of hours in a day), then some may prefer to put the value into the formula or and some may prefer to do a reference to the cell.
When you copy one or more cells to another location, the values in the cells copied from are of course copied to the new location. If one subsequently changes the values of the original cells, this will not affect the values of the cells in the new location. If one wants such changes to be reflected in the new location, instead of copying the cells originally, one links the new cell to the old cell. To do this, with the cursor in the new location, one types an = followed by the reference of the cell to which one wants to link. This is an example of a very simple formula. Alternatively, one can copy the cell, then Paste>Paste Link. Another method is the copy and paste the cell, and then cursor over the paste options button that appears to the right of the paste location and select "Link Cells". References in formulas to cells in other sheets -- the simplest way to do this is when one is entering the formula to click on the other sheet and then click on the desired cells(s).
Debugging tools - (1) Range finder - double click the cell containing the formula
-- it will highlight the cells referenced in the formula, you can drag the colored
borders to other cells to change the reference and then hit Enter to make the
change
(2) Formulas>Trace Precedents and (3) Formulas>Trace Dependents
-- in either case, if you repeat the command, it shows the second level dependencies
or precedents
Charting
Selecting cells - One can apply formatting to more than one cell at a time by dragging the mouse through multiple cells. To select non-adjacent cells or ranges of cells, hold down control key. Click on row or column heading to select entire row or column. To select the entire worksheet, hit Ctrl and the "a" key at the same time or click to the left of the A column heading and above the row 1 heading.
Alignment - default is that numbers are right-aligned in the cell whereas text is left-aligned; to enter a number as text, precede it with an apostrophe
Formatting just like in Word - Bold, Italics, Underline, Left, Right, Center Align, Font Size. Increase/decrease indent changes indentation by approximately one character width.
Merger and center icon - Centers contents of a single cell across multiple columns; you can also do this by selecting the cells and then launching the Format Cells dialog box and then choosing Alignment and checking the "merge cells" box (this right-aligns them, which you can then change by clicking on one of the alignment icons)
Clearing a cell or range of cells - Select cells, then (1) hit delete key. (2) Home>Clear icon (at far right) >Clear Contents (3) Right click and choose Clear Contents. Any of the above choices clears the contents but does not eliminate the formatting on those cells. Do not press spacebar to clear because it inserts a space. To clear contents and formatting, Home>Clear icon (at far right)>Clear All.
Formatting the Worksheet: (1) Changing Background -- select cells, click on
Fill Color and then select color
(2) Font Color - Select cells, then click on Font Color right next to Fill Color,
or go to Format Menu
(3) Border for cells - Select cells, then click on border style (immediately to the left of the fill color icon)
Changing the Widths of Columns:
(1) Best fit - double click the right boundary of the column heading - this
will make column just wide enough to accomodate the contents of the column at
that time
(2) Dragging right border of column - watch tooltip to see exactly how wide
it is
(3) Right click column heading, and choose Column Width, and then enter a number.
The default number is the current width.
If you select several rows or columns, you can change the width/height for all of them at one time by selecting any of the boundary lines within the selected range and then changing the width/height.
An entry will display on a single line in the specified row. If the entry is too large for the existing column width, it will display in the cell to the right if this cell is empty. otherwise the rest of the cell entry will not display. To force to a new line within a row, one can use Alt-Enter. To allow Excel to determine when to break to new lines, one can select Wrap Text by selecting the cell(s), and launching the Format Cells dialog box, and then choosing Alignment and then checking the wrap text box.
Autocorrect - Just like in Word - to alter the behavior, select Tools>AutoCorrect Options>AutoCorrect tab.
Rotating text in a cell - select cells, then launch the dialog box by clicking at the lower right of the Alignment Grouping, and specifying orientation on the right.
Print only certain items - Select cells, then choose Print, Selection -- Alternatively, you can Select Cells, then choose Pager Layout>Print Area>Set Print Area and then Office Button>Print. Also, hidden rows or columns don't print.
Column Widths Too Narrow for Numbers - ### will be displayed - so make column wider or change formatting
Changing Sheet Names - doubleclick tab, or right click; Can recorder sheets by dragging and dropping
Freezing worksheet rows or columns (panes) - Used in a large sheet where you want certain rows or columns (typically headers) always to be displayed - it will freezes at upper left corner of the cell you select before you choose View>Freeze Panes. To remove this, select View>Unfreeze Panes.
Splitting the window - To split into sections, select the cell value where you want the 4 parts to initially intersect and then choose View>Split. If only 2 parts desired (which is more likely), select cell in first row or column. This is used in a large sheet where you want to be working with several different parts. To remove the split, select View>Split (it functions as a toggle switch)..
Page Layout>Page Setup. Can select orientation (landscape vs. portrait) and within the scaling section of the dialog box, there is a Fit to option which means Excel will automatically scale the the output size to fit on the specified number of pages. The Margins tab of the Page Setup box allows one to center things vertically or horizontally on the page. The Header/Footer tab allows one to specify a custom header or footer with automatic page numbering and date/time options like in Word, along with path and file, file name, and sheet name options. The Sheet tab allows one to specify the printing of row and column headings and gridlines, which are especially useful when printing the formulas version of a worksheet, as well as rows to repeat at top or columns to repeat at left for multi-page printing.
Autocalculate - select cells containing numbers, and then you will see a calculation in the right hand side of the status bar at the bottom. You can right-click in this area of the status bar to choose which statistics (Average, Count, Max, Min, Sum) you want displayed. If you want to put one of these functions in the spreadsheet, use the drop down arrow to the right of the AutoSum icon.
Autofill of series - select series, then use fill handle at lower right to drag - right clicking to drag provides additional options
Find and Replace - Home>Find and Select>Find and Replace. Just like in Word, but you can also search for format or replace with format by clicking the Options button. You can specify the format to be searched for, or choose it from a cell.
Revised: January 12, 2010. Comments to William Pegram, bill@billpegram.com