In this article, I will introduce 5 MS Excel tricks and shortcuts to help you get the most out of data with the least amount of effort. Learn these tricks and shortcuts, and you can significantly reduce the amount of time it takes to analyze your data and extract what you’re looking for. It will take, literally, just a few keyboard strokes.
5 MS Excel Shortcuts that will save you a ton of time!
For insurance professionals, part of the job requires gleaning actionable insights from large quantities of data. Monthly or biweekly, reports are generated containing lots of data – renewal lists, accounts receivables lists, expiry lists, new business reports, loss history reports, to name just a few examples.
Understanding all this data helps you set priorities, allocate resources, unearth trends, manage operations, and provide helpful information to others. It’s clearly important work, yet, so is following up with a prospect, reviewing policy terms, preparing for a client meeting, or binding a renewal. And you need to get it all done. Yesterday.
Click to watch a video with tips
TIP 1: Navigating the data with CTRL + Arrows
When I receive a new Excel file, I like to see how big it is and how many entries I am looking at. To quickly navigate around the document, hold down the CTRL key and then press an Arrow key. When you do this, the last used cell in the data region you’re in will be selected.
For example, if I have a spreadsheet containing a list of 100 renewals and a header row, the information for the first renewal appears starting in cell A2. The last entry, the one for the 100th renewal in the list, will start in cell A101. If my cursor is in cell A2, and I press CTRL + Arrow Down, my cursor will jump down to cell A101.
This is a quick way to navigate the document and quickly see how many entries you are dealing with.
An alternative is to use CTRL+Home to select and view A1 and CTRL+END to get to the last used cell in the worksheet.
TIP 2: Simplify viewing large quantities of data using Freeze Pane: ALT, W, F
Most Excel data files we use in insurance contain a header. The header is a row that provides column names – typically policy number, effective and expiry dates, client name, premium, commission, and policy type. When we navigate the document, it is helpful to continue seeing the header, which is typically on row 1. Excel gives you the option to freeze rows or columns. When you freeze a row or column, the frozen section will continue to be visible while scrolling through the rest of the worksheet. Just press Alt, W, F to access the Freeze functions.
I like this time-saving trick because I don’t have to keep scrolling to the top to remind myself of the information I’m looking at.
When printing out Excel sheets, there is a similar function available where you can have that top row (header) printed on every page. You’ll find this function in Print Titles under the Page Layout tab.
For more on Freeze Pane and Print titles, you can also take the Excel Layout lesson.
Tip 3: Save time entering addition formulas with ALT = to sum
To automatically sum rows or columns, use this shortcut for AutoSum: press ALT and = at the same time. This function is great because when you press ALT and =, Excel will show you the range it intends to add. If it looks ok, then just press ENTER to accept the suggestion.
In the video below, we use this shortcut to sum all the numbers in a column. (Yes, it will also add a row.)
If you want to sum several columns, select the empty cells below all the columns you want to sum, then press ALT= and ENTER. The sum for each column will be automatically inserted below the respective column.
Tip 4: Quickly apply number formats with CTRL1 to Format Cells
Personally, this is the shortcut I use the most. Type CTRL+1 (press CTRL and number 1 at the same time) to open the formatting dialogue box. In insurance, we use many different types of numbers (percentages, dates, with/without currency), so those numbers need to be formatted accordingly in order for the data to make sense. This shortcut makes it easy to access and change the number formatting.
This same shortcut also gives you access to text alignment, font types, borders, fill (as in cell color background), and cell protection. All you have to do is press the two keys, and you immediately have access to many formatting options without ever touching your mouse!
Tip 5: Don’t waste time on repetitive steps, use F4 to repeat the last action
The F4 key on your keyboard lives a double-life!
You may know F4 as the key used to access all 4 types of cell references and set absolute references (insert a link to references in Excel course). That’s when you add the $ sign for your formula reference.
However, F4 does a lot more than that! F4 is also used to repeat the last action. And it’s really simple. Use F4 to repeat actions such as inserting columns/rows, formatting cells, copy and pasting, or even formatting borders. There is no need to press Alt or Ctrl. Just press F4 to repeat the last action, and you can do it over and over again!
For example, if you’re highlighting policies that need attention, highlight the first one, then move your cursor to the next cell you want to highlight and press F4 again. Use F4 over and over to highlight other cells as you navigate through an expiry list.
Watch this video to see the 5 MS Excel shortcuts in action:
We’ve got a host of additional Excel shortcuts and tips in our Excel courses, so be sure to check those out – and don’t miss the free preview lessons!
