Working With Formulas In Excel

Excel spreadsheets are often used to crunch numbers. To do this, one must understand the basics of formulas. Whether calculating sums or averages, these shortcuts will help users be more efficient in formula creation and use.

WINDOWS

=	Start a formula
Alt + =	Insert the AutoSum formula
Shift + F3	Display the Insert Function dialog box
Ctrl + a	Display Formula Window after typing formula name
Ctrl + Shift + a	Insert Arguments in formula after typing formula name
Shift + F3	Insert a function into a formula
Ctrl + Shift + Enter	Enter a formula as an array formula
F4	After typing cell reference (e.g. =E3) makes reference absolute (=$E$4)
F9	Calculate all worksheets in all open workbooks
Shift + F9	Calculate the active worksheet
Ctrl + Alt + F9	Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation
Ctrl + Alt + Shift + F9	Recheck dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated
Ctrl + Shift + u	Toggle expand or collapse formula bar
Ctrl + `	Toggle Show formula in cell instead of values

Names

Ctrl + F3	Define a name or dialog
Ctrl + Shift + F3	Create names from row and column labels
F3	Paste a defined name into a formula

MS Excel Shortcuts

Excel is useful in the organization of data, including dates, times, percentages and other numbers. In this section, learn shortcuts to better format the cells and their respective data.

WINDOWS

Ctrl + 1	Format cells dialog
Ctrl + b (or ctrl + 2)	Apply / remove bold
Ctrl + i (or ctrl + 3)	Apply / remove italic
Ctrl + u (or ctrl + 4)	Apply / remove underline
Ctrl + 5	Apply / remove strikethrough
Ctrl + Shift + f	Display the Format Cells with Fonts Tab active. Press tab 3x to get to font-size
Alt + ' (apostrophe)	Display the Style dialog box

Number Formats

Ctrl + Shift + $	Apply the Currency format with two decimal places
Ctrl + Shift + ~	Apply the General number format
Ctrl + Shift + %	Apply the Percentage format with no decimal places
Ctrl + Shift + #	Apply the Date format with the day, month, and year
Ctrl + Shift + @	Apply the Time format with the hour and minute, and indicate am or pm
Ctrl + Shift + !	Apply the Number format with two decimal places, thousands separator, and minus sign (-) for negative values
Ctrl + Shift + ^	Apply the Scientific number format with two decimal places
F4	Repeat last formatting action: Apply previously applied Cell Formatting to a different Cell

Apply Borders to Cells

Ctrl + Shift + &	Apply outline border from cell or selection
Ctrl + Shift + _ (underscore)	Remove outline borders from cell or selection
Ctrl + 1, then Ctrl + Arrow Right/Arrow Left	Access border menu in 'Format Cell' dialog. Once border was selected, it will show up directly on the next Ctrl + 1
Alt + t	In Cell Format in 'Border' Dialog Window, set top border
Alt + b	In Cell Format in 'Border' Dialog Window, set bottom border
Alt + l	In Cell Format in 'Border' Dialog Window, set left border
Alt + r	In Cell Format in 'Border' Dialog Window, set right border
Alt + d	In Cell Format in 'Border' Dialog Window, set diagonal and down border
Alt + u	In Cell Format in 'Border' Dialog Window, set diagonal and up border

Align Cells

Alt + h, ar	Align Right
Alt + h, ac	Align Center
Alt + h, al	Align Left