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)
- Toolbars - Standard and Formatting Toolbars in one row - to display
them in two rows, click on the "Toolbar Options" icon at the end of
the toolbar and select two rows.
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.
Inserting rows or columns - To insert multiple rows, select the number of rows you want to insert and then do Insert>Row (same for multiple columns)
Moving and Docking a Toolbar -- Click in title area of toolbar or in blank are to move it. To dock it, just drag it to top, bottom, left side, or right side of the screen and release mouse
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 Edit>Copy on menu and then move to destination, and select Edit>Paste. (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. (5) Use Cut, Copy, and Paste icons on toolbar. (6) 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.
Charts - You can select the whole chart, and then individual items of it, to do formatting
Moving an embedded chart - 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).
If function: =IF(test,cell or value, truevalue, falsevalue). If the test is true, the cell will display the truevalue where truevalue is a cell, value, or expression; if the test if false, the cell will display the falsevalue where falsevalue is a cell, value, or expression. Can use a nested if statement with up to 7 levels of nesting - e.g. (test,value if true, if (test, value if true, value if false))
Comparison operators - <> for not equal to
Displaying and Printing the Formulas Version of the Worksheet - Ctrl + ` (left quotation mark, at upper left corner of keyboard) - When printing, may want to use Landscape and Fit to Option -- Also good to print Gridlines and Row and Column Headings to better interpret formulas - do this is Page Setup, Sheet
Important Concept: In a spreadsheet formula, you should use cell references to refer to values that might change rather than putting the current value into a formula. 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 Edit>Paste Special>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).
Replacing a function with its current value - Click the cell, then F2, then F9, and hit Enter. Round() function as example where one might wish to do this.
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) Tools, Auditing, Trace Precedents and (3) Tools, Auditing, Trace Dependents
-- in either case, if you repeat the command, it shows the second level dependencies
or precedents
To create unattached text for chart - Select the chart and then type the text into the formula bar and hit enter. This will place the text into the chart where it can be moved and resized as necessary.
Changing scale on y axis - Double click any value on y axis>Scale . Can select minimum, maximum, major and minor intervals.
3D charts - some chart types and subtypes are three-dimensional. If you select one of the pies in a pie chart, then the 3D View option under Chart on the menu will not be greyed out. You can then select the elevation and rotation.
File>Page Setup. Page
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 choosing Format>Cells>Alignment and checking the "merge cells" box (this doesn't center them)
Clearing a cell or range of cells - Select cells, then (1) hit delete key. (2) Edit>Clear>Contents (3) Right click and choose Clear Contents, or (4) when pointer changes to cross hairs, go back over cell until they are in shade and then release mouse button. All of the above clear the contents but do not eliminate the formatting on those cells. Do not press spacebar to clear because it inserts a space. To clear formatting as well, choose Edit>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 (right next
to background and text color)
(4) Rotating text in cells - Select cells, then Format>Cells>Alignment
and choose degrees from horizontal.
(5) Numbers - increase or decrease decimal on toolbar or Format, Cells, then
Number, and then number of decimals
(6) Numbers - % icon or Format>Cells>Percentage. Comma icon or Format>Cells>Number
and then check box labelled "Use 1000 Separator (,)"
(7) Currency - adds dollar sign, thousands comma, and 2 decimal places
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.
(4) Select column(s), Format>Column>Width
Changing the Heights of Rows - (1) Drag in row border, (2) Right click row heading, (3) Select cells, Format>Row>Height
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 then choosing Format>Cells>Alignment and then checking the wrap text box.
Autocorrect - Just like in Word - to alter the behavior, select Tools>AutoCorrect Options>AutoCorrect tab.
Autoformat - Select cells and then choose Format>AutoFormat and select the choice you want. Options gives you a choice as to which aspects of the predefined format you want.
Rotating text in a cell - select cells, then Format>Cells>Alignment
Spellcheck, Page Setup, Header and Footer - like in Word
Hide specified rows - Select Rows, then Format>Row>Hide. For columns, select Columns, then Format>Column>Hide. To unhide rows, select the surrounding rows, then Format>Row>Unhide. Similar procedure for columns.
Print only certain items - Select cells, then choose Print, Selection -- Alternatively, you can Select Cells, then choose File>Print Area>Set Print Area and then File>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 Window>Freeze Panes.
Splitting the window - To split into sections, select the cell value where you want the 4 parts to initially intersect and then choose Window>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 Window>Remove Split.
File>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.
Conditional formatting - Formatting applied if and only if cells meets condition - Select cells, then Format>Conditional Formatting and then enter condition and select formatting. The condition can contain a reference to a cell -- the easiest way to enter this cell is to click on it after clicking on the area at the right end of the text box.
Format Painter - Select cells, click on Format Painter, then select cells (to) -- for single cell, Edit>Copy, Edit>Paste Special>Format. Once one selects the cells, the format is painted and the Format Painter is turned off; for repeated painting, double click the Format Painter button and it remains turned on till you single click the Format Painter button.
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 the statistic you want (Average, Count, Max, Min, Sum, Average). If you want to put one of these functions in the spreadsheet, use the drop down arrow to the right of the AutoSum icon.
Get External Data from a Web Source Using a Web Query - Data, Get External Data, Run Saved Query -- use a reference from another sheet to extract the values (active link) you want
Autofill of series - select series, then use fill handle at lower right to drag - right clicking to drag provides additional options
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.
Drawing - View>Toolbars>click on Drawing to display the Drawing Toolbar. Click on arrow to draw an arrow or the Text Box button to draw a text box. When you have finished drawing the box, the insertion point should appear within the box indicating that you can type in the box. The AutoShapes button on the Drawing toolbar has additional shapes that are grouped by category
Saving as Web Page - File>Save As Web Page. This will create files that can be viewed in a web browser. If one clicks the "Add interactivity" checkbox, this allows the user to change data in the web page and have other numbers (which depend on this number through at least some formulas or functions) change. Although interesting, I have never seen a web page which actually used this feature, probably because it is more difficult to format the page than typically would be the case.
Revised June 20, 2007, comments to William Pegram, wpegram@nvcc.edu