Excel Cheat Sheet Side 1 Sean Fox, Academic Computing-Carleton College Oct 30, 2000 File Formats: There are 2 different Excel file formats and 5 different versions on campus (5.0/95, and 97/98/2000 (grouped to indicate common file formats)). Each newer version can import and Save As into the older versions formats. Older versions can't necessarily deal with the newer versions files. This cheat sheet will deal with version 97/98/2000. Note that Excel 2000 has some non-critical features (none covered here) which are ignored if the subsequent file is opened in earlier versions. Also, menu options in Excel 2000 are often hidden—click the double-arrow at the bottom of the menu to reveal additional options. Help: The help is context sensitive. If you're in a pop-up window the help button within that window gives you help specific to that task. You can search for help by name, but it helps if you know what Excel calls a particular feature. It may be useful to look first for a more general topic (or one that's somewhat tangential to your actual goal) that will tell you the name of the feature you're interested in. Once you know the name you can go directly to help on that topic. Data Entry and Formatting: The pointer changes to reflect what will happen when you click in a given location. Here are some examples: This pointer…pops up when you point to: Menu, Tool Bar, Scroll Bar Text in a cell. This is the insertion bar A cell when you are in selection mode The fill handle at the corner of a selected fill or range. A column or row heading boundary which can then be resized A split box on the scroll bar • Numerical formatting (scientific notation, number of decimal places, etc.) and Text formatting are under Format..Cell, including aWrap Text option under the Alignment tab. Changes apply to all selected cells or just the selected portion of a single cell. This can be useful to get a special character (like greek letters from the symbol font), or a subscript mixed plain text. • Paste Special gives an option to paste the formatting (but not the data) from a cell you've copied as well as to Transpose rows and columns • Double-clicking column and row boundaries at the edge of the sheet resizes the column or row to fit the data. • Double-clicking a cell puts you in cell edit mode. Hit return (to accept changes) or escape (to reject changes) to get back out to select mode. Insert..Row/Column puts a new Row/Column above/to the left of the current selected cell. • Window..Freeze Panes "freezes" everything above and to the left of the current selected cell so that they stay in place when you scroll. This is very useful for labels that apply to data spaning several screens. • Center Across Selection in the Alignment tab of the Cell format window allows you to center a label which is nice for printed output. • It's probably best to do fancy formatting just before you are ready to print. Fills: Selecting multiple cells and then dragging the fill handle (at the corner of a cell) allows you to drag and fill cells with data that continue that series. More options are available under Edit..Fill. Multiple Sheets: • Command clicking (control-click in Windows) on sheet tabs (found below the main grid) allows you to select multiple sheets. • New data entered in this mode will appear on all selected sheets. • Edit..Fill allows you to push existing selected data onto all all selected sheets. Cell References: Cells can be referenced individualy ( A4) or as blocks (A4:C12). When formulas with cell references are copied and pasted, the cell references are adjusted to reflect their new location. (e.g. the reference will still refer to the cell "one over and two up") This relative referencing can be disabled: make cell references in formulas absolute by adding a $ in front of the part of the reference to be fixed. e.g. =($A4+1) will always refer to column A, but the row it refers to will change depending on where it is pasted. Likewise =(A$4+1) when copied will always refer to row 4 but its column reference will be relative. When a cell reference is selected F4/Command-T (Windows/Mac) toggles through all the possible absolute/relative reference permutations. Paste Special allows you to paste the values (as opposed to the formulas) you've copied. Formulas: • They are of the format =(A3+B24). • They are easiest to add using the Formula wizard button on the tool bar (with the fx label). • Double-click a cell to edit its formula. • Formulas are auto-calculated on the fly! Excel Cheat Sheet Side 2 Sean Fox, Academic Computing-Carleton College Oct 30, 2000 Analysis Tools: • Anovas, Regressions, Histograms & more are under Tools..Data Analysis. If this option isn't on the menu you'll need to go to Tools..Add-Ins and check the Analysis ToolPack check box. • The regression option includes a check box to create a corresponding chart. • The histogram option allows you to specify the "bins" into which the data is segregated by creating a list of numbers representing the top value of each bin. Charts: • Use the Chart Wizard (the bar chart icon on the toolbar ). • Most chart types only allow data points with an associated label and value (blue fish, 42). If your data points each have two associated variables (e.g. each point has a time and temp associated with it, or a height and width) need to use the X- Y scatter chart type. Excel is unable to do true 3- d plots where each data point has 3 variables associated with it. • You can change the properties of the elements of your chart by selecting the element in question and double-clicking. Clicking on the data points (which Excel calls a data series) is especially useful in this regard. • Selecting an axis and double-clicking gets you to the Format Axis window. The Scale tab in this window allows you to specify the min, max, and tick values for the axis as well as whether to use log scaling. • Title, Axes, and Legends can be added to a chart after it's initial created by selecting the Chart and using the relevant options in the Insert menu. Charts-Fitting Curves: • You can fit a curve (linear, log or polynomial) to a data series in an X-Y chart by selecting the data series and then choosing Chart...Add TrendLine. The options tab has an option to print the formula. • Another way to do the same thing is the regression option under Data Analysis mentioned above. Charts-Error Bars: You can create and control error bars on most graph types. Double-click the data series and then the X or Y - Error Bars tab. Then select a standard % or value for the error or indicate columns with distinct error values for each point. Charts--Putting them into a Word document • Make sure your chart is correct in all aspects (fonts, labels, etc) within Excel before moving it to Word. • Use the Copy command to copy the chart and then Paste it directly into the Word document. • This procedure will paste the Chart as a picture. It is also possible to insert it as a live Excel spreadsheet. I don't recommend this second approach--it will give you nightmares. • If you need to make changes to the Chart it is almost always better to make the changes within Excel and redo the Copy, Paste operation. Printing: • Go to Page Setup and select the Sheet tab. Select the Print Area field and then select the area on the spreadsheet you want to print. • Select landscape or portrait from the Page tab. • You can scale the sheet down or force it to fit on a given height&width page using the Scaling options under this tab. • Use print preview and drag the triangles at the edges to resize rows and columns so that things fit. • You can force a page break (they appear as dotted lines in the main spreadsheet) by selecting the top left cell of the new page and then using Insert..Page Break. (Repeating removes the page break). • You can add headers and footers to your printed page using Header/Footer tab in Page Setup. The Header and Footer menus on this window give you options for common choices including none. • If you have a Column heading or Row label that you want to appear on each printout page use the Print Titles option in the Sheet tab of Page Setup. • The Print section under Sheet in Page Setup allows you to hide the gridlines in a printout, as well as force the printing to be black & white (otherwise things with a hue come out as shades of grey). • Multiple charts may be surrounded by a single boarder. This is a function of the cell formating, and not the charts themselves. Importing Data: • You can import plain text files with columns of data using the normal File...Open procedure. This will start the data import wizard. • If you choose the delimiters option (each element in a row is separated by a space, comma, or tab). Note the useful Treat Multiple Delimiters as One option in step two. • If you choose fixed width the 2nd step allows you to create and drag lines to indicate the boundary between columns. Be sure to scroll through enough of your data to be sure that things are ending up in the correct columns. If you Open and Save a plain text file in Word it will then be in Word format—not plain text format. Use Save..As and choose Text only as the format from within Word to convert back to plain text for importation into Excel.