APR’s Favourite Excel Shortcuts

At APR, most of our time is spent providing high quality interim resourcing using a wide range of programming languages and actuarial software. Surprisingly, one tool that remains prevalent in our day to day work is the age-old software, Microsoft Excel. Despite its drawbacks, Excel continues to be widely used in the actuarial space. This article offers APR’s most valuable shortcuts to maximise productivity in Excel whilst also discussing their advantages and limitations.

Reasons for Excel’s Prevalence in the Actuarial Space

Before delving into the shortcuts, it’s important to understand Excel’s strengths and the keys to its persistence:

1. Familiarity and Training: Excel is a tool that most actuaries are well trained in, making it easy to pass models between professionals, provided sufficient documentation is in place.
2. Quick Calculations: Excel is very good at performing simple calculations on manageable data sets very quickly.
3. Automation Potential: Excel’s built-in programming language, VBA (Visual Basic for Applications), and macros provide opportunities for automation. While beyond the scope of this article, these features enable actuaries to streamline repetitive tasks.
4. Flexibility and Easy Modification: Excel’s flexibility allows actuaries to make changes to models swiftly, so that they are able to accommodate changing requirements.

Understanding Excel’s Limitations

It’s also critical to acknowledge Excel’s limitations as an actuarial tool. These include:

1. Data Limits: Excel has a maximum of 1,048,576 rows and 16,384 columns – actuaries often encounter scenarios where data may be cut off as well as experiencing performance issues when pushing Excel to its limits.
2. Limited Precision: Excel rounds numbers to 15 digits. This is sufficient for basic calculations, however when calculations with larger numbers are being performed, such as reserve calculations, it could lead to material approximations. This limitation is crucial for insurance companies that rely on accurate calculations.
3. Data Management Challenges: Storing data in multiple spreadsheets can be time consuming and prone to errors.

Therefore it is extremely important that the above points are considered carefully when deciding whether to use Excel for a model.

Overcoming these Limitations

If the decision has been made to use Excel to build a model, actuaries should employ the following tips in order to overcome Excel’s limitations and enhance productivity:

1. Minimise External Spreadsheet Links: Avoid excessive linking of data across multiple spreadsheets. This practice reduces complexity and prevents checkers and testers from having to open multiple spreadsheets simultaneously.
2. Keep it Simple: As spreadsheets grow in size, they become more prone to errors and complexities. Actuaries should aim to keep their models as simple as possible to mitigate risks and improve efficiency.
3. Documentation: Actuarial work relies heavily on documentation, enabling seamless knowledge transfer between actuaries and their teams.

Excel’s capabilities are often widely underutilized, with its shortcuts being often overlooked. Actuaries (and any other professional using Excel on a regular basis) should familiarise themselves with Excel shortcuts so that they can streamline repetitive tasks and free up more time for value-add activities, as well as helping prevent errors. Below is a compilation of APR’s favourite Excel shortcuts (note that these may vary for Apple Mac Users).

APRs Favourite Excel Shortcuts

1. Alt + = (Autosum): This shortcut predicts and calculates the sum of selected cells, saving time by eliminating the need to type the formula each time.
2. Ctrl + ; and Ctrl + Shift + ; (Hardcoded Date or Timestamp): These shortcuts allow for easy insertion of hardcoded dates or timestamps, which is invaluable for actuarial audit trails and tracking amendments. They eliminate the need to manually type the date or timestamp each time.
3. Ctrl + Arrow Keys (Navigation Shortcut): This allows you to quickly navigate to the last non-empty cell in a column or row. When working with large datasets, this can save a lot of time.
4. Ctrl + D (Fill Down): This shortcut quickly fills down the value or formula from the cell above into the selected cells below, saving time when populating repetitive data or formulas in a column.
5. Ctrl + Shift + L (Filter Shortcut): This shortcut activates the filter function, enabling you to quickly sort and filter data within a range.
6. Proper formatting of cells is crucial for presenting data accurately and enhancing the readability in Excel. These shortcuts are a great way to improve efficiency:
   • Ctrl + Shift + ~ (General Format): This shortcut resets the selected cell to the general format and removes any specific formatting already applied.
   • Ctrl + Shift + ! (Currency):This shortcut formats numbers with the thousands comma and two decimal places making currencies easier to interpret at a glance.
   • Ctrl + # (Dates): is a quick shortcut to format the cell to date format.

7. Data Tables: Not quite a keyboard shortcut, but they help skip repetitive work and they’re useful enough that they had to be included! They are a powerful tool in Excel that allow actuaries to perform sensitivity analysis and construct what if scenarios. Starting from a formula that will be repeated multiple times with different inputs, they can be used to explore different scenarios, assess the impact of key variables and make informed decisions based on the results obtained.

Constructing a data table in Excel:

For example: From an initial calculation of finding the present value of £600,000 three years prior with an interest rate of 5% (on the left), you are easily able to construct a data table showing the present value of various amounts at various interest rates without worrying about copying the formula multiple times. This can save a lot of calculation time and reduce the risk of errors.

To do this:

1. Add titles, column and row headings for the data table as needed.
2. Highlight the data range for your data table. The formula you want to replicate needs to be in the top-left corner of this range.
3. Choose ‘What-If Analysis’ – ‘Data Table’.
4. Enter the input cell references for rows and columns. In our example, the row input cell would be the “5%” next to discount rate in the top-left, and the column heading would be the “600,000” in the cell above.

Conclusion

In conclusion, despite its limitations, Excel remains widely used in the actuarial field. To maximise productivity actuaries should invest time in making sure that their spreadsheets are foolproof. The Excel shortcuts listed above will allow more time to do this (and hopefully means less time spent on Excel overall).

Heather Wallace

June 2023