Excel: Making Research Administration Easier

Excel: Making Research Administration Easier

Symposium for Research Administrators October 8, 2019

Formatting

Accounting Puts currency symbol at the far left of cell. Negative amounts displayed as $(1.00)

Currency Puts currency symbol next to numbers. Negative amounts displayed as -$1.00

AutoFit Rows: place mouse pointer over the lower boundary of row heading until the doubleheaded arrow appears, and then double-click. Columns: place mouse pointer over the right border of the column heading until the double-headed arrow appears, and double-click. Multiple rows / columns: select the rows you want to AutoFit and double-click in between any border. Entire sheet: press Control + A or click Select All, and double-click the border of any row or column border (or both).

Conditional Automatically apply cell formatting to a range of cells based on criteria you set. Simple Formatting formatting conditions to start with are Greater Than, Less Than, Equal To, and Text that

Contains. Helpful for spreadsheets which must contain 0 in order for a formula to function: conditionally formatting the font to match the background (i.e., both white) keeps the 0 in place but allows it to be hidden from view.

Excel Shortcuts

Cell edit F2

By pressing F2, Excel will enter cell edit mode and place the cursor at the end

mode (cell edit mode) of the cell. A handy shortcut for making quick changes within a cell.

Control + left or right arrow

When in cell edit mode, move to the left or right one word at a time.

Control + Shift + left or right arrow

When in cell edit mode, this keystroke allows you to select one word at a time.

Home / End

When in cell edit mode, press Home to go to beginning of text and End to go to end of text.

Data Control + Shift + Selection left, right, up, or

down arrows

When not in cell edit mode, this will select the full range of data at once.

Control + multiple cells

Using Control will allow you to select multiple cells at once.

Control + Space Selects entire column.

Shift + Space

Selects entire row.

Control + A

Select the entire sheet or range of data.

Alt + ;

Selects only the visible cells in the current selection. If you have a hidden row or column, this will allow you to change only the data that you can see; it won't affect anything in your hidden cells.

Excel Shortcuts

Navigating / Viewing

Control + Home

Quickly go to cell A1 - useful for when you are analyzing large datasets.

Control + scroll mouse wheel

Zoom in or out quickly.

Control + up arrow OR End + up arrow

Jumps to the cell at the top of a column / range

Searching Data

Control + F

Control + H

Find ? if you forgot where you put that crucial piece of data, use this shortcut to quickly find it.

Find & Replace ? use to replace data (misspelled words, etc.). You can also use this to quickly replace data within a formula (i.e., in a range of cells, quickly replace $D$6 with $B$7, etc.). If you only want to replace data in certain cells, be sure select only those cells before using this keystroke.

Undo / Redo Control + Z Control + Y

Quickly undo last action; press multiple times to undo multiple actions. (Panic button!)

Redo last action; press multiple times to redo multiple actions. Using in conjunction with Control + Z to toggle between undo / redo is a useful way to quickly see changes within your data.

Quick Formatting Control + B

Control + U

Control + I

BOLD. You can either bold an entire cell / range, or use in cell edit mode (F2) to bold individual words.

UNDERLINE. You can either underline an entire cell / range, or use in cell edit mode (F2) to underline individual words.

ITALICS. You can either italicise an entire cell / range, or use in cell edit mode (F2) to italicise individual words.

Excel Shortcuts

Quick Formatting

(cont.)

Control + Shift + $

Quickly apply the currency format to selected cells; use for multiple cells if range is selected.

Control + Shift + !

Quickly apply the number format to selected cells; use for multiple cells if range is selected.

Control + Shift + ~

Quickly apply the general format to selected cells; use for multiple cells if range is selected.

Alt + Enter

Starts a new line in the same cell. This shortcut is handy for those of us who need to put a lot of data in one cell. It essentially functions as a line break inside of a cell. If you need to write a lot of text inside a worksheet, this function is essential.

Data Entry Control + ;

Enter current date in m/d/yyyy format.

Control + Shift + ; Enter current time in h:mm AM/PM format.

Control + K

Insert hyperlink. First, select where you want the hyperlink to appear. Then press Control + K and the `insert hyperlink' box appears.

= + cell

Use = to return the value from another cell. You can also use this to enter values from cells in another worksheet.

Control + Enter

Enter same data in multiple cells. Select the cells you want to duplicate data, and perform this keystroke to input data in multiple cells incredibly quickly.

Control + D F4

Duplicate cell above.

Repeat the last edit. If you want to repaste something again or repeat a formula insert, this key will save you time!

Excel Shortcuts

Quick Formulas Alt + =

F2 + F4 Control + `

Quickly sum the selected cells. If you have a block of data and you want to add all of the columns and rows, using this command will perform a quick Autosum of the data. Make sure you include a spare column on the right and a spare row on the bottom for the summation data.

Absolute reference to cell. When in cell edit mode (F2), press F4 to add $ to both the letter and row (i.e., $B$4) to the cell reference closest to your cursor. Pressing F4 will continue to toggle cell references to the letter and row (i.e., $B4, B$4).

Displays all formulas in worksheet. Press again to toggle off.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download