Adv-excel

Advanced Excel Deb’s 30 Tips 1. GOTO Key Use the F5 key or Ctrl-G– GOTO Key to jump around the spreadsheet

2. Create Custom Lists: 1. 2. 3. 4. 5. 6.

Click on Tools, Select Options Custom Lists tab If Manual Entries Click on Add Type list separated by comma, space or if already in a spreadsheet Place cursor in area Import list from cells Click on red arrow Select the text on the spreadsheet, press Enter

3. Auto calculate: Use your mouse to view calculation results Auto Calculate lets you see SUM, MIN, MAX, COUNT, COUNTA and AVERAGE results. 1. Select the cells containing the data. 2. The result is displayed in Excel's Status bar. 3. To switch the type of calculation, right-click on the Status bar ([control]-click on the Mac)

4. Printing Tips: 1. 2. 3. 4.

Print Preview your document before printing it Click on Print Preview Button on standard toolbar Click on Page Break Preview Drag page break to desired location

5. To Fit on One Page 1. Click File, Page Setup 2. Click the Page tab 3. Scaling – Select Fit to, then select number of pages wide by number of pages tall

6. Set Print Area Print area defines the range or ranges of a worksheet that will print. If you select multiple ranges, each range will print on a separate page. Click on File, Choose Print Area, Set Print Area

7. Set Repeating Print Titles 1. 2. 3. 4. 5.

Click File, then Page Setup Click the sheet tab Click in rows to repeat at top box Click the Red Collapse dialog button on right side of box Select rows containing titles to repeat when printing

8. Scroll lock can be useful 1. Press Scroll Lock (this causes a little "Scroll Lock" light to go on). 2. To scroll horizontally, one column at a time, press the left arrow key or the right arrow key. To scroll vertically, one row at a time, press the up arrow key or the down arrow key.

1of 5

Advanced Excel Deb’s 30 Tips 9. Placing a Single entry in multiple cells 1. Select the range or nonadjacent cells 2. Type the entry 3. Ctrl-enter to put the data in all the cells

10. Copy a range of cells 1. Select cell(s) to copy then point 2. Hold down the ctrl key as you drag on the + sign

11. Insert another line in a cell - alt-enter 12. Sort List Using Menu 1. Select any cell in list. 2. Click Data on menu, then click Sort 3. Select Primary and secondary fields to sort by, OK

13. AutoFilter 1. 2. 3. 4. 5.

(Easy way to filter certain criteria)

Click Data on menu Select Filter, Autofilter Click on the down arrow next the fields that you want to query on Select custom for more options Click on Data, Filter, Autofilter to turn off

14. Advanced Filtering (displays only rows in a list that meet predefined criteria) 1. 2. 3. 4. 5. 6. 7. 8.

Create a criteria range by inserting blank rows above list you want to filter. Copy column labels in the list to blank rows above the list. Enter criteria in the blank rows below the labels Do not include blank rows in the criteria range Select the database area including headers Select Data on menu, Filter, Advanced Filter List range is the part of the spreadsheet being filtered Criteria range is the area at the top with the column labels

15. Data Validation (used to make sure that only correct data is entered) 1. 2. 3. 4.

Select cells to restrict Click Data menu, then click validate Click settings tab Select whole number, decimal, date, or time

16. Subtotals 1. 2. 3. 4. 5.

Select any cell in database area Click Data menu, Subtotals Change in header (vendor) sum, total Replace current subtotals Summary below data

2of 5

Advanced Excel Deb’s 30 Tips 17. Protect Worksheet Data: 1. 2. 3. 4. 5.

(Locked cells is the default)

Select entire worksheet Format, Cells, Protection tab Click Locked check box to deselect it Select parts to protect and do Format, Cells, Protection, Lock Tools, Protection, Protect Sheet

18. Looking for inexact matches to search criteria Find or Replace features for near-matches to criteria. Use wildcard characters in a search string. like the DOS rules. The ? wildcard character is used in place of any single character and the * wildcard character is used for multiple characters.

19. Freeze Panes To freeze your column labels: 1. Select the row BELOW the row containing your column labels. 2. Choose Window + Freeze Panes. To freeze your row labels: 1. Select the column to the RIGHT of the column(s) containing your row labels. 2. Choose Window + Freeze Panes. To freeze both row AND column labels: 1. Select the cell in the row just below your column labels and in the column just to the right of your row labels. 2. Choose Window + Freeze Panes.

20. Show Excel's complete Standard and Formatting toolbars (2000) Configure Excel so that both toolbars appear in their own rows. 1. Select View | Toolbars | 2. Customize > Options tab 3. Clear the Standard And Formatting Toolbars Share One Row check box and click OK.

21. Parse Excel data without worksheet formulas (97/2000) 1. 2. 3. 4. 5. 6.

Enter first and last name data in column A of a worksheet. Select the data and choose Data | Text To Columns from the menu bar. Click Next on the Convert Text To Columns Wizard. Clear the check boxes in the Delimiters panel and select the Space check box. (If splitting data like a date, enter a slash (/) in the Other text box Click Next In the Destination text box, enter the worksheet cell where you want the split data placed, then click the Finish button.

22. Moving worksheets between Excel workbooks (97/2000) 1. 2. 3. 4.

Open the workbooks with your data. Choose Window | Arrange from the menu bar, Select the Horizontal option button, and click OK. Click and drag a worksheet tab from one workbook to another.

3of 5

Advanced Excel Deb’s 30 Tips 23. Simplify hyperlink creating in Excel (97/2000) 1. 2. 3. 4. 5. 6.

Open both Excel & IE Select the worksheet cell in Excel where you want to create the hyperlink. Press [Ctrl]K to display the Insert Hyperlink dialog box. Press [Alt][Tab] or use your mouse to select the browser window. Browse to the page you want to link to and then switch back to Excel. The URL is automatically inserted in the dialog box.

24. Re-order worksheet columns 1. Select the columns you want to move. 2. Move your mouse pointer to the edge of the selection until it changes to a pointer arrow. 3. Press [Shift] and then drag the column to the new position. Release the mouse button before releasing the [Shift] key the column is moved without overwriting any data. The same basic technique also works with rows.

25. Conditional Formatting 1. 2. 3. 4.

Select the cells you want to format. Choose Format + Conditional Formatting. In the dialog box that appears, specify your conditions. Click the Format button, specify the formatting you want when the value meets your conditions (set the color in the Patterns tab), and click OK. 5. If you want to set additional conditions, click Add and repeat Steps 2 through 5. You can set up to three conditional formats for a cell. 6. Click OK to close the Conditional Formatting dialog box.

26. Custom Format for data 1. 2. 3. 4. 5.

Try setting up a format like a social security format - 123-45-6789 Select a cell (or column or row of cells) and choose Format, Cells. Click the Number tab. Under Category, locate Custom and select it. Click one of the existing formats to select it. In the Type entry box, delete your selection and type ###-##-#### 6. Click OK. Now, navigate to one of the cells to which you applied the format and enter 123456789 7. Excel should enter 123-45-6789

27. Displaying & Hiding the Grid 1. 2. 3. 4.

Right-click any toolbar and choose Customize from the shortcut menu. In the Customize dialog box, click the Commands tab. In the Categories list, select Forms. In the Commands list, find the Toggle Grid button and drag it into position on one of your toolbars. 5. In the Customize dialog box, click Close. 6. Whenever you want to hide the worksheet grid, click the Toggle grid button.

4of 5

Advanced Excel Deb’s 30 Tips 28. Auto Fill Tip 1. Use it to enter a column of numbers that follow a pattern. For example suppose you need do a backwards fill starting with 100 down to 24 every other number. 2. Go to any cell and type in 100. Now press Enter and then select the same cell. 3. Choose Edit, Fill, Series. 4. In the Step Value entry box, type -2 (minus 2). 5. In the stop value box, enter 24. 6. Select the Series in Columns radio button and click ok. Another way is the drag and fill. Enter 100 in cell A1 and then enter 98 in cell A2. Now select both cells and then use the mouse to drag down until all the cells are filled.

29. Name Cells Select the range to name. 3. Click in the name box and type descriptive name, Enter

30. Close all workbooks without exiting Excel To quickly close multiple files in Excel without quitting the application, hold down the [Shift] key and open the File menu. You'll find that the usual Close command has been replaced with Close All. 31. Chart Tips 1. When selecting data make sure the cell above the 1st column in the first row is empty. 2. When selecting the chart make sure to hold down the “Press & hold view sample button” to see what your chart will look like. 3. When using the wizard for chart source data, try looking at the chart by selecting series in columns and then series in rows to choose the best one. 4. If you need to add text to the chart, turn on the drawing tool and use the text box. 5. To make changes to a chart right mouse click on the area of the chart you need to change. 32. Create a Look_up Table 1. Name the List a. Insert > Name > Define b. Type in a name c. Select the range 2. Create the Drop down list a. Data > Validation > allow: List b. Source =Name or select the range of cells

5of 5