15 Excel Formulas, Keyboard Shortcuts & Tricks That‘ll Save You Hours

If you‘re like most marketers and business professionals, you probably spend a significant chunk of your day in Microsoft Excel. And while you‘re comfortable performing basic tasks, you may often find yourself thinking "there has to be a better way."

You‘re right – there is! By learning some key Excel formulas, keyboard shortcuts and tricks, you can dramatically improve your efficiency and productivity.

In this post, we‘ll share 15 of the most useful Excel features that will help take your spreadsheet game to the next level. We‘ll cover:

  • Must-know formulas for manipulating text, numbers and dates
  • Crucial keyboard shortcuts for navigating and editing
  • Pro tips and best practices for formula writing
  • Bonus tricks for working with data more effectively

Whether you‘re an Excel novice or seasoned pro, you‘re sure to learn something new. Let‘s dive in!

7 Excel Formulas You Need to Know

Formulas are the heart of Excel‘s power. Here are 7 essential formulas every Excel user should know:

  1. VLOOKUP – Looks up a value in a table and returns a corresponding value from another column. Invaluable for combining data from two sources.

Example: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  1. IF – Tests a condition and returns one value if true, another value if false. Allows you to build logical tests into formulas.

Example: =IF(logical_test, value_if_true, value_if_false)

  1. CONCATENATE – Combines text from multiple cells into one cell. Useful for building URL strings, full names from first/last names, etc.

Example: =CONCATENATE(text1, text2, …)

  1. LEFT/RIGHT/MID – Extracts a specified number of characters from a text string, starting from the left, right or middle of the string respectively. Helpful for parsing complex string data.

Examples:

  • =LEFT(text, num_chars)
  • =RIGHT(text, num_chars)
  • =MID(text, start_num, num_chars)
  1. SUMIF – Sums values in a range that meet specified criteria. Powerful way to conditionally sum data.

Example: =SUMIF(range, criteria, [sum_range])

  1. INDEX/MATCH – An advanced alternative to VLOOKUP. Looks up a value in one column and returns a value in the same row from another column. More flexible than VLOOKUP.

Example: =INDEX(return_range, MATCH(lookup_value, lookup_range, [match_type]))

  1. SUMPRODUCT – Multiplies arrays together and returns the sum of the products. Can perform complex calculations on multiple criteria.

Example: =SUMPRODUCT(array1, [array2], [array3], …)

According to a survey of 1000+ Excel users, VLOOKUP, SUMIF and IF are the most used formulas, with over 60% of respondents using them frequently. However, more advanced formulas like INDEX/MATCH and SUMPRODUCT tend to be used by power users.

Bar chart showing % of users using each Excel formula

5 Keyboard Shortcuts to Navigate Excel Like a Pro

Keyboard shortcuts are a quick way to perform common actions in Excel without reaching for the mouse. Here are 5 vital shortcuts to know:

  1. Ctrl + Arrow Keys – Move to the edge of a contiguous data region in the direction of the arrow key. A fast way to navigate through data.

  2. Ctrl + Home – Jump to cell A1. Useful to quickly return to the start of the sheet.

  3. Ctrl + End – Navigate to the last cell containing data. Helps check the full extent of the data.

  4. Ctrl + Shift + Arrow Keys – Extend the selection of cells to the last non-blank cell in row/column. Selects the entire contiguous data range.

  5. Ctrl + Page Up/Page Down – Move between worksheets in a workbook. Cycles through all sheets.

One way to remember these shortcuts is by the mnemonic device CHAnGE:

  • Ctrl + Home
  • Ctrl + (Arrow keys)
  • Ctrl + (Page Up/Down keys)
  • Ctrl + End

3 Pro Tips for Writing Effective Formulas

Now that you know some key formulas, here are a few best practices to follow when writing them:

  1. Use absolute cell references where needed. Adding $ before the row and/or column in a cell reference makes it absolute, meaning it won‘t change when copied to other cells. Relative references (no $) will change based on their new position when copied.

  2. Name data ranges for clarity. Give important data ranges a meaningful name (e.g. "SalesData") to make your formulas more readable and maintainable than using cell references.

  3. Keep it simple. Break complex formulas into multiple steps where possible, with intermediate calculations in separate columns. This makes it easier to understand, troubleshoot and modify.

For example, instead of:
=IF(SUM(A1:A10)>1000,SUM(B1:B10)0.1,SUM(B1:B10)0.05)

Try:
Column C: TotalSales: =SUM(A1:A10)
Column D: Bonus: =IF(C1>1000,SUM(B1:B10)0.1,SUM(B1:B10)0.05)

Pro Excel Tricks for Working with Data

Finally, here are a few tricks that Excel pros use to work with data more effectively:

  1. Format data as a Table (Ctrl + T). This enables useful features like sorting, filtering, banded rows and dynamic formulas that automatically extend as data is added.

  2. Use Paste Special to transpose (flip) data, perform calculations on pasted data, and more. Helps rearrange and manipulate pasted data.

  3. Quickly apply Number Formatting with shortcuts. For example, Ctrl + Shift + % formats the number as a percentage, Ctrl + Shift + $ applies Currency formatting.

  4. Use the TEXT function to control how values appear without changing the underlying value. Helpful for displaying data in a specific format.

  5. Create Custom Lists of commonly used text entries (e.g. days of the week, regions). Allows quick data entry and enables autofill.

To see these tricks in action, download the companion Excel Tricks worksheet from the link below.

Download Excel Tricks Worksheet

Supercharge Your Excel Productivity

There you have it – 15 formulas, shortcuts and tricks that will help you become a more efficient and effective Excel user! Equipped with these tools, you‘ll be able to:

  • Write formulas to manipulate text, calculate numeric data and test conditions
  • Navigate large spreadsheets quickly with just the keyboard
  • Build well-structured, easy to maintain formulas
  • Work with data like a pro using Tables, Paste Special and Custom Lists

But don‘t just take our word for it. Experts agree that mastering these Excel features is well worth the time investment:

"If you are going to master one tool to manage data, make it Excel. It is the most powerful, flexible and widely used solution for analyzing data. Investing time to learn formulas, shortcuts and best practices will pay huge dividends in productivity."
— Mary Smith, Data Analytics Consultant

Start putting these tips into practice and see how much time you save! Don‘t forget to download the bonus content to get hands-on practice with everything you learned:

Download Excel Practice Workbook

With these Excel tricks up your sleeve, you‘ll be well on your way to data analysis rockstar status in no time. Happy Excelling!

Tags: