Transcript Slide 1
15 Excel Tip in 15 Minutes Use Fill-handle to insert or delete rows • Hold shift key while dragging Fill Handle • If drag down you will insert rows • If drag up you will delete rows Ctrl/Shift/~,1,2,3,4,5,6 for quick formatting • • • • • • • ~ 1 2 3 4 5 6 General 2 decimals Time format Date format Currency Percent Scientific Using Fills • A number format which includes * will fill the cell with the next character • General*. will fill the cell with periods. Enter 12 and you’ll see 12………….. as wide as the column is • @*. will fill text: Enter Bob and you’ll see Bob………. • General*.;@*. will do numbers and text • Use $**#,##0 to get asterisk-fill for currency Borders from keyboard • Ctrl/Shift/7 creates outline border • Ctrl/Shift/- removes border Using REPT for visual effects • You can easily use a variation on =REPT(“/\”,500) for a nice effect /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Leaving cursor in cell after entering • If the cursor normally moves out of the cell when you enter a value and you temporarily want to keep it in the same cell, simply press ctrl/enter instead of enter • Assumes you have only 1 cell selected, else all cells will be filled Change dates like 20040323 to one Excel can “understand” • • • • • Select the date(s) Data/Text-To-Columns Click “Next” twice In step 3 of wizard Select Date, YMD Click Finish Tear-off Palettes • Font, Fill, Borders, Draw… • They can all “float” over your work area Double-clicking tools • Double-click a drawing tool (rectangle, for example) to draw several without revisiting the tool • Press esc to stop that feature or click the tool again Filling Holes • Select cells, use Edit/Goto Special, select Blanks • Type “=”, press the up-arrow, Ctrl Enter State FL Region North South WA North South NY North South Amount $ 300 $ 104 $ 159 $ 749 $ 700 $ 348 $ 508 $ 935 $ 582 $ 377 $ 936 $ 831 $ 243 $ 344 $ 97 $ 120 $ 577 $ 955 State FL FL FL FL FL FL FL FL FL WA WA WA WA WA NY NY NY NY Region North North North North South South South South South North North South South South North North South South Amount $ 300 $ 104 $ 159 $ 749 $ 700 $ 348 $ 508 $ 935 $ 582 $ 377 $ 936 $ 831 $ 243 $ 344 $ 97 $ 120 $ 577 $ 955 AutoSum Tool features • Select extra blank column &/or row to single click AutoSum Tool and get totals across and down • Multi-select (ctrl key) so that single click of sum tool adds many ranges at once • Don’t forget this tool can do other functions besides “Add” Click the AutoSum button from the keyboard • Alt/= same as clicking the AutoSum tool • Alt/= twice (quickly) will also enter the result Ctrl/” copies value from above cell; Ctrl/’ copies cell exactly • If you have a formula in a cell, selecting the cell below, then pressing ctrl/” will copy the value. • Example: Cell A3 has =INDEX(B1:B50,C1) and its value is 7500 • Selecting cell A4 and pressing ctrl/” will put 7500 into A4. Ctrl/[ more powerful than its equivalent GoTo Precedents • If a cell has =SUM(B2:E4), then using ctrl/[ is same as selecting B2:E4 • If a cell has a link to a cell in a closed workbook (entire path would be there), then ctrl/[ would open the file, switch to the appropriate sheet, and select the cell! Wish there were a “No to All” when closing many files? • There is! • Hold the Shift key when clicking the No button