Top 10 Excel Tips and Shortcuts

By Adrian Emerson, Association Accounting Specialist

Microsoft Excel is a very versatile program that is not just for crunching numbers. You can use it for multiple tasks, like making lists, drafting a report, and drawing charts and graphs. Excel can be the association staff member’s best friend; however, there are some time saving features of Excel that almost everyone forgets.

Here are my top 10 tips and shortcuts to help make using Excel 2007 and newer more efficient and easier to use.

1. Personalize your Quick Access Toolbar! (you know that little bar at the very top right corner of your screen)

You can add preloaded and custom buttons to this string, like Quick Print or a custom sort feature.

2. Time and Date Stamps.
   1. To populate a static time or date stamp of the current date or time:
      1. For the date use – “Ctrl” + semi-colon
      2. For the time use – “Ctrl” + “Shift” + colon
   2. To populate an automatically updating time or date stamp:
      1. For the date use the formula: “=today()”
      2. For the time use the formula: “=now()”

3. Basic Rules of Lists and Reports.
   1. Some of the features in Excel will not work if the any of the following is in your data group; here are some basic rules to follow:
      1. Put the sheet title in as a header, and not in the spreadsheet
      2. Always include appropriate row and column headings
      3. Do not leave entire rows blank
      4. Do not leave entire columns blank
      5. Make sure to use the same data type in an entire column

4. Navigating Cells In a Data Range.
   1. Pressing “Enter” moves up and down the cells in a column
   2. Pressing “Tab” moves left and right in a row
   3. If you select a range of cells, pressing “Enter” and “Tab” will only move between the selected cells
   4. To quickly select a data range, click the first cell hold “Shift” and click the end of the range. This will select all cells in between.

5. Paste Link.

This feature copies and links the data from another sheet, and will update the next time you open the file.

6. Custom Sorting.
   1. The Multiple Sorting feature can sort data by multiple specified criteria at the same time. So, if you need a report to be sorted by letter in one column and by zip code in another this feature makes it really easy.
   2. Custom sorting listings can be set up for easier sorting, like by month, day of the week, or site location.
      1. To create a new custom list click the “Add” Button.
      2. Then in the “List Entries” box type your new list.
      3. Then Click “OK”

7. Advanced Filter.

Allows you to set up more complicated queries and place a copy in a new area or sheet.

8. Conditional Formatting.

This feature allows you to add selected formatting to specific data, like automatically changing the font color to red for any cell in the range that is below 100 or highlighting a cell if it is within a certain given range.

9. Subtotals.

This feature will automatically place subtotals and grand totals in a range of data per your specified groupings. NOTE: Make sure to sort your data before using this feature.

10. Formulas.

NOTE: If you add new data at the beginning or at the end of a data range it will not automatically recalculate the formula.

A. VLOOKUP – “=VLookup(LookupValue,LookupTable,ColumnToReturn)”

This formula is probably one of the most widely used of the formulas second to AUTOSUM. The VLookup formula allows you to look up data from a range of data. You can use this formula to create a whole new table of data using data from other sheets and workbooks.

B. SUMIF – “=SUMIF(range, criterion, sumrange)

This formula will sum the data in a range that fit the selected criteria, like if you want to know the sum of only the sales for one location or one employee. However, this formula will only allow you to pick one criteria

C. SUMIFS – “=SUMIFS(sumrange, criteriarange1, criteria1, criteriarange2, criteria2, etc.)

This formula provides the same function as its sibling above, but will allow you to pick multiple criteria, like if you want to know the sum of sales for one employee at only one location or if you want to know the sum of only two employee’s sales at all locations.

What are your favorite Excel tips? Share in the comments below!

Want to know more about association management? Contact us info@imiae.com to find out more about what IMI Association Executives can do for your organization.