Excel Tips
As an actuarial intern, you will likely be spending a lot of time working with data in Excel. To help you work more efficiently and accurately, it’s important to know some of the most useful Excel hotkeys and functions. In this post, we’ll explore some of the most valuable Excel hotkeys and functions for actuarial interns, including shortcuts for navigating and selecting data, as well as formulas for performing common calculations and analyses.
Keys that need to be held down will be enclosed with braces {} whereas key-presses will be represented with brackets []. [Arrow] refers to any combination of the following [↑], [↓], [→], or [←]
Excel is not a database.
Hotkeys
Create Pivot Table: {ALT} + [N] + [V] + [T]
[T]may not be required depending on Excel version.
Insert Filters: {CTRL} + {Shift} + [L]
Rename Tab: {ALT} + [O] + [H] + [R]
Freeze Top Row: {ALT} + [W] + [F] + [R]
Hide Current Row: {CTRL} + [9]
Hide Current Column: {CTRL} + [0]
Paste-Special: {CTRL} + {ALT} + [V] + [Underlined letter(s) in below dialog] (or {ALT} + [E] + [S])
Paste as Transposed Values
[ALT]+[E]+[S]+[E]+[V].
Formulas - Recommended
These formulas can take a lot of memory and can be dependent on workbook structure; however, they are important to understand due to their common use and flexibility.
| Function |
|---|
| Sumifs() |
| Countifs() |
| Counta() |
| Sum(SumIfs())1 |
| Conditional SumProduct() |
Formulas - Discouraged
These formulas can take a lot of memory and can be dependent on workbook structure; however, they are important to understand due to their common use and flexibility.
Volatile Functions
Every time Excel performs a calculation in the worksheet, these functions will also recalculate regardless of whether or not their input or results would be impacted.
| Function | Alternative |
|---|---|
| Indirect() | |
| Offset() | |
| Today() | Named range or VBA |
| Now() | Named range or VBA |
Current date/time values are often used to display dates on reports; however, it’s both cleaner and more efficient to calculate these values in a header/footer when the report is printed.
Non-volatile functions
Despite being non-volatile, these functions tend to have superior alternatives that are either more flexible or performant.
| Function | Alternative |
|---|---|
| Vlookup() | Index()/Match() |
| Hlookup() | Index()/Match() |
| SumIf() | SumIfs() |
| CountIf() | CountIfs() |
Navigation
Keyboard
Jump to the last populated value in a row/column {CTRL}+ [Arrow]
Additionally hold shift to select a range {CTRL} + {SHIFT} + [Arrow]
Scroll through tabs {CTRL} + [PgUp] or {CTRL} + [PgDn]
Mouse
Jump Active Sheet 
AutoFit Contents
You can also select-all with
{CTRL}+[A]
Open cell for editing [F2]
Cycle through absolute references [F4] when editting a cell reference 
This can be used to sum a list of values that satisfy the criteria (which prevents multiple sumifs from being necessary). Example will be added at a later date. ↩