Excel Tips and Tricks

MS Office 2010: Excel Tips and Tricks

Excel Tips and Tricks MS Office 2010

Dr. Sherri E. Ritter

Excel Handout 1. Navigation Move to end of row by selecting first cell in row then hold Ctrl + Arrow To select a range click the first cell and hold down the Shift> Click last cell (Shift Ctrl+Down Arrow) To select non-adjacent cells Hold Control Key Split Window

Click and drag splitter or click Freeze Pane in View Tab> Windows Group Freeze Pane Click on the top row (B2)> View> Freeze Pane Conditional Formatting Select data> Home Tab> Styles> Conditional Formatting>Highlight Cells Rule Auto Sum Press Alt = (This works for a single colum/rown or multiple columns/rows) View Calculation in the Status Bar (This works for single or multiple columns/rows)

Right clicking on the status bar will allow you to change the fields that appear in status.

2. Absolute Reference Insert $ by cell names ($B$3). In this sample, B3 is a constant. Pressing F4 once selecting the constant will add the $ to the formula.

Created February 2013 by Dr. Sherri E. Ritter

Page 1

Create a Chart Highlight the cells to include in the chart and click F11 3. Sort a List Click on the column you want to sort> Home Tab> Sort & Filter> Sort A to Z (Or do Custom Sort) Changes case =proper (cell) See cell length =Len(cell) Max length of cells in range of cells =Max(cell:cell) Filter a list Data Tab> Filter

4. Break Text into Multiple Columns Select cell and click Data Tab> Text to Columns Choose Delimited> Next> Space Wrap text with Precision To wrap text at a specific location press Alt + Enter at the point you want to wrap text.

Add Cell Comments (non-printing) Right click the cell you want to add a comment and select Insert Comment Created February 2013 by Dr. Sherri E. Ritter

Page 2

5. Remove Duplicates Select the data fields and click Data Tab> Remove duplicates> Select the columns> OK

Calculate Workdays between Dates Select Formulas> Date & Time> Networkdays

Created February 2013 by Dr. Sherri E. Ritter

Page 3

Future Date Projections =DATE(Year)+B33,(Month)+B34,(Day)+B35)

6. Transpose Copy section> Move to new location> Paste> Paste Special> Transpose> OK

Copy Worksheet Click Sheet Name and click and hold Ctrl while dragging to new location.

Created February 2013 by Dr. Sherri E. Ritter

Page 4

Password Protect Files File> Save As> Tools> General Options>

Created February 2013 by Dr. Sherri E. Ritter

Page 5