dECE. 3, 2012 Eric Rasmusen, erasmuse@indiana.edu This file is now at http://www.rasmusen.org/a/excel.txt This file is for Microsoft Excel spreadsheet commands I might want to use again. ----------------- CTRL-SHIFT + This inserts a new row or column. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SORTING In Windows 7, a sort by a column will not expand to columns past blank columns. Thus i was getting students grades osrted, but not their names! XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX LOGICAL FORMULAS Below is a spreadsheet formula for when I have a column of numerical student grades that I want Oncourse to turn into a letter course grade. These should be in a column labelled Oncourse-Required-Score [100] . =IF((G2=1 ),10,0)+IF((G2=1.3),61,0)+IF((G2=1.7),64,0)+IF((G2=2 ),68,0)+IF((G2=2.3),71,0)+ IF((G2=2.7),78,0)+IF((G2=3),85,0)+IF((G2=3.3),88,0)+IF((G2=3.7),91,0)+IF((G2=4.0),94,0)+IF((G2=4.3),98,0) SWITCHING ROWS AND COLUMNS Copy the area and then right-click for PASTE SPECIAL and check the TRANSPOSE box. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX DELETING DUPLICATE ROWS (for email) From: http://office.microsoft.com/en-us/excel/HA010346261033.aspx Open the file in Excel. Select all of it. Do DATA then FILTER, then ADVANCED FILTER, then check off UNIQUE RECORDS ONLY. That will show only the unique records. Then COPY what is showing to the clipboard. Go back to DATA then FILTER then ADVANCED FILTER then Do NOT check off UNIQUE RECORDS ONLY. Then the whole file will show. Press the DELETE key to delete all the rows. Then do CTRL-V to paste in the uniquely filtered rows. Then SAVE the file, and all the duplicates will be gone. There isn't a way I know of to filter on just duplicates in one column and deleting all rows that have duplicates in that column even if they are different in other columns. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX DEFINING A VARIABLE To define a variable. First, put the value into a cell-- maybe put 0.9 into D1. Then INSERT then NAME then DEFINE. A box will appear where you will put the name of the variable Discountfactor and the lcoation D1. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX NEGATIVE NUMBERS IN PARENTHESES To get rid of the nasty conversion of negative numbers to parenthetic numbers or vice versa: FORMAT, then STYLE, then NORMAL, then NUMBER, then TEXT this does not allow formulas, and is still bad, but it allows one to make regession tables, at least. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Freezing Panes in Excel. Select the second row, the row *under* the header row with your column titles. Then hit WINDOWS and FREEZE PANES. After you do that, if you scroll down you still see the column titles. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Creating Functions in Excel on TOOLS, Point to MACRO and click VISUAL BASIC EDITOR. On INSERT in the MS VISUAL BASIC window, click MODULE. In the MODULE window, type basic code like this: Function V(mynum) If mynum=4 then V=10 If mynum>4 then V=mynum^2 End function The go to File and exit from the visual basic window. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX There is a PRINT AREA command which is very useful. Select an area, and then press PRINT PREVIEW, and then SETUP to get it as a landscape, and then reduc ehte stize to 70 percent. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Remember the Right-Click button in Excel. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Autoformat is a good command for diagrams. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX How do I make no header or footer the default? XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Figure out where the STARTUP file is for EXCEL so I can put my autoformat files there. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX For formulas, copy them from one cell to another by CTRL and dragging the southeast corner of the box. Remember Pivot tables, for condensing quarterly to become annual data. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX To mark a lower boundary of a spreadsheet for scrolling purposes, go to the spot and hit CTRL-UP. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX CTRL-END goes to the last cell with data. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX To select an area, click once on one end, Hold down SHIFT and click once on the other end. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX To put copies of data into a selected area, type in the data and hit CTRL-ENTER. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX FIGURES Notes on Excel Diagrams February 5, 2006 Here is how in Excel one can change the standard formatting of a Line chart, to take away the boxes around it, get rid of the legend, make the y-axis label read horizontally, and so forth. Chart Type: Line (or Custom Type--Rasmusen) Chart Options. Gridlines. Usually click on the boxes to delete all the horizontal lines, but keep them if you think them useful. Chart Options. Legend: Click on Show Legend to get rid of the ugly box with the legend. Chart Options. Titles. Here put a chart title and labels for the axes, if appropriate. It might be better to add these in MS-Word instead. Left-click the y-axis label and then right-click to Format Axis Title and then pick the Alignment tab to make the label horizontal instead of vertical. Left-click the x-axis. Then right-click it and choose Format Axis. On the Scale tab, choose a good number of tick marks between the labels, and a good number of categories between tick marks. On the Alignment Tab choose 0 degrees, to get the labels horizontal. You may wish to left-click each axis, right-click and choose Format Axis, and on the Patterns tab, choose None for Tick Mark Labels. In that case, you would add the labels in MS-Word, which allows more flexibility. Left-click on the plot and then right-click to get Format Data Series. In the Patterns tab, Check Custom under Marker, and then pick the circle marker, and make it 3 pt in size. Check None under Line if you don't want a line connecting the dots. You may also wish to use the Data Labels tab and Value to put the number value of each point next to that point. Right-click on the interior of the plot to get Format Plot Area. Pick None for Border, and Automatic or None for Area (depending on whether you want a grey fill interior or white). Once the chart is formatted correctly, right-click on the chart, pick Chart Type, check User-Defined, and click on Add. You can then save the formatting style of the chart for future use. Something you can't put in a User-Defined chart is not to have a box border around the entire graph. For that, you need to right-click on that box and choose Format Chart Area and under Border pick None. Only then should you cut and paste the graph into Word, or into Paint or Powerpoint (which you need to do if you want to save the chart as a separate *bmp or *.jpg file). XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX